vlookup函数查找12种易犯错误.docx
- 文档编号:30037788
- 上传时间:2023-08-04
- 格式:DOCX
- 页数:10
- 大小:364.19KB
vlookup函数查找12种易犯错误.docx
《vlookup函数查找12种易犯错误.docx》由会员分享,可在线阅读,更多相关《vlookup函数查找12种易犯错误.docx(10页珍藏版)》请在冰豆网上搜索。
vlookup函数查找12种易犯错误
vlookup函数查找12种易犯错误
vlookup函数查找12种易犯错误(1~3)
2014-05-21 喜欢就点蓝字▶ 宁波公益精英讲堂
vlookup函数是一个非常好用的查找函数,但由于种种原因,在实际使用时会遇到种种让人搞不明白的错误。
于是我们把常遇到的vlookup错误问题来一次大整理,希望能对同学们有用。
一、函数参数使用错误。
第1种:
第2个参数区域设置错误之1。
例1:
如下图所示,根据姓名查找龄时产生错误。
错误原因:
vlookup函数第二个参数是查找区域,该区域的第1列有一个必备条件,就是查找的对象(A9),必须对应于区域的第1列。
本例中是根据姓名查找的,那么,姓名列必须是在区域的第1列位置,而上述公式中姓名列是在区域A1:
E6的第2列。
所以公式应改为:
=VLOOKUP(A9,B1:
E6,3,0)
----------------------------------
第2种:
第2个参数区域设置错误之2。
例2如下图所示根据姓名查找职务时产生查找错误。
错误原因:
本例是根据姓名查找职务,可大家注意一下,第2个参数B1:
D6根本就没有包括E列的职务,当然会产生错误了。
所以公式应改为:
=VLOOKUP(A9,B1:
E6,4,0)
----------------------------------
第3种:
第4个参数少了或设置错误。
例3,如下图所示根据姓名查找
错误原因:
vlookup第四个参数为0时表示精确查找,为1或省略时表示模糊查找。
如果忘了设置第4个参数则会被公式误以为是故意省略,按模糊查找进行。
当区域也不符合模糊查找规则时,公式就会返回错误值。
所以公式应改为。
=VLOOKUP(A9,A1:
D6,2,0)
或=VLOOKUP(A9,A1:
D6,2,)注:
当参数为0时可以省略,但必须保留“,”号。
vlookup函数查找12种易犯错误(4~6)
2014-05-22 喜欢就点蓝字▶ 宁波公益精英讲堂
书接上回,继续介绍vlookup函数查找常见错误。
二、数字格式不同,造成查找错误。
第4种查找为数字,被查找区域为文本型数字。
例4:
如下图所示根据工号查找姓名,查找出现错误。
错误原因:
在vlookup函数查找过程中,文本型数字和数值型数字会被认为不同的字符。
所以造成无法成功查找。
解决方案:
把查找的数字在公式中转换成文本型,然后再查找。
即:
=VLOOKUP(A9&"",A1:
D6,2,0)
-----------------------------------------------
第5种查找格式为文本型数字,被查找区域为数值型数字。
例5:
如下图所示根据工号查找姓名,查找出现错误
错误原因:
同4
解决方法:
把文本型数字转换成数值型。
即:
=VLOOKUP(A9*1,A1:
D6,2,0)
注:
如果你手工把文本转换成数值类型,就不必在公式中转换格式了。
-------------------------------------------
三、引用方式使公式复制后产生错误。
第6种没有正确的使用引用方式,造成在复制公式后区域发生变动引起错误。
例6,如下图所示,当C9的公式复制到C10和C11后,C10公式返回错误值。
错误原因:
由于第二个参数A2:
D6是相对引用,所以向下复制公式后会自动更改为A3:
D7,而A10中的工号A01所在的行,不在A3:
D7区域中,从而造成查找失败。
解决方案:
把第二个参数的引用方式由相对引用改为绝对引用即可。
B9公式改为:
=VLOOKUP(A9,$A$2:
$D$6,2,0)
vlookup函数查找12种易犯错误(7~9)
2014-05-23 喜欢就点蓝字▶ 宁波公益精英讲堂
前面讲了6种vlookup常见的查找错误类型,今天接着说。
四、多余的空格或不可见字符
第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列进行分列操作(数据-分列),即可把不可见字符分离出去。
在日常vlookup函数查找错误示例中,第7种是最常见的一种错误之一。
同学遇到vlookup查找错误时,如果参数设置没有问题,就看一下数据表中有没有多余的空格。
vlookup函数查找12种易犯错误(10~12)
2014-05-25 喜欢就点蓝字▶ 宁波公益精英讲堂
不知不觉,我们已学完了vlookup函数的前9个易错知识点,好多同学说很实用。
谢谢同学们的鼓励。
今天是最后三个知识点了。
第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,))
vlookup函数常见的12种错误我们介绍完了,掌握了这些技巧可能在以后的工作中不再会被vlookup公式困绕了。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- vlookup 函数 查找 12 犯错误