Excel中subtotal()函数的用法

1. 函数功能概述
   • SUBTOTAL函数用于返回数据清单或数据库中的分类汇总。这个函数可以计算平均值、计数、最大值、最小值等多种统计结果，并且在包含隐藏行的数据区域中，它能根据可见行的数据进行计算，这是它与其他一些简单统计函数（如SUM、AVERAGE等）的一个重要区别。
2. 语法结构
   • SUBTOTAL(function_num,ref1,[ref2,...])
   • function_num：这是必需参数，是一个数字，范围从 1 到 11（包含隐藏值）或 101 到 111（忽略隐藏值），用于指定要执行的函数类型。例如，1 代表AVERAGE（平均值），9 代表SUM（求和）等。
   • ref1：这是必需参数，要对其进行分类汇总计算的第一个单元格区域或引用。
   • ref2,...：这是可选参数，最多可以有 254 个可选的单元格区域或引用。
3. 参数function_num对应的函数类型示例
   • 求和（SUM）
     • 当function_num = 9（包含隐藏值）或function_num = 109（忽略隐藏值）时，用于计算指定区域内数值的总和。
     • 例如，在一个销售数据表格中，有列 A 是产品名称，列 B 是销售数量。如果要计算销售数量的总和，可以使用SUBTOTAL(9,B:B)或SUBTOTAL(109,B:B)（假设数据在 B 列）。在有隐藏行的情况下，9 会把隐藏行的数据也参与求和计算，而 109 则会忽略隐藏行的数据。
   • 平均值（AVERAGE）
     • 当function_num = 1（包含隐藏值）或function_num = 101（忽略隐藏值）时，计算指定区域内数值的平均值。
     • 例如，在学生成绩表格中，列 C 是成绩数据。如果要计算成绩的平均值，可使用SUBTOTAL(1,C:C)或SUBTOTAL(101,C:C)。
   • 计数（COUNT）
     • 当function_num = 2（包含隐藏值）或function_num = 102（忽略隐藏值）时，用于计算包含数字的单元格的数量。
     • 比如，在一个人员信息表格中，列 D 是年龄数据。如果要统计有年龄数据（即包含数字）的单元格数量，可使用SUBTOTAL(2,D:D)或SUBTOTAL(102,D:D)。
   • 最大值（MAX）
     • 当function_num = 4（包含隐藏值）或function_num = 104（忽略隐藏值）时，返回指定区域内的最大值。
     • 例如，在一个库存表格中，列 E 是库存数量。如果要找出库存数量的最大值，可使用SUBTOTAL(4,E:E)或SUBTOTAL(104,E:E)。
   • 最小值（MIN）
     • 当function_num = 5（包含隐藏值）或function_num = 105（忽略隐藏值）时，返回指定区域内的最小值。
     • 例如，在一个成本表格中，列 F 是成本数据。如果要找出成本的最小值，可使用SUBTOTAL(5,F:F)或SUBTOTAL(105,F:F)。
4. 使用场景及注意事项
   • 数据筛选和隐藏行的情况
     • 在对数据进行筛选操作后，SUBTOTAL函数会根据筛选后的可见行进行计算。例如，当你对一个销售数据表按地区进行筛选后，使用SUBTOTAL函数计算销售额的总和，它只会计算可见行（即符合筛选条件的行）的销售额总和。
   • 嵌套分类汇总
     • 可以在同一数据区域中多次使用SUBTOTAL函数进行不同类型的分类汇总。例如，先计算销售数量的总和，再计算销售金额的平均值等，通过合理设置function_num参数来实现多种统计需求的嵌套。
   • 与其他函数的区别
     • 与简单的统计函数如SUM、AVERAGE等相比，SUBTOTAL函数在处理隐藏行数据时有独特的优势。普通的SUM函数会把隐藏行的数据也计算在内，而SUBTOTAL函数可以根据需要选择是否忽略隐藏行的数据。