|
大家好，我是明天又要被上班支配的小 E。 面对这个事实，我只能…… 多些几篇 Excel 文章，来刷自己的存在感了。 话不多说，来学操作！ 在工作中，面对一张表格，我们经常需要做些查询工作。 比如，下图表格中，需要根据左边的部门人员清单，查找每个部门对应的所有人员姓名。 这是一个典型的一对多查询问题，根据部门查找多个对应的姓名。 很多函数高数都会给出 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」三键回车。 可是，作为一个大白，我完全看不懂复杂的函数公式。 稍有数据改动，就会束手无策。 那么，有什么其它简单易懂的方法吗？ 答案是有的！ 来，跟小 E 一起学习学习。 01 思路解析之 VLOOKUP 要解决这个问题，需要我们最熟悉的 VLOOKUP 函数。 但是 VLOOKUP 函数只会返回查找区域中，查找值第一个对应的值。 比如下图。 在查找人事部的人员姓名时，虽然林晨和张琴都是人事部门人员。 但是，VLOOKUP 函数只会返回「林晨」这一个值。 隔壁小美：你刚刚不是说，VLOOKUP 可以解决问题的吗，怎么又不行了？ 小 E：小美，你别心急啊，听我慢慢跟你 Suo。 02 思路解析之改变查找值 既然 ，VLOOKUP 函数只会返回第一个对应值，那我们只需要将查找值，变得不同就可以了。 比如说将后面出现的「人事部」变成「人事部 1」、「人事部 2」…… 然后分别查找「人事部 1」，「人事部 2」对应的人员姓名就可以了。 所以，我们要分两步来解决这个问题。 第一步：将查找值「人事部」变成「人事部 1」和「人事部 2」 「人事部」三个字是不变的，变化的是用来区分不同的后缀：1 和 2。 简单说，就是固定的「人事部」连接（&）变化的「数字」构成新的查找值来实现查找值的不同。 在此例中，我们可以使用 COLUMN 函数，返回指定单元格引用的列号，来实现后缀的变化。 COLUMN 函数，返回指定单元格引用的列号。 下图中，在 F9 单元格输入以下公式： =$E9&COLUMN(A1) 向右拖拽，由于单元格列号的变化，就会得到「人事部 1」，「人事部 2」，以此类推。 公式中要固定「人事部」，即 E9 单元格的列号要用「$」锁定。 03 思路解析之改变查找区域 第二步，要在查找区域中有「人事部 1」，「人事部 2」。 在下面这张表格中，A 列「辅助列」是我们新增的查找区域。 如何根据原本的 B 列「部门」，来生成我们需要的辅助列，是这一步的关键所在。 具体方法是： 在 A2 单元格输入公式： =B2&COUNTIF($B$2:B2,B2) 向下填充即可。 接下来，我们分析一下，公式为什么这样写。 首先，COUNTIF 函数，是用于统计满足某个条件的单元格的数量。 但这个公式的思路，不仅仅是使用 COUNTIF 函数，还是单元格的引用方式。 上图中 E9:E13 区域是第一次出现「人事部」，所以得到「1」； E9:E17 区域是第二次出现「人事部」，所以得到「2」。 注意到这两个区域的开始部分都是 E9。 所以在生成辅助列的时候，开始区域是绝对引用，结束区域是相对引用。 这样得到的次数才会递增。 04 完成公式编写 经过上面几步的解析，我们已经完成了改变查找值和改变查找区域两步。 但是，当公式在 H3,查找「人事部 3」的时候： 查找区域中并没有「人事部 3」，就会返回 #N/A 错误。 所以，还需要使用 IFERROR 函数，来屏蔽错误值。 最终 ，F1 单元格的公式是： =IFERROR(VLOOKUP($E2&COLUMN(A1),$A$1:$C$11,3,0),'') 呀，这么复杂的问题终于解决了！ 赶紧向小美炫耀一下我的厉害！
05 总结 针对上面的问题，我总结出了两点结论： ❶ 对于一些复杂问题，我们可以使用辅助列和比较熟悉的函数来解决。 ❷ 写公式的时候，思路很重要。先有思路，才会有公式。
好啦，不说了，小E准备提前去准备工作材料了。
哦，对了，练习小E放在评论区了，别忘了看~ |
|
|
