SUBTOTAL函数进阶教程

SUBTOTAL是一个多功能函数，第一参数支持11种聚合函数，再加上2种模式，非常强大。

但是这写都是基础，我们今天要聊一下进阶用法！进阶用法主要利用两点

如果你先学习基础可以阅读此篇：SUBTOTAL函数详解

1、第一参数支持数组

2、第二参数支持三维引用，且必须是单元格区域！所知最佳搭档是OFFSET

我们通过两个案例详解来，学习上面两点！

案例1 |  全部测试的最好测试成绩平均值

=AVERAGE(SUBTOTAL(4,OFFSET(C2:G2,ROW(1:5),)))

这个是不通过辅助列，直接完成结果的，我们使用一般的处理方式来进行校验

一般处理方式是先在旁边新增一列辅助列，然后使用MAX函数取出最大值，

最后通过AVERAGE函数对取出的最大值求平均值！

结果完全OK

公式详解

1、这里的要点是在第二参数支持三维引用，而这里的OFFSET结果生成了

5个平面组成的三维，如下图！

=OFFSET(C2:G2,ROW(1:5),)

这些平面相对独立，空间上呈现“三维”，而我们说SUBTOTAL支持三维，所以他可以根据第一参数4-MAX，对这5个平面分别求最大值，而且互不干扰！

关于OFFSET三维的一些知识，可以阅读此篇进一步学习：

函高 | OFFSET进阶引用之参数数组化

2、5个平面每个一个最大值，求出后，形成内存数组，我们就可以使用AVERAGE等函数进行后续处理！

案例2 |  第一参数数组化

▼非365请选择区域三键录入

=SUBTOTAL({4,5},OFFSET(C2:G2,ROW(1:5),))

公式解析

1、第一参数使用{4,5}常量输入，或把我们每个人成绩的最大值和最小值都求出来，形成内存数组，O365的同学可以跟演示一样，回车即可看到动态扩展的数据结果，非365，请选择较大区域，三键录入查看

2、如果我们先把最大值和最小值分别求和怎么办呢？这个其实做适合的就是MM函数-MMULT

=MMULT(COLUMN(A:E)^0,SUBTOTAL({4,5},OFFSET(C2:G2,ROW(1:5),)))

如果你现在还是看不懂MM函数也不要紧，确实属于进阶函数类，先学习的同学也可以阅读扩展：这个MM函数没你想的那么可爱

OK，今天我们就先到这里，如果对你有帮助，记得动动小手~

感谢（收藏，点赞、在看、转发）