Aggregate函數是 Excel 2010 新增的函數之一，它用于忽略錯誤值、空值、隱藏行列、SubTotal函數分類匯總求和、求平均值、統計單元格個數、查找最大值、最小值等。Aggregate函數分為引用形式和數組形式，它們支持 19 個函數；引用區域至少有一個，最多只能有 253 個；數組形式只支持 6 個函數，并且不能少 k 參數。

Aggregate函數的主要作用就是忽略錯誤值、空值、隱藏行列，如果用相應的函數求和、求平均值等不能達到此目的，可以使用Aggregate函數，這樣不但便于寫公式并且使問題變得簡單。

一、Excel Aggregate函數語法

（一）引用形式

表達式：AGGREGATE(Function_Num, Options, Ref1, [Ref2], …)

中文表達式：Aggregate(函數序號, 忽略選項, 引用區域1, [引用區域2], …)

（二）數組形式

表達式：AGGREGATE(Function_Num, Options, Array, [k])

中文表達式：Aggregate(函數序號, 忽略選項, 數組, [第幾個])

（三）說明：

1、Function_Num 為函數序號，它的取值范圍為 1-19，每個數字代表一個函數，具體如下：

Function_num　　　　函數
1　　　　　　AVERAGE
2　　　　　　COUNT
3　　　　　　COUNTA
4　　　　　　MAX
5　　　　　　MIN
6　　　　　PRODUCT
7　　　　　STDEV.S
8　　　　　STDEV.P
9　　　　　　SUM
10　　　　　VAR.S
11　　　　　VAR.P
12　　　　　MEDIAN
13　　　　　MODE.SNGL
14　　　　　LARGE
15　　　　　SMALL
16　　　　　PERCENTILE.INC
17　　　　　QUARTILE.INC
18　　　　　PERCENTILE.EXC
19　　　　　QUARTILE.EXC

2、Options 為忽略選項，共有 0 - 7 八個取值，具體如下：

0 或省略：忽略嵌套的 Subtotal 函數和 Aggregate 函數；

1：忽略隱藏行、嵌套的 Subtotal 函數和 Aggregate 函數；

2：忽略錯誤值、嵌套的 Subtotal 函數和 Aggregate 函數；

3：忽略隱藏行、錯誤值、嵌套的 Subtotal 函數和 Aggregate 函數；

4：忽略空值；

5：忽略隱藏行；

6：忽略錯誤值；

7：忽略隱藏行和錯誤值。

3、Ref 至少有一個，最多只能有 253 個；如果引用了三維單元格，將返回值錯誤 #VALUE!。

4、如果使用Aggregate函數的數組形式，一些函數不能省略參數 K，這些函數有 Large(Array, k)、Small(Array, k)、Percentile.inc(array, k)、Quartile.inc(Array, Quart)、Percentile.exc(Array, k)、Quartile.exc(Array, Quart)，如果省略了參數 K，將返回值錯誤#VALUE!。

5、用Aggregate函數進行分類匯總時，隱藏的列仍然會被計入匯總，而隱藏的行不被計匯總。

二、Excel Aggregate函數的使用方法及實例

（一）忽略嵌套的 Subtotal 函數實例

1、雙擊 E9 單元格，把公式 =SUBTOTAL(9,E2:E8) 復制到 E9，按回車，返回求和結果 5028；再次雙擊 E9 單元格，把公式 =AGGREGATE(9,0,E2:E9) 復制到 E10，按回車，返回求和結果 5028；操作過程步驟，如圖1所示：

圖1

2、公式 =SUBTOTAL(9,E2:E8) 是對 E2:E8 求和，公式 =AGGREGATE(9,0,E2:E9) 是對 E2:E9 求和，兩公式返回結果一樣，說明當Aggregate函數把 Options 參數設置為 0 時忽略 Subtotal 函數的求和結果。

（二）忽略空值的實例

1、雙擊 E9 單元格，把公式 =AGGREGATE(9,4,E2:E8) 復制到 E9，按回車，返回求和結果 5028；選中 E6，按 Delete 鍵，把數值刪除，E9 中的值變為 4266；操作過程步驟，如圖2所示：

圖2

2、當把 E6 中的數值刪除后，求和結果變小，說明把參數 Options 設置為 4 時，Aggregate函數會忽略空值。

（三）忽略隱藏行但包含隱藏列實例

1、雙擊 E9 單元格，把公式 =AGGREGATE(2,5,D2:E8) 復制到 E9，按回車，返回統計結果 14；右鍵第二行行號 2，在彈出的菜單中選擇“隱藏”，則第二行被隱藏，E9 中的統計結果隨之變為 12；右鍵 D 列列號 D，在彈出的菜單選擇“隱藏”，把 D 列隱藏，E9 中的值仍是12；操作過程步驟，如圖3所示：

圖3

