SUBTOTAL函數(shù)主要用于篩選和隱藏后的數(shù)據(jù)統(tǒng)計(jì)，其語法為SUBTOTAL(function_num,ref1,…)，其中參數(shù)function_num用于指定要為分類匯總使用的函數(shù)，如求和、計(jì)數(shù)、求平均值等。
function_num參數(shù)值的具體含義如下表所示：
當(dāng)function_num取1~11時(shí)，SUBTOTAL函數(shù)對(duì)篩選后的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，包括手工隱藏的數(shù)據(jù)。當(dāng)function_num取101~111時(shí)，SUBTOTAL函數(shù)對(duì)可見的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，忽略篩選掉的數(shù)據(jù)和手工隱藏的數(shù)據(jù)。無論function_num取1~11還是101~111，SUBTOTAL函數(shù)均不統(tǒng)計(jì)篩選掉的數(shù)據(jù)，兩個(gè)參數(shù)范圍的區(qū)別在于是否統(tǒng)計(jì)手工隱藏的數(shù)據(jù)。
本文主要以使用SUBTOTAL函數(shù)進(jìn)行計(jì)數(shù)（即function_num為3、103）和求和（即function_num為9、109）為例，講解SUBTOTAL函數(shù)的用法。
如下圖所示，A1:C11為各業(yè)務(wù)員銷售額。要求計(jì)算篩選出的人數(shù)。
在F1單元格輸入公式：=SUBTOTAL(3,$A$2:$A$11)如下圖所示，當(dāng)未篩選數(shù)據(jù)時(shí)，SUBTOTAL函數(shù)返回值為“10”。當(dāng)篩選出“銷售1部”的數(shù)據(jù)時(shí)，SUBTOTAL函數(shù)返回值為“4”。如下圖所示，在F1單元格輸入公式：=SUBTOTAL(9,$C$2:$C$11)當(dāng)未篩選數(shù)據(jù)時(shí)，SUBTOTAL函數(shù)返回值為“550”。
當(dāng)篩選出“銷售1部”數(shù)據(jù)時(shí)，SUBTOTAL函數(shù)返回值為“220”。
如下圖所示，在F1單元格輸入公式：=SUBTOTAL(103,$A$2:$A$11)此時(shí)所有數(shù)據(jù)均未隱藏，SUBTOTAL函數(shù)返回值為“10”。
當(dāng)隱藏第2、3行數(shù)據(jù)后，SUBTOTAL函數(shù)返回值為“8”，如下圖所示。
如下圖所示，在F1單元格輸入公式：=SUBTOTAL(109,$C$2:$C$11)此時(shí)所有數(shù)據(jù)均未隱藏，SUBTOTAL函數(shù)返回值為“550”。當(dāng)隱藏第2、3行數(shù)據(jù)后，SUBTOTAL函數(shù)返回值為“520”。如下圖所示，在A2單元格輸入公式：=SUBTOTAL(103,$B$2:B2)*1拖動(dòng)填充柄向下復(fù)制公式，在A2:A11生成連續(xù)序號(hào)。
當(dāng)篩選出“銷售1部”時(shí)，序號(hào)仍保持連續(xù)，如下圖所示：
六、對(duì)篩選后的數(shù)據(jù)進(jìn)行條件計(jì)數(shù)=SUMPRODUCT(($B$2:$B$11=B15)*(SUBTOTAL(2,OFFSET($C$2,ROW($C$2:$C$11)-2,0))))拖動(dòng)填充柄將公式向下復(fù)制至C16單元格。
當(dāng)未篩選數(shù)據(jù)時(shí)，SUBTOTAL函數(shù)返回的是各部門的員工人數(shù)。
當(dāng)篩選出“銷售額>=50”的數(shù)據(jù)時(shí)，SUBTOTAL函數(shù)返回的是各部門銷售額不低于50的員工人數(shù)，如下圖所示：
本例中，使用OFFSET函數(shù)作為SUBTOTAL函數(shù)ref1參數(shù)，確定C2:C11單元格是否被篩選出。使用公式($B$2:$B$11=B15)判斷是否為B15單元格的部門（即“銷售1部”）。使用SUMPRODUCT函數(shù)計(jì)算滿足條件的單元格個(gè)數(shù)。
|
