AGGREGATE 函数
适用范围
Microsoft 365 专属 Excel, Microsoft 365 Mac 版专属 Excel, Excel 网页版, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016, Excel Web App
1. 函数简介
AGGREGATE 函数用于返回列表或数据库中的合计值。它可以将不同的聚合函数（如 AVERAGE、SUM、MAX 等）应用到列表或数据库，并提供忽略隐藏行、错误值的选项。
2. 语法
AGGREGATE 函数有引用形式和数组形式两种语法：
' 引用形式
AGGREGATE(function_num, options, ref1, [ref2], …)
' 数组形式
AGGREGATE(function_num, options, array, [k])| 参数 | 选填 | 说明 |
|---|---|---|
| function_num | 必需 | 1 到 19 的数字，指定要使用的聚合函数（详见下表）。 |
| options | 必需 | 0 到 7 的数字，指定计算时要忽略的内容（详见下表）。 |
| ref1 | 必需 | 要对其执行聚合计算的第一个数值参数（引用形式），或要执行计算的数组（数组形式）。 |
| ref2, ... | 可选 | 要对其执行聚合计算的第 2 至第 253 个数值参数；array 形式下为 k，表示 LARGE、SMALL、PERCENTILE.INC、QUARTILE.INC、PERCENTILE.EXC、QUARTILE.EXC 等函数所需的第 k 个参数。 |
function_num 取值对照表：
| 编号 | 函数 | 编号 | 函数 |
|---|---|---|---|
| 1 | AVERAGE | 11 | VAR.P |
| 2 | COUNT | 12 | MEDIAN |
| 3 | COUNTA | 13 | MODE.SNGL |
| 4 | MAX | 14 | LARGE |
| 5 | MIN | 15 | SMALL |
| 6 | PRODUCT | 16 | PERCENTILE.INC |
| 7 | STDEV.S | 17 | QUARTILE.INC |
| 8 | STDEV.P | 18 | PERCENTILE.EXC |
| 9 | SUM | 19 | QUARTILE.EXC |
| 10 | VAR.S |
options 取值对照表：
| 取值 | 行为 |
|---|---|
| 0 或省略 | 忽略嵌套的 SUBTOTAL 和 AGGREGATE 函数 |
| 1 | 忽略隐藏行、嵌套的 SUBTOTAL 和 AGGREGATE 函数 |
| 2 | 忽略错误值、嵌套的 SUBTOTAL 和 AGGREGATE 函数 |
| 3 | 忽略隐藏行、错误值、嵌套的 SUBTOTAL 和 AGGREGATE 函数 |
| 4 | 忽略空值 |
| 5 | 忽略隐藏行 |
| 6 | 忽略错误值 |
| 7 | 忽略隐藏行和错误值 |
3. 示例
设区域 A1:A11 中存有一组包含错误值 #NUM! 的数据（82, 72, 65, 30, 95, #NUM!, 63, 31, 53, 96, 71）。
3.1. 忽略错误值求最大值
=AGGREGATE(4, 6, A1:A11)计算 A1:A11 中的最大值，同时忽略区域中的错误值，结果为 96。
3.2. 忽略错误值求第 k 大的值
=AGGREGATE(14, 6, A1:A11, 3)计算 A1:A11 中第 3 大的值，同时忽略错误值，结果为 72。
3.3. 缺少必需的 k 参数
=AGGREGATE(15, 6, A1:A11)SMALL 对应的 function_num=15 需要提供第 k 个参数，缺少该参数会返回 #VALUE! 错误。
3.4. 跨多个区域计算中值
=AGGREGATE(12, 6, A1:A11, B1:B11)计算 A1:A11 与 B1:B11 合并后的中值，同时忽略错误值，结果为 68。
4. 实用技巧
4.1. AGGREGATE 主要面向列或垂直区域设计
AGGREGATE 主要面向列或垂直区域设计：隐藏的列不会影响水平区域的求和结果，但隐藏行会影响垂直区域的聚合结果，使用前需注意数据排列方向；
4.2. 注意错误条件
当 function_num 为 14–19（LARGE、SMALL、PERCENTILE 等）时，必须通过 ref2 / [k] 提供第 k 个参数，否则返回 #VALUE! 错误；
4.3. 如果数组参数中包含计算（例如 =AGGREGATE(14
如果数组参数中包含计算（例如 =AGGREGATE(14, 3, A1:A100*(A1:A100>0), 1)），AGGREGATE 不会忽略隐藏行、嵌套的分类汇总或聚合函数；
4.4. 三维引用作为参数会返回 #VALUE! 错误
三维引用作为参数会返回 #VALUE! 错误；
4.5. 相比 SUBTOTAL
相比 SUBTOTAL，AGGREGATE 提供了更多聚合函数可选（如 MEDIAN、LARGE、PERCENTILE.INC 等），并能同时忽略错误值，适合处理含异常数据的区域。