Excel中的aggregate函數,你會用嗎?

2021-02-20 Excel函數與公式

長按下圖  識別二維碼，參加Excel特訓營系統提升

原創作者 | 李銳

微信公眾號 | Excel函數與公式（ID：ExcelLiRui）

微信個人號 | （ID：ExcelLiRui520）

關鍵字：aggregate

Excel中的aggregate函數，你會用嗎？

aggregate函數是一個功能相當豐富和強大的函數，集眾多功能於一身，求和、計數、求平均、最大值、最小值等等樣樣都會，學過subtotal函數的同學一定會想到subtotal也有這樣的功能。

但是今天出場的aggregate函數比subtotal函數還要強大，因為面對錯誤值和分類匯總嵌套時subtotal無法處理，但aggregate照樣搞的定！

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

函數作用、語法結構、參數說明


aggregate函數聚合了多種函數的豐富功能，可以按要求返回數據列表或資料庫的合計，並且還可以提供忽略錯誤值、忽略隱藏行、嵌套分類匯總等的選項，滿足各種情況下的統計需求。

她的語法結構很簡單：

aggregate（功能參數，選項參數，數據）

第一參數用於指定要執行的功能，這塊不必死記硬背，藉助Excel內置的函數參數提醒可以輕鬆記憶。

輸入函數的過程中就可以對照這個提示，選擇自己需要的功能了。

如果你看過之前的subtotal函數的教程，對這種功能一定不陌生了對吧。

aggregate的第二參數決定在函數的計算區域內要忽略哪些值。

0 或省略：忽略嵌套 SUBTOTAL 和 AGGREGATE 函數

1：忽略隱藏行、嵌套 SUBTOTAL 和 AGGREGATE 函數

2：忽略錯誤值、嵌套 SUBTOTAL 和 AGGREGATE 函數

3：忽略隱藏行、錯誤值、嵌套 SUBTOTAL 和 AGGREGATE 函數

4：忽略空值

5：忽略隱藏行

6：忽略錯誤值

7：忽略隱藏行和錯誤值


了解了這兩個參數的用法，其他就簡單了。

咱們主要看看aggregate比subtotal更強大之處，來看一個需要排除錯誤值統計的案例展示。

Excel案例展示（忽略錯誤值求和）

要求忽略錯誤值和忽略隱藏行，僅對顯示出來的成績統計總和。

C2=SUBTOTAL(109,C5:C99)無法忽略錯誤值

D2=AGGREGATE(9,3,C5:C99)成功忽略錯誤值照樣統計

Excel應用1：忽略錯誤值和隱藏行計算平均值

再來看看忽略錯誤值統計平均值。

C2=SUBTOTAL(101,C5:C99)無法忽略錯誤值返回正確結果

D2=AGGREGATE(1,3,C5:C99)面對錯誤值毫無壓力完成任務

Excel應用2：忽略錯誤值和隱藏行統計前三名數據

黃色單元格是公式生成，可以使序號永遠保持連續。

C2=AGGREGATE(14,3,$C$6:$C$99,ROW(1:1))

將公式向下填充即可



Excel應用3：無論如何篩選，都永遠連續的序號

黃色單元格是公式生成，可以使序號永遠保持連續。

A4=AGGREGATE(3,3,B$4:B4)

相比subtotal函數aggregate提供了更豐富的功能和選項，希望你能很好的應用它。

還想看更多精彩？更多實用Excel技能請長按識別下圖二維碼，裡面有各種方向不同的Excel特訓營，每一期特訓營內容不同，都可以幫你系統提升自己！

長按下圖  識別二維碼，參加Excel實戰特訓營

長按上圖↑識別二維碼，查看詳情

如果你覺得有用，就分享給朋友們看看吧~

別忘了幫忙去底部點讚

>>推薦閱讀 <<

（點擊藍字可直接跳轉）

vlookup丨sum丨if丨countif丨sumif丨sumproduct

index丨match丨datedif丨frequency丨個稅丨年終獎

長按下圖 識別二維碼

關注微信公眾號（ExcelLiRui），每天有乾貨

點「閱讀原文」進入直播間+關注，不再錯過精彩！

