Excel 中的 SUBTOTAL() 公式：完整计算指南

Excel 用户常常需要能够随筛选或分组而变化的灵活汇总方式。传统函数如 SUM()、AVERAGE() 或 COUNT() 会基于区域内的所有单元格计算结果，而不管某些行是否被隐藏或筛除。SUBTOTAL() 提供了一种动态替代方案，可根据工作表当前可见内容自动调整计算结果。

如果您刚接触 Excel，想在学习 SUBTOTAL() 等高级函数前先打好基础，我们提供的 Introduction to Excel 课程涵盖工作表导航、基础公式和数据格式设置等核心技能，为更高阶的 Excel 应用做好准备。

什么是 Excel 中的 SUBTOTAL() 函数？

SUBTOTAL() 用于对数据集执行求和、平均、计数及其他统计运算。与标准 Excel 函数不同之处在于它具有动态特性——会根据可见或被筛选的内容改变结果。根据您指定的函数编号，它可以忽略手动隐藏的行以及通过筛选隐藏的行。

当您对数据应用筛选或手动隐藏某些行时，SUBTOTAL() 会自动调整，只对可见单元格进行计算。这使其非常适合在筛选数据集中创建汇总行、构建交互式仪表板，或生成需根据用户选择实时更新的报告。

该函数支持与 Excel 标准函数相同的统计运算——包括求和、平均、计数、最大值、最小值等——但将它们打包成一个可感知上下文、能随数据当前状态自适应的函数。

语法与支持的运算

SUBTOTAL() 的语法结构十分直观：

=SUBTOTAL(function_num, ref1, [ref2], ...)技术支持

第一个参数 function_num 同时决定计算类型以及对隐藏行的处理方式。其余参数（ref1、ref2 等）为要参与计算的单元格区域。最多可指定 254 个不同区域。

根据对手动隐藏行的处理方式，函数编号分为两类：

函数编号 1-11 在计算中包含手动隐藏行，而 101-111 则排除手动隐藏行。但两组编号都会忽略通过筛选隐藏的行。

例如，SUBTOTAL(9, A2:A10) 会对 A2:A10 求和并包含任何手动隐藏行，而 SUBTOTAL(109, A2:A10) 则在求和时排除手动隐藏行。两种情况下，被筛选隐藏的行都会自动被排除在结果之外。

SUBTOTAL() 实战示例

下面通过一家电子与家具商店的销售数据示例来了解 SUBTOTAL() 的实际效果。

未筛选数据集。图片来源：作者。

示例数据包含 7 个产品，分属两类——4 个电子产品（Laptop、Phone、Tablet、Monitor）和 3 个家具产品（Desk、Chair、Lamp）。在未应用筛选时，SUBTOTAL(109,C2:C8) 与常规 SUM(C2:C8) 的结果相同：3525（所有销售额之和）。

已筛选数据集。图片来源：作者。

现在我们应用筛选，仅显示电子产品，隐藏了 3 行家具产品。请注意计算结果的关键差异：

• SUBTOTAL 公式 显示为 3000 ——自动仅对可见的电子产品行求和
• 常规 SUM 公式 仍显示 3525 ——继续包含被隐藏的家具产品

这一对比清楚展示了 SUBTOTAL() 的核心优势：它能对筛选与可见性变化作出动态响应，而 SUM() 等传统函数则保持静态。当您筛选数据或隐藏行时，SUBTOTAL() 会即时重新计算，仅反映当前可见内容，非常适用于响应式仪表板和交互式报告。

SUBTOTAL() 与隐藏/筛选数据的协同方式

SUBTOTAL() 对不同类型的隐藏数据有特定处理方式，在构建公式时了解这些行为非常重要。

配合筛选行的 SUBTOTAL()

当您对数据集应用筛选时，无论使用哪个函数编号，SUBTOTAL() 都会将被筛除的行排除在计算之外。此行为在所有函数编号（1-11 与 101-111）中都是自动且一致的。

例如，若您拥有 1 至 12 月的销售数据，并筛选仅显示一季度（1、2、3 月），SUBTOTAL(9, B2:B13) 将只对这三个月（可见项）求和，忽略被筛除的二至四季度数据。

配合手动隐藏行的 SUBTOTAL()

对于手动隐藏的行（右键行号并选择“隐藏”），处理方式取决于您选择的函数编号。

使用编号 1-11 时，计算会包含这些手动隐藏行；使用编号 101-111 时，则会将其排除。这样您可以自行控制隐藏数据是否影响结果。

SUBTOTAL() 与横向/纵向区域

SUBTOTAL() 最适合纵向数据区域（按列排列）。虽然它也能处理横向区域，但其隐藏逻辑针对基于行的操作进行了优化，因为 Excel 的筛选与隐藏功能是纵向工作的。

当您在同一区域内嵌套多个 SUBTOTAL() 公式时，函数会自动跳过其他 SUBTOTAL() 的结果以避免重复计数。这在创建层级汇总或分组小计时尤其实用，确保总计准确反映各自的独立分组且不重复。

SUBTOTAL() 的高级用法

使用 SUBTOTAL() 创建动态下拉汇总

您可以将 SUBTOTAL() 与 IF() 组合，创建随用户选择而变化的交互式汇总。该方法通过下拉列表让用户在不同计算类型间切换。

首先在 D1 单元格设置下拉选项，如 "Sum"、"Average"、"Count" 和 "Max"。

然后在另一个单元格（如 D2）使用以下公式，根据用户选择动态调整计算：

=IF(D1="Sum",SUBTOTAL(109,A2:A10),
  IF(D1="Average",SUBTOTAL(101,A2:A10),
  IF(D1="Count",SUBTOTAL(103,A2:A10),
  IF(D1="Max",SUBTOTAL(104,A2:A10),"Select Option"))))技术支持

