AGGREGATE函数用法详解—6个典型用法

AGGREGATE函数用于返回列表或数据库中的分类汇总，提供忽略隐藏行和错误值的选项。

AGGREGATE函数与SUBTOTAL函数的功能类似，但功能更为强大，可以看作是SUBTOTAL函数的增强版本。

SUBTOTAL函数的用法：SUBTOTAL函数用法详解—6个典型用法

AGGREGATE函数的语法为

AGGREGATE(function_num,options,ref1,[ref2],…)

参数function_num是一个介于1到19之间的数字，用于指定要为分类汇总使用的函数。各数字代表的函数如下图所示：

参数options用于决定在函数的计算区域内要忽略哪些值。不同取值代表的含义如下表所示：

本文主要以使用AGGREGATE函数求和（即function_num为9）、求最大值（即function_num为14）、求最小值（即function_num为15）为例，讲解AGGREGATE函数的用法。

一、忽略错误值求和

如下图所示，A1:B8为各业务员销售额，其中单元格B5、B7的数据为错误值。要求将错误值视为0，计算各业务员销售额合计。

如果在D2单元格直接输入公式“=SUM(B2:B8)”会得到错误值，因为SUM函数无法忽略错误值求和。

在D2单元格输入公式：=AGGREGATE(9,6,$B$2:$B$8)

参数“９”代表SUM函数，参数“６”表示忽略B2:B8中的错误值。

二、忽略错误值和隐藏行求和

如下图所示，在单元格E2输入公式：=AGGREGATE(9,7,$C$2:$C$8)

AGGREGATE函数第二个参数“7”代表“忽略隐藏行和错误值”。
当没有筛选数据时，AGGREGATE函数返回值为“150”。

当筛选出“销售1部”的数据时，AGGREGATE函数返回值为“100”。

三、忽略错误值求最大值

如下图所示，要求找到最大的销售额。在E2单元格输入以下公式：

=AGGREGATE(14,6,$C$2:$C$8,1)

其中，第一个参数值“14”代表LARGE函数；第二个参数值“6”代表忽略错误值；第四个参数值“1”代表获取C2:C8的第1个最大值。

四、忽略错误值，根据条件求最大值

如下图所示，要求找到“销售2部”的最高销售额。在F2单元格输入公式：

=AGGREGATE(14,6,$C$2:$C$8/($B$2:$B$8=E2),1)

本例中使用公式“$C$2:$C$8/($B$2:$B$8=E2)”构造AGGREGATE函数的参数ref1。$C$2:$C$8/($B$2:$B$8=E2)返回结果为

{#DIV/0!;20;#DIV/0!;30;#DIV/0!;#NAME?;#DIV/0!}。

五、一对多查询

如下图所示，A1:B8为各部门员工姓名表。要求提取“设计部”的所有员工姓名。在E2单元格输入以下公式：

=IFERROR(INDEX($B$2:$B$8,AGGREGATE(15,6,ROW($A$2:$A$8)/($A$2:$A$8=$D$2),ROW(A1))-1),"")

拖动填充柄向下复制公式，直到公式返回空值。

公式解析：

（1）使用ROW($A$2:$A$8)/($A$2:$A$8=$D$2)作为AGGREGATE函数的ref1参数，如A2:A8为“设计部”则返回行号，否则返回错误值。返回结果为{2;#DIV/0!;#DIV/0!;#DIV/0!;6;#DIV/0!;8}

（2）AGGREGATE函数的第四个参数ROW(A1)，A1为相对引用，随着公式向下复制，依次返回第1个、第2个…最大值。

（3）AGGREGATE函数返回的是A2:A8中“设计部”所在的行号，需要将返回的行号减1，这样才是“设计部”在A2:B8的行号。例如A2单元格的“设计部”在第2行，但相对于A2:B8区域，为第1行。

六、文本和数字混合，提取最大的数值

如下图所示，A2:A4为各班级学生成绩，姓名和成绩在一个单元格内。要求提取每个单元格内最大的数值。在B2单元格输入公式：

=AGGREGATE(14,6,--MID(A2,ROW($1:$26),COLUMN($A:$Z)),1)

公式解析：

（1）本例使用--MID(A2,ROW($1:$26),COLUMN($A:$Z))作为AGGREGATE函数的ref1参数。MID函数表示从A2单元格的第1个字符开始，分别取1个、2个、3个…26个字符；再从第2个字符开始，分别取1个、2个、3个…26个字符。以此类推，一直到第26个字符。

（2）MID函数前加双负号（“--”）可以将MID函数提取出的文本转换为错误值。