查看完整版本 : excel VBA 用CELL運算,真係好慢!好慢!

QQL0102 2019-2-17 09:55 PM

excel VBA 用CELL運算,真係好慢!好慢!

只是用兩次for loop, 類似下面咁, 用左成10分鐘都仍然計緊:smile_42: :smile_42:

(Remark: 係Virtual winxp 用單核計)



For i = 1 To 10000
    Count = 1
    For c = i + 1 To 10000
        If Sheets("x").Cells(i, 2) = Sheets("x").Cells(c, 2) Then
            Count = Count + 1
            If Sheets("x").Cells(c, 6) = "" Then Sheets("x").Cells(c, 6) = Count
        End If
    Next c
Next i

[[i] 本帖最後由 QQL0102 於 2019-2-18 09:18 AM 編輯 [/i]]

alee001 2019-2-17 10:21 PM

總數行10000x10000次當每0.001秒/次,10min算快...

QQL0102 2019-2-17 10:36 PM

[quote]原帖由 [i]alee001[/i] 於 2019-2-17 10:21 PM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=495012476&ptid=28041053][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
總數行10000x10000次當每0.001秒/次,10min算快... [/quote]

最後改用array 去運算, 1分鐘內計好,用cell 真係好慢

煙民母親生賤種 2019-2-18 01:35 AM

[quote]原帖由 [i]QQL0102[/i] 於 2019-2-17 09:55 PM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=495011307&ptid=28041053][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
只是用兩次for loop, 類似下面咁, 用左成10分鐘都仍然計緊:smile_42: :smile_42:

(Remark: 係Virtual winxp 用單核計)



For i = 1 To 10000
    ...................
    For c = i + 1 To 10000
        If Sheets("x").Cells(i, ... [/quote]excel vba 本身是單線程運作。話之你24核,都係無分別。當然,OS要用最少1核來做野,會唔會OS本身做背景野而塞住左就不得而知。如果你有 libre office calc,試下 paste d code 去試下,應該會快好多。:fst_011:

QQL0102 2019-2-18 09:18 AM

[quote]原帖由 [i]煙民母親生賤種[/i] 於 2019-2-18 01:35 AM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=495020344&ptid=28041053][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
excel vba 本身是單線程運作。話之你24核,都係無分別。當然,OS要用最少1核來做野,會唔會OS本身做背景野而塞住左就不得而知。如果你有 libre office calc,試下 paste d code 去試下,應該會快好多。:fst_011: ... [/quote]
唔識用libre office calc,  一放D CODE入邊RUN,就出ERROR, EXCEL好似都可以多緒執行



REM  *****  BASIC  *****
Sub Main

For i = 1 To 10000
    Count = 1
    For c = i + 1 To 10000
        If Sheets("x").Cells(i, 2) = Sheets("x").Cells(c, 2) Then
            Count = Count + 1
            If Sheets("x").Cells(c, 6) = "" Then Sheets("x").Cells(c, 6) = Count
        End If
    Next c
Next i
End Sub

alee001 2019-2-18 11:35 AM

[quote]原帖由 [i]QQL0102[/i] 於 2019-2-18 09:18 AM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=495026204&ptid=28041053][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]

唔識用libre office calc,  一放D CODE入邊RUN,就出ERROR, EXCEL好似都可以多緒執行



REM  *****  BASIC  *****
Sub Main

For i = 1 To 10000
    Count = 1
    For c = i + 1 To 10000
        If Sheets("x").Cells( ... [/quote]
試吓唔選該項目睇吓差幾遠?

QQL0102 2019-2-18 01:51 PM

[quote]原帖由 [i]alee001[/i] 於 2019-2-18 11:35 AM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=495032381&ptid=28041053][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]

試吓唔選該項目睇吓差幾遠? [/quote]

我得vm winxp(單核) 有OFFICE,如果唔選,結果都應該一樣

alee001 2019-2-18 02:05 PM

[quote]原帖由 [i]QQL0102[/i] 於 2019-2-18 01:51 PM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=495037780&ptid=28041053][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]


