运用Excel进行中学考试分析.pdf

2 0 1 4 年1 月教育纵横运用E x c e I 进行中学考试分析湖北大学数计学院吴俊在中学教学中我们必须面对如何有效地、适当地对教学结果进行评价的问题只有通过对学生成绩进行正确，全面，科学的分析，我们才能对学生的学习质量做出符合实际地、有效地评价，进而了解学生的学习状况、知识能力结构，找出影响教学效果的深层因素，并反馈于教学活动，进一步提高教学水平本文以E x c e l 为工具，对某中学一月份文、理科月考四个班级的语、数、外成绩进行全面的教学质量分析，并尝试分析文、理科在学科上的差异，以及文理科的学生各科学习状况的分布情况一、试卷分析的相关概念对于每次考试，应该从试卷本身开始研究，一次考试的测试结果是否可信，是否有效?这是我们在测量研究中首先应该关注的问题只有测量的方法正确，测量的结果才会真实的反映学生的学习状况，我们才能够在此基础之上运用统计方法做出符合实际的分析，最后有效的反馈到我们的教学之中因此，对于试卷的研究在教学质量分析中意义重大一份试卷的评判标准主要有五个：正态性、信度，效度，难度，区分度1 正态性：由统计学理论，作为随机变量，大量的考试成绩结果的数值大小是服从正态分布的，在我们的教学评价中也主要是从试卷考试成绩结果的正态性检验开始。均值为M，方差为c r 2 的随机变量所服从的正态分布密1(，“)度函数是9(戈)=上e 一百，x R、2 订口其中，均值u 描述的是考试成绩分布整体的均值，方差口颂0 是描述考试成绩的变化相对于整体均值的偏移情况。正态分布具有以下性质：正态分布的密度曲线是对称的，对称轴是z=“；正态分布的分布集中程度受方差影响，当方差变大，分布分散，方差变小，分布集中理论上，检验一组数据服从正态分布的方法是卡方检验：假设一次考试的成绩总体f 服从均值为z，方差为s 2的正态分布，此假设可以表示为：日。：亭一(并，s 2)为了检验假设风，可以将考试成绩按1 0 分为间隔分组，设每个分组的频数为，概率为P i，根据皮尔逊定理，坛线统计量：殆型“，妒i近似服从自由度为r m l 的卡方分布，对于均值方差未知的分布，则m=z 在置信水平0 0 5 下，查r 分布表，得r(r 一3)，若Z 帮(卜3)，则接受巩，即认为成绩服从正态分布，否则拒绝凰，即认为成绩不服从正态分布在实际试卷分析中，我们可以直接用E x c e l 作出成绩频率分布折线图，通过正态分布密度函数曲线的性质直观上判断正态性，从而减少工作量2 信度：即测量的可信程度，度量的是试卷检测结果与实际考试成绩的差异s；为检测成绩的方差，s；为实际2被测者应有成绩的方差r 信=3 效度即测量结果的准确性和有效性的程度，受到舞弊，抄袭，经验型的结果影响造成的误差都会影响效度在信度的构成中我们可以将s 扮解为s；和爱，s；表示有效乎测试值的方差，5；!表示系统误差，效度的计算公式为r 效-：为了提高信度和效度可以通过扩大考试覆盖面，建立考察范围的层次结构，在大范围的知识点，多层次考查要求下的试卷信度和效度都是比较高的，这也是目前很多考试采用的办法此外，效度高的测验，其信度是比较高的在实际的考试分析中我们通常可以用下面两个公式近似的计算信度和效度：。r 信2 寺l1。吉喜s；l 克伦巴赫公式)1 r 效=二己n g 训其中g 为试题总数，s 为全体学生成绩的标准差，D 沩第i 题的区分度试卷的信度一般在0 9 0 9 5 之间为宜，效度在0 4 0 4 7 之间为宜 J 5 4 难度：即考试的难易程度，难度的计算公式为肚鲁五p 为难度系数，分子为平均分，分母为总分，难度大于o 8 5 为容易，小于o 3 为偏难5 区分度：是指考试试题对测试对象的心理特征的高中版中?毒i：?_ l 一万方数据圈教育纵横2 0 1 4 年1 月区分能力主要采用内部一致性法，即分析考生的得分与整个测试的一致性程度也就是计算得分与总分的相关系数由于选填题和解答题得分情况不同，计算选填题区分度公式为D=竺、丙J)其中，p 表示正确的考生比率，q 表示错误的考生比率，石为回答正确考生的总分均分，云为回答错误的考生的总分均分，S 为总分的样本标准差由于解答题的得分是“连续”的，故计算公式为r戈，p 半忪一半 其中戈是戈i 的简写，表示第i 个考生的得分，是竹的缩写，表示该试题总分，n 为考试人数以上的计算过程，就是求每个考生该题得分与其总分相关系数的过程，在具体操作中将所有考生某题得分与总分作为变量进行相关分析，得到的相关系数也就是该题的区分度在本例中，采用得分率求差法求区分度，首先将学生分为高分组和低分组，一般取总体人数或者分数极差(最大值减去最小值)的2 7 为界，计算高分组和低分组的平均分，以平均分除以总分得到各组得分率，最后将得分率的差视为区分度区分度大于0 3 为区分度好，小于0 1 5 为区分度差区分度D=掣二、试卷分析的E X C E L 实现(一)用E x c e l 作出单科成绩的频率分布折线图在正态分布的基础上，我们可以通过作出频率分布折线图观察考试成绩的分布情况，以理科数学成绩为例将数据录入E x c e l 中，作频率分布折线图如图1(具体操作略)图l 语、数、外成绩分布折线图(1)在i 条曲线中，语文成绩的频率分布的曲线最接近于正态分布的曲线，在这次的考试中英语成绩的分布比较正常，分数都分布在1 0 5 分左右，并在五分的波动范围内波动，整体的考试状况较好，但是这种完全近似于正零碧獭。1 中。7 毒：一?高中版态分布的曲线，在区分度上是不够的，在后面的部分我们会继续分析；(2)其中英语成绩的频率分布，峰度不高，并且在1 1 0分段的数据低于1 0 人，表现出异常的波动，曲线跨度较大，说明成绩的差距大，整条曲线没有较好的统计现象，成绩散乱的分布在8 0 分到1 2 0 分之间，英语成绩比较凌乱，在日常的教学中存在一定的问题；(3)最后是数学成绩的频率分布，这条线在7 0 到9 0 的分段出现波动，整体近似正态分布，并且图像在1 1 0 分左右峰度较高，人数比较多，数学成绩整体偏高，在试题难度适当的情况下，数学学科的成绩是非常好的(二)用E x c e l 计算单科成绩的信度。效度。难度以及区分度。同样，我们以理科数学试卷为例分析试卷的信度，效度，难度以及区分度首先计算试卷的信度，根据信度公式，我们需要计算考试成绩各题的方差，计算方差的方法是在E x c e l 中空白列中输入题号，插入样本标准差函数计算，操作：插入函数一S T D E V()然后以填充柄填充，求出每题的样本标准差，根据信度公式就可以得到试卷的信度计算效度时，由于效度公式中包含区分度，首先我们应该计算区分变根据得分率求差法公式，通过m a x()，m i n()函数可以求出最大最小值，以总人数的2 7 为标准分出高分组和低分组，通过总分计算区分度计算难度，在难度区域输入等号，选择插入一插入函数一均值(a v e m g e)一确定，根据难度公式容易得到难度，然后以填充柄填充经过计算得出相关结果如表1：表l 试卷分析表语文数学外语人数9 09 09 0单科总分1 5 01 5 01 5 0单科均分9 8 9 8 8 8 91 0 1 5 7 7 88 7 4 8 3 3 3单科最高分1 1 81 2 91 2 4 5单科最低分O00高分段人数2 42 42 4低分段人数2 42 42 4低分段均分8 7 2 57 6 56 6 6 0 4 1 7高分段均分1 0 8 51 1 9 4 41 0 7 7信度0 3 50 2 5O 8 0效度0 1 6O 2 l0 2 2区分度O 1 4 1 6 6 70 2 8 6 2 6 70 2 7 3 9 7 2难度0 6 5 9 9 2 60 6 7 7 1 8 50 5 8 3 2 2 2万方数据2 0 1 4 年1 月教育纵横在表1 中就是我们得到的相关计算过程量与结果，在本次考试的三科试卷，对于取样的两个班的成绩来说，语文试卷的信度为O 3 5，试卷信度较差，效度为0 1 6低于0 4，区分度0 1 4 低于0 1 5，试卷在评价准确度上是比较差的，难度为0 6 6，难度为容易，整体试卷制作不够理想；数学试卷的信度为0 2 5，可信度较低，效度为0 2 1，评价的有效性也较低，区分度为0 2 8 接近0 3，数学试卷在区分度上的效果是比较好的，难度为容易，数学试卷在信度和效度的要求上需要进一步提高；英语试卷的信度为0 8 高达8 0 的信度是比较好的，能够较准确地体现学生的真实水平，效度为0 2 2，效果较差，英语的区分度为0 2 7 接近0 3，能够比较好的区分学生，难度为0 5 8 容易横向来看，英语的信度为o 8，较符合试卷的信度要求，一份标准试卷的信度应该在0 8 以上，最好是能够达到0 9 的信度三科试卷的效度均低于0 3，与标准水平0 4有较大差距，试卷的效度是较好的；数学和英语的区分度都接近0 3，语文试卷的区分度较差，三科试卷的难度在0 5 到o 7 之间，这是一个比较合适的难度区间综合上述分析，在本次考试中试卷的整体情况为英语 数学 语文并且语文试卷的制定是比较差的，需要重新反思，在以后的实际中需要较大的改进三、成绩分析(一)成绩结构分析在试卷分析的基础上，我们要对单科成绩整体的内部结构进行分析，主要是对总体成绩的极差，均值，分布集中程度，以及成绩在各分数段的集中情况特别是四分位点(包括成绩的及格率，优秀率)的分析均值：是所有值之和除以其观察值的个数它考虑了所有数值，因而均值的大小受总体中极端数值的影响如果总体中有极大值出现，则会使均值偏于分布的右边，如果总体中出现极小值，均值则会偏于分布的左边众数：是总体中出现次数最多的数值，它只考虑总体中各数值出现频数的多少，不受极端数值的影响，但当总体中出现多个众数时，众数便没有意义中位数：只是考虑各单位数值在总体中的顺序变化它受极端数值的影响不大离中趋势用于测定数据集中各数值之间的差异程度表现在一个分布中各数值与均值的离差程度它的作用主要有：用于说明均值的代表性大小，反映现象的质量与风险，用于统计推断用于描述离中趋势的统计量为方坛线差和标准差，其中方差是标准差的平方，此外样本的峰度也可以用来描述离中趋势总体标准差：盯_、至鱼=生：、至垒孚VnVn。这些指标总体来说都是围绕成绩结构的集中趋势和离中趋势进行分析，此外在结构分析中仍然需要考虑对分布的分析，这是因为在相同均值和标准差的情况下，分布的整体形态也可能不一样，通过再一次的对分布形态进行研究，可以对正态分析作出有效的反馈，这里我们采用偏度和峰度的指标在统计分析中，用偏度指标对其进行测定偏度数值等于零，说明分布为对称；偏度数值大于零，说明分布呈现右偏态；如果偏度数值小于零，说明分布呈左偏态峰度是掌握分布形态的另一个指标，它能够描述分布的平缓或陡峭如果峰度数值等于0，说明分布为正态；如果峰度数值大于O，说明分布呈陡峭状态；如果峰度值小于0，则说明分布形态趋于平缓对于以上介绍的指标我们怎样在E x c e l 中方便的得到结果呢?我们可以直接插入统计函数，即可以迅速的得出我们需要的结果具体步骤如下：首先选中待分析数据，通过M A X(最大值)，M I N(最小值)，A V E R A G E(均值)，S T D E V P(n u m b e r l，n u m b e r 2，)(方差)，M E D I A N(中位数)，M O D E(众数)，Q U A R T I L E(a r r a v，q u a n)(四分位数)通过改变q u a r t 的值分别求第一四分位数，第二四分位数，第三四分位数和第四四分位数在结构分析中我们首先通过以上的函数对成绩进行整体的观测，然后需要对各分数段的分数进行统计，并计算分布率，与分布分析不同的是分组不是等距的，需要使用函数名为C O U N T I F 的函数，C O U N，I I F 函数的功能是对满足一定条件的数据进行频数统计，此函数在进行分数段统计时非常方便，c O u N T I F 函数的使用方法是选中统计区域插入函数一统计一C O U N T I F 图2 分数段统计对话框中第二个数据域是输人计数条件的，在实际运用中可以选取单元格，直接输入=c O U N，1 1 F(数据区域，“=7 2”)，第二个条件控制需要加双引号，通过=C O u N T I F(数据区域，“=9 6”)一C O u N T I F(数据区域，“英语 语文我们知道了每个学科对总分增加的影响，就可以考虑在哪一科上得分会使总分增加的概率是最高?在计算出得分因子的基础上，由概率论我们可以定义单科得分率记学科得分概率为阳不得分概率为g 产1 _ p i，则单科得分率r=卫鲤L 一。p l q 2 9 3+g l p 2 9 3+g l g 妒3在我们的学科中语、数、外的对应得分概率以及不得分概率为：p 1 观3 2，p 2=0 3 4，p 3-0 3 4，g l-0 6 8，g 飙6 6，q 舢6 6 代入公式计算得到语数外的单科得分率分别为r 语=0 3 1 4，r 数=0 3 4 3，r 外=0 3 4 3 从结果我们得到单科得分率的关系为：r 语 r 数=r 外，使得总分增加一分的概率较高的是数学和外语，概率较低的是语文，也就是说在接下来的教学中我们应该在外语和数学投入更多的精力，使总分增加的可能更高，但是我们需要注意的是，语、数、外三科成绩的得分率均低于0 5，得分率并不高，整体来看并没有对总分影响突出的学科，如果出现了某一学科得分率高于0 5 的情况，那么针对性的教学对学生成绩的提高将会有很好的效果这样，通过得分率研究我们可以得到哪一学科使总分增加的概率更高，从而确定偏重科目，教师可以运用这一规律在教学过程中进行实践检验四、结语通过对具体实例的演示，E x c e l 简单易上手，通过加载数据分析宏包又可以进行复杂的统计分析，给中学的教学研究者提供了很好的研究平台充分利用E x c e l 的相关功能，我们可以通过对试卷的整体状况，学生的成绩作出科学的判断，并将结果充分的运用到实际的教学过程。中去，不仅能够使得教学效果提高，而且为中学老师提供震量舅黑中。?善幺?高中版了另一种视角：深入地认识E X c e l 软件，使教师作为教学研究者能够掌握一种科学研究工具，在此基础之上可以进行更进一步的研究最后，通过一次完整的成绩分析操作后，我们可以建立并保存这个计算模板，在以后的考试分析中只需要更新分数就可以直接得出结果这也是本文撰稿的重要目的参考文献：1 雷蕾巧用E x c e l 进行有效的考试成绩分析 J 南昌高专学报，2 0 0 9(3)2 王春峰用E x c e l 函数进行考试成绩分析 J 中小学信息技术教育，2 0 0 4(6)3 邹来智史延龄，李洪津基于E x c e l 的成绩综合分析表制作 J 中国现代教育装备，2 0 0 8(7)4 任小艳李晓昕E x c e l 在学校教学与管理中的若干应用 J 焦作师范高等专科学校学报，2 0 0 4(4)5 安爱平，安素菊如何用E x c e l 制作班级成绩表 J 河北教育，2 0 0 3(1 1)6 马巧娥闫红军E x c e l 查找函数及其在数据管理中的应用 J 杨凌职业技术学院学报，2 0 0 4(2)7 韦伟利用E x c e l 实现考试成绩的统计分析 J 电子商务，2 0 1 l(8)8 肖信袁中直利用E x c e l 进行学生成绩统计分析的技巧 J 化学教学，2 0 0 2(1 0)9 刘鹏程，张明虎基于E x c e l 的教学数据分析 J 科教导刊(上旬刊)，2 0 1 0(4)1 0 苏琼E x c e l 在成绩管理和分析中的应用 J 卫生职业教育，2 0 0 4(2 4)1 1 罗洪群相关分析在加气站顾客满意度分析中的运用 J 成都大学学报，2 0 1 2(3 1)1 2 吴洋数据挖掘在大学英语教学质量分析中的应用研究 J 现代教育技术，2 0 1 0(2)1 3 武彤基于数据仓库技术的教学质量分析与评估系统 J 计算机工程与设计，2 0 0 9(3 0)1 4 陈德祥对期末考试成绩的分析和思考 J 安徽工业大学学报，2 0 0 3(2 0)1 5 赵明用E x c e l 实现成绩分析与试卷质量评价模型 J 吉林化工学院学报，2 0 0 4(2 1)1 6 应敏多元统计分析在考试成绩分析中的应用 J 科技论坛，2 0 0 6(4)1 7 苏斌统计分析在学生成绩评估中的应用 J 系统工程理论与实践。2 0 0 6(7)万方数据