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

1、 学习目标 熟练掌握统计函数COUNT、COUNTIF 及COUNTIFS的使用方法 熟练掌握逻辑判断函数IF的使用方法 掌握条件格式的设置方法 熟练使用图表的创建、修改及格式化 2021-7-1第8章 Excel综合应用1成绩表统计与分析2 8.1 成绩表统计案例分析 8.2 实现方法 8.2.1 用统计函数与公式制作“成绩统计表” 跨工作表的单元格引用 统计函数COUNTA及COUNT的使用 条件统计函数COUNTIF与COUNTIFS的使用 公式计算 8.2.2 用IF函数与条件格式制作“各科等级表” 单元格数据的清除与删除 逻辑判断函数IF的使用 条件格式 8.2.3用图表向导制作成绩

2、统计图 创建图表 修改图表 格式化图表 8.3 案例总结 8.4 课后练习 2021-7-1第8章 Excel综合应用1成绩表统计与分析3 8.1成绩表统计案例分析 任务的提出 班主任王老师要制作 “成绩统计表”、“成绩等级表” 及“成绩统计图。 解决方案 2021-7-1 第8章 Excel综合应用1成绩表统计与分 析 4 第8章 Excel综合应用1成绩表统计与分析 5 2021-7-1 跨工作表的单元格引用 打开“统计表（素材）.xlsx” 文件，选择“成绩统计表” 工作表。 从“各科成绩表”中，将四 门课程的“班级平均分”、 “班级最高分”和“班级最 低分”的数据引用到“成绩 统计表”

3、中的相应单元格 中 。 第8章 Excel综合应用1成绩表统计与分析 6 2021-7-1 统计函数COUNT及COUNTA的使用 将“各科成绩表”中各门课程的“参考人数”和“应考人 数”的统计结果放置到“成绩统计表”中的相应单元格中。 在“开始”选项卡的“编辑”组中单击“自动求和”按钮 旁边的 下拉箭头，在弹出的列表中选择“计数”选项。可计算出“参考 人数”。 在“公式”选项卡的“函数库”组中单击“其他函数”按钮 ，在 下拉列表中选择“统计”选项，在弹出的下拉列表框中选择 “COUNTA”。可计算出“应考人数”。 第8章 Excel综合应用1成绩表统计与分析72021-7-1 条件统计函数C

4、OUNTIF的功能 COUNTIF函数的功能： 统计指定区域内满足给定条件的单元格数目。 语法格式： COUNTIF(range,criteria) 其中：Range 指定单元格区域，Criteria表示指 定的条件表达式。 条件表达式的形式可以为数字、表达式或文本。 例如，条件可以表示为 60、60、=90 或 缺考等。 第8章 Excel综合应用1成绩表统计与分 析 82021-7-1 条件统计函数COUNTIF的使用 在“成绩统计表”的相应单元格中，用COUNTIF函数 统计“各科成绩表”中4门课程的“缺考”人数、 “90-100”以及“小于60”分数段的人数。 在“函数参数”对话框中，

5、进行如下图所示的设置：编辑 栏中的函数为 =COUNTIF(各科成绩表各科成绩表!D2:D38,缺考缺考)。 用相同方法可以统计出“90-100”以及“小于60”分数段的 人数。 第8章 Excel综合应用1成绩表统计与分析92021-7-1 条件统计函数COUNTIFS的功能 COUNTIFS函数的功能是： 将条件应用于跨多个区域的单元格，并计算符 合所有条件的次数。 语法格式： COUNTIFS (Criteria_range1, Criteria1, Criteria_range2, Criteria2) 其中： Criteria_range1 必需。在其中计算关联条件的第一个区 域，

6、Criteria1必需。条件表达式； Criteria_range2, Criteria2, .可选。附加的区域及其关联 条件。最多允许 127 个区域/条件对。 第8章 Excel综合应用1成绩表统计与分 析 102021-7-1 条件统计函数COUNTIFS的使用 在“成绩统计表”的相应单元格中，用COUNTIFS函数 统计“各科成绩表”中4门课程的“80-89”、“70-79” 以及“60-69”分数段的人数。 在打开的“函数参数”对话框中填写上两组单元格范围和 条件，如下图所示，单击“确定”按钮。 用相同方法可以统计出“70-79”以及“60-69”分数段的人 数。 第8章 Excel

7、综合应用1成绩表统计与分 析 112021-7-1 公式计算 在“成绩统计表”中，计算各门课程的优秀率 和及格率 。 计算优秀率 ： =COUNTIF(各科成绩表!D$2:D$38,=90)/COUNT(各科成绩 表!D$2:D$38) 或：=B9/B6 计算及格率 ： =COUNTIF(各科成绩表!D$2:D$38,=60)/COUNT(各科成绩 表!D$2:D$38) 或：=1-B13/B6 第8章 Excel综合应用1成绩表统计与分 析 122021-7-1 思考题（一） 如何引用同一工作簿中不同工作表的数据？ 引用单元格的数据与复制粘贴数据有何区 别？ 统计函数COUNT与COUNTA

