如何用EXCEL制作成绩分析的正态分布图

《如何用EXCEL制作成绩分析的正态分布图》由会员分享，可在线阅读，更多相关《如何用EXCEL制作成绩分析的正态分布图（6页珍藏版）》请在金锄头文库上搜索。

1、如何用 EXCEL 制作成绩分析的正态分布图摘要：教学评价在学校教育教学工作中的重要地位毋容置疑。考试是对学生进行的一种教育测量，也是对教师教学质量、出题水平的评价。特别是数理统计方法的应用，使得我们对学生的教育测量转化为教学评价得到了有效的帮助。本文论述了如何用 EXCEL 制作考试成绩的正态分成图，并结合其它相关的衡量标准，比如，区分度，学生成绩柱状分布图，难度系数，优秀率等，融合于一个图表中进行分析。这是一种有效的可操作的方法，能让每一位教师从图中获得一种易于接受的直观认识，并且方便找出教学中存在的问题，并为以后教学改进措施的制定提供有效的帮助。关键词：教学评价，EXCEL，成绩分析，正

2、态分布。教育评价学是教育科学领域中的一个重要的应用性很强的分支学科。在当今世界教育领域中，教育评价、教育基础理论和教育发展被认为是三大研究范围。教育是人类有目的、有计划、有组织的活动，教育活动涉及教育方案、教育活动的实施、教育活动的参与者等等，要提高学校教育活动的有效性，就必须对这些内容进行适当的评价。因此，教育评价对于学校教育的改革和发展，对于学校教育的管理和决策，都有着至关重要的作用，所以备受各国政府及其教育行政部门的重视。在学校日常工作中，通过教育评价活动来强化管理，已受到人们的广泛重视。不论是宏观的教育行政管理还是微观的学校工作管理，都把教育评价当作一种有效的管理手段。就一所学校而言，

3、管理水平的高低在一定程度上能反映出该校的评价工作开展得怎么样，而评价水平的高低又能体现出学校领导者的管理水平。实施素质教育的关键是教师素质的高低。为了提高教师素质，教育行政部门和学校都加大了对教师的管理力度，开展了对教师的教学评价工作。通过有效地评价教师，不仅调动了教师工作的积极性，而且进一步促进了师资队伍的建设。所以，要做一个有效的管理者，就要重视教育评价的作用。教学评价是教育评价的重要组成部分。它以考试作为一种基础性的手段，来收集有关学生对知识的掌握程度方面的信息；以测验作为测量的手段，获得客观的数据，进行进一步的分析、综合，并作出价值上的判断。在学校教育教学工作中，从研究的目的出发开展评

4、价工作，就是要通过评价活动促进教育教学改革实验的进行，从而提高教育教学的科学研究水平。因此，教学评价将有助于学校及教育工作者自身进行检查、反思，并主动改进教育教学工作，从而有助于提高教育教学质量。教学常规工作中的段考、期考，不仅仅是为了测量学生的知识掌握程度，我们还应该使用现代的数理统计技术和现代信息技术来对考试成绩进行仔细、有效的分析，从中找出需要改进的教学问题，并为今后的教学改革提供依据。因此，我们就需要使用正态分布曲线来给我们的成绩分析提供一个有效的参考。一、如何用 EXCEL 制作成绩分析的正态分布图呢？我们先来看一份样图：图 1语 文 : 平 均 分 :91.3 难 度 ： 0.61

5、 区 分 度 :0.21低 分 率 ： 0.04 及 格 率 ： 0.63 优 秀 率 0.00051015202530351 11 21 31 41 51 61 71 81 91 101 111 121 131 141 151从图 1 中，我们可以看到一些信息，横坐标为从 0 分到 150 分，共计 151 个刻度，纵坐标为人数(相当于样本的频数)。在这里，纵坐标没有使用“频率”而是使用“频数”来做单位，这是因为我们不是只抽取一部分“样本（随机抽取学生） ”来进行分析，而是对整个“总体（整个年级） ”来进行分析，因此，我们使用频数（具体的人数）来做纵坐标单位，更加具有实际意义，也更为直观。图

