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

s3526369

¥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. 
   
5.     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
   
6.     DA = WorksheetFunction.SumIf(Sheets("¥þ¾÷ºØBOM").Range("P:P"), Sheets("­Ü®w®w¦s").Cells(i, 1), Sheets("¥þ¾÷ºØBOM").Range("Z:Z")) '¤½¥qÁ`»Ý¨D
   
7.     BA = WorksheetFunction.SumIf(Sheets("A»Ý¨D").Range("A:A"), Sheets("­Ü®w®w¦s").Cells(i, 1), Sheets("A»Ý¨D").Range("H:H")) 'A­Ü
   
8.     bb = WorksheetFunction.SumIf(Sheets("b»Ý¨D").Range("A:A"), Sheets("­Ü®w®w¦s").Cells(i, 1), Sheets("B»Ý¨D").Range("H:H")) 'B­Ü
   
9.     BC = WorksheetFunction.SumIf(Sheets("«ü¹Ï©ú²Ó").Range("F:F"), Sheets("­Ü®w®w¦s").Cells(i, 1), Sheets("«ü¹Ï©ú²Ó").Range("L:L")) 'Á`¥X³f
   
10.     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®Æ
    
11.     FX = WorksheetFunction.SumIf(Sheets("«ü¹Ï©ú²Ó").Range("F2:F2000"), Sheets("¼o®Æ­Ü").Cells(i, 1), Sheets("«ü¹Ï©ú²Ó").Range("K2:K2000")) '¼o®Æ
    
12.     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
    
13. 
    
14.    
    
15.     QA = Sheets("­Ü®w®w¦s").Cells(i, 4) + Sheets("­Ü®w®w¦s").Cells(i, 5) ' ''­Ü®w®w¦s
    
16.     QB = Sheets("­Ü®w®w¦s").Cells(i, "K") + Sheets("­Ü®w®w¦s").Cells(i, "L")
    
17.    
    
18.     Sheets("­Ü®w®w¦s").Cells(i, 5) = X '¤J®w¦X­p
    
19.     Sheets("­Ü®w®w¦s").Cells(i, "M") = BC ''Á`¥X³f
    
20.     Sheets("­Ü®w®w¦s").Cells(i, "C") = DA ' Á`»Ý¨D
    
21.     Sheets("­Ü®w®w¦s").Cells(i, "H") = QA - QB - BA - bb - BC ''¤½¥q­Ü
    
22.     Sheets("­Ü®w®w¦s").Cells(i, "I") = bb ''B­Ü
    
23.     Sheets("­Ü®w®w¦s").Cells(i, "J") = BA ''A­Ü
    
24.     Sheets("­Ü®w®w¦s").Cells(i, "G") = QA - QB - BC ''Á`¼Æ
    
25.     Sheets("¼o®Æ­Ü").Cells(i, 3) = FY + FX
    
26.     Sheets("°h®w").Cells(i, 3) = EY
    
27. 
    
28. Next i
    
29. 
    
30. End Sub

½Æ»s¥N½X

samwang

¦^´_ 1# s3526369


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

s3526369

¦^´_ 2# samwang


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

s3526369

¦^´_ 2# samwang


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

¤U¸ü¦¸¼Æ: 13

2022-9-14 08:50

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

Andy2483

¦^´_ 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

s3526369

¦^´_ 5# Andy2483


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

samwang

¦^´_  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

Andy2483

¦^´_ 7# samwang


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

s3526369

¦^´_ 7# samwang


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

s3526369

¦^´_ 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ªº¸Ü