8、的区别是什么？ 条件统计函数COUNTIF和COUNTIFS的应用场 合是分别什么？ 在“各科成绩表”工作表中分别统计出男、 女生人数应该用什么函数？试写出函数的 表达式。 第8章 Excel综合应用1成绩表统计与分 析 132021-7-1 单元格数据的删除与清除 复制“各科成绩表”工作表，并将复制后的工 作表更名为“各科等级表”。在“各科等级表” 中，清除4门课程列中的分数内容；清除“总 分”列的所有属性；删除分数统计所在单元格 区域。 按Delete键，只清除选定单元格的内容，而保留了 单元格的格式。 在“开始”选项卡的“编辑”组中单击“清除”按 钮 ，在弹出的列表中选择“全部清除”选项

9、。此 时单元格中的内容和格式全部被清除。 在“开始”选项卡的“单元格”组中单击“删除” 按钮 ，这时单元格区域的内容会由其他内容替代。 第8章 Excel综合应用1成绩表统计与分 析 142021-7-1 逻辑判断函数IF的功能 IF函数的功能是： 判断给出的条件是否满足，如果满足返回一个 值，如果不满足则返回另一个值。 语法格式： IF(logical_test,value_if_true,value_if_false) 共包括三个参数，其中： Logical_test 逻辑判断表达式； value_if_true 表达式为真时返回的值； value_if_false 表达式为假时返回的值。

10、 第8章 Excel综合应用1成绩表统计与分 析 152021-7-1 逻辑判断函数IF的使用 在“各科等级表”的“应用文写作”列中，利用IF 函数引用“各科成绩表”中“应用文写作”列的对 应分数进行逻辑判断，如果分数在60分以上的返回 “及格”，否则返回“不及格”。 选择目标单元格G2 ，在“函数参数”对话框中进行如 下图所示的设置。 第8章 Excel综合应用1成绩表统计与分 析 162021-7-1 逻辑判断函数IF的使用（二） 在“各科等级表”的“应用文写作”列中，利用IF函数 再添加一个条件：对“各科成绩表”的“应用文写作” 列中成绩为“缺考”的考生，在对应位置仍设置为“缺 考”。

11、在“各科等级表”中选择目标单元格G2，选择除“=”以外 的其他内容，按快捷键Ctrl+X。 在对话框中进行如下图所示的设置。其中在“Value_if_false” 编辑框中，将插入点定位后，按快捷键Ctrl+V。 第8章 Excel综合应用1成绩表统计与分析 17 2021-7-1 逻辑判断函数IF的使用（三） 在“各科等级表”的“大学英语”、“计算机应用”、 “高等数学”列中，利用IF嵌套函数，引用“各科成绩 表”中对应的3门课程的分数，按下表中分数与成绩等 级的对应关系，在相应的科目中进行成绩等级设置。 分数等级 缺考缺考 分数=90A 90分数=80B 80分数=70C 70分数=60D

