Excel成绩统计

用Excel模板进行成绩统计实战操作

教育教学评价是贯穿课程改革、素质教育全过程的重要内容，通过对教师教学质量和学生学习质量的精确评价,利用先进的评价分析方法从不同的角度分析和反馈教育教学质量情况，是引导教师改进教学方法、提高学生学习成效的重要手段，也是教育行政部门、教育科研单位、中小学校着力研究的重要课题。

教学评价的环节中必不可少的则是对学生考试成绩的统计分析，它可以让教师随时发现自己所教班级在整个年级中的实际状态，从多角度、多方位对整体班级的实力状况进行综合的教学质量分析，及时了解和分析自己班级的每名学生在整个学业阶段的学习进步状态。因此，如何快速、准确的完成统计是当务之急。

而目前市面上的成品统计软件由于栏目、项目固定，往往不符合学校及教师的实际需求。我通过不断摸索、改进，采用在Excel中建立模板的方法，只要将原始数据复制到模板中，所有的统计表格就可以立即打印出来，大大提高了工作效率。下面就请大家一起看看如何建立一个成绩统计的模板吧！

一、成绩统计模板创建前期准备

1、创建不同功能的工作表

打开一个新的工作簿，将其中的工作表名称sheet1、sheet2、„„等重命名为“年级”、“一班”、“二班”„„、“平均”、“总分”等。新打开的工作薄默认只有三个工作表，其它所需的工作表只需插入工作表后再重命名

即可。其中“年级”、“一班”、“二班”„„用来存放原始数据，“平均”、“总分”等工作表用来制作相应的不同统计项目的统计表格，完全根据本校实际情况来创建。（如图一）

图一

2、完成原始数据工作表字段定义

在“年级”和“一班”、 “二班”等班级工作中第一行统一定义如下的字段名称：“班次、考号、学号、姓名、语文、数学、英语、„„总分、级名次、班名次”。（如图二）

图二

3、制作成绩统计表框架

根据不同学校的实际需求，可以制作很多不同格式的成绩统计表，本文实例介绍最常用和必须的几类统计表。其他类型的统计表都可以在此基础之上修改而成。

按图示分别制作出如下空表：“平均分统计表”、“总分统计表”、“比率统计表”、“分班单科成绩统计表”。

平均分统计表

总分统计表

比率统计表

分班单科成绩统计表

二、成绩统计模板函数和公式的创建

函数和公式的建立和函数的复制是快速制作成绩统计模板的关键。下面将对我们已经建立的各种工作表中应设立的函数和公式进行介绍。

1、求总分。在“年级”工作表中L2单元格中输入公式“=SUM(E2:K2)”，即求第一个学生的“语文”至“生物”共七科的总分。然后从L2单元格开始向下作自动填充公式操作（方法是：鼠标放在L2单元格的右下角，鼠标会由原来的空心十字架变为实心黑十字架，然后单击此处向下作拖动操作）到合适的格数为止，有多少人就拉多少格。

2、求名次。包括某成绩在年级的年级名次或在该班的班级名次。

在“年级”工作表M2单元格中建立公式“=RANK(L2,L$2:L$900)”，它的含义是：返回单元格L2中的数值在L2到L900这个范围的排位，即求出该同学的总分年级名次。然后从M2单元格开始向下作自动填充公式操作。

需要说明的是：在公式“=RANK(L2,L$2:L$900)”中既有相对引用，又有绝对引用，这是为准确的进行公式复制和自动填充公式所必须的。其中L2为相对引用，可以保证公式向下复制时返回的始终是相应总分数值的排位；L$2:L$900为绝对引用，可以保证公式向下复制时数值的排位范围始终保持为单元格L2到L900的绝对位置范围（该范围可以根据学生人数调大，必须保证大于等于学生人数）。

在“班级”工作表N2单元格中建立公式“=RANK(L2,L$2:L$100)”，它的含义是：返回该同学的总分在班上的班级名次。然后从N2单元格开始向下作自动填充公式操作。

3、求平均。包括班级平均和年级平均。

在“平均”工作表B3单元格建立公式“=AVERAGE(一班!$E$2:$E$100)”它的含义是：求“一班”工作表中语文科所在的单元格从E2到E100的平均值。然后将公式向右进行自动填充公式操作，再将“一班！”相应的改为“二班！”“三班！” „„，则求出各班的“语文”平均分。

