办公常用的十大函数

@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完成数组公式的输入。

三、合并汇总

参考

• Subtotal函数：各种简单统计