在之前的文章《實(shí)用Excel技巧分享：利用“查找替換”進(jìn)行日期數(shù)據(jù)篩選》中，我們了解了幾種“查找替換”的實(shí)用操作。而今天我們來聊聊一種神奇的Excel統(tǒng)計(jì)函數(shù)，它竟然可以一個(gè)抵19個(gè)，簡直是神器呀！趕緊收藏起來。
今天要和大家介紹的這個(gè)函數(shù)叫做AGGREGATE。雖說是Excel 2010中就有的函數(shù)，可是知道這個(gè)函數(shù)的人真沒多少，這是一件非常遺憾的事情，因?yàn)锳GGREGATE函數(shù)不僅可以實(shí)現(xiàn)諸如SUM、AVERAGE、COUNT、LARGE等19個(gè)函數(shù)的功能，而且還可以忽略隱藏行、錯(cuò)誤值、空值等。如果區(qū)域中包含錯(cuò)誤值，SUM等函數(shù)將返回錯(cuò)誤，這時(shí)用 AGGREGATE函數(shù)就非常方便了。
光說不練假把式，下面就看看AGGREGATE的本領(lǐng)。
我們用一個(gè)成績表來說明AGGREGATE的基本用法，數(shù)據(jù)源如圖所示：
每個(gè)學(xué)生參加六項(xiàng)測(cè)試，根據(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é)果是否正確呢？不妨使用AVERAGE函數(shù)來驗(yàn)證一下：
可以看到，結(jié)果完全一致！
接下來我們?cè)倏纯慈绾斡肁GGREGATE函數(shù)統(tǒng)計(jì)總分。
二、統(tǒng)計(jì)總分
當(dāng)前表格總分統(tǒng)計(jì)公式為：=AGGREGATE(9,,B2:G2)。在I2單元格中輸入公式再整列填充公式即可獲得各學(xué)生總分。
只需要將第一個(gè)參數(shù)改為9即可，因?yàn)?對(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)行簡單的分析，在我們使用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ù)之后，工作效率成倍增長。這個(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é)生的語文成績是利用vlookup函數(shù)從成績表中獲取的（這個(gè)函數(shù)前面有教程講過，還不了解的伙伴可以點(diǎn)鏈接去學(xué)習(xí)一下：插入鏈接）。當(dāng)姓名不在成績表的時(shí)候，就會(huì)得到一個(gè)錯(cuò)誤值，如李四和張三，此時(shí)無論我們使用SUM函數(shù)或者是SUBTOTAL函數(shù)，都無法得到正確的語文成績總分，只有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ù)之王，快收藏吧！
相關(guān)學(xué)習(xí)推薦：excel教程
以上是Excel函數(shù)學(xué)習(xí)之神奇的AGGREGATE，竟可一個(gè)抵19個(gè)！的詳細(xì)內(nèi)容。更多信息請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！
熱AI工具
Undress AI Tool
免費(fèi)脫衣服圖片
Undresser.AI Undress
人工智能驅(qū)動(dòng)的應(yīng)用程序，用于創(chuàng)建逼真的裸體照片
AI Clothes Remover
用于從照片中去除衣服的在線人工智能工具。
Clothoff.io
AI脫衣機(jī)
Video Face Swap
使用我們完全免費(fèi)的人工智能換臉工具輕松在任何視頻中換臉！
熱門文章
熱工具
記事本++7.3.1
好用且免費(fèi)的代碼編輯器
SublimeText3漢化版
中文版，非常好用
禪工作室 13.0.1
功能強(qiáng)大的PHP集成開發(fā)環(huán)境
Dreamweaver CS6
視覺化網(wǎng)頁開發(fā)工具
SublimeText3 Mac版
神級(jí)代碼編輯軟件(SublimeText3)
excel打印表格框線消失怎么辦
Mar 21, 2024 am 09:50 AM
如果在打開一份需要打印的文件時(shí)，在打印預(yù)覽里我們會(huì)發(fā)現(xiàn)表格框線不知為什么消失不見了，遇到這樣的情況，我們就要及時(shí)進(jìn)行處理，如果你的打印文件里也出現(xiàn)了此類的問題，那么就和小編一起來學(xué)習(xí)下邊的課程吧：excel打印表格框線消失怎么辦？1、打開一份需要打印的文件，如下圖所示?！ ?、選中所有需要的內(nèi)容區(qū)域，如下圖所示?！ ?、單擊鼠標(biāo)右鍵，選擇“設(shè)置單元格格式”選項(xiàng)，如下圖所示?！ ?、點(diǎn)擊窗口上方的“邊框”選項(xiàng)，如下圖所示?！ ?、在左側(cè)的線條樣式中選擇細(xì)實(shí)線圖樣，如下圖所示?！ ?、選擇“外邊框”
excel同時(shí)篩選3個(gè)以上關(guān)鍵詞怎么操作
Mar 21, 2024 pm 03:16 PM
在日常辦公中經(jīng)常使用Excel來處理數(shù)據(jù)，時(shí)常遇到需要使用“篩選”功能。當(dāng)我們?cè)贓xcel中選擇執(zhí)行“篩選”時(shí)，對(duì)于同一列而言，最多只能篩選兩個(gè)條件，那么，你知道excel同時(shí)篩選3個(gè)以上關(guān)鍵詞該怎么操作嗎？接下來，就讓小編為大家演示一遍。第一種方法是將條件逐步添加到篩選器中。如果要同時(shí)篩選出三個(gè)符合條件的明細(xì)，首先需要逐步篩選出其中一個(gè)。開始時(shí)，可以先根據(jù)條件篩選出姓“王”的員工。然后單擊【確定】，接著在篩選結(jié)果中勾選【將當(dāng)前所選內(nèi)容添加到篩選器】。操作步驟如下所示?！ ⊥瑯?，再次分別執(zhí)行篩選
excel表格兼容模式改正常模式的方法
Mar 20, 2024 pm 08:01 PM
在我們?nèi)粘５墓ぷ鲗W(xué)習(xí)中，從他人處拷貝了Excel文件，打開進(jìn)行內(nèi)容添加或重新編輯后，再保存的有時(shí)候，有時(shí)會(huì)提示出現(xiàn)兼容性檢查的對(duì)話框，非常的麻煩，不知道Excel軟件，可不可改為正常模式呢？那么下面就由小編為大家?guī)斫鉀Q這個(gè)問題的詳細(xì)步驟，讓我們一起來學(xué)習(xí)吧。最后一定記得收藏保存。1、打開一個(gè)工作表，在工作表的名稱中顯示多出來一個(gè)兼容模式，如圖所示。2、在這個(gè)工作表中，進(jìn)行了內(nèi)容的修改后保存，結(jié)果總是彈出兼容檢查器的對(duì)話框，很麻煩看見這個(gè)頁面，如圖所示。 3、點(diǎn)擊Office按鈕，點(diǎn)另存為，然
excel閱讀模式在哪里設(shè)置
Mar 21, 2024 am 08:40 AM
在軟件的學(xué)習(xí)中，我們習(xí)慣用excel,不僅僅是因?yàn)樾枰奖悖驗(yàn)樗梢詽M足多種實(shí)際工作中需要的格式，而且excel運(yùn)用起來非常的靈活，有種模式是方便閱讀的，今天帶給大家的就是：excel閱讀模式在哪里設(shè)置。1、打開電腦，然后再打開Excel應(yīng)用，找到目標(biāo)數(shù)據(jù)。2、要想在Excel中，設(shè)置閱讀模式，有兩種方式。第一種：Excel中，有大量的便捷處理方式，分布在Excel中布局中。在Excel的右下角，有設(shè)置閱讀模式的快捷方式，找到十字標(biāo)志的圖案，點(diǎn)擊即可進(jìn)入閱讀模式，在十字標(biāo)志的右邊有一個(gè)小的三
excel上標(biāo)應(yīng)該如何設(shè)置
Mar 20, 2024 pm 04:30 PM
在處理數(shù)據(jù)時(shí)，有時(shí)我們會(huì)遇到數(shù)據(jù)包含了倍數(shù)、溫度等等各種符號(hào)的時(shí)候，你知道excel上標(biāo)應(yīng)該如何設(shè)置嗎？我們?cè)谑褂胑xcel處理數(shù)據(jù)時(shí)，如果不會(huì)設(shè)置上標(biāo)，這可是會(huì)讓我們的很多數(shù)據(jù)在錄入時(shí)就會(huì)比較麻煩。今天小編就為大家?guī)砹薳xcel上標(biāo)的具體設(shè)置方法。1.首先，讓我們打開桌面上的MicrosoftOfficeExcel文檔，選擇需要修改為上標(biāo)的文字，具體如圖所示。2.然后，點(diǎn)擊右鍵，在點(diǎn)擊后出現(xiàn)的菜單中，選擇“設(shè)置單元格格式”選項(xiàng)，具體如圖所示。3.接下來，在系統(tǒng)自動(dòng)彈出來的“單元格格式”對(duì)話框
excel中iif函數(shù)的用法
Mar 20, 2024 pm 06:10 PM
大部分用戶使用Excel都是用來處理表格數(shù)據(jù)的，其實(shí)Excel還有vba程序編寫，這個(gè)除了專人士應(yīng)該沒有多少用戶用過此功能，在vba編寫時(shí)常常會(huì)用到iif函數(shù)，它其實(shí)跟if函數(shù)的功能差不多，下面小編給大家介紹下iif函數(shù)的用法。Excel中SQL語句和VBA代碼中都有iif函數(shù)。iif函數(shù)和excel工作表中的IF函數(shù)用法相似，執(zhí)行真假值判斷，根據(jù)邏輯計(jì)算的真假值，返回不同結(jié)果。IF函數(shù)用法是(條件，是，否)。VBA中的IF語句和IIF函數(shù)，前者IF語句是控制語句可以根據(jù)條件執(zhí)行不同的語句，后者
html怎么讀取excel數(shù)據(jù)
Mar 27, 2024 pm 05:11 PM
html讀取excel數(shù)據(jù)的方法：1、使用JavaScript庫讀取Excel數(shù)據(jù)；2、使用服務(wù)器端編程語言讀取Excel數(shù)據(jù)。
PPT幻燈片插入excel圖標(biāo)的操作方法
Mar 26, 2024 pm 05:40 PM
1、打開PPT，翻頁至需要插入excel圖標(biāo)的頁面。點(diǎn)擊插入選項(xiàng)卡。2、點(diǎn)擊【對(duì)象】。3、跳出以下對(duì)話框。4、點(diǎn)擊【由文件創(chuàng)建】，點(diǎn)擊【瀏覽】。5、選擇需要插入的excel表格。6、點(diǎn)擊確定后跳出如下頁面。7、勾選【顯示為圖標(biāo)】。8、點(diǎn)擊確定即可。
