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

SUBTOTAL函数使用方法详解

2024-11-11 12:49:01

SUBTOTAL函数是Excel中一个功能强大的工具,主要用于筛选和隐藏后的数据统计。这个函数不仅能够帮助用户快速得到所需的数据汇总,还能在处理包含隐藏数据的表格时表现出其独特的灵活性。以下是SUBTOTAL函数的详细使用方法及实例解析,旨在帮助那些对SUBTOTAL函数使用感兴趣的读者。

SUBTOTAL函数使用方法详解 1

SUBTOTAL函数怎么用?

SUBTOTAL函数在Excel中的语法结构为:SUBTOTAL(function_num, ref1, [ref2], ...),其中function_num用于指定分类汇总时要使用的函数类型,如求和、计数、求平均值等,而ref1, [ref2], ...则是需要进行分类汇总计算的单元格区域或命名区域。值得注意的是,function_num的取值范围有两个:1至11和101至111。这两个范围的区别在于,当function_num取1至11时,SUBTOTAL函数会对筛选后的数据进行统计,包括手动隐藏的数据;而当function_num取101至111时,SUBTOTAL函数仅对可见的数据进行统计,忽略筛选掉的数据和手动隐藏的数据。

SUBTOTAL函数使用方法详解 2

1. SUBTOTAL函数的基本功能

SUBTOTAL函数支持多种函数类型,具体对应关系如下:

SUBTOTAL函数使用方法详解 3

1 (101): AVERAGE,求平均值

SUBTOTAL函数使用方法详解 4

2 (102): COUNT,计数非空单元格

3 (103): COUNTA,计数非空单元格(包括字母)

4 (104): MAX,求最大值

5 (105): MIN,求最小值

6 (106): PRODUCT,求乘积

7 (107): STDEV,基于样本的标准偏差值(忽略逻辑值和文本)

8 (108): STDEVP,基于总体的标准偏差值

9 (109): SUM,求和

10 (110): VAR,基于样本的方差(忽略逻辑值和文本)

11 (111): VARP,基于总体的方差

2. 使用SUBTOTAL函数进行计数和求和

假设A1:C11区域存储了各业务员的销售额数据,我们希望通过SUBTOTAL函数来进行计数和求和操作

计数实例:

在F1单元格输入公式:=SUBTOTAL(3, $A$2:$A$11),用于计算A列中非空单元格的数量。当未筛选数据时,SUBTOTAL函数返回值为“10”。当筛选出“销售1部”的数据时,SUBTOTAL函数返回值为“4”,仅计算筛选后非空单元格的数量。

求和实例:

在F1单元格输入公式:=SUBTOTAL(9, $C$2:$C$11),用于计算C列中销售额的总和。当未筛选数据时,SUBTOTAL函数返回值为“550”。当筛选出“销售1部”的数据时,SUBTOTAL函数返回值为“220”,仅计算筛选后数据的总和。

3. 隐藏行后的计数和求和

在某些情况下,数据表中的某些行可能被隐藏。SUBTOTAL函数在处理隐藏数据时表现出不同的行为,取决于function_num的取值。

隐藏后计数实例:

在F1单元格输入公式:=SUBTOTAL(103, $A$2:$A$11),此时所有数据均未隐藏,SUBTOTAL函数返回值为“10”。当隐藏第2、3行数据后,SUBTOTAL函数返回值为“8”,说明它忽略了隐藏的行。

隐藏后求和实例:

在F1单元格输入公式:=SUBTOTAL(109, $C$2:$C$11),此时所有数据均未隐藏,SUBTOTAL函数返回值为“550”。当隐藏第2、3行数据后,SUBTOTAL函数返回值为“520”,忽略了隐藏行中的销售额。

4. 生成筛选后仍连续的序号

在数据表中,有时需要为筛选后的数据生成连续的序号。通过SUBTOTAL函数可以实现这一点。

在A2单元格输入公式:=SUBTOTAL(103, $B$2:B2)*1,然后拖动填充柄向下复制公式,在A2:A11生成连续序号。当筛选出“销售1部”的数据时,序号仍保持连续,这是因为SUBTOTAL函数在生成序号时忽略了隐藏或筛选掉的行。

5. 对筛选后的数据进行条件计数

通过SUBTOTAL函数与其他函数的组合,可以实现更复杂的统计需求,如对筛选后的数据进行条件计数。

假设在B2:B11区域存储了部门信息,C2:C11区域存储了各业务员的销售额。我们希望计算筛选出销售额大于等于50的各部门

最新游戏
  • 美味星球4全解锁版类型:益智休闲
    大小:32.94M

    《美味星球4全解锁版》是一款集休闲、益智与冒险于一体的经典吞...

  • 红辰乐购APP类型:购物时尚
    大小:22.89M

    红辰乐购APP是一款集购物、娱乐、优惠于一体的综合性电商平台...

  • 坦克入侵战类型:飞行射击
    大小:31.29M

    坦克入侵战是一款策略与动作相结合的战争类游戏。玩家将扮演一名...

  • 画画树APP类型:实用工具
    大小:81.18M

    画画树APP是一款专为绘画爱好者和创意工作者设计的综合性绘画...

  • 一键无痕看1.2.7类型:实用工具
    大小:1.04M

    一键无痕看1.2.7是一款旨在保护用户隐私的浏览工具,它允许...

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