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

[µo°Ý] ¦p¦ó´£°ª¦h±ø¥ó¥[Á`®Ä²v SUMIFS vs SUMPRODUCT vs VBA

[µo°Ý] ¦p¦ó´£°ª¦h±ø¥ó¥[Á`®Ä²v SUMIFS vs SUMPRODUCT vs VBA

¦h±ø¥ó¥[Á`®Ä²v SUMIFS³Ì§Ö¡ASUMPRODUCT ¦¸¤§
§Ú¼gªºVBA³ÌºC¡C
¤£ª¾¹D¦U¦ì¤j¤j¯à§_«üÂI¤@¤U¡A¬O§_¦³¿ìªk´£°ªVBAªº®Ä²v¡C

¨â­Ó±ø¥óªº¥[Á` - Sonny.rar (491.08 KB)
ss

¦^´_ 1# sunnyso
½Ð±Ð¤@­Ó°ÝÃD¡G
  1. fSumIFs = "=SUMIFS(­ì©l¸ê®Æ!R2C3:R" & RowsCnt & "C3,­ì©l¸ê®Æ!R2C1:R" & RowsCnt & "C1,Á`ªí!RC1,­ì©l¸ê®Æ!R2C5:R" & RowsCnt & "C5, COLUMN(R[-3]C[-1]))"
½Æ»s¥N½X
¨ä¤¤³Ì§ÀºÝ¤§ COLUMN(R[-3]C[-1]) «üªº¬O¡H ¥¦ªº¥Î·N¦ó¦b¡H
ÁÂÁ§A¡I

TOP

¦^´_ 2# c_c_lai

=column(a1)=1 to 12 (¤ë¡^
ss

TOP

¦^´_  c_c_lai

=column(a1)=1 to 12 (¤ë¡^
sunnyso µoªí©ó 2013-6-1 16:50

­ì¨Ó¦p¦¹¡A¸U¤À·P¿E¡I

TOP

¦^´_ 1# sunnyso
¸Õ¸Õ³o­Óµ{¦¡½X¡A¯Ó®É 0.921875
  1. Sub Ex_VBA_Array()          '  VBA Code Array
  2.     Dim RowsCnt As Long, m As Long, SubTotalAr() As Double
  3.     Dim t1 As Variant, t2 As Variant, AllType As Variant
  4.     Dim DataArea As Variant
  5.     Dim i%, j%
  6.    
  7.     t1 = Timer
  8.     AllType = Array("AÃþ", "BÃþ", "CÃþ", "DÃþ", "EÃþ", "FÃþ", "GÃþ", "HÃþ", "IÃþ", "JÃþ")
  9.     ReDim SubTotalAr(0 To UBound(AllType), 0 To 11)
  10.     Application.ScreenUpdating = False
  11.    
  12.     '  ²M²z¼ƾÚ
  13.     '  Sheets("Á`ªí").Activate
  14.     Sheets("Á`ªí").Range("A3").CurrentRegion.Offset(1, 1).Clear
  15.    
  16.     With Sheets("­ì©l¸ê®Æ")
  17.         RowsCnt = .Range("A1").CurrentRegion.Rows.Count
  18.         DataArea = .Range("A2").Resize(RowsCnt, 3)
  19.         
  20.         For m = 1 To UBound(DataArea)
  21.             For i = 0 To UBound(AllType) '  AÃþ To JÃþ
  22.                 If DataArea(m, 1) = AllType(i) Then           ' Jan to Dec
  23.                     SubTotalAr(i, Month(DataArea(m, 2)) - 1) = SubTotalAr(i, Month(DataArea(m, 2)) - 1) + DataArea(m, 3)
  24.                 End If
  25.             Next i
  26.         Next m
  27.     End With
  28.    
  29.     With Sheets("Á`ªí")
  30.         .Range("B4").Resize(UBound(AllType) + 1, 12) = SubTotalAr
  31.         .Range("N4:N13").FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
  32.         For i = 0 To 3
  33.             '  .Range(Chr(79 + i) & 4 & ":" & Chr(79 + i) & 13).FormulaR1C1 = "=SUM(RC[-" & (13 - i * 2) & "]:RC[-" & (11 - i * 2) & "])"
  34.             .Range(Chr(79 + i) & 4).Resize(UBound(AllType) + 1).FormulaR1C1 = "=SUM(RC[-" & (13 - i * 2) & "]:RC[-" & (11 - i * 2) & "])"
  35.             .Range(Chr(79 + i) & 4).Resize(UBound(AllType) + 1) = .Range(Chr(79 + i) & 4).Resize(UBound(AllType) + 1).Value
  36.         Next i
  37.         .Range("B14:R14").FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
  38.     End With
  39.     Application.ScreenUpdating = True
  40.     t2 = Timer
  41.     MsgBox "¯Ó®É" & t2 - t1
  42.     '  Sheets("­ì©l¸ê®Æ").[F3] = "¯Ó®É¡G " & (t2 - t1)
  43. End Sub
½Æ»s¥N½X

TOP

¦^´_ 1# sunnyso
  1. Sub ex()
  2. Dim ap(11, 17), s#, i&, j%, k%, cnt#
  3. s = Timer
  4. Set dic = CreateObject("Scripting.Dictionary") '¤ë
  5. ay = Array("1¤ë", "2¤ë", "3¤ë", "4¤ë", "5¤ë", "6¤ë", "7¤ë", "8¤ë", "9¤ë", "10¤ë", "11¤ë", "12¤ë", "", "1©u", "2©u", "3©u", "4©u")
  6. ak = Array("AÃþ", "BÃþ", "CÃþ", "DÃþ", "EÃþ", "FÃþ", "GÃþ", "HÃþ", "IÃþ", "JÃþ")
  7. With Sheets("­ì©l¸ê®Æ")
  8.    ar = .Range("A1").CurrentRegion.Value
  9.    For i = 2 To UBound(ar, 1)
  10.       dic(ar(i, 1) & Month(ar(i, 2)) & "¤ë") = dic(ar(i, 1) & Month(ar(i, 2)) & "¤ë") + ar(i, 3) '¤ë
  11.       dic(ar(i, 1)) = dic(ar(i, 1)) + ar(i, 3)   '²Ö­p
  12.       dic(ar(i, 1) & Int((Month(ar(i, 2)) - 1) / 3) + 1 & "©u") = dic(ar(i, 1) & Int((Month(ar(i, 2)) - 1) / 3) + 1 & "©u") + ar(i, 3)
  13.    Next
  14. End With
  15. For j = 0 To 16
  16. cnt = 0
  17.    For k = 0 To 9
  18.       ap(k, j) = dic(ak(k) & ay(j))
  19.       cnt = cnt + dic(ak(k) & ay(j))
  20.    Next
  21.     ap(10, j) = cnt
  22. Next
  23. Sheets("Á`ªí").[B4].Resize(11, 17) = ap
  24. MsgBox Timer - s
  25. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 6# Hsieh
·PÁ¶Wª©, ¦r¨åªk§®. Åý§Ú¥Î®É¶¡¦n¦n®ø¤Æ¤@¤U. ¦³°ÝÃD¦A°Ý. Thanks in advance.

³o¸Ì¯u¬O­Óªøª¾ÃѪº¦a¤è¡C°ª¤â¦p¤ª¡C
ss

TOP

¦^´_ 1# sunnyso

¦³·N«äªº¤ñ¸û¡A§Ú´ú¸Õµ²ªG©M§Aªº¤£¦P

1.662109375        SUMIF
9.76953125        AutoFilter
4.728515625        VBA CODE
10.07226563        SUMPRODUCT
0.654296875        ¶Wª©Hsieh
0.3359375        c_c_lai

SUMPRODUCT³ÌºC¡C¤½¦¡¸ÌCOUNTIF¤ÎSUMPRODUCT³£¬OºC­¦¤¤¨ç¼Æ

.xlsb ¶}±Ò®É¨Ã¨S¦³³qª¾¥¨¶°¦w¥þªº³qª¾¡A¶}±Ò«á¤]¥i¥H°õ¦æ¥¨¶°¡A³o¬O.xlsb ªº¯S©Ê¶Ü?
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 8# ML089

¦b¥t¤@»O¹q¸£ i3-2120¡]3.3GHz¡^+4GRAM¡A µ²ªG¦p¤U
1.301        SUMIFS
3.87        AutoFilter
3.602        VBA CODE
5.023        SUMPRODUCT
0.481        ¶Wª©Hsieh-¦r¨å
10.18        c_c_lai

1.µLºÃ¶Wª©ªº³Ì§Ö
2.SUMIFS¦¸¤§
3.­«ÂI¬OVBA For Loop ©M SUMPRODUCT¡BAutoFilterªº®Ä²v¹ï¤ñ ¡]1¼Ó´ú¸Õ¦b¤@»ONetbook¤W¡^
¦n¹³For Loop ¨üCPU©MRAMªº­­¨î¸û©úÅã¡Cµy«á¦³®É¶¡¦b§âNetbookªº´ú¸Õ®É¶¡©ñ¤W¨Ó¡C
4.§AªºµwÅé°t³Æ¦p¦ó¡H¦U¦ì¦³®É®É¶¡´£¨Ñ¤@¤UµwÅé°t³Æ©M´ú¸Õ®É¶¡¡C¬Ý¬Ý¤W­±ªº²q´ú¬O§_¥¿½T¡C
5. ½Ð°Ý§A´ú¸Õc_c_laiªºcode¬O´ú¸Õ¦h¤Ö¦Cªº¸ê®Æ¡]70µ§¡AÁÙ¬O52933µ§¡^
6. xlsbªºÀÉ®×size©úÅã¤ñxlsx¡A xlsm¤p¡A³t«×²¤²¤§Ö¡C ÁÙ¬O·|¦³¦w¥þ´£¥Ü¡C§Ú¤@¯ë·|¥Îxlsb¨Ó¦sÀÉ
ss

