今天要和大家介紹的這個函數(shù)叫做AGGREGATE。雖說是Excel 2010中就有的函數(shù)，可是知道這個函數(shù)的人真沒多少，這是一件非常遺憾的事情，因為AGGREGATE函數(shù)不僅可以實現(xiàn)諸如SUM、AVERAGE、COUNT、LARGE等19個函數(shù)的功能，而且還可以忽略隱藏行、錯誤值、空值等。如果區(qū)域中包含錯誤值，SUM等函數(shù)將返回錯誤，這時用AGGREGATE函數(shù)就非常方便了。

光說不練假把式，下面就看看AGGREGATE的本領(lǐng)。

我們用一個成績表來說明AGGREGATE的基本用法，數(shù)據(jù)源如圖所示：

每個學(xué)生參加六項測試，根據(jù)成績來得出藍(lán)色區(qū)域的五項統(tǒng)計內(nèi)容，相信對于大多數(shù)朋友來說，要完成這個表格并不難，無非就是掌握幾個最基礎(chǔ)的函數(shù)：AVERAGE（平均分）、SUM（總分）、MAX（最高分）、MIN（最低分）和COUNT（實際參考科目）分別對五項內(nèi)容進(jìn)行統(tǒng)計。可能也有些新朋友還不了解以上提到的這五個函數(shù)，那么正好，你只需要學(xué)習(xí)AGGREGATE這一個函數(shù)就可以實現(xiàn)上面這些數(shù)據(jù)的統(tǒng)計。

AGGREGATE的基本格式為：=AGGREGATE（統(tǒng)計功能，忽略哪些值，數(shù)據(jù)區(qū)域），以下分別來看看如何完成例子中的五項統(tǒng)計內(nèi)容。

一、統(tǒng)計平均分

當(dāng)前表格平均分統(tǒng)計公式為：=AGGREGATE(1,,B2:G2)。在H2單元格中輸入公式再整列填充公式即可獲得各學(xué)生的平均分。

說明：當(dāng)統(tǒng)計功能為1的時候，函數(shù)實現(xiàn)計算平均值的功能。本例中我們并不需要指定忽略統(tǒng)計的數(shù)據(jù)，因此第二參數(shù)可以省略（此處寫了兩個逗號，中間省略了一個參數(shù)），最后一個參數(shù)就是要計算的數(shù)據(jù)區(qū)域B2:G2，函數(shù)用法非常簡單，那么結(jié)果是否正確呢？不妨使用AVERAGE函數(shù)來驗證一下：

可以看到，結(jié)果完全一致！

接下來我們再看看如何用AGGREGATE函數(shù)統(tǒng)計總分。

二、統(tǒng)計總分

當(dāng)前表格總分統(tǒng)計公式為：=AGGREGATE(9,,B2:G2)。在I2單元格中輸入公式再整列填充公式即可獲得各學(xué)生總分。

只需要將第一個參數(shù)改為9即可，因為9對應(yīng)的就是求和功能。

說到這里，可能會有些朋友擔(dān)心，第一個參數(shù)里1代表平均值，9代表求和，這個函數(shù)一共有19個功能，會不會很難記住。

實際上完全不需要有這種擔(dān)心，Excel為我們提供了非常智能的提醒功能，當(dāng)我們輸入函數(shù)之后，就有對應(yīng)參數(shù)功能的選項：

只要對照這個提示，選擇自己需要的功能即可。

三、統(tǒng)計最高分

了解這個功能以后，最后的三個統(tǒng)計項目就很容易完成了，最高分肯定是選擇4，因此J2單元格公式為：=AGGREGATE(4,,B2:G2)

四、統(tǒng)計最低分

最低分選擇5，K2單元格公式為：=AGGREGATE(5,,B2:G2)

五、統(tǒng)計實際參考科目

實際參考科目也就是統(tǒng)計數(shù)據(jù)區(qū)域中數(shù)字的個數(shù)，使用COUNT功能，選擇2，因此公式為：=AGGREGATE(2,,B2:G2)

好了，通過以上五個例子，朋友們對于AGGREGATE的基本用法應(yīng)該有所掌握，雖然說只用了一個函數(shù)就完成了五個函數(shù)的工作，相比之前要分別使用五個函數(shù)來完成工作提高了一定的效率，但每個公式還是要修改一下才能用。如果能夠使用一個公式右拉下拉的話，那才爽呢。（有同感的朋友可以在文末留言哦）

六、五種統(tǒng)計一步到位

對于有這種想法的朋友，應(yīng)該提出表揚(yáng)，畢竟我們學(xué)習(xí)Excel的函數(shù)公式，不僅僅是為了完成工作，更加希望能夠提高效率。那么有沒有可能使用一個公式右拉下拉來完成例子中的五項統(tǒng)計呢？答案是肯定的：有！不過要用到一對函數(shù)組合，那就是choose和column。

