上期我们说了 VLOOKUP 小姑凉找不到对象,不不不,是她闹#N/A 小脾气的各种缘由,本期我们继续聊聊她的其他小脾气。
首先我们还是先对她的小脾气做下说明:
❶ #REF!错误:引用了无效的单元格,会出现该错误。
❷ #VALUE!错误:公式中引用了错误参数或数值,会出现该错误。
换句话说:
如果出现#N/A 错误,大家应该从数据上去找原因。
如果出现以上两种错误,大家应该追踪一下公式哪里写错了。
下面我就继续举几个例子进行说明一下吧。
01
#REF!错误
◆ ◆ ◆
错误解析
在此例中,C1:D10 这个数据区域只有两列,VLOOKUP 函数的第三参数却要求返回第 3 列的数据,超过了查找区域的最大列,所以返回#REF!错误。
解决方法
D14 单元格正确公式应为:
=VLOOKUP(C14,C1:D10,2,0)
02
#VALUE! 错误
◆ ◆ ◆
错误解析
我们知道 VLOOKUP 函数的第三参数必须要介于(1~查询区域的最大列),而此例中第三参数小于 1,所以返回#VALUE! 错误。
解决方法
D14 单元格正确公式应为:
=VLOOKUP(C14,C1:D10,2,0)
03
结果就是不对
◆ ◆ ◆
以上小情绪我们还能摸得着症结,但有时候小姑凉还会恶作剧:能够返回结果,但返回的结果是错的,不信你往下看。
(1)问题:第 4 参数近似匹配
错误解析
在此例中,武汉分部的销售额应该是 40846,但公式返回结果却是 83070,经过检查发现是 VLOOKUP 的第四参数使用了近似匹配。
解决方法
D14 单元格正确公式应为:
=VLOOKUP(C14,C1:D10,2,0)
(2)问题:查找值中包含通配符
错误解析
在此例中,10*20*50 产品型号的库存应该是 236,但公式返回结果却是 479。这是因为查找值中包含通配符「*」,而 VLOOKUP 支持通配符查询,导致查找到的是「10*200*50」所对应的库存数 479。
在 Excel 中有三个通配符:*、?、~,用法如下图所示:
当查找值中包含这三个字符时,VLOOKUP 直接查找可能会返回错误的结果,所以在本例中需要将查找值中的「*」替换为「~*」,取消「*」作为通配符的特性。
解决方法
D14 单元格正确公式应为:
=VLOOKUP(SUBSTITUTE(C14,'*','~*'),C1:D10,2,0)
虽然小姑凉的脾气够折腾人,不过我们也分析得差不多了,最关键的就是找对症结,然后对症下药好好哄啦~
担心哄不好?后台回复关键词【VLOOKUP】,下载练习文件,对着这两次的纠错宝典好好练习吧!
秋叶 Excel
◆ ◆ ◆
在秋叶 Excel 中,我们特意制作了「精华文章分类宝典」供您查阅。宝典分类里,有近百篇详尽的教学文章,随时随地为你解决问题。
进入公众号,点击菜单栏中的【快速学习】,就能找到它啦。
◆ 好文推荐 ◆
▌关于本文
作者:Excel 研究院—李大饼
联系客服