巧用Excel分析評價英語試卷

張維維Summary：Excel是大家熟悉的辦公軟件，利用Excel可以輕松完成復(fù)雜的試卷質(zhì)量分析與評價。從而使英語教師對整個教學(xué)過程有一個全面、系統(tǒng)、科學(xué)的認(rèn)識，為強化教學(xué)監(jiān)控與提高教學(xué)質(zhì)量提供準(zhǔn)確化、科學(xué)化的量化依據(jù)。Key：Excel；英語試卷；測試；統(tǒng)計分析目前，90%的初中每周會有一次單元測驗，再加上月考，期中考，期末考，教師僅憑手工進行數(shù)據(jù)資料的整理和計算，工作繁重枯燥，只能提供考生的考試分?jǐn)?shù)、平均分和及格率等基本考試情況。利用Excel可以輕松完成復(fù)雜的試卷質(zhì)量分析與評價。在教育統(tǒng)計學(xué)中，對統(tǒng)計資料的處理分為三個步驟進行：第一步是全面而準(zhǔn)確地收集客觀數(shù)據(jù)資料；第二步是整理數(shù)據(jù)資料；第三步是統(tǒng)計資料的計算和分析。一、數(shù)據(jù)資料的計算和分析數(shù)據(jù)資料的收集和整理以后，為了更客觀地解釋考生分?jǐn)?shù)，更好地了解教學(xué)班的考試情況，一般通過如下幾個統(tǒng)計參數(shù)進行計算和分析，在此舉例說明。下例對某班一次“英語單元測試”成績進行統(tǒng)計分析，將學(xué)生成績填入Excel的單元格區(qū)域“B2:B15”中，如表1所示。1．班級人數(shù)。在Excel表中可用COUNT函數(shù)計算，例如：在單元格E3中填入公式=COUNT(B2：B15)。如果有學(xué)生缺考，應(yīng)該用COUNTA函數(shù)對非數(shù)值型數(shù)據(jù)進行統(tǒng)計,例如:在單元格D3填入公式=COUNTA(A2：A15)。2．班級最高分?？捎肕AX函數(shù)計算，例如：在單元格F3中填入=MAX(B2：B15)。3．班級最低分?？捎肕IN函數(shù)計算，例如：在單元格中G3填人=MIN(B2：B15)。4．班級平均分?？捎肁VERAGE函數(shù)來計算，例如：在單元格H3中填入公式：AVERAGE(B2:B15)。5．及格率。通常將60分設(shè)定為及格線。在Excel中可用COUNTIF函數(shù)來計算，例如：在單元格J3中填入=COUNTIF(B2:B15，“>=60”)／COUNTA(A2：A15)。6．優(yōu)秀率。初中英語教學(xué)通常將95分以上設(shè)定為優(yōu)秀分。還是用COUNTIF函數(shù)來計算，例如：在單元格D9中填入=COUNTIF(B2：B15，“>=95”)／COUNTA(A2：A15)。7．分?jǐn)?shù)段。將考生成績按照高低分進行分?jǐn)?shù)段的統(tǒng)計，可以大致了解考試出題難度組合狀況、考生成績平衡性等。在Excel中可用COUNTIF函數(shù)來統(tǒng)計。例如：“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”)進行統(tǒng)計，其余類似。8．平均差。該參數(shù)表示全班學(xué)生成績與全班平均分的絕對偏差的平均值，用于測評全部學(xué)生成績與平均分的差異情況。在Excel中可用AVEDEV函數(shù)來計算。例如：在單元格E9中填入公式=AVEDEV(B2:B15)。9．標(biāo)準(zhǔn)差。這是反映考生考試成績波動情況和判斷、區(qū)分考生成績的最重要的統(tǒng)計量。理論上認(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ù)在教育工作中的用途很多，它可以用來比較各個學(xué)生的成績在班級成績中的地位，也可以用來比較某個學(xué)生在兩種或多種測驗中所得分?jǐn)?shù)的優(yōu)劣。計算機可以方便的將原始分轉(zhuǎn)換成標(biāo)準(zhǔn)分，使課程之間和班級之間具有可比性，找到各班、年級在校內(nèi)的位置，從而對全校教學(xué)情況一目了然。例如：計算某個學(xué)生(陳一)的標(biāo)準(zhǔn)分，在單元格中填入公式=(B2—AVERAGE(B2：B15))／STDEV(B2：B15)。二、考卷的分析和評估直方圖工具：以某校八年級某班期末考試成績?yōu)槔瑢⒖荚嚦煽冚斎隕xcel表格C1:C41。該測驗最高分為100，最低分為78，先確定一個區(qū)間[77.1,102.1](組限通常取比數(shù)據(jù)的精度高一位，即多一位小數(shù)點或個位數(shù)，以免數(shù)據(jù)剛好落在端點上)。將區(qū)間[77.1,102.1]等分為5個小區(qū)間，即組距△=（102.1-71.1）/5=5。這樣各小區(qū)間的組限端點從左至右依次為：77.1，82.1，87.1，92.1，97.1和102.1打開EXCEL文檔，點開“工具”——“數(shù)據(jù)分析”——“直方圖”，在“輸入?yún)^(qū)域”對話窗輸入C1:C41（即學(xué)生成績所在欄）,在“接受區(qū)域”輸入組限端點所在地單元格區(qū)域，選中“標(biāo)志位在第一行”。即可得到直方圖：根據(jù)直方圖，我們可以大致看出某項目（如某次測試的成績）的分布圖形，從而判定該項目所蘊涵的意義。分布圖形一共分六種：正態(tài)分布說明測試結(jié)果與學(xué)生的實際情況一致，各種難度的項目比例合理。正偏態(tài)分布說明試題難度偏高，難度較大的項目比例偏大。呈這種分布的試題有利于將成績優(yōu)秀的學(xué)生和中等程度的學(xué)生區(qū)別開，但不利于將中等程度的學(xué)生和成績較差的學(xué)生區(qū)別開。負(fù)偏態(tài)分布說明試題難度偏低，難度較低的項目比例偏大。呈這種分布的試題有利于將成績較差的學(xué)生和中等程度的學(xué)生區(qū)別開，但不利于將中等程度的學(xué)生和成績優(yōu)秀的學(xué)生區(qū)別開。尖峰態(tài)/陡峭型分布說明試題中同等難度的項目較多，梯度偏小。呈這種分布的試題幾乎不能將不同程度的學(xué)生區(qū)分開，分?jǐn)?shù)分布過于集中。低峰態(tài)/平坡型分布說明試題中各種難度的項目比例接近，梯度較大。呈這種分布的試題區(qū)分度較高，但分?jǐn)?shù)之間的差異偏大。雙峰型分布說明試題存在兩極分化現(xiàn)象，即難度偏高的和難度偏低的項目較多，而中等難度的項目偏少，項目難度的分布缺乏梯度，不夠合理。呈這種分布的試題可以區(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ù)過于集中。Reference：1.黃光揚.《教育測量與評價》華東師范大學(xué)出版社，20022.侯曉霞，王子勇.《試卷質(zhì)量分析與評價系