ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

¦p¦ó¥ÎVBA°µ¦h±ø¥ó§P©w

¦^´_ 1# jj369963
¸Õ¸Õ¬Ý
1.Äæ¦ì¸m¤J¤½¦¡
Sub ex()
Dim r As Integer
r = Range("A65535").End(3).Row    '­p¼Æ¸ê®Æ¼Æ
[H2].Resize(r - 1) = "=countif($B$2:$D$" & r & ",A2)"   '¥¿¦V¦¸¼Æ¥[Á`Äæ©ñ¤J¤½¦¡
[I2].Resize(r - 1) = "=countif($E$2:$G$" & r & ",A2)"   '­t¦V¦¸¼Æ¥[Á`Äæ©ñ¤J¤½¦¡
[J2].Resize(r - 1) = "=Standardize(H2, $P$2, $Q$2)"     'LMÄæ©ñ¤J¤½¦¡
[K2].Resize(r - 1) = "=Standardize(I2, $P$2, $Q$2)"     'LLÄæ©ñ¤J¤½¦¡
[L2].Resize(r - 1) = "=Sum(J2:K2)"                      'SIÄæ©ñ¤J¤½¦¡
[M2].Resize(r - 1) = "=J2 - K2"                         'SPÄæ©ñ¤J¤½¦¡
[N2].Resize(r - 1) = "=IF(AND((J2>0)*(K2<0)*(M2>1)=1),""¨üÅwªï"",IF(AND((J2<0)*(K2>0)*(M2<-1)=1),""³Q©Úµ´"",IF(AND((J2<0)*(K2<0)*(L2<-1)=1),""³Q©¿µø"",IF(AND((J2>0)*(K2>0)*(L2>1)=1),""¨üª§Ä³"",IF(AND((M2<1)*(M2>-1)*(L2<1)*(L2>-1)=1),""¥­§¡²Õ"")))))"   '¤ÀÃþÄæ©ñ¤J¤½¦¡
End Sub

2.ª½±µ­pºâ¬ÛÃö¼Æ­È
Sub ex1()
Dim r, x As Integer
For r = 2 To Range("A65535").End(3).Row
x = 0
'------­pºâ¥¿¦V¦¸¼Æ¥[Á`
For Each Rng In Range([B2], [D65535].End(3))
    If Cells(r, "a") = Rng Then x = x + 1
Next
Cells(r, "H") = x
x = 0
'------­pºâ­t¦V¦¸¼Æ¥[Á`
For Each Rng In Range([E2], [G65535].End(3))
    If Cells(r, "a") = Rng Then x = x + 1
Next
Cells(r, "I") = x
Next
For r = 2 To Range("A65535").End(3).Row
   Cells(r, "J") = WorksheetFunction.Standardize(Cells(r, "H"), Range("P2"), Range("Q2"))
   Cells(r, "K") = WorksheetFunction.Standardize(Cells(r, "I"), Range("P2"), Range("Q2"))
   Cells(r, "L") = Cells(r, "J") + Cells(r, "K")
   Cells(r, "M") = Cells(r, "J") - Cells(r, "K")
   If Cells(r, "J") > 0 And Cells(r, "K") < 0 And Cells(r, "M") > 1 Then Cells(r, "N") = "¨üÅwªï"
   If Cells(r, "J") > 0 And Cells(r, "K") > 0 And Cells(r, "L") > 1 Then Cells(r, "N") = "¨üª§Ä³"
   If Cells(r, "J") < 0 And Cells(r, "K") > 0 And Cells(r, "M") < -1 Then Cells(r, "N") = "³Q©Úµ´"
   If Cells(r, "J") < 0 And Cells(r, "K") < 0 And Cells(r, "L") < -1 Then Cells(r, "N") = "³Q©¿µø"
   If Cells(r, "M") < 1 And Cells(r, "M") > -1 And Cells(r, "L") < 1 And Cells(r, "L") > -1 Then Cells(r, "N") = "¥­§¡²Õ"
Next
End Sub

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡µLªk¾B¾×¡j©È®É¶¡®ø³u¡Aªá¤F³\¦h¤ß¦å¡A·QºÉ¦U¦¡¤èªk­n¾B¾×®É¶¡¡Aµ²ªG¬O¡G®ö¶O¤F§ó¦h®É¶¡¡A¥B¤@µL©Ò¦¨¡I
ªð¦^¦Cªí ¤W¤@¥DÃD