EXCEL在多數據成績統計中的應用

☆周曉敏

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

課任教師、教務主任、教研員等教學工作人員常常要進行學生成績的統計與分析，有時要統計幾十、幾百、幾千甚至上萬名學生成績，而傳統的紙質加人工統計的方式，不僅效率低下,而且容易出錯。EXCEL是微軟辦公套裝軟件的一個重要組件，廣泛地應用于學生成績統計等眾多領域。本文將以EXCEL 2007為例，以統計某縣學校、班級學生成績為例來介紹EXCEL在多數據成績統計中的應用，相信能夠對同行在統計成績方面提供幫助。

一、考試人數統計

1.單條件考試人數統計

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

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

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

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

圖1

2.雙條件考試人數統計

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

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

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

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

圖2

二、平均分統計

1.單條件平均分統計

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

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

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

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

圖3

2.雙條件平均數統計

統計班級平均分既要滿足學校相同和班級相同兩個條件。對于同時滿足多個條件的數據求平均值，可以利用AVERAGE函數添加多個條件進行統計。現在利用AVERAGE函數統計各所學校各個班級考生平均分，設置方法如下。

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

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

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

圖4

三、及格率、優秀率統計

1.雙條件及格率、優秀率統計

要統計各學校及格率和優秀率，首先要統計各學校的及格人數與優秀人數，可以通過COUNTIFS函數按照“學校”和“>=60”兩個條件來統計，統計方法如下。

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

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

圖5

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

同理，可編輯公式求出各學校的優秀人數及優秀率（如圖6）。

圖6

2.多條件及格率、優秀率統計

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

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

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

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

圖7

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

圖8

四、名次統計

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

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

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

圖9

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

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

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

圖10

五、分段統計

1.單條件分數段統計

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

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

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

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

圖11

2.雙條件分數段統計

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

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

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

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

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

圖12

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