2024年6月15日发(作者：)

SUBTOTAL函数及其妙用

关键词：SUBTOTAL 及其妙用

最初使用EXCEL，从技巧到公式，每学习一点并能够在实际中发挥作用，

总会有那么一点点的兴奋。几年下来，似乎再难有大的进步，兴奋的感觉也久违

了。非常偶然的机会，发现了SUBTOTAL函数，久违了的兴奋回来了！

一、SUBTOTAL函数

SUBTOTAL函数表达式为SUBTOTAL(function_num, ref1, ref2, ...)，有二个

参数。

第二个参数ref1、ref2, ...是要进行分类汇总的区域，可以是一个或多个区域，

最多可以有254个区域。

第一个参数function_num是1—11或101—111之间的数字，分别代表11种

函数（表一）。

第一个参数

包含隐藏的值

1

2

3

4

5

6

7

8

9

10

11

不包含隐藏的值

101

102

103

104

105

106

107

108

109

110

111

AVERAGE

COUNT

COUNTA

MAX

MIN

PRODUCT

STDEV

STDEVP

SUM

VAR

VARP

相当于函数

平均值

计数

计数

最大值

最小值

乘积

标准差

标准差

和、

方差

方差

表一

当第一个参数为1或101时，SUBTOTAL函数相当于AVERAGE函数；当第

一个参数为1，隐藏的值参与计算；当第一个参数为101，隐藏的值不参与计算。

第一个参数为2或102时，SUBTOTAL函数相当于COUNT函数，其它类推。

由此可见，SUBTOTAL函数是一个功能强大、作用广泛的分类汇总函数。

二、怎样理解“隐藏的值”

怎样理解SUBTOTAL函数中“隐藏的值”？下面将以表二的数据为例。

1

表二

1、第一个参数为1或101

在A8、A9、A10单元格分别输入“=SUBTOTAL(1,A1:A7)”、

“=SUBTOTAL(101,A1:A7)”、“=AVERAGE(A1:A7)”，并且公式向右填充。

在G1、H1、I1、单元格分别输入“=SUBTOTAL(1,A1:F1)”、

“=SUBTOTAL(101,A1:F1)”、“=AVERAGE(A1:F1)”，并且公式向下填充。

A8、A9、A10三个单元格的公式的计算结果完全一样，G1、H1、I1三个单

元格的公式的计算结果也完全一样（表三）。

表三

隐藏第6行，隐藏E列，A8、A10单元格的计算结果与表三一样，G1、H1、

I1单元格的计算结果也与表三一样，只有A9、B9、C9、D9、F9单元格（第9

行）的计算结果发生了变化（表四）。

表四

2、第一个参数为2或102

为验证以上结果，在表三中，将公式中的第一个参数1或101替换为2或

102，将AVERAGE函数替换为COUNT函数（表五）。

表五

隐藏第6行，隐藏E列，计算结果也只有A9、B9、C9、D9、F9单元格（第

2

9行）发生变化（表六）。

表六

3、怎样理解“隐藏的值”

为保证验证结果的准确性，笔者按照以上方法，改变第一个参数的值，对其

它九个“相当于函数”进行验证，计算结果仍然只有A9单元格发生变化。

比较表三、表四或比较表五、表六：如果隐藏行，第一个参数为1—11时结

果不发生变化，第一个参数为101—111时结果发生变化；如果隐藏列，第一个

参数为1—11或101—111时，结果均不发生变化。

于是，得出结论：“隐藏的值”指隐藏的行的值（经过验证，在筛选状态下，

不可见的行也属于隐藏的行；筛选状态下有多少行，并且都在第二个参数的区域

范围内，SUBTOTAL函数就计算多少行）。

三、SUBTOTAL函数妙用

1、问题提出

表七是一张考试成绩表，成绩表包含14个班级共722名学生的成绩，按总

分降序排列。其中A列与N列数值完全一样。

表七

在筛选状态下打印各班成绩时，A列和N列显示的数值都是学生在年级的

序号或年级名次，不能显示学生在班级的序号或名次（表八以1班为例）。

3