巧用Excel分析评价英语试卷

张维维

摘要：Excel 是大家熟悉的办公软件，利用Excel 可以轻松完成复杂的试卷质量分析与评价。从而使英语教师对整个教学过程有一个全面、系统、科学的认识，为强化教学监控与提高教学质量提供准确化、科学化的量化依据。

关键词：Excel； 英语试卷； 测试； 统计分析

目前，90%的初中每周会有一次单元测验，再加上月考，期中考，期末考，教师仅凭手工进行数据资料的整理和计算，工作繁重枯燥，只能提供考生的考试分数、平均分和及格率等基本考试情况。

利用Excel 可以轻松完成复杂的试卷质量分析与评价。

在教育统计学中，对统计资料的处理分为三个步骤进行：第一步是全面而准确地收集客观数据资料；第二步是整理数据资料；第三步是统计资料的计算和分析。

一、数据资料的计算和分析

数据资料的收集和整理以后，为了更客观地解释考生分数，更好地了解教学班的考试情况，一般通过如下几个统计参数进行计算和分析，在此举例说明。

下例对某班一次“英语单元测试”成绩进行统计分析，将学生成绩填入Excel的单元格区域“B2:B15”中，如表1所示。

1． 班级人数。在Excel表中可用COUNT函数计算，例如：在单元格E3中填入公式=COUNT(B2：B15)。如果有学生缺考，应该用COUNTA函数对非数值型数据进行统计,例如:在单元格D3填入公式= COUNTA(A2：A15)。

2． 班级最高分。可用MAX函数计算，例如：在单元格F3中填入=MAX(B2：B15)。

3． 班级最低分。可用MIN函数计算，例如：在单元格中G3填人=MIN(B2：B15)。

4． 班级平均分。可用AVERAGE函数来计算，例如：在单元格H3中填入公式：AVERAGE(B2:B15)。

5． 及格率。通常将60分设定为及格线。在Excel中可用COUNTIF函数来计算，例如：在单元格J3中填入=COUNTIF(B2:B15，“>=60”)／COUNTA(A2：A15)。

6． 优秀率。初中英语教学通常将95分以上设定为优秀分。还是用COUNTIF函数来计算，例如：在单元格D9中填入=COUNTIF(B2：B15，“>=95”)／COUNTA(A2：A15)。

7．分数段。将考生成绩按照高低分进行分数段的统计，可以大致了解考试出题难度组合状况、考生成绩平衡性等。在Excel中可用COUNTIF函数来统计。例如：“80—89”分数段可用公式=COUNTIF(B2：B15，“>=80”)—COUNTIF(B2：B15，“>=90”)，“70—79”分数段可用=COUNTIF(B2：B15．“>=70”)—COUNTIF(B2：B25，“>=80”)，“60-69”分数段可用=COUNTIF(B2：B15，“>=60”)—COUNTIF(B2：B15，“>=70”)，不及格人数可用=COUNTIF(B2：B15，“<60”)进行统计，其余类似。

8． 平均差。该参数表示全班学生成绩与全班平均分的绝对偏差的平均值，用于测评全部学生成绩与平均分的差异情况。在Excel中可用AVEDEV函数来计算。例如：在单元格E9中填入公式=AVEDEV(B2:B15)。

9． 标准差。这是反映考生考试成绩波动情况和判断、区分考生成绩的最重要的统计量。理论上认为标准差大，区分效果好，标准差小，成绩分布则比较集中，通常要求将该值控制在“8—10”之间。在Excel中可用STDEV函数，例如：在单元格F9中填入公式=STDEV(B2:B15)。

10． 标准分。这是以标准差为单位，表示某一分数与平均数的差。标准分数在教育工作中的用途很多，它可以用来比较各个学生的成绩在班级成绩中的地位，也可以用来比较某个学生在两种或多种测验中所得分数的优劣。计算机可以方便的将原始分转换成标准分，使课程之间和班级之间具有可比性，找到各班、年级在校内的位置，从而对全校教学情况一目了然。例如：计算某个学生(陈一)的标准分，在单元格中填入公式=(B2—AVERAGE(B2：B15))／STDEV(B2：B15)。

二、考卷的分析和评估

直方图工具：以某校八年级某班期末考试成绩为例，将考试成绩输入Excel表格C1:C41。该测验最高分为100，最低分为78，先确定一个区间[77.1, 102.1] (组限通常取比数据的精度高一位，即多一位小数点或个位数，以免数据刚好落在端点上)。将区间[77.1, 102.1]等分为5个小区间，即组距△=（102.1-71.1）/ 5=5。这样各小区间的组限端点从左至右依次为：77.1，82.1，87.1，92.1，97.1和102.1

打开EXCEL文档，点开“工具”——“数据分析”——“直方图”，在“输入区域”对话窗输入C1:C41（即学生成绩所在栏）,在“接受区域”输入组限端点所在地单元格区域，选中“标志位在第一行”。即可得到直方图：

根据直方图，我们可以大致看出某项目（如某次测试的成绩）的分布图形，从而判定该项目所蕴涵的意义。分布图形一共分六种：

正态分布 说明测试结果与学生的实际情况一致，各种难度的项目比例合理。

正偏态分布 说明试题难度偏高，难度较大的项目比例偏大。呈这种分布的试题有利于将

成绩优秀的学生和中等程度的学生区别开，但不利于将中等程度的学生和成绩较差的学生区别开。

负偏态分布 说明试题难度偏低，难度较低的项目比例偏大。呈这种分布的试题有利于将成绩较差的学生和中等程度的学生区别开，但不利于将中等程度的学生和成绩优秀的学生区别开。

尖峰态/陡峭型分布 说明试题中同等难度的项目较多，梯度偏小。呈这种分布的试题几乎不能将不同程度的学生区分开，分数分布过于集中。

低峰态/平坡型分布 说明试题中各种难度的项目比例接近，梯度较大。呈这种分布的试题区分度较高，但分数之间的差异偏大。

双峰型分布 说明试题存在两极分化现象，即难度偏高的和难度偏低的项目较多，而中等难度的项目偏少，项目难度的分布缺乏梯度，不够合理。呈这种分布的试题可以区别中等程度的学生，但不利于区别出成绩优秀的学生和成绩较差的学生。

于是我们可以得到结论：

1. 根据该班级测试的直方图，可以判定该正态分布为负偏态分布。有利于将成绩较差的学生和中等程度的学生区分开，但不利于将中等程度的学生和优秀学生区分开。

2. 该正态分布为尖峰态分布。不能将不同程度的学生区分开，分数过于集中。

参考文献：

1. 黄光扬.《教育测量与评价》华东师范大学出版社，2002

2. 侯晓霞，王子勇.《试卷质量分析与评价系统地设计》计算机工程,2000,26(11):151-153

3. 李翠，李进才《利用Excel对试卷质量进行系统分析》集宁师专学报，2001，23（4）：26-28

4. 宣仲良《Excel实现的试卷分析质量表》江苏大学学报（高校研究版）2004,26(3):88-92