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

½Ð°Ýsumif §ï¼g¦¨¦r¨å©Î¬OarrayÅý°õ¦æ³t«×ÅܧÖ

½Ð°Ýsumif §ï¼g¦¨¦r¨å©Î¬OarrayÅý°õ¦æ³t«×ÅܧÖ

¥H¤U¬O§Ú¥Ø«e¥i¥H°õ¦æ ¦ý¬O¹Bºâ®É¶¡¦³ÂI¤[
½Ð°Ý¦³§ó¦nªº¤èªk¥i¥H¥h¨ú¥N¶Ü
  1. Sub ­Ü®w®w¦s¦X­p()
  2. Dim X, i As Long
  3. For i = 4 To Sheets("­Ü®w®w¦s").[A1000].End(3).Row

  4.     X = WorksheetFunction.SumIf(Sheets("¤J®w©ú²Ó").Range("O2:O600"), Sheets("­Ü®w®w¦s").Cells(i, 1), Sheets("¤J®w©ú²Ó").Range("R2:R600")) '¤J®w¦X­p
  5.     DA = WorksheetFunction.SumIf(Sheets("¥þ¾÷ºØBOM").Range("P:P"), Sheets("­Ü®w®w¦s").Cells(i, 1), Sheets("¥þ¾÷ºØBOM").Range("Z:Z")) '¤½¥qÁ`»Ý¨D
  6.     BA = WorksheetFunction.SumIf(Sheets("A»Ý¨D").Range("A:A"), Sheets("­Ü®w®w¦s").Cells(i, 1), Sheets("A»Ý¨D").Range("H:H")) 'A­Ü
  7.     bb = WorksheetFunction.SumIf(Sheets("b»Ý¨D").Range("A:A"), Sheets("­Ü®w®w¦s").Cells(i, 1), Sheets("B»Ý¨D").Range("H:H")) 'B­Ü
  8.     BC = WorksheetFunction.SumIf(Sheets("«ü¹Ï©ú²Ó").Range("F:F"), Sheets("­Ü®w®w¦s").Cells(i, 1), Sheets("«ü¹Ï©ú²Ó").Range("L:L")) 'Á`¥X³f
  9.     FY = WorksheetFunction.SumIf(Sheets("¥X®w©ú²Ó").Range("H2:H600"), Sheets("¼o®Æ­Ü").Cells(i, 1) & Sheets("¼o®Æ­Ü").Cells(1, 1), Sheets("¥X®w©ú²Ó").Range("I2:I600"))  '¼o®Æ
  10.     FX = WorksheetFunction.SumIf(Sheets("«ü¹Ï©ú²Ó").Range("F2:F2000"), Sheets("¼o®Æ­Ü").Cells(i, 1), Sheets("«ü¹Ï©ú²Ó").Range("K2:K2000")) '¼o®Æ
  11.     EY = WorksheetFunction.SumIf(Sheets("¥X®w©ú²Ó").Range("H2:H600"), Sheets("°h®w").Cells(i, 1) & Sheets("°h®w").Cells(1, 1), Sheets("¥X®w©ú²Ó").Range("I2:I600"))   '°h®w

  12.    
  13.     QA = Sheets("­Ü®w®w¦s").Cells(i, 4) + Sheets("­Ü®w®w¦s").Cells(i, 5) ' ''­Ü®w®w¦s
  14.     QB = Sheets("­Ü®w®w¦s").Cells(i, "K") + Sheets("­Ü®w®w¦s").Cells(i, "L")
  15.    
  16.     Sheets("­Ü®w®w¦s").Cells(i, 5) = X '¤J®w¦X­p
  17.     Sheets("­Ü®w®w¦s").Cells(i, "M") = BC ''Á`¥X³f
  18.     Sheets("­Ü®w®w¦s").Cells(i, "C") = DA ' Á`»Ý¨D
  19.     Sheets("­Ü®w®w¦s").Cells(i, "H") = QA - QB - BA - bb - BC ''¤½¥q­Ü
  20.     Sheets("­Ü®w®w¦s").Cells(i, "I") = bb ''B­Ü
  21.     Sheets("­Ü®w®w¦s").Cells(i, "J") = BA ''A­Ü
  22.     Sheets("­Ü®w®w¦s").Cells(i, "G") = QA - QB - BC ''Á`¼Æ
  23.     Sheets("¼o®Æ­Ü").Cells(i, 3) = FY + FX
  24.     Sheets("°h®w").Cells(i, 3) = EY

  25. Next i

  26. End Sub
½Æ»s¥N½X

¦^´_ 1# s3526369


½Ð°Ý¤è«Kªþ¤WÀɮ׶Ü? ÁÂÁÂ

TOP

¦^´_ 2# samwang


    ¦nªº ¨º§Ú²¤Æ¤@¨Ç¦A¶K¤W¨Ó

TOP

¦^´_ 2# samwang


   
­Ü®w¦X­p.rar (467.23 KB)

Àɮצb³o ¦A³Â·Ð§A¬Ý¬Ý

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-9-14 14:41 ½s¿è

¦^´_ 4# s3526369


    ÁÂÁ«e½úµoªí¦¹¥DÃD
«á¾Ç²ß±o«Ü¦h§Þ¥©
1.WorksheetFunction.SumIfªº¥Îªk
2.¤u§@ªí©ñ¤J¦r¨å
3.°}¦C§½³¡©ñ¤JÀx¦s®æ

«á¾Çªº¥H¤U¤èªk¥u¸`¬Ù¤@ÂIÂI®É¶¡,¨Ñ«e½ú°Ñ¦Ò
©ß¿j¤Þ¥É,«á¾Ç¤]·Q¾Ç¼F®`ªº«e½ú­Ìªº«ü¾É
  1. Sub TEST_1()
  2. Application.ScreenUpdating = False
  3. Dim X, i As Long, DA, BA, bb, BC, FY, FX, EY, QA, QB, T
  4. Dim S, Srr, Arr, Ac, xR, c, xC
  5. T = Timer
  6. Set Srr = CreateObject("Scripting.Dictionary")
  7.            '0         1        2     3       4       5
  8. S = Split("¤J®w©ú²Ó,¥þ¾÷ºØBOM,A»Ý¨D,b»Ý¨D,«ü¹Ï©ú²Ó,­Ü®w®w¦s", ",")
  9. For i = 0 To UBound(S)
  10.    Set Srr(i) = Sheets(S(i))
  11. Next
  12. Ac = Srr(5).Cells(Rows.Count, 1).End(3).Row
  13. Arr = Range(Srr(5).[N4], Srr(5).Cells(Ac, 1))
  14. For i = 1 To Ac - 3
  15.    xR = Arr(i, 1)
  16.    Arr(i, 5) = WorksheetFunction.SumIf(Srr(0).[O:O], xR, Srr(0).[R:R]) '¤J®w¦X­p
  17.    Arr(i, 3) = WorksheetFunction.SumIf(Srr(1).[P:P], xR, Srr(1).[Z:Z]) '¤½¥qÁ`»Ý¨D
  18.    Arr(i, 10) = WorksheetFunction.SumIf(Srr(2).[A:A], xR, Srr(2).[H:H]) 'A­Ü
  19.    Arr(i, 9) = WorksheetFunction.SumIf(Srr(3).[A:A], xR, Srr(3).[H:H]) 'B­Ü
  20.    Arr(i, 13) = WorksheetFunction.SumIf(Srr(4).[F:F], xR, Srr(4).[L:L]) 'Á`¥X³f
  21.    QA = Arr(i, 4) + Arr(i, 5) ' ''­Ü®w®w¦s
  22.    QB = Arr(i, 11) + Arr(i, 12)
  23.    Arr(i, 8) = QA - QB - Arr(i, 10) - Arr(i, 9) - Arr(i, 13) ''¤½¥q­Ü
  24.    Arr(i, 7) = QA - QB - Arr(i, 13)  ''Á`¼Æ
  25. Next i
  26. c = Array(, 3, 5, 7, 8, 9, 10, 13)
  27. For i = 1 To UBound(c)
  28.    Srr(5).Cells(4, c(i)).Resize(UBound(Arr), 1) = Application.Index(Arr, , c(i))
  29. Next
  30. MsgBox "¦@¯Ó®É¡G" & Timer - T & " ¬í"
  31. End Sub
½Æ»s¥N½X

TOP

¦^´_ 5# Andy2483


    ¼F®`¤F~·PÁ«e½úÀ°§Ú¸Ñ´b
    §Ú¦A¦n¦n¬ã¨s¬ã¨s!

