忽略错误值求和算平均,Excel 聚合函数aggregate 用法教程

既然敢叫聚合函数，说明也是有过人之处。AGGREGATE 函数可将不同的聚合函数应用于列表或数据库，并提供忽略隐藏行和错误值的选项。感觉有分类汇总函数subtotal的味道，虽然subtotal也很厉害，但跟aggregate函数相比，还是差一些。

第一：subtotal函数支持11种聚合函数，aggregate支持19种聚合函数。

第二：subtotal可以忽略隐藏行和自身嵌套，aggregate可以忽略隐藏行、错误值、subtotal嵌套、aggregate嵌套，并且可以自由指定要忽略内容。

在用法上，aggregate包含引用和数组两种形式：

引用形式：AGGREGATE(function_num, options, ref1, [ref2], …)

数组形式：AGGREGATE(function_num, options, array, [k])

    参数和形式都很多，咱们先从共性的部分开始说起。

     第一个参数function_num，指定aggregate函数使用哪种方式进行计算，共有19种。从示例表格中也可以看出函数引用形式和数组形式的区别，1-13可以采用引用或者数组形式，而14-19只能是数组形式，且必须明确指定第4个参数值。

    第二个参数选项options，决定函数要忽略哪些值。在Excel 2016中，共有8种组合方式。

    对于引用形式，ref引用范围最多支持253个。

    对于数组形式的第三参数只支持一个，至于第四参数k值根据函数代码值确定。

    无论是入门的sum求和函数，还是高级的subtotal分类汇总函数，都没有办法直接对包含错误值的区域进行求和，一般推荐的解决方案是搭配if函数组合使用，数组公式还是比较推荐复杂的。

    其实，通过使用aggregate聚合函数，完全可以把操作简单化，A10单元格公式“=AGGREGATE(9,6,B2:B5)”，通过指定function_num和options参数，实现对单元格区域忽略错误值求和，规避因数据未提交产生的临时性错误。