TTEP.CN > 故障 >
Excel中Subtotal函数使用,可实现多个函数功能
Excel函数的学习本身并不难,但是放在实际应用中就有可能会碰到很多问题。在用Excel做数据统计时,最常见的就是求和Sum、平均值Average、计数Count、最大值Max、最小值Min等函数,但是这些函数解决不了筛选和隐藏的问题。
而Subtotal函数在计算时是可以忽略被筛选和隐藏掉的行,也就是说函数结果会随着筛选结果的变化而变化,而且这一个函数就能实现AVERAGE、COUNT、COUNTA、MAX、MIN、PRODUCT、STDEV、STDEVP、SUM、VAR、VARP这11个函数的功能。
既解决了筛选和隐藏问题,又能以一敌十,这样的函数是不是值得你拥有?
Subtotal函数的语法
SUBTOTAL(function_num,ref1,…)
其中,function_num代表不同的函数,有1-11和101-111两组,对于在筛选模式下被淘汰的行,subtotal函数不再统计这部分行数据。对于手动隐藏的行,1-11统计隐藏的行,101-111是不统计隐藏的行。
ref1、ref2, ...为要进行分类汇总计算的1到254个区域或引用。

Subtotal函数的基本用法
例如,当function_num参数为1时求平均值,效果和average函数是一样的。

例如,当function_num参数为9时求和,效果和sum函数是一样的。

以此类推,Subtotal函数可以实现function_num代表的11个函数的基本功能。
筛选隐藏下Subtotal函数的用法
为便于大家理解,我们就用最常见的SUM、Subtotal(9)、Subtotal(109)三个函数的结果来举例。

由此我们可以看出,在单元格正常显示状态下,SUM、Subtotal(9)、Subtotal(109)三个函数得到的结果是一致的。
现在我们需要筛选出1月的总营业额。

再例如,如果我们需要筛选湖北的总营业额。

大家有没有发现,当做数据筛选后,sum函数仍然是对全部的数据进行求和,而Subtotal(9)、Subtotal(109)只对筛选后可见单元格进行求和。
手动隐藏下Subtotal函数的用法
有时候做数据处理,我们可能会发现个别行的数据不合格但又不能删除,或者暂时不用统计,需要手动隐藏。

从案例中可以看出,当数据被手动隐藏后,sum函数和Subtotal(9)仍然是对全部的数据进行求和,只有Subtotal(109)只对筛选后可见单元格进行求和。
所以,对于在筛选模式下被淘汰的行,subtotal函数不再统计这部分行数据。对于手动隐藏的行,1-11统计隐藏的行,101-111是不统计隐藏的行。
有多行汇总数据的情况下Subtotal函数的用法
在总营业额时,如果中间有小计行,直接使用average或者sum等函数小计行会重复计算,这个时候我们可以使用subtotal这个函数。
当小计是由Subtotal函数计算的,那么汇总的时候再subtotal就会自动过滤掉前面的小计。

如果你说function_num参数代表的函数太多,记不住,怎么办?不着急,咱们可以这样做:

好啦,今天的Subtotal函数教程就到这里啦,如此以一敌十的强大函数是每个职场人士必备的哦(学习更多函数应用,关注wmzhe---)。
而Subtotal函数在计算时是可以忽略被筛选和隐藏掉的行,也就是说函数结果会随着筛选结果的变化而变化,而且这一个函数就能实现AVERAGE、COUNT、COUNTA、MAX、MIN、PRODUCT、STDEV、STDEVP、SUM、VAR、VARP这11个函数的功能。
既解决了筛选和隐藏问题,又能以一敌十,这样的函数是不是值得你拥有?
Subtotal函数的语法
SUBTOTAL(function_num,ref1,…)
其中,function_num代表不同的函数,有1-11和101-111两组,对于在筛选模式下被淘汰的行,subtotal函数不再统计这部分行数据。对于手动隐藏的行,1-11统计隐藏的行,101-111是不统计隐藏的行。
ref1、ref2, ...为要进行分类汇总计算的1到254个区域或引用。

Subtotal函数的基本用法
例如,当function_num参数为1时求平均值,效果和average函数是一样的。

例如,当function_num参数为9时求和,效果和sum函数是一样的。

以此类推,Subtotal函数可以实现function_num代表的11个函数的基本功能。
筛选隐藏下Subtotal函数的用法
为便于大家理解,我们就用最常见的SUM、Subtotal(9)、Subtotal(109)三个函数的结果来举例。

由此我们可以看出,在单元格正常显示状态下,SUM、Subtotal(9)、Subtotal(109)三个函数得到的结果是一致的。
现在我们需要筛选出1月的总营业额。

再例如,如果我们需要筛选湖北的总营业额。

大家有没有发现,当做数据筛选后,sum函数仍然是对全部的数据进行求和,而Subtotal(9)、Subtotal(109)只对筛选后可见单元格进行求和。
手动隐藏下Subtotal函数的用法
有时候做数据处理,我们可能会发现个别行的数据不合格但又不能删除,或者暂时不用统计,需要手动隐藏。

从案例中可以看出,当数据被手动隐藏后,sum函数和Subtotal(9)仍然是对全部的数据进行求和,只有Subtotal(109)只对筛选后可见单元格进行求和。
所以,对于在筛选模式下被淘汰的行,subtotal函数不再统计这部分行数据。对于手动隐藏的行,1-11统计隐藏的行,101-111是不统计隐藏的行。
有多行汇总数据的情况下Subtotal函数的用法
在总营业额时,如果中间有小计行,直接使用average或者sum等函数小计行会重复计算,这个时候我们可以使用subtotal这个函数。
当小计是由Subtotal函数计算的,那么汇总的时候再subtotal就会自动过滤掉前面的小计。

如果你说function_num参数代表的函数太多,记不住,怎么办?不着急,咱们可以这样做:

好啦,今天的Subtotal函数教程就到这里啦,如此以一敌十的强大函数是每个职场人士必备的哦(学习更多函数应用,关注wmzhe---)。
- 上一篇:巧用Excel快捷键,删除重复数据
- 下一篇:没有了
- 最近发表
- 赞助商链接