office交流网--QQ交流群号

Access培训群:792054000         Excel免费交流群群:686050929          Outlook交流群:221378704    

Word交流群:218156588             PPT交流群:324131555

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

2020-05-31 08:00:00
zstmtony
原创
254

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多行数据,则新增行时速度会下降一半左右。

    分享