標題: 80萬個Cell Vlookup加速方法? [打印本頁]

---

作者: bongbong3481    時間: 2021-1-24 21:20     標題: 80萬個Cell Vlookup加速方法?

個EXCEL FILE有成80萬個CELL要用VLOOKUP 運算, 每次要用差唔多半個鐘,有冇方法可以加速?

---

作者: patrickleohk    時間: 2021-1-24 21:25

經驗話我知, 無
一係換機
而且那麼多行, 一定不要 set automatic calculation,
唔係既 update 一個 cell  佢又自動 lookup 一次

---

作者: peter_chan    時間: 2021-1-25 11:51




    睇番以前的舊文﹐即使換機都無用﹐幾頂級的CPU/RAM 都幫唔到幾多。
因為excel 根本唔預你處理咁大量data﹐軟件運算能力係慢的。


處理方法﹐一係正如你所講﹐但唔冶本﹐因為點都要做updata data﹐點都要再計算﹐都係花好長時間的。

徹底一點﹐就用改用database 軟件處理。
想平就自學MS access﹐可以講係最基本最易入手的database軟件﹐入門價亦好親民。

---

作者: bongbong3481    時間: 2021-1-25 19:49



Auto cal轉手動，有做呢個動作，如果沒，會更慢

---

作者: platinum    時間: 2021-1-26 15:01

參考下
https://exceljet.net/formula/faster-vlookup-with-2-vlookups

---

作者: kcthomlau    時間: 2021-1-26 18:13

轉用index+match會唔會快D?

---

作者: bongbong3481    時間: 2021-1-26 19:50

回覆 5# platinum

我試左呢個方法，真是好快，試左一個col, 可以用神速去形容

---

作者: bongbong3481    時間: 2021-1-26 21:23

回覆 6# kcthomlau


    樓上既方法先堅抽 https://exceljet.net/formula/faster-vlookup-with-2-vlookups

---

作者: bongbong3481    時間: 2021-1-26 21:44

回覆 5# platinum


    真系好堅, 同一部機, 30分鐘的運算, 而家是30秒

---

作者: eddy    時間: 2021-1-29 12:38

呢個trick 要bookmark  

via HKEPC IR Pro 3.6.1 - Android(3.3.1)

---

作者: bongbong3481    時間: 2021-1-29 13:58

回覆 10# eddy


    我覺得呢個方法要永久置頂,  我用4核機要30分鐘(8 核15分鐘),  用左呢個方法 4核機30秒, 花錢買好D既CPU 性價比都未必高

---

作者: sophinic    時間: 2021-1-29 19:58

勁分享，但唔太明點解會快左咁多

---

作者: eddy    時間: 2021-1-29 21:26




    搵啱路口先行入條街拎野

---

作者: TH30    時間: 2021-1-29 22:18



為甚麼這麼想不開，用EXCEL裝80萬個CELL

---

作者: bongbong3481    時間: 2021-1-29 22:56

回覆 14# TH30

沒其它工具,得EXCEL。我覺得善用EXCEL,都可以做到好好效果

---

作者: kwliu    時間: 2021-1-29 23:55



Excel無預你query/lookup咁大量data, 本身運算algorithm唔係咁有效率，又無indexing，正常應該用Access之類software做query/lookup.

---

作者: eddy    時間: 2021-1-30 11:18


正常機都有裝excel， 去到邊都用到

via HKEPC IR Pro 3.6.1 - Android(3.3.1)

---

作者: TEKKHG    時間: 2021-1-30 11:27

提示: 作者被禁止或刪除 內容自動屏蔽

---

作者: bongbong3481    時間: 2021-1-30 11:45

回覆 18# TEKKHG

window file explorer 內置SEARCH 功能應該得

---

作者: HKSAR    時間: 2021-1-30 12:03

回覆 15# bongbong3481

如果單機...
學小小PHP+NOSQL DB+ 會唔會好少少 ?
因為全部野可以做到唔需要安裝
當然起頭難

---

作者: TEKKHG    時間: 2021-1-30 12:37

提示: 作者被禁止或刪除 內容自動屏蔽

---

作者: javacomhk    時間: 2021-1-30 14:57

改用vba啦

---

作者: bongbong3481    時間: 2021-1-30 18:33

回覆 22# javacomhk


我識寫VBA, 我只想到用for loop由頂到底, 但有10幾個COL 要VLOOKUP 6-8 個SHEET ,但都好似好慢,好花時間

---

作者: javacomhk    時間: 2021-1-31 16:24




    你用得VBA 就將的 cell 讀入去Array 啦，仲逐個cell 去lookup 咩。

---

作者: bongbong3481    時間: 2021-1-31 19:11

