这个函数的功能=学会20个函数（非常实用）！_【EXCEL】

SUBTOTAL函数的作用对数据进行汇总处理，计算数据的平均值、最大值、最小值、合计数、方差、标准差、乘积、计数。...

1、Subtotal函数如何代替一系列函数

2、青出于蓝而胜于蓝，Subtotal函数的奇特功能（非常实用）

小窍门：将屏幕横放或点击图片可放大图片！▎1 Subtotal函数代替一系列函数

SUBTOTAL函数的作用对数据进行汇总处理，计算数据的平均值、最大值、最小值、合计数、方差、标准差、乘积、计数。

SUBTOTAL函数的语法结构为：

SUBTOTAL(汇总处理方式，数据1，数据2。。。)

SUBTOTAL函数对参数2之后的所有参数进行汇总处理，参数之间由逗号隔开，2010版Excel的SUBTOTAL函数的参数数量最多为255个，汇总处理方式如下表1：

表1 SUBTOTAL函数第1参数汇总处理方式

汇总处理方式代码

汇总处理方式代码

可代替函数

函数说明

（包含隐藏值）

（忽略隐藏值）

1

101

AVERAGE

平均值

2

102

COUNT

对数字单元格计数

3

103

COUNTA

对非空单元格计数

4

104

MAX

最大值

5

105

MIN

最小值

6

106

PRODUCT

乘积

7

107

STDEV

样本标准差

8

108

STDEVP

总体标准差

9

109

SUM

求和

10

110

VAR

样本方差

11

111

VARP

总体方差

（1）SUBTOTAL代替求平均值函数AVERAGE

“平均分”单元格B10公式：

=SUBTOTAL(1,B2:B8)

SUBTOTAL函数的第1参数为1，相当于对单元格区域“B2:B8”进行包含隐藏值的求平均值运算，替代了AVERAGE函数。如下图1所示：

图1 Subtotal函数代替AVERAGE

关于AVERAGE函数的详细介绍点击下方蓝字：

↓↓↓

AVERAGE函数详解丨返回算术平均值

（2）SUBTOTAL代替求和函数SUM

“总分”单元格F2公式：

=SUBTOTAL(9,B2:D2)

SUBTOTAL函数的第1参数为9，相当于对单元格区域“B2:D2”进行包含隐藏值的求和运算，替代了SUM函数。如下图2所示：

图2 Subtotal函数代替SUM

关于SUM函数的详细介绍点击下方蓝字：

↓↓↓

99%的人不知道，Sum函数可以这样用

（3）SUBTOTAL代替求大值函数MAX

“各科最高分”单元格B10公式：

=SUBTOTAL(4,B2:B8)

SUBTOTAL函数的第1参数为4，相当于对单元格区域“B2:B8”进行包含隐藏值的求最大值运算，替代了MAX函数。如下图3所示：

图3 Subtotal函数代替MAX

（4）SUBTOTAL代替求最小值函数MIN

“各科最低分”单元格B10公式：

=SUBTOTAL(5,B2:B9)

SUBTOTAL函数的第1参数为5，相当于对单元格区域“B2:B9”进行包含隐藏值的求最小值运算，替代了MIN函数。如下图4所示：

图4 Subtotal函数代替MIN

（5）SUBTOTAL代替计数函数COUNT、COUNTA

“人数”单元格B10公式：

=SUBTOTAL(3,B2:B8)

SUBTOTAL函数的第1参数为3，相当于对单元格区域“B2:B8”进行包含隐藏值的非空单元格计数运算，替代了COUNTA函数。如下图5所示：

图5 Subtotal函数代替COUNTA

“人数”单元格C10公式：

=SUBTOTAL(2,B2:B8)

SUBTOTAL函数的第1参数为2，相当于对单元格区域“C2:C8”进行包含隐藏值的数字单元格计数运算，替代了COUNT函数。如下图6所示：

图6 Subtotal函数代替COUNT

