靈活運用Excel分析學生成績

摘要:考試結束后，通過對學生考試成績的分析，教師可以對該課程的教學工作進行系統的總結，更好的安排下一階段的教學工作。該文根據個人經驗，介紹了如何靈活運用Excel分析學生成績的方法與技巧，從而使教學工作變得更加簡單、高效。

關鍵詞:文本數據;數據有效性;凍結窗口;函數

中圖分類號:TP317文獻標識碼:A文章編號:1009-3044(2010)02-432-02

Flexible Use of Excel in Analyzing Student's Score

GONG Jing-min

(YingTian Vocational and Technical College， Nanjing 210046，China)

Abstract: After examination， by analyzing student's test scores， teachers can make a systematic summary for their teaching of the course and make a better plan for the next phase of teaching accordingly. Based on personal experience， this article introduces the methods and techniques on how to use the Excel flexibly to analyze student's score ， so as to make teaching more simple and efficient.

Key words: textual data; data validity; freeze window; function

每當考試結束后，教師都會對學生的考試成績進行統計分析，如總分、平均分、排名以及各分數段學生人數的統計等。通過對成績的分析，可以對課程進行合理的安排、系統的總結，更好的布置下一階段的教學工作。

1 數據錄入

1.1 “0”開頭數字的錄入

數據錄入是統計成績的基礎。對于以“0”開頭的學號數據(如“0851201”)，若在Excel的單元格中直接輸入“0851201”(不包括引號，下同)，則會顯示“851201”，前端的“0”被自動舍去。解決這個問題的方法有兩種，一種是對單元格進行格式的設置，即將其設為文本格式。具體操作法是選中所有需要輸入前端帶“0”的單元格，然后單擊右鍵，在彈出的快捷菜單中選擇“設置單元格格式...”命令，在彈出的“單元格格式”對話框中單擊“數字”選項卡，再在“分類”列表框中選擇“文本”，最后點“確定”按鈕即可。另一種方法是在單元格中先輸入一個半角單引號(')，然后再輸入數據。例如:要輸入學號0851201，方法是輸入“'0851201”，輸入完后單引號會自動消失，此時Excel會把帶有單引號的數據自動轉換為文本數據。值得注意的是，文本數據不具備數值數據的特性，不可以進行運算等操作。

1.2 0~100之間數據的限定

學生的成績一般只能在0~100分之間，Excel提供了一個“有效性”功能，可以防止由于錄入錯誤而使成績在0~100分以外。具體操作方法為:選中需要輸入成績的單元格，單擊“數據”菜單中的“有效性”命令，在彈出的“數據有效性”對話框中對“設置”選項卡各選項進行設置，如圖1所示，最后點擊“確定”按鈕即可。

進行以上設置后，如果在相應的單元格中輸入了0~100以外的數據，則會出現提示信息，提醒數據錄入者注意，從而保證了錄入數據的準確性。

1.3 凍結窗口

學生成績表通常數據量較大。隨著數據的輸入，行數的增加，成績表標題欄很快就被移動到工作表可視區以外，不利于對工作表的編輯工作。解決這個問題的方法是:單擊標題欄下面一行的行號，如“3”，以選定該行，再單擊“窗口”菜單中的“凍結窗格”命令。此時第1、2兩行已被凍結，不能被移動。這大大方便了對工作表的編輯工作。倘若要取消對標題欄的凍結， 則可單擊“窗口”菜單中的“撤銷窗口凍結”命令即可。

2 成績分析

2.1 總分和平均分的統計

每次考試后，教師都需要對學生的總分和平均分進行統計計算，我們可以利用Excel提供的SUM函數和AVERAGE函數來實現，以“08512班學生成績單”為例。

1)在F3單元格輸入公式“=SUM(C3:E3)”，按回車鍵后即可計算出01號學生的總分。

2)同上述的方法，在G3單元格中計算01號學生的平均分，此時的公式為“=AVERAGE(C3:E3)”。

3)拖動鼠標選擇F3、G3兩個單元格，并將鼠標移至G3右下角的“填充柄”上，待鼠標指針變成“+”時，向下拖動鼠標，完成其他學生總分和平均分的計算，如圖2所示。

2.2 根據總分排名

關于排名次的問題，有些老師的做法是先將總分按大到小進行排序，然而利用填充柄輸入名次，再按學號的順序排序后還原，這樣做固然很煩瑣。Excel中的RANK函數可以方便地解決這個問題。

在H3單元格中輸入公式“=RANK(F3，$F$3:$F$14)”，按回車鍵后即可計算出01號學生按總分在全班的排名情況。利用填充柄工具，向下拖動鼠標完成其他學生的排名計算。需要說明的是，在公式中出現一個“$”符號，是絕對地址的引用(當復制公式時，F3:F14的范圍始終保持不變)。若不使用“$”符號，F3:F14的范圍將會隨著公式的變化而發生改變，從而產生錯誤的排名。快速輸入“$”符號的方法是:在輸入“F3”后按一下F4功能鍵，即可變為“$F$3”。

2.3 成績分布統計

在分析考試成績的時候，經常要對<60，60~69、70~79、80~89、≥90分數段的人數進行統計。利用FREQUENCY函數可以非常高效的來實現這一功能。

FREQUENCY函數的作用是計算數值在某個區域內的出現頻率，然后返回一個垂直數組。由于函數 FREQUENCY 返回一個數組，所以它必須以數組公式的形式輸入，即輸入完公式后，按“Ctrl+Shift+Enter”組合鍵形成數組公式。該函數的語法為FREQUENCY(data_array，bins_array)。Data_array是一個數組或對一組數值的引用，要為它計算頻率;Bins_array是一個區間數組或對區間的引用，該區間用于對 data_array 中的數值進行分組。Data_array中的元素個數比 Bins_array 中的元素個數多 1 個。多出來的元素表示最高區間之上的數值個數。對“08512班學生成績單”文件的處理如下:

1)在B16:B20單元格中輸入說明性文字“<60，60~69、70~79、80~89、≥90”。

2)在F16:F19單元格(可以是任意單元格輸入)中輸入學生成績的分段點，分別為59、69、79和89。即統計<60，60~69、70~79、80~89、≥90五個分數段的人數。

3)選中要進行分段統計的單元格區域C16:C20，輸入公式“=FREQUENCY(C3:C14，F16:F19)”。

4)輸入完上述內容后，按下“Ctrl+Shift+Enter”組合鍵以形成數組公式，此時在公式的兩端會出現數組公式標志，即一對大括號“{｝”。

5)在C16:C20單元格區域便得到所需的統計結果，如圖3所示。

3 結束語

本文根據個人經驗介紹了利用Excel對學生成績進行統計分析的一些方法與技巧。通過這些技巧的應用，可以簡便和高效的完成學生成績統計工作，從而使教學工作更加簡單、高效。當然Excel的功能遠不止這些，需要我們不斷的去挖掘。

參考文獻:

[1] 杜茂康.Excel與數據處理[M].北京:電子工業出版社，2002.

[2] 漢龍.中文版Excel2003基礎應用與提高[M].上海:上海科學普及出版社，2006.

[3] 韓小良.Excel2003/2007公式與函數的使用藝術[M].北京:中國鐵道出版社，2009.

[4] 王月明，田海宇，王力群. Excel公式和函數在學生成績管理中的應用[J].中國科技信息，2008(7).

[5] 朱滿鳳.巧用Excel處理學生成績[J].科技資訊，2007(18).