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:\Users\gx\Desktop\[订单明细表.xlsx]数据'!$A:$G,
7,0)
最后,我朋友将包含VLOOKUP函数的工作簿发还给了对方,当然了,被VLOOKUP函数引用的自家工作簿是没有发送的。
这事看起来平平无奇,然后就坏菜了。
虽然我朋友没有将自家数据的工作薄发送对方,但由于VLOOKUP建立了数据缓存,因此,它实际上已经默默把相关数据打包带走了。
对方收到包含VLOOKUP函数的公式后,按照相关文件路径(哪怕文件并不存在),输入一个等号运算,即可获取全部数据。
动画演示如下▼
我这位朋友就在不经意间泄漏了公司数据,之后就被迫跳槽加薪升职去了。
耸肩摊手,今天给大家分享的内容就这些。看我小眼神,关于最后一条八阿哥,各位不妨测试一下,使用VLOOKUP跨工作簿引用数据,然后把那个工作簿删掉,并清空回收站……最后,你会发现,使用等号运算依然可以读取VLOOKUP缓存的数据
联系客服