ExcelHome技术论坛

标题: 五分钟,学会一对多、多对多查询经典公式 [打印本页]

作者: 祝洪忠-    时间: 2019-6-21 14:24
标题: 五分钟,学会一对多、多对多查询经典公式
本帖最后由 祝洪忠- 于 2019-6-21 14:27 编辑

和大家分享两个数据查询的经典公式用法。

先来说说一对多查询。

所谓一对多,就是符合某个指定条件的有多个结果,要把这些结果都提取出来。下面咱们就说说一对多查询的典型用法,先看数据源:
(, 下载次数: 486)
A~D列是一些员工信息,要根据F2单元格指定的学历,提取出所有“本科”的人员姓名。

G2单元格输入以下公式,按住Shift+ctrl不放,按回车,再将公式向下拖动到出现空白单元格为止:
  1. =INDEX(C:C,SMALL(IF(B$2:B$11=F$2,ROW($2:$11),4^8),ROW(A1)))&""
复制代码

(, 下载次数: 393)
公式看起来好长啊,不用担心,咱们一点点的拆解。

先来看这部分:
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,最终得到一个内存数组的计算结果:
  1. {2;65536;65536;65536;6;65536;65536;65536;10;65536}
复制代码
把这个结果放到示意图中,会更直观:
(, 下载次数: 339)
有小伙伴会说:为啥用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都没问题。


接下来咱们再说说多对多查询的公式。

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

如下图所示,要提取出性别为“女”,部门为“财务”的所有人员姓名:
(, 下载次数: 346)
这里变成性别和部门两个条件了,两个条件要同时符合,公式怎么用呢?

H2单元格输入以下公式,按住Shift+ctrl不放,按回车,再将公式向下拖动到出现空白单元格为止:
  1. =INDEX(C:C,SMALL(IF(($B$2:$B$11=$F$2)*($D$2:$D$11=$G$2),ROW($2:$11),4^8),ROW(A1)))&""
复制代码
这个公式看起来更长了,不用担心,咱们仔细看看,公式中的大部分内容和前面讲过的公式几乎是一样的,有所不同的地方就是这里:
  1. ($B$2:$B$11=$F$2)*($D$2:$D$11=$G$2)
复制代码
也就是把多个条件分别写到括号内,再用乘号把多个条件对应相乘。只有这几组条件同时符合了,对应相乘后的结果才是1,否则相乘结果是0。
(, 下载次数: 324)
在IF函数的第一参数中,0的作用相当于逻辑值FALSE,不等于0的数值则相当于逻辑值TRUE。也就是两个条件同时符合了,就返回对应的行号,否则返回65536。
(, 下载次数: 327)
公式的其他部分,和前面讲的第一个公式计算过程都是相同的。


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

比如下图中,要提取性别为“女”或是部门为“财务”的所有人员姓名。
(, 下载次数: 344)
可以在G2单元格输入以下公式:
  1. =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。

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

图文制作:祝洪忠



作者: liu2336904    时间: 2019-6-21 15:01
给祝老师捧场顺便复习下经典套路,老师幸苦啦
作者: 约定的童话    时间: 2019-6-21 15:22
大佬这个有配套文件吗?
作者: sbdk007    时间: 2019-6-21 15:57
老师这是和公众号同步啦
作者: zuodaoao    时间: 2019-6-21 16:15
老师辛苦了
作者: 水和米    时间: 2019-7-24 08:06
从细节入手巩固基础
作者: nsxlyj    时间: 2019-8-13 10:36
经典查询公式!
作者: Michael.Deng    时间: 2019-8-13 10:59
居然能前排占坑
作者: acctyb    时间: 2019-8-13 11:59
跟着学了一招
作者: 7259    时间: 2019-8-13 21:08
通常情况下 A-D列的数据在另一个工作表中,是否可以用数据有效性来建立呢?
作者: 祝洪忠-    时间: 2019-8-13 21:30
7259 发表于 2019-8-13 21:08
通常情况下 A-D列的数据在另一个工作表中,是否可以用数据有效性来建立呢?

