在Excel中,统计功能是最基本的功能,如果你对统计函数或技巧一窍不通,那将费时费力……今天我们要学习的是Subtotal函数的使用技巧。


一、SUBTOTAL功能及语法结构。

功能:在指定的范围内根据指定的分类汇总函数进行计算。

语法结构:SUBTOTAL(function_num,ref1,[ref2],...)

1、Function_num 必需; 数字 1-11 或 101-111,用于指定要为分类汇总使用的函数。 如果使用 1-11,将包括手动隐藏的行,如果使用 101-111,则排除手动隐藏的行;始终排除已筛选掉的单元格

excel函数subtotal有多少参数(最牛X的统计函数Subtotal)(1)

2、Ref1 必需;要对其进行分类汇总计算的第一个命名区域或引用。

3、Ref2,... 可选;要对其进行分类汇总计算的第 2 个至第 254 个命名区域或引用。


二、对隐藏值的计算和忽略。

方法:

在目标单元格输入公式=SUBTOTAL(1,C3:C9)或=SUBTOTAL(101,C3:C9)。

解读:

1、公式=SUBTOTAL(1,C3:C9)中的第一个参数为1,故包含隐藏的行;公式=SUBTOTAL(101,C3:C9)中的第一个参数为101,故不包含隐藏的行。

2、当没有隐藏行时,两个公式的计算结果相同,当有隐藏行时,公式=SUBTOTAL(101,C3:C9)的计算结果发生改变。


三、对筛选值的忽略。

目的:统一当前值的平均值。

excel函数subtotal有多少参数(最牛X的统计函数Subtotal)(2)

方法:

1、在目标单元格中输入公式:

=SUBTOTAL(1,C3:C9)或=SUBTOTAL(101,C3:C9)。

2、筛选数据,我们发现计算的结果在发生变化,而且只对当前显示的数值负责。

解读:

通过筛选数据,我们可以发现不管是何种类型的统计,计算的结果只对当前筛选保留的数据复制。


四、永远连续的序号。

目的:隐藏行,序号保持连续。

excel函数subtotal有多少参数(最牛X的统计函数Subtotal)(3)

方法:

1、选定目标单元格。

2、在目标单元格中输入公式:=SUBTOTAL(103,B$3:B3)。

3、Ctrl Enter填充。

4、隐藏或取消隐藏行,可以发现行号都是连续的。

解读:

1、参数103所对应的函数为:Counta。统计飞空单元格的个数。当参数为1XX时,忽略隐藏的行。

2、所以公式=SUBTOTAL(103,B$3:B3)统计的就是从B3开始到当前单元格累计非空单元格数。

,