一個(gè)抵19個(gè)的統(tǒng)計(jì)函數(shù)之王AGGREGATE
?
作者:老菜鳥來源:部落窩教育發(fā)布時(shí)間:2018-05-25 09:39:29點(diǎn)擊:6554
小編有話說:小編以前處理數(shù)據(jù)的時(shí)候,都是背了一大堆的公式,然后再各種嵌套呀五花八門的,忙的自己暈頭轉(zhuǎn)向不說,數(shù)據(jù)還容易出錯(cuò),今天看到作者這篇文章,竟然一個(gè)函數(shù)頂替19個(gè),簡(jiǎn)直是神器呀!趕緊收藏起來。
今天要和大家介紹的這個(gè)函數(shù)叫做AGGREGATE。雖說是Excel 2010中就有的函數(shù),可是知道這個(gè)函數(shù)的人真沒多少,這是一件非常遺憾的事情,因?yàn)?span>AGGREGATE函數(shù)不僅可以實(shí)現(xiàn)諸如SUM、AVERAGE、COUNT、LARGE等19個(gè)函數(shù)的功能,而且還可以忽略隱藏行、錯(cuò)誤值、空值等。如果區(qū)域中包含錯(cuò)誤值,SUM等函數(shù)將返回錯(cuò)誤,這時(shí)用 AGGREGATE函數(shù)就非常方便了。
光說不練假把式,下面就看看AGGREGATE的本領(lǐng)。
我們用一個(gè)成績(jī)表來說明AGGREGATE的基本用法,數(shù)據(jù)源如圖所示:
每個(gè)學(xué)生參加六項(xiàng)測(cè)試,根據(jù)成績(jī)來得出藍(lán)色區(qū)域的五項(xiàng)統(tǒng)計(jì)內(nèi)容,相信對(duì)于大多數(shù)朋友來說,要完成這個(gè)表格并不難,無非就是掌握幾個(gè)最基礎(chǔ)的函數(shù):AVERAGE(平均分)、SUM(總分)、MAX(最高分)、MIN(最低分)和COUNT(實(shí)際參考科目)分別對(duì)五項(xiàng)內(nèi)容進(jìn)行統(tǒng)計(jì)??赡芤灿行┬屡笥堰€不了解以上提到的這五個(gè)函數(shù),那么正好,你只需要學(xué)習(xí)AGGREGATE這一個(gè)函數(shù)就可以實(shí)現(xiàn)上面這些數(shù)據(jù)的統(tǒng)計(jì)。
AGGREGATE的基本格式為:= AGGREGATE(統(tǒng)計(jì)功能,忽略哪些值,數(shù)據(jù)區(qū)域),以下分別來看看如何完成例子中的五項(xiàng)統(tǒng)計(jì)內(nèi)容。
一、統(tǒng)計(jì)平均分
當(dāng)前表格平均分統(tǒng)計(jì)公式為:=AGGREGATE(1,,B2:G2)。在H2單元格中輸入公式再整列填充公式即可獲得各學(xué)生的平均分。
說明:當(dāng)統(tǒng)計(jì)功能為1的時(shí)候,函數(shù)實(shí)現(xiàn)計(jì)算平均值的功能。本例中我們并不需要指定忽略統(tǒng)計(jì)的數(shù)據(jù),因此第二參數(shù)可以省略(此處寫了兩個(gè)逗號(hào),中間省略了一個(gè)參數(shù)),最后一個(gè)參數(shù)就是要計(jì)算的數(shù)據(jù)區(qū)域B2:G2,函數(shù)用法非常簡(jiǎn)單,那么結(jié)果是否正確呢?不妨使用AVERAGE函數(shù)來驗(yàn)證一下:
可以看到,結(jié)果完全一致!
接下來我們?cè)倏纯慈绾斡?span>AGGREGATE函數(shù)統(tǒng)計(jì)總分。
二、統(tǒng)計(jì)總分
當(dāng)前表格總分統(tǒng)計(jì)公式為:=AGGREGATE(9,,B2:G2)。在I2單元格中輸入公式再整列填充公式即可獲得各學(xué)生總分。
只需要將第一個(gè)參數(shù)改為9即可,因?yàn)?span>9對(duì)應(yīng)的就是求和功能。
說到這里,可能會(huì)有些朋友擔(dān)心,第一個(gè)參數(shù)里1代表平均值, 9代表求和,這個(gè)函數(shù)一共有19個(gè)功能,會(huì)不會(huì)很難記住。
實(shí)際上完全不需要有這種擔(dān)心,Excel為我們提供了非常智能的提醒功能,當(dāng)我們輸入函數(shù)之后,就有對(duì)應(yīng)參數(shù)功能的選項(xiàng):
只要對(duì)照這個(gè)提示,選擇自己需要的功能即可。
三、統(tǒng)計(jì)最高分
了解這個(gè)功能以后,最后的三個(gè)統(tǒng)計(jì)項(xiàng)目就很容易完成了,最高分肯定是選擇4,因此J2單元格公式為:=AGGREGATE(4,,B2:G2)
四、統(tǒng)計(jì)最低分
最低分選擇5,K2單元格公式為:=AGGREGATE(5,,B2:G2)
五、統(tǒng)計(jì)實(shí)際參考科目
實(shí)際參考科目也就是統(tǒng)計(jì)數(shù)據(jù)區(qū)域中數(shù)字的個(gè)數(shù),使用COUNT功能,選擇2,因此公式為:=AGGREGATE(2,,B2:G2)
好了,通過以上五個(gè)例子,朋友們對(duì)于AGGREGATE的基本用法應(yīng)該有所掌握,雖然說只用了一個(gè)函數(shù)就完成了五個(gè)函數(shù)的工作,相比之前要分別使用五個(gè)函數(shù)來完成工作提高了一定的效率,但每個(gè)公式還是要修改一下才能用。如果能夠使用一個(gè)公式右拉下拉的話,那才爽呢。(有同感的朋友可以在文末留言哦)
六、五種統(tǒng)計(jì)一步到位
對(duì)于有這種想法的朋友,應(yīng)該提出表揚(yáng),畢竟我們學(xué)習(xí)Excel的函數(shù)公式,不僅僅是為了完成工作,更加希望能夠提高效率。那么有沒有可能使用一個(gè)公式右拉下拉來完成例子中的五項(xiàng)統(tǒng)計(jì)呢?答案是肯定的:有!不過要用到一對(duì)函數(shù)組合,那就是choose和column。
在揭曉公式之前,先對(duì)問題進(jìn)行簡(jiǎn)單的分析,在我們使用AGGREGATE完成五項(xiàng)數(shù)據(jù)統(tǒng)計(jì)的公式中,只有第一參數(shù)也就是統(tǒng)計(jì)方式在發(fā)生變化,依次為:1、9、4、5、2。如果要想使用一個(gè)公式右拉下拉來完成的話,就得讓公式在右拉時(shí)第一參數(shù)按照這個(gè)順序來進(jìn)行變化(下拉時(shí)不需要變化,因?yàn)榻y(tǒng)計(jì)方式相同)。
通常要使用公式右拉得到順序變化的數(shù)據(jù)時(shí)就會(huì)用到column這個(gè)函數(shù):
Column這個(gè)函數(shù)的作用是得到參數(shù)對(duì)應(yīng)的列號(hào),例如column(a1)就得到a1這個(gè)單元格的列號(hào)也就是1,右拉時(shí)由于a1會(huì)變成b1、c1……,公式結(jié)果就會(huì)按照1、2、3……這個(gè)順序變化。
在本例中,我們需要得到的并不是一個(gè)很有規(guī)律的數(shù)列,而是1、9、4、5、2這樣一個(gè)無序的數(shù)列,這時(shí)候就要用到choose函數(shù)來實(shí)現(xiàn):
Choose函數(shù)的基本格式為:=choose(選擇指數(shù),值1,值2,值3……)
Choose函數(shù)根據(jù)第一個(gè)參數(shù)的數(shù)字來返回參數(shù)列表中的值。例如上圖,當(dāng)?shù)谝粎?shù)為1時(shí),就返回參數(shù)列表中的第1個(gè)值“1”;當(dāng)?shù)谝粎?shù)為2時(shí),就返回參數(shù)列表中的第2個(gè)值“9”,以此類推,使用column作為choose的第一參數(shù),就可以返回指定的序列了。
以上是對(duì)choose和column這對(duì)函數(shù)組合的說明,現(xiàn)在回到我們的問題,可以用來右拉下拉的這個(gè)公式就是:=AGGREGATE(CHOOSE(COLUMN(A1),1,9,4,5,2),,$B2:$G2)
可能有些新手還是會(huì)覺得暈乎乎的,這很正常,相信通過持續(xù)地學(xué)習(xí),你就可以對(duì)這種公式運(yùn)用自如了。
七、第一參數(shù)功能集錦
通過以上介紹,可以看到當(dāng)我們合理運(yùn)用了AGGREGATE函數(shù)之后,工作效率成倍增長(zhǎng)。這個(gè)函數(shù)的第一參數(shù)到底有哪19種功能呢,通過下面這個(gè)對(duì)照表可以一目了然:
實(shí)際上比較常用的就是那么幾種。
八、第二參數(shù)功能集錦
接下來我們?cè)賮砜纯吹诙?shù)又是什么功能,還是通過一個(gè)對(duì)照表來直觀地了解:
1.忽略空值
以下通過兩個(gè)例子看看如何使用第二參數(shù)來選擇忽略的內(nèi)容:=AGGREGATE(9,1,B2:B15)
第一參數(shù)選擇9,代表求和,第二參數(shù)選擇1,代表忽略隱藏行,當(dāng)數(shù)據(jù)全部顯示的時(shí)候,使用AGGREGATE函數(shù)求和與使用SUM函數(shù)的結(jié)果一致(第16行總分使用的是SUM函數(shù)求和),當(dāng)我們隱藏其中的某幾行數(shù)據(jù)時(shí),就看到區(qū)別了:
隱藏第4行、第8行、第11行之后,公式=AGGREGATE(9,1,B2:B15)只對(duì)當(dāng)前顯示的數(shù)據(jù)進(jìn)行了匯總。
說到這里,學(xué)過SUBTOTAL函數(shù)的同學(xué)一定會(huì)想到SUBTOTAL也有這樣的功能。但是今天出場(chǎng)的AGGREGATE函數(shù)比SUBTOTAL函數(shù)還要強(qiáng)大,因?yàn)槊鎸?duì)錯(cuò)誤值和分類匯總嵌套時(shí)SUBTOTAL無法處理,但AGGREGATE照樣搞得定。
2.忽略錯(cuò)誤值
今天的最后一個(gè)例子,看看遇到錯(cuò)誤值的時(shí)候會(huì)有什么情況:
如上圖所示,各學(xué)生的語文成績(jī)是利用vlookup函數(shù)從成績(jī)表中獲取的(這個(gè)函數(shù)前面有教程講過,還不了解的伙伴可以點(diǎn)鏈接去學(xué)習(xí)一下:插入鏈接)。當(dāng)姓名不在成績(jī)表的時(shí)候,就會(huì)得到一個(gè)錯(cuò)誤值,如李四和張三,此時(shí)無論我們使用SUM函數(shù)或者是SUBTOTAL函數(shù),都無法得到正確的語文成績(jī)總分,只有AGGREGATE可以忽略錯(cuò)誤值得到正確結(jié)果。當(dāng)然你可以使用iferror等函數(shù)進(jìn)行處理之后再去用SUM求和,但這并不能掩蓋AGGREGATE的強(qiáng)大。
19種統(tǒng)計(jì)函數(shù)功能加7種忽略項(xiàng)目,這種逆天的整合功能,真的不是一般函數(shù)可以比的!AGGREGATE是當(dāng)之無愧的統(tǒng)計(jì)函數(shù)之王,快收藏吧!
本文配套的練習(xí)課件請(qǐng)加入QQ群:316492581下載。
如果您因工作所需使用到Excel,不妨關(guān)注部落窩教育的《一周Excel直通車》視頻課或《Excel極速貫通班》直播課系統(tǒng)學(xué)習(xí)。
相關(guān)推薦:
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!