Excel函数学习之神奇的AGGREGATE,竟可一个抵19个!

时间:2022-05-17
阅读:

今天要和大家介绍的这个函数叫做AGGREGATE。虽说是Excel 2010中就有的函数,可是知道这个函数的人真没多少,这是一件非常遗憾的事情,因为AGGREGATE函数不仅可以实现诸如SUM、AVERAGE、COUNT、LARGE等19个函数的功能,而且还可以忽略隐藏行、错误值、空值等。如果区域中包含错误值,SUM等函数将返回错误,这时用 AGGREGATE函数就非常方便了。

光说不练假把式,下面就看看AGGREGATE的本领。

我们用一个成绩表来说明AGGREGATE的基本用法,数据源如图所示:

Excel函数学习之神奇的AGGREGATE,竟可一个抵19个!

每个学生参加六项测试,根据成绩来得出蓝色区域的五项统计内容,相信对于大多数朋友来说,要完成这个表格并不难,无非就是掌握几个最基础的函数:AVERAGE(平均分)、SUM(总分)、MAX(最高分)、MIN(最低分)和COUNT(实际参考科目)分别对五项内容进行统计。可能也有些新朋友还不了解以上提到的这五个函数,那么正好,你只需要学习AGGREGATE这一个函数就可以实现上面这些数据的统计。

AGGREGATE的基本格式为:= AGGREGATE(统计功能,忽略哪些值,数据区域),以下分别来看看如何完成例子中的五项统计内容。

一、统计平均分

当前表格平均分统计公式为:=AGGREGATE(1,,B2:G2)。在H2单元格中输入公式再整列填充公式即可获得各学生的平均分。

Excel函数学习之神奇的AGGREGATE,竟可一个抵19个!

说明:当统计功能为1的时候,函数实现计算平均值的功能。本例中我们并不需要指定忽略统计的数据,因此第二参数可以省略(此处写了两个逗号,中间省略了一个参数),最后一个参数就是要计算的数据区域B2:G2,函数用法非常简单,那么结果是否正确呢?不妨使用AVERAGE函数来验证一下:

Excel函数学习之神奇的AGGREGATE,竟可一个抵19个!

可以看到,结果完全一致!

接下来我们再看看如何用AGGREGATE函数统计总分。

二、统计总分

当前表格总分统计公式为:=AGGREGATE(9,,B2:G2)。在I2单元格中输入公式再整列填充公式即可获得各学生总分。

Excel函数学习之神奇的AGGREGATE,竟可一个抵19个!

只需要将第一个参数改为9即可,因为9对应的就是求和功能。

说到这里,可能会有些朋友担心,第一个参数里1代表平均值, 9代表求和,这个函数一共有19个功能,会不会很难记住。

实际上完全不需要有这种担心,Excel为我们提供了非常智能的提醒功能,当我们输入函数之后,就有对应参数功能的选项:

Excel函数学习之神奇的AGGREGATE,竟可一个抵19个!

只要对照这个提示,选择自己需要的功能即可。

三、统计最高分

了解这个功能以后,最后的三个统计项目就很容易完成了,最高分肯定是选择4,因此J2单元格公式为:=AGGREGATE(4,,B2:G2)

Excel函数学习之神奇的AGGREGATE,竟可一个抵19个!

四、统计最低分

最低分选择5,K2单元格公式为:=AGGREGATE(5,,B2:G2)

Excel函数学习之神奇的AGGREGATE,竟可一个抵19个!

五、统计实际参考科目

实际参考科目也就是统计数据区域中数字的个数,使用COUNT功能,选择2,因此公式为:=AGGREGATE(2,,B2:G2)

Excel函数学习之神奇的AGGREGATE,竟可一个抵19个!

好了,通过以上五个例子,朋友们对于AGGREGATE的基本用法应该有所掌握,虽然说只用了一个函数就完成了五个函数的工作,相比之前要分别使用五个函数来完成工作提高了一定的效率,但每个公式还是要修改一下才能用。如果能够使用一个公式右拉下拉的话,那才爽呢。(有同感的朋友可以在文末留言哦)

六、五种统计一步到位

对于有这种想法的朋友,应该提出表扬,毕竟我们学习Excel的函数公式,不仅仅是为了完成工作,更加希望能够提高效率。那么有没有可能使用一个公式右拉下拉来完成例子中的五项统计呢?答案是肯定的:有!不过要用到一对函数组合,那就是choose和column。

