[µ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

sunnyso

¦^´_ 2# c_c_lai

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

sunnyso

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

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

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ÀÉ

sunnyso

¦^´_ 10# ML089

¬Ý¨ÓCPUªºcache¹ïª«¥óªºÅª¨ú®Ä²v¦³«Ü¤j¼vÅT

sunnyso

¦^´_ 12# stillfish00

Stillfish00¤j¤jªº½T¼F®`¡A¨º­ÓcodeªGµM¦³°ÝÃD¡A§ï¤F¤@¤U¡]§Ö¤F«Ü¦h¡^¡A½Ð«ü±Ð

1. Sub sonny3_v2()
   
2.     t1 = Timer
   
3.     Dim RowsCnt, m, SubTotalAr() As Double
   
4.     Dim DataArea As Variant
   
5.     Dim i%, j%
   
6.     AllType = Array("AÃþ", "BÃþ", "CÃþ", "DÃþ", "EÃþ", "FÃþ", "GÃþ", "HÃþ", "IÃþ", "JÃþ")
   
7.     ReDim SubTotalAr(0 To UBound(AllType), 0 To 11)
   
8.     Application.ScreenUpdating = False
   
9.     With Sheets("­ì©l¸ê®Æ")
   
10.         RowsCnt = .Range("A1").CurrentRegion.Rows.Count
    
11.         DataArea = .Range("A2").Resize(RowsCnt - 1, 3)
    
12.         For m = 1 To UBound(DataArea)
    
13.             i = -1
    
14.             Do
    
15.                 i = i + 1
    
16.             Loop Until DataArea(m, 1) = AllType(i)
    
17.             j = -1
    
18.             Do
    
19.                 j = j + 1
    
20.             Loop Until Month(DataArea(m, 2)) = (j + 1)
    
21.             SubTotalAr(i, j) = SubTotalAr(i, j) + DataArea(m, 3)
    
22.         Next m
    
23.     End With
    
24.    
    
25.     With Sheets("Á`ªí")
    
26.         .Range("B4").Resize(UBound(AllType) + 1, 12) = SubTotalAr
    
27.         .Range("B14:R14").FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
    
28.         .Range("N4:N13").FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
    
29.         .Range("O4:R13").FormulaR1C1 = "=SUM(RC[-13]:RC[-11])"
    
30.     End With
    
31.     Application.ScreenUpdating = True
    
32.     t2 = Timer
    
33.     MsgBox "¯Ó®É" & t2 - t1
    
34. End Sub

½Æ»s¥N½X

sunnyso

¦^´_ 15# ML089

ªº½T·|§ó§Ö

¦ý¬O¥u¯à¥Î¦b³oºØ¯S®í±¡ªp, ¨Ò¦p±ø¥ó¤G¬°¦a°Ï¡]¤¤³¡,ªF¥_³¡,«n³¡...¡^´N¤£¾A¥Î

p.s. ³oùØ·Q°Q½×¤ñ¸û³q¥Îªº±ø¥ó¥[Á`¥[³tªk
³oùتº¸ê®Æ¬O°Ñ·Ó½×¾ÂùØ¥t¤@­Ó¶K½s¥X¨Óªº, ·íªì¨S¦³¦Ò¼{¨ì¥¦ªº¯S®í©Ê¡C

sunnyso

¦^´_  sunnyso
ML089 µoªí©ó 2013-6-5 21:38

·íµM³]­pµ{§Ç®É¥R¤À§Q¥Î°ÝÃDªº¯S©Êµ´¹ï¬O¥¿½Tªº¡C

sunnyso

¦^´_ 18# ML089
Netbook  N470 (1.83GHz) + 2GRAM ´ú¸Õµ²ªG

4.234375        SUMIFS
21.234375        AutoFilter
26.953125        VBA Code
18.203125        SUMPRODUCT
1.078125        ¶Wª©Hsieh
1.390625        New VBA Code

sunnyso

¦^´_ 15# ML089

µ²¦XML089ªº¤èªk¡A§Q¥Î­ì©l¸ê®Æªº¯S©Ê¤U­±ªºCODE§ó§Ö

1. Sub sonny3_v3()
   
2.     t1 = Timer
   
3.     Dim RowsCnt, m, SubTotalAr() As Double
   
4.     Dim DataArea As Variant
   
5.     Dim i%, j%
   
6.     AllTypeStr = "AÃþBÃþCÃþDÃþEÃþFÃþGÃþHÃþIÃþJÃþ"
   
7.     ReDim SubTotalAr(0 To UBound(AllType), 0 To 11)
   
8.     Application.ScreenUpdating = False
   
9.     With Sheets("­ì©l¸ê®Æ")
   
10.         RowsCnt = .Range("A1").CurrentRegion.Rows.Count
    
11.         DataArea = .Range("A2").Resize(RowsCnt - 1, 3)
    
12.         For m = 1 To UBound(DataArea)
    
13.             i = (InStr(AllTypeStr, "DÃþ") + 1) / 2
    
14.             j = Month(DataArea(m, 2)) - 1
    
15.             SubTotalAr(i, j) = SubTotalAr(i, j) + DataArea(m, 3)
    
16.         Next m
    
17.     End With
    
18.    
    
19.     With Sheets("Á`ªí")
    
20.         .Range("B4").Resize(UBound(AllType) + 1, 12) = SubTotalAr
    
21.         .Range("B14:R14").FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
    
22.         .Range("N4:N13").FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
    
23.         .Range("O4:R13").FormulaR1C1 = "=SUM(RC[-13]:RC[-11])"
    
24.     End With
    
25.     Application.ScreenUpdating = True
    
26.     t2 = Timer
    
27.     MsgBox "¯Ó®É" & t2 - t1
    
28. End Sub

½Æ»s¥N½X