AGGREGATE函數(shù)返回列表或數(shù)據(jù)庫(kù)中的合計(jì)。AGGREGATE函數(shù)消除了條件格式的限制，如果區(qū)域中存在錯(cuò)誤，則數(shù)據(jù)條、圖標(biāo)集和色階將無(wú)法顯示條件格式。這是因?yàn)楫?dāng)計(jì)算區(qū)域存在錯(cuò)誤時(shí)，MIN、MAX和PERCENTILE函數(shù)不進(jìn)行計(jì)算。同樣，LARGE、SMALL和STDEVP函數(shù)也會(huì)影響某些條件格式規(guī)則的相應(yīng)功能。通過(guò)使用AGGREGATE函數(shù)，將忽略這些錯(cuò)誤。AGGREGATE函數(shù)可以將不同的聚合函數(shù)應(yīng)用于列表或數(shù)據(jù)庫(kù)，并提供忽略隱藏行和錯(cuò)誤值的選項(xiàng)。

圖1

什么情況下使用AGGREGATE函數(shù)?

AGGREGATE函數(shù)是在Excel 2010中引入的一個(gè)非常強(qiáng)大的函數(shù)，可以對(duì)列表或數(shù)據(jù)庫(kù)應(yīng)用不同的聚合函數(shù)并忽略隱藏行和錯(cuò)誤值。它能夠：

• 計(jì)算最大值、最小值、中值等

• （不用數(shù)組公式）計(jì)算滿(mǎn)足多條件的數(shù)據(jù)最大值

• （不用數(shù)組公式）提取滿(mǎn)足多個(gè)條件的數(shù)據(jù)

AGGREGATE函數(shù)語(yǔ)法

AGGREGATE函數(shù)有兩種形式，一種是引用形式，一種是數(shù)組形式。其引用形式語(yǔ)法如下：

• AGGREGATE(function_num,options,ref1,ref2,[ref3],…)

其數(shù)組形式語(yǔ)法如下：

• AGGREGATE(function_num,options,array,[k])

• function_num: 必需。一個(gè)在1至19之間的數(shù)字，指定要使用的函數(shù)，其中：1為AVERAGE函數(shù)，2為COUNT函數(shù)，3為COUNTA函數(shù)，4為MAX函數(shù)，5為MIN函數(shù)，6為PRODUCT函數(shù)，7為STDEV.S函數(shù)，8為STDEV.P函數(shù)，9為SUM函數(shù)，10為VAR.S函數(shù)，11為VAR.P函數(shù)，12為MEDIAN函數(shù)，13為MODE.SNGL函數(shù)，14為LARGE函數(shù)，15為SMALL函數(shù)，16為PERCENTILE.INC函數(shù)，17為QUARTILE.INC函數(shù)，18為PERCENTILE.EXC函數(shù)，19為QUARTILE.EXC函數(shù)。

• options: 必需。一個(gè)數(shù)值，確定在函數(shù)的計(jì)算區(qū)域內(nèi)要忽略的值。其中：0或者省略，忽略嵌套SUBTOTAL和AGGREGATE函數(shù)；1指定忽略隱藏行、嵌套SUBTOTAL和AGGREGATE函數(shù)；2指定忽略錯(cuò)誤值、嵌套SUBTOTAL和AGGREGATE函數(shù)；3指定忽略隱藏行、錯(cuò)誤值、嵌套SUBTOTAL和AGGREGATE函數(shù)；4指定忽略空值；5指定忽略隱藏行；6指定忽略錯(cuò)誤值；7指定忽略隱藏行和錯(cuò)誤值。

• ref1:必需。函數(shù)的第一個(gè)數(shù)值參數(shù)。對(duì)于使用數(shù)組的函數(shù)，可以是一個(gè)數(shù)組或者數(shù)組公式，也可以是對(duì)要為其計(jì)算聚合值的單元格區(qū)域的引用。

