excel如何一个条件匹配多个数据? |
您所在的位置:网站首页 › 解方程组tuv方法 › 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」三键回车。 可是,作为一个大白,我完全看不懂复杂的函数公式。 稍有数据改动,就会束手无策。 那么,有什么其它简单易懂的方法吗? 答案是有的! 我来分享啦! 【1】思路解析之VLOOKUP 要解决这个问题,需要我们最熟悉的 VLOOKUP 函数。 但是 VLOOKUP 函数只会返回查找区域中,查找值第一个对应的值。 比如下图。 在查找人事部的人员姓名时,虽然林晨和张琴都是人事部门人员。 但是,VLOOKUP 函数只会返回「林晨」这一个值。 你刚刚不是说,VLOOKUP 可以解决问题的吗,怎么又不行了? 你别心急啊,听我慢慢跟你 Suo。 【2】思路解析之改变查找值 既然 ,VLOOKUP 函数只会返回第一个对应值,那我们只需要将查找值,变得不同就可以了。 比如说将后面出现的「人事部」变成「人事部 1」、「人事部 2」…… 然后分别查找「人事部 1」,「人事部 2」对应的人员姓名就可以了。 所以,我们要分两步来解决这个问题。 第一步:将查找值「人事部」变成「人事部 1」和「人事部 2」 「人事部」三个字是不变的,变化的是用来区分不同的后缀:1 和 2。 简单说,就是固定的「人事部」连接(&)变化的「数字」构成新的查找值来实现查找值的不同。 在此例中,我们可以使用 COLUMN 函数,返回指定单元格引用的列号,来实现后缀的变化。 COLUMN 函数,返回指定单元格引用的列号。例如,公式 =COLUMN(D10) 返回 4,因为列 D 为第四列。下图中,在 F9 单元格输入以下公式: =$E9&COLUMN(A1)向右拖拽,由于单元格列号的变化,就会得到「人事部 1」,「人事部 2」,以此类推。 公式中要固定「人事部」,即 E9 单元格的列号要用「$」锁定。「&」起连接作用。【3】思路解析之改变查找区域 第二步,要在查找区域中有「人事部 1」,「人事部 2」。 在下面这张表格中,A 列「辅助列」是我们新增的查找区域。 如何根据原本的 B 列「部门」,来生成我们需要的辅助列,是这一步的关键所在。 具体方法是: 在 A2 单元格输入公式: =B2&COUNTIF($B$2:B2,B2)向下填充即可。 接下来,我们分析一下,公式为什么这样写。 首先,COUNTIF 函数,是用于统计满足某个条件的单元格的数量。 但这个公式的思路,不仅仅是使用 COUNTIF 函数,还是单元格的引用方式。 上图中 E9:E13 区域是第一次出现「人事部」,所以得到「1」; E9:E17 区域是第二次出现「人事部」,所以得到「2」。 注意到这两个区域的开始部分都是 E9。 所以在生成辅助列的时候,开始区域是绝对引用,结束区域是相对引用。 这样得到的次数才会递增。 【4】完成公式编写 经过上面几步的解析,我们已经完成了改变查找值和改变查找区域两步。 但是,当公式在 H3,查找「人事部 3」的时候: 查找区域中并没有「人事部 3」,就会返回 #N/A 错误。 所以,还需要使用 IFERROR 函数,来屏蔽错误值。 最终 ,F1 单元格的公式是: =IFERROR(VLOOKUP($E2&COLUMN(A1),$A$1:$C$11,3,0),"")呀,这么复杂的问题终于解决了! 总结一下: ①对于一些复杂问题,我们可以使用辅助列和比较熟悉的函数来解决。 ②写公式的时候,思路很重要。先有思路,才会有公式。 这就是分享的干货啦~ 关注我获得更多Excel干货,不再加班! 拜托拜托拜托点个赞,谢谢您。 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |