基于Excel的考試分數簡易定量分析方法

摘 要：文章使用Excel數據分析工具對某科考試分數進行定量分析，操作簡單快捷，得到了比較全面的、有價值的數據信息，便于使用數量統計的方法推斷考試的總體情況，分析考試班級差異，可供教師定量評價考試分數時推廣使用。

關鍵詞：數據分析；描述統計；直方圖；t檢驗

中圖分類號：TP311.56 文獻標識碼：B 文章編號：1673-8454（2011）02-0057-02

引言

考試試卷批改完之后，教師需要對某班的考試分數進行分析，計算平均分、最高分、最低分、標準差等描述統計量；進行頻度分析，統計各個分數段的人數，得出優秀率、及格率和不及格率；分析對比兩個班級的成績差異等。將考試分數輸入Excel單元格區域中，應用“數據分析工具”可以快速得到上述分析結果。下面通過例子演示Excel的分析過程。

在某科考試中，A班有20人，考試分數分布在B2:B21，B班有21人，考試分數分布在C2:C22中，如圖1所示。

根據上述試卷分析目的，Excel分析任務有三項：第一項是計算出考試分數的平均值、最大值、最小值、方差等基本統計量，可以采用Excel數據分析工具中的“描述統計”功能完成；第二項是統計各分數段的人數分布情況，可以采用Excel數據分析工具中的“直方圖”來完成；第三項是定量比較分析A班和B班的考試分數差異，可以采用Excel數據分析工具中的T檢驗、F檢驗完成。準備好數據后進行相應的設置，Excel能快速準確地給出相應的分析結果。下面簡略介紹上述三項分析任務通過Excel分析工具庫完成的過程、理論依據，以便使用者舉一反三，靈活應用。

一、Excel描述統計功能

1.操作

單擊菜單“工具”、“數據分析”，在彈出的數據分析窗口選擇“描述”統計，如圖2所示。

2.選項設置

“輸入區域”是待分析數據組所在單元格區域，A班分數位于B1:B21中，計算A班統計量應在“輸入區域”中選B1:B21。由于待分析數據是按列分布的，“分組方式”選“逐列”。“輸入區域”中第一個單元格B1顯示“A班分數”是該列的變量名稱，并非考試分數，所以要選上“標志位于第一行”。在“輸出區域”中選入單元格E1，表示要把A班的描述統計分析結果放在E1開始的E列中。另外選上“匯總統計”、“平均數置信度”、“第K大值”、“第K小值”。“第K大值”后面的方框中填上“1”表示給出最大的“1”個數，即最高分，根據需要可以自行選填數字。設置完之后，按“確定”即可求出A班分數的描述統計量。按同樣的操作方法可求出B班分數的統計量。

3.分析結果

Excel描述統計分析結果如圖3所示，已求出考試分數的平均值、最大值、最小值、方差等基本統計量。

二、Excel直方圖的使用

完成了第一項任務之后，下面使用Excel數據分析中的“直方圖”，統計各個分數段的人數分布情況。

1. “接收區域”數據準備

在D2：D5中輸入數值60、70、85、100。接收區域是數據分組的依據：“60”表示小于等于60的數組；“70” 表示大于60小于等于70的數組；“85”表示大于70小于等于85的數組；100表示大于85小于等于100的數組。

2.操作

選“工具”/“數據分析”/“直方圖”，彈出“直方圖”窗口，如圖4，進行相應選項的設置。大部分選項的含義與上述“描述統計”窗口一致，選上“累積百分率”、“圖表輸出”，按“確定”。對于B班分數采用同樣的操作方法。

3.分析結果

輸出結果如圖5。由分析結果可知，A班分數沒有60分以下的；大于60小于等于70有2人，占10%；70到85之間的有11人，占55%；85以上有7人，占35%。

三、T檢驗與F檢驗

下面采用T檢驗、F檢驗定量比較分析A班與B班分數的差異。先采用F檢驗判斷A、B班考試分數是否等方差，然后采用T檢驗判斷A、B班分數是否有顯著差異。

1. F檢驗

選“工具”/“數據分析”/“F檢驗 雙樣本方差”，如圖6所示。“變量1的區域”方框中選上A班分數B1:B21，“變量2的區域”方框中選上B班分數C1:C22，選上“標志”，默認的α值（顯著性水平）為0.05，“輸出區域”選D1，按“確定”，得到F方差齊性檢驗的結果。

F檢驗結果：P(F<=f) 單尾=0.271753086>0.05，接受同方差假設，即認為兩個班的考試分數方差是一致的。

2.T檢驗

選“數據分析”/“t-檢驗：雙樣本等方差假設”，如圖7所示。與F檢驗一樣，“變量1的區域”方框中選上A班分數B1:B21，“變量2的區域”方框中選上B班分數C1:C22，選上“標志”。“假設平均差”輸入“0”，表示原假設認為兩個班的分數不存在差異，即平均分之差為0。默認的α值為0.05，“輸出區域”選D1，按“確定”，得到T檢驗結果。

T檢驗結果：

P(T<=t) 雙尾=0.138418242>0.05，

t Stat=1.512698968，

t 雙尾臨界=2.022690901，即接

受原假設，認為A、B兩班的考試分數差異在統計上不顯著。

參考文獻：

[1]沈浩，李亦蘭，王迎迎.Excel高級應用與數據分析[M].北京：電子工業出版社，2008.

[2]賈俊平，何曉群，金勇進.統計學[M].北京：中國人民大學出版社，2000.

[3]呂紅升.用Excel的數據分析工具分析試卷[J].巢湖學院學報，2004，(6).（編輯：魯利瑞）