Excel函数学习41：AGGREGATE函数

L罗乐 2018-01-11

展开全文

AGGREGATE函数返回列表或数据库中的合计。AGGREGATE函数消除了条件格式的限制，如果区域中存在错误，则数据条、图标集和色阶将无法显示条件格式。这是因为当计算区域存在错误时，MIN、MAX和PERCENTILE函数不进行计算。同样，LARGE、SMALL和STDEVP函数也会影响某些条件格式规则的相应功能。通过使用AGGREGATE函数，将忽略这些错误。AGGREGATE函数可以将不同的聚合函数应用于列表或数据库，并提供忽略隐藏行和错误值的选项。

图1

什么情况下使用AGGREGATE函数?

AGGREGATE函数是在Excel 2010中引入的一个非常强大的函数，可以对列表或数据库应用不同的聚合函数并忽略隐藏行和错误值。它能够：

计算最大值、最小值、中值等
（不用数组公式）计算满足多条件的数据最大值
（不用数组公式）提取满足多个条件的数据

AGGREGATE函数语法

AGGREGATE函数有两种形式，一种是引用形式，一种是数组形式。其引用形式语法如下：

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

其数组形式语法如下：

AGGREGATE(function_num,options,array,[k])

function_num: 必需。一个在1至19之间的数字，指定要使用的函数，其中：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为MODE.SNGL函数，14为LARGE函数，15为SMALL函数，16为PERCENTILE.INC函数，17为QUARTILE.INC函数，18为PERCENTILE.EXC函数，19为QUARTILE.EXC函数。
options: 必需。一个数值，确定在函数的计算区域内要忽略的值。其中：0或者省略，忽略嵌套SUBTOTAL和AGGREGATE函数；1指定忽略隐藏行、嵌套SUBTOTAL和AGGREGATE函数；2指定忽略错误值、嵌套SUBTOTAL和AGGREGATE函数；3指定忽略隐藏行、错误值、嵌套SUBTOTAL和AGGREGATE函数；4指定忽略空值；5指定忽略隐藏行；6指定忽略错误值；7指定忽略隐藏行和错误值。
ref1:必需。函数的第一个数值参数。对于使用数组的函数，可以是一个数组或者数组公式，也可以是对要为其计算聚合值的单元格区域的引用。
ref2: 对于某些函数必需，例如LARGE(array,k)、SMALL(array,k)、PERCENTILE.INC(array,k)、QUARTILE.INC(array,quart)、PERCENTILE.EXC(array,k)、QUARTILE.EXC(array,quart)。

AGGREGATE函数陷阱

如果第二个引用参数是必需的但未提供，那么AGGREGATE将返回错误值#VALUE!。如果有一个或多个引用是三维引用，那么AGGREGATE将返回错误值#VALUE!。如果在Excel 2007或之前的版本打开AGGREGATE工作簿将返回#NAME?。

如果ref1,ref2,…中有其他AGGREGATE（或嵌套AGGREGATE），将忽略这些嵌套AGGREGATE，避免重复计算。如果AGGREGATE函数的引用中包含SUBTOTAL，那么将忽略这些SUBTOTAL。如果SUBTOTAL函数中包含AGGREGATE，那么将忽略这些AGGREGATE。