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

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

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

  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

¥»©«³Ì«á¥Ñ GBKEE ©ó 2020-4-24 06:18 ½s¿è

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2020-4-24 10:15 ½s¿è

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

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

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C
ªð¦^¦Cªí ¤W¤@¥DÃD