一、Subtotal函数

1、函数功能：Subtotal函数返回列表或数据库中的分类汇总。

2、函数格式：SUBTOTAL(function_num,ref1,ref2,...)

语法解析：function_num——必须为1到11（包含隐藏值）或101到111（忽略隐藏值）之间的数字，用于指定使用何种函数在列表中进行分类汇总计算，具体数字对应的函数如下表：

SUBTOTAL： function_num

ref1：必选，要对其进行分类汇总计算的第1个命名区域或引用。

Ref2，……：可选，要对其进行分类汇总计算的第2个至第254个命名区域或引用。

二、用法

我们有如下图的Excel表，我们所有函数都将在此表为基础（适当修改）进行介绍:

例表

Ps:function_num：1到11（包含隐藏值）或101到111（忽略隐藏值）的用法思路是一样的，这里我们简单的介绍其中几个，其他的以此类推即可。 

---

1、以1和101——AVERAGE（平均值）为例介绍Subtotal函数

1 （包含隐藏值） -求平均值

=SUBTOTAL(1,E2:E6)求的是销售1部各员工冰箱和洗衣机的平均销售额

我们试一试隐藏最后2组数据

1 （包含隐藏值）    101（不包含隐藏值）

发现区别了吗？

=SUBTOTAL(1,E2:E6)即使隐藏了最后2组数据，但是计算区域是包含了隐藏值的；

=SUBTOTAL(101,E2:E6) 隐藏了最后2组数据，计算区域是忽略了隐藏值的；

---

下面我们来看看另外一种隐藏的情况，就是筛选：

筛选数据 筛选后： 1 （包含隐藏值） 筛选后： 101（不包含隐藏值）

看到没有，在仅仅是筛选的情况下，=SUBTOTAL(1,E2:E6)和=SUBTOTAL(101,E2:E6)计算结果是一样的，那是因为：

1、SUBTOTAL，只对筛选后数据结果数据进行计算，这也是在SUBTOTAL求和时SUBTOTAL，只对筛选数据结果数据进行求和，而SUM不受筛选结果影响，会对所有数据进行求和。

2、此函数是Excel中唯一一个能统计用户可见单元格的函数

3、subtotal的包含隐藏值和忽略隐藏值是指手动隐藏值，而不是自动隐藏值，自动筛选不属于手动隐藏。所以在自动筛选的情况下包含隐藏值和忽略隐藏值计算结果是一样的。

---

最后我们看那一看在上面筛选的情况下对数据进行隐藏会有什么情况：

我们准备隐藏员工陈宗华这一行的数据

隐藏前 隐藏后  ：1 （包含隐藏值） 隐藏后： 101（不包含隐藏值）

这时的计算结果也是一样的，大家可以多试试。

subtotal函数是Excel中唯一一个能统计用户可见单元格的函数

---

2、Subtotal计数

原始数据 function_num说明 拓展资料

---

3、利用subtotal函数自动编号

subtotal函数自动编号 countal函数自动编号

以上使用=COUNTA($B$2:B2)和=SUBTOTAL(3,$B$2:B2)是一样的。但是我们如何做才能得到这样自动编号效果呢？

当我们隐藏员工王淑芬和周雨欣这两行数据时，上述序号如下：

隐藏数据后序号变化（3）

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

前面我们提到，subtotal函数在第一参数使用101到111代码时，会自动忽略隐藏值，subtotal中计算非空单元格数的代码为103，因此我们可以试试103参数：

隐藏数据后序号变化（103） 取消隐藏后序号变化（103）

那么这样当单行或者多行被隐藏或者取消隐藏时，A列序号会自动调整的。

---

4、Subtotal求和避开同类计算

平均销售额

=SUBTOTAL(1,E2:E17)可以计算出销售1部、销售2部、销售3部各销售员的平均销售额，但是你会不会担心会把第7行销售1部汇总数据和第12行销售2部汇总数据进行了汇总而导致重复计算呢？ 

你的担心是多余的，因为分类汇总情况下的汇总求和是使用Subtotal求和

分类汇总

而SUBTOTAL函数有以下几个特点

1、如果在 ref1, ref2,… 中有其他的分类汇总（嵌套分类汇总），将忽略这些嵌套分类汇总，以避免重复计算。也就是在数据区域中有SUBTOTAL获得的结果将被忽略；

2、当function_num 为从 1 到 11 的常数时，SUBTOTAL 函数将包括通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。当你要分类汇总列表中的隐藏和非隐藏值时，请使用这些常数。当function_num 为从 101 到 111 的常数时，SUBTOTAL 函数将忽略通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。当你只分类汇总列表中的非隐藏数字时，使用这些常数。 但不论使用什么 function_num 值，SUBTOTAL 函数都会忽略任何不包括在筛选结果中的行。且SUBTOTAL 函数不适用于数据行或水平区域。隐藏某一列不影响分类汇总。但是隐藏分类汇总的垂直区域中的某一行就会对其产生影响；

3、在需要处理隐藏数据相关的应用时，SUBTOTAL是其它函数无法代替的，也是SUBTOTAL最大最重要的特点，SUBTOTAL函数并不是“一个函数”，而是“一群函数”。

---

三、综合应用

一般比赛的要求是去掉一个最高分，再去掉一个最低分，然后再取剩余值的平均值作为选手的最后得分。

Office职场大学的第一届复盘大赛中各同学的成绩如下：

Office职场大学的第一届复盘大赛成绩

那么一般来说各位同学的最终得分可以用=(SUM(B3:J3)-MAX(B3:J3)-MIN(B3:J3))/7计算

1、求总分：=SUM(B3:J3)

2、求最低分和最高分：=MAX(B3:J3)和MIN(B3:J3)

3、求平均分：=(SUM(B4:J4)-MAX(B4:J4)-MIN(B4:J4))/7【剩余的值求平均值，本次复盘大赛中共有9位评委，去掉最低分和最高分两位评委，将剩下的7位评委的分数中求平均分】

在普通解法中，我们用到了三个函数，思路很简单，很好理解。但是还是比较麻烦的，如果我们使用subtotal函数，2个函数就可以搞定各位同学的最后得分了。公式如下：

{=SUM(SUBTOTAL({9,4,5},B3:J3)*{1,-1,-1})/7}

subtotal函数求最终得分

公式解析：

1、subtotal函数共2个参数，第一个参数为{9,4,5},9为求和，4为最大值，5为最小值，第二个参数为B3:J3是计算的第1个命名区域；

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

【这就把一般解法的三个公式汇总在一起了】；

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

                                                                                                                                                                                                                                   Wehfouh

                                                                                                                                                                                                                                   2018-10-16