如果是同一个工作簿内的不同工作表 公式用法是一样的 试试看
作者: cam333    时间: 2019-8-15 09:52
公式讲解很祥细,学习收藏
作者: 吴永飞    时间: 2019-8-26 14:56
非常好,好好学习,慢慢消化
作者: hanye32211    时间: 2019-9-1 14:11
举一反三,感谢分享
作者: 624134745    时间: 2019-9-1 15:50
之前一直看别人用这个公式感觉好复杂,今天经过祝老师讲解,明白了好多。
作者: 664349687    时间: 2019-9-1 16:29
给祝老师捧场顺便复习下经典套路,老师幸苦啦
作者: BG3PS    时间: 2019-9-1 17:00
学习学习学习
作者: fengpiaoyi    时间: 2019-10-12 22:12
学习到了,非常感谢!!
作者: tomling2005    时间: 2019-10-13 20:49
今天正在学习用这个一对多的,只能套出第一个,后面符合条件的出不来,不知道怎么回事。
作者: IR、独白    时间: 2019-10-13 21:16
谢谢老师..
作者: 天城0003    时间: 2019-10-14 10:00
公式讲的好细致,简单易懂,学习了
作者: rtnhf    时间: 2019-11-10 11:47
强大,收藏,刚好用上
作者: Me-262    时间: 2019-11-10 14:48
受教了,非常强大
作者: 赤道往北30度    时间: 2019-11-10 22:13
强的,真的又学了一招收藏了
作者: qiaoweivip    时间: 2019-11-11 17:02
tomling2005 发表于 2019-10-13 20:49
今天正在学习用这个一对多的,只能套出第一个,后面符合条件的出不来,不知道怎么回事。

这是个数组公式,公式写完要转化成数组形式,写完公式后要按ctrl+shift+enter三键组合。
作者: cs_sunsky    时间: 2019-11-13 10:37
感谢老师,学到很多内容,但是如果查询得到结果要去掉重复的内容公式又该怎么调整呢?
作者: 祝洪忠-    时间: 2019-11-13 14:49
cs_sunsky 发表于 2019-11-13 10:37
感谢老师,学到很多内容,但是如果查询得到结果要去掉重复的内容公式又该怎么调整呢?

可以将有代表性数据的Excel文件上传,  手工模拟需要的结果看看
作者: robter    时间: 2019-11-19 09:05
很好很好,学习学习
作者: yeshuangyong    时间: 2019-11-19 12:02
牛逼的不要不要的
作者: 米粒儿天天向上    时间: 2019-11-21 00:25
学无止境呀!!
作者: bpw0258    时间: 2019-11-22 08:44
学习了,谢谢分享!
作者: wojiushi60    时间: 2019-11-22 14:04
还好有老师在,太棒了
作者: silence05    时间: 2019-11-23 20:22
真的是发散思维,学习到了
作者: 大大的表哥    时间: 2019-11-27 15:55
受教了,谢谢
作者: ghexcel    时间: 2019-12-8 22:14
=INDEX(C:C,SMALL(IF(B$2:B$11=F$2,ROW($2:$11),4^8),ROW(A1)))&""
请问老师,模糊查询要怎么写条件呢?比如,我要找只要有“本科”这两个字的其中一个字,或含有这两个字的都显示出来。不知道我有没有表达清楚意思

作者: 祝洪忠-    时间: 2019-12-9 09:28
ghexcel 发表于 2019-12-8 22:14
=INDEX(C:C,SMALL(IF(B$2:B$11=F$2,ROW($2:$11),4^8),ROW(A1)))&""
请问老师,模糊查询要怎么写条件呢?比 ...

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

类似这样的写法
作者: 飘零雪    时间: 2019-12-9 13:29
学些了,受益匪浅!
作者: ghexcel    时间: 2019-12-9 20:55
祝洪忠- 发表于 2019-12-9 09:28
=INDEX(C:C,SMALL(IF(ISNUMBER(FIND(F$2,B$2:B$11)),ROW($2:$11),4^8),ROW(A1)))&""

类似这样的写法

公式不能用。。。
作者: mmyyuu    时间: 2019-12-10 01:32
请问如果我娄据里的姓名有多个同名,我想多条件查询并去重,应该怎么写???
作者: nk0769    时间: 2019-12-10 09:28
解释和例句堪称完美
作者: theking_wsz    时间: 2019-12-10 11:11
学习了,系统学习很重要啊。
作者: Yoon1224    时间: 2019-12-10 15:34
学习了,厉害
作者: 一般人我不坑他    时间: 2019-12-14 01:06
本帖最后由 一般人我不坑他 于 2019-12-15 18:16 编辑

老师  帮我看下 这个怎么解  我按大佬指的路 来硬套你这个公式 还是不好使 达不到预计效果


作者: groovyzzz    时间: 2019-12-14 15:31
好东西就要顶
作者: 祝洪忠-    时间: 2019-12-14 19:46
一般人我不坑他 发表于 2019-12-14 01:06
老师  帮我看下 这个怎么解  我按大佬指的路 来硬套你这个公式 还是不好使 达不到预计效果

