用Excel进行试题质量分析.docx

用 Excel 进行试题质量分析考试成绩能否反映考生水平,试题质量是关键通过试题质量分析,总结经验, 指导老师对教学、命题进行改进，不断提高教学质量与命题质量是一项有实际意 义的工作但是现在没有这方面的软件可用，老师一般都是手工计算加办公软件 辅助，工作繁杂、容易出错、效率低下且不直观，分析不够准确等，本文介绍一 种实用的解决这个问题的方法一、目的Microsoft® Office Excel 现在是使用最为遍及的办公软件，于是就做了一个 Excel 试题质量分析系统提供给广大老师使用，老师们也可根据文中所述方法自 己动手制作一个更符合自己需要的试题质量分析系统下载地址： 1.1要求对试题质量分析中的标准差、难度指数及区别指数的计算方法标准差：设有一组共n个数据x , x , , x , x是它们的平均值贝U：1 2 n1 一 一 一S = [(x — x)2 + (x — x)2 + •••( x — x)2 ]n 1 2 nS 为这个数据的标准差标准差是用来衡量一组数据波动程度的一个重要指标若用x，x，x分别表示一个班级全部n个学生在一门课程考试中的成绩，x表 12n示他们的平均成绩，则由上式计算得到的标准差反应这个班级n个学生在此门课 程考试中成绩的波动情况。

标准差从一个侧面反应考试试卷的水平难度(P)——即考卷题目的难易程度区分度(r)——即考卷区分学生优劣的程度，区别指数好的试卷测定结果是好 的学生分高，差的学生得分低区别指数低的试卷测定结果是好的学生得分差距 不大区别指数适当的测定结果，能把学生好坏的差距拉开具体计算方法是将全班学生每类考题的分数按高低顺序排列，然后两端各 取总人数的 25%作为高低两个分数组(极端分组法)难度：P = X 100%N区分度：r = 2( E - L}N式中:E—高分组学生该题得分总和L—低分组学生该题得分总和N 高低二组总人数该题满分和1.2标准难度指数(P) 区别指数(r)<30%比较难>0.35极好试题30—70%可以接受0.25—0.34良好试题50—60%可靠0.15—0.24勉强可用，但必须修改>70%偏易<0.15不宜采用表1二、制作过程2.1 基本数据创建一个空白的工作簿，将“sheetl”命名为“data”用于输入基本数据如 图1：H4单元格中输入公式"二SUM(B5:G5))"如图，然后填充至H112单元格2.2 取样这是最为重要和关键的一步， 在 I86 单元格中输入公式：=IF(COUNT(B$5:B$65536)<4*(ROW()-85),"",LARGE(B$5:B$65536,ROW()-85))， 然后填充I86:N112区域，完成高分的取样。

低分取样与高分取样雷同在 O86 单元格输入公式=IF(COUNT(B$5:B$65536)<4*(ROW()-85),"",SMALL(B$5:B$65536,ROW()-85)) 填充整个O86:T112区域，完成低分取样2.3 计算学生成绩分布的频数在 data 工作表中增加图 2 内容， 选中 J5:J14 区域输入公式："二FREQUENCY(H5:H65536,I5:I14)"按 F2 键，再按 Ctrl+Shift+Enter 组合键以数 组公式的形式输入公式IJK123分段频数459. 990<10619. 990<20729. 990<30839. 995<40949. 994<501059. 9919<601169. 9926<701279. 9928<801389. 9911<90148<=100图22.4 制作报表将工作表sheet2改名为报表，按照图3制作出两页报表ABC D E F G H1爭西南民旗职业技术学覘试劉冲龍告25雌生艮:14部门:趣:5铐活三竝:11213成翁优秀旧0-10①匡好(SO-0S)中等(70-79)融〔00-69)不矗榕［上60】比境平均修Stir第N第2第四題堆fg-器1Y181920211300 年 1W2 三22'£5g汪(签宇):年乓三年W三图32.5 编辑公式D3中输入公式：=data!D2&""D4中输入公式：=data!B2&""G4 中输入公式： =data!F2&""H5 中输入公式：=COUNT(data!H:H)-lD7中输入公式：=data!J14E7 中输入公式： =data!Jl3F7 中输入公式： =data!J12G7 中输入公式： =data!J11H7 中输入公式： =SUM(data!J5:J10)D8中输入公式："二D7/$H$5"并设置为百分比格式，填充至H8单元格。

D9 中输入公式：=AVERAGE(data!H5:H65536)G9 中输入公式： =IF(COUNT(data!H5:H65536)<2,"",STDEV(data!H5:H65536))C11中设置格式为百分比并输入公式：=IF(COUNT(data!I86:I65536)=0,"",(SUM(data!I86:I65536)+SUM(data!O86:O6553 6))/((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},{"不宜采用"," 勉强可用，但必须修改"，“良好试题"，"极好试题"}))，选择C11:C14填充至 H11:H14。

至此所有公式全部输入完毕，已能够实现自动计算和分析功能2.6 生成学生成绩分布图表打开工作表“报表”，单击“图表向导”按钮，打开“图表向导”对话框， 在“图表类型”中选择“柱型图”-“簇状柱型图”-“下一步”-“数据区域” f选择或输入："二data!$J$5:$J$14"“系列产生在”一歹U，在“系列”选项卡中“分 类（X）轴标志”一选择或输入："二data!$K$5:$K$14"f “下一步”在图表选项 中选择“标题” 一 “图表标题”输入“成绩分布图”，“分类（X）轴”输入“分 数”，“数值（Y）轴”输入“人数” 一 “下一步”在图表位置中选择“作为其中 对象插入”中的“报表”，单击“完成”即得到“成绩分布图”，将“系列1”图 例清除，对图表作适当调整并拖放到15行上2.7 设置数据有效性和保护工作表 填写时应给予指导说明可以用数据有效性来设置指导说明在“报表”工作表中选中合并的B16单元格，单击“数据”菜单，选择“有 效性…”命令，打开数“据有效性”对话框，选择“输入信息”选项卡，在“标 题”中输入：“填写说明换行请用Alt+Enter”在“输入信息”中输入必要的填 写说明在data工作中选中B5单元格，单击“数据” 一 “有效性…” 一 “设置” 选项卡一“允许”中选择“小数”，“数据”中选择“介于”，“最小值”中输入“0”， “最大值”中输入：“=B$3”； “输入信息”选项卡一 “标题”中输入“得分”，“输 入信息”中输入图 4中的信息；“出错警告”选项卡一“样式”中选择“停止”， “标题”中输入“错误”，“错误信息”中输入“分值必须在0与此题满分之间！”。

这样如果输入分值超出了本题满分则会出现“错误”消息框，避免人为错误选择B5单元格，将B5:G112区域完全填充，完成数据有效性设置 对工作表进行保护之前须对将要输入数据的单元格的锁定取消三、功能和使用：3.1 功能与特点1. 被试越多，分析结果越准确，当被试人数超过 108 时可选中第 112行向 下填充；2. 此表只是提供了一个分析的方法所以只有六个题型分析，可根据需要增 加；3. 教师只需输入该题型的总分和被试该题的得分，其它均自动完成；4. 适用于各类学校教师做试卷质量分析3.2 使用时应注意：1. 为避免输入错误分数，输入时先输入每题总分，再来输入被试样本的得 分2. 请勿移动可以操作的单元格避免发生不必要的错误。