任务5学生成绩分析报告

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