VLOOKUP函数的这三个BUG，越早知道越好

HI，大家好，我是汪站长。

 

众所周知，VLOOKUP是Excel中最常使用的函数之一，它对工作的帮助如此之大，以至于博了个大众情人的称号。

 

但这函数也有很多臭毛病。

 

有些是广为人知的，比如，查找值必须在查找范围的首列、文本数值和纯数值彼此不相等，默认的匹配方式是模糊而非精确等。

 

除此之外，还有一些并不被人所知，乍看就像八阿哥。

跟我读 我是阿哥，我是BUG👇

今天给大家分享三条，最后一条，不注意的话，甚至有被公司开除的危险。挑眉，骗你娶你，不论男女。

 

先说第一条。

 

如下图所示，A:D是数据明细，C列的系数总分是A列和B列两列的得分相加，比如，C2单元格的公式为：

=A2+B2

 

现在需要据此查询F列总分对应的人名。

 

在D2单元格输入以下公式：

 

=VLOOKUP(F2,C:D,2,0)

结果发现公式返回了错误值，表示C列查无0.204。

 

但C列明明有0.204（C2单元格），在G2单元格输入公式：

=C2=F2

 

它返回了逻辑值TRUE，表示两个单元格的值是相等的。

 

……

 

问题症结所在，是不同函数或公式对浮点误差的态度不一样。

说一下啥是浮点误差。

 

计算机是二进制，人类的数学是十进制。Excel在对数值进行运算的时候，不管是加减乘除还是乘幂，都需要先将十进制转换为二进制，计算完了，再转换成十进制呈现出来……换来换去，特别是小数运算部分，会有中间商赚差价，就产生了浮点误差。

不同函数对浮点的计算精度不同，等号只比对数值的15位精度，它们认为0.204和0.203+0.001是相等的。

 

输入公式：

=0.203+0.001=0.204

返回结果为TRUE。

 

但VLOOKUP函数的计算精度要高于等号，远超Excel明确呈现出来的15位，它就认为0.203+0.001和0.204两者不相等：

 

就这么回事。

 

有朋友会想，既然VLOOKUP这么娇贵，换INDEX+MATCH组合函数吧——换了也没用，MATCH和VLOOKUP一个德行，它也会返回错误值。

 

也不要去指望灭霸XLOOKUP，它也有一样的臭毛病。

 

这事可以换用LOOKUP函数，LOOKUP采用的等号匹配机制，可以避免过于严格的浮点精度匹配问题。

 

不过最好还是从源头修正错误。既然VLOOKUP等函数的计算精度高于15位，那咱们可以只保留小数点后4位，其它的全部砍掉。

 

摊手，你看，从源头上把人解决了，问题自然也就解决了不是？

在C列的系数总分公式外增加一个ROUND函数进行修约：

 

=ROUND(A2+B2,4)

 

VLOOKUP就可以正常运算了。

 

小贴士💡

 

天秤座等式并不忽略浮点误差，它只是比对精度未超过15位，当你在单元格中输入以下公式，会返回结果为FALSE。

 

=4.1-4.2+1=0.9

 

 

因此，当你在Excel中处理的数据包含数学运算，特别是小数运算时，请务必讲讲武德，使用ROUND函数修约处理，避免让大众情人甚至天秤座发脾气，切切好自为之。

 

……

 

再说下VLOOKUP第2条不广为人知的臭毛病。

 

VLOOKUP函数的查找值是有长度限制的，最大长度是255个字符。这事咱们长话短说，毕竟一般人确实不会遇上。

 

我举个例子。

 

如下图所示，A列是诗词的内容，B列是名字，需要根据E列的内容查询对应的诗歌名字。

 

F2输入以下VLOOKUP函数公式，会返回错误值，表示查无结果。

 

=VLOOKUP(A2,A:B,2,0)

但是，当你在F2单元格输入公式

=E2=A3

会发现公式计算结果为TRUE，表示E2和A3两个单元格的值是相等的。

 

当查找值的长度超过了255，VLOOKUP就会撒泼，她不管三七二十一，全部返回错误值——当然了，如果长度没有超过255，她还是很淑女的。

 

怎么解决呢？

 

如果你用的是365或WPS，可以换用XLOOKUP函数：

 

=XLOOKUP(E2,A:A,B:B)

低版本可以换用LOOKUP函数：

 

=LOOKUP(1,0/($A$2:$A$6=E2),$B$2:$B$6)

……

 

打个响指，说最后一条。

 

微软是这么说的，从2016版开始，对VLOOKUP/HLOOKUP/MATCH的运算机制进行了强力优化，从相同表区域查找多个列时，将为所搜索的列范围创建内部缓存索引，后续查找中，将重用这一缓存的索引。

 

上面这句话是什么意思呢？简单而言，就是VLOOKUP对引用类数据建立了缓存，当它运算时，不会反复去读取引用，而是直接使用缓存数据——

这有两点好处。

 

一个是极大提高函数的运算效率，你甚至会发现，高版本的VLOOKUP计算几十万行数据都不费吹灰之力。另外一个是，VLOOKUP可以在不打开数据源工作簿的情况下，跨工作簿读取数据。

 

但……也有不好的一面。

 

我曾经有个朋友，做结算工作。有家公司给她发了个对账单的工作簿，她在里面写了条VLOOKUP函数，引用自家公司工作簿的数据，比对两者是否一致。

 

如下图所示，C列是A司金额，我朋友在D列输入以下VLOOKUP函数得到自家的金额，然后在E列两者相减，即可获取差异。

 

=VLOOKUP(A2,'C:UsersgxDesktop[订单明细表.xlsx]数据'!$A:$G,7,0)

最后，我朋友将包含VLOOKUP函数的工作簿发还给了对方，当然了，被VLOOKUP函数引用的自家工作簿是没有发送的。

这事看起来平平无奇，然后就坏咯。

 

虽然我朋友没有将自家数据的工作薄发送对方，但由于VLOOKUP建立了数据缓存，因此，它实际上已经默默把相关数据打包带走了。

 

对方收到包含VLOOKUP函数的公式后，按照相关文件路径(哪怕文件并不存在)，输入一个等号运算，即可获取全部数据。

 

动画演示如下▼

我这位朋友就在不经意间泄漏了公司数据，之后就被迫跳槽加薪升职去了。

 

耸肩摊手，今天给大家分享的内容就这些。看我小眼神，关于最后一条八阿哥，各位不妨测试一下，使用VLOOKUP跨工作簿引用数据，然后把那个工作簿删掉，并清空回收站……最后，你会发现，使用等号运算依然可以读取VLOOKUP缓存的数据

有啥问题可以在VIP会员群中提问交流，挥挥手，明天再见。

END

---

往期推荐 1. Office365/2021/2019/2016/2013/2010官方永久激活密钥（可绑定账号） 2.  软件库（软件下载） 3.  CorelDRAW 2021破解版下载&安装步骤 4.  Google Earth Pro 7.3 |（谷歌地球）安装教程 5.  最新行政区划、乡镇级、村级shp矢量地图、2022道路、水系、建筑轮廓

Office自学网公众号 Office自学网客服

打开微信扫一扫，Office自学网

专注分享软件安装和视频教程，让你一号在手，办公无忧！