学生成绩分析报告

学生成绩分析报告Office应用:学生成绩分析报告学习目的:1.掌握Excel中图表的操作方法

2.掌握Excel中IF函数的使用方法知识要点:1.Excel中筛选的使用方法

2.Excel中条件格式的使用方法

3.Excel中分类汇总的使用方法技能要点：

1.图表分析

2.数据管理:学生成绩分析报告教学重点：

图表操作数据筛选条件格式分类汇总教学难点：

数据管理和分析的意义

教学内容一、任务综述：教务员张三要根据李四的成绩统计结果写一份成绩分析报告，除了李四提供的基本统计数据外，在分析中还需要每门课程各分数段成绩分布图、全科合格（五门成绩都及格）的学生清单、有一门及以上课程成绩不及格的学生清单、成绩不及格加红色标记和90分以上加绿色标记四方面的素材。

教学内容二、任务实施1.利用Excel图表功能，绘制每门课程各分数段成绩分布图，效果图如下所示。

教学内容①先打开包含所需统计信息的数据表②选择要进行数据分析的数据区域，横坐标、纵坐标③单击“常用”工具栏中的按钮，弹出如图5.1所示的“图表向导-4步骤之1-图表类型”对话框。选择“柱形图”，在“子图表类型”选项区域中选择“簇状柱形图”选项，然后单击“下一步”按钮，弹出如图5.2所示

教学内容

④单击“系列”标签切换到“系列”选项卡，如图5.3所示。⑤在“系列”选项卡中单击“添加”按钮，在对应的“名称”输入框中输入“＝"社会"”，在“值”输入框中输入“=Sheet1!$D$143:$D$146”；同理，继续按以上操作步骤，添加其他4门课程的“名称”和“值”。在“分类（X）轴标志”输入框中输入“=Sheet1!$A$143:$A$146”后单击“下一步”按钮，弹出如图5.4所示教学内容⑥单击“标题”标签切换到“标题”选项卡，如图5.5所示。⑦在“标题”选项卡内根据提示，输入图表标题内容、分类（X）轴内容、数值（Y）轴内容，然后单击“下一步”按钮，弹出如图5.6所示。⑧单击“完成”按钮，即可完成本任务所需的“分数段人数分布柱形图”。

实践内容（1）要求：

①根据任务4提供的数据表-成绩分析表.xls中已经划分的各分数段学生人数，要求进行如下操作：思考：

如果用课程名称作为横坐标，分数段划分作为图例，那么应该如何操作？教学内容2．制作全科成绩合格与至少有一门课程成绩不及格的学生清单（方法1）

提示：筛选出全科成绩都合格的学生，剩余的学生就是至少有一门课程成绩不及格的学生。

建议：为了不破坏原始数据，建议不要直接在“Sheet1”工作表中进行，而在另一张工作表“Sheet2”中进行操作。

完成两表间的有效数据复制教学内容①在备份的“Sheet2”工作表中，单击单元格A3，执行菜单栏中的“数据”→“筛选”→“自动筛选”命令，在表栏目中出现“自动筛选”后的工作表，如图5.8所示。教学内容②单击下拉列表中的“自定义”选项，弹出“自定义自动筛选方式”对话框，如图5.9所示。③在“自定义自动筛选方式”对话框中，通过单击左边输入框的下拉按钮，在下拉列表中选择“大于或等于”，再在右边输入框中输入“60”，单击“确定”按钮，然后将其余四门课程对应的筛选用同样方式输入完毕。教学内容观察：注意最左侧的行号，颜色是蓝色的，而且行号是不连续的，说明这已经是筛选的结果。

教学内容④筛选出来的记录建议放在另一张表中。选定经筛选后的记录和表头，单击“复制”按钮，切换到“Sheet3”工作表，在单元格A1处单击“粘贴”按钮，就将五门课程都及格的学生清单复制了过来。然后修改表头内容为“××学校初三段课程全部及格学生一览表”，如图5.11所示。

