EXCEL在多數(shù)據(jù)成績統(tǒng)計中的應用

☆周曉敏

（常山縣實驗小學，浙江常山 324200）

課任教師、教務主任、教研員等教學工作人員常常要進行學生成績的統(tǒng)計與分析，有時要統(tǒng)計幾十、幾百、幾千甚至上萬名學生成績，而傳統(tǒng)的紙質(zhì)加人工統(tǒng)計的方式，不僅效率低下,而且容易出錯。EXCEL是微軟辦公套裝軟件的一個重要組件，廣泛地應用于學生成績統(tǒng)計等眾多領(lǐng)域。本文將以EXCEL 2007為例，以統(tǒng)計某縣學校、班級學生成績?yōu)槔齺斫榻BEXCEL在多數(shù)據(jù)成績統(tǒng)計中的應用，相信能夠?qū)ν性诮y(tǒng)計成績方面提供幫助。

一、考試人數(shù)統(tǒng)計

1.單條件考試人數(shù)統(tǒng)計

要統(tǒng)計分析數(shù)據(jù)，少不了統(tǒng)計人數(shù)。例如：要統(tǒng)計某縣小學三年級語文全縣各學校考試人數(shù)，因全縣學生的數(shù)據(jù)量非常大，每個學?？荚嚾藬?shù)從一百余人到近千人不等，如果僅僅使用COUNT函數(shù)分別對每個學校進行統(tǒng)計，不僅效率低下而且勞形苦心。這時，如果使用COUNTIF函數(shù)按照學校來統(tǒng)計考試人數(shù)，就會簡化、方便很多，具體步驟如下。

（1）根據(jù)工作表中的數(shù)據(jù)建立好統(tǒng)計內(nèi)容。選中H2單元格，在編輯欄中輸入公式：=COUNTIF（B2:B10,G2）,按回車鍵，即可計算出“城東小學”實考人數(shù)。

（提示：為方便顯示，例子只列舉了有限條數(shù)的記錄，以下均同。B2:B10表示需要統(tǒng)計的滿足條件的單元格區(qū)域，G2表示指定的統(tǒng)計條件。）

（2）選中H2單元格，向下復制公式，即可計算出其它學校的考試人數(shù)（如圖1）。

圖1

2.雙條件考試人數(shù)統(tǒng)計

如果要統(tǒng)計各班考試人數(shù)，既要滿足學校匹配和班級匹配兩個條件，這時使用COUNTIFS函數(shù)則較為方便，COUNTIFS函數(shù)是EXCEL 2007以后版本新增的多條件統(tǒng)計函數(shù)。統(tǒng)計班級考試人數(shù)可以使用COUN?TIFS函數(shù)按如下方法設置求解公式。

（1）根據(jù)工作表中的數(shù)據(jù)建立好統(tǒng)計內(nèi)容。選中I2單元格，在編輯欄中輸入公式：=COUNTIFS（B2:B10,G2,C2:C10,H2）,按回車鍵，即可計算出“城東小學1班”的實考人數(shù)。

（提示：B2:B10表示需要滿足的第一個條件單元格區(qū)域，G2表示第一個判斷條件。C2:C10表示需要滿足的第二個條件單元格區(qū)域，H2表示第二個判斷條件。）

（2）選中I2單元格，向下復制公式，即可計算出各所學校相應的考試人數(shù)（如圖2）。

圖2

二、平均分統(tǒng)計

1.單條件平均分統(tǒng)計

學校學科平均分是評價各學校該學科成績的一個重要指標，統(tǒng)計學校學生平均分則可以通過AVERAGE函數(shù)來計算。而計算對應學校學生的平均分，可以使用AVERAGE函數(shù)按如下方法設置求解公式。

（1）選中H2單元格，在編輯欄中輸入公式：=AVER?AGE(IF(B2:B10=G2,E2:E10))，按“CTRL+SHIFT+EN?TER”組合鍵鎖定數(shù)組公式，即可計算出“城東小學”學生平均成績。

