一对多查询怎么办

如果你遇到了一对多的查找需求,你在网上查找过相关公式吗,是不是看起来都很复杂的样子,大神公式是好,只是对于初学者来说,没有一定的函数功底,理解起来是有难度的,有没有更简单的方法呢?答案是有的,条条大道通罗马嘛

如下是图书借阅记录表,现在需要查询张丽玲的借阅书籍明细,先看看复杂公式的方法

方法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)如果还想添加切片器,可以这样操作,这步看需求,不添加就不用这步

完整的操作动画如下:


(0)

相关推荐

  • VLOOKUP按职位排序

    原创作者 | 李锐 微信公众号 | Excel函数与公式(ID:ExcelLiRui) 个人微信号 | (ID:ExcelLiRui520) VLOOKUP按职位排序 今天再来讲个VLOOKUP函数的 ...

  • SLOOKUP诞生-强过VLOOKUP百倍

    VLOOKUP函数,几乎是我们办公中必备的函数,使用率也是最高的的几个函数之一,其强大,毋庸置疑 但是,她确实存有一定的缺陷 比如 1.无法反向查找 2.不能反悔多个满足条件的结果 3.结果非数组,无 ...

  • 按指定次数重复内容的套路合集

    -套路合集- 按指定次数重复内容 1.vlookup精确查找(4参为0) 如下图所示,B列的是要重复的内容,C列是要重复的次数,最后想要的效果是E列那样.A重复2次,B重复3次...... 首先在A列 ...

  • 逆向查询的这几种常用方法,你最喜欢哪一种?

    下表是某公司员工的信息表,要根据E3单元格的工号逆向查询出对应的姓名.这种查询方式在工作中会经常用到,还不会的小伙伴要赶紧学起来. 方法1:vlookup+if重构数组 在F3单元格输入下面的公式,完 ...

  • 如何提取出最后一个斜杠(\)之前的内容?

    你好,我是刘卓.欢迎来到我的公号,excel函数解析.今天要分享的内容是如何提取出最后一个斜杠之前的内容,这类型的题很多小伙伴也会经常遇到. 下图A列是一些包含路径的文件名,现在要提取出最后一个斜杠前 ...

  • vlookup进阶用法,通配符的运用

    之前我们说过vlookup的一些基本用法,和模糊查找的用法,还有向左查询的用法.今天说说通配符的用法.先上2个题目. -01- 根据序号查询出货号.你可能会说,这不就是vlookup向左查询吗,用上次 ...

  • Vlookup函数一对多查找

    Excel中Vlookup函数可查询符合条件的一行数据,但如果查询结果符合条件的是多行数据怎么办?例如下面的表格中要查找姓名为"李飞"对应的职务,有3行符合条件的记录,怎样把这符号 ...

  • vlookup配合一个等号,轻松搞定一对多查询,设计得太巧妙了

    Hello,大家好,今天跟大家分享我们如何使用vlookup函数来解决一对多查询的问题,相信很多人都听过到一对多查询,但是不明白具体是什么意思,简单来说一对多查询就是通过查找一个值来返回多个结果,比如 ...

  • vlookup配合一个等号,轻松搞定一对多查询,设计的太巧妙了

    vlookup配合一个等号,轻松搞定一对多查询,设计的太巧妙了

  • Excel和Access双剑合壁,轻松实现Excel函数vlookup一对多查询

    Excel函数vlookup估计很多人用过,大多数人用到这个函数的功能是进行一对一查询,如果满足条件的记录有多条,返回结果始终是第一条记录,怎样把符合条件的多条记录全部查找出来呢?小编之前写过vloo ...

  • VLOOKUP一对多查询,还不会吗?其实很简单!

    你好,我是刘卓.欢迎来到我的公号,excel函数解析.一对多查询是很多小伙伴在工作中经常遇到的问题,对于初学函数的同学来说,复杂的数组公式难以理解,不太友好.我们可以通过辅助列的方法,利用vlooku ...

  • 一对多查询,我只想到4种方法

    方法一:高级筛选 将所需条件复制到H1:H2,将所需信息字段复制到H4:J4,如下图: 依次点击[数据]--[排序和筛选]--[高级],进行如下设置: 点击[确定],结果瞬间就出来啦~ 在H5单元格输 ...

  • 一对多查询,复杂问题变简单

    一对多查询,就是符合条件的有多个结果.通常使用数组公式来返回多个结果,由于比较复杂,是很多小伙伴的一块心病. 今天和大家分享一个比较简单的方法,来实现一对多的数据查询需要. 先看数据,是一份各部门的员 ...

  • 一对多查询只会vlookup就out了,Excel全部3种查询方式你需要知道

    使用Excel的朋友都知道,经常会碰到的一个操作那就是数据查询.如果是简单的数据查询,我们使用vlookup.lookup或者其他的一些基本查询函数就可以实现.但是工作中我们会发现有一种查询经常会出现 ...

  • 多工作表一对多查询

    如下图所示,有6个月的销售数据分别保存在不同的工作表中.现在的要求是将这6个月中销量大于等于10000的数据全部查询出来,并存放在[多表查询]这个工作表中.结果如动图所示. 像这种问题,用函数做就比较 ...

  • 学习和创新永无止境!一对多查询,各位大神的套路都在这里了。

    你好,我是刘卓.欢迎来到我的公号,excel函数解析.在工作和学习中,我们经常需要根据一个条件,来查询出满足该条件的所有值,俗称一对多查询. 关于一对多查询的方法,我相信有些小伙伴已经非常熟悉了,可能 ...

  • 一对多查询竟然有这么多方法,惊呆我了!

    小伙伴们,大家好.今天继续来分享一对多查询的方法,还是昨天的案例.是在昨天思路的基础上通过其他函数来完成的,主要用到lookup+frequency+countif+offset这4个函数.count ...