在揭晓公式之前,先对问题进行简单的分析,在我们使用AGGREGATE完成五项数据统计的公式中,只有第一参数也就是统计方式在发生变化,依次为:1、9、4、5、2。如果要想使用一个公式右拉下拉来完成的话,就得让公式在右拉时第一参数按照这个顺序来进行变化(下拉时不需要变化,因为统计方式相同)。

通常要使用公式右拉得到顺序变化的数据时就会用到column这个函数:

Excel函数学习之神奇的AGGREGATE,竟可一个抵19个!

Column这个函数的作用是得到参数对应的列号,例如column(a1)就得到a1这个单元格的列号也就是1,右拉时由于a1会变成b1、c1……,公式结果就会按照1、2、3……这个顺序变化。

在本例中,我们需要得到的并不是一个很有规律的数列,而是1、9、4、5、2这样一个无序的数列,这时候就要用到choose函数来实现:

Excel函数学习之神奇的AGGREGATE,竟可一个抵19个!

Choose函数的基本格式为:=choose(选择指数,值1,值2,值3……)

Choose函数根据第一个参数的数字来返回参数列表中的值。例如上图,当第一参数为1时,就返回参数列表中的第1个值“1”;当第一参数为2时,就返回参数列表中的第2个值“9”,以此类推,使用column作为choose的第一参数,就可以返回指定的序列了。

以上是对choose和column这对函数组合的说明,现在回到我们的问题,可以用来右拉下拉的这个公式就是:=AGGREGATE(CHOOSE(COLUMN(A1),1,9,4,5,2),,$B2:$G2)

Excel函数学习之神奇的AGGREGATE,竟可一个抵19个!

可能有些新手还是会觉得晕乎乎的,这很正常,相信通过持续地学习,你就可以对这种公式运用自如了。

七、第一参数功能集锦

通过以上介绍,可以看到当我们合理运用了AGGREGATE函数之后,工作效率成倍增长。这个函数的第一参数到底有哪19种功能呢,通过下面这个对照表可以一目了然:

Excel函数学习之神奇的AGGREGATE,竟可一个抵19个!

实际上比较常用的就是那么几种。

八、第二参数功能集锦

接下来我们再来看看第二参数又是什么功能,还是通过一个对照表来直观地了解:

Excel函数学习之神奇的AGGREGATE,竟可一个抵19个!

1.忽略空值

以下通过两个例子看看如何使用第二参数来选择忽略的内容:=AGGREGATE(9,1,B2:B15)

Excel函数学习之神奇的AGGREGATE,竟可一个抵19个!

第一参数选择9,代表求和,第二参数选择1,代表忽略隐藏行,当数据全部显示的时候,使用AGGREGATE函数求和与使用SUM函数的结果一致(第16行总分使用的是SUM函数求和),当我们隐藏其中的某几行数据时,就看到区别了:

Excel函数学习之神奇的AGGREGATE,竟可一个抵19个!

隐藏第4行、第8行、第11行之后,公式=AGGREGATE(9,1,B2:B15)只对当前显示的数据进行了汇总。

说到这里,学过SUBTOTAL函数的同学一定会想到SUBTOTAL也有这样的功能。但是今天出场的AGGREGATE函数比SUBTOTAL函数还要强大,因为面对错误值和分类汇总嵌套时SUBTOTAL无法处理,但AGGREGATE照样搞得定。

2.忽略错误值

今天的最后一个例子,看看遇到错误值的时候会有什么情况:

Excel函数学习之神奇的AGGREGATE,竟可一个抵19个!

如上图所示,各学生的语文成绩是利用vlookup函数从成绩表中获取的(这个函数前面有教程讲过,还不了解的伙伴可以点链接去学习一下:插入链接)。当姓名不在成绩表的时候,就会得到一个错误值,如李四和张三,此时无论我们使用SUM函数或者是SUBTOTAL函数,都无法得到正确的语文成绩总分,只有AGGREGATE可以忽略错误值得到正确结果。当然你可以使用iferror等函数进行处理之后再去用SUM求和,但这并不能掩盖AGGREGATE的强大。

19种统计函数功能加7种忽略项目,这种逆天的整合功能,真的不是一般函数可以比的!AGGREGATE是当之无愧的统计函数之王,快收藏吧!

返回顶部
顶部