function函数的用法_Excel中的神器SUBTOTAL函数，你真的会用吗？

提到数据汇总，绝大部分同学都会想到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个小计单元格也包含了进去，但是并不会参与运算，这就很优雅解决了总计求和的繁琐公式问题。

这样的话是不是就能少写非常多的公式了呢，超级便捷，下班又可以早一点了。