AGGREGATE：会跳过错误的统计

SUM 见了一个错误值就罢工

做月度汇总时最让人抓狂的一幕是这样的：一张两千行的明细表，其中第 837 行因为源数据缺失，VLOOKUP 没找到，老老实实地显示了 #N/A。你兴冲冲地在底部写 =SUM(G2:G2001)，回车，结果也是一个 #N/A。两千个好数据被一个坏数据全票否决，SUM 连商量都不商量。

传统的绕法有三种，都不太体面。第一种是把错误值改掉，用 IFERROR 把整列包一层，可这样就多了一列辅助数据，源数据一更新还得跟着刷；第二种是数组公式 =SUM(IF(ISNUMBER(G2:G2001),G2:G2001))，写完之后得按 Ctrl+Shift+Enter 三键结束，交给同事十有八九会被改坏；第三种是用 =SUMIF(G2:G2001,"<>#N/A")，但它只能过滤一种错误，遇到 #DIV/0! 或 #VALUE! 就失效了。

AGGREGATE 就是为了这一刻而生的。它是 Excel 2010 引入的“带条件的统计函数”，把两个维度塞进一个函数里：第一个维度是你想做哪种统计（求和、平均、计数、第 k 大……），第二个维度是你想忽略什么（错误值、隐藏行、嵌套的小计……）。于是 =AGGREGATE(9,6,G2:G2001) 一句话就解决了上面的困境：9 代表求和，6 代表忽略错误值，回车出结果，不需要三键，不需要辅助列，也不需要把源数据洗干净。

更妙的是它和筛选的联动。用 SUBTOTAL 的人知道，筛选之后统计会自动只算可见行；AGGREGATE 把这个能力也收了进来，而且能同时忽略错误值。也就是说，一个函数同时做到了“只算看得见的”和“跳过算错的”，这在 SUBTOTAL 时代是做不到的。

为什么错误值会“传染”？因为 Excel 的运算规则里，错误值参与任何算术运算都还是错误值，一条链上有一个坏节点，整条链就宣告失败。这个设计其实很合理，它逼着你去看见问题而不是掩盖问题，只是在“我只是想先看看总额”的时刻显得过于严厉。AGGREGATE 提供的是一个折中：我明确知道这里可能有错，请你在统计时绕开它，但不要打扰我做别的事。

还要提醒一句，AGGREGATE 跳过的是错误值本身，不是“看起来可疑的数据”。如果源系统把缺失值填成了 0 或者 -9999，函数照样会把它们算进去，结果偏低还不报错。所以在使用这类容错函数之前，最好先弄清楚缺失值在源数据里到底长什么样，别把容错当成万能药。

还有一层隐藏的好处：AGGREGATE 不需要三键结束。Excel 2010 之前，凡是涉及数组运算的公式大多要按 Ctrl+Shift+Enter，公式外面套一对大括号，同事一编辑就失效。AGGREGATE 从设计上就绕开了这个限制，写进去是什么样就是什么样，能随便复制、随便拖动、随便让不懂公式的人双击进去看。对于要交给别人维护的共享表格，这一点比省几行代码重要得多。

两个参数决定一切：函数编号与选项

AGGREGATE 的完整语法是 =AGGREGATE(函数编号,选项,数组,[k])。前两个参数是理解它的钥匙，后面两个是常规的区域和可选的第 k 个值。函数编号从 1 到 19，每个数字对应一种我们熟悉的统计函数，第一次看会觉得反人类，用熟了反而比记函数名快。

第二个参数叫选项，取值 0 到 7，决定忽略哪些内容。这张表值得单独存一份，因为它是 AGGREGATE 区别于其他统计函数的核心。

日常用得最多的是 3 和 7，两者都能同时跳过隐藏行和错误值，区别在于是否忽略嵌套的小计。如果表里已经有用 SUBTOTAL 算出来的分区小计，而你在总计行用 AGGREGATE 统计整列，就必须选 3，否则小计会被重复计算一次，总额翻倍；如果统计的区域里根本没有嵌套小计，选 7 更省心。至于 0 到 2 和 4 到 6，属于特定场景才用得上，比如明确只想忽略错误但保留隐藏行时选 2 或 6。

