返回列表 上一主題 發帖

[發問] 如何提高多條件加總效率 SUMIFS vs SUMPRODUCT vs VBA

[發問] 如何提高多條件加總效率 SUMIFS vs SUMPRODUCT vs VBA

多條件加總效率 SUMIFS最快,SUMPRODUCT 次之
我寫的VBA最慢。
不知道各位大大能否指點一下,是否有辦法提高VBA的效率。

兩個條件的加總 - Sonny.rar (491.08 KB)
ss

回復 1# sunnyso
請教一個問題:
  1. fSumIFs = "=SUMIFS(原始資料!R2C3:R" & RowsCnt & "C3,原始資料!R2C1:R" & RowsCnt & "C1,總表!RC1,原始資料!R2C5:R" & RowsCnt & "C5, COLUMN(R[-3]C[-1]))"
複製代碼
其中最尾端之 COLUMN(R[-3]C[-1]) 指的是? 它的用意何在?
謝謝你!

TOP

回復 2# c_c_lai

=column(a1)=1 to 12 (月)
ss

TOP

回復  c_c_lai

=column(a1)=1 to 12 (月)
sunnyso 發表於 2013-6-1 16:50

原來如此,萬分感激!

TOP

回復 1# sunnyso
試試這個程式碼,耗時 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.     '  清理舊數據
  13.     '  Sheets("總表").Activate
  14.     Sheets("總表").Range("A3").CurrentRegion.Offset(1, 1).Clear
  15.    
  16.     With Sheets("原始資料")
  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("原始資料").[F3] = "耗時: " & (t2 - t1)
  43. End Sub
複製代碼
04.png
2013-6-1 22:26

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季", "2季", "3季", "4季")
  6. ak = Array("A類", "B類", "C類", "D類", "E類", "F類", "G類", "H類", "I類", "J類")
  7. With Sheets("原始資料")
  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)   '累計
  12.       dic(ar(i, 1) & Int((Month(ar(i, 2)) - 1) / 3) + 1 & "季") = dic(ar(i, 1) & Int((Month(ar(i, 2)) - 1) / 3) + 1 & "季") + 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
複製代碼
學海無涯_不恥下問

TOP

回復 6# Hsieh
感謝超版, 字典法妙. 讓我用時間好好消化一下. 有問題再問. Thanks in advance.

這裡真是個長知識的地方。高手如云。
ss

TOP

回復 1# sunnyso

有意思的比較,我測試結果和你的不同

1.662109375        SUMIF
9.76953125        AutoFilter
4.728515625        VBA CODE
10.07226563        SUMPRODUCT
0.654296875        超版Hsieh
0.3359375        c_c_lai

SUMPRODUCT最慢。公式裡COUNTIF及SUMPRODUCT都是慢郎中函數

.xlsb 開啟時並沒有通知巨集安全的通知,開啟後也可以執行巨集,這是.xlsb 的特性嗎?
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

回復 8# ML089

在另一臺電腦 i3-2120(3.3GHz)+4GRAM, 結果如下
1.301        SUMIFS
3.87        AutoFilter
3.602        VBA CODE
5.023        SUMPRODUCT
0.481        超版Hsieh-字典
10.18        c_c_lai

1.無疑超版的最快
2.SUMIFS次之
3.重點是VBA For Loop 和 SUMPRODUCT、AutoFilter的效率對比 (1樓測試在一臺Netbook上)
好像For Loop 受CPU和RAM的限制較明顯。稍後有時間在把Netbook的測試時間放上來。
4.你的硬體配備如何?各位有時時間提供一下硬體配備和測試時間。看看上面的猜測是否正確。
5. 請問你測試c_c_lai的code是測試多少列的資料(70筆,還是52933筆)
6. xlsb的檔案size明顯比xlsx, xlsm小,速度略略快。 還是會有安全提示。我一般會用xlsb來存檔
ss

TOP

回復 9# sunnyso

這是辦公室電腦 CPU: i5-760 2.8GHz / RAM 4G / WIN7-32 / EXCEL 2007
測試資料52993筆,就是你的檔案我再將超版Hsieh及c_c_lai  VBAC CODE複製進來及執行
1.223         SUMIF
8.074         AutoFilter
4.148         VBA CODE
6.652         SUMPRODUCT
0.574         超版Hsieh
0.418         c_c_lai

c_c_lai 的時間好像差異很大

XLSB開啟有VBA安全性確認(上次在家裡測試沒有出現,)


這是我執行的檔案
兩個條件的加總 - Sonny.rar (505.49 KB)
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

        靜思自在 : 是非當教育,讚美作警惕。
返回列表 上一主題