Excel综合应用1—成绩表统计与分析

1、Excel 综合应用 1成绩表统计 与分析河南质量工程职业学院教案首页第 7 章 节 需 4 学时审阅人授课课题第 7 章 Excel 综合应用 1 成绩表统计与分析学时4授课时间2008 年 11 月 5 日 星期 三 第 1 、2 节知识目标统计函数 COUNT 、COUNTIF 和逻辑判断函数 IF 的使用 方法，条件格式的设置方法，图表的创建、修改能力目标熟练掌握统计函数 COUNT 、COUNTIF 的使用方法，熟 练掌握逻辑判断函数 IF 的使用方法，掌握条件格式的设置方 法，熟练使用图表的创建、修改素质 教育 目标穿插上机，提高学生的动手能力教学重点统计函数 COUNT 、COU

2、NTIF 和逻辑判断函数 IF 的使用 方法教学难点条件格式的设置方法授课方法多媒体教学仪器投影仪，多媒体电脑教学反馈第 7 2 页第 7 章 Excel 综合应用 1 成绩表统计与分析7.1 成绩表统计案例分析 任务的提出 班主任王老师要制作“成绩统计表” 、“成 绩等级表”及“成绩统计图。解决方案7.2.1 用统计函数与公式制作“成绩统计表” 跨工作表的单元格引用打开“第 7 章素材”文件夹下的“统计表 （素材） .xls”文件，选择“成绩统计表” 工作表。从“各科成绩表”中，将四门课程的“班 级平均分”、“班级最高分”和“班级最低 分”的数据引用到如右图所示的“成绩统 计表”中的相应单元

3、格中。统计函数 COUNTA 及 COUNT 的使用 将“各科成绩表”中各门课程的“应考人 数”和“参考人数” 的统计结果放置到 “成 绩统计表”中的相应单元格中。 在“插入函数”对话框中，选择“统计”， 在“选择函数”列表框中，选择 “COUNTA ”函数，单击“确定”按钮。 打开“函数参数”对话框。进行如下图 所示的设置。“COUNT ”函数的设置类似(略) 。 条件统计函数 COUNTIF 的功能COUNTIF 函数的功能： 统计指定区域内满足给定条件的单元格数 目。语法格式：COUNTIF(range,criteria)其中：Range 指定单元格区域， Criteria 表示指定的条

4、件表达式。条件表达式的形式可以为数字、表达式或文本。例如，条件可以表示为 60、60 、=90 或 缺考 等用 COUNTIF 函数，将“各科成绩表” 中各门课程的缺考人数以及各分数段人 数的统计结果放置到 “成绩统计表” 中的 相应单元格 。在“函数参数”对话框中，进行如下图 所示的设置：编辑栏中的函数为 =COUNTIF（ 各科成绩表 !D2:D38, 缺考 ）。各分数段人数的统计条件对应函数90-100（人）=COUNTIF（ 各 科 成 绩 表!D2:D38,=9080-89（人）=COUNTIF（ 各 科 成 绩 表 !D2:D38,=80）- B970-79（人）=COUNTIF（

5、 各 科 成 绩 表!D2:D38,=70）-B9-B1060-69（人）=COUNTIF（ 各 科 成 绩第 7 6 页表!D2:D38,=60）-B9-B10-B11=COUNTIF（ 各 科 成 绩 表!D2:D38,=60）/COUNT（ 各 科 成绩表 !D$2:D$38） 或： 1-B13/B7计算优秀率：=COUNTIF（ 各 科 成 绩 表 !D$2:D$38,=90）/COUNT（ 各 科 成绩表 !D$2:D$38）或： B9/B7思考题（一） 如何引用同一工作簿中不同工作表的数 据？ 引用单元格的数据与复制粘贴数据有何 区别？ 统计函数 COUNT 与 COUNTA 的区

6、别是 什么？ 条件统计函数 COUNTIF 的应用场合是什 么？其中的两个参数各代表什么？ 在“各科成绩表 ”工作表中分别统计出男、 女生人数应该用什么函数？试写出函数的 表达式。单元格数据的删除与清除 将“各科成绩表”工作表复制一份，并将 复制后的工作表更名为“各科等级表” 。在 “各科等级表” 中，清除“大学英语”、“计 算机应用”、“高等数学”及“应用文写作” 列中的分数内容，并清除“总分” 、“名次” 列的所有属性；删除“班级平均分” 、“班 级最高分”、“班级最低分”所在单元格区域。7.2.2 用 IF 函数与条件格式制作 “各科等级表” 逻辑判断函数 IF 的功能IF 函数的功能是

7、：判断给出的条件是否满足，如果满足返 回一个值，如果不满足则返回另一个值。 语法格式：IF(logical_test,value_if_true,value_if_false)共包括三个参数，其中：Logical_test 逻辑判断表达式； value_if_true 表 达式 为 真 时 返 回 的 值；value_if_false 表达式为假时返回的值。利用 If 函数对“各科成绩表”中的“应用文写作”成绩在 60 分以上的，在“各科等 级表”中“应用文写作”的对应位置设置 为“及格”，否则为“不及格” 。选择目标单元格 G2 ，在“函数参数” 对话框中进行如下图所示的设置。按下表所示的分

