自从几年前微软推出Xlookup函数以来,网上的画风是这样的。
但Xlookup真能全面吊打Vlookup吗?至少在大数据量的实战面前,Xlookup还只是个小弟。
测试方式
Xlookup VS Vlookup
这是最直接的对比,10万行Vlookup平均花了0.4秒,Xlookup平均0.56秒,前者比后者足足快了40%!
Xlookup VS Index/Match
Index/Match组合,是另一个常用的查询函数,有时比vlookup更灵活。测试结果如下。
Index/Match组合平均0.41秒,略慢于Vlookup,但也明显比Xlookup更快(快了36%)。另一个组合方式,Index/XMatch,则比Xlookup还慢10%。这里似乎可以得出一个结论,即近年推出的"X"开头的升级版新函数,运行速度都比原版函数更慢。
Xlookup 的二级制搜索模式
Xlookup比Vlookup多了2个可选参数,其中一个是搜索模式。其中,如果搜索目标列是升序或降序排序,则可以选用二进制搜索模式。在这种情况下,该函数不会马上进行全列搜索,而是先将列按值的大小,一分为二,如果查找值在目标列的上一半,则进一步将上一半的数据一分为二继续查找,依此类推。因为每次都将目标列缩小了一半的范围,因此速度会比全局查找要快得多。实验也得到了验证,采用二进制模式,用时缩短了69%。
Vlookup也有自己的“快速”查找模式,即用模糊匹配取代精确匹配。我们再来看看Xlookup的二进制模式对比Vlookup的模糊匹配。Vlookup模糊匹配比精确匹配提速明显,从0.4秒下降到0.14秒,同时也比Xlookup的二进制模式略快。但优势已经不如Vlookup对比Xlookup常规模式那么明显。
二维匹配
即行和列的值都要匹配,这是VLookup无法实现的。Index/Match需要再加一个Match。对比下来,Index/Match/Match仍然比Xlookup快了近30%。
加上二进制搜索模式之后,Xlookup提速42%,反超Index/Match/Match。
未找到值
Xlookup比Vlookup多了另一个参数是:如未找到有效的匹配值,返回指定值。我们都知道,如果Vlookup查找不到对应值,会返回错误。这时需要外层套一个Iferror。Xlookup这个参数相当于内置了iferror。下图测试了三种情况:Xlookup使用内置的参数,Xlookup嵌套Iferror,Vlookup嵌套Iferror。结果是Xlookup无论用内置还是嵌套的形式,速度差异不大。但都比Vlookup嵌套Iferror慢了一倍以上。
结论
Xlookup操作上Vlookup更灵活,至少它不需要再去数目标列在哪个位置。但通过上述实战对比,我们可以清晰地看到,处理数以万计的数据时,X字辈函数(Xlookup、Xmatch)在它们的老前辈Vlookup、Match面前仍然不够看。全面替代Vlookup?不存在的。
本文分享自 PowerBI x Python 微信公众号,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ,欢迎热爱写作的你一起参与!