该公式会判断下拉选择，并应用相应的 SUBTOTAL()。用户无需修改公式即可在不同计算类型间切换，使报告更具交互性且更易用。

Excel 表格与分级显示中的 SUBTOTAL()

Excel 表格在“总计行”功能中会自动使用 SUBTOTAL()。当您将数据区域转换为表格（Ctrl+T）并启用总计行时，Excel 在求和时默认插入 SUBTOTAL(109, …)。每当筛选行变化时，该总计行都会动态更新，只反映可见数据。

您可以通过单击总计行单元格中的下拉箭头并选择 Count、Average、Max、Min 等选项来更改计算类型。Excel 会在保持 SUBTOTAL() 结构的同时自动更新函数编号。

“分级显示”（数据 > 分类汇总）功能同样依赖 SUBTOTAL()。当您对数据进行分组并插入小计时，Excel 会在每个分组断点自动放置 SUBTOTAL()，从而创建层级汇总。在展开或折叠分级时，函数会正确忽略嵌套的小计结果。

常见陷阱与限制

了解 SUBTOTAL() 的常见错误与限制，有助于避免计算失误，并为您的数据分析选择合适的方法。

• 无效函数编号导致 #VALUE! 错误： 最常见的错误发生在使用无效的函数编号时。SUBTOTAL() 仅接受 1-11 和 101-111 这两组函数编号。使用 12、50 或 200 等数字会返回 #VALUE! 错误。务必确认您的编号对应受支持范围内的有效运算。

• 隐藏列与隐藏行的差异： SUBTOTAL() 对隐藏行的响应取决于函数编号，但对隐藏列并不相同。无论使用 1-11 或 101-111，函数都会将隐藏列中的数据计入计算。这一限制意味着，当列可见性会影响分析时，您需要采用替代方法。

• 不支持三维引用（3D 引用）： 与许多 Excel 函数不同，SUBTOTAL() 无法引用跨多工作表的区域。类似 SUBTOTAL(109, Sheet1:Sheet3!A1:A10) 的公式会报错。变通方法是先在各工作表上分别用 SUM() 等标准函数计算汇总，再在汇总表上用单个 SUBTOTAL 合并这些中间结果。

• 错误值会参与计算： SUBTOTAL() 不会忽略包含 #N/A、#DIV/0! 或 #VALUE! 等错误值的单元格。这些错误会在小计计算中传播，可能导致整体结果失效。在应用 SUBTOTAL() 前请清理错误，或考虑使用可跳过错误值的 AGGREGATE()。

• 纵向数据效果最佳： 虽然 SUBTOTAL() 也能处理横向区域，但其设计更适配纵向数据结构。Excel 的筛选、排序与隐藏按行工作，使纵向排列更契合该函数的预期行为。

Subtotal() 与 AGGREGATE()

AGGREGATE() 是 SUBTOTAL() 的增强替代，具备更多统计运算并能更好地处理错误。SUBTOTAL() 提供 11 种基础运算，而 AGGREGATE() 提供 19 种函数，包括百分位数、四分位数和中位数等计算。

AGGREGATE() 的主要优势在于其错误处理能力。与会将错误值计入计算的 SUBTOTAL() 不同，AGGREGATE() 可自动跳过包含 #N/A、#DIV/0! 或 #VALUE! 等错误的单元格。这使其在处理不完整或存在问题的数据集时尤为实用。

AGGREGATE() 还提供更精细的忽略控制。您可以通过其选项参数配置是否跳过隐藏行、嵌套小计、错误值，或这些元素的任意组合。

使用 SUBTOTAL() 的专业提示

先排序，再分类汇总

使用“数据 > 分类汇总”前，请先按分组列对数据排序。这样可将相关记录聚拢，形成干净的分组断点。未排序的数据会产生分散的小计，难以形成有意义的汇总。

在各列首行使用标签

在应用小计前，请为数据区域的首行设置清晰的表头。Excel 会使用这些标签生成更有意义的小计描述，也便于解读报告。清晰的列标题还能帮助您选择用于手动 SUBTOTAL() 公式的区域。

注意汇总位置（上方或下方）

Excel 的分类汇总工具默认将汇总行放在每个分组下方，您也可以选择放在上方。请根据报告用途进行选择：在上方的汇总适合管理层仪表板，而在下方的汇总更符合传统会计格式。

区分隐藏与筛选以精确控制

了解筛选与手动隐藏的区别，才能获得预期结果。若希望 SUBTOTAL() 临时忽略某些数据，请使用筛选；若希望更为持久地排除，请使用手动隐藏（右键 > 隐藏），并选择 101-111 的函数编号以遵循隐藏状态。

这种方法提供分层控制：用筛选获得临时视图，用手动隐藏实现半永久排除，再通过选择合适的函数编号来体现您的隐藏意图。

结语

熟练掌握 SUBTOTAL() 能显著改变您在 Excel 中进行数据分析的方式。相比于在数据变化时容易失效的静态计算，您可以构建能随用户操作和数据修改自动适应的稳健公式。在处理大型数据集或制作会被多人长期筛选与操作的报告时，这项能力尤为重要。

若您希望在 SUBTOTAL() 等单一函数之外，系统提升 Excel 能力，我们提供的 Excel Fundamentals 技能路径为期 16 小时，按部就班地带您从数据准备一路进阶到高级分析与可视化。若您希望立即上手分析应用，我们的 Data Analysis in Excel 课程将教授数据透视表与高级逻辑函数，助您挖掘更深层洞见。