一对多查询,这组Excel公式太好用了

发布时间:2020-01-06 来源:网络


如何按给定的条件查找数据中所有记录?本期技巧妹与大家分享一个非常强大的公式组合,具体是什么?一起看看吧。


效果图:



由于需要制作一个联动的查找,这里借助数据有效性制作一个下拉菜单,选中单元格,然后点击数据——数据工具——数据有效性,验证条件选择序列,数据输入相应的内容即可。



在A16单元格中输入公式=IFERROR(INDEX($A$1:$F$10,SMALL(IF($C$1:$C$10=$B$13,ROW($A$1:$A$10),4^8),ROW(A1)),COLUMN(A:A)),""),然后按Ctrl+Shift+Enter组合键。



公式说明:


IF($C$1:$C$10=$B$13,ROW($A$1:$A$10),4^8):用IF函数先判断部门所在的区域$C$1:$C$10是否与B13单元格的内容一致,若是一样则返回指定区域所在的行号,若是不同则返回一个较大的数值,这里给出的是4^8,数值大小可以自行设置,但一定要比返回的行号大。


ROW($A$1:$A$10):表示返回行号。


SMALL(IF($C$1:$C$10=$B$13,ROW($A$1:$A$10),4^8),ROW(A1)):是SMALL函数的用法,表示返回第几个最小值,它的语法结构=SMALL(数值数组或者数值区域,第几个最小值),由于需要向下填充产生第几个最小值,所以用ROW函数来实现。


INDEX函数:由于我们知道相应的区域和行号,只要再确定列号即可引用具体内容,所以用INDEX函数来实现,它的语法结构是=INDEX(查找区域,返回行,返回列),这里是从工号列开始引用的,所以列号用COLUMN(A:A)来确定即可。


以上就是今天要与大家分享的一对多查询的用法,希望对大家有所帮助。