Excel 使用lookup vlookup 以及index+match 3种公式速度性能效率对比（详细数据分析）

Excel的表格如果有大量的计算公式，打开和计算的速度会有些慢，特别是数据量比较大时会有些卡顿，为了找到最佳性能的公式，决定对lookup vlookup 以及index+match 3种公式速度性能效率对比

以下对比的环境是完全一致的

1.使用同一个文件复制出来。格式完全一样

2.数据行数完全一样

3.表格列数完全 一样（除了第2种多了一列辅助列）
4.测试电脑 操作系统 内存 CPU完全 一样

测试结果：
1.直接先match找到行号，再用index取到各列的相应数据
物料名称使用的公式（其它类同）
=IFERROR(INDEX(系统_物料基本信息表[物料名称],MATCH([@物料编码],系统_物料基本信息表[物料编码],0)),"")
新增一行数据到弹出录入窗口的时间：1.2188   1.1719

2.先做一个辅助列 物料序号，用match找到行号填入到 物料序号辅助列，其它各列用index取到各列的相应数据，但都共用这个物料序号辅助列
物料序号辅助列 公式
=MATCH([@物料编码],系统_物料基本信息表[物料编码],0)
物料名称使用的公式（其它类同）
=IFERROR(INDEX(系统_物料基本信息表[物料名称],[@物料序号]),"")
新增一行数据到弹出录入窗口的时间：1.1797   1.1328

3.直接使用lookup ,但物料表未排序（有些数据取不准）
物料名称使用的公式（其它类同）
=IFERROR(LOOKUP([@物料编码],系统_物料基本信息表[物料编码],系统_物料基本信息表[物料名称]),"")

新增一行数据到弹出录入窗口的时间：1.3711   1.3242

4.直接使用vlookup
物料名称使用的公式（其它类同）
=IFERROR(VLOOKUP([@物料编码],系统_物料基本信息表[[物料编码]:[单价]],COLUMN(系统_物料基本信息表[物料名称])-COLUMN(系统_物料基本信息表),FALSE),"")

新增一行数据到弹出录入窗口的时间：1.5039   1.6602

5.使用Vlookup +非连续列

=IFERROR(VLOOKUP([@物料编码],IF({1,0},系统_物料基本信息表[物料编码],系统_物料基本信息表[物料名称]),2,FALSE),"")

出乎意料，速度更慢：3.1992

方法说明：

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

=INDEX（在哪儿找，第几行）

2、MATCH函数：返回指定数值在指定区域中的位置。

=MATCH（找谁，在哪儿找，匹配方式）

3、VLOOKUP函数：纵向查找返回表中的值。缺点：查阅值需要位于查找区域的第一列。

=VLOOKUP（找谁，在哪儿找，第几列，匹配方式）

总结：

1.带辅助列的index + match 函数的方法最快
2.不带辅助列的index + match 函数的方法较快且不需要辅助列，方便，推荐使用
3.lookup速度更慢，且如果不对物料资料按物料编码先进行排序的话，取的数据会不准确

用lookup函数，如果用lookup查找前要对文字一列进行升序排序。然后就可以正确查找了

原因是Lookup函数采用的二叉树的查找方式，如果不排序，其运行机制将发生偏差，产生上述问题

4.vlookup速度第2慢  ，使用Vlookup +非连续列 最慢

vlookup函数则必须将源数据区域搜索主键列调换顺序 ，放在第1列

网上有2重vlookup的方法比较快，担前提也需要先对 物料资料表先按物料编码进行排序

5.另vlookup 也可搜索  非连续 的2列

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

IFERROR(VLOOKUP([@物料编码],IF({1,0},系统_物料基本信息表[物料编码],系统_物料基本信息表[物料名称]),2,FALSE),"")

另数据行数越多，所有使用公式的速度都会成比例下降

以上是2000多行数据

如果到4000多行数据，则新增行时速度会下降一半左右。