我得vm winxp(單核) 有OFFICE,如果唔選,結果都應該一樣 [/quote]

都估到應該冇乜分別。
可否貼上改良後行array嘅code作對比?

QQL0102 2019-2-18 05:03 PM

其實D CODE 幾乎一樣,只不過將Sheets("x").Cells(i, 2) 轉晒做 arr (i,2) [array 的語法]

煙民母親生賤種 2019-2-19 12:19 AM

[quote]原帖由 [i]QQL0102[/i] 於 2019-2-18 09:18 AM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=495026204&ptid=28041053][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]

唔識用libre office calc,  一放D CODE入邊RUN,就出ERROR, EXCEL好似都可以多緒執行



REM  *****  BASIC  *****
Sub Main

For i = 1 To 10000
    Count = 1
    For c = i + 1 To 10000
        If Sheets("x").Cells( ... [/quote]你要加句 vbasupport 先得。

不過我試過 calc 都係唔快。2分鐘先 loop 32 次。應該都係要 access 大量物件。完全唔可以用 array 比。因為每個 sheet , cell 的物件開銷都好大。就算我 screenupdate false 無乜幫助,因此應該唔關顯示事。而係存取大量 cell 出問題。:fst_016:

[attach]9344119[/attach]

[[i] 本帖最後由 煙民母親生賤種 於 2019-2-19 12:21 AM 編輯 [/i]]

QQL0102 2019-2-19 07:47 AM

[quote]原帖由 [i]煙民母親生賤種[/i] 於 2019-2-19 12:19 AM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=495065589&ptid=28041053][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
你要加句 vbasupport 先得。

不過我試過 calc 都係唔快。2分鐘先 loop 32 次。應該都係要 access 大量物件。完全唔可以用 array 比。因為每個 sheet , cell 的物件開銷都好大。就算我 screenupdate false 無乜幫助,因此應該唔關顯示事。而係存取大量 cell 出問題。:fst_016:

9344119 ... [/quote]
我部機比你慢好多,2分鐘18次:funk::smile_42::smile_39:,最後總結,用array 計算DATA先是王道,市場上所有VBA提速方法,都沒咩用eg.screenupdate/with end with/。不過咁,換部頂級電腦,可能快好多

[[i] 本帖最後由 QQL0102 於 2019-2-19 07:49 AM 編輯 [/i]]

QQL0102 2019-2-19 08:28 AM

[quote]原帖由 [i]煙民母親生賤種[/i] 於 2019-2-19 12:19 AM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=495065589&ptid=28041053][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
你要加句 vbasupport 先得。

不過我試過 calc 都係唔快。2分鐘先 loop 32 次。應該都係要 access 大量物件。完全唔可以用 array 比。因為每個 sheet , cell 的物件開銷都好大。就算我 screenupdate false 無乜幫助,因此應該唔關顯示事。而係存取大量 cell 出問題。:fst_016:

9344119 ... [/quote]
唔知係唔係set 錯野,係單春Virtual box winxp office 2007, 2分鐘去到721次,比雙春ubuntu libre office 快好多好多:smile_41::smile_41:

alee001 2019-2-19 12:48 PM

行雙loop係慢啲,我i7 2600行晒都要8分鐘,或者array之外可以試吓其他方法...如do
[attach]9345374[/attach]


以下解釋用array同cell嘅分別:
[url=http://www.cpearson.com/excel/cells.htm]http://www.cpearson.com/excel/cells.htm[/url]

[[i] 本帖最後由 alee001 於 2019-2-19 01:18 PM 編輯 [/i]]

QQL0102 2019-2-19 04:34 PM

[quote]原帖由 [i]alee001[/i] 於 2019-2-19 12:48 PM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=495083118&ptid=28041053][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
行雙loop係慢啲,我i7 2600行晒都要8分鐘,或者array之外可以試吓其他方法...如do
9345374


以下解釋用array同cell嘅分別:
[url=http://www.cpearson.com/excel/cells.htm]http://www.cpearson.com/excel/cells.htm[/url] [/quote]

你意思指將 2個for loop 改成2個 do loop?  都係咁慢

alee001 2019-2-19 09:25 PM

最快都要2分鐘...
[attach]9347190[/attach]

QQL0102 2019-2-19 10:16 PM

[quote]原帖由 [i]alee001[/i] 於 2019-2-19 09:25 PM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=495107442&ptid=28041053][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
最快都要2分鐘...
9347190 [/quote]
呢位仁兄,而家的方法好似只係loop 1次,總共10000次; 我之前方法,係loop 2次,總共1億次;我覺得2個方法的意思好似有D唔同

我而loop 2次,其實係搵同一Col 入邊, 相同種類的row 數目,好似下面咁(有2個11, 2個23)

11   
23   
11    2
22   
23    2
21

alee001 2019-2-19 10:34 PM

你冇例子我唔會你意思。

QQL0102 2019-2-19 11:10 PM

[quote]原帖由 [i]alee001[/i] 於 2019-2-19 10:34 PM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=495110306&ptid=28041053][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]
你冇例子我唔會你意思。 [/quote]

其實d code就是搵同一col 相同資料的數目,因為吾係呢個位kick 住,所以沒將例子講埋出黎。 最大問題是cell 運算效率同速度,所以只講code 果部分。

11    
23    
11    2
22    
23    2
21

alee001 2019-2-19 11:53 PM

你有例子就可以試吓以下方法:
[attach]9347911[/attach]

煙民母親生賤種 2019-2-20 02:03 AM

[quote]原帖由 [i]QQL0102[/i] 於 2019-2-19 11:10 PM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=495112169&ptid=28041053][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]


其實d code就是搵同一col 相同資料的數目,因為吾係呢個位kick 住,所以沒將例子講埋出黎。 最大問題是cell 運算效率同速度,所以只講code 果部分。

11   
23   
11    2
22   
23    2
21 ... [/quote]如果係呢個目的,你先前既曲係會出個錯的結果。因為你 count 既時候,無同時 remove 左相同既數目。導致有 over count 既情況,例如:
5
6
12
5
6
9
5
18

本來只有3個5。但因為你無 remove 到 count 左既 5,當數到位置第4時,變左 3 + 2, 當數到第 7 時,變左 3 + 2 +1 , 其實 2 同 1 係 over count 。

idontknowjack 2019-2-20 09:07 AM

鄙人明白各位前輩說的是 VBA loop 的速度,我撘訕一下⋯
從把一批 cell 的 value 加起來的目的來說,會不會在 code 內用 application object 來 invoke Excel 自己的 SUM spreadsheet function 最快?
我沒有 Excel, 估計而已。

alee001 2019-2-20 10:10 AM

[quote]原帖由 [i]煙民母親生賤種[/i] 於 2019-2-20 02:03 AM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=495118145&ptid=28041053][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
如果係呢個目的,你先前既曲係會出個錯的結果。因為你 count 既時候,無同時 remove 左相同既數目。導致有 over count 既情況,例如:
5
6
12
5
6
9
5
18

本來只有3個5。但因為你無 remove 到 count 左既 5,當數到位置第4時,變左 3 + 2, 當數到第 7 時,變左 3 + 2 +1 , 其實 2 同 1 係 over co ... [/quote]


所以樓主嘅本身code只係check每行至今該data現有的總數(即omit以住結果)。

其實如果唔厭公式多,簡單啲用10000個countif都得啦...

QQL0102 2019-2-20 11:38 PM

[quote]原帖由 [i]煙民母親生賤種[/i] 於 2019-2-20 02:03 AM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=495118145&ptid=28041053][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
如果係呢個目的,你先前既曲係會出個錯的結果。因為你 count 既時候,無同時 remove 左相同既數目。導致有 over count 既情況,例如:
5
6
12
5
6
9
5
18

本來只有3個5。但因為你無 remove 到 count 左既 5,當數到位置第4時,變左 3 + 2, 當數到第 7 時,變左 3 + 2 +1 , 其實 2 同 1 係 over co ... [/quote]
現在的CODE用你的例子,好似都出到我想要既野,最last 的5計到係3(total 有3個5), 最last 的6計到係2(total 有2個6); 第2個5出個2 (計到第2個5的時間,就共有2個5),我覺得好似沒問題。
(可能我表達得唔清楚,令你誤解我的意思; 其實,我係想計算出,每一行ROW,由第一行ROW到那一行ROW,重覆資料的數目,行到第4行的時間,計到有2個5,行到第7行時間,計到有3個5,即是上邊我講的意思)

[attach]9352172[/attach]

QQL0102 2019-2-20 11:40 PM

[quote]原帖由 [i]alee001[/i] 於 2019-2-20 10:10 AM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=495126500&ptid=28041053][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]



