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

. 如何用EXCEL制作成绩分析的正态分布图  摘要：教学评价在学校教育教学工作中的重要地位毋容置疑考试是对学生进展的一种教育测量，也是对教师教学质量、出题水平的评价特别是数理统计方法的应用，使得我们对学生的教育测量转化为教学评价得到了有效的帮助本文论述了如何用EXCEL制作考试成绩的正态分成图，并结合其它相关的衡量标准，比方，区分度，学生成绩柱状分布图，难度系数，优秀率等，融合于一个图表中进展分析这是一种有效的可操作的方法，能让每一位教师从图中获得一种易于承受的直观认识，并且方便找出教学中存在的问题，并为以后教学改良措施的制定提供有效的帮助 关键词：教学评价，EXCEL，成绩分析，正态分布  教育评价学是教育科学领域中的一个重要的应用性很强的分支学科在当今世界教育领域中，教育评价、教育根底理论和教育开展被认为是三大研究围教育是人类有目的、有计划、有组织的活动，教育活动涉与教育方案、教育活动的实施、教育活动的参与者等等，要提高学校教育活动的有效性，就必须对这些容进展适当的评价因此，教育评价对于学校教育的改革和开展，对于学校教育的管理和决策，都有着至关重要的作用，所以备受各国政府与其教育行政部门的重视。

 在学校日常工作中，通过教育评价活动来强化管理，已受到人们的广泛重视不管是宏观的教育行政管理还是微观的学校工作管理，都把教育评价当作一种有效的管理手段就一所学校而言，管理水平的上下在一定程度上能反映出该校的评价工作开展得怎么样，而评价水平的上下又能表达出学校领导者的管理水平实施素质教育的关键是教师素质的上下为了提高教师素质，教育行政部门和学校都加大了对教师的管理力度，开展了对教师的教学评价工作通过有效地评价教师，不仅调动了教师工作的积极性，而且进一步促进了师资队伍的建立所以，要做一个有效的管理者，就要重视教育评价的作用 教学评价是教育评价的重要组成局部它以考试作为一种根底性的手段，来收集有关学生对知识的掌握程度方面的信息；以测验作为测量的手段，获得客观的数据，进展进一步的分析、综合，并作出价值上的判断 在学校教育教学工作中，从研究的目的出发开展评价工作，就是要通过评价活动促进教育教学改革实验的进展，从而提高教育教学的科学研究水平因此，教学评价将有助于学校与教育工作者自身进展检查、反思，并主动改良教育教学工作，从而有助于提高教育教学质量教学常规工作中的段考、期考，不仅仅是为了测量学生的知识掌握程度，我们还应该使用现代的数理统计技术和现代信息技术来对考试成绩进展仔细、有效的分析，从中找出需要改良的教学问题，并为今后的教学改革提供依据。

