ExcelSUBTOTAL——筛选或隐藏状态下的数据统计函数

Question

如题所述

Answer 1

1. 函数SUBTOTAL的语法解析：
SUBTOTAL函数的语法包括以下参数：
- function_num：必需参数，一个介于1到11或101到111之间的数字，用于指定要使用的统计函数。1-11的范围包括手动隐藏的行，而101-111的范围则排除手动隐藏的行。无论选择哪个范围，都会排除通过筛选功能隐藏的单元格。
- ref1：必需参数，表示要进行分类汇总计算的第一个区域或引用。
- ref2，...：可选参数，表示要进行分类汇总计算的第2个至第254个区域或引用。
2. SUBTOTAL函数的工作原理：
- 当function_num为1到11之间的常数时，SUBTOTAL函数会包括通过“隐藏行”命令隐藏的行中的值，适用于对隐藏和非隐藏数据进行分类汇总。
- 当function_num为101到111之间的常数时，SUBTOTAL函数会忽略通过“隐藏行”命令隐藏的行中的值，适用于仅对非隐藏数据进行分类汇总。
- 无论function_num的值如何，SUBTOTAL函数都会忽略任何不包括在筛选结果中的行。
- SUBTOTAL函数适用于数据列或垂直区域，不适用于数据行或水平区域。
- 如果指定的引用是一个三维引用，SUBTOTAL函数会返回错误值#REF!。
3. SUBTOTAL函数的典型应用：
- 在筛选后保持序号不变：使用函数“=SUBTOTAL(3,$H$2:H2)*1”代替原始数字序号，可以自动适应筛选后的序号变化，提高工作效率。
- 筛选条件下的计算：在计算销售总额和平均销售额时，使用SUBTOTAL函数可以根据不同的筛选条件动态改变结果。
- 数据隐藏后的统计：当部分数据被隐藏时，使用SUBTOTAL函数可以确保统计数据不包含隐藏的数据。例如，使用公式“=SUBTOTAL(109,B3:B14)”可以求和隐藏后的数据，使用公式“=SUBTOTAL(102,B2:B13)”可以计算隐藏后的数据数量。