• ref2: 對(duì)于某些函數(shù)必需，例如LARGE(array,k)、SMALL(array,k)、PERCENTILE.INC(array,k)、QUARTILE.INC(array,quart)、PERCENTILE.EXC(array,k)、QUARTILE.EXC(array,quart)。

AGGREGATE函數(shù)陷阱

如果第二個(gè)引用參數(shù)是必需的但未提供，那么AGGREGATE將返回錯(cuò)誤值#VALUE!。如果有一個(gè)或多個(gè)引用是三維引用，那么AGGREGATE將返回錯(cuò)誤值#VALUE!。如果在Excel 2007或之前的版本打開(kāi)AGGREGATE工作簿將返回#NAME?。

如果ref1,ref2,…中有其他AGGREGATE（或嵌套AGGREGATE），將忽略這些嵌套AGGREGATE，避免重復(fù)計(jì)算。如果AGGREGATE函數(shù)的引用中包含SUBTOTAL，那么將忽略這些SUBTOTAL。如果SUBTOTAL函數(shù)中包含AGGREGATE，那么將忽略這些AGGREGATE。

AGGREGATE函數(shù)適用于數(shù)據(jù)列或垂直區(qū)域，不適用于數(shù)據(jù)行或水平區(qū)域。

AGGREGATE函數(shù)僅適用于2010及其后的版本。參數(shù)function_num指定的函數(shù)代號(hào)中，1至13不能處理數(shù)組操作，14至19可以處理數(shù)組操作。例如，5代表MIN函數(shù)，但不能用于獲取最小值的數(shù)組運(yùn)算，可以使用15代表的SMALL函數(shù)來(lái)獲取最小值。

示例1: 計(jì)算最大值、最小值、中值等

在下圖2所示的工作表，數(shù)據(jù)單元格區(qū)域?yàn)?/span>A1:B11，其中單元格A1和A4中含有錯(cuò)誤值。使用AGGREGATE函數(shù)來(lái)計(jì)算最大值、最小值、中值等。

圖2

示例2: 計(jì)算滿(mǎn)足多條件的數(shù)據(jù)最大值

如下圖3所示的工作表，在單元格區(qū)域A1:C12中是不同超市的水果銷(xiāo)售數(shù)據(jù)?，F(xiàn)在想要知道，除中心超市外，榴蓮和蘋(píng)果在其他超市的最大銷(xiāo)量。在單元格A16和A17中列出了水果名，在B16中的公式為：

=AGGREGATE(14,6,$C$2:$C$12/(($A$2:$A$12<>$E$2)*($B$2:$B$12=A16)),1)

向下復(fù)制到單元格B17。

圖3

示例3: 提取滿(mǎn)足多個(gè)條件的數(shù)據(jù)

如圖4所示，在單元格區(qū)域A4:D14中是數(shù)據(jù)，在單元格區(qū)域B1:D2中設(shè)置了條件，要從A4:D14中提取滿(mǎn)足B1:D2條件的數(shù)據(jù)并放置到單元格F5開(kāi)始的區(qū)域中。本例中，要提取張三在2017年5月1日至2017年12月1日之間的銷(xiāo)售數(shù)據(jù)。

圖4

在單元格F5中的公式為：

=IFERROR(INDEX(A$5:A$14,AGGREGATE(15,6,(ROW($A$5:$A$14)-ROW($A$5) 1)/(($A$5:$A$14>=$B$2)*($A$5:$A$14<=$C$2)*($C$5:$C$14=$D$2)),ROWS(F$5:F5))),'')

拖動(dòng)公式單元格至I5，然后向下拖動(dòng)至沒(méi)有數(shù)據(jù)即可。

本文屬原創(chuàng)文章，轉(zhuǎn)載請(qǐng)注明出處。

歡迎在下面留言，完善本文內(nèi)容，讓更多的人學(xué)到更完美的知識(shí)。