首页 -> 2007年第10期
基于Ｅｘｃｅｌ２００３ＸＰ环境下考试成绩分析模型的建立
作者：霍 红
考试是教学效果和学习能力最有效的检验手段,考试成绩分析是教学过程中的一项重要内容,是教学评估材料中重要支撑材料,通过分析可以了解教师的命题水平､授课质量和教学效果,了解学生对所学内容的掌握情况,因此,考试成绩分析既是教学评估的需要,又是教学信息研究的需要｡
随着计算机的发展和普及,利用计算机强大的计算能力来辅助教师进行试卷分析已经成为必然趋势,许多学校基于不同的环境开发了试卷分析系统｡但据自己的调查和多年的实际教学经验,大部分试卷分析系统只是应用于各学校教务管理部门,而大多数教师,在每学期末还是处于烦琐的手工考试成绩分析中,特别是从事基础教育的教师,学生数量大,工作任务尤其繁重｡本文从实际需要出发,介绍在Excel2003/XP环境下,建立考试成绩分析的模型,实现考试成绩的自动统计和分析｡
二､面向试卷本身的数据指标
1.创建成绩模板
(1)建立成绩统计表
创建一个Excel工作簿｡将工作表Sheet1命名为班级名称｡在各工作表中根据实际需要输入表头;或者选中A1单元格,在单元格中输入:“=YEAR(TODAY())&“年度学生成绩统计表””,选中A1:I1单元格,设置为“跨列居中”或“合并及居中”｡之后分别输入学生姓名(可从教务数据库中导入无须授课教师输入)和各阶段的考试成绩(读卡成绩可直接导入)(见图1)｡
(2)用红色标出不及格成绩
选中百分制的成绩的区域,执行菜单“格式”→“条件格式”命令,在弹出的对话框中选择和填写所需要的值(条件选项选择“小于”,值中填写“60”),单击“格式”按钮,在“单元格格式”对话框中的“字体”标签中,设置字体为红色并加粗体显示｡这样当输入的成绩小于60分时,就会自动显示为红色粗体字｡
(3)设置成绩录入的有效范围
选定所有的成绩区域,执行菜单“数据”→“有效性”命令,在弹出窗口的“设置”标签中,单击“允许”下拉列表框,选择“整数或小数”选项,数据项选择“介于”,并输入最小值为“0”,最大值为“100”｡再切换到“出错警告”标签,设置“无效数据,请输入0～100之间的数”的提示信息｡这样当成绩录入错误时,会发出警告,保证了数据的正确性｡
2.综合数据统计
(1)成绩统计
期末成绩一般不是单一的期末考试的卷面成绩,而是各阶段成绩的整合｡以某学校的数学期末成绩为例,期末总成绩是由平时成绩和期末考试成绩组成｡其中:
平时成绩占30%,包括平时测验占10%､期中考试占12%､平时作业占8%;
期末考试占70%,包括读卡成绩(客观题)为60分､卷面成绩(主观题)为40分｡
计算期末成绩:在工作表中,选中J4单元格,在公式栏输入公式“=H4+I4”(H4､I4为读卡成绩和卷面成绩),其结果即显示在J4单元格中;再次选中J4单元格,按住填充柄向下拖拽至J8单元格,则在期末考试栏中显示所有学生的期末成绩｡
计算学期总成绩:在工作表中,选中K4单元格,在公式栏输入公式“=(C4+D4)*$C$2/2+(E4+F4)*$E$2/2+G4*$G$2+J4*$I$2”,($为绝对单元格引用),其结果显示在K4单元格中;再次选中K4单元格,按住填充柄向下拖拽至K8单元格,则在最终成绩栏中显示所有学生的期末总成绩｡
(2)分段统计
分段统计是试卷分析工作中最常用的也是最形象直接的指标,通过分段统计,可以清楚地知道考生在考试中的答题情况,各分数段的人数和百分比,也可以从中方便地推出该次考试的及格率､不及格率和优秀率｡
计算各分数段的人数:以计算80～89分数段人数为例,选中C23单元格,在公式栏输入公式“=COUNTIF(C4:C8,“<90”)-COUNTIF(C4:C8,“<80”)”(C4:C8为成绩区域),其统计结果显示在C23单元格中;再次选中C23单元格,按住填充柄向右拖拽至K23单元格,则所有考生的各阶段考试情况被统计出｡
计算各分数段的百分比:以计算80～89分数段的百分比为例,选中C24单元格,在公式栏输入公式“=C23/C9”(C9为实考人数),显示统计结果后,向右拖拽填充柄,即获得此分数段各阶段考试的百分比｡
计算及格率:选中C12单元格,在公式栏输入公式“=(C9-C15)/C9”(C15为不及格人数),显示统计结果后,向右拖拽填充柄,即获得各阶段考试的及格率｡
计算优秀率(>=90):选中C13单元格,在公式栏输入公式“=(C25+C27)/C9”(C25､C27分别90～99和100分的人数),显示统计结果后,向右拖拽填充柄,即获得各阶段考试的优秀率｡
(3)最高分､最低分､平均分统计
最高分､最低分､平均分三个指标统计,可体现考试的普遍性和特殊性,通过最高分､最低分这两个极端指标可以知道试卷出题是否超过了哪个极端,或者帮助教师发现该班中存在的个别学生,因材施教｡平均分体现了该班级学生在考试中的平均答题水平,一定程度上代表了该班级考生对该试卷学科知识的掌握程度｡
统计最高分:选中C10单元格,在公式栏输入公式“=MAX(C4:C8)”,即可在C10单元格显示最高分｡再次选中C10单元格,按住填充柄向右拖拽至K10单元格,则在最高分栏内显示各阶段考试最高成绩｡
统计最低分:选中C11单元格,在公式栏输入公式“=MIN(C4:C8)”,即可在C11单元格显示最低分｡再次选中C11单元格,按住填充柄向右拖拽至K11单元格,则在最低分栏内显示各阶段考试最低成绩｡
统计平均分统计:选中C14单元格,在公式栏输入公式“=SUM(C4:C8)/C9”,即可在C14单元格计算出平均成绩｡再次选中C14单元格,按住填充柄向右拖拽至K14单元格,则在平均分栏内显示各阶段考试的平均成绩｡
(4)综合成绩统计
以上是对各班的分数统计,为了掌握各年级的学生学习及考试情况,可将全年级所有学生的成绩进行统一管理｡在工作簿中插入新的工作表,并以年级名称命名｡在此工作表中,数据来源于各班工作表中的数据｡
计算某课程的年级实际参考人数,在公式栏输入“=COUNT(一班!I4:I8,二班!K4:K8,三班!I5:I10)”,数据来源于各班工作表｡
年级最高分统计,在公式栏输入“=MAX(一班!K4:K8,二班!K4:K8,三班!I5:I10)”｡
年级最低分统计,在公式栏输入“=MIN(一班!K4:K8,二班!C4:C8,三班!C5:C10)”｡
课程优秀率的统计,在公式栏输入“=(C21+C23)/C5”,数据可来源于本工作表｡
年级平均分统计,在公式栏输入“=SUM(一班!K4:K8,二班!C4:C7,三班!C5:C8)/C5”｡
三､数据指标分析
通过以上操作,完成了对学生成绩的统计和管理｡除此之外,对试卷和成绩的分析也是至关重要的｡
1.定制分析图
图示具有直观效果,利用Excel的图表功能可以显示全年级和各班学生各分数段的成绩分布情况,通过图示可以使授课教师掌握不同学生对各阶段知识的掌握程度,并根据教学科目特点,学生接受能力,分析教学的侧重点,重新组织教学｡
2.成绩的筛选分析
期末考试之后,需要统计分析各班成绩优秀､不及格和缺考同学的人数和名单｡利用Excel的高级筛选功能,把各班不及格同学的名单筛选出来并集中合并到一张工作表中,从而确定学生补考名单;统计筛选各班缺考同学的名单,并与Word中的“邮件合并”功能相结合,可自动生成补考通知单,发放给学生;对于成绩优秀的同学进行筛选并统一管理,适当的时候给予重点培养,可为各类竞赛作人员储备｡
灵活运用Excel的高级筛选功能关键在于正确的建立条件范围,巧妙利用多重条件的组合,首先要明确条件区域中各条件之间的“与”､“或”关系,即在条件区域“同一行”中出现的条件,相互关系为“与”(见图2);在“不同行”中出现的条件,相互关系为“或”(见图3)｡
首先在工作表中的条件区域输入条件范围,执行“数据”→“筛选”→“高级筛选”命令,在高级筛选对话框中,“方式”选择为“将筛选结果复制到其他位置”,并选择“列表区域”为B3:K8､“条件区域”为A30:D31或A36:D40､“复制到”区域为A32单元格或A41单元格或新工作表中的某个区域,确定后可得到筛选结果(见图2､3)｡
四､考试质量指标分析
考试质量的高低直接关系到能否真实地反映学生学习成绩的真实水平,而试卷中试题的难易程度又影响考试的质量｡通过以上数据的统计分析,很容易得出试卷的标准差及是否符合正态分布的分析｡
1.分布形态判断
正态分布又称常态分布,是数理统计中应用广泛又相当重要的概率分布,多数随机现象都服从或近似
就课程考试成绩来说,符合学生实际水平的考试中,成绩居中等水平(70～85分)的学生占多数,而成绩特别好(90～100分)和特别差(0～59分)为少数,即考试成绩应该服从正态分布｡因此,考试试卷的质量高低,是否适合考生的实际情况,首先应该判断考试成绩是否服从或近似服从正态分布,然后再判断其他指标是否符合标准｡
2.标准差分析
标准差描述了考试成绩的离散程度,是最常用的一种量化形式｡离散度反映的是准确的测量结果,是评价好坏最重要也是最基本的指标｡就考试成绩而言,标准差大,说明学生离散程度高,分数参差不齐,不是试卷出得不好就是教学有问题;而标准差小,说明学生分数很接近,不是教学效果好就是试卷偏易｡
五､结束语
考试成绩分析与试卷质量评估是教学过程中的一个重要环节｡全面分析考试成绩,准确评价考生的学习能力,客观评估试卷质量,反馈教学过程中的成功与不足,对于提高教学质量,深化教学改革,有着重要的意义｡此模型可满足各类学校教师的需要,也可大大提高考试成绩分析工作的效率和准确率,同时也减轻了老师工作强度｡这一方法希望在广大教师中得以推广｡
(责任编辑 关燕云)