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

小伙伴们,大家好。今天继续来分享一对多查询的方法,还是昨天的案例。是在昨天思路的基础上通过其他函数来完成的,主要用到lookup+frequency+countif+offset这4个函数。countif和offset的多维引用得到了辅助列的结果,frequency像vlookup一样定位第1个1,第1个2的位置,lookup返回对应位置的姓名。

根据学历查询出所有的姓名

在G2单元格中输入公式=LOOKUP(,0/FREQUENCY(ROW(A1),COUNTIF(OFFSET(C$3,,,ROW($1:$12)),F$2)),A$3:A4)&"",不用三键,向下填充,完成。公式也不算太长。

=COUNTIF(OFFSET(C$3,,,ROW($1:$12)),F$2)这部分返回的结果如下图D列所示,实际就是昨天辅助列的效果,只不过现在形成了一个内存数组。

下面简单说一下offset的多维引用。我们一般引用区域的时候只引用一个区域,比如C3:C14;而offset的多维引用可以引用多个区域,比如OFFSET(C$3,,,ROW($1:$12))就引用了12个区域,第1个区域是C3:C3,第2个区域是C3:C4,第3个区域是C3:C5。。。第12个区域是C3:C14。

然后用countif分别统计这12个区域中F2的个数,上图F2是大专,所以就是分别统计这12个区域中大专的个数。也就是昨天辅助列的效果。
得到了辅助列的效果,接下来就可以查找第1个1的位置,然后返回这个位置对应的姓名,也就是“李凯凯”,公式下拉,查找第1个2的位置,然后返回它对应的姓名“程昊”,以此类推,把所有大专对应的姓名都查完。

=FREQUENCY(ROW(A1),COUNTIF(OFFSET(C$3,,,ROW($1:$12)),F$2))这部分用frequency定位第1个1的位置,结果如下图E列所示,在D列第1个1的对应位置计数1,其他位置都为0。此时frequency的第1参数是1,在第2参数中第1个1的位置计数1。

=LOOKUP(,0/FREQUENCY(ROW(A1),COUNTIF(OFFSET(C$3,,,ROW($1:$12)),F$2)),A$3:A4)这部分用lookup返回第1个1对应的姓名“李凯凯”。其中lookup的第2参数如下图E列所示,也就是用0除以上一步frequency的结果,是0的变为错误值,1的变为0。lookup的第1参数啥也没写,用逗号隔开代表0,也就是在第2参数中查找0的位置,找到后返回第3参数中的姓名。lookup的第3参数A$3:A4和A3:A14是一样的效果,因为具有延展性,像sumif的第3参数一样。

上面lookup的公式就是G2单元格的公式,公式下拉到G3单元格变为=LOOKUP(,0/FREQUENCY(ROW(A2),COUNTIF(OFFSET(C$3,,,ROW($1:$12)),F$2)),A$3:A5),此时只有frequency的第1参数变为2,其他的都不变。就是用frequency定位第1个2的位置,结果如下图E列所示;然后用lookup返回这个位置对应的姓名“程昊”。

公式下拉到G4就用frequency定位第1个3的位置,然后用lookup返回对应的姓名“谢文燕”,和上面是一样的用法就不再重复说明。公式再下拉,到了G5的时候,此时所有大专对应的姓名都查询完了,它返回的结果是0,并且公式再下拉的话,返回的结果都是0,如下图所示。

为什么G5以后的单元格都是0呢?还得要看frequency,因为只有它的第1参数在变,G5的时候它的第1参数是4,在第2参数中找不到大于等于4的,所以在最后一个位置计数1,如上图E15所示,然后用lookup返回对应的A15单元格,而A15单元格是空单元格,所以显示为0。为了不让0显示出来,所以公式最后要连接空文本,也就是&""。

这种方法我认为还是很经典的,你怎么看?加上今天的方法,一对多查询的方法我应该分享过4-5种了,你学会了几种?如果你还有其他的方法,欢迎分享,让我们一起学习。

