基于Excel 的试卷质量深度分析平台的实现

张 芸 武 斌 杨 阳

(滁州职业技术学院信息工程系，安徽 滁州 239000)

在学校的日常教学中，考试占据重要的地位,通过考试可以检查课程目标的实施情况、学生接受知识的程度、教师的教学效果，用以判定课程设计的效果，并据此做出改进教学的决策[1]。课程考试后会出现大量的数据，目前大多数的考试分析仅是简单求出平均分和各个分数段区间，而这些是不够的，不能全方位的反映出考试的情况。除了计算出一些普通的数据指标外，还应该对教师出制的试卷进行分析，才能得出综合评价。试卷分析是指根据教育测量学和数理统计的原理对课程的测试成绩进行定量研究，对考试从命题到成绩进行分析，再计算出试题的难易度、区分度、信度、效度等情况后，对考试结果做出综合评价[2]。对试卷进行综合评价后，可以获得许多重要信息，对评判教师的出题质量、判断学生的学习能力、正确评估教学效果、后期提高教学质量均具有重要的意义[3]。

试卷分析中的各个指标如果是用人工方法计算的话会非常繁琐，而一些专业的统计分析软件如SPSS 软件操作起来又很复杂，没有学习过该软件的人很难熟练掌握。而微软公司的Excel 软件是用户都非常熟悉的常用办公软件，用Excel 创建包含计算分数段人数、难易度、区分度、方差、信度、效度等功能的试卷质量深度分析平台，具有一定的实用价值和研究意义。

一、平台的功能需求分析

根据课程评估的目的，基于Excel 的试卷质量深度分析平台应具备以下功能：

收集试卷分数：在“试卷”工作簿中创建“试卷分数”工作表，录入班级每个学生各种题型的分数和满分。

计算出各个指标：在“试卷”工作簿中创建“试卷深度分析”工作表，对考试结果进行统计分析，求出各个分数段人数和试题及试卷的难度、区分度、方差、信度、效度。难度P 是指试题或试卷的难易程度，系数过高或过低，都会降低考试的可靠性；区分度D 是指试题或试卷对学生水平的区分程度，区分度高的试题能使能力高的学生得高分，能力低的学生得低分，区分度低的试题却使所有学生的得分相差不大；信度B 用以衡量试题的可靠性与稳定性，系数高说明考生分数可以比较真实地反映考生的实际水平，系数低则说明考生分数容易受偶然因素造成的误差的影响；效度R 是指试题能否有效地实现考试的目标，用以衡量试题的准确性和有效性[4]。

深度分析试卷：根据计算出的难度、区分度、信度和效度的高低，判断试卷质量、学生的掌握情况和教师的教学效果。

二、平台的设计与实现

具备上述功能的试卷质量深度分析平台是利用Excel 软件的公式与函数[5]对考试分数等数据进行统计分析实现的。

1.收集试卷分数

创建“试卷质量深度分析平台”工作簿，并在工作簿中创建“试卷分数”工作表，在其中输入“姓名”、“单项选择题”、“多项选择题”、“填空题”、“判断题”、“问答题”、“总分”等字段名称，然后将班级学生的考试分数录入到表格中，以50 人为例，如图1 所示。

图1 “试卷分数”工作表

再创建“试卷深度分析”工作表, 在其中添加好表头及需要分析的各字段，再输入表头信息及“各题型总分”，如图2 所示。

图2 “试卷深度分析”工作表

2.计算各个分数段人数和所占百分比

分析考试成绩时，首先应该统计出各分数段的人数，通过这些不同分数段区间的人数可以知道考试成绩的总体分布。如果利用人工去数个数的话既麻烦费时又容易出错，利用Excel 中的FREQUENCY 函数可以快速地统计出各分数段的人数。

如在“试卷深度分析”工作表中要统计出100 分-90分、89 分 -80 分、79 分 -60 分、59 分 -40 分、40 分 以 下的分数段的人数，则在相应的“区间分割点”中填入100、89、79、59、40，在C5中输入“=FREQUENCY(试卷分数!$G$2：$G$51,C4：G4)”即可计算出 100-90 分区间的人数，在C6 中输入“=C5/$H$2”计算出该区间人数所占百分比。向右拖动C5 和C6 的填充柄即可求出其它分数段的人数和所占百分比。

3.计算难度

难度分试题的难度P_i 和试卷的难度P。

在表中先用AVERAGE 函数计算出“班级平均分”，再在C10 内输入“=1-C9/C8”，然后拖动C10 的填充柄至H10计算出其它的难度。

当系数小于等于0.2 时说明试卷难度太低；当系数大于0.2 且小于等于0.4 时说明试卷难度适中；当系数大于0.4 且小于0.8 时说明试卷较难；当系数大于等于0.8 时说明试卷难度太大。

4.计算区分度

区分度主要是通过控制试题的难度来提高的，所有学生都答不出来的太难的试题是没有区分度的，同样，所有学生都能答出来的太容易的试题也没有区分度，只有适当难度的试题才会有很好的区分度。区分度包括各试题的区分度和试卷的区分度。

