设为星标,第一时间接收到小编的文章 进入主题,直接来看 语法: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。 |
|