VLookup等方法在大量多列数据匹配时的效率对比及改善思路

Excel到PowerBI2021-01-10 13:45:19

VLookup无疑是Excel中进行数据匹配查询用得最广泛的函数,但是,随着企业数据量的不断增加,分析需求越来越复杂,越来越多的朋友明显感觉到VLookup函数在进行批量性的数据匹配过程中出现的卡顿问题也越来越严重。

那么,在数据量较大,需要批量进行数据匹配查找的情况下,是否有办法进行适当的改善,以提高数据的匹配查找效率呢?

以下用一个例子,分别对比了四种常用的数据匹配查找的方法,并在借鉴PowerQuery的合并查询思路的基础上,提出一个简单的公式改进思路,供大家参考。


一、测试数据

本次测试以微软罗斯文贸易数据库的订单表和订单明细表进行扩展,涉及数据概况及要求如下:

  • 订单表21581行(含标题)

  • 订单明细表17257行(含标题)

  • 要求将订单表中的“订单ID”、“客户”、“雇员”、“订购日期”、“到货日期”、“发货日期”等6列数据匹配到订单明细表中。

如下图所示:


二、测试设备配置

本次测试使用个人笔记本电脑进行,系统、软件及配置情况如下:

  • 笔记本型号:Thinkpad X250

  • 核心配置:CPU i5-5200U @2.20GHz(4核)+ 内存 8GB + 固态硬盘

  • 系统:Windows 7(64位)

  • Excel版本:2016(64位)

三、     测试方法

为尽可能减少相关程序及不同工作簿之间可能造成的干扰,分别对四种方法建立单独的工作簿,每次仅打开一个工作簿进行独立操作,如下图所示:

       公式法统一在第一行写上公式,然后统一向下扩展填充至所有行,从开始填充起计算至填充完成的时间,如下图所示:


四、4种数据匹配查找方法

1、VLookup函数,按常用全列匹配公式写法如下图所示:


2、Index+Match函数,按常用全列匹配公式写法如下图所示:


3、Lookup函数,按常用全列匹配公式写法如下图所示:


4、Power Query合并查询,按常规表间合并操作如下图所示:


五、4种方法数据匹配查找方法用时对比

经过分别对以上4中方法单独执行多列同时填充(Power Query数据合并法单独执行数据刷新)并计算时间,结果如下表所示:

从运行用时来看:

  • VLookup函数和Index+Match函数的效率基本一样;

  • Lookup函数在大批量数据的查找中效率最低,甚至不能忍受;

  • Power Query的效率非常高。


六、     对公式法的改进

考虑到仍有大量的朋友没有使用PowerQuery,我在想:

  • 是否有可能对公式进行一定程度的改进,以实现效率上的提升?

  • PowerQuery的合并查询效率为什么会这么高?

  • PowerQuery进行合并查询的思想是否可能借鉴用于公式查询?

在思考这些问题的时候,我突然想到,Power Query进行合并查询的步骤,其实是分两步的:

  • 第一步:先进行数据的匹配

  • 第二步:按需要进行数据的展开

也就是说,只需要匹配查找一次,其它需要展开的数据都跟着这一次的匹配而直接得到,而我们在前面用VLookup、Index+Match写公式的思路则是对每一个需要取的值,都是一次单独的匹配和单独的取值。也就是说,每次为了查找到一个数据,都需要从订单表的2万多条数据里搜索一遍,这种效率自然会很低。

那么,如果我们在公式中也可以做到只匹配一次,后面所需要取的数据都跟着这次匹配的结果而直接得到,那么,效率是否会大有改善呢?

再回头看Index+Match结合的公式,其中,Match函数用于确定所需要查找内容的位置,而Index用于提取该位置相应的值!那么,如果我们只用Match一次把位置先找出来,后面所有的列都直接用这个位置去提取相应的值,会怎样?

于是,我首先用Match函数构建一个辅助列,用于获取匹配位置,如下图所示:


然后,通过Index函数,直接根据辅助列的位置从订单表里读取相应的数据,如下图所示:


     分不同情况执行如下:

  • 单独填充位置列(Match公式列),用时约15秒;

  •  同时根据已匹配的位置列填充G:L列(Index公式全部列),用时约1秒(双击填充柄直接出现进度条,不出现“正在计算,##%”过程);

  •  位置列和其他数据列同时填充,用时约17秒,约为直接使用VLookup函数或Index+Match函数组合公式(约85秒)的五分之一


七、结论

       在批量性匹配查找多列数据的情况下,通过对Index和Match函数的分解使用,先单独获取所需要匹配数据的位置信息,然后再根据位置信息提取所需多列的数据,效率明显提升,所需匹配提取的列数越多,效率提升越明显。

当然,使用公式的方法,即使在一定程度上进行改进,和Power Query相比仍然有很大的差距。因此,在数据量较大,数据处理较为复杂的情况下,建议使用Power Query来进行


【▼更多精彩内容敬请关注▼】

Excel入门动画系列001-020

Excel 50+函数1个文件搞定

Power Query入门精选20篇

Power Query进阶精选20篇

Power Query实战精选20篇

M语言及函数入门精选20篇

数据透视基础入门精选15篇

 Power Pivot入门精选15篇 】