文件链接:

https://pan.baidu.com/s/1mrT0qa4VrzOs9wRH3qE3vQ

提取码:o7z0

(0)

相关推荐

  • 统计不重复数据

    我有手工台账如下: 小本买卖,上面都是便利店的王牌销售产品! 随着种类的不断丰富,我想知道我进货的种类一共有多少! 怎么做? 思路一: 文字描述: 找到每一个品种在整个列表中有几个,如果有N个,则自己 ...

  • 你知道有哪些公式组合可以提取唯一值?

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 下面是一份某商场某些产品的进货明细.由于各个品牌的商品中还有不同型号的进货 ...

  • 试用期就破格升职加薪?这位新同事做的Excel自动查询表,我服!(建议收藏)

    每天一点小技能 职场打怪不得怂 编按:大型公司都会要求库存管理中心的员工学会制作"进出货查询表".在基于先进先出的原则下,能否熟练地使用EXCEL完成日常进出货的查询和记录工作,是 ...

  • 精通Excel数组公式15:使用INDEX函数和OFFSET函数创建动态单元格区域(续)

    excelperfect 导语:本文为<精通Excel数组公式14:使用INDEX函数和OFFSET函数创建动态单元格区域>的后半部分. 将动态单元格区域公式定义为名称 创建动态单元格区域 ...

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

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

  • 【Excel】一对多、多对多查询,最简单的方法请拿好

    先看数据,是一份各部门的员工信息表: 咱们首先要完成的是: 一对多查询 一对多查询,就是符合条件的有多个结果.本例中,咱们要根据G2单元格指定的部门,提取出所有财务部的人员信息. 步骤1 在D2单元格 ...

  • Excel中一对多查询的5种方法

    如下图所示,查询右侧员工编号为"45424"的所有的销售数量. 方法01 辅助列+VLOOKUP 对于查询类的问题,大家第一时间可能想到的会是VLOOKUP函数,是的小必老师给大家 ...

  • 一对多、多对多查询,最简单的方法请拿好

    先看数据,是一份各部门的员工信息表: 咱们首先要完成的是: 一对多查询 一对多查询,就是符合条件的有多个结果.本例中,咱们要根据G2单元格指定的部门,提取出所有财务部的人员信息. 步骤1 在D2单元格 ...

  • 一对多查询?这里的方法是比较全的,值得你收藏!

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 工作中如果遇到一对一查找的问题时,大家都会使用VLOOKUP函数,这个很简 ...

  • Excel教程:看高手秒秒钟列出多种Excel一对多查询方法!

    Excel一对多查询,你能够想到用什么函数?excel一对多查询不仅可以使用函数公式,还可以数据透视表. 我承认我只是一个普通人或者是懒人,尽管高手的方法很多,但我只衷情于数据透视表进行一对多查询,因 ...

  • 看高手秒秒钟列出多种Excel一对多查询方法!

    Excel一对多查询,你能够想到用什么函数?excel一对多查询不仅可以使用函数公式,还可以数据透视表. 我承认我只是一个普通人或者是懒人,尽管高手的方法很多,但我只衷情于数据透视表进行一对多查询,因 ...

  • 秒秒钟列出多种Excel一对多查询方法

    绿色按钮收听Excel课程  今晚8点开课 主讲/滴答老师 - 咨询QQ:800094815 Excel一对多查询,你能够想到用什么函数?不仅可以使用函数公式,还可以数据透视表. 我承认我只是一个普通 ...

  • 任性!看高手秒秒钟列出多种Excel一对多查询方法!

    Excel一对多查询,你能够想到用什么函数?excel一对多查询不仅可以使用函数公式,还可以数据透视表. 我承认我只是一个普通人或者是懒人,尽管高手的方法很多,但我只衷情于数据透视表进行一对多查询,因 ...