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

2024-08-23 07:20:59

字體：大中小

來源：轉載

供稿：網友

　　學校對于某年級各教學班的考試成績分析，一般要統計每個班的參加考試人數、及格率、平均分(或每班前若干名學生的平均分)、學生個體在本教學班中的名次等，如果僅用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 學科實考統計

CUOxin.COM推薦：點擊免費下載最新版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)。