學(xué)校對于某年級各教學(xué)班的考試成績分析，一般要統(tǒng)計每個班的參加考試人數(shù)、及格率、平均分(或每班前若干名學(xué)生的平均分)、學(xué)生個體在本教學(xué)班中的名次等，如果僅用wps表格的內(nèi)置函數(shù)計算，則需要一個班一個班的分塊后計算，非常麻煩。筆者通過巧妙組合WPS表格的內(nèi)置函數(shù)編寫出數(shù)組公式，簡捷方便地完成了以上各種特征量數(shù)計算(如圖1所示)。

　　圖1 班級學(xué)生成績統(tǒng)計

　　1、每個班參加考試人數(shù)

　　假設(shè)某年級有4個班，學(xué)生總數(shù)不大于260名，班號在A列(用01、02、03…表示各班號)，從261行開始為匯總行。要統(tǒng)計的是語文學(xué)科(C 列)的各班參加考試人數(shù)(每個班有得分的學(xué)生數(shù)，即非空格數(shù))，則在要計算各班參加考試人數(shù)的縱向連續(xù)的第一個單元格C262中輸入公式：

　　=SUM((A$2:A$260=0&ROW(A1))*(C$2:C$260<>""))

　　按Ctrl+Shift+Enter組合鍵確認(rèn)輸入后，向下拖動填充到全部班級單元格C262 :C265，即可得到各班的該學(xué)科參加考試人數(shù)。

　　上述公式為數(shù)組公式，在編輯欄輸入這種公式后，必須按Ctrl+Shift+Enter組合鍵確認(rèn)，確認(rèn)后在公式兩邊自動加上花括號“{}”(如圖2)。

　　圖2 學(xué)科實(shí)考統(tǒng)計

CUOxin.COM推薦：點(diǎn)擊免費(fèi)下載最新版WPS辦公軟件 》》查看更多電腦教程

　　公式解釋：將班號列所在區(qū)域A$2:A$260為“某班”(0&ROW(A1))和某學(xué)科考分列所在區(qū)域C$2:C$260為非空單元格 (<>"")兩個條件做乘法運(yùn)算。當(dāng)兩個條件都成立時返回1(TRUE* TRUE=1)，否則返回0(TRUE* FALSE=0、FALSE *TRUE =0、FALSE * FALSE=0)。邏輯判斷的結(jié)果組成0、1數(shù)組，通過函數(shù)SUM()將所有的0、1值求和，得出該學(xué)科參加考試人數(shù)。

　　2、每個班所有考生的總平均分

　　假設(shè)E列為“總分”列，在要計算各班總平均分的縱向連續(xù)的第一個單元格E262中輸入數(shù)組公式：

　　=AVERAGE(IF(A$2:A$260=0&ROW(A1),E$2:E$260))

　　按Ctrl+Shift+Enter組合鍵確認(rèn)后向下拖動填充到全部班級單元格E262 :E265，得到每個班所有考生的平均分(如圖3)。

　　圖3 每個班的平均分

　　公式解釋：首先通過IF()函數(shù)，在總分所在區(qū)域E$2:E$260中篩選滿足條件為“某班”的一組數(shù)值，然后通過AVERAGE()對所篩選的數(shù)值求平均值。

　　3、每個班前若干名學(xué)生的平均分

　　為了分層測試教學(xué)效果，往往按截取一部分學(xué)生的成績作為樣本計算。例如，計算各班前30%學(xué)生的平均分，需在F262單元格中輸入數(shù)組公式如下：

　　=AVERAGE(LARGE(IF($A$2:$A$260=0&ROW(A1),E$2:E$260),ROW(INDIRECT("1:"&ROUND(COUNTIF(A$2:A$260,0&ROW(A1))*0.3,0)))))

　　然后將此公式向下拖動到F265單元格(如圖4)。

　　圖4 各班前30%平均分

　　公式解釋：首先通過ROUND(COUNTIF(A$2:A$260,0&ROW(A1))*0.3,0)(以下簡稱為X)，計算前30% 的人數(shù)有多少，并將其四舍五入到整數(shù);再通過ROW(INDIRECT("1:"&X) (以下簡稱為Y) 由小到大數(shù)排列1到前30%的人數(shù)的若干個自然數(shù)(數(shù)組)，由LARGE(IF($A$2:$A$260=0& ROW(A1),E$2:E$260),Y)抽取總分所在區(qū)域E$2:E$260中某班前30%學(xué)生的得分(數(shù)組)，最后通過AVERAGE()求選取數(shù)值的平均分。

　　4、每個班的學(xué)科及格率：

　　假如語文學(xué)科(C列)及格分?jǐn)?shù)為60分，則在要計算各班及格率的縱向連續(xù)的第一個單元格G262中輸入數(shù)組公式：

　　=SUM((A$2:A$260=0&ROW(A1))*(C$2:C$260>=60))/SUM((A$2:A$260=0&ROW(A1))*(C$2:C$260<>""))

　　按Ctrl+Shift+Enter組合鍵確認(rèn)后向下拖動填充到全部班級單元格G262:G265，得到各班該學(xué)科的及格率(如圖5)。

　　圖5 學(xué)科及格率

　　假如語文(C列)、數(shù)學(xué)(D列)中的學(xué)科及格分?jǐn)?shù)分別為60分、72分，則計算每個班這兩個學(xué)科都及格的人數(shù)的數(shù)組公式為：

　　=SUM((A$2:A$260=0&ROW(A1))*(C$2:C$260>=60)*(D$2:D$260>=72))

　　5、每個學(xué)生在本班的名次

　　一個學(xué)生在全年級的名次，可用函數(shù)RANK()求出，而該學(xué)生在本班內(nèi)的名次，卻沒有現(xiàn)成的內(nèi)置函數(shù)可用，我們?nèi)匀豢梢郧捎脭?shù)組公式來解決。在名次列的第一個學(xué)生成績行所在單元格F2中輸入數(shù)組公式：

　　=SUM((A$2:A$260=A2)*(E$2:E$260>E2),1)

　　按Ctrl+Shift+Enter組合鍵確認(rèn)后向下拖動到最后一行，得到每個學(xué)生在本班的名次(如圖6)。

　　圖6 班內(nèi)排名次

　　公式解釋：此公式是通過求本班大于該學(xué)生總分的人數(shù)與1的和，得到該學(xué)生在本教學(xué)班的名次。

　　如下的兩個數(shù)組公式與上述公式等價(見G列和H列的名次1和名次2)：

　　=SUM(((A$2:A$260=A2)*E$2:E$260>E2)*1,1)

　　=SUM(IF((A$2:A$260=A2)*E$2:E$260>E2,1))+1

　　由上面幾個例子可見，數(shù)組公式可以解決許多有條件的統(tǒng)計問題。為了方便，我們可以根據(jù)上面講到的各數(shù)組公式，設(shè)置成考試成績統(tǒng)計模板應(yīng)用于每次考試，起到一勞永逸的作用。