Excel 使用lookup vlookup 以及index+match 3種公式速度性能效率對比（詳細數據分析）

Excel的表格如果有大量的計算公式，打開和計算的速度會有些慢，特別是數據量比較大時會有些卡頓，為了找到最佳性能的公式，決定對lookup vlookup 以及index+match 3種公式速度性能效率對比

以下對比的環(huán)境是完全一致的

1.使用同一個文件復制出來。格式完全一樣

2.數據行數完全一樣

3.表格列數完全 一樣（除了第2種多了一列輔助列）
4.測試電腦 操作系統(tǒng) 內存 CPU完全 一樣

測試結果：
1.直接先match找到行號，再用index取到各列的相應數據
物料名稱使用的公式（其它類同）
=IFERROR(INDEX(系統(tǒng)_物料基本信息表[物料名稱],MATCH([@物料編碼],系統(tǒng)_物料基本信息表[物料編碼],0)),"")
新增一行數據到彈出錄入窗口的時間：1.2188   1.1719

2.先做一個輔助列 物料序號，用match找到行號填入到 物料序號輔助列，其它各列用index取到各列的相應數據，但都共用這個物料序號輔助列
物料序號輔助列 公式
=MATCH([@物料編碼],系統(tǒng)_物料基本信息表[物料編碼],0)
物料名稱使用的公式（其它類同）
=IFERROR(INDEX(系統(tǒng)_物料基本信息表[物料名稱],[@物料序號]),"")
新增一行數據到彈出錄入窗口的時間：1.1797   1.1328

3.直接使用lookup ,但物料表未排序（有些數據取不準）
物料名稱使用的公式（其它類同）
=IFERROR(LOOKUP([@物料編碼],系統(tǒng)_物料基本信息表[物料編碼],系統(tǒng)_物料基本信息表[物料名稱]),"")

新增一行數據到彈出錄入窗口的時間：1.3711   1.3242

4.直接使用vlookup
物料名稱使用的公式（其它類同）
=IFERROR(VLOOKUP([@物料編碼],系統(tǒng)_物料基本信息表[[物料編碼]:[單價]],COLUMN(系統(tǒng)_物料基本信息表[物料名稱])-COLUMN(系統(tǒng)_物料基本信息表),FALSE),"")

新增一行數據到彈出錄入窗口的時間：1.5039   1.6602

5.使用Vlookup +非連續(xù)列

=IFERROR(VLOOKUP([@物料編碼],IF({1,0},系統(tǒng)_物料基本信息表[物料編碼],系統(tǒng)_物料基本信息表[物料名稱]),2,FALSE),"")

出乎意料，速度更慢：3.1992

方法說明：

1、INDEX函數：返回表中的值。

=INDEX（在哪兒找，第幾行）

2、MATCH函數：返回指定數值在指定區(qū)域中的位置。

=MATCH（找誰，在哪兒找，匹配方式）

3、VLOOKUP函數：縱向查找返回表中的值。缺點：查閱值需要位于查找區(qū)域的第一列。

=VLOOKUP（找誰，在哪兒找，第幾列，匹配方式）

總結：

1.帶輔助列的index + match 函數的方法最快
2.不帶輔助列的index + match 函數的方法較快且不需要輔助列，方便，推薦使用
3.lookup速度更慢，且如果不對物料資料按物料編碼先進行排序的話，取的數據會不準確

用lookup函數，如果用lookup查找前要對文字一列進行升序排序。然后就可以正確查找了

原因是Lookup函數采用的二叉樹的查找方式，如果不排序，其運行機制將發(fā)生偏差，產生上述問題

4.vlookup速度第2慢  ，使用Vlookup +非連續(xù)列 最慢

vlookup函數則必須將源數據區(qū)域搜索主鍵列調換順序 ，放在第1列

網上有2重vlookup的方法比較快，擔前提也需要先對 物料資料表先按物料編碼進行排序

5.另vlookup 也可搜索  非連續(xù) 的2列

=VLOOKUP(E1,IF({1,0},$B$1:$B$3,$A$1:$A$3),2,0)

IFERROR(VLOOKUP([@物料編碼],IF({1,0},系統(tǒng)_物料基本信息表[物料編碼],系統(tǒng)_物料基本信息表[物料名稱]),2,FALSE),"")

另數據行數越多，所有使用公式的速度都會成比例下降

以上是2000多行數據

如果到4000多行數據，則新增行時速度會下降一半左右。