Excel中的SUBTOTAL函数是一个非常强大且灵活的统计函数,广泛应用于数据分析和处理。通过调整第一个参数,SUBTOTAL函数可以实现计数、求和、平均值、最大值等多种统计功能,并且可以选择是否忽略隐藏或筛选的单元格。本文将详细介绍SUBTOTAL函数的用法及一些使用技巧,帮助读者更好地掌握这一函数。
SUBTOTAL函数的语法结构为:SUBTOTAL(function_num, ref1, [ref2], ...),其中:
function_num(必需):这是一个数字,用于指定要执行的汇总函数类型。这个数字可以是1到11或101到111之间的任何整数。1到11之间的数字包含手动隐藏的行,而101到111之间的数字则忽略手动隐藏的行。无论是1到11还是101到111,SUBTOTAL函数始终会忽略已筛选掉的单元格。
ref1(必需):要进行汇总计算的第一个引用或命名区域。
[ref2](可选):要进行汇总计算的第二个到第254个引用或命名区域。
SUBTOTAL函数的主要功能包括:
1. 求和:使用function_num为9或109。当使用9时,包含隐藏的行;当使用109时,忽略隐藏的行。
2. 计数:使用function_num为3或103。当使用3时,包含隐藏的行;当使用103时,忽略隐藏的行。
3. 平均值:使用function_num为1或101。当使用1时,包含隐藏的行;当使用101时,忽略隐藏的行。
4. 最大值:使用function_num为4或104。
5. 最小值:使用function_num为5或105。
假设在D2:D22区域内有销售数据,并希望根据筛选结果动态求和。
包含隐藏行:在目标单元格中输入公式=SUBTOTAL(9, D2:D22)。
忽略隐藏行:在目标单元格中输入公式=SUBTOTAL(109, D2:D22)。
假设在A2:A11区域内有员工姓名,并希望统计筛选后的员工数量。
包含隐藏行:在目标单元格中输入公式=SUBTOTAL(3, $A$2:$A$11)。
忽略隐藏行:在目标单元格中输入公式=SUBTOTAL(103, $A$2:$A$11)。
假设在C3:C9区域内有销售数据,并希望计算筛选后的平均值。
包含隐藏行:在目标单元格中输入公式=SUBTOTAL(1, C3:C9)。
忽略隐藏行:在目标单元格中输入公式=SUBTOTAL(101, C3:C9)。
在处理数据表时,有时需要为筛选后的数据生成连续的序号。可以使用SUBTOTAL函数来实现这一点。
在目标单元格中输入公式=SUBTOTAL(103, B$2:B2)*1(或=SUBTOTAL(103, B$2:B2)-1后自行调整)。
然后拖动填充柄向下复制公式,即可生成连续的序号。
有时需要计算筛选后符合特定条件的数据数量,可以结合SUBTOTAL函数和SUMPRODUCT函数来实现。
假设在B2:B11区域内有部门名称,C2:C11区域内有销售额数据。
希望在C14单元格中计算筛选后销售额大于等于50且属于“销售1部”的数据数量。
在C14单元格中输入公式:=SUMPRODUCT(($B$2:$B$11=B15)*(SUBTOTAL(2, OFFSET($C$2, ROW($C$2:$C$11)-2, 0))))。
SUBTOTAL函数的一个特点是它不会统计公式计算的结果。这意味着,如果数据区域中包含由其他公式计算得到的值,SUBTOTAL函数将忽略这些公式结果。
SUBTOTAL函数可以根据筛选结果动态调整求和范围。例如,如果筛选出了某个部门的数据,SUBTOTAL函数会自动计算筛选后的数据总和。
在目标单元格中输入公式=SUBTOTAL(9, $
40M玲珑视频免升级去广告软件
40M玲珑视频去除广告
40M玲珑视频应用
110.94M小红车app官方版
1.02G姜饼人王国国际版最新版本
1.02G冲呀饼干人王国国际服
1.02G饼干人王国 国际版
89.77M植物大战僵尸1原版中文
298.92M无尽梦回手游
26.32M口袋妖怪漆黑的魅影6.0
本站所有软件来自互联网,版权归原著所有。如有侵权,敬请来信告知 ,我们将及时删除。 琼ICP备2023003481号-5