在實際的數據統計分析中，經常會遇到很多複雜的因素，例如，對隐藏的行或計算結果返回錯誤類型的值不予統計等等……如果此時還用常規的Sum系列、Count系列、Average系列等函數去做數據統計分析，将會是難上加難或者根本無法完成。此時，如果要一個隻對“可見”單元格或區域、忽略錯誤等類型進行統計分析的函數，将會是“雪中送炭”……今天，小編帶大家了解一下萬能的分類統計彙總函數Subtotal和Aggregate。

一、分類彙總函數：Subtotal，返回指定區域的分類彙總結果。

功能：返回列表或數據庫中的分類彙總。

語法結構：=Subtotal(彙總方式,數據區域1,[數據區域2]……[數據區域254])。

其中【彙總方式】分為1~11（包含隐藏值）和101~111（忽略隐藏值）兩大類。具體功能請參閱下表。

注意事項：

1、參數【彙總方式】必須為數值類型或可轉換為數值的數據，且必須為1~11或101~111以内的數字，否則返回錯誤值“#VALUE!”。

2、如果計算的區域總存在隐藏行，使用代碼1~11時，隐藏的行仍然在統計的範圍内，如果使用101~111時，隻對“可見”區域有效，暨忽略隐藏的行。

3、Subtotal函數對隐藏列區域無效，即如果統計的數據範圍内包含隐藏的列，不管使用代碼1~11還是101~111，這些隐藏的列數據仍然在統計的範圍内。

4、【數據區域】隻支持二維引用，不支持三維引用，否則返回錯誤值“#VALUE!”。

（一）、分類彙總函數Subtotal：隐藏彙總。

目的：對“可見”數據區域進行彙總。

方法：

在目标單元格中輸入公式：=SUBTOTAL(109,E3:E9)。

解讀：

1、從示例中可以看出，未“隐藏”之前，3種形式的計算結果是相同的，但“隐藏”之後，代碼“109”的計算結果和其它2種的不同，原因在于代碼“109”忽略隐藏行的數據，隻對“可見”數據區域有效。

2、其它代碼所對應的功能和求和的用法相同。

（二）分類彙總函數Subtotal：篩選彙總。

目的：按部門統計“年薪”。

方法：

在目标單元格中輸入公式：=SUBTOTAL(9,E3:E9)、=SUBTOTAL(109,E3:E9)。

解讀：

1、在篩選數據後，代碼“9”和代碼“109”的返回結果是相同的，而且為“可見”單元格的彙總數據。

2、其它代碼所對應的功能和求和的用法相同。

（三）分類彙總函數Subtotal：經典用法之保持序号的連續性。

方法：

在目标單元格中輸入公式：=SUBTOTAL(3,B$2:B2)或=SUBTOTAL(103,B$2:B2)。

解讀：

代碼“3”或“103”代表的函數為Counta，即非空單元格的個數。而對于篩選，代碼“3”或“103”都是對可見單元格有效。所以用公式=SUBTOTAL(3,B$2:B2)或=SUBTOTAL(103,B$2:B2)都統計的是從當前單元格的上一單元格開始的非空單元格的個數。

二、分類彙總函數：Aggregate，返回指定區域的分類彙總結果。

功能：返回列表或數據庫中的分類彙總。

語法結構：=Aggregate(彙總方式,忽略方式,數據區域1,[數據區域2]……[數據區域254])。

其中【彙總方式】為1~19之間的數字。具體功能請參閱下表。

【忽略方式】表示要在函數的計算區域中忽略那些值，該參數為0~7之間的數字。具體功能請參閱下表。

注意事項：

1、當【彙總方式】參數為14~19時，必須制定【數據區域2】的值，否則Aggregate函數将返回錯誤值“#VALUE!” 。

2、如果Aggregate函數的引用中包含嵌套的Aggregate和Subtotal函數，則将忽略這兩個函數。

