提到数据汇总,绝大部分同学都会想到SUM/AVERAGE等常用函数。
然而却有一个万能的统计函数,拥有至少11 种功能。
却因为名字太难记忆几乎被90%的职场人忽略,它就是SUBTOTAL函数。
其实这个函数学习起来也没有这么难,这一个函数就相当于:平均值、技术、最大值、最小值、乘积、标准差、求和、方差、非空单元格等等共计11个函数。
并且SUBTOTAL也是Excel智能表中的默认求和函数,比SUM函数拥有更加优良的特性。
花了一个下午的时间,整理了一篇关于SUBTOTAL函数的教程,本文共包含该函数的6种高效使用技巧。
SUBTOTAL基础用法
不得不提一下,SUBTOTAL是Excel中唯一一个能统计用户可见单元格的函数,所以如果碰见筛选/隐藏单元格,首选使用这个函数。
官方对这个函数的介绍是返回列表或数据库中的分类汇总,也就是说这其实是“一群函数”,来简单看下它的语法:
参数非常简单,后面的ref直接看成是单元格区域即可。
重点在于function_num这个参数的相关配置,这个参数的范围从1~11或者101~111,刚好相当于11个函数,这里已经帮大家列出来了。
当function_num=1的时候,也就意味着有以下公式的成立:
同理当function_num=2的时候,SUBTOTAL函数就变成了COUNT函数,以此类推。
同理当function_num范围是101~111的时候也是这样理解,现在是不是一目了然了呢?
忽略隐藏值
那么问题来了,当function_num的范围从101~111的时候,跟1~11有什么区别呢?其实最大的区别就是:
① 1~11的时候,隐藏的单元格也会参与计算(例如隐藏了不可见单元格);
② 101~111的时候,隐藏不可见的单元格不会参与计算;
芒种君来给大家举一个小小的例子,在下图中,将5/6/7/8这4行给隐藏掉,当function_num等于9的时候计算结果为459,而等于109的时候计算结果为292。
这就证明了当function_num以10开头的时候,是不会将隐藏单元格参与运算的,而这也是这个函数与SUM函数的第一个差异。
筛选数据下的SUBTOTAL
除了隐藏单元格之外,筛选数据也会产生不可见单元格,那么在筛选下SUBTOTAL会有什么特征呢?
我们来尝试将上面那一份函数的案例修改成筛选的情况,筛选出所有的单数,如下:
可以发现,无论function_num为9或者109,计算的结果都是308,也就是说,在筛选情况下SUBTOTAL永远只会计算可见单元格(针对11个函数)。
而SUM函数默认是将所有的单元格全部参与运算了。
这也是SUBTOTAL和SUM等函数的第2个差异。
SUBTOTAL的计数功能
接下来看下这个函数的计数功能,当function_num等于2或者3的时候,就化身成为COUNT和COUNTA函数。
来看一个简单的小案例:
可以发现当function_num=2的时候,只会统计数字单元格的数量,这里的“无业绩”并非数字。
所以计算的结果结果为9,实现了COUNT函数的功能。
而当function_num=3的时候统计非空单元格,这里并没有空单元格,所以结果为10,跟COUNTA函数一模一样。
没有什么太神秘的地方,当成COUNT和COUNTA使用即可。
自动编号功能
“如何在筛选后的表格输入连续的序号”,这是一个令很多Excel使用者崩溃的问题,其实SUBTOTAL就可以轻松实现这个需求。
例如筛选出电脑产品后,要给工号依次录入序号:
如果我们直接下来填充之后,发现所有的序号都变成了1,永远都填充不出1、2、3、4这种序号。
其实输入公式:=SUBTOTAL(3,$C$3:C3)*1,然后双击下拉填充,就可以轻松完成这个需求:
那么为什么要在公式的最后写多一个*1呢?加上*1之后,如果我们的最后一行没有被筛选出来,双击下拉填充的时候,公式也能填充到最后一行。
这是一个非常棒的技巧,也是非常细节的地方。
避免同类计算
这是一个制作财务报表经常会出现的小烦恼,同时使用了小计和合计,合计的时候需要使用多个SUM函数相加才能统计出来,非常繁琐。
其实SUBTOTAL还有一个特性,就是不会将由SUBTOTAL计算出来的数值参与运算,这句话怎么理解,看下下图相信你就明白了。
上图中,小计和总结中都使用了SUBTOTAL公式,虽然在总计中中将2个小计单元格也包含了进去,但是并不会参与运算,这就很优雅解决了总计求和的繁琐公式问题。
这样的话是不是就能少写非常多的公式了呢,超级便捷,下班又可以早一点了。