（提示：因要通過這個單一的公式，執(zhí)行多個輸入的操作并產(chǎn)生多個結(jié)果，所以，要通過“CTRL+SHIFT+ENTER”組合鍵鎖定數(shù)組公式。按下“CTRL+SHIFT+ENTER”后，公式變成｛=AVERAGE(IF(B2:B10=G2,E2:E10))｝，這里要注意“{}”不能手工鍵入，必須按組合鍵由系統(tǒng)自動產(chǎn)生。）

（2）選中H2單元格，將公式向下填充，即可分別求出其它學校的平均成績（如圖3）。

圖3

2.雙條件平均數(shù)統(tǒng)計

統(tǒng)計班級平均分既要滿足學校相同和班級相同兩個條件。對于同時滿足多個條件的數(shù)據(jù)求平均值，可以利用AVERAGE函數(shù)添加多個條件進行統(tǒng)計?，F(xiàn)在利用AVERAGE函數(shù)統(tǒng)計各所學校各個班級考生平均分，設置方法如下。

（1）選中I2單元格，在編輯欄中輸入公式：=AVER?AGE(IF((B2:B10=G2)*(C2:C10=H2),E2:E10))，按“CTRL+SHIFT+ENTER”組合鍵鎖定數(shù)組公式，公式變成｛=AV?ERAGE(IF((B2:B10=G2)*(C2:C10=H2),E2:E10))｝即可計算出“城東小學1班”學生平均成績。

（提示：公式中“*”表示將各條件進行相聯(lián)。）

（2）選中I2單元格，將公式向下填充，即可分別求出各所學校相應班級的平均成績（如圖4）。

圖4

三、及格率、優(yōu)秀率統(tǒng)計

1.雙條件及格率、優(yōu)秀率統(tǒng)計

要統(tǒng)計各學校及格率和優(yōu)秀率，首先要統(tǒng)計各學校的及格人數(shù)與優(yōu)秀人數(shù)，可以通過COUNTIFS函數(shù)按照“學?！焙汀?=60”兩個條件來統(tǒng)計，統(tǒng)計方法如下。