2、公式 =AGGREGATE(2,5,D2:E8) 中 2 代表統計數值單元格數目函數 Count，5 表示忽略空值；當隱藏第二行后，統計結果減少兩個，說明把參數 Options 設置為 5，Aggrerate函數會忽略隱藏行；當隱藏列后，統計結果不變，說明Aggrerate函數包含隱藏列。

（四）忽略錯誤值實例

1、假如要對營業額求和。雙擊 G11 單元格，把公式 =AGGREGATE(9,6,G2:G10) 復制到 G11，按回車，返回求和結果 2682664.1；雙擊G12，輸入公式 =SUMD(G2:G10)，按回車，返回值錯誤 #VALUE!，再次雙擊 G12，把 G2 改為 G3，按回車，同樣返回求和結果 2682664.1；操作過程步驟，如圖4所示：

圖4

2、公式 =AGGREGATE(9,6,G2:G10) 中 6 表示忽略錯誤值，求和區域為 G2:G10，其中 G2 為錯誤，但被忽略；改用 Sum函數，則返回值錯誤 #VALUE!，只有不包含錯誤值才能返回正確結果。

提示：其它函數（如求平均值函數 Average、統計函數 Count、CountA、乘除法函數 Product、最大值函數 Max、最小值函數Min 等）也支持忽略錯誤值計算。

（五）需要參數 K 的函數省略后返回錯誤實例

1、假如要返回第三個銷量最小值。雙擊 E9 單元格，把公式 =AGGREGATE(15,3,E2:E8,3) 復制到 E9，按回車，返回 638，它正好是第三個銷量最小值；再次雙擊 E9 單元格，把“,3”去掉，按回車，返回值錯誤 #VALUE!；操作過程步驟，如圖5所示：

圖5

2、公式 =AGGREGATE(15,3,E2:E8,3) 中 15 表示 Small函數，參數 Options（3）表示“忽略隱藏行、錯誤值、嵌套的 Subtotal 函數和Aggregate 函數”，參數 k（3）表示返回第三個最小值；當把 k（3）刪除后，返回 #VALUE!，說明 Small函數不能省略 k。

三、Excel 用Aggregate函數數組形式求平均值

1、假如有營業額和價格，要求根據它們求平均銷量。雙擊 E9 單元格，把公式 =AGGREGATE(1,6,E2:E8/D2:D8) 復制到 E9，按回車，返回值錯誤 #VALUE!；再次雙擊 E9 單元格，把公式改為 =AGGREGATE(14,6,E2:E8/D2:D8,2)，按回車，返回服裝銷量第二個最大值 897；雙擊 E10，把公式 =AVERAGE(IFERROR(E2:E8/D2:D8,FALSE)) 復制到 E10，按 Ctrl + Shift + 回車，返回服裝平均銷量 721.60；再次雙擊 E10，把公式中 FALSE 改為 0，按 Ctrl + Shift + 回車，返回服裝平均銷量 515.43；操作過程步驟，如圖6所示：

圖6

2、公式說明：

A、公式 =AGGREGATE(1,6,E2:E8/D2:D8) 中 1 表示求平均值，6 表示忽略錯誤值，E2:E8/D2:D8 表示用 E2:E8 中每個元素除以 D2:D8 中對應的元素并以數組形式返回結果；公式的本意為：把 E2:E8/D2:D8 返回的數組求平均值，但Aggregate函數不支持Average函數的數組形式，因此返回值錯誤 #VALUE!。

B、公式 =AGGREGATE(14,6,E2:E8/D2:D8,2) 中 14 表示求最大值函數 Large，最后一個參數 2 表示返回第二個最大值；由于Aggregate函數支持Large函數的數組形式，因此能返回正確值。由此可知，要用數組形式，必須用支持數組形式的函數，即函數序號為 14-19 的函數。

C、公式 =AVERAGE(IFERROR(E2:E8/D2:D8,FALSE)) 中 IFERROR(E2:E8/D2:D8,FALSE) 用于判斷 E2:E8 中每個元素除以 D2:D8 對應的元素，如果出錯，返回 FALSE，否則返回相除的結果；例如第一次用 E2/D2，返回結果 329，第二次 E3/D3 發生錯誤，返回 FALSE，其它的以此類推，最后返回數組 {329;FALSE;638;FALSE;762;982;897}；則公式變為 =AVERAGE({329;FALSE;638;FALSE;762;982;897})，最后求平均值，返回 721.60，FALSE 不參與求平均值，即不包含 E2:E8 和 D2:D8 中為文本的單元格。

D、公式 =AVERAGE(IFERROR(E2:E8/D2:D8,0)) 把 FALSE 改為 0 后，區別在于 IFERROR(E2:E8/D2:D8,0) 返回的數組中 FALSE 變為 0，即 {329;0;638;0;762;982;897}，則公式變為 =AVERAGE({329;0;638;0;762;982;897})，返回求平均值結果 515.43，0 參與求平均值，即包含E2:E8 和 D2:D8 中為文本的單元格。