可以把有代表性数据的Excel文件压缩后上传  问题解决会更快
作者: 一般人我不坑他    时间: 2019-12-14 21:10
祝洪忠- 发表于 2019-12-14 19:46
可以把有代表性数据的Excel文件压缩后上传  问题解决会更快

好的 传上来了 帮看下

作者: 祝洪忠-    时间: 2019-12-15 08:38
一般人我不坑他 发表于 2019-12-14 21:10
好的 传上来了 帮看下

(, 下载次数: 264)

作者: 一般人我不坑他    时间: 2019-12-15 18:18
祝洪忠- 发表于 2019-12-15 08:38

原来SUMIFS 就可以解的,还是学艺不精 想复杂了 不能彻底理解简单化,谢谢
作者: yzt110    时间: 2019-12-20 12:40
给祝老师捧场顺便复习下经典套路,老师幸苦啦
作者: Joan1234567    时间: 2019-12-20 14:33
尝试实操了一下公式结果都为0,请问是哪个环节出错了吗?
作者: 马启才    时间: 2020-1-2 11:09
群主 ,在   EXCEL  2010 中最大行数1048576 ,输入1048576  数字 ,公式运行不了
作者: abigailluck    时间: 2020-1-8 15:53
mark~mark~mark~mark~
作者: 祝洪忠-    时间: 2020-1-8 20:08
马启才 发表于 2020-1-2 11:09
群主 ,在   EXCEL  2010 中最大行数1048576 ,输入1048576  数字 ,公式运行不了

公式编辑后 注意要按住SHift+ctrl不放 按回车
作者: 干勇平    时间: 2020-1-12 17:12
确实经典,万金油的公式。乘号和加号的运用,效果真是不同。
作者: 249419618    时间: 2020-1-12 17:52
厉害了,这个公式可以解决好多问题!
作者: 34459078    时间: 2020-1-13 08:58
牛逼,试了下成功了
作者: 西北王者    时间: 2020-2-10 15:36
受益匪浅,谢谢了
作者: 沅潇01    时间: 2020-2-11 11:53
用的真好,太精彩了
作者: Excellover    时间: 2020-2-13 16:49
最近看了数组公式,再理解老师讲的这类应用,就感觉一下子清楚了。
作者: Tienx332570665    时间: 2020-2-15 23:13
感谢分享 学习了
作者: Tienx332570665    时间: 2020-2-15 23:17
感谢分享 学习了
作者: child_bad    时间: 2020-2-18 00:53
感谢祝老师分享,学习并练习了一下,想到一个问题再请教。
如果要查询同时符合多个条件,但有条件单元格空白,却要查询到结果时,该如何写公式呢?
比如说查询条件为学历、部门、性别,如果在对应的查询单元格内都有值,那各条件用*连接,但此时有性别的查询条件为空时会查不到结果。我希望看到的结果是:条件单元格均空白时就显示所有人员,有一个查询条件,就按该条件查,有两个就按两个条件查,有三个就按三个条件查。
作者: yvhusrwg414    时间: 2020-2-18 09:42
系统学习的重要性确实不一般,一个一个的讲解太好了.要不然我是一知半解.
作者: 大头sky    时间: 2020-2-23 17:46
这个公式前面加 textjoin  好像没用啊   不能将找出的写在一个单元格里面?
作者: 祝洪忠-    时间: 2020-2-23 21:37
child_bad 发表于 2020-2-18 00:53
感谢祝老师分享,学习并练习了一下,想到一个问题再请教。
如果要查询同时符合多个条件,但有条件单元格空 ...

明白你的意思了  如果某项条件为空白 即忽略该条件

可以手工模拟部分数据看看
作者: zylz9941    时间: 2020-2-24 12:37
楼主分享的很实用呀
作者: Kop10    时间: 2020-3-4 10:16
学习了  感谢
作者: liyou5566    时间: 2020-3-7 20:46
学习,膜拜~~~·
作者: wangrunming    时间: 2020-3-19 10:36
讲的很明白,清楚,学习了!
作者: csj986111    时间: 2020-3-19 12:08
还看不懂   需要多花点时间琢磨   谢谢大神   学习了   
作者: 阳汖    时间: 2020-3-23 14:17
虽然有点长了,但还是感谢。多练几回就清楚了。
作者: sunseas89    时间: 2020-3-24 09:03
感谢分享,老师辛苦了
作者: zylz9941    时间: 2020-3-25 12:46
好好学习,慢慢消化
作者: baohugengdi    时间: 2020-3-25 15:26
一直在学习,学习,谢谢

