计算机应用基础第7章Excel综合应用1—成绩表统计与分析

第7章 Excel综合应用1—成绩表统计与分析 计算机应用基础 主编：许晞 副主编：刘艳丽 曾煌兴 郑杰 秦高德 学习目标 熟练掌握统计函数COUNT、COUNTIF的使用方法 熟练掌握逻辑判断函数IF的使用方法 掌握条件格式的设置方法 熟练使用图表的创建、修改 目录 7.1 成绩表统计案例分析 7.2 实现方法 7.2.1 用统计函数与公式制作“成绩统计表” 跨工作表的单元格引用 统计函数COUNTA及COUNT的使用 条件统计函数COUNTIF的使用 公式计算 7.2.2 用IF函数与条件格式制作“各科等级表” 逻辑判断函数IF的使用 条件格式 7.2.3用图表向导制作成绩统计图 使用“图表向导”创建图表 修改图表 格式化图表 7.3 案例总结 7.4 课后练习 7.1成绩表统计案例分析 任务的提出 班主任王老师要制作 “成绩统计表”、“成绩等级表” 及“成绩统计图。 解决方案 跨工作表的单元格引用 打开“第7章素材”文件夹下的“统计表(素材).xls”文件，选择“成绩统计表”工作表。 从“各科成绩表”中，将四门课程的“班级平均分”、“班级最高分”和“班级最低分”的数据引用到如右图所示的“成绩统计表”中的相应单元格中 。 统计函数COUNTA及COUNT的使用 将“各科成绩表”中各门课程的“应考人数”和“参考人数”的统计结果放置到“成绩统计表”中的相应单元格中。 在“插入函数”对话框中，选择“统计”，在“选择函数”列表框中，选择“COUNTA”函数，单击“确定”按钮。 打开“函数参数”对话框。进行如下图所示的设置。 “COUNT”函数的设置类似(略)。 条件统计函数COUNTIF的功能 COUNTIF函数的功能： 统计指定区域内满足给定条件的单元格数目。 语法格式： COUNTIF(range,criteria) 其中：Range 指定单元格区域，Criteria表示指定的条件表达式。 条件表达式的形式可以为数字、表达式或文本。例如，条件可以表示为 60、"60"、">=90" 或 "缺考"等。 条件统计函数COUNTIF的使用 用COUNTIF函数，将“各科成绩表”中各门课程的缺考人数以及各分数段人数的统计结果放置到“成绩统计表”中的相应单元格 。 在“函数参数”对话框中，进行如下图所示的设置：编辑栏中的函数为 =COUNTIF(各科成绩表!D2:D38,"缺考")。 各分数段人数的统计 公式计算 在“成绩统计表”中，计算各门课程的及格率和优秀率 。 计算及格率 ： 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 思考题(一) 如何引用同一工作簿中不同工作表的数据？ 引用单元格的数据与复制粘贴数据有何区别？ 统计函数COUNT与COUNTA的区别是什么？ 条件统计函数COUNTIF的应用场合是什么？其中的两个参数各代表什么？ 在“各科成绩表”工作表中分别统计出男、女生人数应该用什么函数？试写出函数的表达式。 单元格数据的删除与清除 将“各科成绩表”工作表复制一份，并将复制后的工作表更名为“各科等级表”。在“各科等级表”中，清除“大学英语”、“计算机应用”、“高等数学”及“应用文写作”列中的分数内容，并清除“总分”、“名次”列的所有属性；删除“班级平均分”、“班级最高分”、“班级最低分”所在单元格区域 。 逻辑判断函数IF的功能 IF函数的功能是： 判断给出的条件是否满足，如果满足返回一个值，如果不满足则返回另一个值。 语法格式： IF(logical_test,value_if_true,value_if_false) 共包括三个参数，其中： Logical_test 逻辑判断表达式； value_if_true 表达式为真时返回的值； value_if_false 表达式为假时返回的值。 逻辑判断函数IF的使用 利用If函数对“各科成绩表”中的“应用文写作”成绩在60分以上的，在“各科等级表”中“应用文写作”的对应位置设置为“及格”，否则为“不及格” 。 选择目标单元格G2 ，在“函数参数”对话框中进行如下图所示的设置。 逻辑判断函数IF的使用(续) 逻辑判断函数IF的使用(续) 条件格式 在“各科等级表”中，利用条件格式将所有“不及格”或所有等级为“E”的单元格设置成“黄色底纹红色 加 粗字体”；将所有“缺考”的单元格设置为“浅青绿底纹梅红色 加 粗字体” 。 在菜单栏中选择“格式”→“条件格式”命令，打开“条