班级成绩统计与分析

长清区教师培训模块五

班级成绩统计与分析

长清实验中学王长泉

●主要内容：

以任务驱动的方式，通过完善“学生成绩表”中的数据，并计算出学生的总分、平均分，统计出优秀率、及格率、优秀人数、及格人数、最高分和最低分，给学生排出名次，并以等级方式显示。掌握利用EXCEL进行班级成绩统计与分析的方法。

●快速操作技巧

1、快速得到合适列宽和行高

如果各列中数据宽度一致的话，我们可以选中各列，然后把鼠标移至列标题的右边框，待鼠标变成双向箭头时拖动至合适的宽度，这样就可以得到统一列宽的各列了。如果各列中数据宽度不一致的话，我们经常要逐列进行调整，非常麻烦。此时可以选中含有数据的各列，同样将鼠标移至其中一个列标题的右边框，当鼠标指针变成双向箭头时双击鼠标左键，可以立刻将各列的列宽设置为“最适合的列宽”，即各列宽度为恰好可以完整显示单元格数据，用这个方法也可以得到“最适合的行高”，只是要双击行标题的下边框。

2、快速移动活动单元格

经常要在数据区域的最开始和最末尾处、最左端和最右端处来回折腾吗？其实，用不着多次单击滚动条，也不用多次滚动鼠标的滚轮的。只要将鼠标指向当前单元格的下边框，当鼠标指针变为四个箭头的十字形时双击，就可以使活动单元格迅速定位于当前数据区域中当前列的最后一行。双击单元格的上边框可以到达最上一行，而双击左边框和右边框则可以分别迅速到达最左端和最右端。

3、快速选中单元格区域

选中单元格区域快速的方法是利用名称框。如果我们清楚地知道应该选择哪些区域，那么直接在编辑栏左侧的“名称框”内填写选择区域是最简便的了。比如我们要同时选中A2:D15单元格区域和H列，那么就可以在名称框内直接输入“A2:D15,H:H”，按下回车键，则这些区域就被直接选中了。

4、快速拆分窗口

为方便查看表格的不同部分，我们经常要对工作表进行垂直或水平拆分。快速拆

分的方法是：先在需要拆分的位置单击鼠标，激活活动单元格，然后将鼠标指向垂直滚动条顶端的拆分框，待鼠标指针变成拆分指针时，双击鼠标，那么窗口就会在活动单元格上方将窗口水平拆分。再次双击拆分框，则拆分取消。

如果双击水平滚动条右端的拆分框，则会在活动单元格的左侧进行垂直拆分。

5、冻结窗格

如果列数较多，行数也较多时，一旦向下滚屏，则上面的标题行也跟着滚动，在处理数据时往往难以分清各列数据对应的标题，事实上利用"冻结窗格"功能可以很好地解决这一问题。具体方法是将光标定位在要冻结的标题行(可以是一行或多行)的下一行，然后选择“窗口|冻结窗格”即可。滚屏时，被冻结的标题行总是显示在最上面，大大增强了表格编辑的直观性。

6、快速输入相同内容

如果需要在不同工作表的同一单元格区域输入相同内容，我们可以先同时选中那些工作表，然后选择需要输入内容的单元格区域，在区域的左上角单元格输入内容，然后按下“Ctrl+ Enter”快捷键，那么所有选中的工作表中的单元格区域都会自动填充相同的内容。如果需要在不同工作表的同一单元格输入相同的公式，也可以采用这个办法。

实例操作

1、学号的输入

由于学号是以0开头的所以不能直接输入，可采用以下两种方法：

（1）在A2单元格中首先输入“’”（即单引号，但必须在英文状态下输入），再输入学号，然后点回车，利用填充句柄填充其他学号。如下图所示：

（2）将A列的单元格格式设为文本型，然后再输入学号。

选定A列→格式→单元格→文本，如下图所示

2、计算总分

（1）利用公式

