2024年5月4日发(作者：)

Excel高级函数使用SUBTOTAL和

AGGREGATE进行数据分组和聚合

在Excel中，数据的分组和聚合是非常常见的操作。在处理大量数

据时，我们经常需要对数据进行分类和汇总，以便更好地理解数据的

特征和趋势。Excel提供了许多高级函数来实现这些操作，其中包括

SUBTOTAL和AGGREGATE函数。

一、SUBTOTAL函数

SUBTOTAL函数是一个非常实用的函数，它可以对指定的数据范

围进行分组并进行不同的计算。SUBTOTAL函数的语法为：

=SUBTOTAL(function_num, range1, range2, ...)

其中，function_num是用于指定要执行的计算方式的数字，range1、

range2等是要进行计算的数据范围。

SUBTOTAL函数支持多达11个不同的计算方式，比如求和、平均

值、最大值、最小值等。具体的计算方式如下：

1. AVERAGE：计算范围内数值的平均值。

2. COUNT：计算范围内的数值个数。

3. COUNTA：计算范围内的非空单元格个数。

4. MAX：计算范围内数值的最大值。

5. MIN：计算范围内数值的最小值。

6. PRODUCT：计算范围内数值的乘积。

7. STDEV.S：计算范围内数值的标准偏差（总体）。

8. STDEV.P：计算范围内数值的标准偏差（样本）。

9. SUM：计算范围内数值的和。

10. VAR.S：计算范围内数值的方差（总体）。

11. VAR.P：计算范围内数值的方差（样本）。

下面是一个使用SUBTOTAL函数的例子：

假设我们有一个销售数据表格，包含了不同地区的销售额数据。我

们想要对每个地区的销售额进行分组，并计算每个地区的销售额总和。

首先，在一个新的单元格中输入SUBTOTAL函数的公式：

=SUBTOTAL(9, B2:B10)

这里的9代表SUM函数，B2:B10是要进行计算的数据范围。按下

Enter键后，我们会得到地区销售额的总和。

接下来，我们可以将这个公式拖拽应用到其他地区的数据范围，以

快速计算每个地区的销售额总和。

二、AGGREGATE函数

AGGREGATE函数是Excel 2010及以上版本中新增的一个函数，它

的作用与SUBTOTAL类似，但提供了更多的计算方式和选项，使用更

加灵活。AGGREGATE函数的语法为：

=AGGREGATE(function_num, options, range1, range2, ...)

其中，function_num是用于指定要执行的计算方式的数字，options

是用来控制计算的选项，range1、range2等是要进行计算的数据范围。

AGGREGATE函数支持多达19个不同的计算方式，比SUBTOTAL

函数更加强大。具体的计算方式如下：

1. AVERAGE：计算范围内数值的平均值。

2. COUNT：计算范围内的数值个数。

3. COUNTA：计算范围内的非空单元格个数。

4. MAX：计算范围内数值的最大值。

5. MIN：计算范围内数值的最小值。

6. PRODUCT：计算范围内数值的乘积。

7. STDEV.S：计算范围内数值的标准偏差（总体）。

8. STDEV.P：计算范围内数值的标准偏差（样本）。

9. SUM：计算范围内数值的和。

10. VAR.S：计算范围内数值的方差（总体）。

11. VAR.P：计算范围内数值的方差（样本）。

12. MEDIAN：计算范围内数值的中位数。

13. LARGE：返回指定顺序的数值。

14. SMALL：返回指定顺序的数值。

15. ：计算范围内数值的百分位数。

16. ：计算范围内数值的排除百分位数。

17. ：计算范围内数值的四分位数。

18. ：计算范围内数值的排除四分位数。

19. ：计算范围内数值的众数。

下面是一个使用AGGREGATE函数的例子：

假设我们有一个学生成绩表格，包含了不同科目的学生成绩数据。

我们想要计算每个科目的平均分和中位数。

首先，在一个新的单元格中输入AGGREGATE函数的公式：

=AGGREGATE(1, 1, B2:B10)

这里的1代表AVERAGE函数，1代表忽略错误值。B2:B10是要进

行计算的数据范围。按下Enter键后，我们会得到科目平均分。

接下来，我们可以将这个公式拖拽应用到其他科目的数据范围，以

快速计算每个科目的平均分。

类似地，我们可以使用其他的AGGREGATE函数来计算每个科目

的中位数、最大值、最小值等。

总结：

使用Excel的高级函数SUBTOTAL和AGGREGATE可以方便地对

数据进行分组和聚合操作。无论是对销售数据、成绩数据还是其他类

型的数据，这些函数都能够帮助我们更好地理解数据的特征和趋势。

熟练掌握这些函数的使用方法，对于数据分析和报告生成都非常重要。

通过使用SUBTOTAL和AGGREGATE函数，我们可以在Excel中高

效地进行数据处理和统计分析。