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

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

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

11^# 跳轉到 »

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

回復 33# sunnyso

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

不同硬體有不同的效果，甚至第二次執行都比第一次執行快，但大致方向可以藉由這次體驗獲得一些寶貴經驗。

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

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

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

12^#

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

回復 38# sunnyso

好想法

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

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

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

13^#

發表於 2013-7-1 09:51 | 顯示全部帖子

回復 41# sunnyso

我測試結果也是一樣
UBound(Split(Split( 用了3個函數，Split( 這種文字函數應該也比較慢

這例子學習到：
EXCEL 函數比 VBA函數慢很多
文字函數比數字函數慢
減少迴圈適時跳出
減少函數使用量

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

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

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

14^#

發表於 2013-7-5 12:23 | 顯示全部帖子

回復 43# sunnyso

VBA將資料一次讀入陣列再做計算比較快，不知道陣列大小有無限制。
如果資料量很大超過陣列限制時有無處理方式。
目前我想將9個文字檔(每個檔案約3~5萬行，每行80字)同時直接讀入陣列再來處理，請教這種方式是否可行。

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

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

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

15^#

發表於 2013-7-5 17:08 | 顯示全部帖子

回復 46# stillfish00

謝謝回覆

查詢下列網頁
http://msdn.microsoft.com/zh-tw/library/b388cb5s(v=vs.80).aspx

裡面說到，每個陣列之維度長度都受限於 Integer 資料型別的最大值，也就是 (2 ^ 31) - 1，然而，陣列之總大小也同時受限於系統可用的記憶體。
所以說可宣告數量應該不是問題，可是受限於系統可用的記憶體。
後續處理時真的遇到問題再來請教大家。

------------------------------------------------------------------------
Visual Basic 中的陣列大小

Visual Studio 2005 其他版本本主題尚未接受評分 - 為這個主題評分
陣列大小為其所有維度 (Dimension) 長度之乘積。它代表目前包含於陣列中的元素總數。
下列範例宣告一個三維陣列︰
Dim prices(3, 4, 5) As Long
變數 prices 陣列的整體大小為 (3 + 1) x (4 + 1) x (5 + 1) = 120。

陣列大小考慮事項
處理陣列大小時，請注意幾點︰
維度長度
每個維度的索引都以 0 為起點，也就是它的範圍是由 0 到它的上限 (Upper Bound)。因此，指定維度的長度會比該維度的宣告上限多 1。

長度限制
每個陣列之維度長度都受限於 Integer 資料型別的最大值，也就是 (2 ^ 31) - 1。然而，陣列之總大小也同時受限於系統可用的記憶體。若您試圖對總大小超過可用的 RAM 之陣列進行初始化，Common Language Runtime 將擲回 OutOfMemoryException 例外狀況。

大小及元素大小
陣列大小與其元素的資料型別無關。大小永遠是指元素的總數，而不是它們於儲存體中所佔的位元組。
記憶體消耗量
對陣列在記憶體中的儲存方式做任何假設都是不安全的。儲存體會因不同資料寬度的平台而有差異，所以相同陣列於 64 位元系統上所佔記憶體將較 32 位元系統來的多。當您初始化陣列時，隨著系統組態不同，Common Language Runtime (CLR) 會指派儲存體盡可能將元素存放在一起，或是根據實體硬體界限將它們全部加以調整。同時，陣列需要耗用儲存體以供其控制資訊使用，此消耗量會隨著維度增加而增加。

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

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

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

16^#

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

回復 50# GBKEE

謝謝補充說明
陣列的索引上下指標也可以自訂

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

TOP

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

[收藏此主題] [關注此主題的新回復]

[通過 QQ、MSN 分享給朋友]