运用Excel进行学生成绩的统计分析[1]

❑学习，能够使用Excel实行学生成绩的统计分析。

❑统计各分数段的人数及比例

❑图示成绩分布（饼图绘制）

❑学生总成绩及名次

❑成绩等级及积点计算

❑试卷质量评价

❑示例1：用IF函数计算成绩等级

❑>=90—A 80-89—B 70-79—C 60-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：使用数据分析统计

❑工具—加载宏—分析工具库

❑工具—数据分析—直方图

❑成绩分段统计（P59-62）

❑各分数段人数占总人数的比例❑饼图

❑注意数据源，切忌勿先算比例

❑图的修饰和格式化

❑示例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，函数MA TCH 查找等于lookup_value 的第一个数值。

❑如果match_type 为-1，函数MA TCH 查找大于或等于lookup_value 的最小数值。

❑示例7：用INDEX和MA TCH函数计算成绩等级

❑=INDEX(H$2：H$11,MATCH(1,(C2>=F$2：F$11)*(C2<=G$2：G$11),0))

❑示例7：用INDEX和MA TCH函数计算成绩积点

❑平均值、最大值、最小值、方差

❑数据分析

❑工具—数据分析—统计描述

❑示例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")