12、 分数60E 第8章 Excel综合应用1成绩表统计与分析 18 2021-7-1 逻辑判断函数IF的使用（四） （1）在目标单元格D2中输入“=”。 （2）单击“名称框”处的“IF”，打开“函数参数”对话框。 （3）在“函数参数”对话框中进行如下图所示的设置。并将插入点定位在 第3个参数“Value_if_false”编辑框中。 （4）再次单击“名称框”处的“IF”。第2次打开“函数参数”对话框，在 “Logical_test”处输入“各科成绩表!D2=90 优秀 90分数=80良好 80分数=70中等 70分数=60及格 分数=90,优秀,if(90A2=80,良好,if(80A2=70,

13、中 等,if(70A2=60,及格,if(A260,不及格,false) 制作各科成绩统计图 图表比数据更易于表达数据之间的关系以及数据变化的 趋势。 “成绩统计图”可以直观地表现各门课程各分数段人 数。 2021-7-1 第8章 Excel综合应用1成绩表统计与分 析 24 创建图表 在“成绩统计表”中， 根据各分数段人数及缺 考人数制作图表。要求 如下：图表类型为“簇 状柱形图” 。 选择目标单元格区域 A8:E13。 在“插入”选项卡的 “图表”组中单击“柱 形图”按钮 ，在弹出的 下拉列表中选择“二维 柱形图”区域的“簇状 柱形图”选项。生成的 图表如右图所示。 第8章 Excel综合

14、应用1成绩表统计与分析 25 2021-7-1 这里有错误 选择数据源 选中图表，选择“图表工具-设计”选项卡，在“数据”组中单 击“选择数据”按钮 ，弹出“选择数据源”对话框。 在保留原选定区域的基础上，按住Ctrl键不放，再选择“A2:E2” 区域，如下图所示。可以看到，工作表中选定区域出现了两个 闪动的虚线框，虚线框的单元格以绝对地址的形式出现在“图 表数据区域”文本框中，新增的标题区域“A2:E2”，已反映在 图例项中，替换了“系列1”、“系列4”。单击“确定” 按钮后，图表也同步更新。 第8章 Excel综合应用1成绩表统计与分析 26 2021-7-1 设置图表布局与图表样式 设置

15、图表布局为“布局9”、图表样式为“样式26”。 在“图表工具-设计”选项卡的“图表布局”组中单击列表 框旁的“其他”按钮 ，弹出“图表布局”列表框，选择“布 局9”。 在“图表工具-设计”选项卡的“图表样式”组中单击列表 框旁的“其他”按钮 ，弹出“图表样式”列表框，选择“样 式26”。设置效果如下图所示。 第8章 Excel综合应用1成绩表统计与分析 27 2021-7-1 修改图表（一） 在“成绩统计表”工作表 中，为图表添加“模拟运 算表”、将“图例”位置 移动到顶部显示。 选中图表，选择“图表工 具-布局”选项卡，在“标 签”组中单击“模拟运算 表”按钮 ，在弹出的下拉 列表中选择“显

16、示模拟运 算表和图例项标示”选项。 在“标签”组中单击“图 例”按钮 ，在弹出的下拉 列表中选择“在顶部显示 图例”选项。设置效果如 右图所示。 第8章 Excel综合应用1成绩表统计与分析 28 2021-7-1 第8章 Excel综合应用1成绩表统计与分 析 292021-7-1 修改图表 （二） 在“成绩统计表”工作表中，将图表类型改为“簇状圆 柱图”、“切换行/列”，从图表中删除“缺考人数”、 将图表移动到工作簿的新工作表中，并将新工作表命名 为“成绩统计图”。 单击图表。选择“图表工具-设计”选项卡。 在“类型”组中单击“更改图表类型”按钮 ，打开“更改 图表类型”对话框，在“柱形图

17、”区域中选择“簇状圆柱图” 选项。 在“数据”组中单击“选择数据”按钮 ，打开“选择数据 源”对话框，单击“切换行/列”按钮，图例项变为行标题。 在“图例项（系列）”栏中，选择“缺考人数”系列，单击 “删除”按钮。 在“位置”组中单击“移动图表”按钮 ，打开“移动图表” 对话框，选择“新工作表”单选按钮，在右边的文本框中输 入“成绩统计图”。 修改图表（三） 在“成绩统计图”工作表中，调整三维视图的 角度，修改图表标题及坐标轴标题。 选择“成绩统计图”工作表中的图表。单击鼠标右 键，在弹出的快捷菜单中选择“三维旋转”命令， 打开“设置图表区格式”对话框，在“三维旋转” 的“图表缩放”区域中勾选

18、“直角坐标轴”复选框。 鼠标单击并选中“图表标题”文字，输入“成绩统 计图”。 鼠标右键单击横坐标轴位置，从快捷菜单中选择 “编辑文字”，删除原有文字，输入“等级”。用 相同的方法，在纵坐标轴位置输入“人数”。 2021-7-1第8章 Excel综合应用1成绩表统计与分析30 格式化图表 在“成绩统计图”工作表中，设 置“图表区”、“背景墙”的填 充效果；修饰“图表标题”。效 果如右图所示。 选择“图表区”。 在“形状样式”组中选择“细微效果-蓝 色，强调颜色1”。 在“形状效果” 列表中选择“阴影” 中 的“内部”区域中的“内部右上角”选项。 选择“背景墙”。 在“形状样式”组中选择“纹理”

19、中的 “蓝色面巾纸”选项。 选择图表标题。 设置字体为“隶书”、字号为“28”。 选择第4行第2列的艺术字样式。 在“文本效果” 的“阴影”选项中选择 “外部”区域中的“左下斜偏移”选项。 第8章 Excel综合应用1成绩表统计与分析 31 2021-7-1 第8章 Excel综合应用1成绩表统计与分 析 322021-7-1 思考题（三） 选择图表的数据源时，如何选择不连续的 数据区域？ 如何修改图表？ 对已建立完成的图表，应如何添加“图表 标题”？ 对图表进行格式化时，应该如何操作？ 将“图表区”的填充效果设置为“雨后初 晴”，应如何操作？ 第8章 Excel综合应用1成绩表统计与分 析 332021-7-1 案例总结 （一） 统计函数COUNT、COUNTA、COUNTIF、COUNTIFS 在使用COUNTIF函数时应该注意，当公式需要复制时，如 果参数“Range”的引