標題: [發問] vba 找儲存格位置執行效能請益 [打印本頁]

---

作者: ketrddem    時間: 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物件呢

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

---

作者: ketrddem    時間: 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

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

---

作者: PKKO    時間: 2017-3-25 13:59

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

複製代碼

---

作者: stillfish00    時間: 2017-3-27 14:24

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

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

1. Sub find2()
   
2.     Dim s
   
3.     T = Timer
   
4.     s = Sheets("工作表1").Cells(1, 1).Resize(1000000, 1)
   
5.     For i = 1 To 1000000
   
6.         If s(i, 1) = "987267" Then
   
7.             Exit For
   
8.         End If
   
9.     Next
   
10.     MsgBox "第" & i & "行 , " & Timer - T
    
11. End Sub

複製代碼

---

作者: ketrddem    時間: 2017-3-27 22:39

太感謝二位大哥的解說

小弟馬上試試看

---

作者: ketrddem    時間: 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

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

---

作者: GBKEE    時間: 2017-3-28 07:14

回復 6# ketrddem

Application.Match 也可以

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

複製代碼

---

作者: PKKO    時間: 2017-3-28 10:03

回復 6# ketrddem


我開一個新的excel檔案
複製你的程式直接貼上模組
執行之後一秒之內就跑完囉

---

作者: ketrddem    時間: 2017-3-28 11:45

回復 8# PKKO


    感謝。我晚上再來試一次

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)