在“平均”工作表K3单元格建立公式“=AVERAGE(年级!$E$2:$E$900)”它的含义是：求“年级”工作表中语文科所在单元格从E2到E100的平均值。注意：求年级平均分时不能将各班的平均分再次求平均。

用同样的方法可以求出数学、英语„„等学科的平均分。 4、求总分分数段。

在“总分”工作表F4单元格中建立公式“=COUNTIF(一班!$L$2:$L$90,\"<450\")”，它的含义是：求“一班”中“总分”小于450的人数。在G4单元格中建公式“=COUNTIF(一班!$L$2:$L$90,\">=450\")”，

求出总分大于等于450分的人数。然后从G4单元格开始向右自动填充公式，再把其中的“450”改为480、500、520„„等。则求出一班相应的总分分数段的人数。

将一班的分数段公式向下复制到二班、三班„„，然后将其中的“一班！”相应的替换为“二班！”、“三班！” „„，则求出其它班级的总分分数段。

5、求优生率、及格率

求比率可以分三步完成：第一步在“比率”工作表C4单元格建公式“=COUNTIF(一班!$E$2:$E$100,\">0\")”，含义是求“一班”中“语文”科考试成绩大于0的人数，即语文科实际参考人数。第二步在D4单元格建公式“=COUNTIF(一班!$E$2:$E$90,\">=135\")”，求出一班语文科135分以上的人数，即优生人数。第三步在E4单元格建公式“=D4/$C4*100”，求出优生率（%）。

如果不需要显示优生人数和参考人数，也可以直接求出优生率，则在单元格输入的公式为 “=COUNTIF(一班!$E$2:$E$90,\">=135\")/ COUNTIF(一班!$E$2:$E$100,\">0\")*100”。

及格率的方法类似，仅将成绩由135改为90分即可（150分制）。 6、求各科分数段。

前面讲过大于或小于某分值的分数段的公式建立方法，这里主要讲两个分值之间的人数统计。例如在“班科”工作表中，在单元格K3输入公式求语文成绩在80和90之间的人数，公式应为：“=COUNTIF(一班!$E$2:$E$90,\">=80\")-COUNTIF(一班!$E$2:$E$90,\">=90\")。其余的分数

段范围以此类推可以很容易的完成。

统计模板中还涉及其它函数，诸如求最高分MAX（）、最低分MIN（）、求和函数SUM（）等一些常用函数，这里不再累述。

三、保存成绩统计Excel模板

单击菜单“文件→保存（或另存为）”，出现“另存为”的对话框，选择“保存类型”中的“模板”，在文件名输入栏中输入“成绩自动统计模板”。

四、输入数据、完成统计

完成模板创建后，就可以随时打开模板、输入数据，让它替我们完成统计工作了。

1、输入“年级”工作表数据

在“年级”工作表中输入数据的方法主要有两种：一是直接在“年级”工作表中录入，二是从其它文件粘贴到本工作表中，可以大大提高效率。

输入成绩的顺序可以按考号也可按学号，完成后可以进行排序操作，不会影响统计结果。

2、输入班级工作表数据

将“年级”工作表已经完成的数据按“班级”主要关键字递增排序，次要关键字可以根据自己的需要选择。此操作将数据按一班、二班„„的顺序集中在一起，然后将相应的一班的数据复制到“一班”工作表，二班的数据复制到“二班”工作表，以此类推„„。

以上两步操作完成后，我们创建的平均分统计表、总分统计表、比率统计表、单科成绩统计表等所有数据全部自动完成统计，可以立即打印输出。

总之在模板创建好之后，每次在成绩统计时，我们所做的工作仅仅是向里面输入数据，其它的都由模板替你完成了。

本模板有如下几个优势：1、它的各种统计表格都在的工作表中完成，与原始数据是相对的，改变了其它一些统计方法中原始数据与统计数据在同一工作表，不便于打印输出的毛病。2、增减统计项目简便。可以根据本校实际情况对模板作修改，解决了一些成品软件中栏目、项目相对固定、呆板的局限。3、模板中所用函数、公式易于掌握，便于推广使用。