sunnyso

回復 39# ML089

好像還是 loop 快

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

複製代碼

ML089

回復 41# sunnyso

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

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

sunnyso

回復 42# ML089

完全同意
另加

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

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

sunnyso

回復  ML089

完全同意
另加

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

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

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

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

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

ML089

回復 43# sunnyso


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

stillfish00

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

ML089

回復 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) 會指派儲存體盡可能將元素存放在一起，或是根據實體硬體界限將它們全部加以調整。同時，陣列需要耗用儲存體以供其控制資訊使用，此消耗量會隨著維度增加而增加。

sunnyso

回復 47# ML089

感謝ML089,

stillfish00

回復 47# ML089
非常詳盡的資訊，謝謝，又學到了不少。

GBKEE

回復 47# ML089
   

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

也可以是這樣的

下載 (46.31 KB)

2013-7-6 05:49