因此，我们就需要使用正态分布曲线来给我们的成绩分析提供一个有效的参考 一、 如何用EXCEL制作成绩分析的正态分布图呢？我们先来看一份样图： 从图1中，我们可以看到一些信息，横坐标为从0分到150分，共计151个刻度，纵坐标为人数(相当于样本的频数)在这里，纵坐标没有使用“频率〞而是使用“频数〞来做单位，这是因为我们不是只抽取一局部“样本〔随机抽取学生〕〞来进展分析，而是对整个“总体〔整个年级〕〞来进展分析，因此，我们使用频数〔具体的人数〕来做纵坐标单位，更加具有实际意义，也更为直观图中的黑色柱状图是学生成绩的分布，粉红色的曲线是由学生该科成绩的平均分和标准方差所决定的正态分布曲线，左边的红色竖线是低分率的线，中间的黄色竖线是与格率的线，右边的绿色线是优秀率的线上面还附有该科的平均分、难度系数、区分度、低分率、与格率、优秀率等数值 相应的一些评价参数的制作方法如下： 1、平均分 平均分在EXCEL里使用函数：AVERAGE(Number1,Number2„„)其中：Number1，number2，„„是要计算平均值的 1～30 个参数 例如：在新的单元格中插入AVERAGE(D3:D923)，“D3:D923〞就是学生成绩所在的单元格围。

 2、难度系数  难度系数是反映试题的难易程度，即考生在一个试题或一份试卷中的失分程度如总分值150分的试题，考生平均得分108分，平均失分42分，那么难度系数为42/150=0.28 难度系数的计算公式为：L=1—X/W 其中，L为难度系数，X为样本平均得分，W为试卷总分〔一般为100分或150分〕 为了简化试卷难度系数的计算，在EXCEL中可以用以下简单的计算公式进展计算： P=平均分/总分值值 题目的难度值越大，那么题目的难度越小；题目的难度值越小，那么题目的难度越大  3、试题区分度 为了简单计算， 教师可以使用下面的一种方法进展计算区分度： 先将分数排序，P1=27％高分组的平均分，P2=27％低分组的平均分，区分度D=〔P1－P2〕/总分值值  区分度一般在0～+1之间，值越大区分度越好试题的区分度在0.4以上说明此题的区分度很好，0.3～0.39说明此题的区分度较好，0.2～0.29说明此题的区分度不太好需修改，0.19以下说明此题的区分度不好，应淘汰 4、低分率、与格率、优秀率 我们按照低分线、与格线、优秀线分别为总分40%、60%、80%来确定，在图中分别用红线、黄线、绿线来注明，并且在图中上方注明对应的低分率、与格率、优秀率，这样看起来比拟形像直观。

 5、学生成绩的柱状分布图 在EXCEL里使用函数：COUNTIF (range,criteria)，其中range为单元格围，直接左键拖击单元格即可选定，criteria为判定标准，可以是数值〔直接输数字即可无需引号，如0,1等〕或字符〔须用英文双引号括起，如"YES","A","男","教师"〕等， 例如：我要计算50分的人数,那么插入的函数为COUNTIF(D3:D923,"=50") ，“D3:D923〞是学生成绩所在的单元格围，“=50〞那么是条件另外需要注意的地方是，如果考试成绩不是整数，比方：50.5，在使用“=50〞的条件下，就不会计算在，这时就必需使用取整函数：ROUND(number,num_digits)，按指定的位数对数值进展四舍五入为了防止不必要的麻烦，一旦发现该科成绩存在小数局部，一般都应该是取整在先，统计人数在后也就是先用ROUND取整，再用COUNTIF计算人数从0分到150分的人数都统计出来后，就可以用EXCEL的“图表向导〞制作出相应的柱状分布图了，如图2所示 6、正态曲线 制作正态曲线需要使用函数：NORMDIST(x,mean,standard_dev,cumulative)  X 为需要计算其分布的数值，Mean 为分布的算术平均值，standard_dev 为分布的标准偏差，Cumulative 为一逻辑值，指明函数的形式。

