办公常用的十大函数
@SUBTOTAL函数
subtotal函数基本功能
分类汇总函数**SUBTOTAL
,它可以包括求和、汇总、平均等等多个函数的使用。SUBTOTAL
函数的优点在于可以忽略隐藏的单元格、支持三维运算和区域数组引用**。
SUBTOTAL
函数就是返回一个列表或数据库中的分类汇总情况。
语法:
SUBTOTAL(function_num,ref1,[ref2],...)
参数:
Function_num:必需。 数字 1-11 或 101-111,用于指定要为分类汇总使用的函数。 如果使用 1-11,将包括手动隐藏的行,如果使用 101-111,则排除手动隐藏的行;始终排除已筛选掉的单元格。
ref1:必需。要对其进行分类汇总计算的第一个命名区域或引用。
**ref2…**:可选。要对其进行分类汇总计算的第 2 个至第 254 个命名区域或引用。
案例
一、利用subtotal函数给表格自动编号
如何做才能得到这样自动编号效果(当单行或者多行被隐藏或者取消隐藏时,A列序号会自动调整)呢?

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

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

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

二、利用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完成数组公式的输入。