用Excel進(jìn)行試題質(zhì)量分析

用Excel進(jìn)行試題質(zhì)量分析考試成績能否反映考生水平，試題質(zhì)量是關(guān)鍵。通過試題質(zhì)量分析，總結(jié)經(jīng)驗(yàn),指導(dǎo)老師對(duì)教學(xué)、命題進(jìn)行改進(jìn)，不斷提高教學(xué)質(zhì)量與命題質(zhì)量是一項(xiàng)有實(shí)際意義的工作。但是現(xiàn)在沒有這方面的軟件可用，老師一般都是手工計(jì)算加辦公軟件輔助，工作繁雜、容易出錯(cuò)、效率低下且不直觀，分析不夠準(zhǔn)確等，本文介紹一種實(shí)用的解決這個(gè)問題的方法。一、目的Microsoft?OfficeExcel現(xiàn)在是使用最為遍及的辦公軟件，于是就做了一個(gè)Excel試題質(zhì)量分析系統(tǒng)提供給廣大老師使用，老師們也可根據(jù)文中所述方法自己動(dòng)手制作一個(gè)更符合自己需要的試題質(zhì)量分析系統(tǒng)。下載地址：/files/872cdd82-b8a1-11de-874c-0014221b798a/1.1要求對(duì)試題質(zhì)量分析中的標(biāo)準(zhǔn)差、難度指數(shù)及區(qū)別指數(shù)的計(jì)算方法標(biāo)準(zhǔn)差：設(shè)有一組共n個(gè)數(shù)據(jù)七，x尸，七，x是它們的平均值。則：S=「—[(x—x)2+(x—x)2+,,,(x—x)2]\n12nS為這個(gè)數(shù)據(jù)的標(biāo)準(zhǔn)差。標(biāo)準(zhǔn)差是用來衡量一組數(shù)據(jù)波動(dòng)程度的一個(gè)重要指標(biāo)。若用氣，x2,x分別表示一個(gè)班級(jí)全部n個(gè)學(xué)生在一門課程考試中的成績，x表示他們的平均成績，則由上式計(jì)算得到的標(biāo)準(zhǔn)差反應(yīng)這個(gè)班級(jí)n個(gè)學(xué)生在此門課程考試中成績的波動(dòng)情況。標(biāo)準(zhǔn)差從一個(gè)側(cè)面反應(yīng)考試試卷的水平。難度(P)——即考卷題目的難易程度區(qū)分度(r)——即考卷區(qū)分學(xué)生優(yōu)劣的程度，區(qū)別指數(shù)好的試卷測定結(jié)果是好的學(xué)生分高，差的學(xué)生得分低。區(qū)別指數(shù)低的試卷測定結(jié)果是好的學(xué)生得分差距不大。區(qū)別指數(shù)適當(dāng)?shù)臏y定結(jié)果，能把學(xué)生好壞的差距拉開。具體計(jì)算方法是將全班學(xué)生每類考題的分?jǐn)?shù)按高低順序排列，然后兩端各取總?cè)藬?shù)的25%作為高低兩個(gè)分?jǐn)?shù)組(極端分組法)。難度：P=E+LX100%N區(qū)分度：,=空WN式中:E一高分組學(xué)生該題得分總和L一低分組學(xué)生該題得分總和N——高低二組總?cè)藬?shù)該題滿分和1.2標(biāo)準(zhǔn)難度指數(shù)(P)區(qū)別指數(shù)(r)<30%比較難>0.35極好試題30—70%可以接受0.25—0.34良好試題50—60%可靠0.15—0.24勉強(qiáng)可用，但必須修改>70%偏易<0.15不宜采用表1

