2.3　统计分析：成为数据分析师的第一步

Excel强大的数据处理能力最重要的体现之一在于它的数学计算和数据统计方面。工作中需要用到计算的情况非常多，例如，汇总成绩、计算工时、费用花销、统计人数、统计频次、排序与排名等。

2.3.1　函数界的瑞士军刀SUBTOTAL

为什么说SUBTOTAL是函数界的瑞士军刀？因为它可以完成求和、平均值、计数、最大值、最小值等一系列功能，最重要的是，它在分类汇总中进行计算具有不可替代的作用。

首先看一下SUBTOTAL函数的语法解析，SUBTOTAL表示小计、求项目的部分之和的意思，简单地说，就是分类汇总。它的参数由两部分组成，如图2-27所示。

· 参数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所示。

包含隐藏值和忽略隐藏值到底有什么区别？下面通过案例体会两者之间的差异。

SUBTOTAL的第一个经典用法：只对可见单元格汇总。

什么叫只对可见单元格进行汇总？如图2-29所示，首先将数据区域的第5～14行进行隐藏，然后在C1单元格用SUM对产品销量进行求和，我们发现求出的结果明显不对，它把隐藏的数据也计算在内了，数据区域隐藏了第5～14行。

使用SUBTOTAL函数可以只对可见单元格求和，在C1单元格输入公式=SUBTOTAL(109,C5:C16)，结果是56，刚好等于C15与C16单元格值之和。

下面再详细分析SUBTOTAL函数第一个参数包含隐藏值和忽略隐藏值的区别。我们知道单元格隐藏分为两种情况，一是隐藏行或列，二是对数据区域进行筛选。这两个操作在视觉上都能使单元格不可见，看起来都像是隐藏了。但这在Excel中表现出来的状态是不一样的，隐藏行或列，单元格行号不变色，筛选单元格行号会变为蓝色。

接下来做两个试验。

①先对数据区域进行筛选，如图2-30所示，筛选出“安老师”的销售数据，分别用代码9和109对销量进行求和，结果是一样的，因为数据区域中没有隐藏行。

②再用隐藏操作，如图2-31所示，把除“安老师”外的所有行都隐藏，会发现代码9和109对销量求和的结果不一致，代码9把隐藏的数值也计算在内了。

基于此，我们能够总结SUM函数和SUBTOTAL函数在计算中对两种类型的不可见单元格处理的方式，得出的规律如图2-32所示。

对于SUBTOTAL函数，无论第一个参数的代码是9还是109，对于筛选而不见的数值，它们都不会计算在内，这就是它们和SUM最本质的区别。

SUBTOTAL的第二个经典用法：为隐藏行自动添加编号。

给数据行自动添加编号的方法之一是用COUNTA函数，比如，在A2单元格输入公式=COUNTA($B$2:B2)，将其向下复制填充，只要B列中填写了内容，就可以自动进行编号。然而，当我们隐藏或筛选某些行时，A列的序号却无法自动更新。

在这种情况下，就需要使用SUBTOTAL函数中的代码103。我们在A2单元格输入公式=SUBTOTAL (103,B$2:B2)，然后将此公式向下复制填充即可。无论怎么隐藏或筛选，A列的序号都将自动调整，如图2-33所示。

SUBTOTAL的第三个经典用法：智能求总计。

如果数据表中有“小计”，比如，如图2-34所示的一个数据表，分别对华北、华东、华南的销量进行小计，在这种情况下，如果使用SUM函数进行总计，数值就会计算两遍，在C15单元格中使用的是SUM函数，而D15单元格中使用SUBTOTAL函数，则可自动忽略计算中分类汇总的内容，确保计算不会出错。

在这个案例中，其实就是Excel中的分类汇总功能，我们来看一下，使用Excel自带的分类汇总功能，最终的小计行和汇总行用的也是SUBTOTAL函数，如图2-35所示。

SUBTOTAL虽然比SUM、COUNT等的曝光率低，但它的用途却非常广，大家一定要熟练掌握它。 mR0ICsmYlYr8g0rLchT1brbp9JXfbrzPH/fvDH6KwIHeSkfiGLS4X3wcbqA0o5Ij