王志軍

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

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

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

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

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

第3步：獲取動(dòng)態(tài)統(tǒng)計(jì)結(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)計(jì)結(jié)果?；蛘咭部梢允褂霉健?COUNTIFS（$A$2：$A$14，INDEX（A：A.MATCH（1，D$1：D$14，0）），$C$2：$C$14.B17）”，統(tǒng)計(jì)結(jié)果完全相同。

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