圖1：二、制作過程2.1基本數(shù)據(jù)創(chuàng)建一個(gè)空白的工作簿，將二、制作過程2.1基本數(shù)據(jù)創(chuàng)建一個(gè)空白的工作簿，將“sheetl”命名為“data”用于輸入基本數(shù)據(jù)如H4單元格中輸入公式"=SUM(B5:G5))"如圖，然后填充全H112單元格。2.2取樣這是最為重要和關(guān)鍵的一步，在I86單元格中輸入公式：=IF(COUNT(B$5:B$65536)<4*(ROW()-85),"",LARGE(B$5:B$65536,ROW()-85))然后填充I86:N112區(qū)域，完成高分的取樣。低分取樣與高分取樣雷同在O86單元格輸入公式=IF(COUNT(B$5:B$65536)<4*(ROW()-85),"",SMALL(B$5:B$65536,ROW()-85))填充整個(gè)O86:T112區(qū)域，完成低分取樣。2.3計(jì)算學(xué)生成績分布的頻數(shù)圖2在data工作表中增加圖2內(nèi)容，選中J5:J14區(qū)域輸入公式："=FREQUENCY(H5:H65536,I5:I14)"按F2鍵，再按Ctrl+Shift+Enter組合鍵以數(shù)組公式的形式輸入公式。圖22.4制作報(bào)表將工作表sheet2改名為報(bào)表，按照?qǐng)D3制作出兩頁報(bào)表。ABCDEFGH1琪西南民族職業(yè)技術(shù)學(xué)院說卷湖拓昔23解名稱:雌垣J4部門:任^武匠:睥:5跆:考域三必6成翎艮好(SO-0S)中等(70-79)蛔00-69)不及郵60】78VF法平均鍥Stir撕第四溟13言1415分折是具161Y18192021IS。。年［月W三22三土任［谷孝)/主任():&年月日年￥=三圖32.5編輯公式D3中輸入公式：=data!D2&""D4中輸入公式：=data!B2&""G4中輸入公式：=data!F2&""H5中輸入公式：=COUNT(data!H:H)-1D7中輸入公式：=data!J14E7中輸入公式：=data!J13F7中輸入公式：=data!J12G7中輸入公式：=data!J11H7中輸入公式：=SUM(data!J5:J10)D8中輸入公式：”二。7/$日$5”并設(shè)置為百分比格式，填充至H8單元格。D9中輸入公式：=AVERAGE(data!H5:H65536)G9中輸入公式：=IF(COUNT(data!H5:H65536)<2,"",STDEV(data!H5:H65536))C11中設(shè)置格式為百分比并輸入公式：=IF(COUNT(data!I86:I65536)=0,"",(SUM(data!I86:I65536)+SUM(data!O86:O65536))/((COUNT(data!I86:I65536)+COUNT(data!O86:O65536))*data!B4))C12中輸入公式：=IF(C11="",",LOOKUP(C11,{0,0.3,0.5,0.6,0.7},{"*較難","可接受","可靠","可接受","偏易"}))C13中輸入公式：=IF(COUNT(data!I86:I65536)=0,"",(SUM(data!I86:I65536)-SUM(data!O86:O65536))*2/((COUNT(data!I86:I65536)+COUNT(data!O86:O65536))*data!B4))C14中輸入公式：=IF(C13="",",LOOKUP(C13,{0,0.15,0.25,0.35},{'不宜采用","勉強(qiáng)可用，但必須修改","良好試題","極好試題"}))，選擇C11:C14填充全H11:H14。至此所有公式全部輸入完畢，已能夠?qū)崿F(xiàn)自動(dòng)計(jì)算和分析功能。2.6生成學(xué)生成績分布圖表打開工作表“報(bào)表”，單擊“圖表向?qū)А卑粹o，打開“圖表向?qū)А睂?duì)話框，在“圖表類型”中選擇“柱型圖”一“簇狀柱型圖”一“下一步”一“數(shù)據(jù)區(qū)域”一選擇或輸入："=data!$J$5:$J$14"“系列產(chǎn)生在”一列，在“系列”選項(xiàng)卡中“分類（X）軸標(biāo)志”一選擇或輸入：”=data!$K$5:$K$14”—“下一步”在圖表選項(xiàng)中選擇“標(biāo)題”一“圖表標(biāo)題”輸入“成績分布圖”，“分類（X）軸”輸入“分?jǐn)?shù)”，“數(shù)值（Y）軸”輸入“人數(shù)”一“下一步”在圖表位置中選擇“作為其中對(duì)象插入”中的“報(bào)表”，單擊“完成”即得到“成績分布圖”，將“系列1”圖例清除，對(duì)圖表作適當(dāng)調(diào)整并拖放到15行上。2.7設(shè)置數(shù)據(jù)有效性和保護(hù)工作表填寫時(shí)應(yīng)給予指導(dǎo)說明?？梢杂脭?shù)據(jù)有效性來設(shè)置指導(dǎo)說明。在“報(bào)表”工作表中選中合并的B16單元格，單擊“數(shù)據(jù)”菜單，選擇“有效性…”命令，打開數(shù)“據(jù)有效性”對(duì)話框，選擇“輸入信息”選項(xiàng)卡，在“標(biāo)題”中輸入：“填寫說明換行請(qǐng)用Alt+Enter”。在“輸入信息”中輸入必要的填寫說明。在data工作中選中B5單元格，單擊“數(shù)據(jù)”一“有效性…”一“設(shè)置”選項(xiàng)卡一“允許”中選擇“小數(shù)”，“數(shù)據(jù)”中選擇“介于”，“最小值”中輸入“0”，“最大值”中輸入："=B$3”；“輸入信息”選項(xiàng)卡一“標(biāo)題”中輸入“得分”，“輸入信息”中輸入圖4中的信息；“出錯(cuò)警告”選項(xiàng)卡一“樣式”中選擇“停止”，“標(biāo)題”中輸入“錯(cuò)誤”，“錯(cuò)誤信息”中輸入“分值必須在0與此題滿分之間！”。這樣如果輸入分值超出了本題滿分則會(huì)出現(xiàn)“錯(cuò)誤”消息框，避免人為錯(cuò)誤。選擇B5單元格，將B5:G112區(qū)域完全填充，完成數(shù)據(jù)有效性設(shè)置。對(duì)工作表進(jìn)行保護(hù)之前須對(duì)將要輸入數(shù)據(jù)的單元格的鎖定取消。三、功能和使用：3.1功能與特點(diǎn)被試越多，分析結(jié)果越準(zhǔn)確，當(dāng)被試