ªð¦^¦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

¦^´_ 2# c_c_lai

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

TOP

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

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

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

¦^´_ 10# ML089

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

TOP

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

TOP

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

TOP

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


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

TOP

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

TOP

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

TOP

        ÀR«ä¦Û¦b : ¥ÌÄ@°µ¡BÅw³ß¨ü¡C
ªð¦^¦Cªí ¤W¤@¥DÃD