TOP

¦^´_  samwang


   


Àɮצb³o ¦A³Â·Ð§A¬Ý¬Ý
s3526369 µoªí©ó 2022-9-14 08:51


½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim Arr, xD, xD1, xD2, xD3, xD4, T$, i&, QA, QB
Set xD = CreateObject("Scripting.Dictionary")   '¤J®w¦X­p
Set xD1 = CreateObject("Scripting.Dictionary")  '¤½¥qÁ`»Ý¨D
Set xD2 = CreateObject("Scripting.Dictionary")  'A­Ü
Set xD3 = CreateObject("Scripting.Dictionary")  'B­Ü
Set xD4 = CreateObject("Scripting.Dictionary")  'Á`¥X³f
TM = Timer
With Sheets("¤J®w©ú²Ó")
    Arr = .Range(.[r1], .[o65536].End(3))
    For i = 2 To UBound(Arr)
        T = Arr(i, 1): xD(T) = xD(T) + Arr(i, 4) '¤J®w¦X­p
    Next
End With
With Sheets("¥þ¾÷ºØBOM")
    Arr = .Range(.[z1], .[p65536].End(3))
    For i = 2 To UBound(Arr)
        T = Arr(i, 1): xD1(T) = xD1(T) + Arr(i, 11) '¤½¥qÁ`»Ý¨D
    Next
