Excel：函数中的万金油：INDEX+SMALL+IF

 

Excel里有个号称“万能”的函数组合，这个函数组合就是INDEX+SMALL+IF，很多应用场合都能看到它们的身影。

这个函数的特点是：能提取出满足某条件或某些条件的所有数据，可以一次性返回单行单列或多行多列的结果。

让我们通过一个实例来认识一下它们到底有多“万能”吧！

1、公式应用情景介绍

如下图，A1:F9单元格为某次考试结果，每个人一行数据。现在需要在B11单元格（棕色底色填充）输入班级名称，在A14:F21区域返回该班级所有学生考试成绩信息。

本例假设需要查询【1班】所有学生的成绩。

2、解决问题思路分析

我们的目的是要提取出A列班级名称与B11单元格内容一致的所有数据，如果：1）能提取出符合条件的所有行数；

2）这些行数能从小到大一个一个的输出；

3）可以从原始数据中按第2步输出的行数把数据引用过来，那么问题就解决了。

恰好，在Excel中存在这么几个函数能达到目的，下面我们来逐个介绍。

3、如何提取出符合条件的所有行数？

我们可以使用IF函数判断来生成所有符合条件的行号数组，如下：

=IF($A$2:$A$9=$B$11,ROW($A$2:$A$9))

$A$2:$A$9=B11判断A列数据是否和B11单元格内容一致，会返回TRUE和FALSE的数组。

如果$A$2:$A$9=B11则返回A2:A9的行号，否则返回FALSE。

本例生成的数组为棕色部分（A2=1班，所以返回A2的行号2，以此类推），按F9可以看到如下数组：

{2;FALSE;FALSE;5;6;FALSE;FALSE;9}

如果要满足多个条件，可以用

=IF((条件区域1=条件)*(条件区域2=条件2),ROW(区域))

这种方式来实现，例如要返回【班级】为“1班”且【总分】>200的查询结果，此部分可以改成：

=IF(($A$2:$A$9="一班")*($F$2:$F$9>200),ROW($A$2:$A$9))

4、如何顺序输出上一步获得的行号？

由于上一步获得的符合条件的行号数组是数字和FALSE的组合，我们利用SMALL函数就可以逐步输出符合条件的行号。

举例来说，SMALL({11,22,33},1)=11，也就是SMALL返回第一参数中第1（第二参数）小的数字。

同理，SMALL({11,22,33},2)=22。

为了下拉公式方便，我们一般用ROW()函数做SMALL的第二参数，保证下拉公式时自动提取第2小，第3小……

也就是用：

=SMALL(IF($A$2:$A$9=$B$11,ROW($A$2:$A$9)),ROW(A1))

将第一步产生的数组中符合条件的行号从最小的开始连续输出。从下图可以看到，符合条件的行号已经可以连续输出了。

错误值可以先不用关心，后面我们用其他函数屏蔽。

5、接下来，用函数提取数据

最后一步提取数据可以选用的函数比较多，本例介绍INDEX函数方法。

简单说，INDEX函数可以实现从第一参数中提取出第二参数个数据的目的。

例如：=INDEX({11,21,31},1)返回11，也就是从{11,21,31}提取第1个数字；=INDEX({11,21,31},2)返回21，也就是从{11,21,31}提取第2个数字。

那么，我们用（红色部分是上一步SMALL函数，第一个返回值为2）：

=INDEX(A$1:A$9,SMALL(IF($A$2:$A$9=$B$11,ROW($A$2:$A$9)),ROW(A1)))

就可以返回A1:A9中的第2个值，也就是第1个“1班”；下拉公式就返回了第2个“1班”……

6、完成阶段

我们把上一步的函数组合放在查询结果区域的第一列，Ctrl+Shift+enter运行公式，并下拉，结果如下：

这样就获得了班级的返回结果。

后面要获取姓名和各科成绩等信息怎么办呢？

通过分析，我们知道了SMALL函数输出的结果已经固定不变了，现在只需要改变INDEX函数引用的列即可。

因此我们将INDEX函数的第一参数设置成了A$1:A$9【行绝对引用，列相对引用】，这样，我们向右拉动公式就会自动变成B$1:B$9。

完成效果图如下：

7、屏蔽错误值

可以看到上步产生的结果还有错误值存在，我们用IFERROR函数去屏蔽错误值，如果INDEX函数返回的是错误值，则在单元格返回空。

=IFERROR(INDEX(A$1:A$9,SMALL(IF($A$2:$A$9=$B$11,ROW($A$2:$A$9)),ROW(A1))),"")

这样就最终完成了查询功能的设置。

补充说明

一般还有用INDEX(大区域,SMALL(IF(条件区域=条件,ROW(条件区域),4^8),ROW(A1)))&""这种方式去屏蔽错误值，这种屏蔽错误值的思路是，如果条件区域=条件就返回条件区域行号，否则返回一个比较大的用不到的行号，比如说4^8行或数据区域下面的一个空行行号。这样，INDEX在输出完所有符合条件的行后开始调用4^8行的值（空单元格），为了避免返回0所以再加上一个&""。

本例未使用此种方法，主要是由于后面要查询的其他列有数字，用上述&""的方法数字会变成文本。

如果有日期的时候，日期格式存储的数据会变成一个数字。

---

删除重复数据：这个方法就是使用Excel函数迷们使用的经典的筛选公式，在B2单元格输入公式：=INDEX(A:A,SMALL(IF(MATCH(A$1:A$14,A:A,)=ROW($1:$14),ROW($1:$14),99),ROW(A2)))&""，然后CTRL+SHIFT+回车，三键结束公式输入，然后下拉，就可以提取到唯一值。