本文总共1382个字,阅读需5分钟,全文加载时间:2.148s,本站办公入门专栏收录该内容! 字体大小:

文章导读:小伙伴们好啊,今天老祝和大家分享一对多查询数据查询的经典公式用法。 所谓一对多,就是符合某个指定条件的有多个结果,要把这些结果都提取出来。下面咱们就说说一对多查询的典型用法,先看数据源: A~D列是一……各位看官请向下阅读:

小伙伴们好啊,今天老祝和大家分享一对多查询数据查询的经典公式用法。

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

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都没问题。

想要从零系统学习Excel,查看本站上方“视频教程”获取最新秋叶Office三合一办公应用课程!

以上内容由优质教程资源合作伙伴 “鲸鱼办公” 整理编辑,如果对您有帮助欢迎转发分享!

你可能对这些文章感兴趣:

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注