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

sunnyso

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

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

下載次數: 53

2013-6-1 04:42

c_c_lai

回復 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]) 指的是？ 它的用意何在？
謝謝你！

sunnyso

回復 2# c_c_lai

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

c_c_lai

回復  c_c_lai

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

原來如此，萬分感激！

c_c_lai

回復 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

複製代碼

下載 (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季", "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

複製代碼

sunnyso

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

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

ML089

回復 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 的特性嗎?

sunnyso

回復 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來存檔

ML089

回復 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)

下載次數: 31

2013-6-4 12:42