6、中的黑色柱状图是学生成绩的分布，粉红色的曲线是由学生该科成绩的平均分和标准方差所决定的正态分布曲线，左边的红色竖线是低分率的线，中间的黄色竖线是及格率的线，右边的绿色线是优秀率的线。上面还附有该科的平均分、难度系数、区分度、低分率、及格率、优秀率等数值。相应的一些评价参数的制作方法如下：1、平均分平均分在 EXCEL 里使用函数：A VERAGE(Number1,Number2)其 中 ：Number1， number2， 是 要 计 算 平 均 值 的 1 30 个 参 数 。例 如 ： 在 新 的 单 元 格 中 插 入 AVERAGE(D3:D923)， “D3:D923”就 是 学 生

7、 成 绩 所 在的 单 元 格 范 围 。2、难度系数 难度系数是反映试题的难易程度，即考生在一个试题或一份试卷中的失分程度。如满分150 分的试题，考生平均得分 108 分，平均失分 42 分，则难度系数为 42/150=0.28。难度系数的计算公式为： L=1X/W其中，L 为难度系数，X 为样本平均得分，W 为试卷总分（一般为 100 分或 150 分） 。为了简化试卷难度系数的计算，在 EXCEL 中可以用以下简单的计算公式进行计算：P=平均分/满分值题目的难度值越大，则题目的难度越小；题目的难度值越小，则题目的难度越大。一般认为，试题的难度指数在 0.3-0.7 之间比较合适。整份试

8、卷的平均难度指数最好掌握在 0.5 左右，高于 0.7 和低于 0.3 的试题不能太多。3、试题区分度为了简单计算， 教师可以使用下面的一种方法进行计算区分度： 先将分数排序，P1=27高分组的平均分，P2=27低分组的平均分，区分度 D=（P1P2）/满分值。 区分度一般在 0+1 之间，值越大区分度越好。试题的区分度在 0.4 以上表明此题的区分度很好，0.30.39 表明此题的区分度较好，0.20.29 表明此题的区分度不太好需修改，0.19 以下表明此题的区分度不好，应淘汰。 4、低分率、及格率、优秀率我们按照低分线、及格线、优秀线分别为总分 40%、60%、80%来确定，在图中分别用

9、红线、黄线、绿线来注明，并且在图中上方注明对应的低分率、及格率、优秀率，这样看起来比较形像直观。5、学生成绩的柱状分布图在 EXCEL 里使用函数：COUNTIF (range,criteria)，其中 range 为单元格范围，直接左键拖击单元格即可选定，criteria 为判定标准，可以是数值（直接输数字即可无需引号，如 0,1 等）或字符（须用英文双引号括起，如YES,A,男,老师）等，例如：我要计算 50 分的人数,则插入的函数为 COUNTIF(D3:D923,=50) ， “D3:D923”是学生成绩所在的单元格范围， “=50”则是条件。另外需要注意的地方是，如果考试成绩不是整数

10、，比如：50.5，在使用“=50”的条件下，就不会计算在内，这时就必需使用取整函数：ROUND(number,num_digits)，按指定的位数对数值进行四舍五入。为了避免不必要的麻烦，一旦发现该科成绩存在小数部分，一般都应该是取整在先，统计人数在后。也就是先用ROUND 取整，再用 COUNTIF 计算人数。从 0 分到 150 分的人数都统计出来后，就可以用EXCEL 的“图表向导”制作出相应的柱状分布图了，如图 2 所示。051015202530351 11 21 31 41 51 61 71 81 91 101 111 121 131 141 151图 2 学生成绩的柱状分布图6、正

11、态曲线制作正态曲线需要使用函数：N ORMDIST(x,mean,standard_dev,cumulative) X 为 需 要 计 算 其 分 布 的 数 值 ， Mean 为 分 布 的 算 术 平 均 值 ， standard_dev 为 分 布的 标 准 偏 差 ， Cumulative 为 一 逻 辑 值 ， 指 明 函 数 的 形 式 。 如 果 cumulative 为 TRUE， 函 数 NORMDIST 返 回 累 积 分 布 函 数 ； 如 果 为 FALSE， 返 回 概 率 密 度 函 数 。例如：如果满分为 150 分，那么我们就要从 0 分到 150 分一共 15

