請益是否有更快的查詢方式 - Excel程式區

fantersy

中學生 Rank: 2

帖子: 132
主題: 35
精華: 0
積分: 179
點名: 0
作業系統: Windows
軟體版本: Office 2007
閱讀權限: 20
性別: 男
來自: Taiwan
註冊時間: 2014-12-24
最後登錄: 2024-8-2

11^# 跳轉到 »

發表於 2021-7-30 14:33 | 只看該作者

回復 3# singo1232001

函數的部分我有使用過
只不過這是我舉例範例數量只有2萬多筆
若筆數越多用函數電腦會越吃力
後來就放棄用函數了 ><""

TOP

fantersy

中學生 Rank: 2

帖子: 132
主題: 35
精華: 0
積分: 179
點名: 0
作業系統: Windows
軟體版本: Office 2007
閱讀權限: 20
性別: 男
來自: Taiwan
註冊時間: 2014-12-24
最後登錄: 2024-8-2

10^#

發表於 2021-7-30 14:31 | 只看該作者

回復 8# 准提部林

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

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

TOP

samwang

大學生 Rank: 4

帖子: 976
主題: 7
精華: 0
積分: 1018
點名: 0
作業系統: Win10
軟體版本: Office 2016
閱讀權限: 50
性別: 男
註冊時間: 2013-4-19
最後登錄: 2024-10-30

9^#

發表於 2021-7-30 12:54 | 只看該作者

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

回復 1# fantersy

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

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-25

8^#

發表於 2021-7-30 11:58 | 只看該作者

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

Xl0000114.rar (386.29 KB)

TOP

samwang

大學生 Rank: 4

帖子: 976
主題: 7
精華: 0
積分: 1018
點名: 0
作業系統: Win10
軟體版本: Office 2016
閱讀權限: 50
性別: 男
註冊時間: 2013-4-19
最後登錄: 2024-10-30

7^#

發表於 2021-7-30 09:14 | 只看該作者

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

singo1232001

　暱稱: 熱狗慶

中學生 Rank: 2

帖子: 354
主題: 5
精華: 0
積分: 387
點名: 0
作業系統: windows7
軟體版本: vba,vb,excel2007
閱讀權限: 20
性別: 男
註冊時間: 2017-1-8
最後登錄: 2024-8-2

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

6^#

發表於 2021-7-30 05:04 | 只看該作者

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

回復 5# singo1232001

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

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

TOP

singo1232001

　暱稱: 熱狗慶

中學生 Rank: 2

帖子: 354
主題: 5
精華: 0
積分: 387
點名: 0
作業系統: windows7
軟體版本: vba,vb,excel2007
閱讀權限: 20
性別: 男
註冊時間: 2017-1-8
最後登錄: 2024-8-2

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

5^#

發表於 2021-7-30 04:46 | 只看該作者

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

　暱稱: 熱狗慶

中學生 Rank: 2

帖子: 354
主題: 5
精華: 0
積分: 387
點名: 0
作業系統: windows7
軟體版本: vba,vb,excel2007
閱讀權限: 20
性別: 男
註冊時間: 2017-1-8
最後登錄: 2024-8-2

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

4^#

發表於 2021-7-30 04:26 | 只看該作者

本帖最後由 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

singo1232001

　暱稱: 熱狗慶

中學生 Rank: 2

帖子: 354
主題: 5
精華: 0
積分: 387
點名: 0
作業系統: windows7
軟體版本: vba,vb,excel2007
閱讀權限: 20
性別: 男
註冊時間: 2017-1-8
最後登錄: 2024-8-2

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

3^#

發表於 2021-7-30 04:04 | 只看該作者

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

回復 1# fantersy

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

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

工單查詢 v1.zip (902.97 KB)

TOP

n7822123

　暱稱: 阿龍

中學生 Rank: 2

帖子: 406
主題: 8
精華: 0
積分: 453
點名: 0
作業系統: WINDOWS 7
軟體版本: 2007
閱讀權限: 20
性別: 男
來自: 台灣
註冊時間: 2015-2-7
最後登錄: 2021-7-31

2^#

發表於 2021-7-30 01:47 | 只看該作者

本帖最後由 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

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

[收藏此主題] [關注此主題的新回復]

[通過 QQ、MSN 分享給朋友]

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

[收藏此主題] [關注此主題的新回復]

[通過 QQ、MSN 分享給朋友]

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