實(shí)例總結(jié)Excel中AGGREGATE函數(shù)的八個(gè)用法（sql aggregate function）

手機(jī)如何做表格：點(diǎn)擊查看

AGGREGATE函數(shù)用法與SUBTOTAL函數(shù)類似，但在功能上比SUBTOTAL函數(shù)更加強(qiáng)大，不僅可以實(shí)現(xiàn)諸如SUM、AVERAGE、COUNT、LARGE、MAX等19個(gè)函數(shù)的功能，而且還可以忽略隱藏行、錯(cuò)誤值、空值等，并且支持常量數(shù)組。

該函數(shù)的第一參數(shù)是1到19之間的數(shù)字，用于指定要使用的匯總方式：

第二參數(shù)是介于0到7之間的數(shù)字，指定在計(jì)算區(qū)域內(nèi)要忽略哪些類型的值：

接下來咱們就說說這個(gè)函數(shù)的一些典型用法：

這個(gè)函數(shù)的強(qiáng)大之處就是在于2參可以指定參數(shù)來忽略錯(cuò)誤值直接統(tǒng)計(jì)

如下圖，藍(lán)色區(qū)域中包含有不同的錯(cuò)誤值，現(xiàn)在要對這幾個(gè)不連續(xù)的區(qū)域求和。

公式為：

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

如下圖，在篩選后的數(shù)據(jù)區(qū)域中包含有錯(cuò)誤值，如何對可見單元格進(jìn)行統(tǒng)計(jì)呢？

公式為：

=AGGREGATE(9,7,B6:B18)

第一參數(shù)使用9，表示求和，第二參數(shù)使用7，表示忽略隱藏行和錯(cuò)誤值。

如下圖，A3:B14單元格區(qū)域中是篩選后的的數(shù)據(jù)，要分別統(tǒng)計(jì)在可見區(qū)域和所有數(shù)據(jù)的最大、最小、平均、總和、計(jì)數(shù)和中位數(shù)。

只要一個(gè)公式就夠了：

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

注意是區(qū)域數(shù)組公式，先選取c17:d22區(qū)域，然后在編輯欄寫上公式，最后按ctrl+shift+enter三鍵錄入。

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

除了向下求和的方向外還有隱藏和錯(cuò)誤值，這是subtotal+sum(if)都無法實(shí)現(xiàn)的統(tǒng)計(jì)效果

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

這個(gè)函數(shù)提早五年就實(shí)現(xiàn)了2016才有的maxifs和minifs函數(shù)的統(tǒng)計(jì)效果，而且不需要三鍵。

如下圖，要計(jì)算1車間對應(yīng)的最小值，公式為：

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

公式中的第一參數(shù)使用15，表示使用SMALL函數(shù)，第二參數(shù)使用6，表示忽略錯(cuò)誤值。要統(tǒng)計(jì)的區(qū)域是B4:B15/(A4:A15=”1車間”)

A4:A15=”1車間”部分，先對比A列的車間是不是等于指定的條件。如果A4:A15單元格區(qū)域中等于”1車間”，就返回邏輯值TRUE，否則返回邏輯值FALSE。然后再用B4:B15除以這組內(nèi)存數(shù)組，結(jié)果為：

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

最后，AGGREGATE函數(shù)忽略里面的錯(cuò)誤值，得到第一個(gè)最小值。

如果要計(jì)算1車間對應(yīng)的第三個(gè)最小值，只需要將最后的1，變成3就好了。

如果要計(jì)算1車間對應(yīng)的最大值，咱們可以修改一下第一參數(shù)，使用14，就是第k個(gè)最大值了。

如果想要一對多查詢，很多人想到的是INDEX+SAMLL+IF函數(shù)的三鍵客組合。其實(shí)，用aggregate函數(shù)替代也是能實(shí)現(xiàn)的。

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

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

這個(gè)公式的思路和第五個(gè)公式基本相同。

如下圖，B列多人的考核情況被寫到同一個(gè)單元格內(nèi)，要統(tǒng)計(jì)其中的最大值。公式為：

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

公式中的MID(B4,ROW($1:$50),COLUMN(A:AZ))部分，使用MID函數(shù)，依次從第1~50個(gè)字符處開始，各提取長度為1~50的字符串，得到一個(gè)巨長的內(nèi)存數(shù)組。再使用兩個(gè)負(fù)號，把內(nèi)存數(shù)組中的文本變成錯(cuò)誤值，數(shù)值仍然是其本身的值。

最后使用AGGREGATE函數(shù)，忽略內(nèi)存數(shù)組中的錯(cuò)誤值，計(jì)算出其中的第一個(gè)最小值。

如下圖所示，要同時(shí)統(tǒng)計(jì)1車間對應(yīng)的最大和最小值。

先同時(shí)選中F4:G4單元格，編輯欄輸入以下公式，按Ctrl+Shift+回車。

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

AGGREGATE第一參數(shù)使用常量數(shù)組{16,15}，表示分別使用最大值和最小值的計(jì)算規(guī)則。

最終的結(jié)果也是一個(gè)內(nèi)存數(shù)組，所以要同時(shí)選中兩個(gè)單元格輸入。

這個(gè)函數(shù)的特性在于第一參數(shù)為14~19時(shí)，可以使用第四參數(shù)，此時(shí)的第四參數(shù)是支持?jǐn)?shù)組的，因此就能玩出各種應(yīng)用，來替代不能直接忽略錯(cuò)誤值的SMALL、LARGE等函數(shù)。

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

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