帖子: 26
主題: 10
精華: 0
積分: 84
點名: 0
作業系統: win10
軟體版本: excel2010
閱讀權限: 20
性別: 男
註冊時間: 2016-6-10
最後登錄: 2018-8-20

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 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

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

2^#

發表於 2017-7-9 09:29 | 只看該作者

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

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

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

jazzcarr

中學生 Rank: 2

帖子: 26
主題: 10
精華: 0
積分: 84
點名: 0
作業系統: win10
軟體版本: excel2010
閱讀權限: 20
性別: 男
註冊時間: 2016-6-10
最後登錄: 2018-8-20

3^#

發表於 2017-7-9 22:17 | 只看該作者

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

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

TOP

vanguarx

中學生 Rank: 2

帖子: 41
主題: 8
精華: 0
積分: 64
點名: 0
作業系統: WIN7
軟體版本: OFFICE2010
閱讀權限: 20
註冊時間: 2015-3-11
最後登錄: 2020-10-7

4^#

發表於 2017-7-10 15:17 | 只看該作者

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

TOP

vanguarx

中學生 Rank: 2

帖子: 41
主題: 8
精華: 0
積分: 64
點名: 0
作業系統: WIN7
軟體版本: OFFICE2010
閱讀權限: 20
註冊時間: 2015-3-11
最後登錄: 2020-10-7

5^#

發表於 2017-7-12 08:28 | 只看該作者

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

You can refer to this page.

TOP

jazzcarr

中學生 Rank: 2

帖子: 26
主題: 10
精華: 0
積分: 84
點名: 0
作業系統: win10
軟體版本: excel2010
閱讀權限: 20
性別: 男
註冊時間: 2016-6-10
最後登錄: 2018-8-20

6^#

發表於 2017-7-13 07:54 | 只看該作者

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

TOP

mhl9mhl9

中學生 Rank: 2

帖子: 214
主題: 74
精華: 0
積分: 296
點名: 0
作業系統: win7
軟體版本: office2007
閱讀權限: 20
性別: 男
來自: hk
註冊時間: 2013-6-17
最後登錄: 2018-11-3

7^#

發表於 2017-7-15 01:39 | 只看該作者

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

lmh

TOP

jazzcarr

中學生 Rank: 2

帖子: 26
主題: 10
精華: 0
積分: 84
點名: 0
作業系統: win10
軟體版本: excel2010
閱讀權限: 20
性別: 男
註冊時間: 2016-6-10
最後登錄: 2018-8-20

8^#

發表於 2017-7-17 23:12 | 只看該作者

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

TOP

bioleon69

　暱稱: 小B

中學生 Rank: 2

帖子: 62
主題: 14
精華: 0
積分: 86
點名: 0
作業系統: win10
軟體版本: offffice2013
閱讀權限: 20
性別: 男
來自: 台北
註冊時間: 2016-8-23
最後登錄: 2017-11-5

論壇贊助勳章

9^#

發表於 2017-7-20 03:13 | 只看該作者

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

回復 7# mhl9mhl9

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

VBA 從0開始
先從學會看的懂開始
先從會有基本修改能力開始
一步一步學習中

TOP

mhl9mhl9

中學生 Rank: 2

帖子: 214
主題: 74
精華: 0
積分: 296
點名: 0
作業系統: win7
軟體版本: office2007
閱讀權限: 20
性別: 男
來自: hk
註冊時間: 2013-6-17
最後登錄: 2018-11-3

10^#

發表於 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")''''''顯示程式執行耗時多少秒

lmh

TOP