Excel的VLOOKUP公式计算提速方法

你经历过二十万行数据从另外五十万行数据里用VLOOKUP匹配的绝望吗?动辄半小时白屏未响应,难道就没什么方法可以解决吗?Fear not, this article is just for you!

一劳永逸的解决方法

这种数量级的破事儿为什么要用Excel?直接上数据库写查询吧,专业的东西要用专业的工具。

当然了学习成本也许有点高

毕竟不是所有人都顺便学了SQL,而且有些时候真的直接用Excel会方便一些。难道就没有什么方法能加速VLOOKUP吗?

问题的症结

基本上这种时候出现问题的公式不外乎就是精确匹配的VLOOKUP:

因为但凡用过这公式的都懂如果模糊匹配得到的结果不一定对。

问题也发生了,这个破公式在精确匹配的时候,会不厌其烦地逐字逐行匹配到底,数量少还行,一旦数量到了十万vs十万这个级别,一般企业的垃圾办公电脑跑起来就很吃力了。

时间差不多能减半的处理方法

其实用VLOOKUP也会有不爽的时候,那就是原始数据里,需要取的数据在匹配数据的左边。而VLOOKUP只能向右取值。于是用另外两个公式的combo可以完美解决这种情况,同时效率会有些许提升:

MATCH用来返回查找值在目标数组当中的位置,第三个参数0为精确匹配。也就是说,这个函数自己,可以直到你要找的值在匹配数据的第几行。

没错,INDEX就是用来取给定位置的数据。

所以把文章第一个公式用这两个重写一下,就是:

lookup_value是需要匹配的值,lookup_range是包含需要匹配的原始数据的那一列,array_range是需要取值的那一列。

效果几乎完全等同于VLOOKUP,运行效率稍微高一些,但不是翻天覆地的变化。

奇技淫巧

如果你的原始数据可以排序,比如是一堆纯数字的序列号,可以直接按照数字升序排序,那你中奖了。因为VLOOKUP的模糊排序(第三个参数为TRUE)是飞快的,就是稍微比较一下数据,然后返回离查找值最接近的那个。如果有完全一致的数据,那么VLOOKUP返回值就是查找值,也就是IF公式的第一条。所以这一条就是单纯看看有没有“精确匹配”,如果返回值和查找值不同,说明没有,如果相同,说明有。因此这个判断条件只需要比对查找值和可能包含查找值的那一列而已,飞快。

如果有“精确匹配”了,就正常模糊匹配,飞快,而且一定是对的。如果没有,就返回#N/A。一整个公式和一个VLOOKUP精确匹配的效果完 全 一 致。

前提是,用于匹配出目标值的原始数据必须是排好序的。

当然了,目前我测试过纯文本匹配,排个序也可以正常匹配,理论上没什么问题。

有多快呢?10分钟->1秒。

Share

发表评论

电子邮件地址不会被公开。 必填项已用*标注