日常工作中,经常会碰到一对多查询,就是查找值,在原始数据里面有多条记录,我们需要全部将它的结果显示出来,如下所示,根据部门信息,将所有员工姓名列出来 1、传统方法:vlookup公式首先需要建立一个辅助项,输入的公式是: =COUNTIFS($B$2:B2,B2)&B2 也就是把每个部门的累计出现次数放在数据前面 然后我们使用公式: =IFERROR(VLOOKUP(COLUMN(A1)&$E2,$A:$C,3,0),'') 通过column()函数,分别查询各部门的第1个,第2个,第3个,通过iferror屏蔽错误值,就可以实现一对多查询匹配 但是公式还是稍微复杂了一点,对新手朋友们不友好,很难一次就学会。 2、新公式Filter这个新版本里面的筛选函数,当我们想汇总市场部的员工时,我们其实是可以在左边的表格里面, 进行筛选,市场部 这个公式的灵感来源也就是这里,把这一系列操作,写进了公式参数里面,使用用法是: =Filter(筛选区域,筛选条件) 所以我们想统计市场部的员工,只需要输入公式: =FILTER(B:B,A:A=D2) 对B列结果进行筛选,筛选条件是A列的值等于我们的筛选值 但是它是纵向排列的结果,如果我们需要横向展示,就需要结果transpose转置公式: =TRANSPOSE(FILTER(B:B,A:A=D2)) 向下填充,就得到了所有的结果: 3、新公式Textjoin如果我们想把所有的员工,放在一个单元格里面, 那我们可以使用文本连接公式Textjoin 使用用法: =textjoin(文本符,是否忽略空白值,连接文本) 当我们使用if公式判断: =IF(A2:A9=D2,B2:B9,'') 这样得到了市场部所有的员工信息,以及空白单元格 这个时候,我们只需要使用textjoin('、',true,e2:e9) 它就可以把E列的文本使用顿号进行连接,第2参数是忽略空白单元格,从而得到了我们想要的结果 所以我们一气呵成,两个公式放在一起,就可以直接得到结果: =TEXTJOIN('、',TRUE,IF(A:A=D2,B:B,'')) 关于一对多查询的两个新方法,你学会了么?动手试试吧! |
|
来自: 昵称58195209 > 《办公文档》