Excel函數學習之神奇的AGGREGATE，竟可一個抵19個！

在先前的文章《實用Excel技巧分享：利用「尋找替換」進行日期資料篩選》中，我們了解了幾種「尋找取代」的實用操作。而今天我們來聊聊一種神奇的Excel統計函數，它竟然可以一個抵19個，簡直是神器呀！趕緊收藏起來。

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

光說不練假把式，下面就來看看AGGREGATE的本領。

我們用一個成績表來說明AGGREGATE的基本用法，資料來源如圖所示：

每個學生參加六項測試，根據成績來得出藍色區域的五項統計內容，相信對大多數朋友來說，要完成這個表格並不難，無非就是掌握幾個最基礎的函數：AVERAGE（平均分數）、SUM（總分）、MAX （最高分）、MIN（最低分）和COUNT（實際參考科目）分別對五項內容進行統計。可能也有些新朋友還不了解以上提到的這五個函數，那麼正好，你只需要學習AGGREGATE這一個函數就可以實現上面這些數據的統計。

AGGREGATE的基本格式為：= AGGREGATE(統計功能，忽略哪些值，資料區域)，以下分別來看看如何完成範例中的五項統計內容。

一、統計平均分數

目前表格平均分數統計公式為：=AGGREGATE(1,,B2:G2) 。在H2儲存格中輸入公式再整列填充公式即可獲得各學生的平均分數。

說明：當統計函數為1的時候，函數實作計算平均值的函數。本例中我們並不需要指定忽略統計的數據，因此第二參數可以省略（這裡寫了兩個逗號，中間省略了一個參數），最後一個參數就是要計算的數據區域B2:G2，函數用法非常簡單，那麼結果是否正確呢？不妨使用AVERAGE函數來驗證一下：

可以看到，結果完全一致！

接下來我們再看看如何用AGGREGATE函數統計總分。

二、統計總分

目前表格總分統計公式為：=AGGREGATE(9,,B2:G2) 。在I2儲存格中輸入公式再整列填充公式即可獲得各學生總分。

只需要將第一個參數改為9即可，因為9對應的就是求和功能。

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

其實完全不需要有這種擔心，Excel為我們提供了非常聰明的提醒功能，當我們輸入函數之後，就有對應參數功能的選項：

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

三、統計最高分

了解這個功能以後，最後的三個統計項目就很容易完成了，最高分肯定是選擇4，因此J2單元格公式為：

=AGGREGATE(4,,B2:G2)

##四、統計最低分最低分選5，K2單元格公式為：

=AGGREGATE(5,,B2:G2)

五、統計實際參考科目

實際參考科目也就是統計資料區域中數字的個數，使用COUNT功能，選擇2，因此公式為：=AGGREGATE(2,,B2:G2)

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

六、五種統計一步到位

對於有這種想法的朋友，應該提出讚美，畢竟我們學習Excel的函數公式，不只是為了完成工作，更希望能夠提高效率。那麼有沒有可能使用公式右拉下拉來完成例子中的五項統計呢？答案是肯定的：有！不過要用到一對函數組合，那就是choose和column。

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

通常要使用公式右拉得到順序變化的資料時就會用到column這個函數：

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

在本例中，我們需要得到的並不是一個很有規律的數列，而是1、9、4、5、2這樣一個無序的數列，這時候就要用到choose函數來實現：

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

Choose函數根據第一個參數的數字來傳回參數清單中的值。例如上圖，當第一參數為1時，就傳回參數清單中的第1個值“1”；當第一參數為2時，就傳回參數清單中的第2個值“9”，以此類別推，使用column作為choose的第一參數，就可以傳回指定的序列了。

以上是choose和column這對函數組合的說明，現在回到我們的問題，可以用來右拉下拉的這個公式就是：=AGGREGATE(CHOOSE(COLUMN(A1), 1,9,4,5,2),,$B2:$G2)

可能有些新手還是會覺得暈乎乎的，這很正常，相信透過持續地學習，你就可以對這種公式運用自如了。

七、第一參數功能集錦

透過上述介紹，可以看到當我們合理運用了AGGREGATE函數之後，工作效率成倍增長。這個函數的第一參數到底有哪19種功能呢，透過下面這個對照表可以一目了然：

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

八、第二參數功能集錦

接下來我們再來看看第二參數又是什麼功能，還是透過一個對照表來直觀地了解：

1.忽略空值

##以下透過兩個例子看看如何使用第二個參數來選擇忽略的內容：

=AGGREGATE(9,1,B2:B15)

#第一參數選擇9，代表求和，第二參數選擇1，代表忽略隱藏行，當資料全部顯示的時候，使用AGGREGATE函數求和與使用SUM函數的結果一致（第16行總分使用的是SUM函數求和），當我們隱藏其中的某幾行資料時，就看到差異了：

隱藏第4行、第8行、第11行之後，公式=AGGREGATE(9,1,B2:B15)只對目前顯示的資料進行了匯總。

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

2.忽略錯誤值

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

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

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

相關學習推薦：excel教學

以上是Excel函數學習之神奇的AGGREGATE，竟可一個抵19個！的詳細內容。更多資訊請關注PHP中文網其他相關文章！