统计函数之 SUBTOTAL --函数中的瑞士军刀 2019.

    统计函数一共有三个：subtotal , countif, countifs. 这是第一次碰到完全陌生的函数，在工作中从来都没有用过。学过之后才觉得真好用。今天我先来讲解SUBTOTAL 函数。

---

SUBTOTAL 的英文名意思是分类汇总。 这是一个神奇的函数， 因为它不是一个函数在战斗，而是11 个函数附体！不信你输入SUBTOTAL 函数，然后就会出现这样的画面:

有眼尖的同学一点说，不止11个呀，后面还有101,102.......厄，这其实是这11个函数的一个变体。我们下面再单独讲。

它的基本语法是：SUBTOTAL( 函数，数据区域）。 意思是对参数2 的数据区域域进行参数1 的函数运算。有点绕？举个栗子。

比如这里， 选了1 AVERAGE , 就是计算 B2:B8区域数据的平均值，选了9，就是计算求和。

这时肯定有同学会问了,  计算求和平均值直接用SUM 和AVERAGE 就OK 了，为什么要套个SUBTOTAL 啊？

因为 有了SUBTOTAL 就可以只计算筛选后的 可 见 单 元 格。大家都知道，在EXCEL 中有两种隐藏单元格的方式：筛选和隐藏。

我们先来看筛选的情况

     平时工作中一大串数据，经常会筛选查看，如果用SUM 函数求合计，还是会把所有的单元格都一起计算在内，但是如果用SUBTOTAL，就只计算筛选后的了。

大家仔细看这左边和右边的图。 右边进行了筛选，SUBTOTAL 计算的值就变了。

这个功能真的非常好用，我赶紧用在我的产品销售统计上。把下面的合计改为了用SUBTOTAL 而不是SUM. 当领导突然出现在我面前，说 把这个月各产品销售我看看呢，此时我打开表，来不及做个数据透视了，就直接用筛选，不同的产品一选，下面的销售就自动出来了。哈哈哈。

下面我们再来说隐藏的情况

此时就要提到那个101，102...... 的变异功能了。

从这个表可以看到1和101， 2和102 ....都是一一对应的。

具体怎么运用呢？还是上图举例子。

仔细对比， 右图我们把郭靖隐藏了 ，变异体的101 和109 计算的结果就发生了变化，因为他们只计算了可见单元格，被隐藏的郭靖就没有计算在内。你看，SUBTOTAL 还是只计算了可见的单元格，这就是它的神奇功能。

总结一下：SUBTOTAL 函数无论是1-11 还是1-111 对筛选后的隐藏单元格都不会计算， 对于隐藏后的隐藏单元格1-11会计算但是1-111不会计算。

---

COUNT 与COUNTA

 这里来说明一下这2个函数的区别。

另外，我下面还单独用 COUNT和COUNTA 两个函数做了计算。大家发现为什么计算结果和SUBTOTAL 的不一样呢？嗯，注意表格的左上方的红框，我进行了筛选的。始终记住SUBTOTAL 只显示可见单元格，所以这里是有一项被我筛选掉了。

---

  为隐藏行增加编号

         这是SUBTOTAL 最重要的一个运用之一。

        大家一定遇到过这样的问题：给表格编了序号，但是一筛选，序号就不连续了。SUBTOTAL 就可以解决这个问题。大家记住这个函数公式：' =SUBTOTAL(3,$B$2:B2)*1。 大家看下图：

这个逻辑是什么呢？ 3是COUNTA. 也就是计算非空单元格的个数。上面我们讲了，SUBTOTAL 只计算可见单元格，被筛选掉的单元格就不会被计数了。

这里还有一点要注意：大家看中间的图片，我只筛选了B， 为什么最后一行出现了A 呢？因为SUBTOTAL 这里默认最后一行是汇总行，所以会总是显示。此时我们只要在*1 就OK了。

有同学又要问了，万一我是隐藏，不是筛选怎么办呢? 很简单，把公式中的3 改为103 就OK 拉。

---

智能求总计

   当在表格里面有小计和总计时，我们用SUBTOTAL 函数就可以直接求和。因为SUBTOTAL函数遇到同类就会避开，也就是不会统计由SUBTOTAL计算出来的数值。如下图

在这里，小计都是用SUBTOTAL 计算的。因此合计时就只会计算两个小计的值。这里总计也可以用ALT+=，我验证了结果是一样的，还更快捷。不过主要是让大家知道SUBTOTAL的这个功能。

  以上就是SUBTOTAL 的主要用法。 大家赶快用起来吧!