標題: 關於大面積的Vlookup緩慢改善的可能? [打印本頁]

---

作者: jazzcarr    時間: 2017-7-8 22:57     標題: 關於大面積的Vlookup緩慢改善的可能?

假設
A1~A65535 放商品代碼(英數混合共十位數)
B1~B65535 放價格 (但價格可能缺漏 或 出現錯誤值)
C1~C9999 隨機放入商品代碼(或客人自行輸入)
D1~D9999 顯示對應C欄的商品價格

目前的作法是將A1~B65536定義為名稱定義"商品價格表"
再把D欄用Vlookup的公式參照C欄去抓價格

但因為資料量大的關係? 比較弱小的電腦會跑很久
請問有更好的函數或自訂函數或陣列函數的寫法嗎?

謝謝

又
ps.小弟不是科班出生 有下面想法 不知道對否 如果觀念不對下可能會看不懂請忽略 或 指正 感恩!
VBA可以避免重複大量的運算嗎?例如先把名稱定義"商品價格表"的資料讀到記憶體內等方式?
不要讓D欄每一格Vlookup都去抓資一次名稱定義"商品價格表"的資料?
讓整個D欄每一格Vlookup(或自訂函數)共用記憶體內的"商品價格表"的資料
藉此讓Vlookup(或自訂函數)可以更快速運算?

謝謝

---

作者: ML089    時間: 2017-7-9 09:29

將A1~B65536定義為名稱定義"商品價格表"
若資料沒有那麼多，資料表定義那麼大，除浪費記憶體也讓查詢範圍變大速度變慢。

定義"商品價格表"，採用動態範圍來定義
=OFFSET($A$2,0,0,counta($A:$A)-1,2)

---

作者: jazzcarr    時間: 2017-7-9 22:17

本帖最後由 jazzcarr 於 2017-7-9 22:19 編輯

其實資料量 還超過655536
價格可能缺漏 或 出現錯誤值的比率約0.1% (因為商品可能下架或暫時停售)
有比vlookup更好的函數或VBA函數嗎?

---

作者: vanguarx    時間: 2017-7-10 15:17

用 index(match)
你可以去google 一下用法, 我實際用過的經驗, 在資料很多時, 的確比 vlookup 快

---

作者: vanguarx    時間: 2017-7-12 08:28

http://www.exceluser.com/formulas/how-to-use-index-match-part-3.html

You can refer to this page.

---

作者: jazzcarr    時間: 2017-7-13 07:54

好喔 謝謝
另外請問可以寫成陣列公式嗎?
會比較快嗎?

---

作者: mhl9mhl9    時間: 2017-7-15 01:39

[attach]27476[/attach]回復 1# jazzcarr
[d1:d65536]用字典計算0.3秒,用vlookup函數計算5秒,用vlookup函數的好處是保留公式,c欄隨填d欄馬上得到結果

---

作者: jazzcarr    時間: 2017-7-17 23:12

謝謝 mhl9mhl9 大大 我的電腦比較慢 但兩個方法的時間 差了將近20倍!!!
可以請您註解一下程式碼嗎?(字典那個就可以)
第一次看到這樣的方式
但我還需要稍作修改
才能作為實務上的運用
感恩

---

作者: bioleon69    時間: 2017-7-20 03:13

本帖最後由 bioleon69 於 2017-7-20 03:14 編輯

回復 7# mhl9mhl9


非常感謝mhl9mhl9大
剛好有同樣需求
測試了一下您的檔案
這招真的好用!
收下了
謝謝:lol

---

作者: mhl9mhl9    時間: 2017-7-21 03:09

回復 8# jazzcarr
    Set d = CreateObject("scripting.dictionary")''''''建立字典d(不用引用)
    a = [A1].CurrentRegion.Resize(, 4)''''''''''''''''''把儲存格資料放進變量a(資料只有3欄,設定4欄,最後欄將存放結果)
   For i = 1 To UBound(a, 1)
    d(a(i, 1)) = a(i, 2)''''''''''d(要找是參照)=找到的結果'''''把所有參照和結果都裝進字典d,一一對應
    For i = 1 To UBound(a, 1)
    a(i, 4) = d(a(i, 3))''''''第4欄是結果,第4欄=d(第三欄)
    Next i
    [d1].Resize(UBound(a, 1)) = WorksheetFunction.Index(a, 0, 4)'''''右邊是第4欄全部資料.把全部資料輸入D欄   
    MsgBox Format(Timer - pp, "0.0000")''''''顯示程式執行耗時多少秒

---

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