Excel 办公技巧之 SUBTOTAL

辛辛苦苦把明细表按区域筛了一遍，只看北京的数据，结果下面的合计数还是把全国的都加进去了。

你以为是公式写错了，回去检查=SUM(C2:C5000)，没错啊。

但你忘了一件事：SUM这个函数，不管你筛不筛选，它都是把整个区域里所有的数全加一遍。隐藏的行它也加。

于是你看到的明细是北京的，合计数是全国的，这种尴尬每个做过报表的人都遇到过。

解决办法不是手动改公式，而是换一个函数SUBTOTAL。

一、SUM和SUBTOTAL的区别到底在哪

很多人以为SUBTOTAL就是个"高级版SUM"，其实不是。它最核心的能力是：你筛选什么，它就只算什么。

说白了：

SUM 是"我不管你看什么，我就算整个区域"

SUBTOTAL是"你给我看什么，我就算什么"

做报表的时候，你筛筛选选看明细是常态，如果合计数不跟着变，那这个表就是废的。

二、语法：第一个数字是灵魂

SUBTOTAL 的语法长得有点奇怪：

=SUBTOTAL(功能代码, 要算的区域)

第一个参数是个数字，代表你要算什么，这也是大部分人搞不懂的地方。

你没看错9 和 109 都是求和，区别在于：

9：筛选出来的行算，手动右键隐藏的行也算

109：筛选出来的行算，手动右键隐藏的行不算

大部分人用了几年SUBTOTAL都不知道还有这个区别。

那到底用9还是109

看你怎么隐藏行：

如果是通过"筛选"功能看的用9就行，筛选本身就会让SUBTOTAL 忽略隐藏行

如果你是手动右键"隐藏行"来调整显示的用 109，它才会真的忽略掉

日常做报表，9用得最多。

三、真实场景：做一个能跟着筛选自动变的明细表

几千条订单销售明细表，从第2行到第5000行，以前的做法

你在表最下面写了一行合计：

=SUM(D2:D4999)

然后你想看看华中卖了多少点筛选，只勾华中。

明细是变了，但下面的合计数还是全国的总数。

你又得另找个格子写 =SUMIF(B:B,"华中",D:D)，每个区域写一个，写十几个。

现在的做法，把合计那行的公式改成：

=SUBTOTAL(9, D2:D19)

就这一个改动。

然后你再筛：

筛"北京"合计数自动变成北京的

筛"华中"合计数自动变成华中的

取消筛选合计数自动变回全国的

不用写十几个SUMIF，不用切换公式，就一个SUBTOTAL，跟着你的筛选走。

而且这个合计数是嵌在表里的，不是单独拿出来的你往下翻明细的时候，它就在最下面，永远跟着你看的范围变。

四、一个隐藏的好处：自动排除其他SUBTOTAL行

这个功能大部分人不知道，但特别好用。

假设你的表里有几行小计比如北京一个小计、华东一个小计，每个小计都是用 SUBTOTAL算的。

然后你在最下面算全国总计，用 SUBTOTAL(9, D2:D4999)。

它自动把上面那几个 SUBTOTAL 的小计行排除了，不会重复加。

而如果你用SUM(D2:D4999)，它会把小计行也加进去，结果就翻倍了。

这就是为什么很多人做分级汇总的时候，一级一级全用SUBTOTAL最上面的小计、中间的分类、最下面的总计，全用SUBTOTAL，它自己知道哪些是已经汇总过的行，不会重复算。

这个特性是SUM做不到的，SUM不管你是不是小计行，它都加。

五、常见坑

坑1：为什么我改成SUBTOTAL之后，筛选了还是不变？

检查一下你是用"筛选"功能还是手动右键隐藏的。

SUBTOTAL(9, ...) 只忽略"筛选出来的隐藏行"，不忽略你手动右键隐藏的行。

手动隐藏的，要用109才会忽略。

坑2：#VALUE! 错误

一般是因为第一个参数写错了SUBTOTAL只接受固定的那十几个代码，写了别的数字就报错。

对照上面的表抄就行。

坑3：和透视表的区别

有人说，我用透视表不就行了？

透视表是透视表，明细表是明细表。你要的是"原始明细数据 + 下面跟着筛选自动变的合计"这是明细表该有的样子。

透视表是汇总表，你不能拿它当明细用。而且透视表你要钻取、要看每一条订单，它就不灵活了。

六、什么时候用 SUM，什么时候用SUBTOTAL

固定的数用SUM，会跟着筛选变的数用SUBTOTAL。

七、最后说两句

SUBTOTAL 这个函数，很多人用了好几年Excel都没注意到。

但一旦你知道"筛选之后合计数应该自动跟着变"这件事，你就再也回不去了以前那种筛了半天、下面还是全国总数的表，看着就难受。

它不是什么高级的新函数，也不是什么黑科技。就是一个"做明细表的时候该用的合计方式"。但就是这么一个小东西，能让你的报表从"看着像草稿"变成"看着像专业的"。

老板筛一下区域，总数自动跟着变他不用问你"这个数是全国的还是北京的"，因为表本身就回答了。