一对多查询怎么办
如果你遇到了一对多的查找需求,你在网上查找过相关公式吗,是不是看起来都很复杂的样子,大神公式是好,只是对于初学者来说,没有一定的函数功底,理解起来是有难度的,有没有更简单的方法呢?答案是有的,条条大道通罗马嘛
如下是图书借阅记录表,现在需要查询张丽玲的借阅书籍明细,先看看复杂公式的方法
方法1:index+small+if 数组公式
我们先看一下可以在网上查询到的方法,利用index+small+if 函数组合实现一对多查询,在G3单元格输入公式
=INDEX(D:D,SMALL(IF($C$3:$C$17=$F$3,ROW($3:$17),4^8),ROW(1:1)))&""
按Ctrl+shift+enter(因为数组公式需要按此三键),向下拖拽公式,即可实现一对多查询,不过数据量大时,使用数组公式可能会由于计算量大,速度变慢
方法2:用vlookup实现一对多查询
vlookup对于包含多个结果的查找只会返回第一个结果(数据区由上向下第一个),所以vlookup没有直接的解决方法,不过vlookup可以通过添加辅助列的方法实现一对多查找(注意辅助列需添加在左侧,因为要根据辅助列的内容查找)
插入辅助列,在A3输入公式=D3&COUNTIF($D$3:D3,D3),向下拖拽,辅助列A列得到的结果如下:可以看到每个人姓名是第几次出现
在H3输入公式=IFERROR(VLOOKUP($G$3&ROW(1:1),A:E,5,0),""),
公式中$G$3&ROW(1:1)运算结果会得到张海玲1,向下拖拽公式会变成$G$3&ROW(2:2),会得到张海玲2,再向下拖拽公式会变成$G$3&ROW(3:3),会得到张海玲3,理解了吗,现在要查询的变成了张海玲1,张海玲2,张海玲3在数据表中对应的书名,转化成1对1查询了
公式看不懂,没关系,大家都记不住,遇到问题能把公式复制来用,改改公式引用数据范围就好了,接下来我们来看看我推荐给大家的简单方法
方法3:透视表方法
我们先看一下显示效果,你可以输入单一姓名查询,也可以添加切片器,在切片器中切换不同人员的借阅书单
具体操作的方法如下:
1)选择数据源,在插入菜单中选择透视表
2)创建透视表里选择现有工作表
3)用鼠标拖拽的方法,将借阅人先拖拽到筛选区域,再将书籍名称字段分别拖拽到行区域和值区域
4)如果还想添加切片器,可以这样操作,这步看需求,不添加就不用这步
完整的操作动画如下: