搜索
写经验 领红包
 > 知识

aggegate函数怎么用(sum和avg函数的限制)

导语:强大到没有对手的Aggregate函数,碾压Sum等19个函数,你真的会吗

从前期的学习中,我们已经知道,Subtotal函数的功能非常强大,但还有一个函数,其功能强大到了没有对手,此函数就是Aggregate,可以实现Sum、Average、Count、Max、Min、Proudct、Media等19个函数的功能。而且还可以隐藏、错误值、空值等,不仅如此,还支持常量数组。

功能:返回数据列表或数据库的合计。

语法结构:分为“引用形式”和“数组形式”。

引用形式:=Aggregate(功能代码,忽略代码,数据区域1,[数据区域2],[数据区域3]……)

数组形式:=Aggregate(功能代码,忽略代码,数组,[索引值])。

参数解读:

功能代码:必需,介于1-19之间的整数值,指定要使用的汇总方式,也就是指定要使用的函数。

忽略代码:必需,介于0-7之间的数字,指定在计算区域内要忽略那些类型的值。

备注:

1、在将Aggregate函数名称及左括号输入到工作表的单元格中时,就会立即看到作为参数使用的所有函数的列表,如下图:

如果需要的函数不在列表中,请向下拖动滚动条。

2、第2个参数时必须的,但未填写,Aggregate函数将返回VALUE!,所以在用Max函数计算最大值时,返回错误值,此时可以用Aggregate函数忽略错误值,然后计算最大值。

二、Aggregate函数:忽略错误值并计算最大值。

目的:计算第3名员工的“月薪”。

方法:

在目标单元格中输入公式:=AGGREGATE(14,6,G3:G12,3)。

解读:

功能代码14代表的是Large函数,即返回数组中第K个最大值,在本示例中,就是返回G3:G12中的第3个最大值;忽略代码6为忽略错误值。

三、Aggregate函数:忽略错误值并计算最大值。

目的:计算倒数第3名员工的“月薪”。

方法:

在目标单元格中输入公式:=AGGREGATE(15,6,G3:G12,3)。

解读:

公式=AGGREGATE(15,6,G3:G12)看起来并没有错误,因为语法结构中已经明确前3个参数时必须的,最后1个参数可以省略;但仔细分析发现,代码15对应的函数为Small,即返回G3:G12中的第K个最小值,但公式中并没有指定K,所以返回错误值。

四、Aggregate函数:多个区域求和。

目的:忽略错误值,并计算所有员工前半年的总“销量”。

方法:

在目标单元格中输入公式:=AGGREGATE(9,6,D4:I13)。

解读:

如果多个区域不连续,也可以采用=AGGREGATE(9,6,D4:D13,E4:E13,F4:F13,G4:G13,H4:H13,I4:I13)方式实现,即独立编辑每个数据区域;除了求和之外,其他的函数同样适用。

五、Aggregate函数:筛选状态下忽略错误值。

目的:按性别计算总“月薪”。

方法:

在目标单元格中输入公式:=AGGREGATE(9,7,G3:G12)。

解读:

忽略代码7的作用为:忽略隐藏行和错误值。

六、Aggregate函数:批量统计。

目的:一次性查询可见区域和总区域的最大值、最小值、平均值、和值、计数、并计算中位数。

方法:

在目标单元格中输入公式:=AGGREGATE({4;5;1;9;3;12},{5,0},G3:G12),并用Ctrl+Shift+Enter填充。

解读:

由于是区域性数组公式,所以先选取目标单元格区域,然后编辑公式,最后用Ctrl+Shift+Enter填充。

结束语:

从上数的示例中可以看出,Aggregate的功能非常的强大,小编只是列举了部分示例,如果亲有更多的关于Aggregate函数的应用技巧,可以在留言区留言讨论哦!

本文内容由快快网络小林整理编辑!