8、数与等级的对应关系，利 用 If 嵌套函数对“各科成绩表”中的“大 学英语”、“计算机应用”、“高等数学”三 门课程的分数，在“各科等级表”中的对 应科目中进行相应的等级设置 。分数等级第 7 10 页缺考缺考90=分数A80= 分 数 90B70= 分 数 80C60= 分 数 70D分数=90优秀90分 数=80良好80分 数=70中等70分 数=60及格分数=90, 优 秀 ,if(90A2=80, 良 好 ,if(80A2=70, 中 等 ,if(70A2=60, 及 格,if(A260,不及格 ,false)第 7 14 页7.2.3用图表向导制作成绩统计图 制作各科成绩统计图 图表

9、比数据更易于表达数据之间的关系以及数 据变化的趋势。 “成绩统计图 ”可以直观地表现 各门课程各分数段人数。使用“图表向导 ”创建图表在“成绩统计表 ”中，根据各分数段人数及 缺考人数制作图表。要求如下： 图表类型为“簇状柱形图” ； 数据系列产生在“列” ；第 7 15 页图表标题为“成绩统计图” ；分类（ X）轴为“等级”；数值（ Y）轴 为“人数”；将图表“作为其中的对象插入”到“成 绩统计表”中 。选择图表类型在菜单栏中选择 “插入 ”“图表”命令，打 开 “图表向导 -4 步骤之 1-图表类型 ”对话 框，进行如右图所示的设置。单击“下一步 ”按钮第 7 16 页选择源数据按住 Ct

10、rl 键的同时，分别在当前工作表中 选择不连续的两个区域，如下图所示，并 在“图表向导 -4 步骤之 2-图表源数据 ”对话 框中，显示相应的设置。第 7 17 页设置图表选项单击“下一步 ”按钮，打开 “图表向导 -4 步 骤之 3-图表选项 ”对话框，并进行如下图所 示的设置。第 7 18 页选择图表位置“下一步”按钮，打开 “图表向导 -4 步骤之 4-图表位置 ”对话框， 进行如下图所示的 设置。修改图表 在图表的制作过程中四步中的任何一步 出错，都不必从头开始，只需单击图表， 使图表处于激活状态，从 “图表 ”菜单中， 选择对应的命令即可；也可单击图表后， 单击鼠标右键，从快捷菜单中

11、，选择对应 的命令。修改图表要求第 7 19 页在 “成绩统计表 ”工作表中，对图表进行如 下修改：将图表类型改为“三维簇状柱形图” ； 数据系列产生在“行” ，并从图表中删 除缺考人数；在图表中“显示数据表” ；将图表“作为新工作表插入” ，并将新 工作表命名为“成绩统计图” 。 操作步骤略图表的组成第 7 20 页格式化图表在 “成绩统计图 ”工作表中，对图表的外观 进行如下修饰：将图表标题设置为 “幼圆、22号、蓝色、 加粗”；将图表区的填充效果设置为“羊皮纸” ； 将背景墙的填充效果设置为“水滴” ； 为图例添加“阴影边框” 。设置图表区的填充效果在 “图表 ”工具栏上的 “图表对象

12、”下拉列 表中，选择 “图表区 ”，单击 “图表”工具栏 上的 “图表区格式 ”按钮，打开 “图表区格 式”对话框，在“图案 ”选项卡中，单击 “填充效果 ”按 钮，打开 “填充效果 ”对话框，在 “渐变 ”选 项卡中，选择 “预设 ”单选按钮，在 “预设颜 色”下拉列表中，选择 “羊皮纸 ”选项，在“底 纹样式 ”栏中，选择“中心辐射 ”单选项，单第 7 21 页击“确定”按钮。“填充效果 ”对话框的设置 如右图所示。设置背景墙的填充效果鼠标指向 “背景墙”，单击鼠标右键，在弹 出的快捷菜单中选择 “背景墙格式 ”命令， 打开 “背景墙格式 ”对话框，在 “图案 ”选项 卡中，单击 “填充效

13、果 ”按钮，打开 “填充效第 7 22 页 果”对话框。选择 “纹理 ”选项卡，在 “纹理 ”列表框中， 选择“水滴”，如右图所示，单击 “确定 ”按 钮。思考题（三）选择图表的数据源时，如何选择不连续的第 7 23 页数据区域？如何修改图表？对已建立完成的图表，应如何添加 “图表标题”？对图表进行格式化时，应该如何操作？ 将 “图表区 ”的填充效果设置为 “雨后初晴”，应如何操作？案例总结 本章主要介绍了： 统 计 函 数 COUNT 、 COUNTA 、 COUNTIF ；在使用 COUNTIF函数时应该注意， 当 公式需要复制时，如果参数“ Range” 的引用区域固定不变，通常应使用绝对 引用或区域命名方式实现；如果参数 “ Criteria ”是表达式或字符串，必须 在两边加上西文双引号。第 7 24 页 逻辑判断函数 IF 以及嵌套函数的使用； 当逻辑判断给出的条件多于两个时， 通常采用 IF 函数的嵌套。在使用 IF 函 数时，应注意函数多层嵌套时的括号匹 配，并且公式中的所有符号必须为西文 字符。设置条件格式； 图表的创建、修改及