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

时间:2024-04-17 16:57:55

 


很多人在Excel中用函数公式做查询的时候,都必然会遇到的一个大问题,那就是一对多的查找/查询公式应该怎么写?大多数人都是从VLOOKUP、INDEX+MATCH中入门的,纵然你把全部的多条件查找方法都学会了而且运用娴熟,如VLOOKUP和&、SUMPRODUCT、LOOKUP(1,0/....,但仍然只能对这种一对多的查询望洋兴叹。

 

这里讲的INDEX+SMALL+IF+ROW的函数组合号称“万能”的函数组合,就是解决一对多查询的一种通式,如果你能掌握,那在Excel里基本上就没有什么查询你是实现不了的了(除了INDIRECT+RC引用)。

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

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

 

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所以再加上一个&""。

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

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