如果 cumulative 为 TRUE，函数 NORMDIST 返回累积分布函数；如果为 FALSE，返回概率密度函数 例如：如果总分值为150分，那么我们就要从0分到150分一共151个分值进展计算正态曲线的值，然后才能做出对应的曲线假设我们要计算50分，那么在表达式中：D1080*NORMDIST($C$980,D1078,D1079,FALSE)，“$C$980〞对应的就是50分所在的单元格，“D1078〞那么对应我们事先计算得出的平均分，“D1079〞那么对应我们事先计算得出的标准方差，“FALSE〞那么是我们需要的概率密度函数类型D1080〞那么对应这批分析成绩的总人数，是用来矫正正态曲线的重要参数只有经过矫正后，学生成绩的柱状分布图才能与正态曲线结合起来因为学生成绩的柱状分布图的纵坐标是人数，那么柱状图的总面积那么为成绩的总人数而正态曲线与横坐标轴包含的面积那么为1，所以只有把正态曲线的值乘以总人数，两者的面积才能相等，这样两者才能完美结合起来从“频率=频数/样本数〞这一关系来说，正态曲线图的纵坐标单位是“频率〞，因此乘以“D1080〞〔 样本数〕才可以得到“频数〞，这样两者的纵坐标单位才一致，才能放置于同一个坐标系。

 从0分到150分的人数都统计出来后，就可以用EXCEL的“图表向导〞制作出相应的正态曲线分布图了，如图3所示 当我们把“学生成绩的柱状分布图〞与“正态曲线分布图〞共同制作在一个图表中，就可以得到如图1所示的效果图了在这样一幅效果图中，我们建立了一批数据频数分布柱状图与该批数据平均值和标准差所确定的正态分布曲线的对照关系有了参照，我们就可以分析这批学生该科考试成绩的质量了另外再附加上平均分、难度系数、区分度、低分率、与格率、优秀率等数值以后，就可以开场对成绩进展更为详细和完整的分析了 二、做完各科目的成绩正态分布图以后，对于以下的一些例图，我们可以尝试做一些分析 1、从图4中，我们可以看出试题的区分度很好，到达0.56，但是从成绩分布来说，不符合正态分布针对这种情况我们可以采取一些教学上的改良措施，比方：抓根底题的练习，学生每天的习题量要够份量，每天要催促学生独立完成作业,要注重学生平时学习的过程 2、从图5中，我们可以看出，该科的总分〔120分〕用最右边的绿色竖线来标识，左边的绿色竖线那么是优秀线因为做学生成绩分析是所有科目一起做的，为了减少操作的麻烦，也是为了能进展不同科目的横向比拟，虽然不同科目的成绩没有可比性，但是对于他们的教学评价却有一定的可比性，为了能让直观感觉一致性，对于横坐标的刻度标准应该要一致，所以我们统一用150分来确定横坐标的最大值。

这时候，我们只需要在总分值值的地方用一条绿色的坚线来注明该科的总分值为120分，考虑0分为第一刻度，那么120分就应该在121 的刻度位置试题的区分度在0.37，但是从成绩分布来说，应该是属于正偏态分布如果频数分布的顶峰向左偏移，长尾向右侧延伸称为正偏态分布〔考试分数集中在低端〕，也称右偏态分布；同样的，如果频数分布的顶峰向右偏移，长尾向左延伸那么成为负偏态分布〔考试分数集中在高端〕，也称左偏态分布  从这里我们可以了解到，在编制试题的时候，平时测验的难度设定要利于学生的学习，同时还要考虑与格率，防止损伤学困生的自尊心，但一定的难度却能增加区分度，这对全面了解、掌握学生学习情况有十分重要的作用除此之外，难度水平确实定还要考虑对分数分布的影响，一般以正态分布为前提，有时正偏态分布更能激发学生的学习积极性 3、从图7中，我们可以看出存在两个峰值，中间凹，两边凸，说明两极分化严重在教学改良措施中，应该加强对薄弱学生的关注，催促检查他们的学习，从而尽量减小他们与优秀学生的差距，在图中那么尽量表达为两个峰值合为一个峰值 该科的总分〔100分〕用最右边的绿色竖线来标识，右边的绿色竖线那么是优秀线我们可以看到粉红色的正态曲线已经溢出了总分值线。

因为该科的平均分比拟高，区分度为0.41，正态曲线就会比拟矮，由于正态曲线是两端对称性的，所以就会存在一端溢出的现象，这也可以说是用正态曲线来分析成绩的一个局限性因此，当难度系数在0.5，区分度不超过0.5的时候用正态曲线来分析是最适宜的 从以上各类情况，我们可以看到，用EXCEL来制作成绩分析的正态布图，并且结合难度系数、区分度、成绩柱状分布图、低分率、与格率、优秀率等参数来对学生的考试成绩来进展分析，这是一种有效的可操作的方法通过对多种衡量参数融合为一体来进展分析，使得我们对教学质量的整体把握更为详细和有效如果只是单一的学生成绩分布图，没有正态曲线来做参考，那也只能是一副图而已就相当于数量没有单位，就没有了衡量价值 因此，通过这种可操作的方法来对学生考试成绩进展分析，并作出教学评价非常有意义：1、有利于全面贯彻国家的教育方针2、有利于学校教育教学决策的科学化3、有利于推进学校管理、评价工作的规。