End With
With Sheets("A»Ý¨D")
    Arr = .Range(.[h1], .[a65536].End(3))
    For i = 4 To UBound(Arr)
        T = Arr(i, 1): xD2(T) = xD2(T) + Arr(i, 8)  'A­Ü
    Next
End With
With Sheets("B»Ý¨D")
    Arr = .Range(.[h1], .[a65536].End(3))
    For i = 4 To UBound(Arr)
        T = Arr(i, 1): xD3(T) = xD3(T) + Arr(i, 8)  'B­Ü
    Next
End With
With Sheets("«ü¹Ï©ú²Ó")
    Arr = .Range(.[L1], .[f65536].End(3))
    For i = 4 To UBound(Arr)
        T = Arr(i, 1): xD4(T) = xD4(T) + Arr(i, 7)  'Á`¥X³f
    Next
End With
With Sheets("­Ü®w®w¦s")
    Arr = .Range(.[m3], .[a65536].End(3))
    For i = 2 To UBound(Arr)
        T = Arr(i, 1)
        QA = Arr(i, 4) + Arr(i, 5) ' ''­Ü®w®w¦s
        QB = Arr(i, 11) + Arr(i, 12)
        Arr(i, 5) = xD(T)    '¤J®w¦X­p
        Arr(i, 13) = xD4(T)  'Á`¥X³f
        Arr(i, 3) = xD1(T)   'Á`»Ý¨D
        Arr(i, 8) = QA - QB - xD2(T) - xD3(T) - xD4(T)  '¤½¥q­Ü
        Arr(i, 9) = xD3(T)   'B­Ü
        Arr(i, 10) = xD2(T)  'A­Ü
        Arr(i, 7) = QA - QB - xD4(T)  'Á`¼Æ
    Next
    .[a3].Resize(UBound(Arr), 13) = Arr
End With
MsgBox "¦@¯Ó®É¡G" & Timer - TM & " ¬í"
End Sub

TOP

¦^´_ 7# samwang


    ¤Ó§Ö¤F! ±j!
ÁÂÁ«e½ú¤À¨É!

TOP

¦^´_ 7# samwang


    ¦n¼F®`!
    ³o¤]¬O¤@ºØ¤èªk,³t«×¤S§ó§Ö¤F!
    ÁÂÁ«e½ú!

TOP

¦^´_ 7# samwang


  «e½ú½Ð°Ý¤@¤U¦pªG¬O­n§PÂ_¨â­Ó±ø¥ó
¨Ò¦p:
  1. With Sheets("¤J®w©ú²Ó")
  2.     Arr = .Range(.[r1], .[o65536].End(3))
  3.     For i = 2 To UBound(Arr)
  4.         T = Arr(i, 1): xD(T) = xD(T) + Arr(i, 4) '¤J®w¦X­p
  5.     Next
  6. End With
½Æ»s¥N½X
¥[¤J§PÂ_­Ü®w§Oªº¸Ü

TOP

        ÀR«ä¦Û¦b : °ß¨ä´L­«¦Û¤vªº¤H¡A¤~§ó«i©óÁY¤p¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD