美文网首页E战到底特训营
Day13-1 万能函数Subtotal你会吗?

Day13-1 万能函数Subtotal你会吗?

作者: 阿敏志刘 | 来源:发表于2018-09-13 21:43 被阅读0次

    引言:越是碎片化时代,越需要系统性学习
    今天我们来学习传说中的万能统计函数Subtotal。
    在Excel里是这样说明Subtotal的:返回列表或数据库中的分类汇总。通常,使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的列表。一旦创建了分类汇总,就可以通过编辑 Subtotal 函数对该列表进行修改。
    Subtotal有三个不同于其他函数的特点,注定在Excel函数里,起着无法代替的作用。

    下面我们从6个方面来学习: image.png

    一、Subtotal基本用法
    Subtotal是Excel中唯一一个能统计用户可见单元格的函数。和其他函数不同的是他不是“一个函数”,而是“一群函数”。

    一个函数相当于11个函数。可用于统计平均值、计数、最大值、最小值、乘积、标准偏差、求和、方差等,详见下图。 image.png

    其基本语法为:Subtotal(Function_Num,Ref1,Ref2, ...)
    1.Function_Num: 为 1~11(包含隐藏值)或 101~111(忽略隐藏值)之间的数字,指定使用何种函数在列表中进行分类汇总计算。
    2.Ref1……Refn:为要对其进行分类汇总计算的第1至29个命名区域或引用。必须是对单元格区域的引用。
    二、Subtotal隐藏值
    注意:第一个参数使用111和使用101111的区别
    使用1~11,将包括手动隐藏值,如有隐藏行,结果包含隐藏行的值;
    使用101~111,忽略手动隐藏值,如有隐藏行,始终排除隐藏行的值。

    image.png

    三、Subtotal与Sum的区别
    Subtotal:只对筛选后的数据结果数据进行求和

    Sum:不受筛选结果影响,会对所有数据进行求和 image.png

    四、Subtotal计数:Count与Counta
    Count:返回包含数字以及包含参数列表中的数字的单元格的个数
    Counta:返回参数列表中非空值的单元格个数
    =Subtotal(2,B2:B8) 统计包含数字单元格的个数

    =Subtotal(3,B2:B8) 非空单元格数量 image.png

    五、筛选后填充
    Excel中经常会用到筛选,筛选后的序号往往是不连续的,如果想筛选后生成连续序号,可以通过Subtotal实现。
    =Subtotal(3,B2:B2)*1
    第一参数使用3,表示使用Count函数的计算规则,统计B列单元格数量。
    注意:直接使用Subtotal函数时,筛选状态下Excel会默认将末行当做汇总行,公式中 *1,就是为了避免筛选时出现末行。

    image.png

    六、避开同类计算

    Subtotal函数不会统计由Subtotal计算出来的数值,即遇到同类就避开了。 image.png

    总结
    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

    相关文章

      网友评论

        本文标题:Day13-1 万能函数Subtotal你会吗?

        本文链接:https://www.haomeiwen.com/subject/cliggftx.html