Excel用Aggregate函数忽略错误值空值求和求平均值及其数组形式的用法

Aggregate函数是 excel 2010 新增的函数之一，它用于忽略错误值、空值、隐藏行列、SubTotal函数分类汇总求和、求平均值、统计单元格个数、查找最大值、最小值等。Aggregate函数分为引用形式和数组形式，它们支持 19 个函数；引用区域至少有一个，最多只能有 253 个；数组形式只支持 6 个函数，并且不能少 k 参数。

Aggregate函数的主要作用就是忽略错误值、空值、隐藏行列，如果用相应的函数求和、求平均值等不能达到此目的，可以使用Aggregate函数，这样不但便于写公式并且使问题变得简单。

一、Excel Aggregate函数语法

（一）引用形式

表达式：AGGREGATE(Function_Num, Options, Ref1, [Ref2], …)

中文表达式：Aggregate(函数序号, 忽略选项, 引用区域1, [引用区域2], …)

（二）数组形式

表达式：AGGREGATE(Function_Num, Options, Array, [k])

中文表达式：Aggregate(函数序号, 忽略选项, 数组, [第几个])

（三）说明：

1、Function_Num 为函数序号，它的取值范围为 1-19，每个数字代表一个函数，具体如下：

Function_num　　　　函数
　　　　　1　　　　　　AVERAGE
　　　　　2　　　　　　COUNT
　　　　　3　　　　　　COUNTA
　　　　　4　　　　　　MAX
　　　　　5　　　　　　MIN
　　　　　6　　　　　PRODUCT
　　　　　7　　　　　STDEV.S
　　　　　8　　　　　STDEV.P
　　　　　9　　　　　　SUM
　　　　　10　　　　　VAR.S
　　　　　11　　　　　VAR.P
　　　　　12　　　　　MEDIAN
　　　　　13　　　　　MODE.SNGL
　　　　　14　　　　　LARGE
　　　　　15　　　　　SMALL
　　　　　16　　　　　PERCENTILE.INC
　　　　　17　　　　　QUARTILE.INC
　　　　　18　　　　　PERCENTILE.EXC
　　　　　19　　　　　QUARTILE.EXC

2、Options 为忽略选项，共有 0 - 7 八个取值，具体如下：

0 或省略：忽略嵌套的 Subtotal 函数和 Aggregate 函数；

1：忽略隐藏行、嵌套的 Subtotal 函数和 Aggregate 函数；

2：忽略错误值、嵌套的 Subtotal 函数和 Aggregate 函数；

3：忽略隐藏行、错误值、嵌套的 Subtotal 函数和 Aggregate 函数；

4：忽略空值；

5：忽略隐藏行；

6：忽略错误值；

7：忽略隐藏行和错误值。

3、Ref 至少有一个，最多只能有 253 个；如果引用了三维单元格，将返回值错误 #VALUE!。

4、如果使用Aggregate函数的数组形式，一些函数不能省略参数 K，这些函数有 Large(Array, k)、Small(Array, k)、Percentile.inc(array, k)、Quartile.inc(Array, Quart)、Percentile.exc(Array, k)、Quartile.exc(Array, Quart)，如果省略了参数 K，将返回值错误#VALUE!。

5、用Aggregate函数进行分类汇总时，隐藏的列仍然会被计入汇总，而隐藏的行不被计汇总。

二、Excel Aggregate函数的使用方法及实例

（一）忽略嵌套的 Subtotal 函数实例

1、双击 E9 单元格，把公式 =SUBTOTAL(9,E2:E8) 复制到 E9，按回车，返回求和结果 5028；再次双击 E9 单元格，把公式 =AGGREGATE(9,0,E2:E9) 复制到 E10，按回车，返回求和结果 5028；操作过程步骤，如图1所示：

图1

2、公式 =SUBTOTAL(9,E2:E8) 是对 E2:E8 求和，公式 =AGGREGATE(9,0,E2:E9) 是对 E2:E9 求和，两公式返回结果一样，说明当Aggregate函数把 Options 参数设置为 0 时忽略 Subtotal 函数的求和结果。

（二）忽略空值的实例

1、双击 E9 单元格，把公式 =AGGREGATE(9,4,E2:E8) 复制到 E9，按回车，返回求和结果 5028；选中 E6，按 Delete 键，把数值删除，E9 中的值变为 4266；操作过程步骤，如图2所示：

图2

2、当把 E6 中的数值删除后，求和结果变小，说明把参数 Options 设置为 4 时，Aggregate函数会忽略空值。

（三）忽略隐藏行但包含隐藏列实例

1、双击 E9 单元格，把公式 =AGGREGATE(2,5,D2:E8) 复制到 E9，按回车，返回统计结果 14；右键第二行行号 2，在弹出的菜单中选择“隐藏”，则第二行被隐藏，E9 中的统计结果随之变为 12；右键 D 列列号 D，在弹出的菜单选择“隐藏”，把 D 列隐藏，E9 中的值仍是12；操作过程步骤，如图3所示：

