2.3.1 函数界的瑞士军刀SUBTOTAL
为什么说SUBTOTAL是函数界的瑞士军刀？因为它可以完成求和、平均值、计数、最大值、最小值等一系列功能，最重要的是，它在分类汇总中进行计算具有不可替代的作用。
首先看一下SUBTOTAL函数的语法解析，SUBTOTAL表示小计、求项目的部分之和的意思，简单地说，就是分类汇总。它的参数由两部分组成，如图2-27所示。
图2-27 SUBTOTAL函数语法
• 参数1：function_num是功能代码，它必须是1到11或者101到111之间的数字，每个数字对应不同的功能，1～11和101～111的用法也不相同。
• 参数2：ref1是计算区域，这个参数只能定义名称或者单元格引用，最多可以包含254个引用。
SUBTOTAL函数的第一个参数有两种类型，第一是使用1～11的代码，第二是使用101～111的代码，这两种类型中，每个数值对应的函数功能是一样的，区别在哪里呢？代码1到11包含隐藏值，而101到111忽略了隐藏值，如图2-28所示。
图2-28 SUBTOTAL函数两种功能代码的区别
包含隐藏值和忽略隐藏值到底有什么区别？下面通过案例体会两者之间的差异。
SUBTOTAL的第一个经典用法：只对可见单元格汇总。
什么叫只对可见单元格进行汇总？如图2-29所示，首先将数据区域的第5～14行进行隐藏，然后在C1单元格用SUM对产品销量进行求和，我们发现求出的结果明显不对，它把隐藏的数据也计算在内了，数据区域隐藏了第5～14行。
图2-29 隐藏单元格求和
使用SUBTOTAL函数可以只对可见单元格求和，在C1单元格输入公式=SUBTOTAL(109,C5:C16)，结果是56，刚好等于C15与C16单元格值之和。
下面再详细分析SUBTOTAL函数第一个参数包含隐藏值和忽略隐藏值的区别。我们知道单元格隐藏分为两种情况，一是隐藏行或列，二是对数据区域进行筛选。这两个操作在视觉上都能使单元格不可见，看起来都像是隐藏了。但这在Excel中表现出来的状态是不一样的，隐藏行或列，单元格行号不变色，筛选单元格行号会变为蓝色。
接下来做两个试验。
①先对数据区域进行筛选，如图2-30所示，筛选出“安老师”的销售数据，分别用代码9和109对销量进行求和，结果是一样的，因为数据区域中没有隐藏行。
图2-30 筛选数据
②再用隐藏操作，如图2-31所示，把除“安老师”外的所有行都隐藏，会发现代码9和109对销量求和的结果不一致，代码9把隐藏的数值也计算在内了。
图2-31 隐藏数据
基于此，我们能够总结SUM函数和SUBTOTAL函数在计算中对两种类型的不可见单元格处理的方式，得出的规律如图2-32所示。
图2-32 SUM函数与SUBTOTAL函数对不可见单元格的处理方式
对于SUBTOTAL函数，无论第一个参数的代码是9还是109，对于筛选而不见的数值，它们都不会计算在内，这就是它们和SUM最本质的区别。
SUBTOTAL的第二个经典用法：为隐藏行自动添加编号。
给数据行自动添加编号的方法之一是用COUNTA函数，比如，在A2单元格输入公式=COUNTA($B$2:B2)，将其向下复制填充，只要B列中填写了内容，就可以自动进行编号。然而，当我们隐藏或筛选某些行时，A列的序号却无法自动更新。
在这种情况下，就需要使用SUBTOTAL函数中的代码103。我们在A2单元格输入公式=SUBTOTAL (103,B$2:B2)，然后将此公式向下复制填充即可。无论怎么隐藏或筛选，A列的序号都将自动调整，如图2-33所示。
图2-33 SUBTOTAL函数添加编号
SUBTOTAL的第三个经典用法：智能求总计。
如果数据表中有“小计”，比如，如图2-34所示的一个数据表，分别对华北、华东、华南的销量进行小计，在这种情况下，如果使用SUM函数进行总计，数值就会计算两遍，在C15单元格中使用的是SUM函数，而D15单元格中使用SUBTOTAL函数，则可自动忽略计算中分类汇总的内容，确保计算不会出错。
图2-34 SUM与SUBTOTAL函数求汇总的区别
在这个案例中，其实就是Excel中的分类汇总功能，我们来看一下，使用Excel自带的分类汇总功能，最终的小计行和汇总行用的也是SUBTOTAL函数，如图2-35所示。
图2-35 分类汇总与SUBTOTAL函数
SUBTOTAL虽然比SUM、COUNT等的曝光率低，但它的用途却非常广，大家一定要熟练掌握它。