可惜99.9%的人没用过! Excel中万能统计函数AGGREGATE使用指南

Excel中有一个函数它结合了我们日常用的各类求和、计数、平均、最大最小值、排序等各种用法于一身，它就是Aggregate多功能函数。这个函数相信99.9%人都没见过。

微软Office LTSC 2021专业增强版 简体中文批量许可版 2024年09月更新

• 类型：办公软件
• 大小：2.2GB
• 语言：简体中文
• 时间：2024-09-12

查看详情

AGGREGATE函数在Excel中是一个多功能函数，它将我们日常涉及的求和、计数、平均、求大小值等多种用法于一身。一个函数就可以实现多种功能。函数的语法结构主要有以下2种：

• 语法1：AGGREGATE(function_num, options, ref1, [ref2], …)
• 语法2：AGGREGATE(function_num, options, array, [k])

函数解析：

1、第一参数：function_num：它主要为1-19个数字构成，每个数字代表对应实现的功能，如1代表Average，求出平均值。1-19每个数字代表的意义如下图所示：

2、第二参数：Options ：它分别也是由数字1-7组成，每个数字同样代表不同的意思。如下图所示：

3、第三参数：Ref1/array，代表的是对应的内容为对应的数组或者是一个数组公式，同时它也可以是我们需要进行数据处理的单元格区域的引用。如下图所示：

讲到上面的函数解析，相信大家对这个函数还不是特别熟悉，下面我们通过两个实际案例来看一下，这个函数怎么进行运用。

测试一下它的正确性，求和、算平均值正确：

你是不是有疑问，既然我已会了AVERAGE、SUM等函数，还有必要学习AGGREGATE函数吗？

当然有必要！‘AGGREGATE函数有3个很有用的功能：

如果表中有错误值，AVERAGE和SUM函数就会出错，而AGGREGATE函数则可以选择性的忽略错误值。

测试：当有错误值时

=AGGREGATE(1,6,B2:B6) 正确

=AVERAGE(B2:B6) 错误

常用的SUM等函数是无法忽略隐藏行的，而AGGREGATE可以

测试：当表中的行隐藏时：

=AGGREGATE(1,5,B2:B6) 可以忽略隐藏行求平均值

=AVERAGE(B2:B6) 不可以…..

这个函数常用于筛选时让序号连续时：

A2单元格公式

=AGGREGATE(3,5,B$1:B1)

看了AGGREGATE的功能是不是感觉很眼熟?是的，Excel还有另一个函数可以忽略隐藏行：Substotal函数。无论是支持的统计函数个数，还是忽略错误值，它都不如前者。

函数公式：

=AGGREGATE(1,0,D4:D11)

函数解析：

1、我们可以看出这个函数主要有3个参数：第一参数1代表求平均值；第二参数0代表忽略是否有数据筛选或者是忽略任何操作；第三参数D4:D11为需要计算的数据区域。

2、从函数功能上来看，我们可以得出这个函数等同于AVERAGE(D4:D11)。

函数公式：

=AGGREGATE(9,0,D4:D11)

函数解析：

1、函数公式中的第1参数9，代表的是求和；第2参数0，代表的是忽略是否有数据筛选或者是忽略任何操作；第3参数D4:D11，代表的是我们需要计算的数据区域。

2、从函数结果来看，我们可以看到求和的时候，这个函数就等同于SUM(D4:D11)。

推荐阅读：excel中vlookup函数无法识别字母的大小写返回错误值怎么办?