王志軍

同事小秦前來求助，如例所示的教職員T考核表，現(xiàn)在需要動態(tài)統(tǒng)計所篩選部門的考核情況，例如篩選機電部，則統(tǒng)計機電部的考核情況，篩選信息部，則統(tǒng)計信息部的考核情況。這里的關(guān)鍵是需要知道篩選的結(jié)果是哪一個部門，由于實際的數(shù)據(jù)量非常大，手工統(tǒng)計顯然是比較麻煩，有沒有簡單一些的實現(xiàn)方法呢？

第1步：創(chuàng)建輔助列

例如將D列作為輔助列，選擇D2單元格，在編輯欄輸入公式“=SUBTOTAL（3，A$I：A2）-1”，向下拖拽或雙擊填充柄。這里的SUTTOTAL函數(shù)用來統(tǒng)計可見單元格的內(nèi)容，使用的第一個參數(shù)是3，告訴SUBTOTAL函數(shù)需要執(zhí)行的匯總方式是COUNTA，COUNTA函數(shù)用來計算區(qū)域非空單元格的個數(shù)，第二個參數(shù)“A$I：A2”，“A$1”使用了行的絕對引用，也就是引用白A$l單元格到公式所在行的A列這樣一個逐行遞增的引用區(qū)域，來判斷可見非空單元格的個數(shù)。這里之所以是“-1”，是因為Excel是將帶有SUBTOTAL函數(shù)的表格最后一行作為匯總行，因此需要將SUBTOTAL函數(shù)的第二參數(shù)引用起始位置寫成公式所在行的上一行，再將結(jié)果減去1，否則會出現(xiàn)篩選結(jié)果多余的錯誤。

第2步：獲得動態(tài)篩選結(jié)果

接下來我們可以通過MATCH函數(shù)，判斷1在D1：D14的行號，再使用INDEX進行取值，使用公式“=INDEX（A：A，MATCH（1，D$I：D$14，0））”，這樣可以獲得A列班級動態(tài)的篩選結(jié)果。

第3步：獲取動態(tài)統(tǒng)計結(jié)果

選擇C17單元格，在編輯欄輸入公式“=SUMPRODUCT（（$A$2：$A$14=INDEX（A：A，MATCH（1，D$1：D$14，0）））*（$C$2：$C$14=B17））”，向下拖拽或雙擊填充柄，即可獲得圖3所示的統(tǒng)計結(jié)果。或者也可以使用公式“=COUNTIFS（$A$2：$A$14，INDEX（A：A.MATCH（1，D$1：D$14，0）），$C$2：$C$14.B17）”，統(tǒng)計結(jié)果完全相同。

進階：或者也可以不使用輔助列，例如使用數(shù)組公式實現(xiàn)。選擇C17單元格，在編輯欄輸入公式“=SUM（SUBTOTAL（3.OFFSET（A$1.ROW（$1：$14），》*（B17=C$2：C$15））”，注意最后按下“Ctrl+Shift+Enter”組合鍵轉(zhuǎn)換為數(shù)組公式。這里使用了多個函數(shù)的嵌套組合，首先使用OFFSET函數(shù)以Al為基點，分別向下偏移1至14行，形成由A2、A3、A4-A15這樣單個單元格區(qū)域的引用。再使用SUBTOTAL函數(shù)對這些單個的單元格區(qū)域分別統(tǒng)計可見單元格的個數(shù)，相當(dāng)于判斷是否為可見單元格。如果單元格可見，則返回1，否則返回0。使用“B17=C$2：C$15”產(chǎn)生的邏輯值，與這個1和0的數(shù)組相乘，即可得到篩選狀態(tài)下的統(tǒng)計計數(shù)。