12、1 个分值进行计算正态曲线的值，然后才能做出对应的曲线。假如我们要计算 50 分，那么在表达式中：D1080*NORMDIST($C$980,D1078,D1079,FALSE)， “$C$980”对应的就是 50 分所在的单元格，“D1078”则对应我们事先计算得出的平均分， “D1079”则对应我们事先计算得出的标 准 方差 ， “FALSE”则是我们需要的概 率 密 度 函 数 类 型 。 “D1080”则对应这批分析成绩的总人数，是用来矫正正态曲线的重要参数。只有经过矫正后，学生成绩的柱状分布图才能与正态曲线结合起来。因为学生成绩的柱状分布图的纵坐标是人数，那么柱状图的总面积则为成绩的

13、总人数。而正态曲线与横坐标轴包含的面积则为 1，所以只有把正态曲线的值乘以总人数，两者的面积才能相等，这样两者才能完美结合起来。从“频率=频数/样本数”这一关系来说，正态曲线图的纵坐标单位是“频率” ，因此乘以“ D1080”（ 样本数）才可以得到“频数” ，这样两者的纵坐标单位才一致，才能放置于同一个坐标系内。从 0 分到 150 分的人数都统计出来后，就可以用 EXCEL 的“图表向导”制作出相应的正态曲线分布图了，如图 3 所示。051015202530351 11 21 31 41 51 61 71 81 91 101 111 121 131 141 151图 3 正态曲线当我们把“学

14、生成绩的柱状分布图”与“正态曲线分布图”共同制作在一个图表中，就可以得到如图 1 所示的效果图了。在这样一幅效果图中，我们建 立 了 一 批 数 据 频 数 分 布 柱状 图 与 该 批 数 据 平 均 值 和 标 准 差 所 确 定 的 正 态 分 布 曲 线 的 对 照 关 系 。 有 了 参 照 ， 我 们就 可 以 分 析 这 批 学 生 该 科 考 试 成 绩 的 质 量 了 。 另外再附加上平均分、难度系数、区分度、低分率、及格率、优秀率等数值以后，就可以开始对成绩进行更为详细和完整的分析了。二、做完各科目的成绩正态分布图以后，对于以下的一些例图，我们可以尝试做一些分析。1、从图

15、4 中，我们可以看出试题的区分度很好，达到 0.56，但是从成绩分布来说，不符合正态分布。针对这种情况我们可以采取一些教学上的改进措施，比如：抓基础题的练习，学生每天的习题量要够份量，每天要督促学生独立完成作业,要注重学生平时学习的过程。图 4数 学 : 平 均 分 :76.6 难 度 ： 0.51 区 度 :0.56低 分 率 ： 0.35 及 格 率 ： 0.37 优 秀 率 ： 0.15051015202530351 11 21 31 41 51 61 71 81 91 101 111 121 131 141 1512、从图 5 中，我们可以看出，该科的总分（120 分）用最右边的绿色竖

16、线来标识，左边的绿色竖线则是优秀线。因为做学生成绩分析是所有科目一起做的，为了减少操作的麻烦，也是为了能进行不同科目的横向比较，虽然不同科目的成绩没有可比性，但是对于他们的教学评价却有一定的可比性，为了能让直观感觉一致性，对于横坐标的刻度标准应该要一致，所以我们统一用 150 分来确定横坐标的最大值。这时候，我们只需要在满分值的地方用一条绿色的坚线来注明。该科的满分为 120 分，考虑 0 分为第一刻度，那么 120 分就应该在 121的刻度位置。试题的区分度在 0.37，但是从成绩分布来说，应该是属于正偏态分布。图 5物 理 : 平 均 分 :48.7 难 度 ： 0.41 区 分 度 :0.37低 分 率 ： 0.53 及 格 率 ： 0.15 优 秀 率 0.01051015202530351 11 21 31 41 51 61 71 81 91 101 111 121 131 141 151如果频数分布