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

sunnyso

¦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)

¤U¸ü¦¸¼Æ: 53

2013-6-1 04:42

c_c_lai

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

sunnyso

¦^´_ 2# c_c_lai

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

c_c_lai

¦^´_  c_c_lai

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

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

c_c_lai

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

¤U¸ü (55.66 KB)

2013-6-1 22:26

Hsieh

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

sunnyso

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

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

ML089

¦^´_ 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©Ê¶Ü?

sunnyso

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

ML089

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

¤U¸ü¦¸¼Æ: 31

2013-6-4 12:42