EXCEL 实训课件一  学生成绩表的制作
应用背景
学期结束时， 班主任王老师遇到了一个难题：教务处将各位任课教师给出的成绩表(每 张成绩表都是一个单独的 Excel 工作簿，见素材)全部交给了他，要求他据此得到“各科
成绩表”(图 1)及“成绩统计表” (图 2)，便于对全班的各科成绩进行分析统计。
图 1
图 2
起初， 他将所有的成绩表工作簿 (见素材) 全部打开， 企图把各科成绩粘贴到图 1 中， 结果他发现粘贴后的单元格中却显示了出错信息，他手忙脚乱地忙活了半天，图 1 的要求 都没有达到，就更别提完成图 2 的要求了。他只好硬着头皮一个一个去输入了，但这既容 易出错，工作量又很大。不得已他向教 Excel 的老师请教，经过老师的指点，他快速准确 地完成为了图 1 和图 2 两个工作表的制作，并顺利地对全班成绩进行了其他的分析统计工作， 下面是他的解决方案。

解决方案
首先建立一个新的 Excel 工作簿，并打开所有的成绩表工作簿(见素材)，将相应的 工作表复制到新建工作簿中。并按图 1 所示的要求，将内容复制到一个空白工作表中，特 别注意，在复制分数时，必须使用“选择性粘贴”或者引用其他工作表相应单元格的数据， 否则就会显示出错信息。至于得到图 2  的结果嘛，就必须使用各种函数(如： COUNT、 MAX、MIN、COUNTIF 以及 IF 等)来解决问题了！
制作过程
一、制作“计算机应用”成绩登记表
1．新建一个工作簿。将工作簿命名为“学号后 2 位+姓名.xls”
2．在工作表 Sheet1 中，输入图 3 所示的内容。
提示：学号的输入方式为输入数字前加之西文的 ’  将视为文本，或者将单元格的格式设置 为“文本”
图 3
3．将各列的宽度调整到最适合的位置(见图 3)。
4．将“第一次测试”、“第二次测试”栏目的栏目名进行自动换行。
5．将标题的字体设置为“黑体”，“18”号，粗体，并跨列居中。

6．表头单元格区域设置：行高(30)，水平、垂直居中，淡绿底纹。
7．表格数据区域单元格设置：字号(10)；垂直居中；学号内容左对齐，其他数据内 容水平居中。
8．表格边框设置(见图 3)。
9．计算：平时、第一次测试、第二次测试的平均成绩，并将结果放置在“平均成绩” 单元格中。
10．“计算机应用”成绩公式为：平时*0.2+第一次测试*0.45+第二次测试*0.35
11．  通过减少小数位或者 ROUND 函数，分别将“平均成绩”和“计算机应用”的结 果以整数位呈现。
12．将工作表“Sheet1”更名为“计算机应用”。
二、生成“各科成绩表”
1．参见样例“成绩表.xls”，分别打开“程序设计” 、“大学英语”、“企业管理”三个
工作簿，并将同名的工作表复制到“学号后2 位+姓名.xls”的工作簿中。
2．将四个工作表的罗列顺序调整为“计算机应用”、“程序设计”、“大学英语”、“企 业管理”。
3．选择工作表“Sheet2”，并将工作表更名为“各科成绩表”。
4．将“程序设计”工作表中“学号”、“姓名”、“性别”列的数据复制到“各科成绩 表”中的相应位置。
5．用选择性粘贴的方法分别将工作表 “计算机应用”、“程序设计”中的同名列粘贴 到“各科成绩表”中；用引用单元格的方法将“大学英语”、“企业管理”中的同名列引用 到“各科成绩表”中的对应位置。 (思量：你觉得这两种方法各有何特点？)
6．分别计算每人的总分和名次(用RANK 函数完成，注意相对引用与绝对引用的区 别)，并计算各科成绩的班级平均分(参见样例)要求四舍五入到小数点后两位。
三、生成“成绩统计表”
1. 在“学号后 2 位+姓名.xls”的工作簿中，选择工作表“Sheet3”，并将工作表更名 为“统计表”。参见样例“成绩表.xls”中的“统计表”。见图 4。

图 4
2．利用 COUNT 函数计算四门课程参加考试的人数。
提示： 必须引用“各科成绩表”相应区域的数据，如：“=COUNT(各科成绩表!D2:D38)”， 下
同。
3.分别利用 MAX 函数与 MIN 函数计算四门课程中的最高分数与最低分。
4.将“各科成绩表”工作表中四门课程的班级平均分引用到平均分行中。
提示：计算“计算机应用”课程的平均成绩：  =各科成绩表!D39。
5.用COUNTIF 函数分别统计出各门课程中各分数段的人数。
提示：   90-100 分数段：  =COUNTIF(各科成绩表!D2:D38,">=90"；
80-89  分 ：    =COUNTIF( 各 科 成 绩 表 !D2:D38,">=80")-COUNTIF( 各 科 成 绩 表!D2:D38,">=90")    或者      =COUNTIF(各科成绩表!D2:D38,">=80")-Bexcel中round函数的使用方法7；
70-79  分 ：    =COUNTIF( 各 科 成 绩 表 !D2:D38,">=70")-COUNTIF( 各 科 成 绩 表!D2:D38,">=80")或者=COUNTIF(各科成绩表!D2:D38,">=70")-B7-B8。
以此类推。
6.分别计算四门课程中的及格率。
完成以上实训的同学，还可以尝试使用FREQUENCY 函数的方法完成统计各门课程
中各分数段的人数。
提示：将“统计表”复制一份，并清除 B2:E11 单元格的所有内容。
FREQUENCY 函数的用法：
先在一个空白区域(如：  G7:G11)中分别输入如下分数间隔点“100 、89 、79、69、 59”，再选定目标单元格(如：  B7:B11)，然后选择 FREQUENCY 函数。

在 data_array 区域中，选定“各科成绩表”工作表中的 D2:D38 区域(“计算机应用” 的成绩) ；
在 bins_array 区域中，选定间隔点区域 G7:G11，单击“确定”按钮；
按  F2,再按  Ctrl+Shift+Enter。表示公式是以数组公式的形式输入的。
如果要计算其他课程各分数段的人数，应该如何使用 FREQUENCY 函数？
四、制作“成绩统计图”
利用图表向导绘制成绩统计图，见图 5 ，其中：
图 5
1 ．图表类型用：簇状柱形图；
2．数据系列产生在列；
3．图表标题：成绩统计图；分类(X)轴：等级；数值(Y)轴：人数；
4．显示数据表；
5 ．将图表作为新工作表插入，新工作表名称为：成绩统计图；
6．图表区域的填充效果用“麦浪滚滚”；
7．绘图区格式的填充效果用纹理“白大理石”；
8．图例格式的填充效果用“蓝面巾纸”。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。