教学内容⑤回到上述的“Sheet2”工作表中，选定筛选后的数据行（不包括表头和表标题），也就是五门课程都及格的学生数据行，执行菜单栏中的“编辑”→“删除行”命令，此时会发现表中除了表头和表标题外，没有数据行了，接下来执行菜单栏中的“数据”→“筛选”→“自动筛选”命令，即可恢复至正常数据状态。此时，数据表上的内容就是至少有一门课程不及格的学生记录。思考：

分析“××学校初三段课程全部及格学生一览表”和“××学校初三段至少有一门不及格学生一览表”中的班名次和段名次，是否有别？原因在哪里？

教学内容3．制作全科成绩合格与至少有一门课程成绩不及格的学生清单（方法2）方法1采用了“自动筛选”功能

方法2采用“高级筛选”功能①执行菜单栏中的“数据”→“筛选”→“高级筛选”命令，在空的区域创建一个条件区域。比如选定第149行，将“D3:H3”内容复制，在单元格D149，单击“粘贴”按钮，在149行对应的课程下输入“<60”内容，并相互错开行数。教学内容②执行菜单栏中的“数据”→“筛选”→“高级筛选”命令，在弹出的“高级筛选”对话框中将“方式”设置为“将筛选结果复制到其他位置”，“列表区域”选择“A3:L139”，“条件区域”选择刚刚建立的“D149:H154”，“复制到”设置为“A160”，“A160”即筛选结果的起始单元格位置，如图5.14所示。③单击“确定”按钮后，就会以“A160”为起始单元格显示满足“至少有一门课程不及格”筛选条件的结果。教学内容

如果要高级筛选全科成绩都合格的学生，只要将条件格式设为“与”的方式，即在一行中设置条件即可，如图5.15所示。

多种筛选条件同时成立的情况与或

多种筛选条件满足其一成立的情况

各筛选条件

并排罗列

各筛选条件错行罗列

多种筛选条件同时成立的情况与或

各筛选条件

并排罗列

各筛选条件错行罗列

多种筛选条件满足其一成立的情况

多种筛选条件同时成立的情况与或

各筛选条件

并排罗列

各筛选条件错行罗列

实践内容（2）要求：

①根据提供的数据表-成绩分析表.xls分别进行自动筛选和高级筛选，求得全科成绩合格与至少有一门课程成绩不及格的学生清单。

②用自动筛选功能和高级筛选功能求得所有成绩都不及格的学生清单。

③筛选总分小于300分的学生清单。思考：

如果只要求筛选出语文或数学或英语这三门课有一门以上不及格的学生清单应该如何操作？教学内容4．将成绩不及格用红色标注，90分（包括90分）以上用绿色标注。

成绩管理中为了清楚地分辨学生成绩的高低，于是用不同颜色给不及格者和90分以上者进行区分，利用“条件格式”就可以轻松完成。①先复制一份有效数据到一张空表中，选定单元格区域D4:H139，执行菜单栏中的“格式”→“条件格式”命令，系统弹出“条件格式”对话框，根据成绩管理的要求在“条件1”中设置“单元格数值小于60”，单击“格式”按钮，弹出“单元格格式”对话框，在“颜色”位置选择“红色”，在“字形”位置选择“加粗”，单击“确定”按钮回到“条件格式”对话框，完成对不及格成绩的条件设定。教学内容②单击“添加”按钮，出现“条件2”，继续在“条件2”中设置“单元格数值大于或等于90”，单击“格式”按钮，弹出“单元格格式”对话框，在“颜色”位置选择“绿色”，在“字形”位置选择“加粗”，单击“确定”按钮回到“条件格式”对话框，完成对90分以上成绩的条件设定，如图5.17所示。③再单击“确定”按钮，即可在“Sheet3”工作表中，将选定单元格区域D4:H139内的数据按不及格分数用红色粗体显示，90分（包括90分）以上用绿色粗体显示。

