【扩展】新手进阶必学的三个函数②：AGGREGATE / 开普饭

最近推送的三篇文章：

· 正 · 文 · 来 · 啦 ·

前言

在工作中经常遇到这样的需求：

如果不用今天介绍的这个函数，其它二个需求还好办，要忽略掉错误值求和就要麻烦一点，用今天要介绍的函数就很轻松。当然，AGGREGATE函数的功能远不止上面三点，它能以一当十九。下面逐一介绍。

释义

aggregate

美['æɡrɪɡət]英['æɡrɪɡət]

一、忽略掉错误值求和、求平均......

当单元格区域有错误时，比如：

从其他地方查找引用数据，如果未找到会出现#N/A错误，如果是相除的话，分母为零时会出现#DIV/0错误。

当单元格区域中有这些错误，而我们又需要对其进行求和或者统计个数、求平均值时，用SUM、COUNT、AVERAGE这些函数来统计时，是得不到相应结果的，也会显示错误。如果要忽略错误求和，可以使用文章中提到的AGGREGATE函数：

AGGREGATE第一参数为9表示求和，第二参数为3表示忽略隐藏行、错误值、嵌套 SUBTOTAL 和 AGGREGATE 函数。如果只忽略错误值，第二参数可以使用6。

各参数的作用参见本文第四点。

扩展

在设计表格时，遇到求和区域有错误值无法求和，除了使用AGGREGATE来忽略，还有哪些处理方法，详见以前推送的这篇文章：

求和区域有错误值，没法求和，怎么办？

二、忽略掉小计数求和、求平均......

我们在设计预算表格时，可能会设计成下面这种样式：季度小计紧跟着相应月份后，然后在最后加一列计算截止某月的累计数。

比如，计算10月职工薪酬的累计数，如果输入公式：

=SUM(E4:Q4)

就会把一到三季度的小计数重复计入。

为了避免重复计算，我们可以在H列、I列、P列用AGGREGATE函数来求小计数，然后在U列使用AGGREGATE用下面的公式求和：

=AGGREGATE(9,3,E4:Q4)

解释：

AGGREGATE的第二参数为1或2或3时，会自动忽略掉使用了SUBTOTAL或AGGREGATE函数的小计列，从而避免重复求和。

当然，也可使用SUBTOTAL函数实现忽略掉小计列的功能。

需要强调的是，小计列一定要是SUBTOTAL或AGGREGATE函数，如果小计列用SUM函数，是不会忽略的。

扩展

我们将上面公式的第三参数改用OFFSET+MATCH来变动引用求和区域，就可实现求指定月份的累计数：

=AGGREGATE(9,3,OFFSET(E4,0,0,1,MATCH($U$2,$E$3:$S$3,0)))

三、忽略掉隐藏的行求和、求平均......

和SUBTOTAL函数一样，AGGREGATE可以忽略掉隐藏的行，当其第二参数为5时可只对筛选出的行求和。如下图：

AGGREGATE第二参数作用小结

前面所介绍的功能是根据AGGREGATE函数的第二参数来变化的，其第二参数的主要作用如下图：

四、一个当十九个

AGGREGATE可根据第一参数的不同，相当于十九个不同的函数，参见下图的A列B列:

比如第一参数为1时相当于AVERAGE函数，第一参数为9时相当于SUM函数。

最后，献上AGGREGATE函数的自我评价作为结束语：

一个胜十九个，全能冠军，舍我其谁！

【扩展】新手进阶必学的三个函数②：AGGREGATE