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

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

¦^´_ 10# ML089

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

TOP

¬Ý§¹1#ªþ¥ó¡A²Ä¤@¤ÏÀ³¬Oı±o²Ä¤TºØ¤èªkªºVBA CODE ¤¤ For¤Uªº¤£¤Ó¦n¡A­n¬°¥L¥­¤Ï !!! À³¸Ó¬O¹³c_c_lai¤j³o¼Ë¤~¹ï~~~¡C
¥t¥~5# c_c_laiªºcode¡A­n¦A°l¨D®Ä²vªº¸Ü¡A¥i¥H¦b23¦æ«á¥[­ÓExit For  ¡AÀ³¸Ó¯à¦A´£¤É¤@¨Ç®Ä²v¡C
©ÎªÌ¥ÎÅܼƦs©ñMonth(...)­pºâ¥X¨Óªº­È(³o­Ó¼vÅT¤ñ¸û¤p..)¡C

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

¦^´_ 13# sunnyso


    ¤£¥u§Ö«Ü¦h¡A0.312¬í¡A¬O³Ì§Öªº
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 13# sunnyso
  1. j = -1
  2. Do
  3. j = j + 1
  4. Loop Until Month(DataArea(m, 2)) = (j + 1)
  5. §ï¬°¤U¦¡
  6. j = Month(DataArea(m, 2)) - 1
  7. ·|§ó§Ö
½Æ»s¥N½X
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

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

¦^´_ 16# sunnyso

A CODE ­×§ï¬° B CODE¡A°õ¦æ®É¶¡¥Ñ 0.257¬íÅܺC¬°1.304¬í¡AÅܱo«ÜºC
¦bVBA¤º¥Î¬¡­¶Ã¯¨ç¼Æ·|¼W¥[«Ü¦h®É¶¡
¤£ª¾VBA¤º¦³µL¨ä¥L¥i¨ú¥NMATCH¨ç¼Æ

' *** A CODE ***
i = -1
Do
i = i + 1
Loop Until DataArea(m, 1) = AllType(i)

' *** B CODE ***
i = Application.Match(DataArea(m, 1), AllType, 0) - 1
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

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 : §ïÅܦۤv¬O¦Û±Ï¡A¼vÅT§O¤H¬O±Ï¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD