手把手教你，学会单条件和多条件的筛选

筛选符合单个指定条件的记录

示例26-1    提取销售1组的记录

如图26-1所示，A~D列是某企业销售记录表的部分内容，需要根据G1单元格中指定的组别，提取出该组别的全部记录。

在F4单元格中输入以下数组公式，按<Ctrl+ Shift+Enter>组合键，将公式复制到F4:I9单元格区域。

{=INDEX(A:A,SMALL(IF($B$2:$B$12=$G$1,ROW($2:$12),4^8),ROW(A1)))&''}

公式中的“IF($B$2:$B$12=$G$1,ROW($2:$12),4^8)”部分，首先使用IF函数判断B列的组别是否等于G1单元格指定的组别，如果B2:B12单元格区域中的组别等于指定组别，则返回对应的行号；否则返回一个较大的数值“4^8”，即6 5536，结果如下。

{2;65536;4;65536;6;65536;65536;65536;10;65536;12}

“ROW(A1)”部分返回A1单元格的行号， 由于A1单元格中使用了相对引用，公式向下复制时，会依次得到A2，A3，A4，…单元格的行号，也就是一组生成递增的数值。

SMALL函数使用“ROW(A1)”的结果作为第二参数，从以上内存数组中，从小到大依次提取行号。

INDEX函数以SAMLL函数的结果作为索引值，从A列中提取出对应位置的业务员名单。第一参数A:A使用相对引用，当公式向右复制时，要提取的数据范围随之发生变化，最终提取出符合条件的所有记录。

当SMALL函数返回结果为6 5536时，INDEX函数引用指定列中第6 5536行的单元格，一般数据表格中这个位置为空白内容，所以INDEX函数得到空白单元格的引用，最终返回无意义的0。公式最后连接空文本，目的是屏蔽无意义的0值，使其在单元格中显示为空白。

在公式最后连接空文本后，公式得到数值结果将变成文本格式，不能直接进行求和汇总。如果对公式结果有进一步的汇总需求，可以使用以下公式实现相同的提取效果，并且公式得到的数值结果能够直接求和汇总，如图26-2所示。

{=IF(ROW(A1)<=COUNTIF($B:$B,$G$1),INDEX(A:A,SMALL(IF($B$2:$B$12=$G$1,ROW($2:$12),4^8),ROW(A1))),'')}

“ROW(A1)<=COUNTIF($B:$B,$G$1) ”部分先使用“COUNTIF($B:$B,$G$1)”统计出B列的组别中符合指定组别的个数，然后与ROW(A1)进行比较。

当公式向下复制时，如果公式行数大于指定的组别个数，IF函数返回空文本，否则执行INDEX函数部分的提取公式。

示例26    提取指定的产品记录

如图26-3所示，需要根据G1单元格中指定的产品名称，从A~D列中提取出该产品的全部记录。

在F4单元格中输入以下数组公式，按<Ctrl+Shift+En ter>组合键，将公式复制到F4:I9单元格区域。

{=INDEX(A:A,SMALL(($C$2:$C$12<>$G$1)/1%%+ROW($2:$12),ROW(A1)))&''}

公式中的“($C$2:$C$12<>$G$1)/1%%+ROW($2:$12)”部分，先判断C2:C12单元格区域中的产品名称是否不等于G1单元格中指定的产品名称，如果条件成立则返回逻辑值TRUE；否则返回逻辑值FALSE，结果如下。

{TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}

然后用以上内存数组除以1%%，相当于乘以10 000，得到以下结果。

{10000;0;10000;10000;10000;10000;10000;0;10000;10000;0}

用这个新的内存数组加上行号，结果如下。

{10002;3;10004;10005;10006;10007;10008;9;10010;10011;12}

也就是数据区域中不等于指定产品的，所在行号加上了10 000；而等于指定产品的返回所在行号本身。

最后使用SMALL函数从小到大依次提取出行号信息，INDEX函数以此作为索引值，返回对应位置的内容。

同一字段中的多条件筛选

示例26-3    筛选销售额在指定区间的记录

如图26-4所示，需要根据 G1单元格中指定的销售额下限和G2单元格中指定的销售额上限，从A~D列中提取出指定区间的全部记录。

在F5单元格中输入以下数组公式，按<Ctrl+Shift +Enter>组合键，将公式复制到F5:I10单元格区域。

{=INDEX(A:A,SMALL(IF(($D$2:$D$12>=$G$1)*($D$2:$D$12<=$G$2),ROW($2:$12),4^8),ROW(A1)))&''}

AND函数和OR函数不能生成内存数组结果，因此，在数组公式中表示多个条件的“与”关系时，需要使用乘法表示。表示多个条件的“或”关系时，需要使用加法表示。

公式中的两个判断条件“($D$2:$D$12>=$G$1)”和“($D$2:$D$12<=$G$2)” 分别得到两组由逻辑值TRUE和FALSE构成的内存数组。使用乘法，表示两个条件是“与”的关系。

将两组内存数组中的元素对应相乘，如果两个条件同时符合，即相当于“ TRUE*TRUE ”，结果为1。如果两个条件符合其一或是均不符合，即相当于“ TRUE*FALSE ”或是“ FALSE*FALSE ”，结果为0。

当IF函数的第一参数为1，也就是两个条件同时符合时，返回对应的行号，否则返回数值65 536。

最后使用SMALL函数从小到大依次提取出行号信息，INDEX函数以此作为索引值，返回对应位置的内容。

同一字段多条件符合其一的筛选

示例26-4    提取不同产品的记录

如图26-5所示，需要根据G1 单元格和G2单元格中指定的产品名称，从A~D列中提取出两个产品的全部记录。

在F5单元格中输入以下数组公式，按<Ctrl+Shift+Ent er>组合键，将公式复制到F5:I12单元格区域。

{=INDEX(A:A,SMALL(IF(($C$2:$C$12=$G$1)+($C$2:$C$12=$G$2),ROW($2:$12),4^8),ROW(A1)))&''}

公式中的两个判断条件“($C$2:$C$12=$G$1)”和“($C$2:$C$12=$G$2)” 分别得到两组由逻辑值TRUE和FALSE构成的内存数组。使用加法，表示两个条件是“或”的关系。

将两组内存数组中的元素对应相加，如果两个条件符合其一，逻辑值“ TRUE+FALSE ”结果为1；如果两个条件均不符合，即相当于“FALSE+FALSE”，结果为0。

然后使用IF函数进行判断，如果两个条件符合其一时，返回对应的行号，否则返回数值65 536。

最后使用SMALL函数从小到大依次提取出行号信息，INDEX函数以此作为索引值，返回对应位置的内容。

使用以上方法时，如果要指定的条件比较多，则需要设置多个判断条件，然后使用加法进行判断，不但公式冗长，而且容易出错。实际应用时，可以将判断条件作为一个区域整体引用。

在F5单元格中可以输入以下数组公式，按<Ctrl+Shift+Enter>组合键，将公式复制到F5:I12单元格区域。

{=INDEX(A:A,SMALL(IF($C$2:$C$12=TRANSPOSE($G$1:$G$2),ROW($2:$12),4^8),ROW(A1)))&''}

TRANSPOSE函数的作用是对数据区域进行转置。

使用TRANSPOSE函数，将G1:G2单元格区域中两个垂直方向的条件转置为水平方向。

然后使用C2:C12单元格区域中的每个元素与之依次对比，得到由逻辑值构成的11行两列的内存数组。

{TRUE,FALSE;FALSE,TRUE;FALSE,FALSE;……;FALSE,FALSE;FALSE,TRUE}

IF函数根据以上内存数组，返回对应的行号或是65 536，结果如下。

{2,65536;65536,3;65536,65536;……;65536,65536;65536,12}

最后使用SMALL函数从小到大依次提取出行号信息，INDEX函数以此作为索引值，返回对应位置的内容。

多个字段的筛选

使用公式进行多个字段的筛选与同一字段中的筛选公式类似，主要是对条件区域范围的选择不同。

示例26-5    筛选指定组别指定产品的记录

如图26-6所示，需要根据G1单元格中的组别和G2单元格中的产品名称，从A~D列中提取出符合两个条件的全部记录。

在F5单元格中输入以下数组公式，按<Ctrl+Shift+E nter>组合键，将公式复制到F5:I8单元格区域。

{=INDEX(A:A,SMALL(IF(($B$2:$B$12=$G$1)*($C$2:$C$12=$G$2),ROW($2:$12),4^8),ROW(A1)))&''}

公式将“($B$2:$B$12=$G$1)”和“($C$2:$C$12=$G$2)”两个判断条件得到的逻辑值对应相乘。如果B列的组别等于G1单元格中指定的组别，并且C列的产品等于G2

单元格中指定的产品，IF函数返回对应的行号，否则返回65 536。

再使用SMALL函数从小到大提取出行号后，由INDEX函数返回对应单元格的内容。

筛选包含关键字的记录

示例26-6    筛选包含关键字的记录

如图26-7所示，需要根据G1单元格中的产品关键字，从A~D列中提取出符合该条件的全部记录。

在F5单元格中输入以下数组公式，按<Ctrl+Shift+Enter >组合键将，公式复制到F5:I10单元格区域。

{=INDEX(A:A,SMALL(IF(ISNUMBER(FIND($G$1,$C$2:$C$12)),ROW($2:$12),4^8),ROW(A1)))&''}

“FIND($G$1,$C$2:$C$12)”部分使用FIND函数以G1单元格中指定的关键字作为查找对象，在C2:C12单元格区域中查找该关键字在每个单元格中首次出现的位置。

如果单元格中不包含指定的关键字，FIND函数返回错误值，否则返回表示位置的数字，该部分公式得到内存数组结果如下。

{#VALUE!;1;1;#VALUE!;#VALUE!;1;#VALUE!;1;#VALUE!;#VALUE!;3}

再使用ISNUMBER函数判断以上内存数组中的每个元素是否为数值，结果如下。

{FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE}

ISNUMBER函数判断后返回逻辑值TRUE的，就是包含关键字的单元格，IF函数返回对应的行号，否则返回65 536。

最后使用SMALL函数从小到大提取出行号后，由INDEX函数返回对应单元格的内容。