怎么使用大牛函數(shù):AGGREGATE
發(fā)布時(shí)間:2022-11-03 08:51 [ 我要自學(xué)網(wǎng)原創(chuàng) ] 發(fā)布人: 秋文-27173 閱讀: 2461

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)要忽略哪些類型的值:


接下來(lái)咱們就說(shuō)說(shuō)這個(gè)函數(shù)的一些典型用法:

 

1、多個(gè)不連續(xù)區(qū)域忽略錯(cuò)誤值直接求和

這個(gè)函數(shù)的強(qiáng)大之處就是在于2參可以指定參數(shù)來(lái)忽略錯(cuò)誤值直接統(tǒng)計(jì)
如下圖,藍(lán)色區(qū)域中包含有不同的錯(cuò)誤值,現(xiàn)在要對(duì)這幾個(gè)不連續(xù)的區(qū)域求和。
公式為:
=AGGREGATE(9,6,A3:A7,C3:C4,D6:F7)


2、篩選狀態(tài)下忽略錯(cuò)誤值

如下圖,在篩選后的數(shù)據(jù)區(qū)域中包含有錯(cuò)誤值,如何對(duì)可見(jiàn)單元格進(jìn)行統(tǒng)計(jì)呢?
公式為:
=AGGREGATE(9,7,B6:B18)


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

 

3、一個(gè)公式解決多種統(tǒng)計(jì)效果

如下圖,A3:B14單元格區(qū)域中是篩選后的的數(shù)據(jù),要分別統(tǒng)計(jì)在可見(jiàn)區(qū)域和所有數(shù)據(jù)的最大、最小、平均、總和、計(jì)數(shù)和中位數(shù)。
只要一個(gè)公式就夠了:
=AGGREGATE({4;5;1;9;3;12},{5,0},B4:B14)


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

 

4、向上求和你們都會(huì),哪怕是篩選下的,向下呢?

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


除了向下求和的方向外還有隱藏和錯(cuò)誤值,這是subtotal+sum(if)都無(wú)法實(shí)現(xiàn)的統(tǒng)計(jì)效果
(錄入方法是選取區(qū)域定位空值后編輯欄寫(xiě)完公式ctrl+enter批量填充)

 

5、這條開(kāi)始才是重點(diǎn)-條件極值統(tǒng)計(jì)

這個(gè)函數(shù)提早五年就實(shí)現(xiàn)了2016才有的maxifs和minifs函數(shù)的統(tǒng)計(jì)效果,而且不需要三鍵。
如下圖,要計(jì)算1車間對(duì)應(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車間”部分,先對(duì)比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車間對(duì)應(yīng)的第三個(gè)最小值,只需要將最后的1,變成3就好了。
如果要計(jì)算1車間對(duì)應(yīng)的最大值,咱們可以修改一下第一參數(shù),使用14,就是第k個(gè)最大值了。

 

6、 一對(duì)多查詢

如果想要一對(duì)多查詢,很多人想到的是INDEX+SAMLL+IF函數(shù)的三鍵客組合。其實(shí),用aggregate函數(shù)替代也是能實(shí)現(xiàn)的。
如下圖,要提取出二車間的所有工號(hào),可以使用以下公式:
=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($3:$12)/(A$3:A$12=D$3),ROW(A1))),"")


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

 

7、統(tǒng)計(jì)同一單元格中的最大值

如下圖,B列多人的考核情況被寫(xiě)到同一個(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è)字符處開(kāi)始,各提取長(zhǎng)度為1~50的字符串,得到一個(gè)巨長(zhǎng)的內(nèi)存數(shù)組。再使用兩個(gè)負(fù)號(hào),把內(nèi)存數(shù)組中的文本變成錯(cuò)誤值,數(shù)值仍然是其本身的值。
最后使用AGGREGATE函數(shù),忽略內(nèi)存數(shù)組中的錯(cuò)誤值,計(jì)算出其中的第一個(gè)最小值。

 

8、同時(shí)統(tǒng)計(jì)指定條件的最大最小值

如下圖所示,要同時(shí)統(tǒng)計(jì)1車間對(duì)應(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)用,來(lái)替代不能直接忽略錯(cuò)誤值的SMALL、LARGE等函數(shù)。

Excel2019視頻教程
我要自學(xué)網(wǎng)商城 ¥80 元
進(jìn)入購(gòu)買
文章評(píng)論
0 條評(píng)論 按熱度排序 按時(shí)間排序 /350
添加表情
遵守中華人民共和國(guó)的各項(xiàng)道德法規(guī),
承擔(dān)因您的行為而導(dǎo)致的法律責(zé)任,
本站有權(quán)保留或刪除有爭(zhēng)議評(píng)論。
參與本評(píng)論即表明您已經(jīng)閱讀并接受
上述條款。
V
特惠充值
聯(lián)系客服
APP下載
官方微信
返回頂部
分類選擇:
電腦辦公 平面設(shè)計(jì) 室內(nèi)設(shè)計(jì) 室外設(shè)計(jì) 機(jī)械設(shè)計(jì) 工業(yè)自動(dòng)化 影視動(dòng)畫(huà) 程序開(kāi)發(fā) 網(wǎng)頁(yè)設(shè)計(jì) 會(huì)計(jì)課程 興趣成長(zhǎng) AIGC