Subtotal函数:各种简单统计,用我就够了

我们知道,Excel函数的功能总是非常单一的。例如:sum函数用于求和;average函数用于求平均值;count函数用于数字计数;max函数、min函数也只用于求出最大值最小值……如果使用这些函数,想一条公式完成多种统计,往往会使用函数嵌套。然而,因为多函数嵌套对于Excel小白非常的难,所以Excel为我们提供了一个简单的“万能统计”函数:Subtotal函数。这个函数使用非常简单,但却身兼数职:求和、平均值、数字计数、非空单元格计数、最大值、最小值等等足有11个功能之多。学好这个函数,你将轻松拥有完成11种简单统计的技巧。本文将分为三个部分对此函数进行介绍:

文:傲看今朝 图片来自网络

1、简单实用的subtotal函数基本功能;

2、利用subtotal函数给表格自动编号;

3、利用subtotal函数计算选手最后得分

一、简单实用的subtotal函数基本功能

subtotal函数,顾名思义,sub表示替代,total表示总计总额,字面意思就是替代求和(sum)的函数。然而,subtotal绝远不止于此,实际上前面我们已经提到,subtotal函数可以完成11种简单的统计。因此subtotal函数又叫分类汇总函数。

1:函数名,subtotal,意即替代求和,是一个分类汇总函数;

2:功能代码,为1到11或者101到111之间的数字;想使用什么功能,就用对应的代码即可。见后面的对照表;

3:引用区域,这个地方只能是引用而不能是其他。

4:主要功能是对数据表进行求平均、求和、求最大最小、求方差等分类汇总;

1

下面是subtotal函数第一个参数的具体说明:

第一参数说明

大家注意到了这一点:第一列和第二列的数值对应同一个函数功能。不同的只是,代码1到11不忽略隐藏值,而101到111忽略了隐藏值。这之间到底有什么分别呢? 下图以最大值为例来说明这个区别。

第一参数说明

N8单元格为最高值,但被隐藏起来,使用不同的代码却得到了不同的结果,这就是区别。特别注意的一点就是,如果第二参数选择的区域是横向的,那么不管使用什么样的代码都将不会忽略隐藏值。

二、利用subtotal函数给表格自动编号

请看下面的动画,我们如何做才能得到这样自动编号效果(当单行或者多行被隐藏或者取消隐藏时,A列序号会自动调整)呢?

当单行或者多行被隐藏或者取消隐藏时,A列序号会自动调整

我们仔细观察,序号是从小排到大的,且A列每一个序号等于其对于B列单元格向上数非空单元格的个数,例如,A3单元格值为2,即是b2:B3单元格区域中非空单元格的个数;A7单元格的值为6,即是B2:B7单元格区域中非空单元格的个数。因此我们直接在A2单元格中输入公式:=counta(B$2:B2),然后复制到整列即可。

counta函数

然而,当我们隐藏某些行列时,我们发现行列时,A列的序号却无法自动更新调整。如下图:


序号无法自动更新

我们如何解决这个问题呢?很显然,使用counta函数已经无法得到我们想要的效果了,因此我们请出subtotal函数来帮忙,前面我们提到,subtotal函数在第一参数使用101到111代码时,会自动忽略隐藏值,subtotal中计算非空单元格数的代码为103,因此我们可以写出下面的公式:A2单元格中输入公式:=subtotal(103,B$2:B2),然后将此公式复制到全列即可。

无论怎么隐藏,A列的序号都将自动调整

三、利用subtotal函数计算选手最后得分

看下图,如何快速计算出选手的最后得分呢?

如何计算选手的最后得分呢

一般比赛的要求都是去掉一个最高分,再去掉一个最低分,然后再取剩余值的平均值。因此我们解题步骤通常如下:

1.求总分:=SUM(B2:J2)

2.减去最低分和最高分:=SUM(B2:J2)-max(B2:J2)-min(B2:J2)

3.剩余的值求平均值,本例子中共有9位评委,去掉最低和最高两个评委,将剩下的7位评委的分数求平均分,因此公式为:=(SUM(B2:J2)-max(B2:J2)-min(B2:J2))/7

在普通解法中,我们用到了三个函数,思路很简单,但是还是比较麻烦的,如果我们使用subtotal函数,2个函数就可以搞定选手的最后得分了。公式如下:{=SUM(SUBTOTAL({9,4,5},B2:J2)*{1,-1,-1})/7}

这个公式相较于前面的公式理解稍微难一点,我解释一下:

subtotal函数共2个参数,第一个参数为{9,4,5},9为求和,4为最大值,5为最小值,第二个参数为B2:J2;

在subtotal函数乘一个数组{1,-1,-1},即得到sum(B2:J2),-max(B2:J2),-min(B2:J2),接下来利用sum函数将这几个值加起来就相当于公式:=SUM(B2:J2)-max(B2:J2)-min(B2:J2);

将得到的结果除以7即得到了=SUM(SUBTOTAL({9,4,5},B2:J2)*{1,-1,-1})/7的公式,最后按下Ctrl+Shift+Enter完成数组公式的输入。

最终结果

关于subtotal函数,今天的分享就到这里,感兴趣的同学可以与我联系,可以评论留言并写下自己的练习方式,我会将练习材料发给大家学习。

你们给我点赞、关注我的账号就是对我最大支持。

推荐阅读更多精彩内容