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语句中：

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

因此，该单元格中的公式返回的结果为：土豆

其余单元格公式转换原理可依此类推。