如何提高多條件加總效率 SUMIFS vs SUMPRODUCT vs VBA - Excel程式區

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

2^#

發表於 2013-6-4 12:42 | 顯示全部帖子

回復 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 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

3^#

發表於 2013-6-5 21:15 | 顯示全部帖子

回復 13# sunnyso

不只快很多，0.312秒，是最快的

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

4^#

發表於 2013-6-5 21:38 | 顯示全部帖子

回復 13# sunnyso

j = -1
Do
j = j + 1
Loop Until Month(DataArea(m, 2)) = (j + 1)
改為下式
j = Month(DataArea(m, 2)) - 1
會更快

複製代碼

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

5^#

發表於 2013-6-6 09:29 | 顯示全部帖子

回復 16# sunnyso

A CODE 修改為 B CODE，執行時間由 0.257秒變慢為1.304秒，變得很慢
在VBA內用活頁簿函數會增加很多時間
不知VBA內有無其他可取代MATCH函數

' *** 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

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

6^#

發表於 2013-6-6 15:10 | 顯示全部帖子

回復 22# sunnyso

以前都是直接利用儲存格運算存取，經過這次比較才了解不同的方式差異那麼大，真得滿有收穫的。
所以要做查詢運算還是要全部先讀至變數陣列，全部計算好了再將變數陣列一次性指向給儲存格。

執行時間差很多，執行test2後會死當6分鐘，剛好可以喝一杯咖啡。
test1 1.4秒
test2 299.3秒

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

7^#

發表於 2013-6-6 16:03 | 顯示全部帖子

回復 22# sunnyso

如何在Sheet3執行巨集test1，資料寫至Sheet1
我加了紅色字結果執行錯誤，幫忙看看錯在哪裡，
在Sheet1執行巨集test1就正確

Sub test1()
Dim i%, j%
Dim Ar(1000, 1000) As Double
For i = 0 To 1000
      For j = 0 To 1000
         Ar(i, j) = i * 4 + j
      Next j
Next i
Sheets("Sheet1").Range(Cells(1, 1), Cells(1001, 1001)) = Ar
End Sub

VBA 變數陣列與儲存格計算批次讀寫比較.rar (9.47 KB)

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

8^#

發表於 2013-6-6 23:10 | 顯示全部帖子

回復 27# sunnyso

各式的執行時間並無太大差異
Range(Cells(1,1),Cells(1001,1001)) 應該是等於 Range("A1:ALL1000")，可是不能執行無法理解，只好記下來。

下式執行錯誤
Sheets("Sheet1").Range(Cells(1,1),Cells(1001,1001))=Ar

下式執行OK-1.5秒
WithSheets("Sheet1")
.Range(.Cells(1,1),.Cells(1000,1000))=Ar
EndWith

下式執行OK-1.5秒
Sheets("Sheet1").Range("A1").Resize(1000,1000)=Ar

下式執行OK-1.5秒
Sheet1.[A1].Resize(1000,1000)=Ar

下式執行OK-1.5秒
Sheet1.[A1:ALL1000]=Ar

下式執行OK-1.5秒
Sheet1.Range("A1:ALL1000")=Ar

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式