记不住编号也没关系，有个笨办法特别管用：在单元格里输入 =AGGREGATE( 之后，Excel 会弹出参数提示，把光标停在第一个参数上时，屏幕提示里会列出全部 19 个编号和对应函数名，第二个参数同理。选好之后公式栏里显示的就是数字，不用你背。真正需要背的其实只有四个组合：求和用 9、平均用 1、计数用 2、第 k 大用 14，选项固定写 7，日常八成的需求就都被覆盖了。

另外要注意参数顺序容易记反。AGGREGATE 是“先编号后选项”，也就是先说做什么、再说忽略什么，跟中文的语序恰好相反。写反了的后果通常是返回 #VALUE! 或者一个明显不合理的数字，看到这种结果先检查这两个数字有没有对调。

四种能直接抄的写法

写法一，忽略错误值求和，这是最高频的用法。明细列 G2:G2001 里混着 #N/A，直接写 =AGGREGATE(9,6,G2:G2001)，求和并跳过所有错误。顺带一提，9 和 6 这两个数字念几遍就能记住：9 是 SUM，6 是“六亲不认地跳过错误”。

写法二，筛选后统计可见行。给明细表加上自动筛选，筛出“华东区”，然后在汇总单元格写 =AGGREGATE(9,7,G2:G2001)，得到的就是筛选结果的合计，同时还会忽略错误值。再换筛选条件，数字自动跟着变，不用改任何公式。

写法三，取第 k 大值且跳过错误。=AGGREGATE(14,6,G2:G2001,1) 返回可见数据里的最大值，=AGGREGATE(14,6,G2:G2001,3) 返回第三大。编号 14 到 19 这几个函数天生接收数组表达式，不需要三键结束，所以还能玩出花样：=AGGREGATE(14,6,G2:G2001/(B2:B2001="华东"),1) 直接算出华东区的最大值，连辅助列都不用。这种除法规避条件的技巧在SUMPRODUCT：多条件计算的万金油里还会再见到。

写法四，忽略隐藏行求平均但不碰错误。做排行榜时经常手动隐藏掉若干行再算平均分，用 =AGGREGATE(1,5,G2:G2001)。注意这里选项用了 5 而不是 7，意味着错误值仍然会让结果报错，好处是错误不会被悄悄吞掉，你能及时发现数据源有问题。到底该吞掉错误还是暴露错误，取决于这份表是要给老板看还是要给自己查错。给老板看的报表要稳，错误悄悄跳过；给自己查错的分析表要响，错误必须冒出来。

再补两个不那么常见但很有用的编号。编号 12 是中位数，工资、房价、客单价这类分布严重右偏的数据，中位数比平均值更能反映真实水平，配合选项 6 就是 =AGGREGATE(12,6,G2:G2001)，一行出结果。编号 16 和 17 分别是百分位和四分位，做绩效分布、成绩分档时能直接算出 90 分位线，写法是 =AGGREGATE(16,6,G2:G2001,0.9)，最后那个 0.9 就是 k，代表百分之九十的位置。这两个编号加上 k 的用法，是很多人用了几年 Excel 都没碰过的隐藏功能。

如果需要一次给出一组统计量，比如同时要最大值、最小值、平均值，那就写三行 AGGREGATE，别指望一个函数返回多个值，它的第一参数只接受单个编号。想少写几遍长区域，可以先把统计区域定义成一个名称，再在三行公式里引用它，维护时只改一处即可，命名的进阶玩法见命名公式与名称管理器的进阶玩法。

和 SUBTOTAL、SUMPRODUCT 怎么选

三个函数都能做“有条件的统计”，分工其实很清楚。SUBTOTAL 是筛选场景的老前辈，编号 109 求和、101 求平均、103 计数，专门忽略隐藏行，但它对错误值毫无抵抗力，区域里有一个 #N/A 就整个崩掉。它的优势是兼容性好，从 Excel 2003 一路用到现在，还能配合分类汇总功能自动生成小计。

SUMPRODUCT 是条件计算的全能选手，能写任意复杂的多条件逻辑，也不怕错误值吗？其实它同样怕，遇到错误值照样返回错误，得靠 =SUMPRODUCT((条件)*(IFERROR(数据,0))) 这类写法绕。SUMPRODUCT 真正的短板是不管隐藏行，筛选对它完全无效，筛完之后它还是算全部两千行。

AGGREGATE 的位置就在两者中间偏上：它继承了 SUBTOTAL 的隐藏行感知，又补上了忽略错误的能力，代价是函数编号和选项要背，而且只支持固定的 19 种统计，做不到 SUMPRODUCT 那种任意条件组合。所以判断标准很简单：需要筛选联动或跳过错误，用 AGGREGATE；需要多条件自由组合，用 SUMPRODUCT；需要兼容 Excel 2007 及更早版本，用 SUBTOTAL 或 SUMPRODUCT。

如果条件正好落在 SUMIFS、COUNTIFS、AVERAGEIFS 的能力范围内，那更该优先用这三个。它们是多条件统计的正规军，语法直观、性能好、还自带通配符匹配，唯一的短板同样是不认隐藏行。真正非用 AGGREGATE 不可的场景其实只有两个：区域里混着错误值又不想清洗，或者统计必须跟随筛选结果变化。认清这两条，就不会为了炫技而把简单问题复杂化。

版本方面 AGGREGATE 从 Excel 2010 开始提供，Excel 2016、2019、2021 和 Microsoft 365 都有，WPS 最新版也已支持。如果你的文件要发给用 Excel 2007 的同事，就得准备降级方案，把 AGGREGATE 换成 SUBTOTAL 加清洗列的组合，本书后面讲版本对照的那篇会给出完整的替换清单。

翻车提醒

坑一，隐藏列不管。AGGREGATE 只忽略隐藏的行，隐藏的列照样参与计算。很多人把不需要的字段整列隐藏起来，以为统计会自动跳过，结果数字对不上。要么取消隐藏，要么显式缩小区域范围。

坑二，筛选之外的隐藏方式不敏感。AGGREGATE 能识别自动筛选和手动隐藏行，但对“行高设为 0”或者被分组折叠的行，不同版本表现不完全一致。做重要报表前最好用几个已知数据手工验一遍，别完全相信默认值。

坑三，k 参数只对部分编号生效。只有编号 14 到 19 需要第四个参数 k，给其他编号传 k 会被直接忽略；反过来，用 LARGE 却忘了写 k，函数会返回 #VALUE!。另外 k 必须大于 0 且不超过数据个数，否则报 #NUM!。

坑四，跨域引用三维区域会失效。AGGREGATE 不支持跨工作表的三维引用写法，比如 =AGGREGATE(9,6,Sheet1:Sheet3!A1) 是行不通的，只能分别统计再相加。

坑五，它不忽略文本。选项里的“忽略错误值”只针对 # 开头的错误，区域里的文本字符串对 SUM 类统计本来就无效，但对 COUNTA（编号 3）却会被计入。想只数数字就用编号 2，想数所有非空就用编号 3，别选错。

坑六，和 SUBTOTAL 混用时编号要对齐。SUBTOTAL 用 101 到 111 表示忽略隐藏行、1 到 11 表示不忽略，这套编号和 AGGREGATE 的 1 到 19 完全不同源。有人在总计行写 =AGGREGATE(109,7,区域)，把 SUBTOTAL 的 109 直接搬过来，结果返回 #VALUE!，因为 AGGREGATE 根本没有 109 号。两套编号体系各记各的，切换函数时一定要重新查表。

坑七，跨工作簿引用时容易失联。AGGREGATE 本身没问题，但当统计区域指向另一个未打开的工作簿，且那个文件里有错误值时，部分版本会返回错误而不是跳过。稳妥做法是把外部数据先落到本地工作表，再在本地做统计。

常见问题

为什么我用了 AGGREGATE 结果还是错误

先检查选项参数。这个函数的报错八成出在第二个数字上，而不是区域上。最常见的是把选项写成 0 或省略，AGGREGATE 的默认行为是不忽略任何东西，跟 SUM 一模一样，错误值照样穿透。忽略错误要选 2、3、6、7 中的一个；忽略隐藏行要选 1、3、5、7 中的一个。第二个常见原因是区域里混着文本型数字，MAX、MIN 会直接无视它们，导致结果比预期小，用 =COUNT(G2:G2001) 对比一下数字个数就能发现。

选项 3 和选项 7 到底该选哪个

取决于统计区域里有没有嵌套的 SUBTOTAL 或 AGGREGATE。如果这张表已经按部门插入了分类汇总，每个部门底部都有一个小计，而总计行又要对整列做一次统计，就必须选 3，让函数跳过这些小计，否则每个部门会被算两遍，总额直接翻倍。如果区域是干净的明细行，没有任何小计混在里面，选 7 即可，逻辑更直观。拿不准的时候选 3 更安全，最多是漏掉本来就不需要统计的格子。

能不能用 AGGREGATE 做多条件求和

部分可以，但不如 SUMPRODUCT 顺手。对编号 14 到 19，可以用除法构造条件，比如 =AGGREGATE(14,6,金额列/(区域列="华东")/(月份列=3),1) 能算出华东区三月的最大值，因为条件不成立时分母为 0，得到 #DIV/0!，正好被选项 6 忽略掉。但求和（编号 9）不支持这种数组表达式的稳定写法，多条件求和还是老实交给 SUMPRODUCT 或 SUMIFS。工具各有主场，硬套反而容易出错。

性能上三者也有差别。AGGREGATE 是原生函数，单次调用的重算开销比同等逻辑的 SUMPRODUCT 小，尤其在几万行的表上差距明显；但如果为了多条件而堆很多层除法，计算量反而会上去。真到了几万行还要反复筛选的场景，最稳妥的方案是把明细表转成正式表格对象（Ctrl+T），再用数据透视表做汇总，公式只是补充手段，不该承担所有事情。

一个区域里既有错误值又有隐藏行，统计结果对不上怎么办

分步排查。第一步把选项换成只忽略错误（6），看结果是否变化，判断隐藏行有没有被算进去；第二步换成只忽略隐藏行（5），看错误值是不是罪魁祸首；第三步用 =COUNT(区域) 和 =COUNTA(区域) 对比，确认区域里有没有混进文本。三步走完基本能定位问题。如果还是对不上，检查一下区域范围有没有多框或少框一行，尤其是表格中间插入过行的情况，区域引用往往会错位。

能不能让 AGGREGATE 只统计最近 N 行

可以，但要借助别的函数把区域做成动态引用。经典写法是配合 INDEX 和 COUNTA 构造一个“最后 N 行”的区域，例如 =AGGREGATE(9,6,INDEX(G:G,COUNTA(G:G)-8):INDEX(G:G,COUNTA(G:G))) 统计 G 列最后 9 行的合计并跳过错误值，前提是 G 列中间没有空格。如果你的版本支持动态数组，更简单的办法是先用筛选类函数把最近 N 行捞出来，再交给 AGGREGATE 统计，逻辑清楚得多。想彻底搞懂这类动态区域，可以顺着数组溢出：一个公式填满一片格子继续读。

Excel 2007 里没有 AGGREGATE 怎么办

有三条退路。求和用 =SUM(IF(ISNUMBER(区域),区域)) 数组公式，按三键结束，或者用非数组的 =SUMIF(区域,"<>#N/A") 处理单一错误类型；只算可见行用 SUBTOTAL 的 109、101、103 编号；两者都要则只能先加一列 =IF(ISNUMBER(G2),G2,0) 的清洗列，再对清洗列用 SUBTOTAL。多一列不丢人，能跑起来才是硬道理。关于版本差异的完整清单，可以翻版本对照：新函数在老 Excel 里怎么办。