123456
返回列表 上一主題 發帖

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

ML089

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
31# 跳轉到 »
發表於 2013-6-6 23:33 | 只看該作者
回復 28# sunnyso

Scripting.Dictionary 沒有直接用迴圈比較來的快
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
32#
發表於 2013-6-6 23:39 | 只看該作者
回復 30# sunnyso

感謝!受教
你的解釋我懂了
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

sunnyso

帖子
605 
主題
92 
精華
0 
積分
648 
點名
0  
作業系統
windows 
軟體版本
7 
閱讀權限
50 
性別
男 
來自
macau 
註冊時間
2013-4-5 
最後登錄
2019-2-10 

熱烈參與勳章好學求知勳章
33#
發表於 2013-6-6 23:45 | 只看該作者
本帖最後由 sunnyso 於 2013-6-7 00:00 編輯

回復 31# ML089

會比Match快嗎?

另外,在Netbook（N470 1.83GHz)上字典法快過迥圈,
ss

TOP

c_c_lai

帖子
2035 
主題
24 
精華
0 
積分
2031 
點名
0  
作業系統
Win7 
軟體版本
Office2010 
閱讀權限
100 
性別
男 
註冊時間
2012-3-22 
最後登錄
2024-2-1 
34#
發表於 2013-6-7 06:17 | 只看該作者
看完1#附件，第一反應是覺得第三種方法的VBA CODE 中 For下的不太好，要為他平反 !!! 應該是像c_c_lai大這樣 ...
stillfish00 發表於 2013-6-4 20:05

謝謝指導！
Exit Sub 適度的置入，的確能避掉不必要的浪費迴圈，
用變數來取代確實也有些許的助益，如果 Month()
使用頻率大的話，那就會有很大地效率提升了。
再次向你說聲謝謝。

TOP

c_c_lai

帖子
2035 
主題
24 
精華
0 
積分
2031 
點名
0  
作業系統
Win7 
軟體版本
Office2010 
閱讀權限
100 
性別
男 
註冊時間
2012-3-22 
最後登錄
2024-2-1 
35#
發表於 2013-6-7 06:25 | 只看該作者
回復  sunnyso
ML089 發表於 2013-6-5 21:38

如此修改後確實有不錯效益提升。
多角度的思維亦能考驗與增進我們思考的能力。
謝謝你。

TOP

ML089

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
36#
發表於 2013-6-7 09:00 | 只看該作者
回復 33# sunnyso


在PC上i5 2.8GHz RAM-4G上速度快慢比較
迴圈比較 > 字典查詢 > MATCH查詢

不同硬體有不同的效果，甚至第二次執行都比第一次執行快，但大致方向可以藉由這次體驗獲得一些寶貴經驗。
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

sunnyso

帖子
605 
主題
92 
精華
0 
積分
648 
點名
0  
作業系統
windows 
軟體版本
7 
閱讀權限
50 
性別
男 
來自
macau 
註冊時間
2013-4-5 
最後登錄
2019-2-10 

熱烈參與勳章好學求知勳章
37#
發表於 2013-6-7 10:13 | 只看該作者
回復 36# ML089

第二次執行因爲有Cache所以會比較快， 用下面的code 可以在計算結束後清除Cache， but only for Excel 2007 and above

added a Class Module called AppEvents with code like this:
  1. Option Explicit
  2. Private WithEvents App As Application
  3. Private Sub Class_Initialize()
  4. Set App = Application
  5. End Sub
  6. Private Sub App_AfterCalculate()
  7. ClearCache
  8. End Sub
複製代碼
Only Excel 2007 and later have the AfterCalculate event which will be used to empty the cache after each calculate
This code is ignored in Excel 2003 and earlier: since the AfterCalculate event does not exist it never gets called but still compiles OK.
ss

TOP

sunnyso

帖子
605 
主題
92 
精華
0 
積分
648 
點名
0  
作業系統
windows 
軟體版本
7 
閱讀權限
50 
性別
男 
來自
macau 
註冊時間
2013-4-5 
最後登錄
2019-2-10 

熱烈參與勳章好學求知勳章
38#
發表於 2013-6-30 22:46 | 只看該作者
回復 18# ML089

回復  sunnyso
A CODE 修改為 B CODE，執行時間由 0.257秒變慢為1.304秒，變得很慢
在VBA內用活頁簿函數會增加很多時間
不知VBA內有無其他可取代MATCH函數
ML089 發表於 2013-6-6 09:29


不用迴圈，不用Worksheet函數（match等），判斷某element在陣列中位置
例如
  1. Sub ss_test()
  2.     alltype = Array("A類", "B類", "C類", "D類", "E類", "F類", "G類", "H類", "I類", "J類")
  3.     aj = "D類"
  4.     Debug.Print UBound(Split(Split(Join(alltype, "|"), aj)(0), "|")) + 1
  5.    
  6.     Stop
  7. End Sub
複製代碼
ss

TOP

ML089

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
39#
發表於 2013-6-30 23:41 | 只看該作者
回復 38# sunnyso


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

TOP

sunnyso

帖子
605 
主題
92 
精華
0 
積分
648 
點名
0  
作業系統
windows 
軟體版本
7 
閱讀權限
50 
性別
男 
來自
macau 
註冊時間
2013-4-5 
最後登錄
2019-2-10 

熱烈參與勳章好學求知勳章
40#
發表於 2013-6-30 23:46 | 只看該作者
回復 39# ML089

有bug， 改爲
  1. Sub ss_test()
  2.     alltype = Array("A類", "B類", "C類", "D類", "E類", "F類", "G類", "H類", "I類", "J類")
  3.     aj = "D類"
  4.     Debug.Print UBound(Split(Split(“ ” & Join(alltype, "|"), aj)(0), "|"))
  5.    
  6.     Stop
  7. End Sub
複製代碼
ss

TOP

        靜思自在 : 要用心，不要操心、煩心。
123456
返回列表 上一主題