那个以一敌十的函数还是来了……

Excel中多功能函数并不多,今天我们要谈的就是以一敌十Subtotal

设为星标,第一时间接收到小编的文章

进入主题,直接来看

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

人话:SUBTOTAL(汇总方式,引用或者区域1,[引用或者区域2],...)

[]表示可选参数,意思区域至少一个,最多支持254区域或者引用

汇总方式

必须掌握的要点:

1、是否包含隐藏行,这里隐藏是指“手动隐藏”或者菜单中的隐藏行

2、筛选对Subtotal隐藏属性无效,1-11,101-109都忽略隐藏

3、不支持直接多维引用,但是支持函数返回的的多维引用(比如OFFSET)

4、SUBTOTAL 函数适用于数据列或垂直区域。不适用于数据行或水平区域

5、第一参数支持数组

6、忽略嵌套使用的分类汇总(subtotal等),避免重复计算

7、有汇总特性,最后一行不参与筛选

以上基础我们基本讲完,通过几个实战案例学习巩固!

案例1:隐藏特性验证(动画演示)

隐藏只针对“手动”,筛选下,隐藏和非隐藏都忽略隐藏

> 手动隐藏,109忽略了隐藏

> 筛选,9和109结果一致(忽略隐藏行)

案例2:一次搞定最大、最小和平均值(动画演示)

第一参数支持常量数组,一次性搞定,不用在MAX、MIN、AVG分别写了

案例3:忽略小计求总计(动画演示)

嵌套使用subtotal会被忽略,避免重复,此特性用在合计中完美

顺便说一下,如果要sum,也不要一个一个小计+,直接sum/2

案例4:全部学生各科最高分合计

支持函数返回的多维引用

公式:=SUM(SUBTOTAL(4,OFFSET(B1:F1,ROW(1:9),)))

OFFSET返回9行,每行一个平面,所以是三维,SUBTOTAL(4 求出每行的最大值,最后SUM求和

案例5:筛选后连续的序号(动画演示)

主要解决最后一行不参与筛选的特性,一般只要在基础上+-0或者+/1等方式计算一下即可。

小结

如果要处理隐藏数据的函数不多,那么第一个要考虑的就是subtotal,第一参数支持22种统计方式,共计11个函数,在数据数据筛选后看筛选合计的,此函数是不二选择,除了这个函数,还有比他更强的,强多少,10倍吧,他就是AGGREGATE

这里是【易办公 早下班】的 Excel办公实战
(0)

相关推荐