帖子: 50
主題: 8
精華: 0
積分: 66
點名: 0
作業系統: WIN7
軟體版本: OFFICE 2013
閱讀權限: 20
性別: 男
註冊時間: 2015-2-3
最後登錄: 2022-3-5

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2017-3-24 23:23 | 只看該作者

[發問] vba 找儲存格位置執行效能請益

小弟正在設計一個表
但這個表未來預期的需求會達到數萬筆

其中一個很簡單的功能，就是find，指定一個字串後，去找它的位置，只是我得將這數萬筆資料都去找相關位置出來

問題來了，有幾十幾百筆，用什麼方法似乎都感受不到明顯差異

如果數千數萬筆影響就出現了

請教，vba中

查找字串：123456789

方法一：sheet1.range("A").Find(what:="123456789", lookin:=xlValues).address

方法二：
redim a()
for i = 1 to lostrow
      字串(i)=sheet1.cells(i,1)
      if  字串(i)="123456789" then
            msgbox i
      end if
next

目前嚐試這二種方法

小弟知道有一種叫dictionary物件的方法，執行效能超高，但不知如何應用在數萬筆資料中查找位置
或是能否有個範例混合運用陣列及dictionary物件呢

小弟學淺，目前只會使用到陣列，但想到爾後數萬筆資料，真的很想提高效能，懇請版上各位先進指點一二。謝謝。

樓主熱帖

EXCEL VBA新手，請前輩多多指教

ketrddem

中學生 Rank: 2

帖子: 50
主題: 8
精華: 0
積分: 66
點名: 0
作業系統: WIN7
軟體版本: OFFICE 2013
閱讀權限: 20
性別: 男
註冊時間: 2015-2-3
最後登錄: 2022-3-5

2^#

發表於 2017-3-24 23:45 | 只看該作者

補充：
開新的工作簿
插入模組
以下模組自動填入一到一百萬
Sub test()
For i = 1 To 1000000
Sheets("工作表1").Cells(i, 1) = i + 1
Next
End Sub

查找方法一：直接使用內建函教，需時一秒
Sub find1()
t = Timer
If Sheets("工作表1").Range("a:a").Find(what:="987267", LookIn:=xlValues).Rows > 0 Then
MsgBox Timer - t
End If
End Sub

方法二：迴圈填入陣列，天哪，須時十一秒
Sub find2()
ReDim s(1 To 1000000)
t = Timer
For i = 1 To 1000000
s(i) = Sheets("工作表1").Cells(i, 1)
If s(i) = "987267" Then
MsgBox Timer - t
End If
Next
End Sub

懇求第三種方法、第四種方法、第五種方法，小弟就是想找那一種方法效能最高

EXCEL VBA新手，請前輩多多指教

TOP

PKKO

　暱稱: PKKO

高中生 Rank: 3 Rank: 3

帖子: 549
主題: 152
精華: 0
積分: 691
點名: 0
作業系統: WIN7
軟體版本: OFFICE 2010
閱讀權限: 50
性別: 男
註冊時間: 2013-8-10
最後登錄: 2022-9-7

論壇榮譽勳章論壇贊助勳章

3^#

發表於 2017-3-25 13:59 | 只看該作者

SUB TEST
RNG=[A1].RESIZE(10000,2).VALUE'10000的部分可自行變更列數
Set D = CreateObject("SCRIPTING.DICTIONARY") '字典物件
FOR I =1 TO UBOUND(RNG)
D(CSTR(RNG(I,1)))=I
NEXT
'這邊看你要找甚麼資料,假設是x,就會出現x是第幾列了
MSGBOX D(CSTR(X))
END SUB

複製代碼

PKKO

TOP

stillfish00

大學生 Rank: 4

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

4^#

發表於 2017-3-27 14:24 | 只看該作者

本帖最後由 stillfish00 於 2017-3-27 14:28 編輯

回復 2# ketrddem
你的方法二不要在For裡面一個一個從Cell取到陣列
而是先一次取出來...

Sub find2()
Dim s
T = Timer
s = Sheets("工作表1").Cells(1, 1).Resize(1000000, 1)
For i = 1 To 1000000
If s(i, 1) = "987267" Then
Exit For
End If
Next
MsgBox "第" & i & "行 , " & Timer - T
End Sub

複製代碼

表達不清、題意不明確、沒附檔案格式、沒有討論問題的態度~~~~~~以上愛莫能助。

TOP

ketrddem

中學生 Rank: 2

帖子: 50
主題: 8
精華: 0
積分: 66
點名: 0
作業系統: WIN7
軟體版本: OFFICE 2013
閱讀權限: 20
性別: 男
註冊時間: 2015-2-3
最後登錄: 2022-3-5

5^#

發表於 2017-3-27 22:39 | 只看該作者

太感謝二位大哥的解說

小弟馬上試試看

EXCEL VBA新手，請前輩多多指教

TOP

ketrddem

中學生 Rank: 2

帖子: 50
主題: 8
精華: 0
積分: 66
點名: 0
作業系統: WIN7
軟體版本: OFFICE 2013
閱讀權限: 20
性別: 男
註冊時間: 2015-2-3
最後登錄: 2022-3-5

6^#

發表於 2017-3-27 22:50 | 只看該作者

回復 3# PKKO

Sub find3()
t = Timer
Rng = Sheets("工作表1").Cells(1, 1).Resize(1000000, 1).Value '10000的部分可自行變更列數
Set D = CreateObject("SCRIPTING.DICTIONARY") '字典物件

For i = 1 To UBound(Rng)
D(CStr(Rng(i, 1))) = i
Next
'這邊看你要找甚麼資料,假設是x,就會出現x是第幾列了

MsgBox D(CStr("987267"))

End Sub

執行後，就一直跑，然後無法停止了

EXCEL VBA新手，請前輩多多指教

TOP

GBKEE

超級版主 Rank: 8 Rank: 8

帖子: 5923
主題: 13
精華: 1
積分: 5986
點名: 0
作業系統: win10
軟體版本: Office 2010
閱讀權限: 150
性別: 男
來自: 台灣基隆
註冊時間: 2010-5-1
最後登錄: 2022-1-23

2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章 2011兔年全勤獎章

7^#

發表於 2017-3-28 07:14 | 只看該作者

回復 6# ketrddem

Application.Match 也可以

Option Explicit
Sub Ex()
Dim T As Date, R As Variant, No As Variant
No = InputBox("輸入尋找之文字,數字")
If IsNumeric(No) Then No = Val(No)
T = Time
With Sheets("工作表1").Cells(1, 1).Resize(1000000)
R = Application.Match(No, .Cells, 0)
If Not IsError(R) Then
MsgBox "Find In " & .Range("a" & R).Address(0, 0) & " " & Application.Text(Time - T, "[S]") & " 秒 "
Else
MsgBox "Not Find "
End If
End With
End Sub