巧用WPS表格數組公式分析班級學生成績

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

　　圖1 班級學生成績統計

　　1、每個班參加考試人數

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

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

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

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

　　圖2 學科實考統計

三聯推薦：點擊免費下載最新版WPS辦公軟件    》》查看更多電腦教程

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

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

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

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

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

　　圖3 每個班的平均分

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

　　3、每個班前若干名學生的平均分

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

　　=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% 的人數有多少，並將其四捨五入到整數;再通過ROW(INDIRECT("1:"&X) (以下簡稱為Y) 由小到大數排列1到前30%的人數的若干個自然數(數組)，由LARGE(IF($A$2:$A$260=0& ROW(A1),E$2:E$260),Y)抽取總分所在區域E$2:E$260中某班前30%學生的得分(數組)，最後通過AVERAGE()求選取數值的平均分。

　　4、每個班的學科及格率：

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

　　=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組合鍵確認後向下拖動填充到全部班級單元格G262:G265，得到各班該學科的及格率(如圖5)。

　　圖5 學科及格率

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

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

　　5、每個學生在本班的名次

　　一個學生在全年級的名次，可用函數RANK()求出，而該學生在本班內的名次，卻沒有現成的內置函數可用，我們仍然可以巧用數組公式來解決。在名次列的第一個學生成績行所在單元格F2中輸入數組公式：

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

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

　　圖6 班內排名次

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

　　如下的兩個數組公式與上述公式等價(見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

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