运用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 0 9 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 5 6 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 7 0 2 8 6 2 6 70 2 7 3 9 7 2 难度 0 6 5 9 9 2 6 0 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 任小艳