Excel 函数 AGGREGATE:表里一堆 #DIV/0!,账照样算

客户甩过来一张表，让你统计这个月的良率和产值。你点开一看，好家伙——

C 列一片 #DIV/0!，D 列躺着几个 #N/A。你随手敲个 =SUM(C2:C200)，回车，屏幕一黑，公式原地爆炸，连个数都不给你。

这种表你肯定遇到过。产线没开机的日子，良率是 0 除以 0；物料没对上的行，VLOOKUP 找不到返回 #N/A。数据是脏的，错也是真错，但账不能不算。

今天说的这个函数专治这个：AGGREGATE。它一个函数打包了 19 种汇总算法，还能选择性地「看不见」错误值、看不见被筛选掉的行。名字直译过来就是「聚合」——把一堆数拢到一块儿算个账，哪怕中间混着几个错误。

老办法为什么越用越别扭

遇上错误值，老办法是拿 IFERROR 一个一个包：=IFERROR(C2,"")，一行一行往下拖。包到最后你会发现两件事。

第一，包完的表是「假干净」。错误是被遮住了，不是被解决了。哪行数据有问题，你反而看不见了——下次客户问「这个数怎么不对」，你翻遍全表找不着病灶。

第二，两百行公式手包到明年。而且客户下周再甩一张新表来，你还得再包一遍。

我前面专门写过 IFERROR，结论是：它是创可贴，也是手术刀，看场合用。今天这篇给你另一件装备。如果说 IFERROR 是「把错误藏起来」，那 AGGREGATE 就是「承认这里有错误，但算的时候跳过它」。

这两个是两回事。知道错在哪，和假装没错，是两回事。做代做这一行，交付的表迟早要过很多道手，哪天出了问题，能一眼看出病灶的表，才叫专业。

函数长什么样

AGGREGATE 有两种形态。

引用形态：=AGGREGATE(function_num, options, 区域1, 区域2, ...)

数组形态：=AGGREGATE(function_num, options, 数组, [k])

第一个参数 function_num，1 到 19，选今天用哪种算法。常用的拎出来记：1 是 AVERAGE 平均，2 是 COUNT 数数字，3 是 COUNTA 数非空，4 是 MAX，5 是 MIN，9 是 SUM，12 是 MEDIAN 中位数，14 是 LARGE 第几大，15 是 SMALL 第几小。剩下的是 PRODUCT、一堆标准差方差、MODE、PERCENTILE、QUARTILE，用不上别硬记，用的时候再查。

第二个参数 options，0 到 7，决定「什么东西当没看见」。这一个是灵魂，下面单开一段说。

第三个参数就是区域，跟 SUM 一模一样，区域可以一次给好几个。

options 才是灵魂

options 的 8 种行为，不用全背，记三组就够：

6 = 只忽略错误值。5 = 只忽略隐藏行（被筛选掉、或者手动藏起来的行）。7 = 两个都忽略。

其余是跟「嵌套小计忽略」打配合的组合：0 是默认，1 和 3 是带隐藏行的组合，2 是带忽略错误的组合，4 是什么都不忽略。

为什么「忽略隐藏行」是个卖点？你筛选之后，SUM 会把筛掉的行照样算进去，SUBTOTAL 能只算可见行——这个我前面那篇写过。但 SUBTOTAL 算法就那几种，遇上错误值照样死给你看。AGGREGATE 的 option 5 和 7，等于把「筛选态汇总」和「无视错误」两件事一块办了，还多出 LARGE、SMALL、中位数这些 SUBTOTAL 根本没有的算法。

筛完一张五千行的表，敲个 =AGGREGATE(9,7,B2:B5000)，回车——可见行的和，错误值自动跳过。这一个公式，顶过去半个工时。

上手三个场景

场景一：带错汇总。良率报表里混着 #DIV/0!，=AGGREGATE(1,6,C2:C200) 出平均良率，=AGGREGATE(9,6,C2:C200) 出总产量。错误行不参与计算，也不炸。

场景二：筛选态排名。筛完「本月不良 TOP3」，=AGGREGATE(14,7,E2:E200,3) 直接出第三大。注意 14、15 这两个函数必须给第四个参数 k——第几大，不写它直接 #VALUE! 伺候。这是官方文档自己挂出来的翻车例子，我亲眼见过有人在这栽跟头。

场景三：中位数防「平均骗人」。这个是我压箱底的用法。一列工时混着几个加班到半夜的 16 小时，AVERAGE 直接被拉飞，中位数才是「普普通通的一天」。=AGGREGATE(12,7,F2:F200)，错误和隐藏行全跳过。我讲 FREQUENCY 那篇说过，平均数会骗人，中位数不会——两篇对着看，味道更足。

坑，四个

一，14 到 19 号函数（LARGE、SMALL、PERCENTILE、QUARTILE）必须给第二个数值参数，忘了给就是 #VALUE!。

二，数组参数里如果塞了计算表达式，比如 =AGGREGATE(14,3,A1:A100*(A1:A100>0),1)，「忽略隐藏行」会失效——这是官方原话。过滤条件写在数组里和写在选项里，不是一回事。

三，它是为「列」设计的。横着躺的一行数据，隐藏列不影响 option 5 和 7 的结果。用之前先看看你的数据是竖着放的还是横着摊的。

四，3-D 引用（跨表那种 Sheet1:Sheet3!A1:A10）直接 #VALUE!，不支持，别试。

最后老规矩：这函数 Excel 2010 以上才有。客户要是 2007 甚至 2003，老老实实退回「筛选后复制到干净区域再 SUM」，笨是笨，交付不能等。

收尾

AGGREGATE 这个函数，建议你记住的不是那 19 个数字，是它背后的那个态度：

账要算，但不能因为怕错，就把错遮起来算。

遮起来的那种干净，是给领导看的；跳过去的那种干净，是给下一个接手的人看的。你做代做，表交出去只是开始，后面有的是人要在这张表上干活。公式栏里敲烂事，手上留张能查的表。