Excel SUBTOTAL 函数：跳过小计和筛选行的合计

=SUBTOTAL(9,C2:C8) 像 SUM 一样对 C2:C8 中的数字求和，但有两点不同：它会跳过区域中的其他 SUBTOTAL 公式，也会跳过被筛选隐藏的行。第一个参数 9 指定做哪种计算。

公式重置

C8fx

=SUBTOTAL(9,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Check	Result
2	North	Apple	120		SUM of C2:C7	890
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South total		245
8	Grand total		445

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

C8 中的总计覆盖了整列，包括小计行，但仍然显示 445：SUBTOTAL 排除了 C4 和 C7，因为它们是 SUBTOTAL 公式。F2 用 SUM 做同样的事，结果是 890，每笔销售额都算了两遍。每个合计行都用 SUBTOTAL，你就可以添加或移动分组，而不必重写总计公式。

SUBTOTAL 的函数编号

=SUBTOTAL(function_num, ref1, [ref2], ...)

输入 =SUBTOTAL( 时，Excel 会显示这张列表，所以不用死记。最常用的是 9 和 109（SUM）、1（AVERAGE）以及 103（统计可见行数）。

公式重置

F2fx

=SUBTOTAL(1,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Calculation	Result
2	North	Apple	120		AVERAGE (1)	88.33
3	North	Pear	80		COUNTA (3)	6
4	South	Apple	200		MAX (4)	200
5	South	Pear	45		MIN (5)	30
6	East	Apple	55		Visible rows (103)	6
7	East	Plum	30		SUM (109)	530

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

这里没有隐藏任何行，所以每一行都等于对应的普通函数：平均值 88.33、6 行、最大值 200、最小值 30、总和 530。只有在隐藏行时才会看出区别，这正是下一节要讲的。

SUBTOTAL 9 和 109，以及筛选掉的行

用 数据 > 筛选（Ctrl+Shift+L，Mac 上按 Cmd+Shift+F）开启筛选，然后在 Region 下拉列表中选择 North。其他地区的行会被隐藏：

• =SUM(C2:C7) 仍然把六行全部加起来。
• =SUBTOTAL(9,C2:C7) 和 =SUBTOTAL(109,C2:C7) 只加可见的 North 行。
• =SUBTOTAL(103,A2:A7) 统计屏幕上剩下的行数：2，与状态栏中“在 6 条记录中找到 2 个”的数字相同。

这两组编号只在你手动隐藏行时有区别（选中行，右键单击 > 隐藏）。9 仍会把这些行加进去，109 则不会。如果合计应该始终和屏幕上显示的一致，就用 109。如果你隐藏行只是为了让视图更整洁，仍希望把它们算进合计，就用 9。

SUBTOTAL 只对行起作用。隐藏的 列 总是会被计入，所以在一行中横向使用 =SUBTOTAL(109,B2:G2) 也会加上隐藏的列。

得到 SUBTOTAL 最快的方法是在开启筛选时点击“自动求和”按钮：Excel 会写入 =SUBTOTAL(9,...) 而不是 SUM。数据 > 分类汇总 更进一步：在按某列排好序的列表中，它会在每组下方插入一个合计行，再加一个总计，全部用 SUBTOTAL，并提供折叠分组的分级显示按钮。

AGGREGATE：能跳过错误的 SUBTOTAL

区域中只要有一个单元格是错误值，SUM 和 SUBTOTAL 就会返回这个错误。AGGREGATE（Excel 2010 及更高版本）相当于多了一个选项参数的 SUBTOTAL；选项 6 会忽略错误值。

公式重置

F3fx

=AGGREGATE(9,6,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Formula	Result
2	North	Apple	120		SUBTOTAL	#N/A
3	North	Pear	#N/A		AGGREGATE, ignore errors	450
4	South	Apple	200		AGGREGATE, MAX	200
5	South	Pear	45
6	East	Apple	55
7	East	Plum	30

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

C3 中是 #N/A，所以 F2 也显示 #N/A。F3 忽略了它，把其他五个值加起来：450。它的第一个参数和 SUBTOTAL 用的是同一套编号（9 表示 SUM，4 表示 MAX）。其他选项：5 忽略隐藏行，7 忽略隐藏行和错误值，3 忽略隐藏行、错误值以及嵌套的 SUBTOTAL 和 AGGREGATE 公式。把 C3 换成一个数字，F2 就会显示和 F3 相同的合计。

练习：对含小计的列表求总计

公式重置

C9fx

	A	B	C
1	Region	Item	Sales
2	North	Apple	120
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South	Plum	60
8	South total		305
9	Grand total

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

轮到你了： 列表中每个地区下方都有一个小计。在 C9 中写一个覆盖 C2:C8 的总计，且不重复计算小计行。

检查显示答案

为什么用了 SUBTOTAL，合计还是不对

• 分组合计用的是 SUM。 SUBTOTAL 只跳过区域中的其他 SUBTOTAL 公式，不跳过 SUM 公式。写成 =SUM(C2:C3) 的分组合计会被再算一次。把每个合计行都改成 SUBTOTAL。
• 行是手动隐藏的，而函数编号是 9。 改用 109。
• 数据按列排列，而不是按行。 隐藏的列永远不会被跳过。
• 你需要的是条件，而不是筛选。 SUBTOTAL 跟随筛选隐藏的内容。不筛选就想合计 North，请用 SUMIF。要一次汇总每个分组，数据透视表 不需要在数据中插入合计行就能做到。