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, $
404.07M兔小贝识字免费版
283.21M学起Plus手机版
76.78M宜准跑步app最新版
40.55M我爱背单词免费手机版
13.80M艺术签名设计免费生成器
82.11MDuckDuckGo浏览器中文版
90.87M环球青藤
27.60M花火影院免费版
27.60M花火影院
27.60M花火影院2024最新版
本站所有软件来自互联网,版权归原著所有。如有侵权,敬请来信告知 ,我们将及时删除。 琼ICP备2023003481号-5