一对多、多对多查询经典公式

先来说说一对多查询。

所谓一对多,就是符合某个指定条件的有多个结果,要把这些结果都提取出来。下面咱们就说说一对多查询的典型用法,先看数据源:

A~D列是一些员工信息,要根据F2单元格指定的学历,提取出所有“本科”的人员姓名。

G2单元格输入以下公式,按住Shift+ctrl不放,按回车,再将公式向下拖动到出现空白单元格为止:

=INDEX(C:C,SMALL(IF(B$2:B$11=F$2,ROW($2:$11),4^8),ROW(A1)))&''

公式看起来好长啊,不用担心,咱们一点点的拆解。

先来看这部分:

IF(B$2:B$11=F$2,ROW($2:$11),4^8)

IF函数的意思是判断一个条件是不是成立,如果成立返回第二参数,否则返回第三参数。

本例中,要判断的条件是B$2:B$11=F$2,如果B列的学历等于F2单元格中指定的“本科”,就返回2~11行对应的行号ROW($2:$11),否则返回4^8的结果65536,最终得到一个内存数组的计算结果:

{2;65536;65536;65536;6;65536;65536;65536;10;65536}

把这个结果放到示意图中,会更直观:

有小伙伴会说:为啥用65536啊,先不要着急,咱们一会儿再说。

接下来,再用SMALL函数,在这个内存数组中提取内容。

SMALL函数的作用是返回一组数值中的第n个最小值,比如公式SMALL(A:A,3),就是返回A列中的第三个最小值了。

本例中,SMALL函数用IF函数的计算结果作为第一参数,要在这个内存数组中提取第n个最小值,这里的n由谁来指定呢?就是公式最后部分的ROW(A1)。

ROW(A1)的作用是返回A1单元格的行号,结果是1。当公式向下复制时,参数会依次变成ROW(A2)、ROW(A3)、……,也就是得到从1开始、依次递增的序号。最终的目的是给SMALL函数一个动态的参数,依次从内存数组中提取出第1至n个最小值。

咱们回头再看看上面的示意图,SAMLL函数先提取出内存数组中的第1个最小值,结果是2。

这个2有啥用呢?继续往下看:

下面该轮到INDEX函数出场了,这个函数的作用是根据指定的位置信息,从数据区域返回对应位置的内容。刚刚的2就是位置信息,INDEX函数从C列中返回第二个单元格的内容,结果就是第一个符合条件的姓名“刘一山”。

公式向下复制到G3单元格,ROW(A1)变成了ROW(A2),返回A2的行号2,SMALL函数再从内存数组中提取第2个最小值,结果是几呢?再看看上面的示意图,OK,是6。INDEX函数最终再返回C列中的第6个单元格中的内容“王希建”。

如果所有符合条件的行号都提取完了,公式还向下复制,这个时候SMALL函数的结果就是65536了,最终INDEX函数返回C列第65536个单元格中的内容。

通常情况下,咱们的工作表没有这么多数据,也就是65536是空白单元格,INDEX函数引用空白单元格时,会返回一个无意义的0,所以咱们在公式的最后部分加上一个&'',使无意义的0不再显示。

这里的65536可以是其他任意一个较大的数值,只不过人们使用习惯了,只要你高兴,换成63565、65356都没问题。

还没有看晕的小伙伴,接下来咱们再说说多对多查询的公式。

多对多查询通常分为两种情况:一是要提取出同时符合多个条件的所有记录。二是要提取出多个条件符合其一的所有记录。

如下图所示,要提取出性别为“女”,部门为“财务”的所有人员姓名:

这里变成性别和部门两个条件了,两个条件要同时符合,公式怎么用呢?

H2单元格输入以下公式,按住Shift+ctrl不放,按回车,再将公式向下拖动到出现空白单元格为止:

=INDEX(C:C,SMALL(IF(($B$2:$B$11=$F$2)*($D$2:$D$11=$G$2),ROW($2:$11),4^8),ROW(A1)))&''

这个公式看起来更长了,不用担心,咱们仔细看看,公式中的大部分内容和前面讲过的公式几乎是一样的,有所不同的地方就是这里:

