引言:越是碎片化时代,越需要系统性学习
今天我们来学习传说中的万能统计函数Subtotal。
在Excel里是这样说明Subtotal的:返回列表或数据库中的分类汇总。通常,使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的列表。一旦创建了分类汇总,就可以通过编辑 Subtotal 函数对该列表进行修改。
Subtotal有三个不同于其他函数的特点,注定在Excel函数里,起着无法代替的作用。
一、Subtotal基本用法
Subtotal是Excel中唯一一个能统计用户可见单元格的函数。和其他函数不同的是他不是“一个函数”,而是“一群函数”。
其基本语法为:Subtotal(Function_Num,Ref1,Ref2, ...)
1.Function_Num: 为 1~11(包含隐藏值)或 101~111(忽略隐藏值)之间的数字,指定使用何种函数在列表中进行分类汇总计算。
2.Ref1……Refn:为要对其进行分类汇总计算的第1至29个命名区域或引用。必须是对单元格区域的引用。
二、Subtotal隐藏值
注意:第一个参数使用111和使用101111的区别
使用1~11,将包括手动隐藏值,如有隐藏行,结果包含隐藏行的值;
使用101~111,忽略手动隐藏值,如有隐藏行,始终排除隐藏行的值。
三、Subtotal与Sum的区别
Subtotal:只对筛选后的数据结果数据进行求和
四、Subtotal计数:Count与Counta
Count:返回包含数字以及包含参数列表中的数字的单元格的个数
Counta:返回参数列表中非空值的单元格个数
=Subtotal(2,B2:B8) 统计包含数字单元格的个数
五、筛选后填充
Excel中经常会用到筛选,筛选后的序号往往是不连续的,如果想筛选后生成连续序号,可以通过Subtotal实现。
=Subtotal(3,2:B2)*1
第一参数使用3,表示使用Count函数的计算规则,统计B列单元格数量。
注意:直接使用Subtotal函数时,筛选状态下Excel会默认将末行当做汇总行,公式中 *1,就是为了避免筛选时出现末行。
六、避开同类计算
总结
Subtotal有三个特点:
1.如果在 Ref1, Ref2,… 中有其他的分类汇总(嵌套分类汇总),将忽略这些嵌套分类汇总,以避免重复计算。也就是在数据区域中有Subtotal获得的结果将被忽略!
2.当 Function_Num 为从 1 到 11 的常数时,Subtotal 函数将包括通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。当您要分类汇总列表中的隐藏和非隐藏值时,请使用这些常数。当 Function_Num 为从 101 到 111 的常数时,Subtotal 函数将忽略通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。当您只分类汇总列表中的非隐藏数字时,使用这些常数。 但不论使用什么 Function_Num 值,Subtotal 函数都会忽略任何不包括在筛选结果中的行。 而Subtotal 函数不适用于数据行或水平区域。隐藏某一列不影响分类汇总。但是隐藏分类汇总的垂直区域中的某一行就会对其产生影响。
3.可以代替上面说的11种函数,当有上面说的两种特点情况时,就可以使用Subtotal来完成。
所以在需要处理隐藏数据相关的应用时,Subtotal是其它函数无法代替的,也是Subtotal最大最重要的特点。
如此好用的万能函数,你GET到了吗?
image.png
网友评论