應用Excel實現成績統計與試卷分析

陳智勇 王海娥

摘要：本文介紹了應用Excel制作學生成績考核表及試卷分析表模塊的方法，為分析學生學習情況和教師教學效果提供了快捷而準確的統計軟件。并根據教育測量學原理，提出了改進建議。

關鍵詞：成績考核；試卷分析；教育統計

1引言

成績統計與試卷分析是教學考核工作的一個重要環節，也是一項非常復雜細致的工作。如果采取手工計算，不僅費力費時而且結果易出差錯。專業的統計軟件SPSS、SYS-TAT等其功能固然強大，統計分析的專業性、權威性不可否認，但是對于沒有開設統計學專業的院校這些軟件并不常用。學生自己也比較難以找到相應的工具。微軟公司開發的Excel軟件作為一款優秀的表格軟件，其提供的統計分析功能雖然比不上專業統計軟件，但它比專業統計軟件易學易用。便于掌握。以學生試卷成績為依據，利用Excel建立了試卷分析和成績統計兩個模塊，并能夠根據錄入信息自動進行成績統計和試卷分析，避免了大量、復雜的手工計算，使用效率很高。

2成績錄入模塊

大量的原始成績需要輸入，一旦有誤便會影響分析結果。人工多次校對也費時費力，效果得不到保證。在這里我們應用“二次校驗輸入法”，在格式相似的成績表2中再次錄入，和成績表1做比較，如果不一致則自動顯示出錯。這種方法高效且出錯率在萬分之一以下。

(1)錄入成績

把成績錄入到Excel工作薄中。命名為“1”。

(2)二次校驗

新建校驗工作薄，命名為“2”。“姓名”和“學號”與表1布局相同，同時在每門分數后增加一列。這里以檢驗英語成績為例，在D3中輸入公式IF(AND(1＇!C3=C3，AND(C3＞＝0.C3＞=120))，“pass”，“error”)。如果出現單元格的數值與表1中對應的數據不一致或越界就顯示錯誤。此時設定滿分為150分。單擊菜單欄上的“格式一條件格式”，設計單元格內容為“error”時顯示紅色。如果表2中C3單元格中輸入110分，和表1的原始分數一致，D3就顯示“pass”；C4單元格與表1相關單元格分數不一致時，D4顯示紅色“error”。

(3)鎖定原始數據

點擊菜單欄上的“工具”。進入“保護→保護工作表”，輸入密碼確定。這樣做以防疏忽導致的錯誤。設置密碼對數據處理起到保護作用。

3成績指標分析模塊

對成績進行錄入、匯總之后，新建工作表命名為“試卷分析表”，試卷分析表格式按照人數、平均分、標準差、優秀率四個方面對試卷自動進行相關數據分析。

(1)人數

人數指每門課程參加考試的總人數。在此單元格中輸入“COUNT(C9:C1000)”，由于每門課程參加考試的人數不同，可以設計大于最大的人數。

(2)平均分

平均分指每門課程的平均成績，是衡量學習成績的重要參數。在此單元格輸入“AVERAGE(C9:C1000)”。

(3)標準差

樣本中各數據與樣本平均數的差的平方和的平均數叫做樣本方差，標準差指樣本方差的算術平方根。如果標準差大，則反映學習成績波動大，均衡的素質教育沒有得到好的實現。在此單元格輸入“STDEV(D9:D1000)”。

(4)優秀率

本例滿分為150分，輸入“COUNTIF(C9:C1000，“＞=120”)／COUNT(C9:C1000)”。

獲得每個單位成績統計數據后，數據分項放入同一表內，以完成總成績分析數據統計。

4改進建議

根據教育統計與測量學原理，可增加以下分析指標：

(1)增加對試卷效度分析

試卷效度是反映試卷有效性和準確性的指標，即考試達到預期目標的程度。

(2)增加對試卷信度分析

試卷信度是檢驗考試結果穩定性和可靠性的指標。

(3)增加對試卷區分度分析

試卷區分度是指考試題目對考生水平的區分程度。

5結束語

合理而高效地進行試卷分析，對提高今后的命題組卷水平，為建立與不斷完善題庫，為改進考試工作都是很重要的。