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

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

¦^´_ 20# samwang


    «e½ú¦­¦w
1.¨Æ±¡À³¸Ó¤£¬O³o»ò³æ¯Â,¦h¥X¤F´X­Óªí¤F!
2.WorksheetFunction.SumIfs  WorksheetFunction.SumIf ¨âºØ³£¦³
3.ÃD¥D«e½ú·Pı«Ü¤ß«æ!¸£µ¬°Ê±o«Ü§Ö,°¨¤W®M¥Î,¤S°¨¤W¥X·s°ÝÃD! ¤]ÆZ¦³½ì!
4.«Üºa©¯¥i¥H¦b³o½×¾Â¸ò«e½ú¾Ç²ß,ÁÂÁ«ü¾É¤À¨É!

TOP

¦^´_ 20# samwang


  ·PÁ«e½ú~
  ¬O±o¨S¿ù³Ì²³æªº¤èªk,¥i¥H¥ÎIF´N¦n,¤@®É¨S¦³·Q¨ì

TOP

¦^´_ 21# Andy2483


    ¦­¦w~ ¦³§ä¨ì¸Ñ¨M±o¿ìªk¤F
Sub TEST_2()
Application.ScreenUpdating = False
Dim x, i, QA, QB, T, S, Srr, Arr, Ac, xR, C
Dim Trr, Brr, Crr, Rs, Rqs, Rqn, Ras, Ran, B
T = Timer
Set Srr = CreateObject("Scripting.Dictionary")
Set Trr = CreateObject("Scripting.Dictionary")
S = Split("¤J®w©ú²Ó,¥þ¾÷ºØBOM,A»Ý¨D,b»Ý¨D,«ü¹Ï©ú²Ó,­Ü®w®w¦s", ",")
For i = 0 To UBound(S)
   Set Srr(i) = Sheets(S(i))
   Set Trr(i) = CreateObject("Scripting.Dictionary")
Next
Rs = Rows.Count
Ac = Srr(5).Cells(Rs, 1).End(3).Row
Arr = Range(Srr(5).[N4], Srr(5).Cells(Ac, 1))
C = Array(15, 18, 16, 26, 1, 8, 1, 8, 6, 12)
For i = 0 To UBound(C) Step 2
   Set Rqs = Srr(i / 2).Cells(1, C(i))
   Set Rqn = Srr(i / 2).Cells(Rs, C(i)).End(3)
   Brr = Srr(i / 2).Range(Rqs, Rqn)
   Set Ras = Srr(i / 2).Cells(1, C(i + 1))
   Set Ran = Srr(i / 2).Cells(Rqn.Row, C(i + 1))
   Crr = Srr(i / 2).Range(Ras, Ran)
   For x = 1 To UBound(Brr)
      B = Brr(x, 1)
      Trr(i / 2)(B) = Trr(i / 2)(B) + Crr(x, 1)
   Next
Next

For i = 1 To Ac - 3
   xR = Arr(i, 1)
   Arr(i, 5) = IIf(Trr(0)(xR), Trr(0)(xR), 0) '¤J®w¦X­p
   Arr(i, 3) = IIf(Trr(1)(xR), Trr(1)(xR), 0) '¤½¥qÁ`»Ý¨D
   Arr(i, 10) = IIf(Trr(2)(xR), Trr(2)(xR), 0) 'A­Ü
   Arr(i, 9) = IIf(Trr(3)(xR), Trr(3)(xR), 0) 'B­Ü
   Arr(i, 13) = IIf(Trr(4)(xR), Trr(4)(xR), 0) 'Á`¥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
Set Arr = Nothing
Set Brr = Nothing
Set Crr = Nothing
MsgBox "¦@¯Ó®É¡G" & Timer - T & " ¬í"
End Sub
·Q½Ð°Ý¤@¤U¬õ¦â³o¬q,¿ìªk¸ÑÄÀ­ì¦]¶Ü,¬Ý¤£¤ÓÀ´

TOP

¦^´_ 23# s3526369


    ³o¬q¤£¾A¦X«e½úªº·s½d¨ÒÀɤF!±ø¥ó¤£¤@¼Ë
