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

wufei123 2024-05-24 阅读:8 评论:0

在之前的文章《实用excel技巧分享：利用“查找替换”进行日期数据筛选》中，我们了解了几种“查找替换”的实用操作。而今天我们来聊聊一种神奇的excel统计函数，它竟然可以一个抵19个，简直是神器呀！赶紧收藏起来。今天要和大家介绍的这个函...

在之前的文章《实用excel技巧分享：利用“查找替换”进行日期数据筛选》中，我们了解了几种“查找替换”的实用操作。而今天我们来聊聊一种神奇的excel统计函数，它竟然可以一个抵19个，简直是神器呀！赶紧收藏起来。

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

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

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

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

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

一、统计平均分

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

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

可以看到，结果完全一致！

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

二、统计总分

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

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

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

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

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

三、统计最高分

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

四、统计最低分

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

五、统计实际参考科目

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

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

六、五种统计一步到位

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

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

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

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

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

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)

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

七、第一参数功能集锦

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

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

八、第二参数功能集锦

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

1.忽略空值

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

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

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

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

2.忽略错误值

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

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

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