Transcript Excel综合应用1—成绩表统计与分析

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