VLOOKUP是EXCEL中非常经典的查询函数之一。
我们经常会用到函数VLOOKUP帮助匹配两张表的数据,尤其是数据量非常庞大的时候,只要写下一个标准格式公式,按下回车。脑子都不用动一动,就能够轻轻松松让电脑自动完成工作了。
然而,尽管大多数情况下,VLOOKUP都能帮我们顺利完成工作,但偶尔也会罢工。明明公式很正确,但就是显示错误。看着屏幕上的结果——'#N/A'——相当闹心。
真是让人又爱又恨的函数。
怎样才能快速排查掉VLOOKUP的错误?
一般错误出现在两个方面:
1.参数设置错误
2.关键字错误
下面将以这位名叫32135兄弟(临时昵称小三)的典型性案例,一步一步进行说明排查步骤。
小三有两张表,其中表1有数据200行,表2数据有800行。
他想根据关键字A列,将表2连接到表1中,试了很多次,始终不成功,显示N/A报错,问题在哪里?
第一步:检查公式参数设置是否正确
根据office官方给出的VLOOKUP函数使用说明,概括参数设定如下:
小三兄弟的公式为:
VLOOKUP(A2,'[表2.xlsx]Sheet1'!$1:$800,2)
观察参数设定:
我们发现标红底白色的两部分,有两处并未按照要求进行设定。
数据区域单元格地址,只指定了行号,并没有指定列号,所以系统会认为指定区域不明确,拒绝执行公式运算,因此会报错;
(((0)))
根据案例的要求,需要精确匹配关键字,所以要设定为精确匹配参数'FALSE'。
于是将公式修改为:
VLOOKUP(A2,'[表2.xlsx]Sheet1$A'!$1:$A$800,2,FALSE)
如果公式和链接的表名都是正确的,如果仍然报错N/A,怎么办呢?
第二步:检查两张表关键字列是否一致
检查两个方面:一是检查关键字的内容,二是检查关键字的格式
具体做法是下拉公式,如果其他行可匹配,则说明此项在表2中无相关匹配项,需要去补充表2的数据;
否则全部都匹配不上显示N/A,则说明关键字两边不一致,需要人工复查数据,调整关键字保持一致。
观察表1,尽管有大部分显示'#N/A',但仍然有个别行是可以从表2匹配进来的。小三人工检查过,那些没有匹配进来的项目,在表2中是可以找到相关数据的。
所以不需要对表2进行补充。
为何关键字没有错,却仍然找不到匹配项?
经了解,原来表1红圈圈中匹配过来的数据,是由于小三无意识的操作。
他以为是关键字两边不匹配,所以从表1复制了关键字到表2,敲了一下回车键,就自动匹配到了表1中。
难道真的是关键字内容不同吗?
当然不。聪明的你看到标题也猜到是格式的问题。
没错,正是两张表关键字的格式不一致,才是造成表2无法匹配到表1真正原因。
观察表2的关键字列,会发现在每个单元格左上角有一个绿色的小三角,而观察表1是没有的。有绿色三角的单元格是文本格式,无法参与计算。因此,要将表2的关键字单元格改为数值形式。
你是不是以为接下来做的是”全选第一列,调整单元格格式为数值形式”?
如果你是这样想,结果会令人失望的。
这种常规修改格式的办法,并不能解决这个问题。
刚才在上面提到,小三无意中,复制了一个单元格进来,敲了回车,转换成了数值,就可以运算了。
一共800行,难道要一个个复制敲回车吗?
亚!美!爹!
(码字累了,请允许表哥皮一下O(∩_∩)O~)
因为没有原表,所以表哥自己做个小例子示意。
选中需要修改格式的关键字单元格区域(注意不是全选),会出现黄底黑色感叹号,点击旁边下拉三角,选择'转换为数字',即可瞬间批量修正。
表哥Tips:以上就是VLOOKUP排查错误的步骤和方法。
你学会了吗?
↖(^ω^)↗撒花
联系客服