Excel筛选后总计没有变化?对比SUBTOTAL的9和109

VBA・Officeカテゴリを表すパンダのイラスト VBA・Office

关于本文本文是通过利用生成式AI的自动化生成流程创建的。我们确认了Microsoft Support关于SUBTOTAL函数的说明,并整理了筛选与手动隐藏时总计发生变化的条件。

验证状态:📘 已确认官方规范·Excel实机未验证

适用环境、功能与限制(确认于:2026年11月17日)

项目对象与条件
目标产品Windows版 Excel for Microsoft 365、Excel 2024・2021
操作位置桌面版Excel的新建工作簿、数据筛选与行隐藏
许可与权限可使用Excel的环境。无需Copilot、管理员权限或外部服务
保存位置电脑上的练习工作簿。无需上传至OneDrive或SharePoint
功能区分并通过求和排除筛选掉的行以及手动隐藏的行
限制实机屏幕操作、网页版Excel的界面差异以及打印结果尚未验证

在Excel的销售表中,即使只显示“东京”,SUM函数的合计依然保持全国的总数,这并不是故障。SUM通常会统计因筛选而隐藏的行。如果只想对屏幕上显示的行进行求和,请使用SUBTOTAL函数。但是,被筛选排除的行与通过右键单击等方式手动隐藏的行处理方式有所不同。如果混淆了这一点,可能会导致同一个文件在不同经办人手中出现数字不一致的问题。

使用虚拟销售表进行测试

在一个新的空白工作簿中,不是输入接下来的4列,而是先输入2列。无需直接编辑现有的销售台账。

单元格A列:地区B列:金额
第1行地区金额
第2行东京100
第3行大阪200
第4行东京300
第5行大阪400

请在表格外侧放置汇总公式。在 D2 中输入 =SUM(B2:B5),在 D3 中输入 =SUBTOTAL(9,B2:B5),在 D4 中输入 =SUBTOTAL(109,B2:B5)。初始状态下,预计这些值都将是 1000。公式的第一个参数 9 和 109 都表示“求和”,其中 109 用于排除手动隐藏的行。

接下来,选中 A1:B5 并启用[数据]→[筛选],将地区选项仅设为“东京”。显示的金额为 100 和 300。此时,根据官方规范预期的结果是:D2 中的 SUM 保持 1000 不变,而 D3 和 D4 中的 SUBTOTAL 则变为 400。无论使用 9 还是 109,被筛选排除的行都不会被计入汇总。如果记住“只有 109 支持筛选”,则会产生误解。

操作SUMSUBTOTAL(9)SUBTOTAL(109)
显示所有行100010001000
仅按东京筛选地区1000400400
取消筛选后,手动隐藏第 3 行10001000800

表格中的数值并非实机测得值,而是根据微软官方规格计算出的预期值。请在您自己的 Excel 中确认是否一致。

只更改一个条件:隐藏行而不是使用筛选

取消筛选以恢复显示所有行。这次请右键点击行号“3”,并选择“隐藏”。大阪 200 的那行将不可见。在这种情况下,9 包含手动隐藏的行,所以是 1000;而 109 不包含手动隐藏的行,所以是 800。这是观察 9 和 109 之间差异的最简单实验。

为什么同样是“不可见的行”,结果却不同?在 Excel 中,因不符合筛选条件而未显示的行,与用户直接隐藏行本身的状态被视为不同的条件。根据微软支持的说明,SUBTOTAL 函数明确指出:1 到 11 包含手动隐藏的行,101 到 111 会将其排除,而通过筛选排除的行无论编号如何都会被忽略。

数字不符时的排查方法

首先分别确认“是否应用了筛选”、“行号是否不连续”以及“是否有手动隐藏的行”。当 SUM 和 SUBTOTAL 的结果不同时,不要武断地认为是公式错误,而应检查显示状态。同时请注意,仅取消筛选并不会解除手动隐藏。

接下来,检查公式的引用范围是否为 B2:B5。将求和单元格包含在引用范围内会导致循环引用或意外的汇总结果。还要检查其中是否混入了看似数字的字符串、空白或错误值。不要仅凭外观判断,检查目标单元格的编辑栏将有助于排查问题。

此外,SUBTOTAL 是针对纵向列数据设计的函数。如果在横向排列的按月数值中隐藏列,则不能直接期望它表现出 109“排除手动隐藏”的行为。另外,当引用范围内存在另一个 SUBTOTAL 时,会涉及避免重复计算的处理。在混有多个汇总行的报表中,请勿仅凭与简单 SUM 的对比来判定对错。

在业务中使用时确定求和的含义

如果销售人员希望通过限定地区来查看“当前屏幕上显示的案件”的金额,那么 SUBTOTAL(109,范围) 是一个直观易懂的选择。另一方面,如果在月度报告中固定显示所有分公司的销售额,那么使用不受显示状态影响的 SUM 可能会更合适。哪种正确取决于报表的目的。

在共享工作簿中,请向用户明确说明:操作筛选的人员的显示状态与汇总结果是联动设计的。切勿将报告用的确定值与工作中的筛选汇总混用相同的“合计”标签。将筛选结果的屏幕截图粘贴到资料中时,若能同时保留条件名称和汇总公式,日后便能还原数字。

官方信息与未确认事项

第一手资料是微软支持“SUBTOTAL 函数”。我们确认了 9 和 109 的区别、筛选的处理方式以及纵向范围的限制。文章中的 1000、400、800 是根据虚拟数据和规范得出的期望值,并非 Excel 实机操作结果。屏幕显示、版本差异、协同编辑以及发布到 WordPress 后的显示效果均未经验证。

如果您先决定是只想汇总屏幕显示的行,还是连同隐藏的行一起汇总整个表格,就可以根据目的灵活选用 SUM、SUBTOTAL(9) 和 SUBTOTAL(109)。

文档信息

文章??
Excel筛选后总计没有变化?对比SUBTOTAL的9和109
?布日期
更新日期
来源
https://papanda925.com/?p=18711&lang=zh

?可: ?于本站?有相??利的正文及原??表,除非?有?明,可依据 CC BY 4.0 使用。本文可能包含使用生成式AI?建或??的内容。若代??有?可声明,或?接的GitHub???定了?可,?代?以??可?准。引用内容、第三方?料、?片及商?不在本?可范?内。 使用政策

标题和URL已复制