($B$2:$B$11=$F$2)*($D$2:$D$11=$G$2)

也就是把多个条件分别写到括号内,再用乘号把多个条件对应相乘。只有这几组条件同时符合了,对应相乘后的结果才是1,否则相乘结果是0。

在IF函数的第一参数中,0的作用相当于逻辑值FALSE,不等于0的数值则相当于逻辑值TRUE。也就是两个条件同时符合了,就返回对应的行号,否则返回65536。

公式的其他部分,和前面讲的第一个公式计算过程都是相同的。

多对多查询的第二种情况是多个条件符合其一。

比如下图中,要提取性别为“女”或是部门为“财务”的所有人员姓名。

可以在G2单元格输入以下公式:

=INDEX(C:C,SMALL(IF(($B$2:$B$11=$F$2)+($D$2:$D$11=$G$2),ROW($2:$11),4^8),ROW(A1)))&''

这里告诉大家一个处理的小窍门:多个条件分别写到括号内,再用加号把多个条件对应相加就好。

这几组条件中只有一个符合了,对应相加后的结果就不是0,如果所有条件都不符合,相加结果才是0。

其他计算过程和第二个公式是完全一样的,细节咱们不再展开说了。

好了,今天咱们的分享就是这些吧,祝小伙伴们一天好心情!

图文制作:祝洪忠

(0)

相关推荐

  • 用函数实现筛选的功能,HLOOKUP也来凑热闹

    这几天一直说怎么用函数实现筛选的功能,也可以说是一对多的查询问题.我把最近我能想到的方法都来说一下,今天就说下用hlookup函数来实现这个功能. -01- 具体应用 1.根据姓名查询出所有的记录 还 ...

  • 手把手教你,学会单条件和多条件的筛选

    筛选符合单个指定条件的记录 示例26-1    提取销售1组的记录 如图26-1所示,A~D列是某企业销售记录表的部分内容,需要根据G1单元格中指定的组别,提取出该组别的全部记录. 在F4单元格中输入 ...

  • Excel公式练习82:提取最近出现的相同数据旁边的单元格内容

    excelperfect 本次的练习是:如下图1所示,有两列数据,要求在列B中输入数据后,自动匹配其上方最近一次出现的该数据并提取对应的列A中的数据放置到列C对应的单元格中,例如,单元格B6中输入1后 ...

  • 查找不重复记录的几个套路(删除重复项)

    今天和小伙伴们分享几个查找不重复记录的套路,这个不重复的记录相当于用删除重复项这个功能,比如表中有2个相同的名字,只取第一次出现的记录.也就是如果一条记录重复出现多次,只取第一次出现的记录. -01- ...

  • 一对多查询经典函数组合拓展应用--多对多查询

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 小伙伴们好,前一段时间我有几篇帖子专门介绍了一对多数据查询的经典函数组合, ...

  • Excel函数公式多对多查询教程

    Excel函数公式多对多查询教程

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

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

  • Excel多对多查询的快捷算法+万金油公式

    点击下方 ↓ 关注,每天免费看Excel专业教程 置顶公众号或设为星标 ↑ 才能每天及时收到推送 个人微信号 | (ID:LiRuiExcel520) 微信服务号 | 跟李锐学Excel(ID:LiR ...

  • 一对多查询经典函数组合再例

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 这个帖子分享的也是网友问我的一个真实例子.如上图,怎样将供应商清单用公式转 ...

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

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

  • 一对多查询经典函数组合对比并提取两列数据差异

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 对比两列数据差异,我们使用最多的方法是条件格式.今天向大家分享一个函数的方 ...

  • 一对多查询经典函数组合的实例应用

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 前两天看到这样一个操作,感觉非常有意思,现在分享给大家.如上图,人员可以根 ...

  • 经典公式解析-中式排名!

    今天我们来说点谈一下排名的问题,把经典的中式排名公式剖析一下. 我们先来看一下中式排名和美式排名的区别: 简单来说,就是美式相同的排名会占位,比如这里的美式没有第三名, 直接第四名,也就是说,他没有真 ...