第7章Ecel综合应用1_成绩表统计与分析.ppt

计算机应用基础 主编 许晞副主编 刘艳丽曾煌兴郑杰秦高德 第7章Excel综合应用1 成绩表统计与分析 2 2020 2 7 学习目标 熟练掌握统计函数COUNT COUNTIF的使用方法熟练掌握逻辑判断函数IF的使用方法掌握条件格式的设置方法熟练使用图表的创建 修改 第7章Excel综合应用1 成绩表统计与分析 3 2020 2 7 目录 7 1成绩表统计案例分析7 2实现方法7 2 1用统计函数与公式制作 成绩统计表 跨工作表的单元格引用统计函数COUNTA及COUNT的使用条件统计函数COUNTIF的使用公式计算7 2 2用IF函数与条件格式制作 各科等级表 逻辑判断函数IF的使用条件格式7 2 3用图表向导制作成绩统计图使用 图表向导 创建图表修改图表格式化图表7 3案例总结7 4课后练习 第7章Excel综合应用1 成绩表统计与分析 4 2020 2 7 7 1成绩表统计案例分析 任务的提出班主任王老师要制作 成绩统计表 成绩等级表 及 成绩统计图 解决方案 第7章Excel综合应用1 成绩表统计与分析 5 2020 2 7 跨工作表的单元格引用 打开 第7章素材 文件夹下的 统计表 素材 xls 文件 选择 成绩统计表 工作表 从 各科成绩表 中 将四门课程的 班级平均分 班级最高分 和 班级最低分 的数据引用到如右图所示的 成绩统计表 中的相应单元格中 第7章Excel综合应用1 成绩表统计与分析 6 2020 2 7 统计函数COUNTA及COUNT的使用 将 各科成绩表 中各门课程的 应考人数 和 参考人数 的统计结果放置到 成绩统计表 中的相应单元格中 在 插入函数 对话框中 选择 统计 在 选择函数 列表框中 选择 COUNTA 函数 单击 确定 按钮 打开 函数参数 对话框 进行如下图所示的设置 COUNT 函数的设置类似 略 第7章Excel综合应用1 成绩表统计与分析 7 2020 2 7 条件统计函数COUNTIF的功能 COUNTIF函数的功能 统计指定区域内满足给定条件的单元格数目 语法格式 COUNTIF range criteria 其中 Range指定单元格区域 Criteria表示指定的条件表达式 条件表达式的形式可以为数字 表达式或文本 例如 条件可以表示为60 60 90 或 缺考 等 第7章Excel综合应用1 成绩表统计与分析 8 2020 2 7 条件统计函数COUNTIF的使用 用COUNTIF函数 将 各科成绩表 中各门课程的缺考人数以及各分数段人数的统计结果放置到 成绩统计表 中的相应单元格 在 函数参数 对话框中 进行如下图所示的设置 编辑栏中的函数为 COUNTIF 各科成绩表 D2 D38 缺考 第7章Excel综合应用1 成绩表统计与分析 9 2020 2 7 各分数段人数的统计 第7章Excel综合应用1 成绩表统计与分析 10 2020 2 7 公式计算 在 成绩统计表 中 计算各门课程的及格率和优秀率 计算及格率 COUNTIF 各科成绩表 D 2 D 38 60 COUNT 各科成绩表 D 2 D 38 或 1 B13 B7计算优秀率 COUNTIF 各科成绩表 D 2 D 38 90 COUNT 各科成绩表 D 2 D 38 或 B9 B7 第7章Excel综合应用1 成绩表统计与分析 11 2020 2 7 思考题 一 如何引用同一工作簿中不同工作表的数据 引用单元格的数据与复制粘贴数据有何区别 统计函数COUNT与COUNTA的区别是什么 条件统计函数COUNTIF的应用场合是什么 其中的两个参数各代表什么 在 各科成绩表 工作表中分别统计出男 女生人数应该用什么函数 试写出函数的表达式 第7章Excel综合应用1 成绩表统计与分析 12 2020 2 7 单元格数据的删除与清除 将 各科成绩表 工作表复制一份 并将复制后的工作表更名为 各科等级表 在 各科等级表 中 清除 大学英语 计算机应用 高等数学 及 应用文写作 列中的分数内容 并清除 总分 名次 列的所有属性 删除 班级平均分 班级最高分 班级最低分 所在单元格区域 第7章Excel综合应用1 成绩表统计与分析 13 2020 2 7 逻辑判断函数IF的功能 IF函数的功能是 判断给出的条件是否满足 如果满足返回一个值 如果不满足则返回另一个值 语法格式 IF logical test value if true value if false 共包括三个参数 其中 Logical test逻辑判断表达式 value if true表达式为真时返回的值 value if false表达式为假时返回的值 IF函数的逻辑判断方法 第7章Excel综合应用1 成绩表统计与分析 14 2020 2 7 逻辑判断函数IF的使用 利用If函数对 各科成绩表 中的 应用文写作 成绩在60分以上的 在 各科等级表 中 应用文写作 的对应位置设置为 及格 否则为 不及格 选择目标单元格G2 在 函数参数 对话框中进行如下图所示的设置 第7章Excel综合应用1 成绩表统计与分析 15 2020 2 7 逻辑判断函数IF的使用 续 按下表所示的分数与等级的对应关系 利用If嵌套函数对 各科成绩表 中的 大学英语 计算机应用 高等数学 三门课程的分数 在 各科等级表 中的对应科目中进行相应的等级设置 第7章Excel综合应用1 成绩表统计与分析 16 2020 2 7 逻辑判断函数IF的使用 续 按下表所示的分数与等级的对应关系 利用If嵌套函数对 各科成绩表 中的 大学英语 计算机应用 高等数学 三门课程的分数 在 各科等级表 中的对应科目中进行相应的等级设置 第7章Excel综合应用1 成绩表统计与分析 17 2020 2 7 条件格式 在 各科等级表 中 利用条件格式将所有 不及格 或所有等级为 E 的单元格设置成 黄色底纹红色加粗字体 将所有 缺考 的单元格设置为 浅青绿底纹梅红色加粗字体 在菜单栏中选择 格式 条件格式 命令 打开 条件格式 对话框 进行如下图所示的设置 第7章Excel综合应用1 成绩表统计与分析 18 2020 2 7 条件格式 续 在 各科成绩表 中 将 标题 行及 班级平均分 行之间 不包括这两行 的偶数行数据区域设置为 冰蓝色底纹深蓝色字体 在菜单栏中选择 格式 条件格式 命令 打开 条件格式 对话框 进行如下图所示的设置 第7章Excel综合应用1 成绩表统计与分析 19 2020 2 7 思考题 二 对单元格数据的清除和删除操作有何区别 IF函数有几个参数 每个参数的意义是什么 IF函数最多可以有几层嵌套 如何设置条件格式 第7章Excel综合应用1 成绩表统计与分析 20 2020 2 7 问题分析 在下图所示的单元格中 有 分数 等级 两列数据 其中 等级 数据是根据 分数 按以下条件计算所得 条件分数等级分数 90优秀90 分数 80良好80 分数 70中等70 分数 60及格分数 90 优秀 if 90 A2 80 良好 if 80 A2 70 中等 if 70 A2 60 及格 if A2 60 不及格 false 第7章Excel综合应用1 成绩表统计与分析 21 2020 2 7 制作各科成绩统计图 图表比数据更易于表达数据之间的关系以及数据变化的趋势 成绩统计图 可以直观地表现各门课程各分数段人数 第7章Excel综合应用1 成绩表统计与分析 22 2020 2 7 使用 图表向导 创建图表 在 成绩统计表 中 根据各分数段人数及缺考人数制作图表 要求如下 图表类型为 簇状柱形图 数据系列产生在 列 图表标题为 成绩统计图 分类 X 轴为 等级 数值 Y 轴为 人数 将图表 作为其中的对象插入 到 成绩统计表 中 第7章Excel综合应用1 成绩表统计与分析 23 2020 2 7 选择图表类型 在菜单栏中选择 插入 图表 命令 打开 图表向导 4步骤之1 图表类型 对话框 进行如右图所示的设置 单击 下一步 按钮 第7章Excel综合应用1 成绩表统计与分析 24 2020 2 7 选择源数据 按住Ctrl键的同时 分别在当前工作表中选择不连续的两个区域 如下图所示 并在 图表向导 4步骤之2 图表源数据 对话框中 显示相应的设置 第7章Excel综合应用1 成绩表统计与分析 25 2020 2 7 设置图表选项 单击 下一步 按钮 打开 图表向导 4步骤之3 图表选项 对话框 并进行如下图所示的设置 第7章Excel综合应用1 成绩表统计与分析 26 2020 2 7 选择图表位置 下一步 按钮 打开 图表向导 4步骤之4 图表位置 对话框 进行如下图所示的设置 第7章Excel综合应用1 成绩表统计与分析 27 2020 2 7 修改图表 在图表的制作过程中四步中的任何一步出错 都不必从头开始 只需单击图表 使图表处于激活状态 从 图表 菜单中 选择对应的命令即可 也可单击图表后 单击鼠标右键 从快捷菜单中 选择对应的命令 第7章Excel综合应用1 成绩表统计与分析 28 2020 2 7 修改图表要求 在 成绩统计表 工作表中 对图表进行如下修改 将图表类型改为 三维簇状柱形图 数据系列产生在 行 并从图表中删除缺考人数 在图表中 显示数据表 将图表 作为新工作表插入 并将新工作表命名为 成绩统计图 操作步骤略 第7章Excel综合应用1 成绩表统计与分析 29 2020 2 7 图表的组成 第7章Excel综合应用1 成绩表统计与分析 30 2020 2 7 格式化图表 在 成绩统计图 工作表中 对图表的外观进行如下修饰 将图表标题设置为 幼圆 22号 蓝色 加粗 将图表区的填充效果设置为 羊皮纸 将背景墙的填充效果设置为 水滴 为图例添加 阴影边框 第7章Excel综合应用1 成绩表统计与分析 31 2020 2 7 设置图表区的填充效果 在 图表 工具栏上的 图表对象 下拉列表中 选择 图表区 单击 图表 工具栏上的 图表区格式 按钮 打开 图表区格式 对话框 在 图案 选项卡中 单击 填充效果 按钮 打开 填充效果 对话框 在 渐变 选项卡中 选择 预设 单选按钮 在 预设颜色 下拉列表中 选择 羊皮纸 选项 在 底纹样式 栏中 选择 中心辐射 单选项 单击 确定 按钮 填充效果 对话框的设置 如右图所示 第7章Excel综合应用1 成绩表统计与分析 32 2020 2 7 设置背景墙的填充效果 鼠标指向 背景墙 单击鼠标右键 在弹出的快捷菜单中选择 背景墙格式 命令 打开 背景墙格式 对话框 在 图案 选项卡中 单击 填充效果 按钮 打开 填充效果 对话框 选择 纹理 选项卡 在 纹理 列表框中 选择 水滴 如右图所示 单击 确定 按钮 第7章Excel综合应用1 成绩表统计与分析 33 2020 2 7 思考题 三 选择图表的数据源时 如何选择不连续的数据区域 如何修改图表 对已建立完成的图表 应如何添加 图表标题 对图表进行格式化时 应该如何操作 将 图表区 的填充效果设置为 雨后初晴 应如何操作 第7章Excel综合应用1 成绩表统计与分析 34 2020 2 7 案例总结 本章主要介绍了 统计函数COUNT COUNTA COUNTIF 在使用COUNTIF函数时应该注意 当公式需要复制时 如果参数 Range 的引用区域固定不变 通常应使用绝对引用或区域命名方式实现 如果参数 Criteria 是表达式或字符串 必须在两边加上西文双引号 逻辑判断函数IF以及嵌套函数的使用 当逻辑判断给出的条件多于两个时 通常采用IF函数的嵌套