关于vlookup,你可能还不知道的15件事

流浪铁匠

郑重声明,本贴从第7条起已不适合新手阅读,避免对vlookup这个函数的认知与应用发生偏差
本文主要是我对这个函数及其相关性质与涉及字符的一些疯狂测试的特殊结果应用与结论
主要针对人群为函数发烧友
新手请不要由于这些性质对这个函数的理解发生误解
工作中vlookup函数还是很实用而且准确性很高的(前提是相对熟悉这个函数的普通性质(本文顶多1-2条属于这部分范畴))

1,vlookup的1参为文本时,字符数不能超过255个,否则返回vaule错误


2,vlookup的1参为数值时,对数值精度的识别是高于15位的(浮点精度)

3,vlookup忽略大小写的性质很多人都知道,但估计很少有人知道一共支持忽略1835组左右的字符的所谓大小写性质,除了英文字母外希腊/罗马/西里尔文等等乃至个别汉字都可被vlookup相互识别
所以会出现以下几个有趣的查找情况


4,excel存在多个小写字母转为相同大写字母的情况,但vlookup(与其他忽略大小写能力)的函数,仅能相互识别其中大写字母可以转化的那个小写字符        不识别其他能转成该大写字母的小写字符        


由于upper和proper的小写转大写能力有个别差异
有时存在个别多个小写能转为相同大写的情况
此时vlookup只能相互识别该大写字符能用lower或者proper转化的对应小写字符

5,虽然vlookup在4参为0或false时为精确查找,但排除上述2点后还是有至少8个字符是vlookup自己找不到自己的(排除通配性的影响)



6,上述字符中甚至至少还有1个是vlookup自己找不到自己但是能用别的字符识别自己的……







补充内容 (2018-10-15 21:20):
据群友测试反馈,5/6的性质在365版本无效，但2016测试是存在该性质的,365不愧是永远最新的版本

流浪铁匠

7,说到通配性,有2个特殊字符用于vlookup的1参也具有类似通配符的性质,而且这2个字符是默认右对齐的,我把这类字符称为镜像字符


8,除了上述特殊字符,unichar(173)作为vlookup的1参可以精确查找下识别3709个uncode字符(不含通配符)


9,更厉害的是char(1),作为vlookup1参,可以找到全部字符,测试表明这个字符的性质类似 ”* ”,但具有一定局限性,可能要在单元格内先键入该字符激活其所谓的通配性


10,vlookup由于各种因素造成查找结果出错时,错误值的优先级为1参本身为错误值(结果返回该错误值) > 找不到对应时的 #N/A 错误 >超出查找范围的 #REF! 错误
注:本条仅考虑较为常见的查找出错原因,后面有条更极端


11,vlookup在1/3/4 参数为数组时，在数组维度一致时的数组计算的优先级为 4参>1参> 3参

即先识别4参的查找方式再识别1参的常量数组(区域数组方式录入的伪内存数组,否则需t/n+if(1转化),最后才识别3参的列数数组


12,vlookup的3参是支持小数的,临界舍入点和index/round等函数一致为0.99999976146501这个特殊值


13,vlookup的4参也是支持小数的,一般有人会告诉你们0等价false,非0等价true,但其实足够小的数值就会被识别为false,不信你就试试9^-323


14,对于unicode编码大于65535的特殊字符,mid提取一半配合通配符在vlookup进行查找时确实是以编码而不是字符进行查找的
但如果是left/right进行提取,识别的还是字符


15,vlookup的3参为负数时的错误值类型发现
帮助内提示返回value错误
数值足够大时返回num错误,这2个都好理解
但是,大约在-1e9 至 -1e10 范围之间,错误值开始会变成ref错误
这个错误值直到达到excel的最大数值上限才变成num错误
即3参为大约在 -1e10至 -1e308范围,vlookup的结果为ref错误
而这个错误值与引用有关,这让我怀疑vlookup曾经可能被设计为支持反向,(纯粹瞎猜,有朋友说是vba的数据类型的原因,但除非微软进行说明,无法理解这部分返回的错误值为ref类型的原因)

七夕、

别具匠心的老铁，像个Excel说书人、

流浪铁匠

以上为这段时间对这个函数的各种发现
一堆旁门左道
因为有些内容更趋向于bug或者特殊情况，
但是,这些内容可能会对这个函数的理解有更深入的帮助

laowuwyx

流浪铁匠 发表于 2018-5-5 22:45
以上为这段时间对这个函数的各种发现
一堆旁门左道
因为有些内容更趋向于bug或者特殊情况，

佩服专研精神！

q447096568

很佩服你的钻研精神。

对于99%的用户来讲，excel只是工作中的一个工具，是解决工作中的实际问题。楼主举得这些例子基本没有应用场景。

sdg1345

谢谢大佬分享

km122712

佩服！佩服！钻研精神值得学习。

ynsycd

谢谢分享,谢谢分享

kuangben8

楼主这么努力！感谢分享。