Excel函數(shù)學習41：AGGREGATE函數(shù)

L羅樂 2018-01-11

展開全文

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

圖1

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

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

計算最大值、最小值、中值等
（不用數(shù)組公式）計算滿足多條件的數(shù)據(jù)最大值
（不用數(shù)組公式）提取滿足多個條件的數(shù)據(jù)

AGGREGATE函數(shù)語法

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

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

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

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

function_num: 必需。一個在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: 必需。一個數(shù)值，確定在函數(shù)的計算區(qū)域內(nèi)要忽略的值。其中：0或者省略，忽略嵌套SUBTOTAL和AGGREGATE函數(shù)；1指定忽略隱藏行、嵌套SUBTOTAL和AGGREGATE函數(shù)；2指定忽略錯誤值、嵌套SUBTOTAL和AGGREGATE函數(shù)；3指定忽略隱藏行、錯誤值、嵌套SUBTOTAL和AGGREGATE函數(shù)；4指定忽略空值；5指定忽略隱藏行；6指定忽略錯誤值；7指定忽略隱藏行和錯誤值。
ref1:必需。函數(shù)的第一個數(shù)值參數(shù)。對于使用數(shù)組的函數(shù)，可以是一個數(shù)組或者數(shù)組公式，也可以是對要為其計算聚合值的單元格區(qū)域的引用。
ref2: 對于某些函數(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ù)陷阱

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

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

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

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

示例1: 計算最大值、最小值、中值等

在下圖2所示的工作表，數(shù)據(jù)單元格區(qū)域為A1:B11，其中單元格A1和A4中含有錯誤值。使用AGGREGATE函數(shù)來計算最大值、最小值、中值等。

圖2

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

如下圖3所示的工作表，在單元格區(qū)域A1:C12中是不同超市的水果銷售數(shù)據(jù)。現(xiàn)在想要知道，除中心超市外，榴蓮和蘋果在其他超市的最大銷量。在單元格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: 提取滿足多個條件的數(shù)據(jù)

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