实践内容（3）要求：

①根据提供的数据表-成绩分析表.xls进行“条件格式”

设定，90分以上绿色，75分-90之间蓝色，60分以下红色。思考：

如何清除已设置的条件格式？教学内容5．各班各门课程平均值统计

要求对“Sheet1”工作表中的数据进行分类汇总，求出初三（1）、初三（2）、初三（3）三个班级社会、语文、数学、科学、英语五门课程的班级平均分。小技巧：用鼠标单击任一数据单元格，按Ctrl+Shift+8组合键，即可选中整个数据清单①选中整个数据清单，然后执行菜单栏中的“数据”→“分类汇总”命令，系统弹出“分类汇总”对话框，如图5.18所示

②按照要求需各个班级进行如下设置：“分类字段”设置为“班级”；“汇总方式”设置为“平均值”；“选定汇总项”列表框中勾选社会、语文、数学、科学、英语五门课程对应的复选框，如图5.19所示

教学内容教学内容③单击“确定”按钮，即可求出初三（1）、初三（2）、初三（3）各个班级，社会、语文、数学、科学、英语五门课程的班级平均分，结果如图5.20所示。

实践内容（4）要求：

①根据提供的数据表-成绩分析表.xls进行分类汇总，求出初三（1）、初三（2）、初三（3）三个班级社会、语文、数学、科学、英语五门课程的班级平均分。

②根据提供的数据表-成绩分析表.xls进行分类汇总，分别求出初三（1）、初三（2）、初三（3）三个班级社会、语文、数学、科学、英语五门课程的班级最高分和最低分。思考：

如何清除已设置的分类汇总？教学内容6．将每位学生的平均分成绩分等级

按学校要求对初三年级的三个班级进行平均分成绩分等级统计管理，规定将学生平均分的百分制转换成不同的等级。有多种不同的划分方法，这里要求在原数据清单中增加“M3”单元格并输入“等级”，将J列学生的平均分从百分制的分数转换成M列的五个等级A（90～100分）B（80～89分）C（70～79分）D（60～69分）E（低于60分）

解决这类分级问题用逻辑判断函数IF即可教学内容

如果简单地将成绩分成两个等级A（>=60）和B（<60）的话，只需要在指定的单元格如M4中输入

“=IF(J4>=60,”A“,”B“)”即可。如果分成多个等级，只要将逻辑判断的条件进行嵌套，在M4中输入“=IF(J4>=90,"A",IF(J4>=80,"B",IF(J4>=70,"C",IF(J4>=60,"D","E"))))”。教学内容

实践内容（5）要求：

①根据提供的数据表-成绩分析表.xls中的平均分进行等级划分，将学生平均分的百分制转换成不同的等级。思考：

如何在嵌套多IF函数的时候不易出错？讨论内容1.Excel图表主要包含哪几个要素?2.自动筛选与高级筛选哪个灵活度高?3.If函数嵌套,嵌套函数应该是“是”还是“否”处？4.分类汇总的前提是什么?

总结（一）Excel具有强大的图表自动生成功能，生成图表的操作操作步骤如下：1）选择图表类型及子类。2）选择图表源数据。3）“标题”、“坐标轴”、“图例”、“数据标志”、“数据表”等图表选项的设置。4）图表位置的确定，即图表是放在原工作表中，还是放在新工作表中。

总结（二）

在Excel中对数据筛选可使用“自动筛选”与“高级筛选”两种方法。

“自动筛选”可对某列设置单一筛选条件，也可以用“与”关系或“或”关系设定条件；多列设置，其列与列条件成“与”的关系。

“高级筛选”功能，要先设置条件区域，条件区域由标题行和条件行组成。

总结（三）

“条件格式”要做两项操作：一是设定条件（表达式）；二是设定格式（字体、字号、颜色、下划线等）。

该功能操作的结果是将满足条件的数据用指定的格式显示，如不及格者用“红色”标注，以示醒目