第i 题的区分度的计算方法为：先把学生该题的分数进行降序排序，取前百分之27 的学生成绩作为高分组成绩，取后百分之27 的学生成绩作为低分组成绩，分别计算高分数组学生的平均分和低分数组学生的平均分，则Ti为第i 题的满分。

同理，试卷的区分度的计算方法为：先把学生总分数进行降序排序，取前百分之27 的学生成绩作为高分组成绩，取后百分之27 的学生成绩作为低分组成绩，分别计算高分数组学生的平均分和低分数组学生的平均分，则为试卷的满分。

以单项选择题的区分度计算为例，步骤如下：在“试卷分数”工作表中，将单项选择题列的数据复制到一空列J 列中，对此列数据进行降序排序，然后回到“试卷深度分析”工作表，在C11 中输入“=AVERAGE(试卷分数!J2：J15)”计算前14 个分数的平均分，在C12 中输入“=AVERAGE(试卷分数!J38：J51)”计算后14 个分数的平均分，在C13 中输入“=(C11-C12)/C8”，即求出单项选择题的区分度。

再将“试卷分数”工作表中的“多项选择题”、“填空题”、“判断题”、“问答题”、“总分”数据复制到K、L、M、N、O 列，并分别对它们进行单列降序排序。在“试卷深度分析”工作表中向右拖动C11、C12、C13 的填充柄即可把其它的“高分组平均分”、“低分组平均分”和“区分度”计算出来。

系数小于0.2 的试卷区分度为较差；系数大于等于0.2且小于0.3 的试卷区分度为适中； 系数大于等于0.3 且小于等于0.4 的试卷区分度为较好；系数大于0.4 的试卷区分度为非常好。

5.计算信度

信度主要与试题的可靠性和评分的可靠性有关，取决于很多因素如试题的范围、数量、区分度、评分标准等。

Excel 中有专门用于计算方差的函数，因此可以很容易的计算出各个题型的方差值及试卷的方差值。在“试卷深度分析”工作表的C14 中输入“=VAR(试卷分数!B2：B51)”求出单项选择题的方差，向右拖动C14 的填充柄即可求出其它试题和试卷的方差。在H15 中输入“=(5/4)*(1-(C14+D14+E14+F14+G14)/H14)”即求出试卷的信度。

系数小于0.5 的试卷信度较差；系数大于等于0.5 且小于等于0.8 的试卷信度较好；系数大于0.8 的试卷信度非常好。

6.计算效度

效度反映了考试目标实现的程度，取决于考试的目标是否明确和试题的设计是否能有效地体现考试目标。如果是要考核学生对基础知识的掌握，则一般应该用客观性试题来考核；如果是要考核学生应用知识进行推理判断的能力，则用非客观性试题来考核。

在“试卷深度分析”工作表的H16中输入“=(C13+D13+E13+F13+G13)/5”即可计算出试卷的效度。

效度小于0.2 的试卷未能有效地实现考试目标；效度大于等于0.2 且小于0.4 的试卷则是较有效地实现考试的目标；效度大于等于0.4 的试卷可以有效地实现考试的目标。一般来说，只有信度较高，效度才会较高，但反之则不然。

7.分析结果

根据前面计算出的难度、区分度、信度和效度，要在“试卷深度分析”工作表中生成试卷质量分析结果。在B17 中输入“=IF(H10<=0.2,"试卷难度过低",IF(AND(H10>0.2,H10<=0.4),"试卷难易适中",IF(AND(H10>0.4,H10<0.8),"试卷比较难","试卷难度过高")”，会得到难度的分析结果；在B18 中输入“=IF(H13<0.2,"试卷区分度较差",IF(AND(H13>=0.2,H13<0.3),"试卷区分度适中",IF(AND(H13>=0.3,H13<=0.4),"试卷区分度良好","试卷区分度优秀")))”，会得到区分度的分析结果；在B19中输入“=IF(H15<0.5,"试卷信度较差",IF(AND(H15>=0.5,H15<=0.8)," 试卷信度良好"," 试卷信度非常好"))”，会得到信度的分析结果；在B20 中输入“=IF(H16<0.2,"试卷效度差，未能有效地实现考试目标",IF(AND(H16>=0.2,H16<0.4),"试卷效度较好，可以较有效地实现考试的目标","试卷效度好，可以有效地实现考试的目标"))”，会得到效度的分析结果。最终得到的考试质量深度分析表如图3 所示。

图3 考试质量深度分析结果

三、结束语

本文应用教育测量学和数理统计的相关知识对试卷的考试结果进行分析，并利用Excel 软件强大的公式与函数功能将复杂的分析计算过程实现出来，设计出了试卷质量深度分析平台。任意一门考试之后，只要建立好分数工作表，即可方便地得到分数段人数、难度、区分度、信度、效度等各种指标，极大地提高了教师的工作效率。教师根据这些分析结果可以知晓试题的质量、学生的学习能力和平时教学的效果，为今后和考试试题的出制教学质量的提高积累经验。