您的位置:首页 > 新闻资讯

Excel SUBTOTAL函数的高效应用与实用技巧

2024-11-04 14:49:18

Excel中的SUBTOTAL函数是一个非常强大且灵活的统计函数,广泛应用于数据分析和处理。通过调整第一个参数,SUBTOTAL函数可以实现计数、求和、平均值、最大值等多种统计功能,并且可以选择是否忽略隐藏或筛选的单元格。本文将详细介绍SUBTOTAL函数的用法及一些使用技巧,帮助读者更好地掌握这一函数。

Excel SUBTOTAL函数的高效应用与实用技巧 1

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函数的主要功能

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。

SUBTOTAL函数的使用示例

1. 求和

假设在D2:D22区域内有销售数据,并希望根据筛选结果动态求和。

包含隐藏行:在目标单元格中输入公式=SUBTOTAL(9, D2:D22)。

忽略隐藏行:在目标单元格中输入公式=SUBTOTAL(109, D2:D22)。

2. 计数

假设在A2:A11区域内有员工姓名,并希望统计筛选后的员工数量。

包含隐藏行:在目标单元格中输入公式=SUBTOTAL(3, $A$2:$A$11)。

忽略隐藏行:在目标单元格中输入公式=SUBTOTAL(103, $A$2:$A$11)。

3. 平均值

假设在C3:C9区域内有销售数据,并希望计算筛选后的平均值。

包含隐藏行:在目标单元格中输入公式=SUBTOTAL(1, C3:C9)。

忽略隐藏行:在目标单元格中输入公式=SUBTOTAL(101, C3:C9)。

SUBTOTAL函数的高级技巧

1. 生成连续序号

在处理数据表时,有时需要为筛选后的数据生成连续的序号。可以使用SUBTOTAL函数来实现这一点。

在目标单元格中输入公式=SUBTOTAL(103, B$2:B2)*1(或=SUBTOTAL(103, B$2:B2)-1后自行调整)。

然后拖动填充柄向下复制公式,即可生成连续的序号。

2. 筛选后的条件计数

有时需要计算筛选后符合特定条件的数据数量,可以结合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))))。

3. 忽略公式计算结果

SUBTOTAL函数的一个特点是它不会统计公式计算的结果。这意味着,如果数据区域中包含由其他公式计算得到的值,SUBTOTAL函数将忽略这些公式结果。

4. 动态求和与筛选

SUBTOTAL函数可以根据筛选结果动态调整求和范围。例如,如果筛选出了某个部门的数据,SUBTOTAL函数会自动计算筛选后的数据总和。

在目标单元格中输入公式=SUBTOTAL(9, $

最新游戏
  • 悠漫部落漫画app类型:新闻阅读
    大小:12.84M

    悠漫部落漫画app是一款专为漫画爱好者打造的掌上漫画阅读应用...

  • zzzfun类型:影音娱乐
    大小:13.58M

    zzzfun是一款专注于动漫资源分享与在线观看的动漫平台,为...

  • zzzfun动漫纯净版类型:影音娱乐
    大小:13.58M

    zzzfun动漫纯净版是一款专为动漫爱好者打造的在线观看平台...

  • 满分跳绳手机版类型:体育运动
    大小:94.18M

    满分跳绳手机版是一款专为跳绳爱好者及需要跳绳训练的用户设计的...

  • zzzfun最新版本类型:影音娱乐
    大小:13.58M

    zzzfun最新版本是一款专为动漫爱好者设计的多功能应用,集...

本站所有软件来自互联网,版权归原著所有。如有侵权,敬请来信告知 ,我们将及时删除。 琼ICP备2023003481号-5