- ©«¤l
- 1446
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1470
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-10-21
|
¥»©«³Ì«á¥Ñ 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½ú̪º«ü¾É- Sub TEST_1()
- Application.ScreenUpdating = False
- Dim X, i As Long, DA, BA, bb, BC, FY, FX, EY, QA, QB, T
- Dim S, Srr, Arr, Ac, xR, c, xC
- T = Timer
- Set Srr = CreateObject("Scripting.Dictionary")
- '0 1 2 3 4 5
- S = Split("¤J®w©ú²Ó,¥þ¾÷ºØBOM,A»Ý¨D,b»Ý¨D,«ü¹Ï©ú²Ó,Ü®w®w¦s", ",")
- For i = 0 To UBound(S)
- Set Srr(i) = Sheets(S(i))
- Next
- Ac = Srr(5).Cells(Rows.Count, 1).End(3).Row
- Arr = Range(Srr(5).[N4], Srr(5).Cells(Ac, 1))
- For i = 1 To Ac - 3
- xR = Arr(i, 1)
- Arr(i, 5) = WorksheetFunction.SumIf(Srr(0).[O:O], xR, Srr(0).[R:R]) '¤J®w¦Xp
- Arr(i, 3) = WorksheetFunction.SumIf(Srr(1).[P:P], xR, Srr(1).[Z:Z]) '¤½¥qÁ`»Ý¨D
- Arr(i, 10) = WorksheetFunction.SumIf(Srr(2).[A:A], xR, Srr(2).[H:H]) 'AÜ
- Arr(i, 9) = WorksheetFunction.SumIf(Srr(3).[A:A], xR, Srr(3).[H:H]) 'BÜ
- Arr(i, 13) = WorksheetFunction.SumIf(Srr(4).[F:F], xR, Srr(4).[L:L]) 'Á`¥X³f
- QA = Arr(i, 4) + Arr(i, 5) ' ''Ü®w®w¦s
- QB = Arr(i, 11) + Arr(i, 12)
- Arr(i, 8) = QA - QB - Arr(i, 10) - Arr(i, 9) - Arr(i, 13) ''¤½¥qÜ
- Arr(i, 7) = QA - QB - Arr(i, 13) ''Á`¼Æ
- Next i
- c = Array(, 3, 5, 7, 8, 9, 10, 13)
- For i = 1 To UBound(c)
- Srr(5).Cells(4, c(i)).Resize(UBound(Arr), 1) = Application.Index(Arr, , c(i))
- Next
- MsgBox "¦@¯Ó®É¡G" & Timer - T & " ¬í"
- End Sub
½Æ»s¥N½X |
|