vlookup函数12种常见错误1Word文件下载.docx
- 文档编号:18210086
- 上传时间:2022-12-14
- 格式:DOCX
- 页数:10
- 大小:606.07KB
vlookup函数12种常见错误1Word文件下载.docx
《vlookup函数12种常见错误1Word文件下载.docx》由会员分享,可在线阅读,更多相关《vlookup函数12种常见错误1Word文件下载.docx(10页珍藏版)》请在冰豆网上搜索。
=VLOOKUP(A9,B1:
E6,3,0)
第2种:
第2个参数区域设置错误之2。
例2如下图所示根据姓名查找职务时产生查找错误。
错误原因:
本例是根据姓名查找职务,可大家注意一下,第2个参数B1:
D6根本就没有包括E列的职务,当然会产生错误了。
E6,4,0)
第3种:
第4个参数少了或设置错误。
例3,如下图所示根据工号查找姓名返回错误
vlookup第四个参数为0时表示精确查找,为1或省略时表示模糊查找。
如果忘了设置第4个参数则会被公式误以为是故意省略,按模糊查找进行。
当区域也不符合模糊查找规则时,公式就会返回错误值。
所以公式应改为。
=VLOOKUP(A9,A1:
D6,2,0)
或
D6,2,)注:
当参数为0时可以省略,但必须保留“,”号。
二、数字格式不同,造成查找错误。
第4种
查找为数字,被查找区域为文本型数字。
例4:
如下图所示根据工号查找姓名,查找出现错误。
在vlookup函数查找过程中,文本型数字和数值型数字会被认为不同的字符。
所以造成无法成功查找。
解决方案:
把查找的数字在公式中转换成文本型,然后再查找。
即:
=VLOOKUP(A9&
"
A1:
第5种
查找格式为文本型数字,被查找区域为数值型数字。
例5:
如下图所示根据工号查找姓名,查找出现错误
同4
解决方法:
把文本型数字转换成数值型。
=VLOOKUP(A9*1,A1:
三、引用方式使公式复制后产生错误。
第6种
没有正确的使用引用方式,造成在复制公式后区域发生变动引起错误。
例6,如下图所示,当C9的公式复制到C10和C11后,C10公式返回错误值。
由于第二个参数A2:
D6是相对引用,所以向下复制公式后会自动更改为A3:
D7,而A10中的工号A01所在的行,不在A3:
D7区域中,从而造成查找失败。
把第二个参数的引用方式由相对引用改为绝对引用即可。
B9公式改为:
=VLOOKUP(A9,$A$2:
$D$6,2,0)
四、多余的空格或不可见字符
第7种
数据表中含有多余的空格。
例7如下图所示,由于A列工号含有多余的空格,造成查找错误。
多一个空格,用不带空格的字符查找当然会出错了。
解决方案:
1手工替换掉空格。
建议用这个方法
2在公式中用trim函数替换空格而必须要用数据公式形式输入。
即:
=VLOOKUP(A9,TRIM(A1:
D6),2,0)按ctrl+shift+enter输入后数组形式为{=VLOOKUP(A9,TRIM(A1:
D6),2,0)}
第8种:
类空格但非空格的字符。
在表格存在大量的“空格”,但又用空格无法替换掉时,这些就是类空格的不可见字符,这时可以“以其人之道还之其人之身”,直接在单元格中复制不可见字符粘贴到替换窗口,替换掉即可。
第9种:
不可见字符的影响
如下图所示的A列中,A列看不去不存在空格和类空格字符,但查找结果还是出错。
出错原因:
这是从网页或数据库中导入数据时带来的不可见字符,造成了查找的错误。
在A列后插入几列空列,然后对A列进行分列操作(数据-分列),即可把不可见字符分离出去。
第10种:
反向查找vlookup不支持产生的错误。
例10如下图所示的表中,根据姓名查找工号,结果返回了错误。
vlookup不支持反向查找。
1用if函数重组区域,让两列颠倒位置。
=VLOOKUP(D8,IF({0,1},D2:
D4,E2:
E4),2,0)
2用index+match组合实现。
=INDEX(D2:
D4,MATCH(D8,E2:
E4,0))
第11种:
通配符引起的查找错误
例11,如下图所示,根据区间查找提成返回错误值。
~用于查找通配符,如果在vlookup公式中出现,会被认为特定用途,非真正的~。
如在表格中查找3*6,356,376也被查找到。
如果精确查找3*6,需要使用~,如下图所示。
用~~就可以表示查找~了。
所以公式可以修改为
=VLOOKUP(SUBSTITUTE(A8,"
~"
"
~~"
),A2:
B4,2,0)
第12种:
vlookup函数第1个参数不直接支持数组形式产生的错误
例12:
如下图所示,同时查找A和C产品的和,然后用SUM求和。
VLOOKUP第一个参数不能直接用于数组。
利用N/T+IF结构转化一下数组,如果不了解N/T+IF结构用法,可以参考。
公式修改为:
=SUM(VLOOKUP(T(IF({1},A8:
B8)),A2:
B5,2,))
一般出现“#N/A”的错误说明原始表第一列查不到第一参数的值。
这种错误值处理一般有两种方案:
1)核实查找值是否拼写错误,然后改正;
2)改成用通配符查询,如:
=VLOOKUP(A8&
*"
D5,2,0)。
如果第三参数,也就是返回结果的列数,超过了数据表最大列数或者小于1将出现#REF!
错误。
这种情况下,修改正确的返回值列数即可。
如果查找的值不在数据表的第一列,也会出现错误值。
如下图,【工号】信息在原始表的第二列,因此公式返回错误。
这种情况下,将【工号】列挪到数据表第一列或利用我另一篇经验的“VLOOKUP反向查找”的技巧解决。
如果查找的值为数字,并且返回错误值时,很可能的一种情况是查找值和原始数据表值的格式不一样。
如下图,B8单元格输入的是数字“111”,而A列存储的是文本型数字,两者不同,因此返回错误。
如上的错误,可以:
1)利用【分列】功能将文本型数字转化为数字;
2)把VLOOKUP的第一参数加上&
转换为文本。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- vlookup 函数 12 常见 错误