返回列表 上一主題 發帖

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

[發問] 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新手,請前輩多多指教

補充:
開新的工作簿
插入模組
以下模組自動填入一到一百萬
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

  1. SUB TEST
  2. RNG=[A1].RESIZE(10000,2).VALUE'10000的部分可自行變更列數
  3. Set D = CreateObject("SCRIPTING.DICTIONARY") '字典物件

  4. FOR I =1 TO UBOUND(RNG)
  5.      D(CSTR(RNG(I,1)))=I
  6. NEXT
  7. '這邊看你要找甚麼資料,假設是x,就會出現x是第幾列了

  8. MSGBOX D(CSTR(X))


  9. END SUB
複製代碼
PKKO

TOP

本帖最後由 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
複製代碼
表達不清、題意不明確、沒附檔案格式、沒有討論問題的態度~~~~~~以上愛莫能助。

TOP

太感謝二位大哥的解說

小弟馬上試試看
EXCEL VBA新手,請前輩多多指教

TOP

回復 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

回復 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
複製代碼
感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.

TOP

回復 6# ketrddem


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

TOP

回復 8# PKKO


    感謝。我晚上再來試一次
EXCEL VBA新手,請前輩多多指教

TOP

        靜思自在 : 看別人不順眼,是自己修養不夠。
返回列表 上一主題