實例總結Excel中AGGREGATE函數的八個用法

這篇文章為大家帶來了關於excel的相關知識，其中主要介紹了關於AGGREGATE函數的相關內容，該函數用法與SUBTOTAL函數類似，但在功能上比SUBTOTAL函數更加強大，下面一起來看一下，希望對大家有幫助。

相關學習推薦：excel教學

#AGGREGATE函數用法與SUBTOTAL函數類似，但在功能上比SUBTOTAL函數更強大，不僅可以實現諸如SUM、AVERAGE、COUNT、LARGE、MAX等19個函數的功能，而且還可以忽略隱藏行、錯誤值、空值等，並且支援常數數組。

此函數的第一個參數是1到19之間的數字，用來指定要使用的總和方式：

第二個參數是介於0到7之間的數字，指定在計算區域內要忽略哪些類型的值：

接下來咱們就說說這個函數的一些典型用法：

1、多個不連續區域忽略錯誤值直接求和

這個函數的強大之處就是在於2參可以指定參數來忽略錯誤值直接統計

如下圖，藍色區域中包含有不同的錯誤值，現在要對這幾個不連續的區域求和。

公式為：

=AGGREGATE(9,6,A3:A7,C3:C4,D6:F7)

2、篩選狀態下忽略錯誤值

如下圖，在篩選後的資料區域中包含有錯誤值，如何對可見單元格進行統計呢？

公式為：

=AGGREGATE(9,7,B6:B18)

第一參數使用9，表示求和，第二參數使用7，表示忽略隱藏行和錯誤值。

3、一個公式解決多種統計效果

如下圖，A3:B14單元格區域中是篩選後的的數據，要分別統計在可見區域和所有數據的最大、最小、平均、總和、計數和中位數。

只要一個公式就夠了：

=AGGREGATE({4;5;1;9;3;12},{5,0},B4:B14)

注意是區域陣列公式，先選取c17:d22區域，然後在編輯欄寫上公式，最後按ctrl shift enter三鍵入。

4、向上求和你們都會，就算是篩選下的，往下呢？

=AGGREGATE(9,3,A4:A$18)*2-AGGREGATE(9,7,A4:A$18)

除了向下求和的方向外還有隱藏和錯誤值，這是subtotal sum(if)都無法實現的統計效果

(錄入方法是選取區域定位空值後編輯欄寫完公式ctrl enter批量填充)

5、這條開始才是重點-條件極值統計

這個函數提早五年就實現了2016才有的maxifs和minifs函數的統計效果，而且不需要三鍵。

如下圖，要計算1車間對應的最小值，公式為：

=AGGREGATE(15,6,B4:B15/(A4:A15="1車間" ),1)

公式中的第一參數使用15，表示使用SMALL函數，第二參數使用6，表示忽略錯誤值。要統計的區域是B4:B15/(A4:A15=”1車間”)

A4:A15=”1車間”部分，先對比A列的車間是不是等於指定的條件。如果A4:A15單元格區域中等於”1車間”，就傳回邏輯值TRUE，否則傳回邏輯值FALSE。再用B4:B15除以這組記憶體數組，結果為：

{70;69;87;77;55;46;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;19;47}

最後，AGGREGATE函數忽略裡面的錯誤值，得到第一個最小值。

如果要計算1車間對應的第三個最小值，只需要將最後的1，變成3就好了。

如果要計算1車間對應的最大值，咱們可以修改第一參數，使用14，就是第k個最大值了。

6、 一對多查詢

如果想要一對多查詢，很多人想到的是INDEX SAMLL IF函數的三鍵客組合。其實，用aggregate函數取代也是能實現的。

如下圖，要提取出二車間的所有工號，可以使用以下公式：

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW( $3:$12)/(A$3:A$12=D$3),ROW(A1))),"")

#

這個公式的想法和第五個公式基本上相同。

7、統計同一單元格中的最大值

如下圖，B列多人的考核情況被寫到同一個單元格內，要統計其中的最大值。公式為：

=AGGREGATE(14,6,--MID(B4,ROW($1:$50),COLUMN(A:AZ)),1)

#公式中的MID(B4,ROW($1:$50),COLUMN(A:AZ))部分，使用MID函數，依序從第1~50個字元開始，各提取長度為1~50的字串，得到一個巨長的記憶體數組。再使用兩個負號，把記憶體數組中的文字變成錯誤值，數值仍然是本身的值。

最後使用AGGREGATE函數，忽略記憶體陣列中的錯誤值，計算出其中的第一個最小值。

8、同時統計指定條件的最大最小值

如下圖所示，要同時統計1車間對應的最大值和最小值。

先同時選取F4:G4儲存格，編輯欄輸入以下公式，按Ctrl Shift 回車。

=AGGREGATE({16,15},6,B4:B15/(A4:A15=E4),1)

AGGREGATE第一參數使用常數數組{16,15}，表示分別使用最大值和最小值的計算規則。

最終的結果也是一個記憶體數組，所以要同時選取兩個儲存格輸入。

這個函數的特性在於第一參數為14~19時，可以使用第四參數，此時的第四參數是支援陣列的，因此就能玩出各種應用，來取代不能直接忽略錯誤值的SMALL、LARGE等函數。

相關學習推薦：excel教學

以上是實例總結Excel中AGGREGATE函數的八個用法的詳細內容。更多資訊請關注PHP中文網其他相關文章！