• 欢迎访问速搜资源吧,如果在网站上找不到你需要的资源,可以在留言板上留言,管理员会尽量满足你!

【Excel技巧】想学好VLOOKUP,一定不能错过这篇文章

Excel技巧 admin 2年前 (2019-08-03) 669次浏览 已收录 0个评论

在工作中,面对一张表格,我们经常需要做些查询工作。

比如,下图表格中,需要根据左边的部门人员清单,查找每个部门对应的所有人员姓名。

【Excel技巧】想学好VLOOKUP,一定不能错过这篇文章

这是一个典型的一对多查询问题,根据部门查找多个对应的姓名。

很多函数高数都会给出 INDEX+SMALL+IF+ROW 的公式组合,如下:

=IFERROR(INDEX($C$2:$C$11,SMALL(IF($B$2:$B$11=$E2,ROW($1:$10),4^8),COLUMN(A1))),””)

并且在公式编辑完成之后,还会「Ctrl+Shift+Enter」三键回车。

可是,作为一个大白,我完全看不懂复杂的函数公式。

稍有数据改动,就会束手无策。

那么,有什么其它简单易懂的方法吗?

答案是有的!

01

思路解析之 VLOOKUP

要解决这个问题,需要我们最熟悉的 VLOOKUP 函数。

但是 VLOOKUP 函数只会返回查找区域中,查找值第一个对应的值。

比如下图。

在查找人事部的人员姓名时,虽然林晨和张琴都是人事部门人员。

但是,VLOOKUP 函数只会返回「林晨」这一个值。

【Excel技巧】想学好VLOOKUP,一定不能错过这篇文章

02

思路解析之改变查找值

既然 ,VLOOKUP 函数只会返回第一个对应值,那我们只需要将查找值,变得不同就可以了。

比如说将后面出现的「人事部」变成「人事部 1」、「人事部 2」……

然后分别查找「人事部 1」,「人事部 2」对应的人员姓名就可以了。

所以,我们要分两步来解决这个问题。

第一步:将查找值「人事部」变成「人事部 1」和「人事部 2」

「人事部」三个字是不变的,变化的是用来区分不同的后缀:1 和 2。

简单说,就是固定的「人事部」连接(&)变化的「数字」构成新的查找值来实现查找值的不同。

在此例中,我们可以使用 COLUMN 函数,返回指定单元格引用的列号,来实现后缀的变化。

COLUMN 函数,返回指定单元格引用的列号。
例如,公式 =COLUMN(D10) 返回 4,因为列 D 为第四列。

下图中,在 F9 单元格输入以下公式:

=$E9&COLUMN(A1)

向右拖拽,由于单元格列号的变化,就会得到「人事部 1」,「人事部 2」,以此类推。

公式中要固定「人事部」,即 E9 单元格的列号要用「$」锁定。
「&」起连接作用。

【Excel技巧】想学好VLOOKUP,一定不能错过这篇文章

03

思路解析之改变查找区域

第二步,要在查找区域中有「人事部 1」,「人事部 2」。

在下面这张表格中,A 列「辅助列」是我们新增的查找区域。

如何根据原本的 B 列「部门」,来生成我们需要的辅助列,是这一步的关键所在。

【Excel技巧】想学好VLOOKUP,一定不能错过这篇文章

具体方法是:

在 A2 单元格输入公式:

=B2&COUNTIF($B$2:B2,B2)

向下填充即可。

【Excel技巧】想学好VLOOKUP,一定不能错过这篇文章

接下来,我们分析一下,公式为什么这样写。

首先,COUNTIF 函数,是用于统计满足某个条件的单元格的数量。

但这个公式的思路,不仅仅是使用 COUNTIF 函数,还是单元格的引用方式

【Excel技巧】想学好VLOOKUP,一定不能错过这篇文章

上图中 E9:E13 区域是第一次出现「人事部」,所以得到「1」;

E9:E17 区域是第二次出现「人事部」,所以得到「2」。

注意到这两个区域的开始部分都是 E9。

所以在生成辅助列的时候,开始区域是绝对引用,结束区域是相对引用

这样得到的次数才会递增。

【Excel技巧】想学好VLOOKUP,一定不能错过这篇文章

04

完成公式编写

经过上面几步的解析,我们已经完成了改变查找值改变查找区域两步。

但是,当公式在 H3,查找「人事部 3」的时候:

查找区域中并没有「人事部 3」,就会返回 #N/A 错误。

所以,还需要使用 IFERROR 函数,来屏蔽错误值。

最终 ,F1 单元格的公式是:

=IFERROR(VLOOKUP($E2&COLUMN(A1),$A$1:$C$11,3,0),””)

【Excel技巧】想学好VLOOKUP,一定不能错过这篇文章

呀,这么复杂的问题终于解决了!

05

总结

针对上面的问题,我总结出了两点结论

❶ 对于一些复杂问题,我们可以使用辅助列和比较熟悉的函数来解决。

❷ 写公式的时候,思路很重要。先有思路,才会有公式。


速搜资源网 , 版权所有丨如未注明 , 均为原创丨转载请注明原文链接:【Excel技巧】想学好VLOOKUP,一定不能错过这篇文章
喜欢 (0)
[361009623@qq.com]
分享 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址