Excel综合应用1----成绩表.ppt

第八章第八章 ExcelExcel综合应用综合应用1 1 -成绩表成绩表 焦作大学信息工程学院焦作大学信息工程学院 计算机基础教研室计算机基础教研室 第8章 Excel综合应用1成绩表统计与分析 2 * 学习目标 熟练掌握统计函数COUNT、COUNTIF的使 用方法 熟练掌握逻辑判断函数IF的使用方法 掌握条件格式的设置方法 熟练使用图表的创建、修改 第8章 Excel综合应用1成绩表统计与分析 3 * 目录 8.1 成绩表统计案例分析 8.2 实现方法 8.2.1 用统计函数与公式制作“成绩统计表” 跨工作表的单元格引用 统计函数COUNTA及COUNT的使用 条件统计函数COUNTIF的使用 公式计算 8.2.2 用IF函数与条件格式制作“各科等级表” 逻辑判断函数IF的使用 条件格式 8.2.3用图表向导制作成绩统计图 使用“图表向导”创建图表 修改图表 格式化图表 8.3 案例总结 8.4 课后练习 第8章 Excel综合应用1成绩表统计与分析 4 * 8.1成绩表统计案例分析 任务的提出 班主任王老师要制作 “成绩统计表”、“成绩等级表” 及“成绩统计图。 解决方案 第8章 Excel综合应用1成绩表统计与分析 5 * 跨工作表的单元格引用 打开“第8章素材”文件夹下 的“统计表（素材）.xls”文 件，选择“成绩统计表”工 作表。 从“各科成绩表”中，将四 门课程的“班级平均分”、“ 班级最高分”和“班级最低 分”的数据引用到如右图所 示的“成绩统计表”中的相 应单元格中 。 第8章 Excel综合应用1成绩表统计与分析 6 * 统计函数COUNTA及COUNT的使用 将“各科成绩表”中各门课程的“应考人数”和“参考人数”的 统计结果放置到“成绩统计表”中的相应单元格中。 在“插入函数”对话框中，选择“统计”，在“选择函数”列表 框中，选择“COUNTA”函数，单击“确定”按钮。 打开“函数参数”对话框。进行如下图所示的设置。 “COUNT”函数的设置类似（略）。 第8章 Excel综合应用1成绩表统计与分析 7 * 条件统计函数COUNTIF的功能 COUNTIF函数的功能： 统计指定区域内满足给定条件的单元格数目。 语法格式： COUNTIF(range,criteria) 其中：Range 指定单元格区域，Criteria表示指定的条 件表达式。 条件表达式的形式可以为数字、表达式或文本。例如， 条件可以表示为 60、“60“、“=90“ 或 “缺考“等。 第8章 Excel综合应用1成绩表统计与分析 8 * 条件统计函数COUNTIF的使用 用COUNTIF函数，将“各科成绩表”中各门课程的缺 考人数以及各分数段人数的统计结果放置到“成绩统 计表”中的相应单元格 。 在“函数参数”对话框中，进行如下图所示的设置：编辑 栏中的函数为 =COUNTIF(各科成绩表!D2:D38,“缺考“)。 第8章 Excel综合应用1成绩表统计与分析 9 * 各分数段人数的统计 条件对应函数 90-100(人 ) =COUNTIF(各科成绩绩表 !D2:D38,“=90“ 80-89(人)=COUNTIF(各科成绩绩表 !D2:D38,“=80“)- B9 70-79(人)=COUNTIF(各科成绩绩表 !D2:D38,“=70“)-B9-B10 60-69(人)=COUNTIF(各科成绩绩表 !D2:D38,“=60“)-B9-B10-B11 小于60(人 ) =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 第8章 Excel综合应用1成绩表统计与分析 11 * 思考题（一） 如何引用同一工作簿中不同工作表的数据？ 引用单元格的数据与复制粘贴数据有何区别？ 统计函数COUNT与COUNTA的区别是什么？ 条件统计函数COUNTIF的应用场合是什么？其中 的两个参数各代表什么？ 在“各科成绩表”工作表中分别统计出男、女生人 数应该用什么函数？试写出函数的表达式。 第8章 Excel综合应用1成绩表统计与分析 12 * 单元格数据的删除与清除 将“各科成绩表”工作表复制一份，并将复制后的 工作表更名为“各科等级表”。在“各科等级表”中， 清除“大学英语”、“计算机应用”、“高等数学”及“ 应用文写作”列中的分数内容，并清除“总分”、“名 次”列的所有属性；删除“班级平均分”、“班级最高 分”、“班级最低分”所在单元格区域 。 第8章 Excel综合应用1成绩表统计与分析 13 * 逻辑判断函数IF的功能 IF函数的功能是： 判断给出的条件是否满足，如果满足返回一个值，如果 不满足则返回另一个值。 语法格式： IF(logical_test,value_if_true,value_if_false) 共包括三个参数，其中： Logical_test 逻辑判断表达式； value_if_true 表达式为真时返回的值； value_if_false 表达式为假时返回的值。 IF函数的逻辑判断方法 第8章 Excel综合应用1成绩表统计与分析 14 * 逻辑判断函数IF的使用 利用If函数对“各科成绩表”中的“应用文写作”成绩 在60分以上的，在“各科等级表”中“应用文写作”的 对应位置设置为“及格”，否则为“不及格” 。 选择目标单元格G2 ，在“函数参数”对话框中进行如 下图所示的设置。 第8章 Excel综合应用1成绩表统计与分析 15 * 逻辑判断函数IF的使用（续） 按下表所示的分数与等级的对应关系，利用If嵌套 函数对“各科成绩表”中的“大学英语”、“计算机应 用”、“高等数学”三门课程的分数，在“各科等级表 ”中的对应科目中进行相应的等级设置 。 分数等级 缺考缺考 90=90 优秀 90分数=80良好 80分数=70中等 70分数=60及格 分数=90,优秀,if(90A2=80,良好,if(80A2=70,中等 ,if(70A2=60,及格,if(A260,不及格,false) 第8章 Excel综合应用1成绩表统计与分析 21 * 制作各科成绩统计图 图表比数据更易于表达数据之间的关系以及数据变化的 趋势。 “成绩统计图”可以直观地表现各门课程各分数段人数 。 第8章 Excel综合应用1成绩表统计与分析 22 * 使用“图表向导”创建图表 在“成绩统计表”中，根据各分数段人数及缺考人 数制作图表。要求如下： 图表类型为“簇状柱形图”； 数据系列产生在“列”； 图表标题为“成绩统计图”； 分类（X）轴为“等级”；数值（Y）轴为“人数”； 将图表“作为其中的对象插入”到“成绩统计表”中 。 第8章 Excel综合应用1成绩表统计与分析 23 * 选择图表类型 在菜单栏中选择“插入”“ 图表”命令，打开“图表向 导-4步骤之1-图表类型”对 话框，进行如右图所示的 设置。 单击“下一步”按钮 第8章 Excel综合应用1成绩表统计与分析 24 * 选择源数据 按住Ctrl键的同时，分别在当前工作表中选择不连续的两 个区域，如下图所示，并在“图表向导-4步骤之2-图表源数 据”对话框中，显示相应的设置。 第8章 Excel综合应用1成绩表统计与分析 25 * 设置图表选项 单击“下一步”按钮，打开“图表向导-4步骤之3-图表选项” 对话框，并进行如下图所示的设置。 第8章 Excel综合应用1成绩表统计与分析 26 * 选择图表位置 “下一步”按钮，打开“图表向导-4步骤之 4-图表位置”对话 框， 进行如下图所示的设置。 第8章 Excel综合应用1成绩表统计与分析 27 * 修改图表 在图表的制作过程中四步中的任何一步出错，都 不必从头开始，只需单击图表，使图表处于激活 状态，从 “图表”菜单中，选择对应的命令即可； 也可单击图表后，单击鼠标右键，从快捷菜单中 ，选择对应的命令。 第8章 Excel综合应用1成绩表统计与分析 28 * 修改图表要求 在“成绩统计表”工作表中，对图表进行如下修改 ： 将图表类型改为“三维簇状柱形图”； 数据系列产生在“行”，并从图表中删除缺考人数； 在图表中“显示数据表”； 将图表“作为新工作表插入”，并将新工作表命名为“ 成绩统计图”。 操作步骤略 第8章 Excel综合应用1成绩表统计与分析 29 * 图表的组成 第8章 Excel综合应用1成绩表统计与分析 30 * 格式化图表 在“成绩统计图”工作表中，对图表的外观进行如 下修饰： 将图表标题设置为“幼圆、22号、蓝色、加粗”； 将图表区的填充效果设置为“羊皮纸”； 将背景墙的填充效果设置为“水滴”； 为图例添加“阴影边框”。 第8章 Excel综合应用1成绩表统计与分析 31 * 设置图表区的填充效果 在“图表”工具栏上的“图表对象”下拉 列表中，选择“图表区”，单击“图表” 工具栏上的“图表区格式”按钮，打开“ 图表区格式”对话框， 在“图案”选项卡中，单击“填充效果” 按钮，打开“填充效果”对话框，在“渐 变”选项卡中，选择“预设”单选按钮， 在“预设颜色”下拉列表中，选择“羊皮 纸”选项，在“底纹样式”栏中，选择“ 中心辐射”单选项，单击“确定”按钮。 “填充效果”对话框的设置，如右图所 示。 第8章 Excel综合应用1成绩表统计与分析 32 * 设置背景墙的填充效果 鼠标指向“背景墙”，单击 鼠标右键，在弹出的快捷 菜单中选择“背景墙格式” 命令，打开“背景墙格式” 对话框，在“图案”选项卡 中，单击“填充效果”按钮 ，打开“填充效果”对话框 。 选择“纹理”选项卡，在“纹 理”列表框中，选择“水滴” ，如右图所示，单击“确定 ”按钮。 第8章 Excel综合应用1成绩表统计与分析 33 * 思考题（三） 选择图表的数据源时，如何选择不连续的数据区 域？ 如何修改图表？ 对已建立完成的图表，应如何添加“图表标题”？ 对图表进行格式化时，应该如何操作？ 将“图表区”的填充效果设置为“雨后初晴”，应如何 操作？ 第8章 Excel综合应用1成绩表统计与分析 34 * 案例总结 本章主要介绍了： 统计函数COUNT、COUNTA、COUNTIF； 在使用COUNTIF函数时应该注意，当公式需要复制时，如果参数 “Range”的引用区域固定不变，通常应使用绝对引用或区域命名方式 实现；如果参数“Criteria”是表达式或字符串，必须在两边加上西文 双引号。 逻辑判断函数IF以及嵌套函数的使用； 当逻辑判断给出的条件多于两个时，通常采用IF函数的嵌套。在使用 IF函数时，应注意函数多层嵌套时的括号匹配