分类统计函数Subtotal和Aggregate应用技巧解读！

       在Excel中，经常要对数据进行分类统计，如果用普通的函数、公式去完成不仅费时费力，而且部分功能根本无法实现，今天，小编给大家介绍两个分类统计的万能函数：Subtotal和Aggregate。

一、Subtotal函数。

（一）功能及语法结构。

功能：返回一个数据列表或数据库的分类汇总。

语法结构：=Subtotal(功能代码,数据区域)。

其中功能代码分为2大类，如下图：

其中1-11包含隐藏值，101-111不包含隐藏值，只对可见单元格有效。

注意事项：

1、【汇总方式】必须为数值类型或可以转换为数值的类型，否则返回错误值“#VALUE!”。

2、Subtotal函数对隐藏的列区域无效。

3、数据区域只支持二位引用，不支持三维引用，否则返回错误值“#VALUE!”。

（二）应用技巧。

1、对隐藏后的数据求和，明确代码作用。

方法：

在目标单元格中输入：=SUBTOTAL(9,D3:D9)和=SUBTOTAL(109,D3:D9)。

解读：

1、未隐藏行数据之前，Sum、代码为9和109时的结果都相同。

2、隐藏行数据之后，Sum、代码为9的结果不变，而代码为109的结果发生了变化，为当前“可见”单元格区域的和值。

2、对筛选后的数据求平均值，明确代码作用。

方法：

在目标单元格中输入：=SUBTOTAL(1,D3:D9)和=SUBTOTAL(101,D3:D9)。

解读：

1、未筛选数据之前，Average、代码为1和101时的结果都相同。

2、筛选行数据之后，Average的结果不变，代码为1和101的结果发生了变化，为当前“可见”单元格区域的平均值。

3、Subtotal经典应用技巧——保持序号（No）的连续性。

目的解析：保持序号（No）的连续性就是在隐藏、删除或筛选数据行之后，序号自动以自然数的方式填充。

思路：对隐藏、删除、筛选后的序号以自然数的方式填充，其实就是对可见单元格计数，所以用代码103即可。

方法：

在目标单元格中输入公式：=SUBTOTAL(103,B$2:B2)。

解读：

代码“103”表示对可见非空单元格计数，而且参数为当前单元格的“右上角”开始统计。

4、Subtotal函数小结。

Subtotal函数的分类功能主要体现在“功能代码”上，而代码1-11对隐藏的数据无效，101-111对“可见”的数据有效。具体请参阅下图：

但在实际的应用中，代码101-111的应用价值更高，更为实用！

二、Aggregate。

（一）功能及语法结构。

功能：返回一个数据列表或数据库的分类合计。

语法结构：=Aggregate(功能代码,忽略代码,数据区域)

其中功能代码如下图：

忽略代码如下图：

（二）应用技巧。

1、对隐藏后的数据求和。

方法：

在目标单元格中输入公式：=AGGREGATE(9,1,D3:D9)、=AGGREGATE(9,3,D3:D9)、=AGGREGATE(9,5,D3:D9)、=AGGREGATE(9,7,D3:D9)。

解读：

功能代码9对应的函数为Sum，即求和。忽略代码1、3、5、7的功能中均有“忽略隐藏值”，也就是对“可见”单元格有效。所以在隐藏行数据后，其结果发生了变化。

2、忽略隐藏行及错误值汇总。

方法：

在目标单元格中输入公式：=AGGREGATE(9,3,D3:D9)、=AGGREGATE(9,7,D3:D9)。

解读：

1、在用Sum和Subtotal求和时，因为有错误值#N/A ，所以无法返回正确的结果。

2、忽略代码3、7不仅忽略错误值，还可以忽略隐藏的数据行。可以对当前的指定区域D3:D9进行求和运算。

3、筛选数据求和。

方法：

在目标单元格中输入公式：=AGGREGATE(9,1,D3:D9)、=AGGREGATE(9,3,D3:D9)、=AGGREGATE(9,5,D3:D9)、=AGGREGATE(9,7,D3:D9)。

4、忽略错误值筛选求和。

方法：

在目标单元格中输入公式：=AGGREGATE(9,3,D3:D9)、=AGGREGATE(9,7,D3:D9)。