在實際的數據統計分析中，經常會遇到很多複雜的因素，例如，對隱藏的行或計算結果返回錯誤類型的值不予統計等等……如果此時還用常規的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函數公式 的文章
