夜雨聆风
它是Excel中的万能统计函数,能自动忽略错误和隐藏行,可惜 99.9% 的人不会用…
想象一下这个场景：
-
你想用
AVERAGE计算平均值，但数据里有隐藏行或被筛选掉的行，你希望忽略它们，但AVERAGE做不到。 -
你需要一个函数，既能求和、求平均，又能忽略错误、忽略隐藏行，甚至能忽略特定类型的嵌套函数？
如果你在频繁处理带有不完整、不规范的数据源，那么 AGGREGATE函数就是你工具箱里不可或缺的“清洁工”和“多面手”。它能让你在处理复杂数据时，依然保持公式的简洁和健壮。
错误做法 vs 优雅解法
在深入 AGGREGATE之前，我们先看几个常见的、令人头大的“笨办法”：
错误做法1：嵌套 IFERROR公式求总销量
=SUM(IFERROR(C2:C13), 0)) '公式错误错误做法2：先用筛选或替换手动删除错误值
手动操作不仅耗时，而且当源数据更新时，一切又得重来，完全不具备“自动化”思维。
传统笨方法：
=SUMIFS(C2:C13,C2:C13,">0")也可能有人会用到SUBTOTAL函数：
=SUBTOTAL(109,C2:C13) '返回 #N/A，错误值无法忽略但是，Subtotal函数只能忽略隐藏的行，无法忽略错误值，最终返回错误值#N/A
优雅解法：AGGREGATE函数自带忽略模式：
=AGGREGATE(9,7,C2:C13)这个简单的公式完成了三件事：
-
计算区域
C2:C13的总和（由第一个参数9控制）。 -
忽略区域中的任何错误值（由第二个参数
7控制）。 -
忽略任何隐藏的行（也由第二个参数
7控制）。
核心拆解：AGGREGATE(功能, 选项, 数据, [k])
AGGREGATE 函数的强大，来自于其精妙的设计。它像一个函数“工厂”，通过组合不同的功能代码和选项代码，定制出你需要的特定计算“机器”。
函数语法很简单：
=AGGREGATE(功能代码, 忽略选项, 引用区域, [k可选参数])1. 第一个参数：你想做什么？（功能代码）
这是AGGREGATE的核心能力，它内置了19种最常见的统计和查找计算。我们先看几个最常用的：
小编说：记住 1、2、4、5、9 这几个最常用的代码，就能解决 80% 的问题。
2. 第二个参数：你想忽略什么？（选项代码）
这是 AGGREGATE的灵魂所在，让你决定“计算时忽略哪些数据”。
避坑提醒：选项 3 和 7 是孪生兄弟，都忽略“错误值+隐藏行”，
通常，当我们用
A1:A10这种形式时，用 7 就对了。选项 3 和 5 也是如此。
实例：手把手解决3个高频难题
案例1：求销售总额，但数据中有错误和筛选
-
场景：你的销量列（C列）中，有些单元格是
#N/A（表示“未统计”），并且你已经筛选掉了“退货”行。现在，你需要计算实际有效销售的总和。 -
公式：
=AGGREGATE(9, 7, C2:C100) -
拆解：
-
9：代表“求和”功能。 -
7：代表“忽略错误值和隐藏行”。 -
C2:C100：计算范围。 -
结果：这个公式会自动跳过所有的
#N/A错误，并且不把被筛选隐藏的行计入求和，直接得到你想要的、干净的总和。无论你如何改变筛选条件，总和都会自动更新。
案例2：在一堆数字中找到“第二大的有效值”
-
场景：你的数据（A列）里有负数、错误值，甚至还有文本，你需要找到其中“最大的正数”。如果最大的正数是第一名，那“第二名”的正数是谁？这时就需要代码 **14 (LARGE)** 和 15 (SMALL)。
-
公式：
=AGGREGATE(14, 6, A1:A20, 2) -
拆解：
-
14：代表“查找第K大值”（相当于LARGE函数）。 -
6：忽略错误值。 -
A1:A20：数据范围。 -
2：K值，表示“第2大”。 -
结果：这个公式会先自动忽略所有错误值，然后在整个数值区域中找出排名第2的数字。这比先用
IFERROR清洗再计算要优雅得多。
案例3：忽略“小计”行，计算部门平均绩效
-
场景：你的表格里，每个部门下面有一行“小计”（用
SUBTOTAL公式计算），你想计算整个公司（忽略所有小计行）的平均绩效。如果直接用AVERAGE，会把“小计”行的值也计算进去，导致结果错误。 -
公式：
=AGGREGATE(1, 0, C2:C100) -
拆解：
-
1：代表“平均值”功能。 -
1：代表“忽略嵌套函数（SUBTOTAL/AGGREGATE）和隐藏行”。 -
C2:C100：数据范围，其中包含了用SUBTOTAL计算的小计行。 -
结果：公式会自动识别并忽略由
SUBTOTAL或AGGREGATE计算出的单元格，只对其他“原始数据”计算平均值，完美解决了“合计中包含小计”的双重计算问题。
案例4：生成连续的序号
=AGGREGATE(3,7,C$2:C2)解析：参数3表示计数，参数7忽略隐藏行
延伸案例4：计算“销售冠军”的业绩，排除指定值
-
如果数据中有0（可能表示无业绩），你只想在大于10000的业绩中找最大值：
=AGGREGATE(14, 7, B2:B100/(B2:B100>100000), 1)注意：这是数组公式的思路。
B2:B100>0会产生一个由TRUE/FALSE组成的数组，除以它，FALSE（=0）会使得原值变成#DIV/0!错误，然后被选项 7 忽略，从而实现“条件过滤”。这是AGGREGATE配合数组运算的高级技巧。
下次当你写公式时，先问问自己：
-
我的数据里可能有错误值吗？ —— 用 AGGREGATE
-
我需要忽略筛选或隐藏的行吗？—— 用 AGGREGATE
-
我想用一个公式做复杂的条件查找吗？—— 尝试用 AGGREGATE的 14/15 代码配合数组。
别再为错误值和隐藏行烦恼了。收藏这篇，下次再遇到，就用 AGGREGATE函数轻松解决！