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

亮术网 2019-08-30 本网原创

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所示:

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

图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所示:

Excel Aggregate函数忽略空值求和的实例

图2

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

 

(三)忽略隐藏行但包含隐藏列实例

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

Excel Aggregate函数忽略隐藏行但包含隐藏列统计单元格数目实例

图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所示:

Excel Aggregate函数忽略错误值求和实例

图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所示:

Excel Aggregate函数数组形式需要参数 K 的函数省略后返回错误实例

图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所示:

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

图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 中为文本的单元格。