C = Array(15, 18, 16, 26, 1, 8, 1, 8, 6, 12)  '³o¬OO,R,P,ZA,H,A,H,F,LÄæ¦ì
For i = 0 To UBound(C) Step 2   '¨â¨â¤@²Õ¥h«ü©w¤u§@ªí«ü©wÄæ¦ì
   Set Rqs = Srr(i / 2).Cells(1, C(i)) 'i / 2¬O«ü©w¨C­Ó¤u§@ªí  i=0®É Rqs¬O[O1]
   Set Rqn = Srr(i / 2).Cells(Rs, C(i)).End(3)
   Brr = Srr(i / 2).Range(Rqs, Rqn)
   Set Ras = Srr(i / 2).Cells(1, C(i + 1))   ' i=0®É Ras¬O[R1]
   Set Ran = Srr(i / 2).Cells(Rqn.Row, C(i + 1))
   Crr = Srr(i / 2).Range(Ras, Ran)
   For x = 1 To UBound(Brr)
      B = Brr(x, 1)
      Trr(i / 2)(B) = Trr(i / 2)(B) + Crr(x, 1)
   Next
Next

TOP

¦^´_ 24# Andy2483


    ÁÂÁ«e½ú,­ì¨Ó¦p¦¹¦h¤@¼Ë¥i¥H¾Ç²ß

TOP

¦^´_ 17# s3526369


    ³o½d¨Ò¾ã²z¤F¤@¤U,¨Ã¥B§âSumIfªº §ï¬°SumIfs µo²{ºÃ°Ý
1.TC,TD,UC,UD³o´X­ÓÅܼƸò°õ¦æµ²ªG¬O¨S¦³Ãö«Y,©Ò¥Hµ{¦¡¦h¶]¤F0.5¬í
2.°h®w,¼o®Æ­Ü ³o¨â­Ó¤u§@ªí¤]¬O¸ò°õ¦æµ²ªG¬O¨S¦³Ãö«Y

