运用Excel进行学生成绩的统计分析.docx

1、运用Excel进行学生成绩的统计分析学习，能够使用Excel实行学生成绩的统计分析。统计各分数段的人数及比例图示成绩分布（饼图绘制）学生总成绩及名次成绩等级及积点计算试卷质量评价示例1：用IF函数计算成绩等级=90A 80-89B 70-79C 60-69D =85,A,IF(C2=75,B,IF(C2=60,C,D),IF(ISBLANK(C2),缺考,输入有误)ISBLANK值为空白单元格ISNUMBER值为数字ISTEXT值为文本INDEX函数INDEX(reference,row_num,column_num,area_num) 返回引用中指定单元格或单元格区域的引用Reference

2、为对一个或多个单元格区域的引用Row_num为引用中某行的行序号，函数从该行返回一个引用Column_num为引用中某列的列序号，函数从该列返回一个引用MATCH( )返回在指定方式下与指定数值匹配的数组中元素的相对应位置MATCH(lookup_value，lookup_array，match_type)Lookup_value为需要在数据表中查找的数值，它能够是数值(或数字、文本或逻辑值)、对数字、文本或逻辑值的单元格引用。Lookup_array是可能包含所要查找的数值的连续单元格区域Match_type 为数字-1、0或1 ，它说明Excel 如何在lookup_array 中查找lo

3、okup_value。如果match_type 为1，函数MATCH 查找小于或等于lookup_value 的最大数值如果match_type 为0，函数MATCH 查找等于lookup_value 的第一个数值。如果match_type 为-1，函数MATCH 查找大于或等于lookup_value 的最小数值。 示例7：用INDEX和MATCH函数计算成绩等级=INDEX(H$2：H$11,MATCH(1,(C2=F$2：F$11)*(C2104)/COUNTA(C$2：C$46)，利用十字柄往右自动填充，把大于某值的分数改一下，因为总分不一样，上面的例子是总分130分的，假设总分100

4、分的，把104改成80即可，这是优秀率。同理在单元格N5中输入=COUNTIF(C$2：C$46,78)/COUNTA($C$2：$C$46)即可算出合格率。在格式/单元格格式/数值分类中，选择“百分比”，即出“%“符号。统计前三名在单元格N6中输入=LARGE(C$2：C$46,1)，或=MAX(C$2：C$46,1)往右自动填充得出各科成绩最高分=LARGE(C$2：C$46,2)可得各科第二名成绩=LARGE(C$2：C$46,3)可得各科第三名成绩统计后三名在单元格N6中输入=SMALL(C$2：C$46,1)，或=MIN(C$2：C$46,1)往右自动填充得出各科成绩最低分=SMAL

5、L(C$2：C$46,2)可得各科倒数第二名成绩=SMALL(C$2：C$46,3)可得各科倒数第三名成绩第四、利用if函数，对科目成绩实行等级划分，在单元格K2中输入=IF(D2=78,B,C),A)使用if函数实行判断，数学成绩达到优秀的，等级划分为“A”，在合格与优秀之间的，等级为“B”，不合格的，等级为“C”。二、利用excel统计函数实行数据分析对于一组数据，只获得一些常规的总分、排名，优秀率和合格率还远远不够，对于数据之间的分布规律还没表达出来。这时能够使用Excel提供的统计函数来实现。例如AVERAGE(平均值)、STDEV(样本标准差)、VAR(样本方差)、KURT(峰度系数

6、)、SKEW(偏度系数)、MEDIAN(中位数，即在一组数据中居于中间的数)、MODE(众数，即在一组数据中出现频率最高的数值)等。第一、计算样本方差=VAR(C2：C46)利用十字柄往右自动填充即可完成各个科目的样本方差计算。第二、计算标准差=STDEV(C2：C46)利用十字柄往右自动填充即可完成各个科目的标准差计算。样本中各数据与样本平均数的差的平方的平均数叫做样本方差, 样本方差的算术平方根叫做样本标准差。样本方差和样本标准差都是衡量一个样本波动大小的量，样本方差或样本标准差越大，样本数据的波动就越大。结果分析：我们看到数学和英语的样本方差和标准差较大，数学科目的标准差为26.84，英

7、语的标准差更是达到了33.137，说明对于数学和英语，学生的掌握水准参差不齐，差别较大。第三、计算峰度系数=KURT(C2：C46)利用十字柄往右自动填充即可完成各个科目的峰度系数计算。峰度系数（Kurtosis）用来度量数据在中心聚集水准。在正态分布情况下，峰度系数值是0。正的峰度系数说明观察量更集中，有比正态分布更长的尾部；负的峰度系数说明观测量不那么集中，有比正态分布更短的尾部，类似于矩形的均匀分布。结果分析：从上述表中数据能够看书语文和作文科目数值较大，8.5291和22.25，说明绝大部分学生掌握较好，成绩均比平均分高。第四、计算偏度系数=SKEW(C2：C46)利用十字柄往右自动填

8、充即可完成各个科目的峰度系数计算。偏度系数（Skewness）用来度量分布是否对称。正态分布左右是对称的，偏度系数为0。较大的正值表明该分布具有右侧较长尾部。较大的负值表明有左侧较长尾部。结果分析：语文和作文科目表现为较小的负数，说明较多分布在左侧。三、利用excel柱形图直观分析第一、首先得先找个能统计某区域内数据的频率分布函数，这里我们用FREQUENCY(data_array,bins_array)函数，data_array表示用来计算频率的区域，bins_array表示为前面数组实行分割一列数值。各个科目数据分数段分隔点为：max值, (max值+均值)/2，均值, (min值+均值)

9、/2，min值在单元格R33中输入=FREQUENCY($C$2：$C$46,R26：V26)，按下Ctrl+Shift+Enter组合键确认，往右自动填充，即完成语文科目各个分数段的人数统计。第二、插入图表选择图表向导中的柱形图，数据区域选择为上述统计好的人数，即“分布1”区域，完成柱形图。此方法能够较为直观地提供各个分数段人数的分布情况，这与上述峰度系数和偏度系数的计算是相辅相成的。各个科目的柱形图如下： 语文科目的数据分布图 数学科目的数据分布图 英语科目的数据分布图 政治科目的数据分布图 历史科目的数据分布图 作文科目的数据分布图四、分析与评价统计规律表明，学生的智力水平，包括学习水平

10、，实际动手水平等呈正态分布。因而正常的考试成绩分布应基本服从正态分布，柱形图中形状即为“中间高，两边低”。从上述几个数据和柱形图分析来看，对于语文和作文，峰度系数为较大的正数，柱形图上绝绝大部分分布均在左侧，学生掌握得较好；政治和历史在分布上也较为积极；数学和英语除了标准差较大，柱形图上分布也不均匀，学生掌握水准相差太大。个性化时代使得学校不再是用克隆的方法批量生产标准件的地方，必须看到学生之间的差异是客观存有的，只有根据学生的实际情况，因材施教，才能切实提升学校教学质量。第一：对语文、作文、政治和历史科目，展开“提优补差”活动。利用前面各科成绩等级的“ABC”分类，能够直观的看出学生各科的平

11、衡，根据“水桶理论”针对性重点是辅导，和家长沟通也很直接。对“C类”学生采取正面引导和心理沟通，多表扬、多鼓励，协助他们尽早树立自信心，同时对“B类”学生要时时加以鞭策，居安思危。第二：对数学、英语科目，可根据“ABC”等级展开“走班教育”。所谓“走班教学”就是根据大纲和教材要求，针对不同类型的学生，设计不同层次的教学目标，提出不同层次的学习要求，给予不同层次的教学与辅导，实行不同层次的检测，以达到他们各自的最佳状态。对不同的层次，作为教师，对哪些知识点必须详尽讲解，让学生尽快掌握，必须做到心中有数。结语：excel作为一个很好的工具，让我们更容易地对数字实行分析，在数字中发现问题，从而有的放矢的探究教学中。班主任老师不能仅仅注重总分、排名和平均分，也不能根据单一的分数评价学生。通过现代化的信息技术注重到分数背后，如何更好地展开“提优补差”和“走班教育”，因材施教，解决好学生之间的“个性差异”，才是我们今后工作中必须面对解决好的课题，也是落实好素质教育的所在。参考文献：1、张伯懿，统计学原理M.北京：中国石化出版社，1997.9：239244。 2、雪之舫工作室，EXCEL应用案例详解，北京，中国铁道出版社，2004.5