巧用Excel分析評價(jià)英語試卷

張維維

摘要：Excel 是大家熟悉的辦公軟件，利用Excel 可以輕松完成復(fù)雜的試卷質(zhì)量分析與評價(jià)。從而使英語教師對整個(gè)教學(xué)過程有一個(gè)全面、系統(tǒng)、科學(xué)的認(rèn)識，為強(qiáng)化教學(xué)監(jiān)控與提高教學(xué)質(zhì)量提供準(zhǔn)確化、科學(xué)化的量化依據(jù)。

關(guān)鍵詞：Excel； 英語試卷； 測試； 統(tǒng)計(jì)分析

目前，90%的初中每周會(huì)有一次單元測驗(yàn)，再加上月考，期中考，期末考，教師僅憑手工進(jìn)行數(shù)據(jù)資料的整理和計(jì)算，工作繁重枯燥，只能提供考生的考試分?jǐn)?shù)、平均分和及格率等基本考試情況。

利用Excel 可以輕松完成復(fù)雜的試卷質(zhì)量分析與評價(jià)。

在教育統(tǒng)計(jì)學(xué)中，對統(tǒng)計(jì)資料的處理分為三個(gè)步驟進(jìn)行：第一步是全面而準(zhǔn)確地收集客觀數(shù)據(jù)資料；第二步是整理數(shù)據(jù)資料；第三步是統(tǒng)計(jì)資料的計(jì)算和分析。

一、數(shù)據(jù)資料的計(jì)算和分析

數(shù)據(jù)資料的收集和整理以后，為了更客觀地解釋考生分?jǐn)?shù)，更好地了解教學(xué)班的考試情況，一般通過如下幾個(gè)統(tǒng)計(jì)參數(shù)進(jìn)行計(jì)算和分析，在此舉例說明。

下例對某班一次“英語單元測試”成績進(jìn)行統(tǒng)計(jì)分析，將學(xué)生成績填入Excel的單元格區(qū)域“B2:B15”中，如表1所示。

1． 班級人數(shù)。在Excel表中可用COUNT函數(shù)計(jì)算，例如：在單元格E3中填入公式=COUNT(B2：B15)。如果有學(xué)生缺考，應(yīng)該用COUNTA函數(shù)對非數(shù)值型數(shù)據(jù)進(jìn)行統(tǒng)計(jì),例如:在單元格D3填入公式= COUNTA(A2：A15)。

2． 班級最高分?？捎肕AX函數(shù)計(jì)算，例如：在單元格F3中填入=MAX(B2：B15)。

3． 班級最低分。可用MIN函數(shù)計(jì)算，例如：在單元格中G3填人=MIN(B2：B15)。

4． 班級平均分。可用AVERAGE函數(shù)來計(jì)算，例如：在單元格H3中填入公式：AVERAGE(B2:B15)。

5． 及格率。通常將60分設(shè)定為及格線。在Excel中可用COUNTIF函數(shù)來計(jì)算，例如：在單元格J3中填入=COUNTIF(B2:B15，“>=60”)／COUNTA(A2：A15)。

6． 優(yōu)秀率。初中英語教學(xué)通常將95分以上設(shè)定為優(yōu)秀分。還是用COUNTIF函數(shù)來計(jì)算，例如：在單元格D9中填入=COUNTIF(B2：B15，“>=95”)／COUNTA(A2：A15)。

7．分?jǐn)?shù)段。將考生成績按照高低分進(jìn)行分?jǐn)?shù)段的統(tǒng)計(jì)，可以大致了解考試出題難度組合狀況、考生成績平衡性等。在Excel中可用COUNTIF函數(shù)來統(tǒng)計(jì)。例如：“80—89”分?jǐn)?shù)段可用公式=COUNTIF(B2：B15，“>=80”)—COUNTIF(B2：B15，“>=90”)，“70—79”分?jǐn)?shù)段可用=COUNTIF(B2：B15．“>=70”)—COUNTIF(B2：B25，“>=80”)，“60-69”分?jǐn)?shù)段可用=COUNTIF(B2：B15，“>=60”)—COUNTIF(B2：B15，“>=70”)，不及格人數(shù)可用=COUNTIF(B2：B15，“<60”)進(jìn)行統(tǒng)計(jì)，其余類似。

8． 平均差。該參數(shù)表示全班學(xué)生成績與全班平均分的絕對偏差的平均值，用于測評全部學(xué)生成績與平均分的差異情況。在Excel中可用AVEDEV函數(shù)來計(jì)算。例如：在單元格E9中填入公式=AVEDEV(B2:B15)。

9． 標(biāo)準(zhǔn)差。這是反映考生考試成績波動(dòng)情況和判斷、區(qū)分考生成績的最重要的統(tǒng)計(jì)量。理論上認(rèn)為標(biāo)準(zhǔn)差大，區(qū)分效果好，標(biāo)準(zhǔn)差小，成績分布則比較集中，通常要求將該值控制在“8—10”之間。在Excel中可用STDEV函數(shù)，例如：在單元格F9中填入公式=STDEV(B2:B15)。