（1）選中I2單元格，在編輯欄中輸入公式：=COUN?TIFS(B2:B10,G2,E2:E10,“>=60”,按回車就可計算出及格人數(shù)。

（2）選中I2單元格，將公式向下填充，即可分別求出其它學校的及格人數(shù)（如圖5）。

圖5

（3）選中J2單元格，在編輯欄中輸入公式：=I2/H2*100，按回車就可計算出及格率。選中J2單元格，將公式向下填充，即可分別求出其它學校的及格率（如圖5）。

同理，可編輯公式求出各學校的優(yōu)秀人數(shù)及優(yōu)秀率（如圖6）。

圖6

2.多條件及格率、優(yōu)秀率統(tǒng)計

班級及格人數(shù)、優(yōu)秀人數(shù)可以利用COUNTIFS函數(shù)增加條件來統(tǒng)計，每個條件都需要有兩個參數(shù)，一個是單元格選取，另一個就是判斷條件。本例中求及格人數(shù)需要滿足“學?！?、“班級”和“>=60”三個條件。班級及格率按如下方法設置求解公式。

（1）選中J2單元格，在編輯欄中輸入公式：=COUN?TIFS(B2:B10,G2,C2:C10,H2,E2:E10,">=60"),按回車就可計算出及格人數(shù)（如圖7）。

（2）選中J2單元格，將公式向下填充，即可分別求出其它各學校各班級的及格人數(shù)。

（3）選中K2單元格，在編輯欄中輸入公式：=J2/I2*100，按回車就可計算出及格率。選中K2單元格，將公式向下填充，即可求出其它學校各班級的及格率（如圖7）。

圖7

同理，可編輯公式求出各學校各班級的優(yōu)秀人數(shù)及優(yōu)秀率（如圖8）。

圖8

四、名次統(tǒng)計

當計算出各校的平均分、及格率、優(yōu)秀率之后，可以計算出各校的綜合指數(shù)，進而根據(jù)綜合指數(shù)，利用RANK函數(shù)得出各校的名次。

（1）選中J2單元格，在編輯欄中輸入公式：=E2+G2+I2，按回車鍵確定就可得到“城東小學”的學科綜合成績。

（2）選中J2單元格，將公式向下填充，即可分別求出其它學校的綜合指數(shù)（如圖9）。

圖9

（3）選中K2單元格，在編輯欄中輸入公式：=RANK(J2,$J$2:$J$10)，按回車確定，得到城東小學的名次。

（提示：因排序的范圍J2:J10一定，所以，要絕對引用這個范圍，輸入公式選中J2:J10，按F4，即可絕對引用這個區(qū)域。）

（4）選中K2單元格，將公式向下填充，即可分別求出其它學校名次（如圖10）。

圖10

五、分段統(tǒng)計

1.單條件分數(shù)段統(tǒng)計

在成績統(tǒng)計與分析中,經(jīng)常會遇到要將成績進行分段統(tǒng)計的情況，Excel中分段統(tǒng)計的方法有很多，而本文認為最方便好用的是用FREQUENCY函數(shù)來實行分段統(tǒng)計了。它可以只通過一條數(shù)組公式就輕松地統(tǒng)計出各分數(shù)段的人數(shù)分布。例如：要統(tǒng)計出E2:E10區(qū)域內(nèi)相應分數(shù)段內(nèi)的人數(shù)分布可以按照如下方法進行。

（1）在工作表中建立數(shù)據(jù)并輸入所有要參與統(tǒng)計的分數(shù)段及每個分數(shù)段的最高臨界數(shù)。

（2）用鼠標選擇區(qū)域I2至I5，在編輯欄內(nèi)輸入公式：=FREQUENCY(E2:E10,H2:H5)。

（3）按“CTRL+SHIFT+ENTER”組合鍵產(chǎn)生數(shù)組公式“{=FREQUENCY(E2:E10,H2:H5)}”，完成后I2:I5顯示的數(shù)字即各校相應分數(shù)段的人數(shù)（如圖11）。

圖11

2.雙條件分數(shù)段統(tǒng)計

除了對全縣成績進行分數(shù)段統(tǒng)計外，有時還要對各校成績進行分數(shù)段統(tǒng)計，這時可利用FREQUENCY函數(shù)增加IF條件，進行統(tǒng)計，操作步驟如下。

（1）在工作表中輸入所有要參與統(tǒng)計的分數(shù)段、每個分數(shù)段的最高臨界數(shù)及學校。

（2）用鼠標選擇區(qū)域J2至J5，在編輯欄內(nèi)輸入公式：=FREQUENCY(IF($B$2:$B$10=J1,$E$2:$E$10),$H$2:$H$5)。按“CTRL+SHIFT+ENTER”組合鍵產(chǎn)生數(shù)組公式｛=FREQUENCY(IF($B$2:$B$10=J1,$E$2:$E$10),$H$2:$H$5)｝,即可求出城東小學學生各個分數(shù)段的相應人數(shù)。

（提示：輸入公式=FREQUENCY(IF(B2:B10=J1,E2:E10),H2:H5)后，因考慮到公式中某些區(qū)域復制時不再變化，即需要使用絕對引用，選中相應的區(qū)域按F4，可變成上述公式。）

（3）選中J2：J5單元格，將公式向右填充，即可分別求出其它學校各分數(shù)段的人數(shù)。完成后K2:L5將顯示如圖12所示。

圖12

實踐證明，正確巧妙地使用EXCEL相關(guān)函數(shù)對統(tǒng)計多數(shù)據(jù)、多條件的學生成績能起到事半功倍的作用。隨著教育信息化的不斷深入，學生的成績統(tǒng)計與分析越來越電子化，掌握一些常用EXCEL的成績統(tǒng)計技巧，會在成績統(tǒng)計與分析的工作中如虎添翼。