3、Aggregate函數适用于數據列或垂直區域，不适用于數據行或水平區域。

4、Aggregate函數必須在10及以上版本中使用。

（一）、分類彙總函數Aggregate：隐藏彙總。

目的：對隐藏後的“可見”數據區域進行彙總。

方法：

在目标單元格中輸入公式：=AGGREGATE(9,1,E3:E9)、=AGGREGATE(9,3,E3:E9)、=AGGREGATE(9,5,E3:E9)、=AGGREGATE(9,7,E3:E9)。

解讀：

1、如果隻是對隐藏後的“可見”區域進行求和，可以使用公式=SUBTOTAL(109,E3:E9)完成。

2、上述公式中的忽略代碼“1”、“3”、“5”、“7”的一個共同功能是“忽略隐藏行”。

3、其它代碼所對應的功能和求和的用法相同。

（二）、分類彙總函數Aggregate：忽略錯誤值隐藏彙總。

目的：忽略錯誤值并對“可見”區域彙總。

方法：

在目标單元格中輸入公式：=AGGREGATE(9,3,E3:E9)、=AGGREGATE(9,7,E3:E9)。

解讀：

1、數據區域中的值包含一個錯誤值“#N/A”， 此時用Sum或Subtotal函數無法完成彙總任務。所以必須對錯誤值#N/A 忽略，所以用Aggregate函數替代Subtotal或Sum函數。

2、上述公式中的忽略代碼“3”、“7”的一個共同特點“忽略隐藏行、錯誤值”。

3、其它代碼所對應的功能和求和的用法相同。

（三）、分類彙總函數Aggregate：篩選彙總。

目的：對篩選後的“可見”數據區域進行彙總。

方法：

在目标單元格中輸入公式：=AGGREGATE(9,1,E3:E9)、=AGGREGATE(9,3,E3:E9)、=AGGREGATE(9,5,E3:E9)、=AGGREGATE(9,7,E3:E9)。

解讀：

1、如果隻是對篩選後的“可見”區域進行求和，可以使用公式=SUBTOTAL(9,E3:E9)或=SUBTOTAL(109,E3:E9)完成。

2、上述公式中的忽略代碼“1”、“3”、“5”、“7”的一個共同功能是“忽略隐藏行”。

3、其它代碼所對應的功能和求和的用法相同。

（四）、分類彙總函數Aggregate：忽略錯誤值篩選彙總。

目的：忽略錯誤值并對“可見”區域彙總。

方法：

在目标單元格中輸入公式：=AGGREGATE(9,3,E3:E9)、=AGGREGATE(9,7,E3:E9)。

解讀：

1、數據區域中的值包含一個錯誤值“#N/A”， 此時用Sum或Subtotal函數無法完成彙總任務。所以必須對錯誤值#N/A 忽略，所以用Aggregate函數替代Subtotal或Sum函數。

2、上述公式中的忽略代碼“3”、“7”的一個共同特點“忽略隐藏行、錯誤值”。

3、其它代碼所對應的功能和求和的用法相同。

結束語：

從上述的示例中可以看出，Subtotal函數和Aggregate函數都是對指定的區域或數據庫進行分類彙總，其中Subtotal函數在彙總的時候不能進行嵌套，同時數據源中不能有錯誤類型值，而Aggregate函數可以嵌套，如果數據源中有錯誤類型值，可以忽略不計。Subtotal函數“隐藏”和“篩選”是針對不同的代碼而言的，而Aggregate函數隻對“可見”區域有效，即“隐藏”和“篩選”都是同一個代碼，例如求和的代碼都為“9”。

Subtotal函數和Aggregate函數在學習時，數字代碼較多，容易混淆，建議大家根據系統的“聯想”功能有針對性的選擇實用代碼，不建議死記硬背哦！如果親有更好的學習或使用技巧，歡迎在留言區留言讨論哦！

#我要上頭條# #Excel函數公式#

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!