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

s3526369

¦^´_ 5# Andy2483


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

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

¦^´_ 2# samwang


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

¤U¸ü¦¸¼Æ: 13

2022-9-14 08:50

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

s3526369

¦^´_ 2# samwang


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

samwang

¦^´_ 1# s3526369


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