回覆 24# javacomhk


    因為有好多個COL, 同埋好多張SHEET要LOAD, 讀完入ARRAY 運算完, 又要將ARRAY D 野放番入CELL,太麻煩了。 樓上果位仁兄介紹既2個 VLOOKUP 方法,簡單好多

---

作者: saldtch    時間: 2021-2-1 14:41

回覆 1# bongbong3481


    試試turn off screen updating

https://excelribbon.tips.net/T00 ... creen_Updating.html

---

作者: bongbong3481    時間: 2021-2-1 19:35

本帖最後由 bongbong3481 於 2021-2-1 19:51 編輯

回覆 26# saldtch


    Turnofff 左了，都是咁慢

---

作者: javacomhk    時間: 2021-2-2 02:47




    個個方法要sort 好的table 先用得。

---

作者: bongbong3481    時間: 2021-2-2 07:36

回覆 28# javacomhk


    如果唔可以SORTING,呢個方法就用唔到了, 但SORT 完再VLOOKUP都唔太花時間, 而且 SORTING 可以用RECORDING 既錄制VBA, 唔洗寫

---

作者: slt    時間: 2021-2-14 08:10


我公司電腦將vba鎖咗

---

作者: bongbong3481    時間: 2021-2-14 09:52

回覆 30# slt


如公式囉,用公式都解決到好多問題。

(其實VBS RUN 唔RUN 到, 如果RUN 到, 有冇試過系VBS 調用VBA?)

---

作者: javacomhk    時間: 2021-2-14 10:31




鎖咗都可以開番架嘛。

---

作者: kin021360    時間: 2021-2-15 00:30

python pandas

---

作者: bongbong3481    時間: 2021-2-15 09:06

回覆 33# kin021360


    唔識PYTHON, 而且公司沒裝PYTHON

---

作者: KinChungE    時間: 2021-2-15 17:34



你仲要諗辦法點俾user入data落DB

---

作者: Mathk    時間: 2021-2-20 05:23

早排用 Power Query 處理近百萬個Cells，做 table merging 可以用「光速」嚟形容，而且可以唔洗code。

---

作者: bongbong3481    時間: 2021-2-20 08:15

回覆 36# Mathk


    唔識用, 而且要搵IT 裝, 公司電腦DOWNLOAD 唔到

---

作者: Mathk    時間: 2021-2-20 20:32



你隻office係咩版本? 同埋咩平台?

---

作者: bongbong3481    時間: 2021-2-20 21:19

回覆 38# Mathk


    win 7 64 bit , office 2010

---

作者: Mathk    時間: 2021-2-20 21:26



咁真係要 IT 部裝 add-in，如果公司政策可以(即係講兩句搞掂)就叫佢裝啦，之後可以甩少好多頭髮。
https://www.microsoft.com/en-us/download/details.aspx?id=39379

---

作者: Mathk    時間: 2021-2-21 01:59

本帖最後由 Mathk 於 2021-2-21 20:09 編輯



根據以上雙 Vlookup 方法內嘅 Excel Table做咗一個同格式嘅 Data 表，
但係改咗幾樣嘢：ID 可以unsorted，Search ID 可以超過一個。
除ID column 外全部data以random generate 咗出嚟，拎盡1048576行，共7340032隻Cells，
然後用Power Query 做一超簡單嘅 merge table (揀好要search嘅column就得，超自然直觀)，
再output返落Output worksheet度，
超級陳年4核laptop 做一次5個ID search 要30秒左右。(打完按refresh)
好處：基本唔會錯，易hand-over。原資料無需排序。
壞處：只限Windows 平台。





樣本檔下載連結：https://we.tl/t-YPrs6987q4

補充一啲資料：
個file 開時要「enable contents」，去 Data 或 Power Query tab按 「Show Queries」/ 「Show Pane」，之後就可以edit 啲 queries 睇做咗乜。

Load 入 Power Query 方法，要圖嘅可以參考同其幾個Excel posts。或：
1. 揀好要 load 入去嘅範圍，然後去 data tab 搵 「from table」
2. 跳咗入 power query editor，之後揀「close & load to...」出返去，揀「connection only」。
3. 去Search table 個範圍將佢load 埋入 power query editor，但唔洗走返出去。
4. 開始merge table ，喺入面叫「merge queries」，揀「merge queries as new」，然後用常識揀就得。
5. 之後可以close & load 啦。

如果要做multi-tables model 嘅揀埋「add to data model」，用開access 同 Power Pivot 嘅應該會知我講乜。
Pivot Table supports multi-table  嘅功能其實係歸屬於 Power Pivot ，2013版或以前係要開啟Power Pivot 套件，2010版更要額外安裝add-in。

