用Excel进行试题质量分析
考试成绩能否反映考生水平,试题质量是关键。通过试题质量分析,总结经验,指导老师对教学、命题进行改进，不断提高教学质量与命题质量是一项有实际意义的工作。但是现在没有这方面的软件可用，老师一般都是手工计算加办公软件辅助，工作繁杂、容易出错、效率低下且不直观，分析不够准确等，本文介绍一种实用的解决这个问题的方法。
一、目的
Microsoft Office Excel现在是使用最为遍及的办公软件，于是就做了一个Excel试题质量分析系统提供给广大老师使用，老师们也可根据文中所述方法自己动手制作一个更符合自己需要的试题质量分析系统。
下载地址：http://www.rayfile.com/files/872cdd82-b8a1-11de-874c-0014221b798a/
1.1要求
对试题质量分析中的标准差、难度指数及区别指数的计算方法
标准差：设有一组共n个数据x1,x2,,xn，是它们的平均值。则：
S S为这个数据的标准差。标准差是用来衡量一组数据波动程度的一个重要指标。若用x1，x2，xn分别表示一个班级全部n个学生在一门课程考试中的成绩，x表
示他们的平均成绩，则由上式计算得到的标准差反应这个班级n个学生在此门课程考试中成绩的波动情况。标准差从一个侧面反应考试试卷的水平。
难度（P）——即考卷题目的难易程度
区分度（r）——即考卷区分学生优劣的程度，区别指数好的试卷测定结果是好的学生分高，差的学生得分低。区别指数低的试卷测定结果是好的学生得分差距不大。区别指数适当的测定结果，能把学生好坏的差距拉开。
具体计算方法是将全班学生每类考题的分数按高低顺序排列，然后两端各取总人数的25%作为高低两个分数组(极端分组法)。
难度：PEL
N100%
区分度：r2(EL)
N
式中:E——高分组学生该题得分总和
L——低分组学生该题得分总和
N——高低二组总人数该题满分和
二、制作过程
2.1基本数据
创建一个空白的工作簿，将“sheet1”命名为“data”用于输入基本数据如图1：
图1
H4单元格中输入公式
2.2取样
这是最为重要和关键的一步，在I86单元格中输入公式：=IF(COUNT(B$5:B$65536)
低分取样与高分取样雷同在O86单元格输入公式
=IF(COUNT(B$5:B$65536)
2.3计算学生成绩分布的频数
在data工作表中增加图2内容，选中J5:J14区域输入公式：
图2
2.4制作报表
将工作表sheet2改名为报表，按照图3制作出两页报表。
图3
2.5编辑公式
D3中输入公式：=data!D2&
D4中输入公式：=data!B2&
G4中输入公式：=data!F2&
H5中输入公式：=COUNT(data!H:H)-1
D7中输入公式：=data!J14
E7中输入公式：=data!J13
F7中输入公式：=data!J12
G7中输入公式：=data!J11
H7中输入公式：=SUM(data!J5:J10)
D8中输入公式：
G9中输入公式：=IF(COUNT(data!H5:H65536)
=IF(COUNT(data!I86:I65536)=0,
6))/((COUNT(data!I86:I65536)+COUNT(data!O86:O65536))*data!B4))
C12中输入公式：=IF(C11=
C13中输入公式：
=IF(COUNT(data!I86:I65536)=0,
C14中输入公式：=IF(C13=
2.6生成学生成绩分布图表
打开工作表“报表”，单击“图表向导”按钮，打开“图表向导”对话框，
在“图表类型”中选择“柱型图”→“簇状柱型图”→“下一步”→“数据区域”→选择或输入：
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. 请勿移动可以操作的单元格避免发生不必要的错误。