Excel SUBTOTAL 函数
最后修改于 2025 年 4 月 4 日
SUBTOTAL 函数是一个多功能的 Excel 函数，可执行各种计算，同时忽略隐藏的行。它可以计算总和、平均值、计数等。本教程提供了使用 SUBTOTAL 函数的全面指南，并附有详细示例。您将学习基本语法、实际应用和高级技术，以掌握此必不可少的 Excel 函数。
SUBTOTAL 函数基础
SUBTOTAL 函数可以对数据范围执行计算，同时可以选择忽略隐藏的行。它通过其 function_num 参数提供 11 种不同的运算。该语法灵活且功能强大。
| 组成部分 | 描述 |
|---|---|
| 函数名称 | SUBTOTAL |
| 语法 | =SUBTOTAL(function_num, ref1, [ref2], ...) |
| 参数 | function_num (1-11 或 101-111)，ref1 (必需)，ref2 (可选) |
| 返回值 | 指定范围计算的结果 |
此表分解了 SUBTOTAL 函数的关键组成部分。它显示了函数名称、基本语法格式、参数详细信息和返回值特征。
基本 SUBTOTAL 示例 (求和)
此示例演示了 SUBTOTAL 最简单的用法，用于对值范围求和。
| A | B |
|---|---|
| 10 | |
| 20 | |
| 30 | |
| =SUBTOTAL(9, A1:A3) |
下表显示了一个简单的电子表格，其中 A 列包含值，B4 单元格中的 SUBTOTAL 公式对 A1 到 A3 的值求和。
=SUBTOTAL(9, A1:A3)
此公式使用 function_num 9 (SUM) 对单元格 A1 到 A3 的值求和。结果将是 60 (10+20+30)。这显示了 SUBTOTAL 的基本求和功能。
带隐藏行的 SUBTOTAL
使用 function numbers 101-111 时，SUBTOTAL 可以忽略隐藏行中的值。此示例显示了此行为。
| A | B |
|---|---|
| 10 | |
| 20 | (隐藏行) |
| 30 | |
| =SUBTOTAL(109, A1:A3) |
下表演示了 SUBTOTAL 忽略计算中隐藏行的能力。第 2 行被隐藏，公式使用 109 (忽略隐藏行的 SUM)。
=SUBTOTAL(109, A1:A3)
此公式对 A1 (10) 和 A3 (30) 求和，忽略隐藏的 A2 (20)。结果是 40。这展示了 SUBTOTAL 处理过滤数据的独特能力。
SUBTOTAL 用于计算平均值
SUBTOTAL 可以使用 function_num 1 或 101 计算平均值。此示例显示了在忽略隐藏行的情况下计算平均值。
| A | B |
|---|---|
| 10 | |
| 20 | (隐藏行) |
| 30 | |
| =SUBTOTAL(101, A1:A3) |
下表显示了 SUBTOTAL 如何在排除隐藏行的情况下计算平均值。第 2 行被隐藏，公式使用 101 (忽略隐藏行的 AVERAGE)。
=SUBTOTAL(101, A1:A3)
此公式对 A1 (10) 和 A3 (30) 取平均值，忽略隐藏的 A2 (20)。结果是 20。这显示了 SUBTOTAL 执行不同计算的能力。
SUBTOTAL 用于计数操作
SUBTOTAL 可以计算包含数字的单元格 (function_num 2 或 102) 或非空单元格 (function_num 3 或 103)。此示例演示了计数。
| A | B |
|---|---|
| 10 | |
| 文本 | (隐藏行) |
| 30 | |
| =SUBTOTAL(102, A1:A3) | |
| =SUBTOTAL(103, A1:A3) |
下表显示了两个计数操作：计数数字和计数非空单元格，两者都忽略隐藏行。第 2 行被隐藏并包含文本。
=SUBTOTAL(102, A1:A3) // Count numbers, ignoring hidden =SUBTOTAL(103, A1:A3) // Count non-empty, ignoring hidden
第一个公式计算数字单元格 (A1 和 A3)，忽略隐藏的 A2。结果是 2。第二个公式计算所有非空单元格 (A1-A3)，但仍忽略隐藏的 A2。结果是 2 (A1 和 A3)。
带多个范围的 SUBTOTAL
SUBTOTAL 可以像其他 Excel 函数一样处理多个范围。此示例显示了跨不连续范围的求和。
| A | B | C |
|---|---|---|
| 5 | 10 | |
| 15 | 20 | |
| =SUBTOTAL(9, A1:A2, B1:B2) |
下表演示了 SUBTOTAL 在一次计算中合并不同范围的能力。该公式对列 A 和列 B 的范围中的值进行求和。
=SUBTOTAL(9, A1:A2, B1:B2)
此公式对范围 A1:A2 (5+15) 和 B1:B2 (10+20) 中的所有值进行求和。结果是 50。这显示了 SUBTOTAL 在多个输入范围方面的灵活性。
带过滤数据的 SUBTOTAL
SUBTOTAL 在过滤数据时特别有用，因为它在使用 101-111 function numbers 时会自动忽略隐藏 (已过滤掉) 的行。
| A | B | C |
|---|---|---|
| 100 | 北方 | |
| 200 | 南方 | (已过滤) |
| 300 | 北方 | |
| =SUBTOTAL(109, A1:A3) |
下表显示了仅显示“北方”地区销售数据的过滤数据。使用 109 的 SUBTOTAL 公式仅对可见 (未过滤) 的行进行求和。
=SUBTOTAL(109, A1:A3)
此公式仅对 A1:A3 (100 和 300) 中的可见单元格求和，忽略已过滤的 A2 (200)。结果是 400。这种自动处理使得 SUBTOTAL 非常适合过滤后的报告。
嵌套在其他计算中的 SUBTOTAL
SUBTOTAL 的结果可以用于其他计算。此示例显示了计算小计的百分比。
| A | B | C |
|---|---|---|
| 100 | 北方 | |
| 200 | 南方 | (已过滤) |
| 300 | 北方 | |
| =A1/SUBTOTAL(109, A1:A3) |
下表演示了在较大的计算中使用 SUBTOTAL。该公式计算第一行占过滤后总计的百分比。
=A1/SUBTOTAL(109, A1:A3)
此公式将 A1 (100) 除以 A1:A3 (400) 的过滤后总和。结果是 0.25 或 25%。这表明 SUBTOTAL 可以成为更复杂公式的一部分。
SUBTOTAL 函数编号参考
SUBTOTAL 函数通过其 function_num 参数提供 22 种不同的运算。此表列出了所有可用的选项。
| Function_num | 操作 | 包括隐藏 | 排除隐藏 |
|---|---|---|---|
| 1 | AVERAGE | 101 | AVERAGE (忽略隐藏) |
| 2 | COUNT | 102 | COUNT (忽略隐藏) |
| 3 | COUNTA | 103 | COUNTA (忽略隐藏) |
| 4 | MAX | 104 | MAX (忽略隐藏) |
| 5 | MIN | 105 | MIN (忽略隐藏) |
| 6 | PRODUCT | 106 | PRODUCT (忽略隐藏) |
| 7 | STDEV | 107 | STDEV (忽略隐藏) |
| 8 | STDEVP | 108 | STDEVP (忽略隐藏) |
| 9 | SUM | 109 | SUM (忽略隐藏) |
| 10 | VAR | 110 | VAR (忽略隐藏) |
| 11 | VARP | 111 | VARP (忽略隐藏) |
此参考表显示了 SUBTOTAL 的所有 22 个 function_num 选项。数字 1-11 包括隐藏值，而 101-111 在计算中排除它们。
常见的 SUBTOTAL 用途
以下是 SUBTOTAL 发挥作用的一些实际场景
- 动态报告：创建摘要计算，在过滤数据时自动调整
- 数据分析：分析可见数据，同时忽略已过滤掉的值
- 仪表板摘要：构建响应过滤的灵活仪表板指标
- 子报告：计算大数据集中过滤部分的汇总
技巧和最佳实践
要充分利用 SUBTOTAL，请考虑以下技巧
- 处理筛选器或隐藏行时，请使用 101-111 function numbers
- 避免将 SUBTOTAL 嵌套在另一个 SUBTOTAL 中 (这可能导致错误)
- 与自动筛选器结合使用以创建交互式报告
- 与表格结合使用以进行结构化引用
- 使用隐藏和可见数据测试公式以验证结果
局限性
虽然功能强大，但 SUBTOTAL 也有一些限制需要注意
- 仅使用 101-111 function numbers 忽略手动隐藏的行
- 不能直接处理跨多个工作表的 3D 引用
- 无法直接处理数组常量
- 如果范围包含其他 SUBTOTAL 公式，则可能会返回错误
SUBTOTAL 函数是 Excel 中动态报告和过滤数据分析的关键工具。它忽略隐藏行的能力使其在许多任务上优于 SUM 或 AVERAGE 等常规函数。通过掌握 SUBTOTAL，您可以创建灵活、强大的电子表格，在过滤数据或隐藏行时自动更新计算。无论您是构建仪表板、分析数据集还是创建交互式报告，SUBTOTAL 都提供了专业 Excel 工作所需的灵活性。
作者
列出 所有 Excel 公式。