所以樓主嘅本身code只係check每行至今該data現有的總數(即omit以住結果)。

其實如果唔厭公式多,簡單啲用10000個countif都得啦... [/quote]
我都有諗過用countif, 不過唔太想一部份用VBA, 一部份用公式,咁樣可能更慢,而且D公式,一個唔覺意delete左可能唔知道

煙民母親生賤種 2019-2-21 03:17 AM

vba 係幾好玩既。又方便,功能又實用又好 玩。:fst_011:
可惜到現在,本身都係唔支援多線程。除非外接  declare dll

愛國愛港007 2019-2-21 07:38 AM

[quote]原帖由 [i]QQL0102[/i] 於 2019-2-19 11:10 PM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=495112169&ptid=28041053][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]


其實d code就是搵同一col 相同資料的數目,因為吾係呢個位kick 住,所以沒將例子講埋出黎。 最大問題是cell 運算效率同速度,所以只講code 果部分。

11   
23   
11    2
22   
23    2
21 ... [/quote]

如果唔係行行有野, 會不會將10000改做最後的column/row? 起碼唔駛loop 10000^2

QQL0102 2019-2-21 08:07 AM

[quote]原帖由 [i]煙民母親生賤種[/i] 於 2019-2-21 03:17 AM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=495169407&ptid=28041053][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]
vba 係幾好玩既。又方便,功能又實用又好 玩。:fst_011:
可惜到現在,本身都係唔支援多線程。除非外接  declare dll [/quote]