图3

2、公式 =AGGREGATE(2,5,D2:E8) 中 2 代表统计数值单元格数目函数 Count，5 表示忽略空值；当隐藏第二行后，统计结果减少两个，说明把参数 Options 设置为 5，Aggrerate函数会忽略隐藏行；当隐藏列后，统计结果不变，说明Aggrerate函数包含隐藏列。

（四）忽略错误值实例

1、假如要对营业额求和。双击 G11 单元格，把公式 =AGGREGATE(9,6,G2:G10) 复制到 G11，按回车，返回求和结果 2682664.1；双击G12，输入公式 =SUMD(G2:G10)，按回车，返回值错误 #VALUE!，再次双击 G12，把 G2 改为 G3，按回车，同样返回求和结果 2682664.1；操作过程步骤，如图4所示：

图4

2、公式 =AGGREGATE(9,6,G2:G10) 中 6 表示忽略错误值，求和区域为 G2:G10，其中 G2 为错误，但被忽略；改用 Sum函数，则返回值错误 #VALUE!，只有不包含错误值才能返回正确结果。

提示：其它函数（如求平均值函数 Average、统计函数 Count、CountA、乘除法函数 Product、最大值函数 Max、最小值函数Min 等）也支持忽略错误值计算。

（五）需要参数 K 的函数省略后返回错误实例

1、假如要返回第三个销量最小值。双击 E9 单元格，把公式 =AGGREGATE(15,3,E2:E8,3) 复制到 E9，按回车，返回 638，它正好是第三个销量最小值；再次双击 E9 单元格，把“,3”去掉，按回车，返回值错误 #VALUE!；操作过程步骤，如图5所示：

图5

2、公式 =AGGREGATE(15,3,E2:E8,3) 中 15 表示 Small函数，参数 Options（3）表示“忽略隐藏行、错误值、嵌套的 Subtotal 函数和Aggregate 函数”，参数 k（3）表示返回第三个最小值；当把 k（3）删除后，返回 #VALUE!，说明 Small函数不能省略 k。

三、Excel 用Aggregate函数数组形式求平均值

1、假如有营业额和价格，要求根据它们求平均销量。双击 E9 单元格，把公式 =AGGREGATE(1,6,E2:E8/D2:D8) 复制到 E9，按回车，返回值错误 #VALUE!；再次双击 E9 单元格，把公式改为 =AGGREGATE(14,6,E2:E8/D2:D8,2)，按回车，返回服装销量第二个最大值 897；双击 E10，把公式 =AVERAGE(IFERROR(E2:E8/D2:D8,FALSE)) 复制到 E10，按 Ctrl + Shift + 回车，返回服装平均销量 721.60；再次双击 E10，把公式中 FALSE 改为 0，按 Ctrl + Shift + 回车，返回服装平均销量 515.43；操作过程步骤，如图6所示：

图6

2、公式说明：

A、公式 =AGGREGATE(1,6,E2:E8/D2:D8) 中 1 表示求平均值，6 表示忽略错误值，E2:E8/D2:D8 表示用 E2:E8 中每个元素除以 D2:D8 中对应的元素并以数组形式返回结果；公式的本意为：把 E2:E8/D2:D8 返回的数组求平均值，但Aggregate函数不支持Average函数的数组形式，因此返回值错误 #VALUE!。

B、公式 =AGGREGATE(14,6,E2:E8/D2:D8,2) 中 14 表示求最大值函数 Large，最后一个参数 2 表示返回第二个最大值；由于Aggregate函数支持Large函数的数组形式，因此能返回正确值。由此可知，要用数组形式，必须用支持数组形式的函数，即函数序号为 14-19 的函数。

C、公式 =AVERAGE(IFERROR(E2:E8/D2:D8,FALSE)) 中 IFERROR(E2:E8/D2:D8,FALSE) 用于判断 E2:E8 中每个元素除以 D2:D8 对应的元素，如果出错，返回 FALSE，否则返回相除的结果；例如第一次用 E2/D2，返回结果 329，第二次 E3/D3 发生错误，返回 FALSE，其它的以此类推，最后返回数组 {329;FALSE;638;FALSE;762;982;897}；则公式变为 =AVERAGE({329;FALSE;638;FALSE;762;982;897})，最后求平均值，返回 721.60，FALSE 不参与求平均值，即不包含 E2:E8 和 D2:D8 中为文本的单元格。

D、公式 =AVERAGE(IFERROR(E2:E8/D2:D8,0)) 把 FALSE 改为 0 后，区别在于 IFERROR(E2:E8/D2:D8,0) 返回的数组中 FALSE 变为 0，即 {329;0;638;0;762;982;897}，则公式变为 =AVERAGE({329;0;638;0;762;982;897})，返回求平均值结果 515.43，0 参与求平均值，即包含E2:E8 和 D2:D8 中为文本的单元格。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。