运用Excel进行学生成绩的统计分析.docx
《运用Excel进行学生成绩的统计分析.docx》由会员分享，可在线阅读，更多相关《运用Excel进行学生成绩的统计分析.docx（14页珍藏版）》请在冰豆网上搜索。
运用Excel进行学生成绩的统计分析
❑学习，能够使用Excel实行学生成绩的统计分析。
❑统计各分数段的人数及比例
❑图示成绩分布（饼图绘制）
❑学生总成绩及名次
❑成绩等级及积点计算
❑试卷质量评价
❑示例1：
用IF函数计算成绩等级
❑>=90—A80-89—B70-79—C60-69—D<60—E
❑示例2：
用COUNTIF函数统计各分数段的人数及比例
❑示例3：
用FREQUENCY函数统计人数
❑FREQUENCY（data_array,bins_array）
❑Data_array 为一数组或对一组数值的引用
❑Bins_array 为间隔的数组引用，该间隔用于对data_array中的数值实行分组。
❑对于返回结果为数组的公式，必须以数组公式的形式输入。
❑数组的使用
❑选择区域（H81：
H85）
❑输入公式“=FREQUENCY（C71：
C77,G81：
G85）”
❑使用组合键“Ctrl+Shift+Enter
❑示例4：
使用数据分析统计
❑工具—加载宏—分析工具库
❑工具—数据分析—直方图
❑示例5：
学生成绩名次计算
❑总评成绩计算Chapter0502.xls
❑绝对引用=C2*$H$2+D2*$I$2
❑混合饮用=C2*H$2+D2*I$2
❑区域命名=C2*平时比例+D2*期末比例
❑名次计算
❑=RANK（E2,$E$2：
$E$77）
❑示例6：
用IF函数计算成绩等级
❑=IF（ISNUMBER（C2）,IF（C2>=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为对一个或多个单元格区域的引用
❑Row_num为引用中某行的行序号，函数从该行返回一个引用
❑Column_num为引用中某列的列序号，函数从该列返回一个引用
❑MATCH（）
❑返回在指定方式下与指定数值匹配的数组中元素的相对应位置
❑MATCH（lookup_value，lookup_array，match_type）
❑Lookup_value为需要在数据表中查找的数值，它能够是数值（或数字、文本或逻辑值）、对数字、文本或逻辑值的单元格引用。
❑Lookup_array是可能包含所要查找的数值的连续单元格区域
❑Match_type为数字-1、0或1，它说明Excel如何在lookup_array中查找lookup_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）*（C2<=G$2：
G$11）,0））
❑示例7：
用INDEX和MATCH函数计算成绩积点
❑平均值、最大值、最小值、方差
❑数据分析
❑工具—数据分析—统计描述
❑示例1-5
❑作业1，2，5，6，7（P55）
浅谈用Excel统计分析学生成绩
摘要：
在学校教学中，经常需要对学生成绩实行统计和分析，特别是学期结束，作为班主任都要对班上各科目成绩实行统计和分析，除了计算总分、平均分外，还得去分析研究这些数字，找出问题所在，采取合理的措施。
以前往往面对大量的数据，都是通过手工计算，效率低，工作量大，还往往容易出错，经过一段的学习摸索，发现利用EXCEL自带的函数和图表功能，能够很好地协助解决教学中常见的学生成绩统计、分析问题。
关键词：
班主任成绩管理，excel，应用。
一、使用excel函数动态统计
通常在常用的应用中，对学生成绩实行分类汇总，计算平均分、优秀率、合格率等等。
第一、算学生总分
在I2单元格中输入公式：
=sum（C2：
H2），即可求出“学生1”的总分。
利用自动填充工程，拖至I45单元格，即可求出各个同学的总分。
第二、如何按总分排序
排序在成绩统计中最为常用，但人工排序工作量较大。
利用excel轻松能实现排序。
假设一个班46个人，输入0按降序排列
在J2单元格中输入公式
=RANK（I2,$I$2：
$I$46,0）,自动填充工程下拉，即完成每位同学的排名。
第三、如何计算平均分、优秀率、前三名、后三名成绩等数据
①计算平均分
输入=AVERAGE（C2：
C46）,利用十字柄往右自动填充。
②计算优秀率和及格率
在单元格N4中输入=COUNTIF（C$2：
C$46,">104"）/COUNTA（C$2：
C$46），利用十字柄往右自动填充，把大于某值的分数改一下，因为总分不一样，上面的例子是总分130分的，假设总分100分的，把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）往右自动填充
得出各科成绩最低分
=SMALL（C$2：
C$46,2）可得各科倒数第二名成绩
=SMALL（C$2：
C$46,3）可得各科倒数第三名成绩
第四、利用if函数，对科目成绩实行等级划分，
在单元格K2中输入
=IF（D2<104,IF（D2>=78,"B","C"）,"A"）
使用if函数实行判断，数学成绩达到优秀的，等级划分为“A”，在合格与优秀之间的，等级为“B”，不合格的，等级为“C”。
二、利用excel统计函数实行数据分析
对于一组数据，只获得一些常规的总分、排名，优秀率和合格率还远远不够，对于数据之间的分布规律还没表达出来。
这时能够使用Excel提供的统计函数来实现。
例如AVERAGE（平均值）、STDEV（样本标准差）、VAR（样本方差）、KURT（峰度系数）、SKEW（偏度系数）、MEDIAN（中位数，即在一组数据中居于中间的数）、MODE（众数，即在一组数据中出现频率最高的数值）等。
第一、计算样本方差
=VAR（C2：
C46）
利用十字柄往右自动填充即可完成各个科目的样本方差计算。
第二、计算标准差
=STDEV（C2：
C46）
利用十字柄往右自动填充即可完成各个科目的标准差计算。
样本中各数据与样本平均数的差的平方的平均数叫做样本方差,样本方差的算术平方根叫做样本标准差。
样本方差和样本标准差都是衡量一个样本波动大小的量，样本方差或样本标准差越大，样本数据的波动就越大。
结果分析：
我们看到数学和英语的样本方差和标准差较大，数学科目的标准差为26.84，英语的标准差更是达到了33.137，说明对于数学和英语，学生的掌握水准参差不齐，差别较大。
第三、计算峰度系数
=KURT（C2：
C46）
利用十字柄往右自动填充即可完成各个科目的峰度系数计算。
峰度系数（Kurtosis）用来度量数据在中心聚集水准。
在正态分布情况下，峰度系数值是0。
正的峰度系数说明观察量更集中，有比正态分布更长的尾部；负的峰度系数说明观测量不那么集中，有比正态分布更短的尾部，类似于矩形的均匀分布。
结果分析：
从上述表中数据能够看书语文和作文科目数值较大，8.5291和22.25，说明绝大部分学生掌握较好，成绩均比平均分高。
第四、计算偏度系数
=SKEW（C2：
C46）
利用十字柄往右自动填充即可完成各个科目的峰度系数计算。
偏度系数（Skewness）用来度量分布是否对称。
正态分布左右是对称的，偏度系数为0。
较大的正值表明该分布具有右侧较长尾部。
较大的负值表明有左侧较长尾部。
结果分析：
语文和作文科目表现为较小的负数，说明较多分布在左侧。
三、利用excel柱形图直观分析
第一、首先得先找个能统计某区域内数据的频率分布函数，这里我们用FREQUENCY（data_array,bins_array）函数，data_array表示用来计算频率的区域，bins_array表示为前面数组实行分割一列数值。
各个科目数据分数段分隔点为：
max值,（max值+均值）/2，均值,（min值+均值）/2，min值
在单元格R33中输入
=FREQUENCY（$C$2：
$C$46,R26：
V26），按下Ctrl+Shift+Enter组合键确认，往右自动填充，即完成语文科目各个分数段的人数统计。
第二、插入图表
选择图表向导中的柱形图，数据区域选择为上述统计好的人数，即“分布1”区域，完成柱形图。
此方法能够较为直观地提供各个分数段人数的分布情况，这与上述峰度系数和偏度系数的计算是相辅相成的。
各个科目的柱形图如下：
语文科目的数据分布图数学科目的数据分布图
英语科目的数据分布图政治科目的数据分布图
历史科目的数据分布图作文科目的数据分布图
四、分析与评价
统计规律表明，学生的智力水平，包括学习水平，实际动手水平等呈正态分布。
因而正常的考试成绩分布应基本服从正态分布，柱形图中形状即为“中间高，两边低”。
从上述几个数据和柱形图分析来看，对于语文和作文，峰度系数为较大的正数，柱形图上绝绝大部分分布均在左侧，学生掌握得较好；政治和历史在分布上也较为积极；数学和英语除了标准差较大，柱形图上分布也不均匀，学生掌握水准相差太大。
个性化时代使得学校不再是用‘克隆’的方法‘批量生产标准件’的地方，必须看到学生之间的差异是客观存有的，只有根据学生的实际情况，因材施教，才能切实提升学校教学质量。
第一：
对语文、作文、政治和历史科目，展开“提优补差”活动。
利用前面各科成绩等级的“ABC”分类，，能够直观的看出学生各科的平衡，根据“水桶理论”针对性重点是辅导，和家长沟通也很直接。
对“C类”学生采取正面引导和心理沟通，多表扬、多鼓励，协助他们尽早树立自信心，同时对“B类”学生要时时加以鞭策，居安思危。
第二：
对数学、英语科目，可根据“ABC”等级展开“走班教育”。
所谓“走班教学”就是根据大纲和教材要求，针对不同类型的学生，设计不同层次的教学目标，提出不同层次的学习要求，给予不同层次的教学与辅导，实行不同层次的检测，以达到他们各自的最佳状态。
对不同的层次，作为教师，对哪些知识点必须详尽讲解，让学生尽快掌握，必须做到心中有数。
结语：
excel作为一个很好的工具，让我们更容易地对数字实行分析，在数字中发现问题，从而有的放矢的探究教学中。
班主任老师不能仅仅注重总分、排名和平均分，也不能根据单一的分数评价学生。
通过现代化的信息技术注重到分数背后，如何更好地展开“提优补差”和“走班教育”，因材施教，解决好学生之间的“个性差异”，才是我们今后工作中必须面对解决好的课题，也是落实好素质教育的所在。
参考文献：
1、张伯懿，统计学原理［M］.北京：
中国石化出版社，1997.9：
239－244。
2、雪之舫工作室，EXCEL应用案例详解，北京，中国铁道出版社，2004.5
升级会员 