《Office综合应用教程 教学课件 ppt 作者 李永平 涂金德 张浩 电子课件素材任务5制作成绩分析报告 任务5学生成绩分析报告》由会员分享，可在线阅读，更多相关《Office综合应用教程 教学课件 ppt 作者 李永平 涂金德 张浩 电子课件素材任务5制作成绩分析报告 任务5学生成绩分析报告（39页珍藏版）》请在金锄头文库上搜索。
1、任务5:学生成绩分析报告,学习目的: 1.掌握Excel中图表的操作方法 2.掌握Excel中IF函数的使用方法 知识要点: 1.Excel中筛选的使用方法 2.Excel中条件格式的使用方法 3.Excel中分类汇总的使用方法 技能要点： 1.图表分析 2.数据管理,任务5:学生成绩分析报告,教学重点： 图表操作 数据筛选 条件格式 分类汇总 教学难点： 数据管理和分析的意义,任务5 教学内容,一、任务综述： 教务员张三要根据李四的成绩统计结果写一份成绩分析报告，除了李四提供的基本统计数据外，在分析中还需要每门课程各分数段成绩分布图、全科合格（五门成绩都及格）的学生清单、有一门及以上课程成绩
2、不及格的学生清单、成绩不及格加红色标记和90分以上加绿色标记四方面的素材。,任务5 教学内容,二、任务实施 1.利用Excel图表功能，绘制每门课程各分数段成绩分布图，效果图如下所示。,任务5 教学内容,先打开包含所需统计信息的数据表 选择要进行数据分析的数据区域，横坐标、纵坐标 单击“常用”工具栏中的按钮，弹出如图5.1所示的“图表向导-4步骤之1-图表类型”对话框。选择“柱形图”，在“子图表类型”选项区域中选择“簇状柱形图”选项，然后单击“下一步”按钮 ，弹出如图5.2所示,任务5 教学内容,单击“系列”标签切换到“系列”选项卡，如图5.3所示。 在“系列”选项卡中单击“添加”按钮，在对应
3、的“名称”输入框中输入“社会“”，在“值”输入框中输入“=Sheet1!$D$143:$D$146”；同理，继续按以上操作步骤，添加其他4门课程的“名称”和“值”。在“分类（X）轴标志”输入框中输入“=Sheet1!$A$143:$A$146”后单击“下一步”按钮，弹出如图5.4所示,任务5教学内容,单击“标题”标签切换到“标题”选项卡，如图5.5所示。 在“标题”选项卡内根据提示，输入图表标题内容、分类（X）轴内容、数值（Y）轴内容，然后单击“下一步”按钮，弹出如图5.6所示 。 单击“完成”按钮，即可完成本任务所需的“分数段人数分布柱形图”。,任务5 实践内容（1）,要求： 根据任务4提供
4、的数据表-成绩分析表.xls中已经划分的各分数段学生人数，要求进行如下操作：,思考： 如果用课程名称作为横坐标，分数段划分作为图例，那么应该如何操作？,任务5教学内容,2制作全科成绩合格与至少有一门课程成绩不及格的学生清单（方法1）,提示：筛选出全科成绩都合格的学生，剩余的学生就是至少有一门课程成绩不及格的学生。,建议：为了不破坏原始数据，建议不要直接在“Sheet1”工作表中进行，而在另一张工作表“Sheet2”中进行操作。 完成两表间的有效数据复制,任务5教学内容,在备份的“Sheet2”工作表中，单击单元格A3，执行菜单栏中的“数据”“筛选”“自动筛选”命令，在表栏目中出现“自动筛选”后
5、的工作表，如图5.8所示。,任务5教学内容,单击下拉列表中的“自定义”选项，弹出“自定义自动筛选方式”对话框，如图5.9所示。,在“自定义自动筛选方式”对话框中，通过单击左边输入框的下拉按钮，在下拉列表中选择“大于或等于”，再在右边输入框中输入“60”，单击“确定”按钮，然后将其余四门课程对应的筛选用同样方式输入完毕 。,任务5教学内容,观察： 注意最左侧的行号，颜色是蓝色的，而且行号是不连续的，说明这已经是筛选的结果。,任务5 教学内容,筛选出来的记录建议放在另一张表中。选定经筛选后的记录和表头，单击“复制”按钮，切换到“Sheet3”工作表，在单元格A1处单击“粘贴”按钮，就将五门课程都及
6、格的学生清单复制了过来。然后修改表头内容为“学校初三段课程全部及格学生一览表”，如图5.11所示。,任务5 教学内容,回到上述的“Sheet2”工作表中，选定筛选后的数据行（不包括表头和表标题），也就是五门课程都及格的学生数据行，执行菜单栏中的“编辑”“删除行”命令，此时会发现表中除了表头和表标题外，没有数据行了，接下来执行菜单栏中的“数据”“筛选”“自动筛选”命令，即可恢复至正常数据状态。此时，数据表上的内容就是至少有一门课程不及格的学生记录。,思考： 分析“学校初三段课程全部及格学生一览表”和“学校初三段至少有一门不及格学生一览表”中的班名次和段名次，是否有别？原因在哪里？,任务5 教学内
7、容,3制作全科成绩合格与至少有一门课程成绩不及格的学生清单（方法2）,方法1采用了“自动筛选”功能 方法2采用“高级筛选”功能,执行菜单栏中的“数据”“筛选”“高级筛选”命令，在空的区域创建一个条件区域。比如选定第149行，将“D3:H3”内容复制，在单元格D149，单击“粘贴”按钮，在149行对应的课程下输入“60”内容，并相互错开行数。,任务5教学内容,执行菜单栏中的“数据”“筛选”“高级筛选”命令，在弹出的“高级筛选”对话框中将“方式”设置为“将筛选结果复制到其他位置”，“列表区域”选择“A3:L139”，“条件区域”选择刚刚建立的“D149:H154”，“复制到”设置为“A160”，“
8、A160”即筛选结果的起始单元格位置，如图5.14所示。,单击“确定”按钮后，就会以“A160”为起始单元格显示满足“至少有一门课程不及格”筛选条件的结果。,任务5教学内容,如果要高级筛选全科成绩都合格的学生，只要将条件格式设为“与”的方式，即在一行中设置条件即可，如图5.15所示。,多种筛选条件同时成立的情况,与,或,多种筛选条件满足其一成立的情况,各筛选条件 并排罗列,各筛选条件 错行罗列,多种筛选条件同时成立的情况,与,或,各筛选条件 并排罗列,各筛选条件 错行罗列,任务5 实践内容（2）,要求： 根据提供的数据表-成绩分析表.xls分别进行自动筛选和高级筛选，求得全科成绩合格与至少有一
9、门课程成绩不及格的学生清单。 用自动筛选功能和高级筛选功能求得所有成绩都不及格的学生清单。 筛选总分小于300分的学生清单。,思考： 如果只要求筛选出语文或数学或英语这三门课有一门以上不及格的学生清单应该如何操作？,任务5教学内容,4将成绩不及格用红色标注，90分（包括90分）以上用绿色标注。,成绩管理中为了清楚地分辨学生成绩的高低，于是用不同颜色给不及格者和90分以上者进行区分，利用“条件格式”就可以轻松完成。,先复制一份有效数据到一张空表中，选定单元格区域D4:H139，执行菜单栏中的“格式”“条件格式”命令，系统弹出“条件格式”对话框，根据成绩管理的要求在“条件1”中设置“单元格数值小于
10、60”，单击“格式”按钮，弹出“单元格格式”对话框，在“颜色”位置选择“红色”，在“字形”位置选择“加粗”，单击“确定”按钮回到“条件格式”对话框，完成对不及格成绩的条件设定。,任务5教学内容,单击“添加”按钮，出现“条件2”，继续在“条件2”中设置“单元格数值大于或等于90”，单击“格式”按钮，弹出“单元格格式”对话框，在“颜色”位置选择“绿色”，在“字形”位置选择“加粗”，单击“确定”按钮回到“条件格式”对话框，完成对90分以上成绩的条件设定，如图5.17所示。,再单击“确定”按钮，即可在“Sheet3”工作表中，将选定单元格区域D4:H139内的数据按不及格分数用红色粗体显示，90分（包
11、括90分）以上用绿色粗体显示。,任务5 实践内容（3）,要求： 根据提供的数据表-成绩分析表.xls进行“条件格式” 设定，90分以上绿色，75分-90之间蓝色，60分以下红色。,思考： 如何清除已设置的条件格式？,任务5教学内容,5各班各门课程平均值统计,要求对“Sheet1”工作表中的数据进行分类汇总，求出初三（1）、初三（2）、初三（3）三个班级社会、语文、数学、科学、英语五门课程的班级平均分。,小技巧：用鼠标单击任一数据单元格，按Ctrl+Shift+8组合键，即可选中整个数据清单,选中整个数据清单，然后执行菜单栏中的“数据”“分类汇总”命令，系统弹出“分类汇总”对话框，如图5.18所
12、示,按照要求需各个班级进行如下设置：“分类字段”设置为“班级”；“汇总方式”设置为“平均值”；“选定汇总项”列表框中勾选社会、语文、数学、科学、英语五门课程对应的复选框，如图5.19所示,任务5教学内容,任务5教学内容,单击“确定”按钮，即可求出初三（1）、初三（2）、初三（3）各个班级，社会、语文、数学、科学、英语五门课程的班级平均分，结果如图5.20所示。,任务5 实践内容（4）,要求： 根据提供的数据表-成绩分析表.xls进行分类汇总，求出初三（1）、初三（2）、初三（3）三个班级社会、语文、数学、科学、英语五门课程的班级平均分。 根据提供的数据表-成绩分析表.xls进行分类汇总，分别求
13、出初三（1）、初三（2）、初三（3）三个班级社会、语文、数学、科学、英语五门课程的班级最高分和最低分。,思考： 如何清除已设置的分类汇总？,任务5教学内容,6将每位学生的平均分成绩分等级,按学校要求对初三年级的三个班级进行平均分成绩分等级统计管理，规定将学生平均分的百分制转换成不同的等级。有多种不同的划分方法，这里要求在原数据清单中增加“M3”单元格并输入“等级”，将J列学生的平均分从百分制的分数转换成M列的五个等级 A（90100分） B（8089分） C（7079分） D（6069分） E（低于60分）,解决这类分级问题用逻辑判断函数IF即可,任务5教学内容,如果简单地将成绩分成两个等级A
14、（=60）和B（=60,”A“,”B“)”即可。 如果分成多个等级，只要将逻辑判断的条件进行嵌套，在M4中输入“=IF(J4=90,“A“,IF(J4=80,“B“,IF(J4=70,“C“,IF(J4=60,“D“,“E“)”。,任务5教学内容,任务5 实践内容（5）,要求： 根据提供的数据表-成绩分析表.xls中的平均分进行等级划分，将学生平均分的百分制转换成不同的等级。,思考： 如何在嵌套多IF函数的时候不易出错？,任务5讨论内容,1.Excel图表主要包含哪 几个要素? 2.自动筛选与高级筛选哪个灵活度高? 3.If函数嵌套,嵌套函数应该是“是”还是“否”处？ 4.分类汇总的前提是什么
15、?,任务5 总结（一）,Excel具有强大的图表自动生成功能，生成图表的操作操作步骤如下： 1）选择图表类型及子类。 2）选择图表源数据。 3）“标题”、“坐标轴”、“图例”、“数据标志”、“数据表”等图表选项的设置。 4）图表位置的确定，即图表是放在原工作表中，还是放在新工作表中。,任务5 总结（二）,在Excel中对数据筛选可使用“自动筛选”与“高级筛选”两种方法。 “自动筛选”可对某列设置单一筛选条件，也可以用“与”关系或“或”关系设定条件；多列设置，其列与列条件成“与”的关系。 “高级筛选”功能，要先设置条件区域，条件区域由标题行和条件行组成。,任务5 总结（三）,“条件格式”要做两项
16、操作： 一是设定条件（表达式）； 二是设定格式（字体、字号、颜色、下划线等）。 该功能操作的结果是将满足条件的数据用指定的格式显示，如不及格者用“红色”标注，以示醒目。,任务5 总结（四）,使用“分类汇总”功能时，在操作前必须要先对关键字段进行排序，不然的话，结果没有任何意义。分类汇总的结果有以下三种显示形式： 1）明细记录与分类汇总值。 2）分类汇总值。 3）总汇总值。,任务5 总结（五）,IF函数作为逻辑判断用途很广，简单的IF语句可通过对话框进行设置。但大多数的统计需要多重嵌套，因此在编写公式的时候，先分成两段式，再将后一段继续用IF函数展开，最后一定要检查括号的使用是否正确与配套，否则有可能导致结果错误或弹出非法公式的错误信息对话框。,任务5 实战训练,建立如图5.24所示的员工工资表。,任务5 实战训练,1）在D1单元格中填写等级，用IF函数填写D列单元格中的级别，规定“高工”为“A”，“工程师”为“