10． 標(biāo)準(zhǔn)分。這是以標(biāo)準(zhǔn)差為單位，表示某一分?jǐn)?shù)與平均數(shù)的差。標(biāo)準(zhǔn)分?jǐn)?shù)在教育工作中的用途很多，它可以用來比較各個(gè)學(xué)生的成績在班級成績中的地位，也可以用來比較某個(gè)學(xué)生在兩種或多種測驗(yàn)中所得分?jǐn)?shù)的優(yōu)劣。計(jì)算機(jī)可以方便的將原始分轉(zhuǎn)換成標(biāo)準(zhǔn)分，使課程之間和班級之間具有可比性，找到各班、年級在校內(nèi)的位置，從而對全校教學(xué)情況一目了然。例如：計(jì)算某個(gè)學(xué)生(陳一)的標(biāo)準(zhǔn)分，在單元格中填入公式=(B2—AVERAGE(B2：B15))／STDEV(B2：B15)。

二、考卷的分析和評估

直方圖工具：以某校八年級某班期末考試成績?yōu)槔?，將考試成績輸入Excel表格C1:C41。該測驗(yàn)最高分為100，最低分為78，先確定一個(gè)區(qū)間[77.1, 102.1] (組限通常取比數(shù)據(jù)的精度高一位，即多一位小數(shù)點(diǎn)或個(gè)位數(shù)，以免數(shù)據(jù)剛好落在端點(diǎn)上)。將區(qū)間[77.1, 102.1]等分為5個(gè)小區(qū)間，即組距△=（102.1-71.1）/ 5=5。這樣各小區(qū)間的組限端點(diǎn)從左至右依次為：77.1，82.1，87.1，92.1，97.1和102.1

打開EXCEL文檔，點(diǎn)開“工具”——“數(shù)據(jù)分析”——“直方圖”，在“輸入?yún)^(qū)域”對話窗輸入C1:C41（即學(xué)生成績所在欄）,在“接受區(qū)域”輸入組限端點(diǎn)所在地單元格區(qū)域，選中“標(biāo)志位在第一行”。即可得到直方圖：

根據(jù)直方圖，我們可以大致看出某項(xiàng)目（如某次測試的成績）的分布圖形，從而判定該項(xiàng)目所蘊(yùn)涵的意義。分布圖形一共分六種：

正態(tài)分布 說明測試結(jié)果與學(xué)生的實(shí)際情況一致，各種難度的項(xiàng)目比例合理。

正偏態(tài)分布 說明試題難度偏高，難度較大的項(xiàng)目比例偏大。呈這種分布的試題有利于將

成績優(yōu)秀的學(xué)生和中等程度的學(xué)生區(qū)別開，但不利于將中等程度的學(xué)生和成績較差的學(xué)生區(qū)別開。

負(fù)偏態(tài)分布 說明試題難度偏低，難度較低的項(xiàng)目比例偏大。呈這種分布的試題有利于將成績較差的學(xué)生和中等程度的學(xué)生區(qū)別開，但不利于將中等程度的學(xué)生和成績優(yōu)秀的學(xué)生區(qū)別開。

尖峰態(tài)/陡峭型分布 說明試題中同等難度的項(xiàng)目較多，梯度偏小。呈這種分布的試題幾乎不能將不同程度的學(xué)生區(qū)分開，分?jǐn)?shù)分布過于集中。

低峰態(tài)/平坡型分布 說明試題中各種難度的項(xiàng)目比例接近，梯度較大。呈這種分布的試題區(qū)分度較高，但分?jǐn)?shù)之間的差異偏大。

雙峰型分布 說明試題存在兩極分化現(xiàn)象，即難度偏高的和難度偏低的項(xiàng)目較多，而中等難度的項(xiàng)目偏少，項(xiàng)目難度的分布缺乏梯度，不夠合理。呈這種分布的試題可以區(qū)別中等程度的學(xué)生，但不利于區(qū)別出成績優(yōu)秀的學(xué)生和成績較差的學(xué)生。

于是我們可以得到結(jié)論：

1. 根據(jù)該班級測試的直方圖，可以判定該正態(tài)分布為負(fù)偏態(tài)分布。有利于將成績較差的學(xué)生和中等程度的學(xué)生區(qū)分開，但不利于將中等程度的學(xué)生和優(yōu)秀學(xué)生區(qū)分開。

2. 該正態(tài)分布為尖峰態(tài)分布。不能將不同程度的學(xué)生區(qū)分開，分?jǐn)?shù)過于集中。

參考文獻(xiàn)：

1. 黃光揚(yáng).《教育測量與評價(jià)》華東師范大學(xué)出版社，2002

2. 侯曉霞，王子勇.《試卷質(zhì)量分析與評價(jià)系統(tǒng)地設(shè)計(jì)》計(jì)算機(jī)工程,2000,26(11):151-153

3. 李翠，李進(jìn)才《利用Excel對試卷質(zhì)量進(jìn)行系統(tǒng)分析》集寧師專學(xué)報(bào)，2001，23（4）：26-28

4. 宣仲良《Excel實(shí)現(xiàn)的試卷分析質(zhì)量表》江蘇大學(xué)學(xué)報(bào)（高校研究版）2004,26(3):88-92