Excel数据统计教程：SUBTOTAL函数用法详解

编按：在Excel里有个神奇的函数：SUBTOTAL。据说这个函数从Excel2003就存在了，可是现在都2022了，还有很多人不了解他的妙用，今天就和大家再来认识一下SUBTOTAL函数。

妙用1：对筛选数据求和

可以看到，公式=SUBTOTAL(9,D2:D22)可以按照筛选的结果实现动态求和。

但是请注意，这个公式只对筛选有效，如果要排除隐藏数据则需要对公式进行修改。

妙用2：忽略隐藏数据求和

将公式改成=SUBTOTAL(109,D2:D22)，就能忽略隐藏行的数据求和。

这个公式同时也对筛选有效，这就是SUBTOTAL函数第一参数的神奇之处。

可以这样说，这个函数的秘密全部都在第一参数，第二参数只是选择要进行统计的数据区域，并没什么特殊的。

至于第一参数都有些什么秘密，可以参照下面这个图片。

注意功能参数代码的区别，只是包含或者忽略隐藏行，对于筛选行都是有效的。

计算方式则体现了这个函数的多功能特性，前面两个示例中用到了9和109，都是同样的计算方式求和。

另外几个比较常用的统计方式例如：平均值、最大值、最小值等等，只需要改成对应的代码即可。

妙用3：对筛选后的数据求最大值。

=SUBTOTAL(4,D2:D22)

通过上述示例，发现一个问题，A列的序号经过筛选以后变得不连续了，怎么办呢？

正好可以使用SUBTOTAL来得到一个动态连续序号的效果。

妙用4：筛选后的连续序号

公式为=SUBTOTAL(3,$B$1:B2)-1

在这个例子中，数据区域的用法是有点小技巧的，锁定了区域的开始位置，但是结束位置则是会随着公式下拉发生变化，这样就实现了区域递增的目的。

细心的同学可能会有个疑问，公式用=SUBTOTAL(3,$B$2:B2)不就行了吗，为什么非要从第一行开始，然后再减一，是不是多此一举。行不行试试便知。

通过测试可以看到，不管筛选条件是什么，最后都有一行是一直出现的，序号虽然连续了，但是多出来一行数据也不行啊。

为什么会出现这种情况？

还是要从SUBTOTAL函数本身的特殊性来解释，这个函数也叫分类汇总函数，通常情况下总是会将最后一行默认是汇总行，所以不受筛选的影响一直都显示。

如果不想出现这种默认汇总行的话，则需要在函数后面添加一个计算，加减乘除都可以。

最后一个动画的公式改成=SUBTOTAL(3,$B$2:B2)*1实际上也是可以的，有兴趣的同学可以自己测试一下。

好啦，以上就是今天的所有内容，感谢你的收看。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

学习交流请加微信：hclhclsc进微信学习群。

相关推荐：

如何计算员工累计达标的月份，你需要一个SUBTOTAL函数！

去掉最低分和最高分算平均分：SUBTOTAL等四个函数法

别怕，VBA入门级教程来了，条件语句很简单！

八大查找函数公式，轻松搞定数据中的多条件查找

版权申明：

本文作者老菜鸟；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。