相關焦點

  • 學Excel十幾年,你會用SERIES函數嗎?
    熟悉的當選取excel圖表的系列時，就會在編輯欄中看到它的身影。陌生的是很多同學不知道這個SERIES函數倒底是什麼個作用=SERIES("ChartData",{"收入","支出"},摘要!$B$6:$C$6,1)SERIES是生成圖表系列的專用函數，它無法在單元格中使用，只能用在excel圖表中，它的語法為：=SERIES（標題,顯示在分類軸上的標誌,數據源,系列順序）同學們可以對應上圖和公式，就明白每個參數的作用了。肯定有同學在想，既然這個函數是自動生成的，我學它有什麼用呢？下面蘭色就介紹兩個實例小例子。
  • 學Excel十幾年,你會用SERIES圖表函數嗎?
    對於絕大多數Excel用戶來說，SERIES是一個既熟悉又陌生的函數。熟悉的當選取excel圖表的系列時，就會在編輯欄中看到它的身影。$B$6:$C$6,1)SERIES是生成圖表系列的專用函數，它無法在單元格中使用，只能用在excel圖表中，它的語法為：=SERIES（標題,顯示在分類軸上的標誌,數據源,系列順序）同學們可以對應上圖和公式，就明白每個參數的作用了。肯定有同學在想，既然這個函數是自動生成的，我學它有什麼用呢？下面蘭色就介紹兩個實例小例子。
  • Excel中的函數LOOKUP你知道嗎
    Vlookup是我們最常用的查找函數，但在實際查找過程中，它有時卻顯得力不從心，如指定位置查找、多條件查找、反向查找等。有一個函數卻可以輕易實現，它就是今天的主角：LOOKUP函數一、查找最後一條符合條件的記錄【例1】查找A產品的最後一次入庫單價=Lookup(1,0/(c5:10=B13),D5:D10)
  • REPLACE函數,你會用嗎?
    想了解的可以點擊文章鏈《SUBSTITUTE函數怎麼用？你知道嗎？》，本期技巧妹與大家分享有關REPLACE函數用法。一、REPLACE函數的基礎知識REPLACE函數是用來將一個字符串中的部分字符用另一個字符串替換。語法結構：=REPLACE(舊文本字符串，開始位置，替換的長度，新的文本字符串)注意：REPLACEB會將每個雙字節字符按2計數。
  • 你會用Excel做 甘特圖 嗎?
    如果你也寫出上面的公式，說明真的out了。其實用min函數超級簡單。2、添加輔助列，計算剩餘天數同1，如果你也用IF函數寫出下面公式，又out了=IF(E2>TODAY(),E2-TODAY(),0)
  • 你會做 Excel目錄 嗎?它簡直是一部Excel函數百科全書
    在Excel中有一類函數叫宏表函數，功能非常強大，可以提取Excel或電腦的信息，比如提取單元格顏色，提取文件目錄。由於返回的工作表名稱前含工作簿名稱「[抖音Excel技巧集.xlsm]」，所以下一步把用函數它刪除：用Find函數查找"]'的位置，然後用MID
  • Excel 函數 | IF函數,你真的會用了嗎?
    函數」領取《500個Excel函數模板》小夥伴們好啊，今天咱們說說IF函數使用過程中的一些常見的問題，看看你有沒有掉坑裡。1)多次比較 這個錯誤是因為你沒理解函數的判斷機制，多次比較下是分步進行的：第1次比較，1<2，結果為TRUE。第2次比較，TRUE<3，由於Excel裡邏輯值是大於任何數值的，所以結果為FALSE。故多個比較的話請配合AND或者OR函數，正確寫法為：
  • Excel中的選擇性粘貼,你會用嗎?
    ，你會用嗎？工作中的複製粘貼Ctrl+C和Ctrl+V大家都在用，但你知道Excel中的選擇性粘貼功能嗎？今天來科普一下。1 Excel中的選擇性粘貼功能藏在哪裡？Excel中的選擇性粘貼功能在複製數據後，可以通過點擊滑鼠右鍵，調出的快捷菜單中找到選擇性粘貼，它的快捷鍵是Ctrl+Alt+V
  • 你會用 Ctrl + A 鍵嗎?
    Ctrl + A 是Windows全選的快捷鍵，在Excel表格中也是如此。它可以一鍵選取一個連續的Excel表格區域。
  • 你會用 F9 鍵嗎?
    （查看公式中的一部分結果） 在編輯公式時，如果想知道公式中局部表達式的結果，你可以按F9鍵（返回按Ctrl+Z）。不會這個用法你永遠也成不了Excel函數高手。你也可以按F9把表格內容裝進公式。（ 把A列的型號用逗號連接起來）  操作方法：       輸入=A2:A14後選取公式按F9，然後刪除開頭和結尾的符號，再把 ","替換為 ,二、更新表中數據如果你表中使用了手工刷新，可以按F9更新工作簿的數據，這個特徵如果配合Excel函數，可以完成你意想不到的功能：隨機抽獎或隨機出題
  • 你會用Ctrl + H 嗎 ?
    4、按顏色等格式替換在excel中可以替換為不止是字符  註：勾選「單元格匹配」是防止含100的數字也會被替換，如11006、隔行取值公式如果你需要隔N行提取值，直接複製公式是不行的，難道只能用函數？7、跨工作簿複製公式當跨excel文件複製公式時，不但公式會引用原表格位置，單元格的引用也會發生變化。
  • Excel教程:你會用函數TRANSPOSE嗎?
    函數函數TRANSPOSE的基本用法很簡單，就是轉置數據。那麼接下來我們一起來看看函數TRANSPOSE與其他函數的組合應用。2、函數TRANSPOSE與HLOOKUP的組合應用垂直方向查找，首先想到的是使用函數VLOOKUP，其實使用函數HLOOKUP也能垂直方向查找，不過要藉助函數TRANSPOSE的幫助。
  • 四句話讓你搞清楚,Excel中函數與Excel VBA中函數不同
    Excel中我們經常會用到函數，其實ExcelVBA中也有函數。他們之間有什麼區別和聯繫，搞懂下面四句話就知道了。
  • Excel中的選擇性粘貼,你真的會用嗎?
    同學們在平時學習工作中，經常使用Excel軟體中的複製Ctrl+C和粘貼Ctrl+V，但你不一定會用Excel中的選擇性粘貼功能嗎
  • TRIMMEAN 函數,你會用嗎?
    在比賽或考評過程中，為了有效計算相應的成績，往往會去除偏差較大的成績，那用什麼函數比較便捷呢？
  • 你會用Column函數嗎?
    小E推薦你們用COLUMN函數來做列參數，讓公式更靈活，使用更方便。小E剛學會VLOOKUP那會兒，每次遇到查找多列數據的需求時，基本都是手動逐個更改公式中的第3參數。例如，下面需查找學生性別及各科目分數，我以往的操作如下。 如果匹配列數多的話，像我這樣手動修改，不僅容易出錯，還特別沒效率。
  • Excel實用技巧(49):常用的數學函數,你會運算了嗎?
    會計必須懂的Excel實用技巧 》正式開講。    報名地址： http://www.chinatet.com/zhiboke/kjbdexceljq/       日常工作中，我們實用Excel進行數據統計與分析，除了使用加、減、乘、除等常見的運算方式之外，還有一些數學運算的函數也值得我們注意，例如數據的乘方與開方。1.
  • IF函數的用法,你真的會用嗎
    IF函數是Excel中最常用的函數之一，但你真的知道這個函數的用法嗎？本期技巧妹與大家詳細講解有關IF函數的用法。
  • vlookup函數在excel函數中的應用
    excel的功能十分強大,vlookup函數的作用也很強大,是excel函數中最重要的函數之一,可以幫助我們在很多數據中找到我們想要的答案,那這個函數該怎麼用呢?有沒有實例可以參考?有!excel中vlookup函數的使用方法請看下面實例!打開帶有數據的表格,我就隨便找了個數據作為例子,如圖所示,至少包含兩行數據,這樣才能保證有數據可以引用.
  • Excel中條件判斷,你只會用IF函數?
    在Excel中進行條件判斷，大家第一個想到的肯定是IF函數，條件比較多的情況下，使用IF函數多層嵌套，容易出現錯誤。這裡技巧妹跟大家分享另外3個函數公式，同樣可以有效解決Excel中的條件判斷問題。VLOOKUP函數用VLOOKUP函數代替IF函數提取對應的提成比例，輸入公式：=VLOOKUP(B2,$E$2:$F$7,2,1)