aggregate函数特别适合处理这类表格：你要做汇总，同时还得自动跳过隐藏行、错误值，只算筛选后的可见结果。本次操作环境是microsoft 365版本的excel，日常最常用的写法很简单，先选对应统计功能的编号，再选要忽略的内容规则就行，比如输入 =aggregate(9,3,c2:c11)，就能自动求和，同时跳过隐藏行和所有错误值。
AGGREGATE一共支持两种语法：普通的求和、求平均值、计数这类常规计算，直接用引用形式就够；要算第几大、第几小、百分位这种需要指定位置的场景，才需要在最后多补一个 k 参数。
| 语法形式 | 写法 | 适合场景 |
|---|---|---|
| 引用形式 | =AGGREGATE(function_num,options,ref1,[ref2],...) |
求和、平均值、计数、最大值、最小值等常规汇总 |
| 数组形式 | =AGGREGATE(function_num,options,array,[k]) |
第几大、第几小、百分位、四分位等需要序号的计算 |
| 参数 | 作用 | 常用写法 |
|---|---|---|
function_num |
指定要执行的统计类型 |
9 求和、1 平均值、2 计数、4 最大值、5 最小值、14 第 k 大、15 第 k 小 |
options |
指定忽略哪些内容 |
3 常用于忽略隐藏行、错误值和嵌套汇总；6 忽略错误值；7 同时忽略隐藏行和错误值 |
ref1/ref2 |
参与统计的区域或引用 |
C2:C11、C2:C11,E2:E11
|
array |
数组形式里的计算区域 | B2:B10 |
k |
第几个结果，只在部分编号里使用 |
1 表示最大或最小的第 1 个，3 表示第 3 个 |
| options | 忽略规则 |
|---|---|
0 |
忽略嵌套的 SUBTOTAL 和 AGGREGATE |
1 |
忽略隐藏行、嵌套汇总 |
2 |
忽略错误值、嵌套汇总 |
3 |
忽略隐藏行、错误值、嵌套汇总，日常做表最顺手 |
4 |
不忽略任何内容 |
5 |
只忽略隐藏行 |
6 |
只忽略错误值 |
7 |
忽略隐藏行和错误值 |
第一步：准备要汇总的数据区域
先把要统计的列整理成连续的区域，旁边空出一个单元格用来放结果。示例里左侧是原始销售记录，右侧空出来的单元格就是用来输AGGREGATE公式的；如果你的表格本身已经加了筛选、手动藏了部分行，或者区域里混着错误值，后面只要改第二个参数options就行，完全不用额外加辅助列。
第二步：输入求和公式
选中提前留好的结果单元格，输入 =AGGREGATE(9,3,C2:C11) 就行。这里 9 对应SUM求和功能，3 代表自动忽略隐藏行、错误值和嵌套的其他汇总公式，C2:C11 就是你要统计的销售额区域。输完按回车，单元格直接就能返回当前所有有效数据的合计值。
第三步：把统计类型切换成计数或平均值
同一个区域想算订单数量，把第一个参数改成 2：=AGGREGATE(2,3,C2:C11)；想算平均值，把第一个参数改成 1：=AGGREGATE(1,3,C2:C11)。这个函数用起来特别省心，统计规则不用改，只要替换 function_num 就行。
第四步：处理隐藏行和错误值
要是你的表格里混着 #DIV/0!、#N/A 这类错误值，还有不少手动隐藏的行，直接把第二个参数改成 7，公式写成 =AGGREGATE(9,7,B2:B11)。它求和的时候会自动避开所有隐藏行和错误值，不会像普通SUM函数那样直接整行报错，特别适合做完临时筛选的报表统计。
第五步：用 k 参数取第几大或第几小
要找销售额里排第一的最大值，直接写 =AGGREGATE(14,6,C2:C11,1)；要找排第三的最小值，就写 =AGGREGATE(15,6,C2:C11,3)。这里 14 对应LARGE取第N大值的功能，15 对应SMALL取第N小值的功能，最后加的 k 参数就用来指定你要取第几个结果。
常用 function_num 对照
| 编号 | 等同函数 | 常见用途 |
|---|---|---|
1 |
AVERAGE | 平均值 |
2 |
COUNT | 统计数字个数 |
3 |
COUNTA | 统计非空单元格 |
4 |
MAX | 最大值 |
5 |
MIN | 最小值 |
9 |
SUM | 求和 |
14 |
LARGE | 第 k 大 |
15 |
SMALL | 第 k 小 |
容易写错的地方
| 错误现象 | 原因 | 处理方法 |
|---|---|---|
| 公式提示参数不足 | 使用 14 或 15 时漏写 k
|
补上最后一个参数，例如 =AGGREGATE(14,6,C2:C11,1)
|
| 隐藏行没有被忽略 |
options 选错，或隐藏方式不是手动隐藏 |
手动隐藏行时用 1、3、5 或 7；筛选后的可见数据通常用 3 更稳 |
| 结果仍然报错 | 第二个参数没有设置为忽略错误 | 改成 2、3、6 或 7
|
| 返回值和 SUM 不一致 | AGGREGATE 按忽略规则排除了部分行或错误值 | 核对是否有隐藏行、筛选行、错误值或嵌套汇总 |
| WPS 或旧版 Excel 找不到函数 | 当前软件可能不支持 AGGREGATE | 如果使用 WPS 或旧版 Excel，需要先确认是否支持该函数 |
扩展示例
| 需求 | 公式 | 说明 |
|---|---|---|
| 筛选后只统计可见销售额 | =AGGREGATE(9,3,C2:C100) |
求和时跳过隐藏行和错误值 |
| 忽略错误后求平均值 | =AGGREGATE(1,6,C2:C100) |
区域里有错误值也能继续算 |
| 取最高成交额 | =AGGREGATE(14,6,C2:C100,1) |
k=1 表示第 1 大 |
| 取第 3 低成本 | =AGGREGATE(15,6,D2:D100,3) |
k=3 表示第 3 小 |
| 统计有效数字个数 | =AGGREGATE(2,3,C2:C100) |
适合带筛选和错误值的数字列 |
其实只要记住两个核心规则就够用：第一个参数决定你要算什么，第二个参数决定你要跳过什么内容。需要取第几大、第几小的时候，再补最后那个 k 参数，AGGREGATE就能一次性把隐藏行、错误值、重复汇总这些麻烦事全处理完。