作者: 有生之年    时间: 2020-3-28 18:06
如果数据量庞大,这个公式运行起来就会很缓慢,有没有其他途径来达到这个结果
作者: hisunny0213    时间: 2020-3-30 09:53
终于学会了,很清楚的讲解,谢谢老师
作者: hisunny0213    时间: 2020-3-30 11:40
老师,您好,使用了这个公式后发现一个问题,一个名字对应多个项目,其中项目会有重复的情况,相同项目我只需提取一次就好,请问如何解决(图片内容为虚构)

作者: 祝洪忠-    时间: 2020-3-30 11:54
hisunny0213 发表于 2020-3-30 11:40
老师,您好,使用了这个公式后发现一个问题,一个名字对应多个项目,其中项目会有重复的情况,相同项目我只 ...

(, 下载次数: 70)

作者: fendou2323    时间: 2020-3-31 15:09
输入无效,这个怎么办
作者: solextrade    时间: 2020-3-31 15:16
fendou2323 发表于 2020-3-31 15:09
输入无效,这个怎么办

[EXCEL函数与公式版:新人必读之发贴要素]
http://club.excelhome.net/thread-1461222-1-1.html
(出处: ExcelHome技术论坛)

作者: fendou2323    时间: 2020-3-31 15:54
祝洪忠- 发表于 2019-8-13 21:30
如果是同一个工作簿内的不同工作表 公式用法是一样的 试试看

这个我试了,公式无效
作者: hisunny0213    时间: 2020-4-1 09:23
祝洪忠- 发表于 2020-3-30 11:54

谢谢老师,已解决
作者: bnlzc    时间: 2020-4-1 10:13
学习收藏了,辛苦老师了。
作者: 旭日云海    时间: 2020-4-18 17:34
谢谢老师,掰的很细,利于理解
作者: 学无仙景    时间: 2020-4-18 19:33
学学
作者: 上善若水2017    时间: 2020-4-21 15:08
学习,学习
作者: sirius670    时间: 2020-4-22 11:36
尤其是这个满足其中一个条件的查询,简直是无法替代
作者: 布鲁克_    时间: 2020-4-22 20:34
太优秀了!
作者: yeahwgj    时间: 2020-4-22 22:05
受益匪浅,感谢老师深入浅出的讲解!谢谢
作者: lighthaha    时间: 2020-4-23 15:37
本帖最后由 lighthaha 于 2020-4-23 15:38 编辑

把IF(($B$2:$B$11=$F$2)+($D$2:$D$11=$G$2)换为IF(B2:B11&D2:D11=F2&G2
作者: hstwl    时间: 2020-4-23 15:50
祝老师出品,必属精品,有好多思路都是跟着祝老师学习的。还有公众号推文是每期必看。
作者: pecker    时间: 2020-4-23 16:51
MARK一下,做个标记,随时来查
作者: 谢欣(Capricornu    时间: 2020-4-28 17:29
老师你好,能否帮我看一下,为什么我把公式套进去后只匹配到第一个数据,匹配不了第二个数据呢?

作者: 谢欣(Capricornu    时间: 2020-4-29 09:14
祝洪忠- 发表于 2020-4-28 20:02

老师你好,你回复我的帖子的附件好像不是我上传的那个
作者: 祝洪忠-    时间: 2020-4-29 09:31
谢欣(Capricornu 发表于 2020-4-29 09:14
老师你好,你回复我的帖子的附件好像不是我上传的那个

(, 下载次数: 183)

作者: 谢欣(Capricornu    时间: 2020-4-29 11:07
本帖最后由 谢欣(Capricornu 于 2020-4-29 11:17 编辑
祝洪忠- 发表于 2020-4-29 09:31
老师你好,请问公式最后:COUNTIF(A$2:A2,A2)这个是什么意思?
作者: 祝洪忠-    时间: 2020-4-29 11:32
谢欣(Capricornu 发表于 2020-4-29 11:07
老师你好,请问公式最后:COUNTIF(A$2:A2,A2)这个是什么意思?

统计从A2开始到公式所在行的编码个数

SMALL函数根据编码个数,来确定返回行号中的第几个最小值


作者: lixiaoxue    时间: 2020-4-29 15:24
学到了,这个太实用了!
作者: jiangle    时间: 2020-4-29 15:47
谢谢楼主的贡献,受教了
作者: zhanglong2018    时间: 2020-5-18 16:01
学习了!感谢!




欢迎光临 ExcelHome技术论坛 (https://club.excelhome.net/) Powered by Discuz! X3.4