vlookup报错的6种类型和解决办法——九数云BI
本文介绍了一些vlookup函数报错的类型和解决方法,希望可以帮到你!如果你不想使用复杂的excel vlookup函数,可以使用九数云BI进行更便捷的数据分析工作
我们在日常工作中经常用到vlookup函数,但是vlookup函数脾气比较大,不仅硬性条件多,还动不动就vlookup报错显示#N/A或#REF;更过分的是有时候一不注意,数据查错了,这不闯大祸。九数云BI作为一款便捷型的数据分析软件,今天为大家梳理了vlookup函数公式报错的最全原因!对照着检查,一定能找到问题!
在开始检查vlookup报错前,首先我们来回忆一下vlookup的四个参数:=VLOOKUP(查找对象,查找范围,返回列数,精确匹配或者近似匹配),咱们先从四个参数下手,看看公式是否有错误。
一、参数1:查找对象
查找对象不能绝对引用
- vlookup函数报错示范
在F2输入公式=VLOOKUP($F$2,$B$2:$D$11,3,1),向下复制填充,三个单元格中返回的值都是92,显然是错误的
- 错误原因:
参数1查找对象进行了绝对引用,复制填充时,单元格一直引用的F2的数据,查找的是“李婷”对应的成绩
- 修改方法:删除绝对引用
=VLOOKUP($F2,$B$2:$D$11,3,1)或=VLOOKUP(F2,$B$2:$D$11,3,1)
二、参数2:查找范围
1、查找范围没有包含查找值
- vlookup函数报错示范:
在F2输入公式=VLOOKUP($F2,$B$2:$C$11,3,1),公式报错#REF!
- 错误原因:
查找范围必须包含我们需要的查找值,比如上面要查找分数,但是我们引用的范围:B2-C11,不包含D列的分数
- 修改方法:
重新选择查找范围,包含分数列:=VLOOKUP($F2,$B$2:$D$11,3,1)
2、查找范围没有绝对引用
- vlookup函数报错示范
在F2输入公式=VLOOKUP(F2,B2:D11,3,1),有一个报错#N/A,且剩余几个出现错误,如周静的分数是95
- 错误原因
下拉填充时,相当于把公式往下复制,没有绝对引用,查找范围会随着单元格的变化而变化,导致函数无法在预期的范围内查找数据,从而返回错误的结果或#N/A
- 解决办法
按F4对查找范围进行绝对引用,或手动添加绝对引用符号$:【$B$2:$D$11】是一个绝对引用的正确示范
3、查找对象没在查找范围的第一列
- vlookup函数报错示范:
查找范围选择了A2-D11,返回错误#N/A或错误值
- 错误原因
vlookup硬性条件,查找值必须在查找范围的第一列,就本示例来说:即,我们按照姓名查找分数,那么姓名一定在查找范围的首列
- 解决办法
选择范围改为B2-D11,正确范围:【$B$2:$D$11】
三、参数3:返回列数
返回列数理解错误,返回列指的是:在参数2查找范围的第几列,而非表格的第几列
- vlookup函数报错示范
在输入第三个参数时,认为分数在表格的第四列,因此输入了4,返回错误值或#REF
- 错误原因
vlookup硬性要求:返回列数指的时在查找范围的第几列,而不是在表格的第几列;
示例中,返回“分数”列,是在表格的第4列,但是是在我们的查找范围(B-D)的第3列,因此应该输入“3”
- 解决办法:将参数3中的【4】修改为【3】
四、参数4:精确匹配或近似匹配
忘记第四个参数,或者分不清
vlookup函数报错示范:
如图我们查找值和查找范围为一对一关系,但是我们选择了TRUE近似匹配,导致返回错误值
- 错误原因
VLOOKUP函数的参数4为true或省略时,表示精确查找,false值时表示模糊查找。这里设置为true,进行的是模糊查找,模糊查找会找到和它最接近,但比它小的那个数。因此返回的数值不正确。
- 解决办法
改为精确查找false
PS :当两个表格能够完全一一对应时认准精确匹配
如果以上都没发现错误,那么恭喜你,公式用对了!
但是表格可能还存在一些格式傻瓜的问题,可以按照如下方法慢慢检查
五、查找值
1、查找值存在空格
vlookup函数报错示范:
公式写法没问题,引用方式也没问题,但出现错误#N/A
错误原因:
【陈 晨】中间有空格,excel将其识别为不同于【陈晨】的内容,导致查找不到其成绩;
(以上为示例选择了明显的空格,很多时候空格我们可能都看不出来,因此需要排查)
解决方法:
Ctrl+H调出【查找和替换】对话框,在【查找内容】文本框中输入空格,点【全部替换】按钮;最好中文、英文输入法都替换一次
2、查找值存在不可见字符
系统导出的数据源,很有可能存在不可见字符
解决方法:选择可能包含不可见字符的列,点击【数据】选项卡下的【分列】,并直接点击【完成】即可
六、查找范围
1、查找范围的格式不一致
vlookup函数报错示范:
下图根据学号查学生的分数,报错#N/A,
错误原因:B列为没有小数点的数值,而G列为文本型数值格式,导致excel认为两个内容不一致
解决方法:统一两个区域为一种格式
2、查询内容包含“*”“~”两个特殊符号
这两个符号含有“通配符”的意义,直接将其替换掉即可
以上就是一些vlookup函数报错的类型和解决方法,希望可以帮到你!如果你不想使用复杂的excel vlookup函数,可以使用九数云BI进行更便捷的数据分析工作,九数云BI提供多种简便计算方法,不再需要写复杂函数,做好一次分析后,所有的分析步骤都能留存,只需要更新数据源,结果就能自动更新!
上一篇: excel筛选数据的便捷方法,看这里——九数云BI
下一篇: 中小企业数字化水平提升与SAAS BI系统——九数云BI