圆的弧长计算公式SUBTOTAL函数及其妙用

2020年9月24日发(作者：景翩翩)
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函数，其它类推。< br>由此可见，SUBTOTAL函数是一个功能强大、作用广泛的分类汇总函数。
二、怎样理解“隐藏的值”
怎样理解SUBTOTAL函数中“隐藏的值”？下面将以表二的数据为例。

1
表二
1、第一个参数为1或101
在A8、A9、A10单元格分别输入“=SU BTOTAL(1,A1:A7)”、
“=SUBTOTAL(101,A1:A7)”、“=AVER AGE(A1:A7)”，并且公式向右填充。
在G1、H1、I1、单元格分别输入“=SUBTO TAL(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函数替换为COU NT函数（表五）。

表五
隐藏第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

表八
能不能在打印班级成绩册时，前面的序号显示学生在班级的名次？
2、SUBTOTAL函数的妙用
SUBTOTAL函数的第一个参数为3或103时，相当 于COUNTA函数，可以计
算非空单元格的个数。
在表七的A1单元格输入“=SUBTO TAL(103,B$$2:B2)”，公式向下复制，A
列的序号与原来完全一样。在筛选状态下看各班 成绩表，奇妙的结果产生了（表
九是筛选状态下1班的成绩表），A列序号为1、2、3……，该序号为 班级名次。
前面提出的问题得以解决。
表九
说明：表九A列的公式中，将第一个参 数替换为3，结果完全一样，从而证
明前述的结论：在筛选状态下，不可见的行不属于隐藏的行。
3、SUBTOTAL函数的妙用扩展
4

当第一个参数为1或101时 ，SUBTOTAL函数相当于AVERAGE函数。下面
是对SUBTOTAL函数的又一个应用：在 筛选状态下，把年级平均成绩与班级平
均成绩进行比较。
在表九中，先取消筛选（显示全部名 单），在标题行前插入二行，在D1、
D2单元格分别输入“=AVERAGE(D4:D800)”、 “ =SUBTOTAL(101,D4:D800)”
（第一个参数可以是1），公式向右复制到M列 。D1与D2、E1与E2…….M1
与M2的值一样，因为它们分别计算的是整个年级的对应学科的平 均成绩，不同
的是第一行用的是AVERAGE函数，第二行用的是SUBTOTAL函数。进入筛选< br>状态看某班的成绩，奇妙产生了，第一行是整个年级的对应学科的平均成绩，第
二行是班级的对应 学科的平均成绩（表十）。
表十
学习EXCEL，留心、细心、潜心，一定会有新的发现。以上可能会有谬误
之处，请指正！



5