SUBTOTAL函数的使用方法:
功能说明:将数据或数据库中数据分类汇总。
语法表达式:SUBTOTAL(function_num, ref1,ref2,……)
参数说明:
- ref1,ref2,……:表示需要进行分类汇总的单元格区域。使用函数时,该参数最多可设255个。
- function_num:一个数值,表示分类汇总时使用哪个函数。使用函数时,参数的取值范围是1~11之间的整数,各数值表示的分类汇总如表1所示。
表11 SUBTOTAL函数的参数数值和对应的函数
使用说明:在ref1,ref2,……中只能汇总一次,如果再进一步分类,则函数按最外边一层的分类进行汇总。
函数在进行分类汇总计算时,只对可见的数据进行分类汇总,那些隐藏行或列的数据将被忽略,因此在进行分类汇总时一定要保证数据处于可见状态。
函数只对二维数据进行分类汇总,如果参数值是三维数据,则返回错误值#VALUE!。
SUBTOTAL函数实例:
实际应用:某次考试中,某班统计了各位同学三个科目的成绩,现在需要统计每位同学总成绩、最高单科和平均成绩,其基础数据如图1所示。
图1 基础成绩数据
根据上面的基础成绩数据,具体的计算步骤如下:
(1)统计每位同学的总成绩。选择单元格区域B11~B16,在标题栏中输入表达式”=SUBTOTAL(9,OFFSET($B$1,ROW($A$2:$A$7)-ROW($A$1),1,3))”,然后同时按下”Ctrl+Shift+Enter”组合键,得到的结果如图2所示。
图2 统计每位同学的总成绩
(2)统计每位同学的最高单科。选择单元格区域C11~C16,在标题栏中输入表达式”=SUBTOTAL(4,OFFSET($B$1,ROW($A$2:$A$7)-ROW($A$1),1,3))”,然后同时按下”Ctrl+Shift+Enter”组合键,得到的结果如图3所示。
图3 统计每位同学的最高单科
(3)统计每位同学的平均成绩。选择单元格区域D11~D16,在标题栏中输入表达式”=SUBTOTAL(1,OFFSET($B$1,ROW($A$2:$A$7)-ROW($A$1),1,3))”,然后同时按下”Ctrl+Shift+Enter”组合键,得到的结果如图4所示。
图4 统计每位同学的平均成绩
应用说明:SUMTOTAL函数除了可以完成汇总求和、平均值、最大值和最小值功能外,可以根据实际需要进行汇总。