在H2中输入公式“＝C2+D2+E2+F2+G2”，然后复制填充。如下图：

（2）利用函数

首先选中H2单元格，点击常用工具栏上的∑按钮，然后复制填充。如下图：

2、计算平均分

（1）利用公式

选中I2单元格，输入公式“＝H2/5”，向下复制填充。

（2）利用函数

选中I2单元格，点击常用工具栏上的∑按钮右侧的箭头，选择平均值，如下图

选择计算范围C2至G2, 向下复制填充。

3、统计名次

（1）利用“筛选”

这个方法并不单单适用于老师统计成绩，很多的数据表都可以用到这个。先要指定需要筛选的单元格，我们用鼠标点选H1“总分”这个单元格。接着在“数据”菜单中找到“筛选”下的“自动筛选”，点击一下。现在“考号”那一行的“姓名”、科目等文字右边都出现了一个小的下拉菜单按钮。按下这些小按钮，我们就可以任意选择排序啦！现在我们需要根据“总分”的高低来排序，按下“总分”右边的按钮。因为我们需要把分数高的排在前面，所以在下拉菜单中选择“降序”。

在名次列中从上到下填充序号就可以啦！Excel有聪明的填充柄，写好1、2，后面的直接用鼠标往下一拉就好啦！

如果老师还需要恢复表格按考号排序，按一下“考号”旁边的箭头按钮，选择“升序”就可以了。自然，老师也可以用这种方法随时查看各个科目的成绩排名。提醒：用“筛选”方法做的排序，“名次”是固定写死的，如果有成绩发生改变，名次是不能跟着改变的。所以，这种方式适用于不需要修改的成绩表。如果成绩发生改变，就需要重新排序，重新定位名次。

（2）利用RANK函数

首先介绍一下RANK函数，函数语法是“RANK(number,ref,order)”。其中，第一个参数“number”是需要找到排位的数字，第二个参数“ref”

则是数字列表的引用，第三个参数如果非零则是按升序排

列，如果省略则是按降序排列，这正是我们需要的。比如公

式“=RANK(A3,A2:A6)”的含义就是得到A3单元格数据在

A2:A6单元格数据中的排名。

现在我们在名次下面的J2单元格中填写函数“=RANK(H2,$H$2:$H$51)”，告诉这个单元格需要显示的结果是H2这个单元格的数值（在从H2到H51的数值（所有同学的总分）中所处的排序位置。（计算范围要用绝对引用，不然公式复制后要出错），向下复制公式，这样得到的数字就是老师需要的“名次”了。

4、划分等级

在K2单元格中输入“=IF(I2>=80,"A",IF(I2>=70,"B",IF(I2>=60,"C","D")))”（说明：平均分>=80为A，>=70为B，>=60为C，60以下为D）然后把鼠标指针指向K22单元格的右下角，等鼠标指针变成黑色十字加号时，按住左键向右拖动到这列单元格的最后放手。

5、人数统计

（1）及格人数统计

COUNTIF函数可以统计某一区域中符合条件的单元格数目，它的语法为COUNTIF （range，criteria）。其中range为参与统计的单元格区域，criteria是以数字、表达式或文本形式定义的条件。其中数字可以直接写入，表达式和文本必须加引号。

在C52中输入“=COUNTIF(C2:C51,">=60")”说明：统计语文>=60分的人数。向右填充可统计出其他科目的及格人数。

（2）优秀人数统计

在C53中输入“=COUNTIF(C2:C51,">=85")”说明：统计语文>=85分的人数。向右填充可统计出其他科目的优秀人数。

6、及格率、优秀率的统计

在C54中输入公式“=COUNTIF(C2:C51,">=60")/COUNT(C2:C51)”

说明：就是用及格人数除以总人数，=COUNTIF(C2:C51,">=60")为统计及格人数，COUNT(C2:C51)为统计全班人数。

向右填充可统计出其他科目的及格率。

同样统计优秀率，只不过要将条件改为>=85(设定>=85为优秀)