头部广告

Excel超牛的统计函数SUBTOTAL,1个可抵11个函数,职场必备!

在Excel中对数据进行统计分析是必备技能,今天就跟大家分享一个超牛的统计函数SUBTOTAL,这个函数1个可抵11个函数使用。并且,它最大的特点就是可以仅统计可见区域数据,不统计不统计的隐藏的数据区域,在特定工作场景中非常实用。

SUBTOTAL函数简介

SUBTOTAL函数返回数据清单或数据库中的分类汇总。它一共有11种对应的计算规则,分别使用对应的数字来代替。

语法:SUBTOTAL(function_num,ref1,ref2,...)

第一参数:函数序号其实就是数据分类汇总的规则,当参数值为 1 到 11(包含隐藏值)或 101 到 111(忽略隐藏值)之间的数字,指定使用何种函数在数据清单中进行分类汇总计算。 具体的参数对应,可详见下图

第二参数:第1个引用区域

第三参数:第2个引用区域

以此类推,可以设置1到29个区域或引用。

1.jpg


SUBTOTAL函数的使用效果

如下图所示,我们想统计员工的平均工资,这时使用SUBTOTAL函数的话,我们需要把第一产生设置为1或者101(算术平均值),可以分别针对是否统计隐藏的数据进行求平均值。

使用公式:

包含隐藏值=SUBTOTAL(1,B2:B8)

不包含隐藏值=SUBTOTAL(101,B2:B8)

2.gif


解读:

1、如上图所示,公式=SUBTOTAL(1,B2:B8)中的第一个参数为1,故包含隐藏的行;公式=SUBTOTAL(101,B2:B8)中的第一个参数为101,故不包含隐藏的行。

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

填充连续序号

在日常工作中,我们可以利用SUBTOTAL让表格序号在隐藏行、删除行、筛选数据后都会保持自动更新,这是一个非常实用的小技巧。

使用公式:=SUBTOTAL(103,B$2:B2)

如下图所示,我们在编号这一列第一个空单元格输入公式,然后向下填充编号,然后我们无论隐藏行、删除行、筛选数据后编号这列都是从1开始,并且是连续自动更新。

3.gif


解读:

①公式中参数103所对应的函数为:COUNTA,对非空单元格个数进行计数,从而实现函数方式添加序号。当参数为103时,忽略隐藏的行。

②SUBTOTAL(103,B$2:B2)的结果为1,函数第二参数里面前面的参数B2必须用锁定行B$2,往下拖到从而实现数据区域自动变化,就能自动生成B$2:B2、B$2:B3、B$2:B4...等值。

关于SUBTOTAL函数我们需要记住以下两点:

1、SUBTOTAL函数是可以不统计隐藏区域的数据

2、第一参数设置为1-11是统计隐藏区域,设置为101-111不会统计隐藏区域。


底部广告