Excel AGGREGATE 函数
最后修改于 2025 年 4 月 4 日
AGGREGATE 函数是 Excel 中一个功能强大且用途广泛的函数，可以执行各种计算，同时忽略错误、隐藏行或其他特定值。本教程将通过详细示例全面介绍 AGGREGATE 函数的用法。您将学习其语法、函数编号、选项和实际应用，以精通此高级 Excel 函数。
AGGREGATE 函数基础
AGGREGATE 函数可以执行 19 种不同的操作，如 SUM、AVERAGE、MAX 等，并提供忽略错误、隐藏行或其他子总计的选项。它有两种形式：引用形式和数组形式。
| 组成部分 | 描述 |
|---|---|
| 函数名称 | AGGREGATE |
| 语法（引用形式） | =AGGREGATE(function_num, options, ref1, [ref2], ...) |
| 语法（数组形式） | =AGGREGATE(function_num, options, array, [k]) |
| 参数 | 3-253 项，取决于形式 |
| 返回值 | 指定聚合的结果 |
此表分解了 AGGREGATE 函数的基本组成部分。它显示了两种语法形式、参数限制和返回值特性。
函数编号和选项
AGGREGATE 函数使用数字代码来指定要执行的操作以及要忽略的值。以下是可用的函数编号和选项。
| 函数编号 | 操作 |
|---|---|
| 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 |
| 选项编号 | 行为 |
|---|---|
| 0 或省略 | 忽略嵌套的 SUBTOTAL 和 AGGREGATE 函数 |
| 1 | 忽略隐藏行、嵌套的 SUBTOTAL 和 AGGREGATE 函数 |
| 2 | 忽略错误值、嵌套的 SUBTOTAL 和 AGGREGATE 函数 |
| 3 | 忽略隐藏行、错误值、嵌套的 SUBTOTAL 和 AGGREGATE |
| 4 | 不忽略任何内容 |
| 5 | 忽略隐藏行 |
| 6 | 忽略错误值 |
| 7 | 忽略隐藏行和错误值 |
这些表格显示了 AGGREGATE 函数所有可用的操作和选项。函数编号决定了计算方式，而选项则控制计算过程中要忽略的内容。
基本 AGGREGATE 示例（SUM）
此示例演示了 AGGREGATE 最简单的用法，即在忽略错误的情况下对范围求和。
| A | B |
|---|---|
| 10 | |
| 20 | |
| #N/A | |
| 30 | |
| =AGGREGATE(9, 6, A1:A4) |
表格显示了一个包含数字和错误值的范围。B5 中的 AGGREGATE 函数在忽略错误 (#N/A) 的情况下对范围求和。
=AGGREGATE(9, 6, A1:A4)
此公式使用函数编号 9（SUM）和选项 6（忽略错误）。它对 A1 (10)、A2 (20) 和 A4 (30) 求和，忽略 A3 (#N/A)。结果是 60。这展示了 AGGREGATE 优雅处理错误的能力。
带隐藏行的 AGGREGATE
此示例展示了 AGGREGATE 如何在计算中忽略隐藏行。
| A | B |
|---|---|
| 100 | |
| 200 | |
| 300 | |
| 400 | |
| =AGGREGATE(9, 5, A1:A4) |
假设第 2 行被隐藏，此表展示了 AGGREGATE 在计算中排除隐藏行的能力。该函数将仅对可见单元格求和。
=AGGREGATE(9, 5, A1:A4)
此公式使用函数编号 9（SUM）和选项 5（忽略隐藏行）。如果第 2 行（A2=200）被隐藏，它将对 A1 (100)、A3 (300) 和 A4 (400) 求和，得到 800。这对于过滤后的数据或手动隐藏行的情况很有用。
带 LARGE 函数的 AGGREGATE
此示例演示了如何使用 AGGREGATE 在忽略错误和隐藏行的情况下查找第二大值。
| A | B |
|---|---|
| 500 | |
| #N/A | |
| 700 | |
| 600 | |
| =AGGREGATE(14, 7, A1:A4, 2) |
表格包含数字和一个错误值。AGGREGATE 函数在忽略错误和任何隐藏行（选项 7）的情况下查找第二大值。
=AGGREGATE(14, 7, A1:A4, 2)
这使用了函数编号 14（LARGE）和选项 7（忽略隐藏行和错误）。“2”指定了第二大值。它忽略 A2 (#N/A)，返回 600（在 700 之后的第二大值）。这显示了 AGGREGATE 的高级筛选功能。
带多个条件的 AGGREGATE
此示例展示了 AGGREGATE 如何在忽略错误和隐藏行的情况下执行条件平均。
| A | B | C |
|---|---|---|
| 北方 | 100 | |
| 南方 | #N/A | |
| 北方 | 200 | |
| 东区 | 300 | |
| 北方 | 400 | |
| =AGGREGATE(1, 7, (A1:A5="North")*B1:B5) |
此表演示了一个复杂的条件平均计算。该公式计算 A 列为“North”时 B 列值的平均值，同时忽略错误和隐藏行。
=AGGREGATE(1, 7, (A1:A5="North")*B1:B5)
此数组公式使用函数编号 1（AVERAGE）和选项 7。它将条件 (A1:A5="North") 与值 (B1:B5) 相乘。结果计算 B1 (100)、B3 (200) 和 B5 (400) 的平均值，忽略 B2 (#N/A)。平均值为 233.33。
带 PERCENTILE 的 AGGREGATE
此示例演示了如何使用 AGGREGATE 在忽略错误的情况下计算第 90 百分位数。
| A | B |
|---|---|
| 10 | |
| 20 | |
| #N/A | |
| 30 | |
| 40 | |
| 50 | |
| =AGGREGATE(16, 6, A1:A6, 0.9) |
表格显示了一个包含错误值的数据集。AGGREGATE 函数在忽略 A3 中的错误 (#N/A) 的情况下计算第 90 百分位数。
=AGGREGATE(16, 6, A1:A6, 0.9)
这使用了函数编号 16（PERCENTILE.INC）和选项 6（忽略错误）。0.9 指定了第 90 百分位数。它从值 10、20、30、40、50 计算，忽略 #N/A。结果是 46（剩余值的第 90 百分位数）。
AGGREGATE 函数是一个高级工具，它结合了多个 Excel 函数和强大的筛选选项。它能够忽略错误、隐藏行和其他特定值，使其在处理实际数据时非常宝贵。精通 AGGREGATE 将显著提升您在 Excel 中的数据分析能力。
作者
列出 所有 Excel 公式。