返回列表 上一主題 發帖

[發問] 請益 是否有更快的查詢方式

[發問] 請益 是否有更快的查詢方式

小弟有一是想請問
附件請參考
做法
1.分頁 序號 內容有兩萬多筆資料。
2.序號內容符合 L 分頁 或者 R分頁的 起始Barcode  及 結束Barcode 區間 。
3.符合區間的序號 在旁邊的工單號碼 自動帶出 L 或者 R 區邊的 工單號碼資訊。
個人做法如下
1.小弟先用資料庫(Arry) 先去讀取了 L 跟 R的所有起始Barcode  及 結束Barcode 區間 ,還有工單號碼
2.再利用迴圈 及 IF  的方式 去判別 是否在區間內開啟跟結束的Barcode。
3.如果符合條件 則帶出 工單號碼。
問題
雖然 L分頁 跟 R分頁的資料不多,但是序號分頁 有兩萬多筆資料,用迴圈跑就會跑很久(差不多20分鐘)
每一筆序號 就要比對 L分頁跟 R分頁的資料。
想請問一下!!是否建議更快速的方式??
謝謝!! 工單查詢.zip (266.96 KB)

本帖最後由 n7822123 於 2021-7-30 01:52 編輯

回復 1# fantersy

1.小弟先用資料庫(Arry) 先去讀取了 L 跟 R的所有起始Barcode  及 結束Barcode 區間 ,還有工單號碼
2.再利用迴圈 及 IF  的方式 去判別 是否在區間內開啟跟結束的Barcode。
3.如果符合條件 則帶出 工單號碼。
問題
雖然 L分頁 跟 R分頁的資料不多,但是序號分頁 有兩萬多筆資料,用迴圈跑就會跑很久(差不多20分鐘)


邏輯沒問題,才2萬多筆資料,做簡單的判斷,理論上不會跑這麼久(20分鐘)

你應該沒有用純數值運算+陣列一次輸出,若是一個個儲存格跑,會很久(儲存格是物件)

我自己嘗試,跑幾秒鐘而已,我電腦也是N年前的普通機,不可能比你快這麼多

你的附檔沒有程式(.xlsx),無法知道是哪裡花較久時間
程式是依需求寫的,需求表達不清楚
或者沒有上傳附件,愛莫能助

TOP

本帖最後由 singo1232001 於 2021-7-30 04:10 編輯

回復 1# fantersy


    還有一兩種方式也很快
調用match函數 或者vlookup函數
只不過要做4次

本檔案內有兩種方式
一般陣列處理 50秒
3維陣列處理 1秒

工單查詢 v1.zip (902.97 KB)

TOP

本帖最後由 singo1232001 於 2021-7-30 04:41 編輯

回復 3# singo1232001


另外補充一下
3維方式內
總共有3組陣列  
ar=放序號用 功能抽象為 桶表資料庫 (3維陣列)
LR=放L跟R的工單&起始結束用    功能抽象為  查詢資料 (2維陣列)
br=放比對後的資料收集表用 功能抽象為 集中整合比對後資料  (2維陣列)

此案與一般不同之處 在於
我是將  資料庫 與查詢資料 是反過來建置的
雖然明面上還是 用查詢資料 去查資料庫
但我一開始就把 資料庫 跟 查詢資料 顛倒過來 建置設計表

原因是
由於L,R本身資料量太少
所以用L,R 去查找比對序號

正常來說 應該要L,R資料特別多
是用序號 去查找比對 L,R資料庫

不過正反查找比對 都可以做
就目前來說 速度還可以

就原理來說 桶表主要放大量資料 速度會比較快
所以才把序號設為 桶表資料庫
否則正常情況下 LR資料量好幾萬筆
就應該要把L,R設為桶表資料庫

另外內附三張圖 便於學習與理解
三維陣列 可以想像成 開了99張的工作表 這樣會比較容易理解

如果資料量還更大
建議直接去學SQL 下語法 會比用excel來的快

另外補充
為什麼會挑第11位 與最後1位 的字元來當基準(這是為了此案而優化的部分)
因為這兩個字元變化較為平均
放在桶裡比較容易分散開來
上述的好處是 ar初始陣列設定大小時 就不用設太大了
只要設2000列 就可以放入了

TOP

回復 4# singo1232001


    另外 有個小失誤
關於 module1內的 sub tt() 有一段 我少打了 一行代碼

  For j = 1 To ar(a, 0, 0) - 1
        If TX = ar(a, j, 0) Then
        rw = ar(a, j, 1)  '序號列位
        br(rw, 0) = LR(i, 3)   '工丹號碼
        br(rw, 1) = LR(i, 2)   '工作表名
        br(rw, 2) = rw         '列位
        rw = 0
        Exit For    '<--這行幫我加上去
        End If
    Next

這樣速度會在快一咪咪

TOP

本帖最後由 singo1232001 於 2021-7-30 05:16 編輯

回復 5# singo1232001


    因為檔案內有個小失誤 我也順便修正了一下檔案
    我把檔案重傳一次

3維陣列工單查詢 v1.zip (903.4 KB)

TOP

回復 1# fantersy

請測試看看,謝謝

Sub test()
Dim Arr, xD, i&, Tm
Tm = Timer
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([L!a1], [L!l65536].End(3))
For i = 2 To UBound(Arr)
    xD(Arr(i, 10)) = Arr(i, 1)
    xD(Arr(i, 12)) = Arr(i, 1)
Next
Arr = Range([R!b1], [R!m65536].End(3))
For i = 2 To UBound(Arr)
    xD(Arr(i, 10)) = Arr(i, 1)
    xD(Arr(i, 12)) = Arr(i, 1)
Next
Arr = Range([序號!a2], [序號!a65536].End(3))
For i = 1 To UBound(Arr)
    Arr(i, 1) = xD(Arr(i, 1))
Next
Sheets("序號").[b2].Resize(UBound(Arr)) = Arr
MsgBox Timer - Tm
End Sub

TOP

區間比對, 沒有好方法, 迴迴圈吧!!!
若Barcode確定是由小而大排序的話, 可以減少判斷幾次~~
Xl0000114.rar (386.29 KB)

TOP

本帖最後由 samwang 於 2021-7-30 12:56 編輯

回復 1# fantersy


看了准大的答案,才知道誤會樓主的需求了,正確需求要區間比對,
所以請忽略7樓程式,謝謝

TOP

回復 8# 准提部林


    感謝 准大 解惑
看來真的只有用迴圈了

我原本的程式判讀 只要符合區間 使用
Exit For  跳出內迴圈
看到 准大 的程式 也是個不錯的方式
再次感謝喔!!

TOP

        靜思自在 : 不要小看自己,因為人有無限的可能。
返回列表 上一主題