½Ð±Ð«e½ú:
A.¬O½d¨Ò¤£§¹¾ã?
B.ÁÙ¬O­n©¿²¤¤W­z¨â¶µºÃ°Ý!²¤Æ¦p¤Uªºµ{¦¡½X
C.¨ä¥L
¦pªG¬OA ½Ð´£¨Ñ·sªº½d¨Ò,¦pªG¬OB «á¾ÇÄ~Äò¥H¤U¦Cµ{¦¡½X¬ã²ßÁYµu°õ¦æ®É¶¡,
¦pªG¬OC ½Ð¦A»¡©ú
  1. Sub ­Ü®w®w¦s¦X­pCOPY3()
  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       6         7
  8. S = Split("¤J®w©ú²Ó,¥þ¾÷ºØBOM,A»Ý¨D,b»Ý¨D,«ü¹Ï©ú²Ó,¥X®w©ú²Ó,¤½¥q½LÂI,­Ü®w®w¦s", ",")
  9. For i = 0 To UBound(S)
  10.    Set Srr(i) = Sheets(S(i))
  11. Next
  12. Ac = Srr(7).Cells(Rows.Count, 1).End(3).Row
  13. Arr = Range(Srr(7).[N4], Srr(7).Cells(Ac, 1))
  14. For i = 1 To Ac - 3
  15.    xR = Arr(i, 1)
  16.    Arr(i, 5) = WorksheetFunction.SumIfs(Srr(0).[R:R], Srr(0).[O:O], xR) '¤J®w¦X­p   CCCCCCC
  17.    TA = WorksheetFunction.SumIfs(Srr(0).[R:R], Srr(0).[O:O], xR, Srr(0).[S:S], "A­Ü")  '¤J®w©ú²Ó-A
  18.    UA = WorksheetFunction.SumIfs(Srr(0).[R:R], Srr(0).[O:O], xR, Srr(0).[S:S], "B­Ü")  '¤J®w©ú²Ó-B
  19.    Arr(i, 3) = WorksheetFunction.SumIfs(Srr(1).[Z:Z], Srr(1).[P:P], xR) '¤½¥qÁ`»Ý¨D CCCCCCC
  20.    'TC = WorksheetFunction.SumIfs(Srr(1).[U:U], Srr(1).[P:P], xR, Srr(1).[T:T], "A­Ü")  '¥þBOM-´Ý³æA»Ý¨D
  21.    'TD = WorksheetFunction.SumIfs(Srr(1).[V:V], Srr(1).[P:P], xR, Srr(1).[T:T], "A­Ü")  '¥þBOM-­q³æA»Ý¨D
  22.    'UC = WorksheetFunction.SumIfs(Srr(1).[U:U], Srr(1).[P:P], xR, Srr(1).[T:T], "B­Ü")  '¥þBOM-´Ý³æB»Ý¨D
  23.    'UD = WorksheetFunction.SumIfs(Srr(1).[V:V], Srr(1).[P:P], xR, Srr(1).[T:T], "B­Ü")  '¥þBOM-­q³æB»Ý¨D
  24.    TE = WorksheetFunction.SumIfs(Srr(4).[L:L], Srr(4).[F:F], xR, Srr(4).[J:J], "A­Ü")  '«ü¹Ï©ú²Ó-A
  25.    UE = WorksheetFunction.SumIfs(Srr(4).[L:L], Srr(4).[F:F], xR, Srr(4).[J:J], "B­Ü")  '«ü¹Ï©ú²Ó-B
  26.    Arr(i, 13) = WorksheetFunction.SumIfs(Srr(4).[L:L], Srr(4).[F:F], xR) '«ü¹Ï©ú²Ó-Á`¥X³f   CCCCCC
  27.    PA = WorksheetFunction.SumIfs(Srr(4).[K:K], Srr(4).[F:F], xR) '«ü¹Ï©ú²Ó-¼o®Æ   CCCCCC
  28.    TB = WorksheetFunction.SumIfs(Srr(5).[R:R], Srr(5).[O:O], xR, Srr(5).[S:S], "A­Ü")  '¥X®w©ú²Ó-A
  29.    UB = WorksheetFunction.SumIfs(Srr(5).[R:R], Srr(5).[O:O], xR, Srr(5).[S:S], "B­Ü")  '¥X®w©ú²Ó-B
  30.    Arr(i, 11) = WorksheetFunction.SumIfs(Srr(5).[R:R], Srr(5).[O:O], xR, Srr(5).[S:S], "°h®w") '¥X®w©ú²Ó-°h®w
  31.    PB = WorksheetFunction.SumIfs(Srr(5).[R:R], Srr(5).[O:O], xR, Srr(5).[S:S], "¼o®Æ­Ü") '¥X®w©ú²Ó-¼o®Æ
  32.    Arr(i, 4) = WorksheetFunction.SumIfs(Srr(6).[G:G], Srr(6).[A:A], xR) '¤W¤ë½LÂI¼Æ
  33.    TF = WorksheetFunction.SumIfs(Srr(6).[F:F], Srr(6).[A:A], xR) '¤½¥q½LÂI-A­Ü
  34.    TG = WorksheetFunction.SumIfs(Srr(6).[K:K], Srr(6).[A:A], xR) '¤½¥q½LÂI-½Õ¾ãA­Ü
  35.    UF = WorksheetFunction.SumIfs(Srr(6).[E:E], Srr(6).[A:A], xR) '¤½¥q½LÂI-B­Ü
  36.    UG = WorksheetFunction.SumIfs(Srr(6).[J:J], Srr(6).[A:A], xR) '¤½¥q½LÂI-½Õ¾ãB­Ü
  37.    Arr(i, 10) = TF + TG + TA + TB - TE 'A­Ü
  38.    Arr(i, 9) = UF + UG + UA + UB - UE 'B­Ü
  39.    Arr(i, 12) = PA + PB
  40.    XA = 0
  41.    If Arr(i, 3) > 0 Then
  42.       XA = Arr(i, 4) + Arr(i, 5) - Arr(i, 11) - Arr(i, 12) - Arr(i, 3)
  43.       If XA > 0 Then
  44.          XA = 0
  45.       End If
  46.    End If
  47.    Arr(i, 6) = XA
  48.    QA = Arr(i, 4) + Arr(i, 5) ' ''­Ü®w®w¦s
  49.    QB = Arr(i, 11) + Arr(i, 12)
  50.    Arr(i, 8) = QA - QB - Arr(i, 10) - Arr(i, 9) - Arr(i, 13) ''¤½¥q­Ü
  51.    Arr(i, 7) = QA - QB - Arr(i, 13)  ''Á`¼Æ
  52. Next i
  53. C = Array(, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13)
  54. For i = 1 To UBound(C)
  55.    Srr(7).Cells(4, C(i)).Resize(UBound(Arr), 1) = Application.Index(Arr, , C(i))
  56. Next
  57. MsgBox "¦@¯Ó®É¡G" & Timer - T & " ¬í"
  58. End Sub
½Æ»s¥N½X

TOP

¦^´_ 17# s3526369


    ÁÂÁ«e½ú´£¨Ñ¦¹½d¨Ò
²ß±o ÅܼƦW°Ñ¼Æ¤Æ
¥H¤Uµ{¦¡½X¨Ñ°Ñ¦Ò
«Ý«e½ú¼Ó¤Wªº¦^ÂÐ
  1. Option Explicit
  2. Sub ­Ü®w®w¦s¦X­pCOPY5()
  3. Application.ScreenUpdating = False
  4. Dim x&, i&, ­È(1 To 17) As Long
  5. Dim S, Srr, Arr, Ac, xR, C, T, XA, QA, QB
  6. T = Timer
  7. Set Srr = CreateObject("Scripting.Dictionary")
  8.            '0         1        2     3       4       5       6         7
  9. S = Split("¤J®w©ú²Ó,¥þ¾÷ºØBOM,A»Ý¨D,b»Ý¨D,«ü¹Ï©ú²Ó,¥X®w©ú²Ó,¤½¥q½LÂI,­Ü®w®w¦s", ",")
  10. For i = 0 To UBound(S)
  11.    Set Srr(i) = Sheets(S(i))
  12. Next
  13. Ac = Srr(7).Cells(Rows.Count, 1).End(3).Row
  14. Arr = Range(Srr(7).[N4], Srr(7).Cells(Ac, 1))
  15. For i = 1 To Ac - 3
  16.    xR = Arr(i, 1)
  17.    ­È(1) = WorksheetFunction.SumIfs(Srr(0).[R:R], Srr(0).[O:O], xR) '¤J®w¦X­pC
  18.    ­È(2) = WorksheetFunction.SumIfs(Srr(0).[R:R], Srr(0).[O:O], xR, Srr(0).[S:S], "A­Ü") '¤J®w©ú²Ó-A
  19.    ­È(3) = WorksheetFunction.SumIfs(Srr(0).[R:R], Srr(0).[O:O], xR, Srr(0).[S:S], "B­Ü") '¤J®w©ú²Ó-B
  20.    ­È(4) = WorksheetFunction.SumIfs(Srr(1).[Z:Z], Srr(1).[P:P], xR) '¤½¥qÁ`»Ý¨DC
  21.    ­È(5) = WorksheetFunction.SumIfs(Srr(4).[L:L], Srr(4).[F:F], xR, Srr(4).[J:J], "A­Ü") '«ü¹Ï©ú²Ó-A
  22.    ­È(6) = WorksheetFunction.SumIfs(Srr(4).[L:L], Srr(4).[F:F], xR, Srr(4).[J:J], "B­Ü") '«ü¹Ï©ú²Ó-B
  23.    ­È(7) = WorksheetFunction.SumIfs(Srr(4).[L:L], Srr(4).[F:F], xR) '«ü¹Ï©ú²Ó-Á`¥X³fC
  24.    ­È(8) = WorksheetFunction.SumIfs(Srr(4).[K:K], Srr(4).[F:F], xR) '«ü¹Ï©ú²Ó-¼o®ÆC
  25.    ­È(9) = WorksheetFunction.SumIfs(Srr(5).[R:R], Srr(5).[O:O], xR, Srr(5).[S:S], "A­Ü") '¥X®w©ú²Ó-A
  26.    ­È(10) = WorksheetFunction.SumIfs(Srr(5).[R:R], Srr(5).[O:O], xR, Srr(5).[S:S], "B­Ü") '¥X®w©ú²Ó-B
  27.    ­È(11) = WorksheetFunction.SumIfs(Srr(5).[R:R], Srr(5).[O:O], xR, Srr(5).[S:S], "°h®w") '¥X®w©ú²Ó-°h®w
  28.    ­È(12) = WorksheetFunction.SumIfs(Srr(5).[R:R], Srr(5).[O:O], xR, Srr(5).[S:S], "¼o®Æ­Ü") '¥X®w©ú²Ó-¼o®Æ
  29.    ­È(13) = WorksheetFunction.SumIfs(Srr(6).[G:G], Srr(6).[A:A], xR) '¤W¤ë½LÂI¼Æ
  30.    ­È(14) = WorksheetFunction.SumIfs(Srr(6).[F:F], Srr(6).[A:A], xR) '¤½¥q½LÂI-A­Ü
  31.    ­È(15) = WorksheetFunction.SumIfs(Srr(6).[K:K], Srr(6).[A:A], xR) '¤½¥q½LÂI-½Õ¾ãA­Ü
  32.    ­È(16) = WorksheetFunction.SumIfs(Srr(6).[E:E], Srr(6).[A:A], xR) '¤½¥q½LÂI-B­Ü
  33.    ­È(17) = WorksheetFunction.SumIfs(Srr(6).[J:J], Srr(6).[A:A], xR) '¤½¥q½LÂI-½Õ¾ãB­Ü
  34.    Arr(i, 5) = ­È(1)
  35.    Arr(i, 3) = ­È(4)
  36.    Arr(i, 13) = ­È(7)
  37.    Arr(i, 11) = ­È(11)
  38.    Arr(i, 4) = ­È(13)
  39.    Arr(i, 10) = ­È(14) + ­È(15) + ­È(2) + ­È(9) - ­È(5) 'A­Ü
  40.    Arr(i, 9) = ­È(16) + ­È(17) + ­È(3) + ­È(10) - ­È(6) 'B­Ü
  41.    Arr(i, 12) = ­È(8) + ­È(12)
  42.    XA = 0
  43.    If ­È(4) > 0 Then
  44.       XA = ­È(13) + ­È(1) - ­È(11) - Arr(i, 12) - ­È(4)
  45.       If XA > 0 Then
  46.          XA = 0
  47.       End If
  48.    End If
  49.    Arr(i, 6) = XA
  50.    QA = ­È(13) + ­È(1) '­Ü®w®w¦s
  51.    QB = ­È(11) + Arr(i, 12)
  52.    Arr(i, 8) = QA - QB - Arr(i, 10) - Arr(i, 9) - ­È(7) '¤½¥q­Ü
  53.    Arr(i, 7) = QA - QB - ­È(7)  'Á`¼Æ
  54. Next i
  55. C = Array(, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13)
  56. For i = 1 To UBound(C)
  57.    Srr(7).Cells(4, C(i)).Resize(UBound(Arr), 1) = Application.Index(Arr, , C(i))
  58. Next
  59. MsgBox "¦@¯Ó®É¡G" & Timer - T & " ¬í"
  60. End Sub
½Æ»s¥N½X

TOP

¦^´_ 26# Andy2483


    «e½ú§A¦n~
    ¬O½d¨Ò¤£§¹¾ã,¥i¥H¥ý©¿²¤±¼¨S¦³Ãö«Y!

TOP

¦^´_ 7# samwang


    «e½ú§A¦n~½Ð°Ý®M¥Îµ{¦¡«á,­Ü®w®w¦sA.BÄ檺¤½¦¡Åܦ¨­È,¦³¸Ñ¨Mªº¿ìªk¶Ü?

TOP

¦^´_ 27# Andy2483

«e½ú~§A¬Ý¬Ý³o­Ó,§Ú®M¥Îsamwang«e½ú¤è¦¡¥h¾ã²z¥X¨Ó!
°õ¦æ³t«×«Ü§Ö,¦ý¬O¥Ø«e¹J¨ìªº°ÝÃD,´N¬OÄæ¦ì­ì¥»¬O¤½¦¡·|Åܦ¨­Èªº°ÝÃD,
³o¥i¯à­nµ¥samwang«e½ú¨Ó¸Ñ!

­Ü®w¦X­pTEST2.rar (633.96 KB)

TOP

        ÀR«ä¦Û¦b : Ãø¦æ¯à¦æ¡AÃø±Ë¯à±Ë¡AÃø¬°¯à¬°¡A¤~¯àª@µØ¦Û§Úªº¤H®æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD