excel求和公式技巧：遍地开花的SUBTOTAL函数

编者注：新手总喜欢抱怨“Excel的函数太多了，我记不住，有没有一个函数可以把大部分函数缩短呢？”今天我们要讲的这个函数最大的作用就是可以替代11个函数，另外还可以根据不同的过滤结果来改变计算的最终结果！那个怎么样？是不是看起来很神奇？话不多说，跟小编一起来看看吧！

＊＊＊＊＊。

1忽略过滤行求和

“苗大师，我遇到一个问题。”小白一上班就来找我，她说：“我有一个总表，打字的时候不想发布某些人的内容，用过滤器隐藏一下，但是改起来很麻烦。”每次都查看概览区域。

我说：“这很简单，只要改变sum函数即可。不要用SUM，试试SUBTOTAL。”

小白：“这是什么作品？我没用过。”

我说：“这个函数比SUM函数更强大，可以处理几种求和的情况！”

小白：“很好，那你就教教我吧。”

“那我就详细的告诉你吧~首先，我们先解决一下你的形态问题。”

“现在，您的表使用SUM函数进行求和。让我们将其更改为SUBTOTAL函数并再次检查。”之后，我在单元格A7中输入公式。

总和(9,A2:A6)

“果然变了，小白又检查了其他线路，发现可以得到她想要的结果了。”但后来他发现了新大陆，“那这个9是什么意思呢？”

我：“这个9意味着忽略未过滤的数据，只聚合过滤后的结果。”

小白：“听你说，还有其他数字有其他含义吗？”

我：“当然可以，那我告诉你其他数字的含义吧！”

2忽略隐藏行数

有时我们会遇到这种情况，有一系列数字和一些数据需要隐藏而不进行计算。如果直接使用SUM，将得不到正确的结果，如下图所示。

即使使用刚刚学过的SUBTOTAL函数的参数“9”，也是无法达到的，如下图所示。

此时我们应该考虑更改参数。

下面是参数“109”，出现了！

公式：SUBSUM(109,A1:A5)

如下图所示，将SUBTOTAL函数的第一个参数改为“109”后，就可以得到忽略隐藏行后的总结果了！如下所示。

参数“109”的作用是对可见值求和，可以对隐藏数据或过滤后的数据求和。参数“9”只能用于过滤行，对隐藏行没有影响。

3使用其他小计标准

SUBTOTAL不限于求和字段，还可以计算平均值、最大值、标准差和方差。比如现在我们要计算隐藏隐藏层的最大值，如下图所示。

公式：SUMSUM(104,A1:A5)

隐藏顶部值“8”后，现在可以直接在单元格A6中找到现在可见的顶部值“7”。

那为什么是104呢？其实SUBTOTAL函数中还有一套数值表示的规则，今天我们来聊聊其他参数，包括均值、最大值、最小值、标准差和方差等11个函数。有些是常用的，有些是不常用的，您可以根据自己的需要进行选择。下面是11个参数的对比表。

4扩展第1部分：仅统计分类和汇总

我们在做表格的时候，经常会遇到这样一种总结的情况，总结在一张表中，如下图所示。

如果用SUM来汇总的话，所有的数据都会被统计进去，如下图所示。

但我们只想添加每个小计的内容！不要惊慌，将SUM替换为SUBTOTAL即可得到我们想要的答案。如下所示。

为什么这个？事实上，除了忽略隐藏和过滤行之外，SUBTOTAL还可以使用SUBTOTAL和AGGREGATE函数忽略单元格。单元格B3、B6和B10都是使用SUBTOTAL函数计算的小计，通常，当将SUBTOTAL函数应用于最终总计时，它们将被忽略。如下所示。

5扩展第2部分：不间断的追踪号码

“在我们了解了SUBTOTAL函数的属性之后，我们可以用它来做一些事情，比如将一个数字放入列表中。”

“列表号不能直接用鼠标拖动吗？”

“不一样啊~我的号码是自动的！无论是删除一行还是隐藏一行，号码都可以自动重新排列！”

“这很神奇，所以我必须向它学习。”

实际上，这很简单，假设我有一个列表，并且序列号列当前为空。，如下图所示。

在A2单元格中输入公式：SUBTOTAL(103,B$2:B2)，然后下拉填写，就得到我们想要的序列号。如下所示。

当我们尝试隐藏系列时，我们会发现序列号仍然按顺序排列，没有受到干扰，如下图所示。

现在我们来逐步解释一下公式SUBTOTAL(103,B$2:B2)

103：看上面的参数对比表，可以看出103的作用是忽略隐藏行和过滤值，统计非空单元格的数量。

B$2:B2:A2单元格中的区域为B$2:B2目的是统计B2:B2区域中非空白单元格的个数，结果为1。执行下面的公式后，单元格A3变为B$2:B3，则统计到的非空单元格数量变为2，结果为2。如下所示。

以此类推，当一个公式画下来时，我们可以找到一组连续的连续数字。结合SUBTOTAL函数的第一个参数只计算可见值的特性，你可以得到一组不间断的连续数字！

什么？关于SUBTOTAL函数的良好使用，您还知道什么？让我们一起交流、学习吧！

＊＊＊＊部落巢教育-excel中不同的汇总方法＊＊＊＊

原文：苗旭/部落沃教育（未经许可请勿转载）