Excel 中的 VLOOKUP 函数出错该如何解决?

原文标题:《再见了,VLOOKUP!这个被无数人吹上神坛的函数,差点让我丢了工作?》

Vlookup 有个特点,上手容易精通难。

我们平时经常会用它进行数据查找,毫不夸张地说,学会并且学好它能够解决工作中 50% 左右的工作。

不仅能提高工作效率,还能成为同事心目中的大神。

但是,就是这么一个函数,差点让我丢了工作。

怎么回事呢?

前些天,我收到一张报表,需要从【子公司报表】中查找「资产」项目对应的金额,填入到【合并报表】这个工作表。

我想,这还不简单,用 VLOOKUP 函数 V 一下不就好了,却没想到,在实际使用的过程中,大名鼎鼎的 VLOOKUP 却出错了……

原凶到底是谁?

今天我们就一起来揭秘~

分析问题

【合并报表】图示:

【子公司报表】图示:

公式如下:

=VLOOKUP(A4子公司报表!$A:$B,2,0)

公式解析:

使用 V 函数在子公司报表 A 列中查找【A4】单元格的内容(即:货币资金),并返回子公司报表 B 列中对应的数据。

乍一看,两张表的项目名称几乎是一样的,为啥还会出现乱码#N/A 呢?

遇到这种情况,别着急,我们先用下面的方法检查一下。

使用等于符号(=)将两个单元格的内容进行比较。比如:将【合并报表】中【A4】单元格的内容与【子公司报表】中的【A5】的内容进行比较。

如果两者内容相同,就返回 TRUE,如果不相同,就返回 FALSE。

图中的结果返回 FALSE,说明两个单元格内容不完全一样。

此时,我们还可以进一步检查【合并报表】中【A4】单元格编辑栏中的内容:

果不其然,A4 单元格里的数据不像表面看起来那么简单!里面多了一些空格字符!

我们再来看看子公司报表中【A5】单元格编辑栏中的内容:

同样有数量不等的空格字符。

这个时候,问题的原因找出来了,后面就好解决了!

解决问题

解决此类空格字符,最常见的方法是使用 TRIM 函数去除。

方法

在【A4】单元格和【子公司报表!A:B】单元格区域前面同时添加一个 TRIM 函数,用来同时去除这两个地方单元格中的空格。Vlookup 函数就不会出错了!如下图:

公式如下:

=VLOOKUP(TRIMA4),TRIM子公司报表!A:B),2,0)

不过,此处因为使用了 TRIM 函数,返回的结果变成了文本型数字。

如果想要变成真正的数字,需要再进行一个四则运算。

比如,我们添加两个负号(--),就会变成真正的数字了!

方法 ❷

方法 01 在第一参数和第二参数两处都使用了 TRIM 函数,导致大量的运算,在数据很多的情况下,会导致表格卡顿。

我们还可以只在第一参数中使用【TRIM 函数 + 通配符】相结合的方法解决这个问题。此时运算效率会更高!

公式如下:

=VLOOKUP("*"&TRIM(A4子公司报表!A:B,2,0)

公式解析:

使用 TRIM 函数去除【A4】单元格中多余的空格,然后在其前面连接一个通配符来匹配任意多个字符(包括空格在内的任意多个字符)。

意思是:无论【子公司报表】中的【A 列】中那些单元格前面有多少个空格,通配符都可以匹配。

这样的话,V 函数就可以顺利的查找出来所要的数据了!

而且这样查找出来的也是真正的数字,并且不需要进行下一步的数学运算。

知识扩展

大部分情况下,上面的问题都是人为手动输入空格来进行项目名称的缩进对齐,而且每次输入空格的数量并不完全相同,才会导致查找出错!

为了避免这类问题的发生,我们可以通过【开始】选项卡中的【增加缩进量】的这个功能来实现项目名称的缩进对齐。

比如:

可以先用查找替换的方法,将两个表中的多余空格全部去除,

然后选中这些项目,点击【开始】选项卡中的【增加缩进量】按钮即可。

即方便又快捷!

而且不会出现查找出错的情况,这样就规范了所有的表格!

写在最后

今天,我们分享了鼎鼎大名的 V 函数查找出错的一种非常常见的原因 —— 手动输入多余的空格,导致查找引用错误。

除了空格之外,还有一些不可见字符(非打印字符),比如:制表符,换行符等等。

想要去除这些非打印字符,我们一般用 CLEAN 函数。

另外,还有一些从网页或者从系统里面导出来的数据,使用 TRIM 函数和 CLEAN 函数可能都不能解决问题。

在此种情况下,可以使用文本截取类函数(MID,LEFT,RIGHT)等来取出需要的数据,然后再作为 V 函数的第一参数进行查找,就不会出错了!

本文来自微信公众号:秋叶 Excel (ID:excel100),作者:明镜在心,编辑:竺兰

广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,IT之家所有文章均包含本声明。

文章价值:
人打分
有价值还可以无价值
置顶评论
    热门评论
      文章发布时间太久,仅显示热门评论
      全部评论
      一大波评论正在路上
        取消发送
        软媒旗下人气应用

        如点击保存海报无效,请长按图片进行保存分享