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

25
设置图表选项
单击“下一步”按钮，打开“图表向导-4步骤之3图表选项”对话框，并进行如下图所示的设置。
26
选择图表位置
“下一步”按钮，打开“图表向导-4步骤之 4-图表位 置”对话框， 进行如下图所示的设置。
27
修改图表
在图表的制作过程中四步中的任何一步出错， 都不必从头开始，只需单击图表，使图表处 于激活状态，从 “图表”菜单中，选择对应 的命令即可；也可单击图表后，单击鼠标右 键，从快捷菜单中，选择对应的命令。
4
任务一
打开“统计表（素材）”工作簿，从“各科 成绩表”中，将四门课程的“班级平均分”、 “班级最高分”、“班级最低分”的的数据 引用到“成绩统计表的相应单元格中。 ★可用填充柄复制其他三门课程的“平均分”、 “最高分”、“最低分”。前提：两张表中 四门课程的排列顺序一致
5
统计函数COUNTA及COUNT
应考人数
参考人数
缺考人数
7
条件统计函数COUNTIF
作用 用来统计指定区域中满足给定条件的单元格的个数。 语法讲解 COUNTIF(range,criteria) Range 为需要计算其中满足条件的单元格数目的单元格区 域，即（范围）。 Criteria 为指定的条件，其形式可以为数字、表达式或文 本，即（条件）。 注意：如果条件是表达式或者字符串，则两边要加上西文双引 号。
=COUNTIF(各科成绩表 各科成绩表!D2:D38,">=90")/COUNT(各科成绩 各科成绩表 各科成绩 表!D2:D38) 或：B9/B7
10
练习
成绩统计表
课程 班级平均分 班级最高分 班级最低分 应考人数 参考人数 缺考人数 90-100(人) 80-89(人) 70-79(人) 60-69(人) 小于60(人) 及格率 优秀率 大学英语 计算机应用 高等数学 应用文写作
28
修改图表要求
在“成绩统计表”工作表中，对图表进行如 下修改：
将图表类型改为“三维簇状柱形图”； 数据系列产生在“行”，并从图表中删除缺考人 数； 在图表中“显示数据表”； 将图表“作为新工作表插入”，并将新工作表命 名为“成绩统计图”。
29
图表的组成
30
格式化图表
在“成绩统计图”工作表中，对图表的外观 进行如下修饰：
打开“统计表（素材）” 完成数据的引入或计算。 “及格率”和“优秀率” 保留两位小数，并设置 成百分比格式。
11
任务二
用IF函数制作“各科等 级表”，将卷面分数转 化为等级
12
IF函数的使用
IF函数的功能是：
判断给出的条件是否满足，如果满足返回一个 值，如果不满足则返回另一个值。
语法格式：
IF(logical_test,value_if_true,value_if_false)
共包括三个参数，其中：
Logical_test 逻辑判断表达式； value_if_true 表达式为真时返回的值； value_if_false 表达式为假时返回绩表”复制一份，更名为“各 科等级表”， （2）清除“大学英语”、“计算机应用”、 “高等数学”及“应用文写作”列的分数值； （3）删除“总分”、“名次”列以及“班级平 均分”、“班级最高分”、“班级最低分” 行
9
公式计算
在“成绩统计表”中，计算各门成绩的及格率和优 秀率
计算及格率 ：
COUNTIF(各科成绩表 COUNTIF(各科成绩表!D2:D38,">=60")/COUNT(各科成绩 各科成绩表!D2:D38,">=60")/COUNT(各科成绩 表!D2:D38) 或：1-B13/B7
计算优秀率 ：
17
讨论
大学英语、计算机应用、高等数学列的等级如何用 IF函数来完成？ 分数 缺考 分数>=90 80<=分数<90 等级 缺考 A B 分数 70<=分数<80 60<=分数<70 分数<60 等级 C D E
分数与等级的对应关系表
18
条件
=IF(G2<60,"E",IF(G2<70,"D",IF(G2<80,"C",I F(G2<90,"B",IF(G2<=100,"A","缺考")))))
8
讨论
成绩统计表
课程 班级平均分 大学英语 计算机应用 高等数学 应用文写作
班级最高分
班级最低分 应考人数 参考人数 缺考人数 90-100(人) 80-89(人) 70-79(人) 60-69(人) 小于60(人) 及格率 优秀率
“成绩统计表”中有哪 些数据需要用 “COUNTIF”函数来统 计出来？
21
任务三、制作各科成绩统计图
图表比数据更易于表达数据之间的关系以及数据 变化的趋势。 “成绩统计图”可以直观地表现各门 课程各分数段人数。
22
使用“图表向导”创建图表
在“成绩统计表”中，根据各分数段人数及 缺考人数制作图表。要求如下：
图表类型为“簇状柱形图”； 数据系列产生在“列”； 图表标题为“成绩统计图”； 分类（X）轴为“等级”；数值（Y）轴为“人 数”； 将图表“作为其中的对象插入”到“成绩统计表” 中。
35
15
IF函数的嵌套
格式： =IF(条件表达式1,值1,IF(条件表达式2, 值2,值3)) 或 =IF(条件表达式1, IF(条件表达式2,值 1,值2),值3)
完成：把白庆辉、赵文静两人的成绩改成“缺 考”，用IF函数统计三种情况。
16
IF函数的嵌套举例
=IF(各科成绩表!G5="缺考","缺考",IF(各科成 绩表!G5>=60,"及格","不及格"))
课程 班级平均分 班级最高分 班级最低分 应考人数
大学英语
计算机应用
高等数学
应用文写作
参考人数 缺考人数 90-100(人) 80-89(人) 70-79(人) 60-69(人) 小于60(人) 及格率 优秀率
3
跨工作表的单元格引用
引用方法一：选择性粘贴 在源表复制数据，打开目标文件单元格， 在源表复制数据，打开目标文件单元格，选择性 粘贴，粘贴链接。 粘贴，粘贴链接。 引用方法二：使用命令 在目标单元格输入 =[excel文件名 工作表名 单元格地址 文件名]工作表名 文件名 工作表名!单元格地址 注意：标点都是在英文状态下输入的。 注意：标点都是在英文状态下输入的。 直接在单元格里输入“ 后面直接点击要引 或者 直接在单元格里输入“=”后面直接点击要引 用的单元格就行。 用的单元格就行。
公式必须以“=”开始，只有返回值是真时，指定格 式才应用于选定的单元格。 例：=MOD（ROW（）,2）=0 MOD为求余函数，返回两数相除的余数 ROW（）函数用于测试当前行号 练习：在“各科成绩表”中，将“标题”行及“班 级平均分”行之间（不包括这两行）的偶数行数据 区域设置为“冰蓝色底纹深蓝色字体” 。
Excel 综合应用
——成绩表的统计与分析 成绩表的统计与分析
1
知识点
统计函数COUNT、COUNTA、COUNTIF 逻辑判断函数IF 条件格式 EXCEL中的图表
2
任务一
成绩统计表
从“各科成绩表”中， 将四门课程的“班级平 均分”、“班级最高 分”、“班级最低分” 的的数据引用到“成绩 统计表的相应单元格中。
19
练习
按照课件P17 “分数与等级的对应关系表”，利用IF 函数的嵌套，将大学英语、计算机应用、高等数学 列的等级设置好。 在“各科等级表”中，利用条件格式将所有的“不 及格”或等级为E的单元格设置成“黄色底纹红色 加粗字体”；将所有“缺考”的单元格设置成“浅 绿色底纹玫红色加粗字体”
20
条件格式中的公式
23
选择图表类型
在菜单栏中选择“插入”→“图表”命令，打 开“图表向导-4步骤之1-图表类型”对话框， 进行如右图所示的设置。 单击“下一步”按钮
24
选择源数据
按住Ctrl键的同时，分别在当前工作表中选择 不连续的两个区域，如下图所示，并在“图 表向导-4步骤之2-图表源数据”对话框中， 显示相应的设置。
32
设置背景墙的填充效果
鼠标指向“背景墙”，单击鼠标右键，在弹 出的快捷菜单中选择“背景墙格式”命令， 打开“背景墙格式”对话框，在“图案”选 项卡中，单击“填充效果”按钮，打开“填 充效果”对话框。 选择“纹理”选项卡，在“纹理”列表框中， 选择“水滴”，如右图所示，单击“确定” 按钮。
33
课后练习
14
完成IF函数的使用
应用文写作 65 应用文写作 65 42 缺考 99 88 69 缺考 53 74 67 98 86 81 47 77 87 41 75 77
将“应用文写作”的成 绩在60分以上的设置为 “及格”，60分以下的 设置为“不及格” 提问：万一有人缺考怎 么办？
42 71 99 88 69 65 53 74 67 98 86 81 47 77 87 41 75 77
功能类似，都返回指定范围内单元格的个数 区别： COUNTA— 返回列表中非空单元格的个数， 单元格类型不限。 COUNT— 返回包含数字以及包含参数列表 中数字型单元格的个数。
6
讨论
课程 大学英语 计算机应用 高等数学 应用文写作 班级平均分
班级最高分
班级最低分
“应考人数”、“参考 人数”的统计用哪个函 数？为什么？ “缺考人数”怎么统计？
将图表标题设置为“幼圆、22号、蓝色、加粗”； 将图表区的填充效果设置为“羊皮纸”； 将背景墙的填充效果设置为“水滴”； 为图例添加“阴影边框”。
31
设置图表区的填充效果
在“图表”工具栏上的“图表对象”下拉列表 中，选择“图表区”，单击“图表”工具栏上 的“图表区格式”按钮，打开“图表区格式” 对话框， 在“图案”选项卡中，单击“填充效果”按钮， 打开“填充效果”对话框，在“渐变”选项卡 中，选择“预设”单选按钮，在“预设颜色” 下拉列表中，选择“羊皮纸”选项，在“底纹 样式”栏中，选择“中心辐射”单选项，单击 “确定”按钮。“填充效果”对话框的设置， 如右图所示。
打开“工资表(素材).xls”，并另 存为“工资表.xls”，参见教材第 178～181页，完成对工资表的数据 计算及图表绘制。
34
课后作业
统计函数COUNT与COUNTA的区别是什么？ 条件统计函数COUNTIF的应用场合是什么？其中的两个参 数各代表什么？ 在“各科成绩表”工作表中分别统计出男、女生人数应该用 什么函数？试写出函数的表达式。 IF函数有几个参数，每个参数的意义是什么？IF函数最多可 以有几层嵌套？ 选择图表的数据源时，如何选择不连续的数据区域？