其實vba 是懶人工具,一勞永逸,如果d code 寫得好

QQL0102 2019-2-21 08:09 AM

[quote]原帖由 [i]愛國愛港007[/i] 於 2019-2-21 07:38 AM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=495172299&ptid=28041053][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]


如果唔係行行有野, 會不會將10000改做最後的column/row? 起碼唔駛loop 10000^2 [/quote]

係實際運算上,兩個loop last row, 我會設定做動態咁detect,   上下限值應該吾知,一定吾會固定

QQL0102 2019-2-21 08:28 AM

[quote]原帖由 [i]idontknowjack[/i] 於 2019-2-20 09:07 AM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=495123809&ptid=28041053][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]
鄙人明白各位前輩說的是 VBA loop 的速度,我撘訕一下⋯
從把一批 cell 的 value 加起來的目的來說,會不會在 code 內用 application object 來 invoke Excel 自己的 SUM spreadsheet function 最快?
我沒有 Excel, 估計而已。 ... [/quote]

你意思指係vba 入邊用excel 內置公式sum 去計一個col 的總和?

我覺得睇你點樣寫code,  如果由1至100咁將所有cell 加總,應該最慢,放係array 計會好快計出總和,但最後ouput 出黎,但慢過過內置公式。 我曾經三種方法都試過。

idontknowjack 2019-2-21 11:20 AM

[quote]原帖由 [i]QQL0102[/i] 於 2019-2-21 08:28 AM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=495173808&ptid=28041053][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]


你意思指係vba 入邊用excel 內置公式sum 去計一個col 的總和?

我覺得睇你點樣寫code,  如果由1至100咁將所有cell 加總,應該最慢,放係array 計會好快計出總和,但最後ouput 出黎,但慢過過內置公式。 我曾經三種方法都試過。 ... [/quote]
是的,SUM 不限於 column, range 都可以,而且可以有多個 arguments 。
頁: [1] 2
查看完整版本: excel VBA 用CELL運算,真係好慢!好慢!