语法
SUBTOTAL(function_num,ref1,…)
TIPS:
·在单元格输入=Subtotal(,就会提示上述语法
·此函数并不是“一个函数”,而是“一群函数”
·此函数是Excel中唯一一个能统计用户可见单元格的函数
=subtotal(函数代码,区域或引用,,……)
参数一:函数代码包括11个包含隐藏值的函数和11个忽略隐藏值的函数,代码对应的函数及用途详见下表
参数二:区域或引用,不可省略,可以是单元格、区域,也可以是定义的名称或其他引用。
参数三及以后:区域或引用,可省略。
SUBTOTAL函数应用
一、对隐藏值的计算和忽略
计算销量平均值
公式:F4=SUBTOTAL(1,C2:C8)
或者G4=SUBTOTAL(101,C2:C8)
(1)小伙伴们有没有发现,图1中如果没有隐藏行时,两个公式的计算结果是相同的。也就相当于=SUM(C2:C8)/7
(2)当图2中把序号1、2行隐藏后,G4结果发生了变化。
公式F4=SUBTOTAL(1,C2:C8)中的第一个参数为1,所以它就包含隐藏的行;
相当于=SUM(C2:C8)/7
公式G4=SUBTOTAL(101,C2:C8)中的第一个参数为101,所以它就不包含隐藏的行。相当于=SUM(C4:C8)/5
二、对筛选值的忽略
统一当前值的平均值
公式=SUBTOTAL(1,C2:C8)
公式=SUBTOTAL(101,C2:C8)
通过筛选数据,小伙伴有没有发现无论是使用哪个公式,结果都在发生变化,而且只对当前显示的数值负责。这就得出:通过筛选数据,不管是何种类型的统计,其计算结果只对当前筛选保留的数据复制
三、永远保持连续的序号
公式A2=SUBTOTAL(103,$B$2:B5),按Ctrl Enter填充
隐藏或取消隐藏行,其行号都是连续的。
参数103所对应的函数为:Counta。统计非空单元格的个数。当参数为1**时,忽略隐藏的行。所以公式=SUBTOTAL(103,$B$2:B5)统计的就是从B2开始到当前单元格累计非空单元格个数。
四、按颜色求和
公式G10==SUBTOTAL(9,C:C)
当subtotal函数第一个参数为9,那么就是筛选求和。选中C列中某一个颜色的单元格,然后右击–筛选–按所选单元格的颜色进行筛选,原理:通过筛选和subtotal函数来实现按颜色求和。