Excel Home
全球知名的Excel资源网站之一

一对多查询的4种解法,你最喜欢哪一种?

就是当一个查询值对应多条记录时,如何才能把这些记录全部提取出来呢?
如下图所示,是多个部门的员工信息。

现在,咱们要按部门提取出对应的姓名。

解法1:VLOOKUP+辅助列

单击A列的列标,然后右键→插入,插入一个空白列。
在A2单元格输入公式,向下复制。
=B2&COUNTIF($B$1:B2,B2)

这一步的作用,相当于在各个部门名称后加上了序号。

最后在H2单元格中输入公式:
=IFERROR(VLOOKUP($G2&COLUMN(A1),$A:$E,3,0),””)

查询内容后面加上&COLUMN(A1)得到的序号,和A列的部门+序号相呼应。
如果找不到部门+序号,就用IFERROR函数返回空文本。

 

解法2:FILTER函数

如果你使用的是Office 365或者是Office 2021,公式就简单多了,G2单元格输入以下公式,向下拖动即可:
=TRANSPOSE(FILTER(B2:B14,A2:A14=F2))

FILTER函数根据指定的条件A2:A14=F2,在B$2:B$14单元格区域中提取出符合条件的姓名。
再使用TRANSPOSE函数把垂直的内存数组转换为水平方向。

 

解法3:万金油公式

以下数组公式在各个Excel版本中通用:
=INDEX($C:$C,SMALL(($B$2:$B$14<>$G2)/1%%+ROW($2:$14),COLUMN(A1)))&””

公式的大致意思是,如果$B$2:$B$14不等于$F2,就将行号放大10000倍,否则返回符合条件的行号。
再使用SAMLL函数从小到大依次提取出行号。最后由INDEX函数根据提取出的行号,返回C列中对应位置的内容。

练手文件:
https://pan.baidu.com/s/18Z5uuDAwNg2e0t0W1cCwog

赞(81)
未经允许不得转载:ExcelHome » 一对多查询的4种解法,你最喜欢哪一种?
分享到

关于我们联系我们
本站特聘法律顾问:李志群律师   沪ICP备11019229号-2

沪公网安备 31011702000001号

征信