b-2轰炸机|EXCEL: SUBTOTAL函数实现多重统计需求 excel

文章图片

文章图片

文章图片

SUBTOTAL函数是一个多功能的函数，他可以用来求和、计数，、平均值、最大最小值等

集成了11种运算，同时也是唯一个可以只统计可见单元格数据的函数，忽略被隐藏单元格的数据。
语法：SUTOTAL（计算方式，数据区域1 ，数据区域2 ， … ，数据区域254）
第一个参数计算方式，共有11种计算方式，每一种又分为是否将隐藏的数据参与统计。具体如下：
第二个参数数据区域：要统计的数据所在的单元格区域。最多可以设置254个数据区域。
SUBTOTAL 函数只适用于列或垂直区域数据计算，不适用于行或水平区域数据计算。当计算方式使用101-111 时需要汇总水平区域时，则隐藏某一列不影响数据汇总。但隐藏垂直区域中的某一行就会产生影响。
示例基本用法
修改函数中第一个参数计算方式，输入数值1-11可以实现11种计算，如果需要忽略隐藏行数值计算的话，把计算方式参数修改为101-111即可。如下图：

当隐藏行是计算数据会在隐藏后，自动重新计算可见单元格区域数据。
筛选状态下数据汇总当数据在筛选状态时如果汇总行使用的是SUM、COUNTA、AVERAGE、MAX或者MIN等函数筛选后仍会对所有数据计算，要对筛选后的数据进行计算就要使用SUBTOTAL函数，计算方式参数使用101-111 。如下图统计求和：

合计公式=SUBTOTAL(109B2:B10)
参数109 ，是对忽略隐藏行的数据求和，仅对B2单元格至B10单元格中可见单元格区域数据求和。不同的筛选条件下会自动更新汇总数据。
隐藏行时序号自动连续
A2单元格序号公式：=SUBTOTAL(103B$2:B2)*1下拉填充。计算方式参数103是忽略隐藏行对非空单元格计数，计数区域B2单元格到B2单元格区域中非空单元格计数，返回1 。 B$2单元格中行号绝对引用，下拉填充后A3单元格统计区域为B$2:B3 。
所以A3单元格公式为：=SUBTOTAL(103B$2:B3)*1 ，对B2单元格到B3单元格区域中非空单元格计数，返回2 。
以此类推。
乘以1是因为统计函数SUBTOTAL ，会默认最后一行为汇总行，在筛选状态下回保留最后一行，乘以1转化为普通计算。
分类汇总时的使用
上图中可见B14行中公式为=SUBTOTAL(9B2:B12) ，为B2至B12但单元格区域的数据求和，结果应返回值为4884 ，但结果却返回了2442.这个是因为B5、B9、B13单元格使用了SUBTOTAL函数汇总求和，如果统计区域中嵌套了其他的小计， SUBTOTAL函数将忽略这些嵌套式小计以避免双重计数。
当使用分类汇总时，汇总行默认求和，求和使用的就是SUBTOTAL函数，这时如果我们需要分类求个数、最大或最小值等，我们只需要修改函数中第一个计算方式参数即可，如下图：
【b-2轰炸机|EXCEL: SUBTOTAL函数实现多重统计需求】