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

sunnyso

高中生 Rank: 3 Rank: 3

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

熱烈參與勳章好學求知勳章

41^# 跳轉到 »

發表於 2013-6-30 23:59 | 只看該作者

回復 39# ML089

好像還是 loop 快

Sub sonny3_v3() 'VBA split+join
t1 = Timer
Dim RowsCnt, m, SubTotalAr() As Double
Dim DataArea As Variant
Dim i%, j%
AllType = " A類|B類|C類|D類|E類|F類|G類|H類|I類|J類"
ReDim SubTotalAr(0 To 9, 0 To 11)
Application.ScreenUpdating = False
With Sheets("原始資料")
RowsCnt = .Range("A1").CurrentRegion.Rows.Count
DataArea = .Range("A2").Resize(RowsCnt - 1, 3)
For m = 1 To UBound(DataArea)
i = UBound(Split(Split(AllType, DataArea(m, 1))(0), "|"))
j = -1
Do
j = j + 1
Loop Until Month(DataArea(m, 2)) = (j + 1)
SubTotalAr(i, j) = SubTotalAr(i, j) + DataArea(m, 3)
Next m
End With
With Sheets("總表")
.Range("B4").Resize(9 + 1, 12) = SubTotalAr
.Range("B14:R14").FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
.Range("N4:N13").FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
.Range("O4:R13").FormulaR1C1 = "=SUM(RC[-13]:RC[-11])"
End With
Application.ScreenUpdating = True
t2 = Timer
MsgBox "耗時" & t2 - t1
End Sub

複製代碼

ss

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猴年全勤獎章

42^#

發表於 2013-7-1 09:51 | 只看該作者

回復 41# sunnyso

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

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

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

TOP

sunnyso

高中生 Rank: 3 Rank: 3

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

熱烈參與勳章好學求知勳章

43^#

發表於 2013-7-1 09:53 | 只看該作者

本帖最後由 sunnyso 於 2013-7-1 09:56 編輯

回復 42# ML089

完全同意
另加

VBA用陣列，減少讀寫worksheet次數

Worksheet 減少用陣列公式（ctrl+shift+Enter)

ss

TOP

sunnyso

高中生 Rank: 3 Rank: 3

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

熱烈參與勳章好學求知勳章

44^#

發表於 2013-7-1 10:14 | 只看該作者

本帖最後由 sunnyso 於 2013-7-1 10:24 編輯

回復 ML089

完全同意
另加

VBA用陣列，減少讀寫worksheet次數

Worksheet 減少用陣列公式（ctrl+shift+Enter)

sunnyso 發表於 2013-7-1 09:53

不要歧視輔助欄，因爲陣列公式比輔助欄慢

陣列公式適合小型計算，（和學習練功）

ss

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猴年全勤獎章

45^#

發表於 2013-7-5 12:23 | 只看該作者

回復 43# sunnyso

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

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

TOP

stillfish00

大學生 Rank: 4

帖子: 1018
主題: 15
精華: 0
積分: 1058
點名: 0
作業系統: win7 32bit
軟體版本: Office 2016 64-bit
閱讀權限: 50
性別: 男
來自: 桃園
註冊時間: 2012-5-9
最後登錄: 2022-9-28

46^#

發表於 2013-7-5 13:43 | 只看該作者

回復 45# ML089
限制應該就是記憶體大小吧，把資料讀到陣列理論上就是讀到記憶體內，用stop中斷並觀察工作管理員>處理程序>Excel.exe所佔的記憶體大小可以得知。但記憶體又分階層:快取、實體、虛擬，如果讀入資料過大而使用到虛擬記憶體(其實就是硬碟分頁檔)，速度就沒原本那麼快了。

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猴年全勤獎章

47^#

發表於 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

sunnyso