![fdf3ead901075d4d937e577dc5ed1673.png](https://img-blog.csdnimg.cn/img_convert/fdf3ead901075d4d937e577dc5ed1673.png)
小伙伴们好啊,前几天给大家分享了一期关于数据查询的经典数组公式一对多、多对多查询经典公式,但是很多小伙伴说,数组公式太复杂,直接看晕了
![3b68e1d0b7966b76951e543e64369411.png](https://img-blog.csdnimg.cn/img_convert/3b68e1d0b7966b76951e543e64369411.png)
今天老祝和就和大家分享一组特别简单的方法,也能实现一对多、多对多的数据查询需要。
先看数据,是一份各部门的员工信息表:
![ffddffb1385f86bfe0377d420bef482f.png](https://img-blog.csdnimg.cn/img_convert/ffddffb1385f86bfe0377d420bef482f.png)
咱们首先要完成的是:
一对多查询
一对多查询,就是符合条件的有多个结果。本例中,咱们要根据G2单元格指定的部门,提取出所有财务部的人员信息。
步骤1
在D2单元格输入以下公式,向下拖动复制公式:
=(A2=G$2)+D1
![ee1db09212fd507a0025263979597742.png](https://img-blog.csdnimg.cn/img_convert/ee1db09212fd507a0025263979597742.png)
(A2=G$2)部分,用A列的部门与G2单元格指定的部门进行对比,如果相同,返回逻辑值TRUE,否则返回逻辑值FALSE。
然后将逻辑值与前一个单元格中的数值相加。
在四则运算中,TRUE的作用相当于1,FALSE的作用相当于0。也就是如果部门条件符合了,D列的数值就增加1,否则还等于前一个数值。
注意观察,1、2、3、4……这些序号首次出现的行,就是符合条件的记录。
步骤2
F6单元格输入以下公式,向下向右拖动:
=IFERROR(INDEX(A:A,MATCH(ROW(A1),$D:$D,0)),"")
![ce724006f03d5062d3cf8bb8f3ab49b7.png](https://img-blog.csdnimg.cn/img_convert/ce724006f03d5062d3cf8bb8f3ab49b7.png)
这个公式稍长了一点,咱们拆解看看:
先说说ROW(A1)部分:
ROW函数的作用是返回参数的行号,ROW(A1)结果就是A1的行号1。当公式向下复制时,ROW(A1)会依次变成ROW(A2)、ROW(A3)……,最终得到递增的序号1、2、3……,结果用作MATCH函数的查找值。
再看MATCH(ROW(A1),$D:$D,0)部分。
MATCH函数的作用是查询指定内容在一行或一列中首次出现的位置。这里要查询的内容是ROW(A1)的结果(也就是数值1)在D列首次出现的位置,查询结果为2。
公式向下复制时,会依次查询1、2、3……在D列首次出现的位置。
得到位置信息了,接下来再使用INDEX函数在A列返回对应位置的内容:
INDEX(A:A,MATCH(ROW(A1),$D:$D,0))
当公式一直向下复制时,增加的序号在D列找不到了,公式会返回错误值,所以在最外层加上IFERROR函数,将错误值转换为空文本""。
接下来咱们再看看如何实现:
多对多查询
多对多查询,就是根据多个条件返回多个指定条件的内容。接下来要根据G2单元格指定的部门和G3单元格指定的性别,提取出所有财务部的女性人员信息。
咱们只要简单修改一下D2单元格的公式就可以了,F列的原有的公式不用管它:
=(A2=G$2)*(C2=G$3)+D1
![f05c5128a035054082e4800736dc071e.png](https://img-blog.csdnimg.cn/img_convert/f05c5128a035054082e4800736dc071e.png)
看出门道了吗?多个条件同时符合,就是条件1*条件2。
多个条件符合其一
现在更进一步,要根据G2和G3单元格指定的部门,提取出两个部门的所有记录。
同样,只要简单修改一下D2单元格的公式就可以了,F列的原有的公式不用管它:
=(A2=G$2)+(A2=G$3)+D1
![ec6b1c38d55fe694ddde82ba54685e6e.png](https://img-blog.csdnimg.cn/img_convert/ec6b1c38d55fe694ddde82ba54685e6e.png)
看出门道了吗?多个条件符合其一,就是条件1+条件2。
多个条件混合
现在再深入一步,要根据G1~G3单元格指定的条件,提取出财务部女性的记录以及安监部的所有记录。
仍然只要简单修改一下D2单元格的公式就可以了,F列的原有的公式不用管它:
=(A2=G$1)*(C2=G$2)+(A2=G$3)+D1
![80dc4013b45d009673eb7cd7b9ae8b77.png](https://img-blog.csdnimg.cn/img_convert/80dc4013b45d009673eb7cd7b9ae8b77.png)
看出门道了吗?要同时符合的多组条件就用乘法,对符合其一的条件就用加法。
好了,今天的分享就是这些吧,最后留给大家一个小问题:如果要提取财务部女性和销售部女性的所有记录,D2单元格的公式要如何修改呢?
![09af832170b8a860a6b41cde5c0e5aea.png](https://img-blog.csdnimg.cn/img_convert/09af832170b8a860a6b41cde5c0e5aea.png)
图文制作:祝洪忠
![f00ad2bcfad3e44b0b77b26822fc901b.png](https://img-blog.csdnimg.cn/img_convert/f00ad2bcfad3e44b0b77b26822fc901b.png)
ExcelHome 云课堂 专业的职场技能充电站