靈活使用WPS表格數(shù)組公式區(qū)分班級(jí)學(xué)生成績(jī)

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

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

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

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

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

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

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

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

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

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

　　2、每個(gè)班所有考生的總平均分

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

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

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

　　圖3 每個(gè)班的平均分

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

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

　　為了分層測(cè)試教學(xué)效果，往往按截取一部分學(xué)生的成績(jī)作為樣本計(jì)算。例如，計(jì)算各班前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)))))

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

　　圖4 各班前30%平均分

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

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

　　假如語(yǔ)文學(xué)科(C列)及格分?jǐn)?shù)為60分，則在要計(jì)算各班及格率的縱向連續(xù)的第一個(gè)單元格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)后向下拖動(dòng)填充到全部班級(jí)單元格G262:G265，得到各班該學(xué)科的及格率(如圖5)。

　　圖5 學(xué)科及格率

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

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

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

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

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

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

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

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

　　如下的兩個(gè)數(shù)組公式與上述公式等價(jià)(見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

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