1.2.4 SUBTOTAL()函数

该函数主要是在数据库分类汇总后，再进行相关的汇总操作。

该函数的参数Function_num为1～11（包含隐藏值）或101～111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。意思是用1～11、101～111这22个数字，分别代表11个函数，对分类筛选后的数据进行处理。

（1）包含隐藏值。

数据库有一部分数据行可能被隐藏了，利用该函数可进行求和、平均值、最大值、最小值等操作时，连同被隐藏的值一起计算。

（2）忽略隐藏值。

数据库隐藏或筛选后，该函数将忽略隐藏的数据行，只对显示的数据行进行诸如求和、平均值、最大值、最小值的操作。

请读者打开“配套光盘\第1章\xls\数学函数.xls”文件，单击进入“SUBTOTAL”工作表，本工作表中有关于SUBTOTAL()函数的详细应用实例，结合本节内容一起操作一遍。

SUBTOTAL()函数的功能与表达式如图1.44所示。

图1.44 SUBTOTAL ()函数的功能与表达式

示例一

忽略隐藏行统计。

如图1.45所示，两列数据“品名”和“数量”。现在要求根据等级统计出数量和总金额。

图1.45 SUBTOTAL筛选求和

SUBTOTAL()函数的参数9、109相当于SUM()函数进行求和时，是“包含隐藏值”还是“忽略隐藏值”。

在C43单元格中输入公式：

      =SUBTOTAL(109,C36:C41)

没有筛选前等于150。

在D43单元格中输入公式：

      =SUBTOTAL(109,D36:D41)

没有筛选前等于5580。

在C44单元格中输入公式：

      =SUBTOTAL(9,C36:C41)

没有筛选前等于150。

在D44单元格中输入公式：

      =SUBTOTAL(9,D36:D41)

没有筛选前等于5580。

结果都是一样的。参数109（忽略隐藏行）和9（包含隐藏行）没起到什么作用。

图1.46 SUBTOTAL隐藏求和

对等级进行筛选，发现结果与未筛选前是一样的。看来筛选后参数“9”对隐藏的行没起到什么作用。

用鼠标右键单击等级为2的37行、40行的行号，执行隐藏操作，可看到C43、D43、C44、D44的公式不变，但参数为“9”、“109”的公式结果不同了。参数为“109”的公式，只对显示的数据行进行计算，忽略了隐藏行的数据，如图1.46所示。

示例二

动态系列编号。

如图1.47所示，F50:H57区域是对B50:D57区域根据“总分”进行排序后的结果。在F列序号呈混乱状态。

在B61单元格输入公式：

      =SUBTOTAL(3,C$61:C61)

并下拉到B67单元格。参数3表示的是统计非空单元格个数，相当于执行统计函数COUNTA()（后面一节将讲解该函数）。按照“总分”排序后，发现B61:B67单元格中的序号自动重新排序。如图1.48所示。

图1.47 排序后序号混乱

图1.48 排序后序号自动重新排序