在揭曉公式之前，先對問題進(jìn)行簡單的分析，在我們使用AGGREGATE完成五項數(shù)據(jù)統(tǒng)計的公式中，只有第一參數(shù)也就是統(tǒng)計方式在發(fā)生變化，依次為：1、9、4、5、2。如果要想使用一個公式右拉下拉來完成的話，就得讓公式在右拉時第一參數(shù)按照這個順序來進(jìn)行變化（下拉時不需要變化，因為統(tǒng)計方式相同）。

通常要使用公式右拉得到順序變化的數(shù)據(jù)時就會用到column這個函數(shù)：

Column這個函數(shù)的作用是得到參數(shù)對應(yīng)的列號，例如column（a1）就得到a1這個單元格的列號也就是1，右拉時由于a1會變成b1、c1……，公式結(jié)果就會按照1、2、3……這個順序變化。

在本例中，我們需要得到的并不是一個很有規(guī)律的數(shù)列，而是1、9、4、5、2這樣一個無序的數(shù)列，這時候就要用到choose函數(shù)來實現(xiàn)：

Choose函數(shù)的基本格式為：=choose（選擇指數(shù)，值1，值2，值3……）

Choose函數(shù)根據(jù)第一個參數(shù)的數(shù)字來返回參數(shù)列表中的值。例如上圖，當(dāng)?shù)谝粎?shù)為1時，就返回參數(shù)列表中的第1個值“1”；當(dāng)?shù)谝粎?shù)為2時，就返回參數(shù)列表中的第2個值“9”，以此類推，使用column作為choose的第一參數(shù)，就可以返回指定的序列了。

以上是對choose和column這對函數(shù)組合的說明，現(xiàn)在回到我們的問題，可以用來右拉下拉的這個公式就是：

=AGGREGATE(CHOOSE(COLUMN(A1),1,9,4,5,2),,$B2:$G2)

可能有些新手還是會覺得暈乎乎的，這很正常，相信通過持續(xù)地學(xué)習(xí)，你就可以對這種公式運(yùn)用自如了。

七、第一參數(shù)功能集錦

通過以上介紹，可以看到當(dāng)我們合理運(yùn)用了AGGREGATE函數(shù)之后，工作效率成倍增長。這個函數(shù)的第一參數(shù)到底有哪19種功能呢，通過下面這個對照表可以一目了然：

實際上比較常用的就是那么幾種。

八、第二參數(shù)功能集錦

接下來我們再來看看第二參數(shù)又是什么功能，還是通過一個對照表來直觀地了解：

1.忽略空值

以下通過兩個例子看看如何使用第二參數(shù)來選擇忽略的內(nèi)容：

=AGGREGATE(9,1,B2:B15)

第一參數(shù)選擇9，代表求和，第二參數(shù)選擇1，代表忽略隱藏行，當(dāng)數(shù)據(jù)全部顯示的時候，使用AGGREGATE函數(shù)求和與使用SUM函數(shù)的結(jié)果一致（第16行總分使用的是SUM函數(shù)求和），當(dāng)我們隱藏其中的某幾行數(shù)據(jù)時，就看到區(qū)別了：

隱藏第4行、第8行、第11行之后，公式=AGGREGATE(9,1,B2:B15)只對當(dāng)前顯示的數(shù)據(jù)進(jìn)行了匯總。

說到這里，學(xué)過SUBTOTAL函數(shù)的同學(xué)一定會想到SUBTOTAL也有這樣的功能。但是今天出場的AGGREGATE函數(shù)比SUBTOTAL函數(shù)還要強(qiáng)大，因為面對錯誤值和分類匯總嵌套時SUBTOTAL無法處理，但AGGREGATE照樣搞得定。

2.忽略錯誤值

今天的最后一個例子，看看遇到錯誤值的時候會有什么情況：

如上圖所示，各學(xué)生的語文成績是利用vlookup函數(shù)從成績表中獲取的（這個函數(shù)前面有教程講過，還不了解的伙伴可以點鏈接去學(xué)習(xí)一下：插入鏈接）。當(dāng)姓名不在成績表的時候，就會得到一個錯誤值，如李四和張三，此時無論我們使用SUM函數(shù)或者是SUBTOTAL函數(shù)，都無法得到正確的語文成績總分，只有AGGREGATE可以忽略錯誤值得到正確結(jié)果。當(dāng)然你可以使用iferror等函數(shù)進(jìn)行處理之后再去用SUM求和，但這并不能掩蓋AGGREGATE的強(qiáng)大。

19種統(tǒng)計函數(shù)功能加7種忽略項目，這種逆天的整合功能，真的不是一般函數(shù)可以比的！AGGREGATE是當(dāng)之無愧的統(tǒng)計函數(shù)之王，快收藏吧！

來源：部落窩教育                作者：老菜鳥