Aggregate — 这个被大多人忽视了的Excel函数,却很有用

我敢打赌,大多数人并不知道Excel中有这个函数。这是一个汇总类函数。这个函数是干什么的呢?实际上差不多它干事情的就是SUM,COUNT,AVERAGE之类函数干的事情,只不过使用Aggregate时对这些函数进行了特性“增强”。

下面我们详细介绍一下这个函数。

1

Aggregate函数介绍

Aggregate最初出现在Excel 2010中。因此,如果你用以前版本的Excel打开时,会返回一个#NAME?的错误值。

Aggregate的语法有两种形式

  1. 引用形式(即引用单元格,下面的公式中,ref1,ref2,即代表单元格区域)
    =AGGREGATE(function_num, options, ref1, [ref2],…)

  2. 数组形式(下面公式中的 array就代表数组)
    =AGGREGATE(function_num, options, array, [optional_argument])

其中,各参数的含义如下:

  • 第一个参数 function_num代表了你希望使用哪个函数就行汇总。下面列出了你可以使用的函数:

    例如,如果你把这个参数值输入为9,就表示你要用Aggregate进行求和运算。

  • 第二个参数options代表了你在进行计算时会忽略哪些值。

    例如,如果这个参数值为6,就在求和时忽略所有错误值。这个参数可以省略,缺省值为0

  • ref1 这个参数是一个单元格区域,是你选择的函数的第一个参数。
    例如,如果你选择的函数是SUM,那么这里ref1就是要求和的区域,例如:A1:A10

  • ref2,... 这是一个可选参数,也代表一个单元格区域,是你选择函数的第二个参数(如果有的话)。
    例如,如果你选择的函数是LARGE,因为LARGE函数有两个参数,所有这里你需要ref2这个参数,例如,可以是2,表示要取第二大的

    又例如,如果你选择的函数是SUM,因为SUM函数不用第二个参数,就可以省略到这个值了。
    需要注意到后面的省略号,表示可以有很多类似ref2的参数,ref3,ref 等等。都是根据你选择的函数是否有对应参数才能决定这里的值,最多可以一直到ref253。

  • array 数组 对应于引用形式的ref1,是你所选择函数的第一个参数,只不过是以数组形式出现的。
    如果你对数组不熟悉,可以忽略这个参数。也可以看后面的例子来熟悉这个用法。

  • optional_argument 对应于引用形式的ref2及后面一直到ref253的参数。

下面列出了需要ref2或者(optional_argument)参数的函数

  • LARGE

  • SMALL

  • PERCENTILE.INC

  • QUARTILE.INC

  • PERCENTILE.EXC

  • QUARTILE.EXC

实际上,这些函数都有两个参数,所有需要ref2。

2

例子

假设我们的数据如下:

如果我们希望在这个数据表中寻找第2大的值,可以用函数:

=LARGE(B3:B15)

结果却是错误值#REF!

此时,你可以使用AGGREGATE函数。

因为我们叫计算第2大的值,所以选择使用LARGE函数,输入14,然后输入第二个参数:

我们需要忽略错误值,可以看到,2,3,6,7都满足条件,你可以根据你的需要输入其中任意一个值。我们只需要忽略错误值,所以可以输入6

现在,来到了第三个参数。由于我们选择的LARGE函数的第一个参数是区域,所以,需要在这里输入(或选择)这个区域:

因为LARGE函数还有第二个参数,所以我们需要ref2,这里输入2,表示选择第二大的值。

3

使用数组形式

上面的例子,可以使用数组形式的公式:
=AGGREGATE(14,6,{1,2,3,4,5},2)

关于AGGREGATE函数我们就介绍到这里。如果你有希望了解学习的函数,就在下面留言。我们会尽快安排讲解。

觉得好看点个【在看】再走吧

(0)

相关推荐