Excel函数组合(四)INDEX + SMALL + IF组合（excel函数组合使用）

大家好，我是永不止步的老牛。

Excel有很多功能强大的函数，如果函数间能组合使用，那就是强强联手，今天我们先看第3组强强联手“INDEX+SMALL+IF”组合。

我们先看一下效果：

这3个函数的组合主要是为了解决一对多的问题，根据一个条件查询，返回多个结果，是个常用组合。

思路是：

• 用IF函数在某列中根据条件匹配数据，满足条件的返回所在行号，不满足条件的就返回一个比较大的数值，IF函数返回的是一个数组；
• SMALL函数在数组中按1，2，3...顺序取出对应的行号，把比较大的数值(超出数据区域)过滤掉；
• INDEX按SMALL返回的行号在区域内获取数据。

分别看一下3个函数的语法和含义：

动图中M2的公式为：

=INDEX($C$2:$C$9,SMALL(IF($A$2:$A$9=$L$2,ROW($A$2:$A$9)-1,4^8),ROW(A1)))

我们解析一下这个公式，公式中IF函数部分：

IF($A$2:$A$9=$L$2,ROW($A$2:$A$9)-1,4^8)
是用IF函数在区域$A$2:$A$9中查找满足条件(=$L$2)的数据，满足条件就返回行号(减掉表头的1行)，不满足就给一个比较大的数字(4^8)，返回的是一个数组{1;65536;3;65536;65536;65536;7;65536}；

用这个数组替代SMALL函数的第一个参数，那么公式中SMALL部分就变成：

SMALL({1;65536;3;65536;65536;65536;7;65536},ROW(A1))

第二个参数用行号返回取第几个数据，往下填充时会每行加1，为的是产生1，2，3...这样的序列，那么M2返回数组中最小的那个是1，M3返回第2小的是3...；

M2最后的公式就是INDEX($C$2:$C$9,1)，就是“张三”；
M3最后的公式就是INDEX($C$2:$C$9,3)，就是“王五”；

因为函数返回的是数组，所以写好公式后，需要按Ctrl+Shift+Enter结束。

我们扩展一下，根据一个条件查询，可以返回任意列多个数据，INDEX函数可以根据行列获取数据，刚才的公式我们只用了INDEX函数的行，列需要呼叫MATCH函数来动态获取，我们把刚才的公式修改一下：

INDEX($B$2:$J$9,SMALL(IF($A$2:$A$9=$L$2,ROW($A$2:$A$9)-1,4^8),ROW(A1)),MATCH(M$1,$B$1:$J$1,0))

红色部分是改动的地方，一是把INDEX函数去区域扩大，包含多行多列，二是用MATCH函数给INDEX函数当第3个参数(返回数据的列)，MATCH(M$1,$B$1:$J$1,0)表示根据选择的列M1，返回在区域中的位置(第几列)，我们看效果：

完美，至于根据一个条件返回多行多列并生成图表，因为牵扯到行数的变化，不同版本的Excel，图表功能略有不同，可以这样实现：

这样处理有个瑕疵，就是选3组时，图表的第3个序列是个空白序列，虽然没有线，但是图例中没有消失掉，可以用VBA来解决，因为篇幅原因，单独开一篇去介绍如何处理。

Excel函数组合持续更新中，如果对你有帮助，请关注点赞支持一下。

Excel函数组合(一) - VLOOKUP和MATCH组合

Excel函数组合(二) - INDEX和MATCH组合

Excel函数组合(三) - SUM和SUMIF组合