Excel 应用SUBTOTAL函数计算列表或数据库中的分类汇总

SUBTOTAL函数的功能是计算列表或数据库中的分类汇总。通常可以使用“数据”选项卡“大纲”组中的“分类汇总”命令来创建带有分类汇总的列表。一旦创建了分类汇总,就可以通过编辑SUBTOTAL函数对该列表进行修改。SUBTOTAL函数的语法如下:


SUBTOTAL(function_num,ref1,ref2,...)

参数function_num为1~11(包含隐藏值)或101~111(忽略隐藏值)之间的数字,指定使用何种函数在列表中进行分类汇总计算。参数ref1、ref2为要进行分类汇总计算的1~254个区域或引用。如果在ref1、ref2等中有其他的分类汇总(嵌套分类汇总),则会忽略这些嵌套分类汇总以避免重复计算。

【背景知识】当参数function_num为从1~11的常数时,SUBTOTAL函数将包括通过“隐藏行”命令所隐藏的行中的值,当要对列表中的隐藏和非隐藏数字进行分类汇总时,须使用这些常数。当参数function_num为从101~111的常数时,SUBTOTAL函数将忽略通过“隐藏行”命令所隐藏的行中的值。当只对列表中的非隐藏数字进行分类汇总时,则使用这些常数。

SUBTOTAL函数的参数数值和对应的函数如表13-2所示。

表13-2 SUBTOTAL函数的参数数值和对应的函数

【典型案例】已知某班级的期末考试成绩,现在来计算学生的总分和平均分。本例的原始数据如图13-74所示。

图13-74 原始数据

步骤1:在E2单元格中输入公式“=SUBTOTAL(9,B2:D2)”,计算第一位同学的总分,然后使用自动填充功能来计算其他同学的总分,如图13-75所示。

步骤2:在F2单元格中输入公式“=SUBTOTAL(1,B2:D2)”,计算第一位同学的平均分,然后使用自动填充功能来计算其他同学的平均分,如图13-76所示。

【使用指南】上面例子中介绍了SUBTOTAL函数中相当于SUM、AVERAGE函数功能部分的运算。当在实际运算中应用到其他参数时,原理是相同的,视具体情况作相应的参数调整即可。SUBTOTAL函数忽略任何不包括在筛选结果中的行,不论使用什么参数function_num值。

图13-75 计算总分

图13-76 计算平均分

SUBTOTAL函数适用于数据列或垂直区域。不适用于数据行或水平区域。当参数function_num大于或等于101时需要分类汇总某个水平区域时,如SUBTOTAL(109,B2:G2),则隐藏某一列不影响分类汇总。但是隐藏分类汇总的垂直区域中的某一行就会对其产生影响。如果所指定的某一引用为三维引用,函数SUBTOTAL将返回错误值“#VALUE!”。