Excel用Aggregate函數(shù)忽略錯誤值空值求和求平均值及其數(shù)組形式的用法

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

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

一、Excel Aggregate函數(shù)語法

（一）引用形式

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

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

（二）數(shù)組形式

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

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

（三）說明：

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

Function_num　　　　函數(shù)
　　　　　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 函數(shù)和 Aggregate 函數(shù)；

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

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

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

4：忽略空值；

5：忽略隱藏行；

6：忽略錯誤值；

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

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

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

5、用Aggregate函數(shù)進行分類匯總時，隱藏的列仍然會被計入?yún)R總，而隱藏的行不被計匯總。

二、Excel Aggregate函數(shù)的使用方法及實例

（一）忽略嵌套的 Subtotal 函數(shù)實例

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函數(shù)把 Options 參數(shù)設置為 0 時忽略 Subtotal 函數(shù)的求和結果。

（二）忽略空值的實例

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

圖2

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

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

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

圖3

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

（四）忽略錯誤值實例

1、假如要對營業(yè)額求和。雙擊 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 表示忽略錯誤值，求和區(qū)域為 G2:G10，其中 G2 為錯誤，但被忽略；改用 Sum函數(shù)，則返回值錯誤 #VALUE!，只有不包含錯誤值才能返回正確結果。

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

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

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

圖5

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

三、Excel 用Aggregate函數(shù)數(shù)組形式求平均值

1、假如有營業(yè)額和價格，要求根據(jù)它們求平均銷量。雙擊 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 中對應的元素并以數(shù)組形式返回結果；公式的本意為：把 E2:E8/D2:D8 返回的數(shù)組求平均值，但Aggregate函數(shù)不支持Average函數(shù)的數(shù)組形式，因此返回值錯誤 #VALUE!。

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

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

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

版權聲明：本文內容由網(wǎng)絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內刪除侵權內容。