ARTICLE · 1050745
Excel 最被低估的函数 AGGREGATE,求和求平均自动忽略错误行
Excel 最被低估的函数 AGGREGATE,求和求平均自动忽略错误行
业务场景 做采购、销售明细表时，经常遇到一种崩溃情况：某一行因为单价填了 "待定"" 待确认 " 这类文字，导致金额列计算出#VALUE!错误。 这时候你用普通SUM(F2:F14)求和，只要区域里有一个错误值，整个求和结果就跟着变成#VALUE!，一行数据出错，整张表的合计全部废掉。 AGGREGATE函数就是为这种场景设计的 —— 它可以在求和、求平均、找最大值时，自动跳过错误值、隐藏行，数据再脏也能算出正确结果。 最终公式 F15 输入公式，直接返回正确合计1210356，F10 的#VALUE! 被自动忽略。 
公式逐层拆解 AGGREGATE 完整语法： 第 1 参数 9：指定要执行的运算 AGGREGATE 用数字代表不同函数，9 就代表 SUM 求和。 常用函数编号速查：
第 2 参数 7：指定忽略哪些内容 7 代表 忽略隐藏行 + 忽略错误值。 选项速查：
本例选 7，既能跳过错误值，又能跳过被隐藏的行，做筛选后合计也不会重复计算。 第 3 参数 F2:F14：要计算的数据区域 就是金额列。AGGREGATE 遍历这个区域，遇到错误值直接跳过，对剩下的数值执行 SUM。 通用模板 常用改写示例 忽略错误值求平均 忽略错误值找最大值 忽略错误值找最小值 高阶拓展写法 拓展 1：求第 2 大金额（忽略错误值） AGGREGATE 第 1 参数写 14（LARGE），第 4 参数写名次： 返回金额第 2 大的值，错误行自动跳过。 拓展 2：求第 3 小金额 拓展 3：和 IFERROR 对比 普通思路是先把每个错误值包成 0 再求和： 旧版 Excel 需要 Ctrl+Shift+Enter 三键结束；AGGREGATE 直接回车，写法更短，还能顺便处理隐藏行。 拓展 4：筛选后只合计可见行 对表格做了筛选（比如只看 "领导餐厅"），AGGREGATE 选项 7 会自动忽略被筛选隐藏的行，合计结果就是当前可见行的合计，和 SUBTOTAL 效果一致但功能更强。 避坑要点 新旧方案对比 ❌普通 SUM：区域里一个错误值，全表合计报废，必须先手动清理错误行才能求和。 ❌IFERROR 数组套 SUM：公式长，旧版要三键结束，不能处理隐藏行。 ✅AGGREGATE：一条公式自动跳过错误值和隐藏行，求和、平均、最大最小全能，直接回车，兼容 Excel 2010 及以上所有版本。 适用场景：含错误值的采购 / 销售明细合计、筛选后可见行汇总、脏数据统计、多条件忽略异常值计算。 
01
02
=AGGREGATE(9,7,F2:F14)普通 =SUM(F2:F14)→ 返回 !（被 F10 拖垮）=AGGREGATE(9,7,F2:F14)→ 返回 1210356（自动跳过错误行）
03
=AGGREGATE(9,7,F2:F14)=AGGREGATE(函数编号, 选项, 数据区域, [可选参数])| 7 | 忽略隐藏行 + 错误值（最常用） |
04
=AGGREGATE(函数编号, 7, 数据区域)函数编号：9 = 求和、1 = 平均、4 = 最大、5 = 最小 选项固定写 7：忽略错误值和隐藏行，最稳妥
05
=AGGREGATE(1,7,F2:F14)=AGGREGATE(4,7,F2:F14)=AGGREGATE(5,7,F2:F14)06
=AGGREGATE(14,7,F2:F14,2)=AGGREGATE(15,7,F2:F14,3)=SUM(IFERROR(F2:F14,0))07
第 2 参数别写错：写 0 不会忽略错误值，照样报错；忽略错误值至少写 2 或 6，最稳妥写 7。 函数编号记不住：求和固定写 9，求平均写 1，最大写 4，最小写 5，这四个覆盖 90% 场景。 AGGREGATE 不支持整列引用带文本：如果区域里有表头文字（如 F1="金额"），AGGREGATE 会自动忽略文本，不影响计算，但建议从数据行开始引用（F2:F14）。 和 SUBTOTAL 的区别：SUBTOTAL 只能做 11 种基础运算，且只忽略隐藏行不忽略错误值；AGGREGATE 支持 19 种运算，能同时忽略错误值和隐藏行，是 SUBTOTAL 的全面升级版。 第 4 参数只有特定函数需要：LARGE (14)、SMALL (15)、PERCENTILE 等需要额外参数，普通 SUM/AVG/MAX/MIN 不用写。
08