关于COUNT、COUNTA函数的详细介绍点击下方蓝字：

↓↓↓

COUNTA函数详解丨计算非空单元格的个数

其他函数不一一举例，有兴趣的同学可以根据“

表

1 SUBTOTAL

函数第

参数汇总处理方式

”中的参数进行尝试。

▎2 青出于蓝而胜于蓝，Subtotal函数的奇特功能（超实用）（1）SUBTOTAL函数可以忽略隐藏值，不对隐藏值进行运算

虽然SUBTOTAL可代替很多函数，但SUBTOTAL函数有代替函数所没有的特性，即SUBTOTAL函数可以忽略隐藏值，不对隐藏值进行运算，这是SUBTOTAL函数与代替函数最大的区别。

下面举例说明。

体重数据如下图7所示。

“总重”单元格B10公式：

=SUBTOTAL(109,B2:B8)

SUBTOTAL函数的第1参数为109，相当于对单元格区域“B2:B8”进行忽略隐藏值的求和运算，替代了SUM函数，但拥有SUM函数没有的特性，即求和时忽略隐藏值。

SUBTOTAL函数求和运算结果为：830。下面测试隐藏数据后的计算结果。

图7 体重数据

隐藏表格中第6行数据：单元格A6：章鱼，单元格B6：80，如下图8所示，

SUBTOTAL函数求和运算时忽略了单元格B6的数值，运算结果变为：750。

图8 忽略隐藏值求和

而同样隐藏第6行，将“总重”单元格B10公式的SUBTOTAL函数的第1参数改成“9”：

=SUBTOTAL(9,B2:B8)

SUBTOTAL函数的第1参数为9，相当于对单元格区域“B2:B8”进行包含隐藏值的求和运算，完全替代了SUM函数。

如下图9所示，表格中隐藏了第6行数据，SUBTOTAL函数求和运算时包含了单元格B6的数值，运算结果为：830。

图9 包含隐藏值求和

（2）SUBTOTAL函数另一个特性：在数据筛选时，SUBTOTAL只对筛选出的数据进行计算，忽略没有被筛选的数据。下面举例说明。

销售明细表如下图10所示。

“总销售额”单元格B10公式：

=SUBTOTAL(9,B2:B8)

SUBTOTAL函数的第1参数为9，相当于对单元格区域“B2:B8”进行包含隐藏值的求和运算，替代了SUM函数，但拥有SUM函数没有的特性，即求和时只对筛选出的数据进行计算，而忽略没有被筛选的数值。

没有筛选时，SUBTOTAL函数求和运算结果为：1040。

图10 没有筛选数据时求和

下面测试筛选数据之后的Subtotal函数的计算结果。

筛选出“战斗机贴膜”的销售额，总销售的计算结果为：战斗机贴膜的销售总数：260，如下方动图11。

而如果保持筛选状态，将“总销售额”单元格B10公式改为：

=SUM(B2:B8)

SUM函数的计算结果为：1040。

图11 对筛选数据进行求和

两个计算结果表明：Subtotal函数只对筛选出的数据进行计算，忽略没有被筛选的数值出，而代替函数SUM函数则不具备这个特性。

（3）SUBTOTAL函数另一个特性：运算时忽略其他SUBTOTAL函数。下面举例说明。

阿波、大贺、阿亮的体重“小计”单元格B5公式：

=SUBTOTAL(109,B2:B4)

计算结果为单元格B2至B4的合计数：350。

老庄、章鱼、明峰、阿彬的体重“小计”单元格B11公式：

=SUBTOTAL(109,B7:B10)

计算结果为单元格B7至B10的合计数：480。

“总重”单元格B13公式：

=SUBTOTAL(109,B2:B11)

计算结果为：830。

计算结果表明，SUBTOTAL求和运算时，并没有对单元格B5的值350，以及单元格B11的值480进行求和，忽略了其他SUBTOTAL函数的运算结果。而代替函数SUM函数则不具备这个特性。