【excel里的vlookup函数特别容易出错,有没有好的解决办法?】

office应用 徐 自远 544℃

【excel里的vlookup函数特别容易出错,有没有好的解决办法?】

大家平时在工作中经常用VLOOKUP查询数据,但是这个函数也有不听话的时候,小脾气上来了,就会返回错误值。咱们就来了解一下VLOOKUP函数返回错误值的原因和解决方法。

一. VLOOKUP函数基本语法:

=VLOOKUP (lookup_value, table_array,col_index_num, [range_lookup])

这个太复杂了哦,看鹅理解的VLOOKUP函数中文语法吧:

=VLOOKUP(查找值,数据源,第几列,模糊查找1/精确查找0)

变成人话,是不是更容易理解啊,哈哈。

二. 实例说话:

接下来,先看一个VLOOKUP函数的应用实例吧。

通过菜名查询库存数量。

C11单元格公式如下:

=VLOOKUP(B11,$B$3:$D$6,2,0)

这样的用法大家都很熟悉了,可是VLOOKUP函数也有不听话的时候,有时会返回错误值#N/A。这是怎么回事呢?咱们来看看出现的原因和解决方法:

第一种:数据源没有绝对引用。

公式拖动时,查找区域发送变化,导致找不到查询值。所以锁定查询区域尤其重要,否则就会查询不到而返回#N/A。

第二种:指定第三参数错误,也会返回错误值。

例如以下公式

=VLOOKUP(E11,$C$3:$D$6,3,0)

这里的查询区域只有C、D两列,而指定返回的列是3,明显超出查询区域范围,Excel就晕了,因此就会显示#REF!

第三种:查找值与数据源中的数据不一致。

1.有空格。

可以双击单元格,查看最后一个字符后面是否有空格或者是在编辑栏公式栏里看。

解决方案:

1) 复制一个数据源,粘贴在公式的查找条件里。

2) 直接通过函数TRIM去掉空格

C11=VLOOKUP(TRIM(B11),$B$3:$D$6,2,0)。

3) 如果ERP系统导出来的数据包含不可见字符,通过CLEAN函数处理一下,一般即可正常查询。

如:

=VLOOKUP(CLEAN(B11),$B$3:$D$6,2,0)

2.查询值和查询区域中的数据类型不统一,既有文本又有数值。

可以通过TYPE函数判断。

TYPY返回信息如下:

数值=1;文字=2;逻辑值=4;错误值=16;数组=64

这种情况下,只要将文本格式的数字转换成真正数字就可以正常查询了。

转换成数字的方法有很多种:

E11*1

E11/1

E11+0

E11-0

–E11

VALUE(E11)

使用时任选其中一种即可。

第四种,查询区域中没有查询值,所以显示#N/A。

通过=IFERROR(公式,””)可以将错误值屏蔽掉。

以上是VLOOKUP函数返回错误值的几种常见原因和解决方法,怎么样,你还能补充一下吗?

 

VLOOKUP函数是日常运用中最常用函数之一,之所以容易出错,一般是大量数据,多列查找,没有锁定数据引用范围;或者是查找数据的与输入值格式不一致造成。

VLOOKUP基本格式:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

基本格式是从左到右查找,即在数据源中,查找结果必须在输入值的右边。

上图是悟空问答标签,根据标签查找熵。见图,熵值在标签右侧,所以只要简单根据vlookup基本套路查找即可。

但是,如果我们按vlookup基本格式用标签来查找排序,就会出错了,这时可以引入一个排序函数{1,0},就可以达到效果。

vlookup函数如何查找右边内容?

上图,排序位于标签左侧,这时用排序函数先将二者顺序调换,再查找,即可查找出准确的结果:

=VLOOKUP(D3,IF({1,0},B:B,A:A),2,0)

学好vlookup等几个基本函数,了解其简单套路,那么就能解决日常大部分需求了。

 

一、函数参数使用错误。

第1种:第2个参数区域设置错误之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时可以省略,但必须保留“,”号。

兰色说:今天所介绍的1~3错误是最简单的查找错误,可能有些同学已能轻松处理,明天咱们继续介绍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查找错误。明天我们将继续介绍vlookup函数的其他查找错误。如果你也遇到了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列进行分列操作(数据 – 分列),即可把不可见字符分离出去。

https://www.wukong.com/m/wapshare/answer/brow/?ansid=6431385464673927425&iid=11984509841&app=news_article&tt_from=android_share&utm_medium=toutiao_android&utm_campaign=client_share

 

转载请注明:徐自远的乱七八糟小站 » 【excel里的vlookup函数特别容易出错,有没有好的解决办法?】

喜欢 (0)

苏ICP备18041234号-1 bei_an 苏公网安备 32021402001397号