TOP

¦^´_ 9# sunnyso

³o¬O¿ì¤½«Ç¹q¸£ CPU: i5-760 2.8GHz / RAM 4G / WIN7-32 / EXCEL 2007
´ú¸Õ¸ê®Æ52993µ§¡A´N¬O§AªºÀɮקڦA±N¶Wª©Hsieh¤Îc_c_lai  VBAC CODE½Æ»s¶i¨Ó¤Î°õ¦æ
1.223         SUMIF
8.074         AutoFilter
4.148         VBA CODE
6.652         SUMPRODUCT
0.574         ¶Wª©Hsieh
0.418         c_c_lai

c_c_lai ªº®É¶¡¦n¹³®t²§«Ü¤j

XLSB¶}±Ò¦³VBA¦w¥þ©Ê½T»{(¤W¦¸¦b®a¸Ì´ú¸Õ¨S¦³¥X²{¡A)


³o¬O§Ú°õ¦æªºÀÉ®×
¨â­Ó±ø¥óªº¥[Á` - Sonny.rar (505.49 KB)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¡i°±º¢¤£«e¡A²×µL©Ò±o¡j¤H³£°g©ó´M§ä©_ÂÝ¡A¦]¦Ó°±º¢¤£«e¡FÁa¨Ï®É¶¡¦A¦h¡B¸ô¦Aªø¡A¤]¤FµL¥Î³B¡A²×µL©Ò±o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD