用Excel进行试题质量分析

用Excel进行试题质量分析考试成绩能否反映考生水平，试题质量是关键。通过试题质量分析，总结经验,指导老师对教学、命题进行改进，不断提高教学质量与命题质量是一项有实际意义的工作。但是现在没有这方面的软件可用，老师一般都是手工计算加办公软件辅助，工作繁杂、容易出错、效率低下且不直观，分析不够准确等，本文介绍一种实用的解决这个问题的方法。一、目的Microsoft®OfficeExcel现在是使用最为遍及的办公软件，于是就做了一个Excel试题质量分析系统提供给广大老师使用，老师们也可根据文中所述方法自己动手制作一个更符合自己需要的试题质量分析系统。下载地址：/files/872cdd82-b8a1-11de-874c-0014221b798a/1.1要求对试题质量分析中的标准差、难度指数及区别指数的计算方法标准差：设有一组共n个数据七，x尸，七，x是它们的平均值。则：S=「—[(x—x)2+(x—x)2+,,,(x—x)2]\n12nS为这个数据的标准差。标准差是用来衡量一组数据波动程度的一个重要指标。若用气，x2,x分别表示一个班级全部n个学生在一门课程考试中的成绩，x表示他们的平均成绩，则由上式计算得到的标准差反应这个班级n个学生在此门课程考试中成绩的波动情况。标准差从一个侧面反应考试试卷的水平。难度(P)——即考卷题目的难易程度区分度(r)——即考卷区分学生优劣的程度，区别指数好的试卷测定结果是好的学生分高，差的学生得分低。区别指数低的试卷测定结果是好的学生得分差距不大。区别指数适当的测定结果，能把学生好坏的差距拉开。具体计算方法是将全班学生每类考题的分数按高低顺序排列，然后两端各取总人数的25%作为高低两个分数组(极端分组法)。难度：P=E+LX100%N区分度：,=空WN式中: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

图1：二、制作过程2.1基本数据创建一个空白的工作簿，将二、制作过程2.1基本数据创建一个空白的工作簿，将“sheetl”命名为“data”用于输入基本数据如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计算学生成绩分布的频数图2在data工作表中增加图2内容，选中J5:J14区域输入公式："=FREQUENCY(H5:H65536,I5:I14)"按F2键，再按Ctrl+Shift+Enter组合键以数组公式的形式输入公式。图22.4制作报表将工作表sheet2改名为报表，按照图3制作出两页报表。ABCDEFGH1琪西南民族职业技术学院说卷湖拓昔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”并设置为百分比格式，填充至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: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},{'不宜采用","勉强可用，但必须修改","良好试题","极好试题"}))，选择C11:C14填充全H11:H14。至此所有公式全部输入完毕，已能够实现自动计算和分析功能。2.6生成学生成绩分布图表打开工作表“报表”，单击“图表向导”按钮，打开“图表向导”对话框，在“图表类型”中选择“柱型图”一“簇状柱型图”一“下一步”一“数据区域”一选择或输入："=data!$J$5:$J$14"“系列产生在”一列，在“系列”选项卡中“分类（X）轴标志”一选择或输入：”=data!$K$5:$K$14”—“下一步”在图表选项中选择“标题”一“图表标题”输入“成绩分布图”，“分类（X）轴”输入“分数”，“数值（Y）轴”输入“人数”一“下一步”在图表位置中选择“作为其中对象插入”中的“报表”，单击“完成”即得到“成绩分布图”，将“系列1”图例清除，对图表作适当调整并拖放到15行上。2.7设置数据有效性和保护工作表填写时应给予指导说明。可以用数据有效性来设置指导说明。在“报表”工作表中选中合并的B16单元格，单击“数据”菜单，选择“有效性…”命令，打开数“据有效性”对话框，选择“输入信息”选项卡，在“标题”中输入：“填写说明换行请用Alt+Enter”。在“输入信息”中输入必要的填写说明。在data工作中选中B5单元格，单击“数据”一“有效性…”一“设置”选项卡一“允许”中选择“小数”，“数据”中选择“介于”，“最小值”中输入“0”，“最大值”中输入："=B$3”；“输入信息”选项卡一“标题”中输入“得分”，“输入信息”中输入图4中的信息；“出错警告”选项卡一“样式”中选择“停止”，“标题”中输入“错误”，“错误信息”中输入“分值必须在0与此题满分之间！”。这样如果输入分值超出了本题满分则会出现“错误”消息框，避免人为错误。选择B5单元格，将B5:G112区域完全填充，完成数据有效性设置。对工作表进行保护之前须对将要输入数据的单元格的锁定取消。三、功能和使用：3.1功能与特点被试越多，分析结果越准确，当被试