将span隐藏的函数_分类汇总函数Subtotal和Aggregate应用技巧解读
在實(shí)際的數(shù)據(jù)統(tǒng)計(jì)分析中，經(jīng)常會(huì)遇到很多復(fù)雜的因素，例如，對(duì)隱藏的行或計(jì)算結(jié)果返回錯(cuò)誤類型的值不予統(tǒng)計(jì)等等……如果此時(shí)還用常規(guī)的Sum系列、Count系列、Average系列等函數(shù)去做數(shù)據(jù)統(tǒng)計(jì)分析，將會(huì)是難上加難或者根本無(wú)法完成。此時(shí)，如果要一個(gè)只對(duì)“可見”單元格或區(qū)域、忽略錯(cuò)誤等類型進(jìn)行統(tǒng)計(jì)分析的函數(shù)，將會(huì)是“雪中送炭”……今天，小編帶大家了解一下萬(wàn)能的分類統(tǒng)計(jì)匯總函數(shù)Subtotal和Aggregate。
一、分類匯總函數(shù)：Subtotal，返回指定區(qū)域的分類匯總結(jié)果。
功能：返回列表或數(shù)據(jù)庫(kù)中的分類匯總。
語(yǔ)法結(jié)構(gòu)：=Subtotal(匯總方式,數(shù)據(jù)區(qū)域1,[數(shù)據(jù)區(qū)域2]……[數(shù)據(jù)區(qū)域254])。
其中【匯總方式】分為1~11（包含隱藏值）和101~111（忽略隱藏值）兩大類。具體功能請(qǐng)參閱下表。
注意事項(xiàng)：
1、參數(shù)【匯總方式】必須為數(shù)值類型或可轉(zhuǎn)換為數(shù)值的數(shù)據(jù)，且必須為1~11或101~111以內(nèi)的數(shù)字，否則返回錯(cuò)誤值“#VALUE!”。
2、如果計(jì)算的區(qū)域總存在隱藏行，使用代碼1~11時(shí)，隱藏的行仍然在統(tǒng)計(jì)的范圍內(nèi)，如果使用101~111時(shí)，只對(duì)“可見”區(qū)域有效，暨忽略隱藏的行。
3、Subtotal函數(shù)對(duì)隱藏列區(qū)域無(wú)效，即如果統(tǒng)計(jì)的數(shù)據(jù)范圍內(nèi)包含隱藏的列，不管使用代碼1~11還是101~111，這些隱藏的列數(shù)據(jù)仍然在統(tǒng)計(jì)的范圍內(nèi)。
4、【數(shù)據(jù)區(qū)域】只支持二維引用，不支持三維引用，否則返回錯(cuò)誤值“#VALUE!”。
（一）、分類匯總函數(shù)Subtotal：隱藏匯總。
目的：對(duì)“可見”數(shù)據(jù)區(qū)域進(jìn)行匯總。
方法：
在目標(biāo)單元格中輸入公式：=SUBTOTAL(109,E3:E9)。
解讀：
1、從示例中可以看出，未“隱藏”之前，3種形式的計(jì)算結(jié)果是相同的，但“隱藏”之后，代碼“109”的計(jì)算結(jié)果和其它2種的不同，原因在于代碼“109”忽略隱藏行的數(shù)據(jù)，只對(duì)“可見”數(shù)據(jù)區(qū)域有效。
2、其它代碼所對(duì)應(yīng)的功能和求和的用法相同。
（二）分類匯總函數(shù)Subtotal：篩選匯總。
目的：按部門統(tǒng)計(jì)“年薪”。
方法：
在目標(biāo)單元格中輸入公式：=SUBTOTAL(9,E3:E9)、=SUBTOTAL(109,E3:E9)。
解讀：
1、在篩選數(shù)據(jù)后，代碼“9”和代碼“109”的返回結(jié)果是相同的，而且為“可見”單元格的匯總數(shù)據(jù)。
2、其它代碼所對(duì)應(yīng)的功能和求和的用法相同。
（三）分類匯總函數(shù)Subtotal：經(jīng)典用法之保持序號(hào)的連續(xù)性。
方法：
在目標(biāo)單元格中輸入公式：=SUBTOTAL(3,B$2:B2)或=SUBTOTAL(103,B$2:B2)。
解讀：
代碼“3”或“103”代表的函數(shù)為Counta，即非空單元格的個(gè)數(shù)。而對(duì)于篩選，代碼“3”或“103”都是對(duì)可見單元格有效。所以用公式=SUBTOTAL(3,B$2:B2)或=SUBTOTAL(103,B$2:B2)都統(tǒng)計(jì)的是從當(dāng)前單元格的上一單元格開始的非空單元格的個(gè)數(shù)。
二、分類匯總函數(shù)：Aggregate，返回指定區(qū)域的分類匯總結(jié)果。
功能：返回列表或數(shù)據(jù)庫(kù)中的分類匯總。
語(yǔ)法結(jié)構(gòu)：=Aggregate(匯總方式,忽略方式,數(shù)據(jù)區(qū)域1,[數(shù)據(jù)區(qū)域2]……[數(shù)據(jù)區(qū)域254])。
其中【匯總方式】為1~19之間的數(shù)字。具體功能請(qǐng)參閱下表。
【忽略方式】表示要在函數(shù)的計(jì)算區(qū)域中忽略那些值，該參數(shù)為0~7之間的數(shù)字。具體功能請(qǐng)參閱下表。
注意事項(xiàng)：
1、當(dāng)【匯總方式】參數(shù)為14~19時(shí)，必須制定【數(shù)據(jù)區(qū)域2】的值，否則Aggregate函數(shù)將返回錯(cuò)誤值“#VALUE!” 。
2、如果Aggregate函數(shù)的引用中包含嵌套的Aggregate和Subtotal函數(shù)，則將忽略這兩個(gè)函數(shù)。
3、Aggregate函數(shù)適用于數(shù)據(jù)列或垂直區(qū)域，不適用于數(shù)據(jù)行或水平區(qū)域。
4、Aggregate函數(shù)必須在10及以上版本中使用。
（一）、分類匯總函數(shù)Aggregate：隱藏匯總。
目的：對(duì)隱藏后的“可見”數(shù)據(jù)區(qū)域進(jìn)行匯總。
方法：
在目標(biāo)單元格中輸入公式：=AGGREGATE(9,1,E3:E9)、=AGGREGATE(9,3,E3:E9)、=AGGREGATE(9,5,E3:E9)、=AGGREGATE(9,7,E3:E9)。
解讀：
1、如果只是對(duì)隱藏后的“可見”區(qū)域進(jìn)行求和，可以使用公式=SUBTOTAL(109,E3:E9)完成。
2、上述公式中的忽略代碼“1”、“3”、“5”、“7”的一個(gè)共同功能是“忽略隱藏行”。
3、其它代碼所對(duì)應(yīng)的功能和求和的用法相同。
（二）、分類匯總函數(shù)Aggregate：忽略錯(cuò)誤值隱藏匯總。
目的：忽略錯(cuò)誤值并對(duì)“可見”區(qū)域匯總。
方法：
在目標(biāo)單元格中輸入公式：=AGGREGATE(9,3,E3:E9)、=AGGREGATE(9,7,E3:E9)。
解讀：
1、數(shù)據(jù)區(qū)域中的值包含一個(gè)錯(cuò)誤值“#N/A”， 此時(shí)用Sum或Subtotal函數(shù)無(wú)法完成匯總?cè)蝿?wù)。所以必須對(duì)錯(cuò)誤值#N/A 忽略，所以用Aggregate函數(shù)替代Subtotal或Sum函數(shù)。
2、上述公式中的忽略代碼“3”、“7”的一個(gè)共同特點(diǎn)“忽略隱藏行、錯(cuò)誤值”。
3、其它代碼所對(duì)應(yīng)的功能和求和的用法相同。
（三）、分類匯總函數(shù)Aggregate：篩選匯總。
目的：對(duì)篩選后的“可見”數(shù)據(jù)區(qū)域進(jìn)行匯總。
方法：
在目標(biāo)單元格中輸入公式：=AGGREGATE(9,1,E3:E9)、=AGGREGATE(9,3,E3:E9)、=AGGREGATE(9,5,E3:E9)、=AGGREGATE(9,7,E3:E9)。
解讀：
1、如果只是對(duì)篩選后的“可見”區(qū)域進(jìn)行求和，可以使用公式=SUBTOTAL(9,E3:E9)或=SUBTOTAL(109,E3:E9)完成。
2、上述公式中的忽略代碼“1”、“3”、“5”、“7”的一個(gè)共同功能是“忽略隱藏行”。
3、其它代碼所對(duì)應(yīng)的功能和求和的用法相同。
（四）、分類匯總函數(shù)Aggregate：忽略錯(cuò)誤值篩選匯總。
目的：忽略錯(cuò)誤值并對(duì)“可見”區(qū)域匯總。
方法：
在目標(biāo)單元格中輸入公式：=AGGREGATE(9,3,E3:E9)、=AGGREGATE(9,7,E3:E9)。
解讀：
1、數(shù)據(jù)區(qū)域中的值包含一個(gè)錯(cuò)誤值“#N/A”， 此時(shí)用Sum或Subtotal函數(shù)無(wú)法完成匯總?cè)蝿?wù)。所以必須對(duì)錯(cuò)誤值#N/A 忽略，所以用Aggregate函數(shù)替代Subtotal或Sum函數(shù)。
2、上述公式中的忽略代碼“3”、“7”的一個(gè)共同特點(diǎn)“忽略隱藏行、錯(cuò)誤值”。
3、其它代碼所對(duì)應(yīng)的功能和求和的用法相同。
結(jié)束語(yǔ)：
從上述的示例中可以看出，Subtotal函數(shù)和Aggregate函數(shù)都是對(duì)指定的區(qū)域或數(shù)據(jù)庫(kù)進(jìn)行分類匯總，其中Subtotal函數(shù)在匯總的時(shí)候不能進(jìn)行嵌套，同時(shí)數(shù)據(jù)源中不能有錯(cuò)誤類型值，而Aggregate函數(shù)可以嵌套，如果數(shù)據(jù)源中有錯(cuò)誤類型值，可以忽略不計(jì)。Subtotal函數(shù)“隱藏”和“篩選”是針對(duì)不同的代碼而言的，而Aggregate函數(shù)只對(duì)“可見”區(qū)域有效，即“隱藏”和“篩選”都是同一個(gè)代碼，例如求和的代碼都為“9”。
Subtotal函數(shù)和Aggregate函數(shù)在學(xué)習(xí)時(shí)，數(shù)字代碼較多，容易混淆，建議大家根據(jù)系統(tǒng)的“聯(lián)想”功能有針對(duì)性的選擇實(shí)用代碼，不建議死記硬背哦！如果親有更好的學(xué)習(xí)或使用技巧，歡迎在留言區(qū)留言討論哦！
總結(jié)
以上是生活随笔為你收集整理的将span隐藏的函数_分类汇总函数Subtotal和Aggregate应用技巧解读的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
