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

第7章 Excel综合应用1—成绩表统计与分析
9
公式计算
在“成绩统计表”中，计算各门课程的及格率和优秀 率。
计算及格率 ：
COUNTIF(各科成绩表!D$2:D$38,">=60")/COUNT(各科成绩 表!D$2:D$38) 或：1-B13/B7
计算优秀率 ：
=COUNTIF(各科成绩表!D$2:D$38,">=90")/COUNT(各科成绩 =COUNTIF(各科成绩表!D$2:D$38,">=90")/COUNT(各科成绩 各科成绩表!D$2:D$38,">=90")/COUNT( 表!D$2:D$38) 或：B9/B7
2011-8-29
第7章 Excel综合应用1—成绩表统计与分析
11
单元格数据的删除与清除
将“各科成绩表”工作表复制一份，并将复制后 的工作表更名为“各科等级表”。在“各科等级 表”中，清除“大学英语”、“计算机应用”、 “高等数学”及“应用文写作”列中的分数内容， 并清除“总分”、“名次”列的所有属性；删除 “班级平均分”、“班级最高分”、“班级最低 分”所在单元格区域 。
2011-8-29
第7章 Excel综合应用1—成绩表统计与分析
17
条件格式（ 条件格式（续）
在“各科成绩表”中，将“标题”行及“班级平均分”行 之间（不包括这两行）的偶数行数据区域设置为“冰蓝色 底纹深蓝色字体” 。
在菜单栏中选择“格式”→“条件格式”命令，打开“条件格 式”对话框 ，进行如下图所示的设置。
7.2.2 用IF函数与条件格式制作“各科等级表”
逻辑判断函数IF的使用 逻辑判断函数IF的使用 IF 条件格式
7.2.3用图表向导制作成绩统计图
使用“图表向导” 使用“图表向导”创建图表 修改图表 格式化图表
7.3 案例总结 7.4 课后练习
2011-8-29
第7章 Excel综合应用1—成绩表统计与分析
在“成绩统计表”中，根据各分数段人数及缺考 人数制作图表。要求如下：
图表类型为“簇状柱形图”； 数据系列产生在“列”； 图表标题为“成绩统计图”； 分类（X）轴为“等级”；数值（Y）轴为“人数”； 将图表“作为其中的对象插入”到“成绩统计表”中 。
2011-8-29
第7章 Excel综合应用1—成绩表统计与分析
在下图所示的单元格中，有“分数”、“等级”两列数据， 其中“等级”数据是根据“分数”按以下条件计算所得： 条件 分数 等级 分数>=90 优秀 90>分数>=80 90> >=80 良好 80>分数>=70 中等 70>分数>=60 及格 分数<60 不及格 请分析以下表达式能否根据给定条件求出正确的结果？如 果有错，请说明出错原因和改正方法。 =if(A2>=90,优秀,if(90>A2>=80,良好,if(80>A2>=70,中 等,if(70>A2>=60,及格,if(A2<60,不及格,false)))))
2011-8-29
第7章 Excel综合应用1—成绩表统计与分析
12
逻辑判断函数IF的功能 逻辑判断函数 的功能
IF函数的功能是：
判断给出的条件是否满足，如果满足返回一个值，如果 不满足则返回另一个值。
语法格式：
IF(logical_test,value_if_true,value_if_false)
按下表所示的分数与等级的对应关系，利用If嵌套 函数对“各科成绩表”中的“大学英语”、“计 算机应用”、“高等数学”三门课程的分数，在 “各科等级表”中的对应科目中进行相应的等级 设置 。
分数 缺考 90<=分数 80<=分数<90 70<=分数<80 60<=分数<70 分数<60 等级 缺考 A B C D E
2011-8-29
第7章 Excel综合应用1—成绩表统计与分析
5
统计函数COUNTA及COUNT的使用 统计函数COUNTA及COUNT的使用 COUNTA
将“各科成绩表”中各门课程的“应考人数”和“参考人 数”的统计结果放置到“成绩统计表”中的相应单元格中。
在“插入函数”对话框中，选择“统计”，在“选择函数”列表 框中，选择“COUNTA”函数，单击“确定”按钮。 打开“函数参数”对话框。进行如下图所示的设置。 “COUNT”函数的设置类似（略）。
2011-8-29
第7章 Excel综合应用1—成绩表统计与分析
8
各分数段人数的统计
条件 90-100(人) 80-89(人) 70-79(人) 60-69(人) 59以下(人)
2011-8-29
对应函数 =COUNTIF(各科成绩表 各科成绩表!D2:D38,">=90" 各科成绩表 =COUNTIF(各科成绩表 各科成绩表!D2:D38,">=80")- B9 各科成绩表 =COUNTIF(各科成绩表 各科成绩表!D2:D38,">=70")-B9各科成绩表 B10 =COUNTIF(各科成绩表 各科成绩表!D2:D38,">=60")-B9各科成绩表 B10-B11 =COUNTIF(各科成绩表 各科成绩表!D2:D38,"<60") 各科成绩表
22
选择图表类型
在菜单栏中选择“插 入”→“图表”命令，打 开“图表向导-4步骤之1图表类型”对话框，进行 如右图所示的设置。 单击“下一步”按钮
2011-8-29
第7章 Excel综合应用1—成绩表统计与分析
23
选择源数据
按住Ctrl键的同时，分别在当前工作表中选择不连 续的两个区域，如下图所示，并在“图表向导-4 步骤之2-图表源数据”对话框中，显示相应的设 置。
27
修改图表要求
在“成绩统计表”工作表中，对图表进行如下修 改：
将图表类型改为“三维簇状柱形图”； 数据系列产生在“行”，并从图表中删除缺考人数； 在图表中“显示数据表”； 将图表“作为新工作表插入”，并将新工作表命名为 “成绩统计图”。
操作步骤略
2011-8-29
第7章 Excel综合应用1—成绩表统计与分析
2011-8-29
第7章 Excel综合应用1—成绩表统计与分析
6
条件统计函数COUNTIF的功能 的功能 条件统计函数
COUNTIF函数的功能：
统计指定区域内满足给定条件的单元格数目。
语法格式：
COUNTIF(range,criteria) 其中：Range 指定单元格区域，Criteria表示指定的条 件表达式。 条件表达式的形式可以为数字、表达式或文本。例如， 条件可以表示为 60、"60"、">=90" 或 "缺考"等。
7.1 成绩表统计案例分析 7.2 实现方法
7.2.1 用统计函数与公式制作“成绩统计表”
跨工作表的单元格引用 统计函数COUNTA COUNT的使用 COUNTA及 统计函数COUNTA及COUNT的使用 条件统计函数COUNTIF的使用 条件统计函数COUNTIF的使用 COUNTIF 公式计算
2011-8-29
第7章 Excel综合应用1—成绩表统计与分析
26
修改图表
在图表的制作过程中四步中的任何一步出错，都 不必从头开始，只需单击图表，使图表处于激活 状态，从 “图表”菜单中，选择对应的命令即可； 也可单击图表后，单击鼠标右键，从快捷菜单中， 选择对应的命令。
2011-8-29
第7章 Excel综合应用1—成绩表统计与分析
利用If函数对“各科成绩表”中的“应用文写作” 成绩在60分以上的，在“各科等级表”中“应用 文写作”的对应位置设置为“及格”，否则为 “不及格” 。
选择目标单元格G2 ，在“函数参数”对话框中进行如 下图所示的设置。
2011-8-29
第7章 Excel综合应用1—成绩表统计与分析
14
逻辑判断函数IF的使用（ 逻辑判断函数 的使用（续） 的使用
2011-8-29
第7章 Excel综合应用1—成绩表统计与分析
7
条件统计函数COUNTIF的使用 的使用 条件统计函数
用COUNTIF函数，将“各科成绩表”中各门课程的 缺考人数以及各分数段人数的统计结果放置到“成 绩统计表”中的相应单元格 。
在“函数参数”对话框中，进行如下图所示的设置：编辑 栏中的函数为 =COUNTIF(各科成绩表!D2:D38,"缺考")。 =COUNTIF(各科成绩表!D2:D38,"缺考 各科成绩表!D2:D38,"缺考"
第7章 Excel综合应用1—成绩表统计与分析
15
2011-8-29
逻辑判断函数IF的使用（ 逻辑判断函数 的使用（续） 的使用
按下表所示的分数与等级的对应关系，利用If嵌套 函数对“各科成绩表”中的“大学英语”、“计 算机应用”、“高等数学”三门课程的分数，在 “各科等级表”中的对应科目中进行相应的等级 设置 。
共包括三个参数，其中：
Logical_test 逻辑判断表达式； value_if_true 表达式为真时返回的值； value_if_false 表达式为假时返回的值。
IF函数的逻辑判断方法 函数的逻辑判断方法
2011-8-29
第7章 Excel综合应用1—成绩表统计与分析
13
逻辑判断函数IF的使用 逻辑判断函数 的使用
2011-8-29
第7章 Excel综合应用1—成绩表统计与分析
16
条件格式
在“各科等级表”中，利 用条件格式将所有“不及 格”或所有等级为“E”的 单元格设置成“黄色底纹 红色 加 粗字体”；将所有 “缺考”的单元格设置为 “浅青绿底纹梅红色 加 粗 字体” 。