圖片附件: RefTable.jpg (2021-2-21 01:34, 114.89 KB) / 下載次數 98
https://www.hkepc.com/forum/attachment.php?aid=2250078&k=c8e053af216b1ece6e8ea3e80b91fa6c&t=1782196409&sid=2NzpTdbcVd



圖片附件: RefTable2.jpg (2021-2-21 01:42, 167.05 KB) / 下載次數 127
https://www.hkepc.com/forum/attachment.php?aid=2250080&k=40b9f7f047c439d9be5bd34145cce7ce&t=1782196409&sid=2NzpTdbcVd



圖片附件: RefTable3.jpg (2021-2-21 01:43, 59.02 KB) / 下載次數 108
https://www.hkepc.com/forum/attachment.php?aid=2250081&k=2382099c18e920d4e672ef947e9c480c&t=1782196409&sid=2NzpTdbcVd

---

作者: bongbong3481    時間: 2021-2-21 15:19

回覆 41# Mathk

如果識用&有得用 Power Query , 好多問題應該簡單好多

---

作者: Mathk    時間: 2021-2-21 20:10



唔洗諗到好難，每一個基礎功能都係5分鐘學識。

---

作者: eoe    時間: 2021-5-3 23:29



可以指教一下我理解岩唔岩
第一組vlookup判斷有無 , 第二組先正式搵野?
如果第一組vlookup搵唔到就唔會去第二組vlookup 所以先提速!?
thank

---

作者: bobopig    時間: 2021-5-4 13:10




    睇過好深, 如果上堂. 好似初級都幾千?

---

作者: eh    時間: 2021-5-5 00:15



應該咁講, exact match mode係大多數人要用既mode, 但係慢
non exact mode快好多, 但係係冇exact match既情況下會return錯既野(有exact match既話正常)
而家佢張exact match拆成non exact lookup+IF判斷係唔係exact match, 咁去到代替一個exact match既lookup
但係由於IF都係用個ID(or你拎泥lookup既野)做判斷, 所以第一次係要lookup番column 1比IF做判斷
confirm有啦, 就可以安全地用non exact mode去真正咁搵
如果第一次搵番泥既ID過唔到個IF, 姐係之後用non exact mode搵會搵錯野, 就用IF泥停左佢/比error出泥

---

作者: sophinic    時間: 2021-5-6 11:37

幾時先識用gpu 計數...
立即解決晒速度問題

---

作者: Mathk    時間: 2021-5-6 19:19




    用錯嘅方法計，就算有GPU都係慢。不過一般大企都有足夠好嘅工具，而一般小企資料量就連30年前嘅Excel都應付到，問題只會係啲人廢連Excel都唔識用。

---

作者: bongbong3481    時間: 2021-5-7 22:06

回覆 47# sophinic

我唔識用GPU,而且手上部公司機廠機,資源有限,我覺得2個VLOOKP 先系王道,比任何硬件提速方法領先好多

---

作者: Mathk    時間: 2021-5-24 21:57




    你用1985嘅技術講2021嘅科技

---

作者: dipsy    時間: 2021-5-25 08:57



一般公司嘅PC, 唔會有獨立gpu 啩

---

作者: bongbong3481    時間: 2021-5-29 08:42

回覆 50# Mathk


   橋唔怕舊

---

作者: Mathk    時間: 2021-5-30 01:09




    操多幾年Excel，上下online course 可能會改觀

---

作者: domwong    時間: 2021-5-30 11:08

行 ADO SQL statement 會快d

---

作者: bongbong3481    時間: 2021-5-30 15:34

回覆 53# Mathk


我知道有好多功能,  沒時間學了, 暫都有提高速度方法,唔搞住

---

作者: bongbong3481    時間: 2021-5-30 15:36

回覆 54# domwong


   其實, 我都有諗過用SQL CODE,  一個LEFT JOIN 應該得,但沒時間試了

---

作者: m_m    時間: 2021-6-2 01:44

Faster VLOOKUP with 2 VLOOKUPS
真心堅！thx bro.

---

作者: bongbong3481    時間: 2021-6-2 21:01

回覆 57# m_m


    我都覺, 絕對是1流的技術,就算3流的硬件,都仍然有1流的速度

---

作者: happy_99    時間: 2021-6-7 23:13

如果單純搵data, SQL 會比excel vlookup 好, 但excel 做filtering, 加worksheet 計計數, 做下management report, 用excel 真係flexable 好多.

---

作者: bongbong3481    時間: 2021-6-12 08:07

回覆 59# happy_99

SQL 既JOIN TABLE 比VLOOKUP 好用好多, 加埋 IF/WHERE 呢類, 篩選DATA簡單好多