Excel合计为什么翻倍?多个小计用SUBTOTAL避免重复求和(WPS适用)
- 2026-11-07 03:35:25
明细加起来明明是600，表格最下面的总计却变成1200。回头看，每个小组的小计也没算错，问题究竟出在哪？
先看一下：你的求和范围里，是不是既有明细，又有小计？如果把它们一起交给SUM，同一笔数就可能被算两遍。今天用一张小表，讲清SUBTOTAL怎样跳过嵌套小计，以及9和109怎么选择。
这篇适用于常见桌面版Microsoft Excel与支持SUBTOTAL的WPS表格。Excel 2016、2019、2021、2024和Microsoft 365均可使用，不需要动态数组，也不用按Ctrl+Shift+Enter。本文只演示同一张普通工作表里的纵向金额列，不涉及跨文件、多维表格或数据模型。
一、先摆好这张明细和小计混排的表
在A1写“项目”，B1写“金额”。A2、A3都填“甲组”，B2填120、B3填180；A4填“甲组小计”，B4先填数值300。A5、A6都填“乙组”，B5填90、B6填210；A7填“乙组小计”，B7也先填数值300。A8写“总计”，B8暂时留空。
这些都是虚构练习数据，金额按数值输入，不在单元格里加“元”等文字。四条明细合计是120加180加90加210，得到600；两条小计各为300，描述的仍是同一批明细，不是额外发生的金额。
先确保所有行都显示，没有启用筛选，也没有手动隐藏。这样容易对照后面的结果。B4、B7此时是手填的小计数值，后面会把它们改成公式，这个顺序不要省略。只改最下面的总计，却保留上面的手填小计，不能达到本篇演示的效果。
为了方便练习，把表复制一份再操作，并保留“项目”列的文字，用来辨认明细和小计。文字只是供人核对，函数并不会看到“小计”两个字就自动判断该跳过这一行。后面真正起作用的是单元格里的公式类型。
二、为什么用SUM会得到1200？
第1步：单击B8，输入下面的公式，再按回车。
=SUM(B2:B7)第2步：检查结果，应为1200。四条明细一共600，再加上B4、B7里的两笔300，总数就成了1200。SUM只按照你给出的范围求和，不知道这两笔300已经代表上面的明细，也不会根据A列的“小计”文字替你排除它们。
这并不表示SUM不能用于总计。如果一张表只有明细，没有夹杂小计，正常求和就很合适。这里要解决的是明细和小计混在同一段范围里，被重复加总的问题，换函数前先确认自己遇到的确实是这个结构。
也可以只挑两格小计相加，但表格以后插入新组、调整行数，就需要继续检查有没有漏选。本篇用SUBTOTAL建立小计与总计的关系，让总计在引用整段区域时识别里面的SUBTOTAL结果。
三、小计和总计，都改用SUBTOTAL
第1步：选中B4，把原来的数值300替换成下面的公式，按回车。
=SUBTOTAL(9,B2:B3)第2步：选中B7，把另一笔手填300替换成下面的公式。
=SUBTOTAL(9,B5:B6)这两处仍分别显示300，但含义已经不同：它们现在是由SUBTOTAL计算出来的小计。第一个参数9表示“求和”，后面的区域是本组明细。小计只引用本组明细，不要把小计单元格自己也选进去。
第3步：回到B8，把刚才的SUM公式替换为下面这一条。
=SUBTOTAL(9,B2:B7)现在B8应为600。虽然引用范围里仍包含B4、B7，但它们是其他SUBTOTAL公式的结果，外层SUBTOTAL会忽略这些嵌套小计，避免重复计算。120、180、90、210四条明细被计入，两条小计不会再加一次。
这三个位置都要核对：B4、B7是小计公式，B8是总计公式。不要因为显示值没变，就以为前两步可做可不做。先把小计建立好，再计算总计，才是本例得到600的完整前提。
四、9和109怎么选？先分清筛选与手动隐藏
两者都能求和，也都会忽略引用区域内其他SUBTOTAL的小计。区别主要在手动隐藏的行：9仍计入手动隐藏行，109会排除手动隐藏行；被筛选排除的行，两者都不计入。
第1步：保持上面的小计公式不变，把B8改成下面这一条，作为109的对照。
=SUBTOTAL(109,B2:B7)全部明细都显示时，用9或109，B8都是600。接着右键第3行的行号，选择隐藏，把金额180所在的整行藏起来：9仍得到600，109应为420。这里隐藏的是行，不是把字体改成白色，也不是让单元格显示空白。
第2步：先取消第3行的手动隐藏，恢复全部行。然后选中A1:B7，通过“数据→筛选”给表头加上筛选按钮。在“项目”列只选“甲组”，不选“甲组小计”。B8放在筛选区域之外，便于始终看见总计。
这时只有金额120、180两条甲组明细符合筛选，B8不论使用9还是109都应为300。对照时只替换B8的公式，B4和B7保持前面的SUBTOTAL小计公式。练习完取消筛选，恢复全部行。
所以不能简单记成“9统计所有行、109统计可见行”：9同样会响应筛选。也不是109永远比9更正确，应该先确定你是否希望手动隐藏的金额仍参与统计。如果原本只是为了方便看表而折叠部分行，却想保留它们的金额，随意改成109反而会少算。
这个区别针对纵向区域中的行。隐藏某一列，不等于从求和中排除那一列；本篇也不把它当作按颜色求和或条件求和的方法。需要指定部门、月份等条件时，应另外设计筛选或条件统计规则。
五、写了SUBTOTAL还翻倍？先检查小计是什么
如果B8仍然得到1200，先点一下B4、B7，看编辑栏里到底是公式还是数字。只是在旁边写了“小计”，或者把300粘贴成数值，都不会变成可被外层忽略的SUBTOTAL结果。
如果这两格用的是普通SUM小计，也不能指望外层SUBTOTAL自动把它们剔除。假如两条小计都仍是手填300，或者都由普通SUM算出300，本例总计仍可能是1200。先核对每一处小计，不能只看总计格的函数名。
不要对整张正式报表一键替换所有SUM。不同位置可能是在算明细、组内小计，或独立的费用合计，引用关系不一定相同。先找到重复计数来自哪里，再对需要组成嵌套汇总的那几处建立正确关系，改完仍要用原始明细核对。
六、用到自己的文件前，再查这几件事
总计放在B8，引用只到B7，不能把B8自己也包含进去，否则可能形成循环引用。小计同样只引用本组的明细，不要把其他组的小计或本行总数混进去。后来增加新明细、新小组，也要复核每处引用范围，不能把“会重新计算”理解成“永远不会漏行”。
金额里有文本、错误值或不同口径的数字，换成SUBTOTAL也不会自动修好。先确认金额确实是数值，检查单位与含义是否一致，再做统计；遇到错误不要为了让总计看起来正常就一律替换成0。SUBTOTAL解决的是汇总方式，不负责判断原始记录是否真实、是否该合并。
如果结果与本例不同，先恢复筛选和手动隐藏状态，回到四条明细都显示的情况，逐一核对B4、B7、B8的公式。再只做一次隐藏或一次筛选，看变化是否符合预期。把几种状态一起改，容易把“公式选错范围”和“隐藏行口径不同”混在一起。
Excel和WPS本篇的函数名、参数编号及示例写法相同，都是普通回车确认；筛选按钮和隐藏菜单的外观可能随版本变化。这里只手工输入公式，不要求使用“分类汇总”向导，也不需要新版动态数组函数。先在这张小表走完一遍，再迁移到自己的文件。
记住：明细600、两条小计各300，不代表总计应该1200。先识别每一行的角色，再用正确的小计公式与总计公式连接起来；筛选和手动隐藏，则按9与109的规则分别核对。
