excel函数获取与满足多个查找条件的所有值 _知识经验

excel函数获取与满足多个查找条件的所有值？如下图1所示，单元格区域A1:E25中存放着数据，列D中是要查找的值需满足的条件，列I和列J中显示查找到的结果，示例中显示的是1月份南区超市销售的蔬菜及其数量。

文章插图
图1
要求在I2中输入公式，向右向下拖拉以获取全部满足条件的数据。
先不看答案，自已动手试一试。
公式
在单元格I2中输入数组公式：
=IF(COUNTIFS($A:$A,$G$6,$B:$B,$G$9,$C:$C,$G$3)<ROWS($I$2:I2),””,INDEX(D:D,SMALL(IF(($A$2:$A$25=$G$6)+($B$2:$B$25=$G$9)+($C$2:$C$25=$G$3)=3,ROW($C$2:$C$25)),ROW(C1))))
向右向下拖拉至出现空单元格。
公式解析
公式中的：
COUNTIFS($A:$A,$G$6,$B:$B,$G$9,$C:$C,$G$3)<ROWS($I$2:I2)
用来计算符合条件的结果数（本例中为5），并与已放置值的单元格数（已返回的值）相比较，以确定在单元格中输入相应的值还是输入空。
公式中的：
($A$2:$A$25=$G$6)+($B$2:$B$25=$G$9)+($C$2:$C$25=$G$3)=3
转换为：
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}+($B$2:$B$25=$G$9)+($C$2:$C$25=$G$3)=3
转换为：
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}+{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}+($C$2:$C$25=$G$3)=3
转换为：
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}+{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}+{FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}=3
转换为：
{1;1;2;2;1;2;0;0;1;1;0;1;2;2;3;3;2;3;1;1;3;3;1;2}=3
数组中有5个3，表明有5条数据满足条件。得到：
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}
代入IF语句中：
IF(($A$2:$A$25=$G$6)+($B$2:$B$25=$G$9)+($C$2:$C$25=$G$3)=3,ROW($C$2:$C$25))
转换为：
IF({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE},ROW($C$2:$C$25))
得到：
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;16;17;FALSE;19;FALSE;FALSE;22;23;FALSE;FALSE}
代入SMALL语句中：
SMALL(IF(($A$2:$A$25=$G$6)+($B$2:$B$25=$G$9)+($C$2:$C$25=$G$3)=3,ROW($C$2:$C$25)),ROW(C1))
转换为：
SMALL({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;16;17;FALSE;19;FALSE;FALSE;22;23;FALSE;FALSE},1)
得到：
16
注意，当公式向下拖拉时，ROW(C1)将更新为ROW(C2)、ROW(C3)……，得到值2、3……等，从而可以获取相应位置的值。
代入INDEX语句中：
【excel函数获取与满足多个查找条件的所有值】INDEX(D:D,SMALL(IF(($A$2:$A$25=$G$6)+($B$2:$B$25=$G$9)+($C$2:$C$25=$G$3)=3,ROW($C$2:$C$25)),ROW(C1)))
转换为：
INDEX(D:D,16)
得到单元格D16中的值：
土豆
由于COUNTIFS($A:$A,$G$6,$B:$B,$G$9,$C:$C,$G$3)<ROWS($I$2:I2)转换为：
5<1
结果为：
False
因此，该单元格中的公式返回的结果为：土豆
其余单元格公式转换原理可依此类推。

excel函数获取与满足多个查找条件的所有值

推荐阅读

和男朋友聊天问他件事情他回答问题打字有空格是什么意思

踏青发朋友圈的精美句子适合踏青发的句子

葡萄什么时候传入中国（葡萄传入中国是什么时候）

米粉是凉性还是热性米粉属于凉性还是热性的呢

叶凝芝是什么电视剧

梦见家人是什么寓意梦见了家人的意思

臭豆腐|乡土散文：臭豆腐卷

家用烤箱多大容量合适

三文鱼菠菜卷的做法三文鱼菠菜卷的做法很简单

丸子汤中的鸡蛋丝是怎么做的（鸡蛋丸子汤做法）

米兰怎样养怎么去修剪

学士服怎么穿穿学士服的正确姿势

鸡养多久能下蛋？

2020款揽运和2019款揽运的区别? 详情分析

暖气片里的水夏天要不要放掉

墙固使用方法墙固如何使用

杭州糟鸡的做法和配方自制杭州糟鸡

广西特产有哪些

支付宝怎么提升每日支付限额

谁知道明天凌晨皇马对巴萨的国王杯哪有的看？