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

2019-11-18  本文已影响0人  一阐提lulu

    统计函数一共有三个: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 的主要用法。 大家赶快用起来吧!

上一篇下一篇

猜你喜欢

热点阅读