[µo°Ý] for ±_ª¬°j°é ¹Bºâ³t«×¦³ÂI¤[½Ð°Ý¸Ó¦p¦ó§ïµ½

s3526369

1. Sub ¾P³f©ú²ÓÁ`¼Æ()
   
2.     Dim x As Long
   
3.     With ¾P³fÁ`¼Æ
   
4.     For i = 5 To 64
   
5.         For c = 10 To 39
   
6.          x = WorksheetFunction.sumif(Sheets("¾P³f©ú²Ó").Range("B2:B500"), Sheets("¾P³fÁ`¼Æ").Cells(1, c) & Sheets("¾P³fÁ`¼Æ").Cells(i, 1), Sheets("¾P³f©ú²Ó").Range("O2:O600"))
   
7.                If Sheets("¾P³fÁ`¼Æ").Cells(i, c) <> x Then
   
8.                    Sheets("¾P³fÁ`¼Æ").Cells(i, c) = x
   
9.                  End If
   
10.             Next
    
11.            Next
    
12.         End With
    
13.   
    
14.             End Sub

½Æ»s¥N½X

­ã´£³¡ªL

1800®æ©ñsumif, ­YÁÙ¬O·|¥d, ¥Î¦r¨å¤Îarray¸û§Ö~~
éµÛ¼g~~~~
Sub TEST_01()
Dim Arr, Crr, xD, R&, C&, T$
Set xD = CreateObject("Scripting.Dictionary")
Arr = [¾P³f©ú²Ó!B2:O500]
For R = 1 To UBound(Arr)
    T = Arr(R, 1)
    If T <> "" Then xD(T) = xD(T) + Val(Arr(R, 14))
Next R
Arr = [¾P³fÁ`¼Æ!A1:AM64]
Crr = [¾P³fÁ`¼Æ!J5:AM64]
For R = 5 To UBound(Arr)
For C = 10 To UBound(Arr, 2)
    Crr(R - 4, C - 9) = xD(Arr(1, C) & Arr(R, 1))
Next C
Next R
[¾P³fÁ`¼Æ!J5:AM64] = Crr
End Sub

======================

GBKEE

¦^´_ 1# s3526369

1. Sub Ex()
   
2.     With Sheets("¾P³fÁ`¼Æ").Range(Sheets("¾P³fÁ`¼Æ").Cells(5, 10), Sheets("¾P³fÁ`¼Æ").Cells(64, 39))
   
3.         '**¼g¤J¤½¦¡
   
4.         .Cells = "=SumIf(¾P³f©ú²Ó!R2C2:R500C2,¾P³fÁ`¼Æ!R1C&¾P³fÁ`¼Æ!RC1,¾P³f©ú²Ó!R2C15:R600C15)"
   
5.         '**¼g¤J¤½¦¡ :¤£Åã¥Ü0
   
6.        ' .Cells = "=IF(SumIf(¾P³f©ú²Ó!R2C2:R500C2,¾P³fÁ`¼Æ!R1C&¾P³fÁ`¼Æ!RC1,¾P³f©ú²Ó!R2C15:R600C15)>0,SumIf(¾P³f©ú²Ó!R2C2:R500C2,¾P³fÁ`¼Æ!R1C&¾P³fÁ`¼Æ!RC1,¾P³f©ú²Ó!R2C15:R600C15),"""")"
   
7.        '**Âл\¤½¦¡±¼,Åã¥Ü­È
   
8.         '.Cells = .Value
   
9.     End With
   
10. End Sub

½Æ»s¥N½X