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

Excel综合应用与分析

—成绩表统计1

河南质量工程职业学院教案首页

第 7 章 节 需 4 学时 授课课题 审阅人 学时 4 第7章 Excel综合应用1—成绩表统计与分析 授课时间 2008 年 11 月 5 日 星期 三 第1、2节 知识 目标 能力 目标 素质 教育 目标 教学重点 教学难点 授课方法 教学仪器 统计函数COUNT、COUNTIF和逻辑判断函数IF的使用方法 条件格式的设置方法 多媒体 投影仪，多媒体电脑 教学反馈 第 7 －2 页

统计函数COUNT、COUNTIF和逻辑判断函数IF的使用方法，条件格式的设置方法，图表的创建、修改 熟练掌握统计函数COUNT、COUNTIF的使用方法，熟练掌握逻辑判断函数IF的使用方法，掌握条件格式的设置方法，熟练使用图表的创建、修改 穿插上机，提高学生的动手能力

第 7 －3 页

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

7.1成绩表统计案例分析

任务的提出

班主任王老师要制作“成绩统计表”、“成绩等级表”及“成绩统计图。

解决方案

7.2.1 用统计函数与公式制作“成绩统计表” 跨工作表的单元格引用

打开“第7章素材”文件夹下的“统计表（素材）.xls”文件，选择“成绩统计表”工作表。

从“各科成绩表”中，将四门课程的“班级平均分”、“班级最高分”和“班级最低分”的数据引用到如右图所示的“成绩统计表”中的相应单元格中。 统计函数COUNTA及COUNT的使用

将“各科成绩表”中各门课程的“应考人数”和“参考人数”的统计结果放置到“成

第 7 －4 页

绩统计表”中的相应单元格中。

在“插入函数”对话框中，选择“统计”，在“选择函数”列表框中，选择“COUNTA”函数，单击“确定”按钮。 打开“函数参数”对话框。进行如下图所示的设置。

“COUNT”函数的设置类似（略）。 条件统计函数COUNTIF的功能

COUNTIF函数的功能：

统计指定区域内满足给定条件的单元格数目。

语法格式：

COUNTIF(range,criteria)

其中：Range 指定单元格区域，Criteria表示指定的条件表达式。

条件表达式的形式可以为数字、表达式或文本。例如，条件可以表示为 60、\"60\"、\">=90\" 或 \"缺考\"等。

第 7 －5 页

用COUNTIF函数，将“各科成绩表”中各门课程的缺考人数以及各分数段人数的统计结果放置到“成绩统计表”中的相应单元格 。

在“函数参数”对话框中，进行如下图所示的设置：编辑栏中的函数为 =COUNTIF(各科成绩表!D2:D38,\"缺考\")。

各分数段人数的统计 条件 对应函数 各各各各科科科科成成成成绩绩绩绩第 7 －6 页

90-100(人) =COUNTIF(80-89(人) =COUNTIF(70-79(人) =COUNTIF(60-69(人) =COUNTIF(表!D2:D38,\">=90\" 表!D2:D38,\">=80\")- B9 表!D2:D38,\">=70\")-B9-B10

表!D2:D38,\">=60\")-B9-B10-B11 59(人) 公式计算

在“成绩统计表”中，计算各门课程的及格率和优秀率。 计算及格率：

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

思考题（一）

如何引用同一工作簿中不同工作表的数

第 7 －7 页

以下=COUNTIF(各科成绩表!D2:D38,\"<60\")

据？

引用单元格的数据与复制粘贴数据有何区别？

统计函数COUNT与COUNTA的区别是什么？

条件统计函数COUNTIF的应用场合是什么？其中的两个参数各代表什么？ 在“各科成绩表”工作表中分别统计出男、女生人数应该用什么函数？试写出函数的表达式。

单元格数据的删除与清除

将“各科成绩表”工作表复制一份，并将复制后的工作表更名为“各科等级表”。在“各科等级表”中，清除“大学英语”、“计算机应用”、“高等数学”及“应用文写作”列中的分数内容，并清除“总分”、“名次”列的所有属性；删除“班级平均分”、“班级最高分”、“班级最低分”所在单元格区

第 7 －8 页

域。

7.2.2 用IF函数与条件格式制作“各科等级表” 逻辑判断函数IF的功能

IF函数的功能是：

判断给出的条件是否满足，如果满足返回一个值，如果不满足则返回另一个值。 语法格式：

IF(logical_test,value_if_true,value_if_f

alse)

共包括三个参数，其中：

Logical_test 逻辑判断表达式； value_if_true 表达式为真时返回的值；

value_if_false 表达式为假时返回的值。

利用If函数对“各科成绩表”中的“应用文写作”成绩在60分以上的，在“各科等

第 7 －9 页

级表”中“应用文写作”的对应位置设置为“及格”，否则为“不及格”。

选择目标单元格G2 ，在“函数参数”对话框中进行如下图所示的设置。

按下表所示的分数与等级的对应关系，利用If嵌套函数对“各科成绩表”中的“大学英语”、“计算机应用”、“高等数学”三门课程的分数，在“各科等级表”中的对应科目中进行相应的等级设置 。

分数 等级 第 7 －10 页

缺考 90<=分数 <90 缺考 A 80<=分数B 70<=分数C <80 60<=分数D <70 分数<60 E 按下表所示的分数与等级的对应关系，利用If嵌套函数对“各科成绩表”中的“大学英语”、“计算机应用”、“高等数学”三门课程的分数，在“各科等级表”中的对应科目中进行相应的等级设置 。 条件格式

在“各科等级表”中，利用条件格式将所有“不及格”或所有等级为“E”的单元格设置成“黄色底纹红色、加粗字体”；将所

第 7 －11 页

有“缺考”的单元格设置为“浅青绿底纹梅红色、加粗字体”。

在菜单栏中选择“格式”→“条件格式”命令，打开“条件格式”对话框 ，进行如下图所示的设置。

在“各科成绩表”中，将“标题”行及“班级平均分”行之间（不包括这两行）的偶数行数据区域设置为“冰蓝色底纹深蓝色字体”。

在菜单栏中选择“格式”→“条件格式”命令，

第 7 －12 页

打开“条件格式”对话框 ，进行如下图所示的设置。

思考题（二）

对单元格数据的清除和删除操作有何区别？

IF函数有几个参数，每个参数的意义是什么？

IF函数最多可以有几层嵌套？ 如何设置条件格式？ 问题分析

在下图所示的单元格中，有“分数”、“等级”两列数据，其中“等级”数据是根据“分数”按以下条件计算所得：

第 7 －13 页

条件 分数 分数>=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)))))

第 7 －14 页

7.2.3用图表向导制作成绩统计图 制作各科成绩统计图

图表比数据更易于表达数据之间的关系以及数据变化的趋势。 “成绩统计图”可以直观地表现各门课程各分数段人数。 使用“图表向导”创建图表

在“成绩统计表”中，根据各分数段人数及缺考人数制作图表。要求如下： 图表类型为“簇状柱形图”； 数据系列产生在“列”；

第 7 －15 页

图表标题为“成绩统计图”；

分类（X）轴为“等级”；数值（Y）轴为“人数”；

将图表“作为其中的对象插入”到“成绩统计表”中 。 选择图表类型

在菜单栏中选择“插入”→“图表”命令，打开“图表向导-4步骤之1-图表类型”对话框，进行如右图所示的设置。 单击“下一步”按钮

第 7 －16 页

选择源数据

按住Ctrl键的同时，分别在当前工作表中选择不连续的两个区域，如下图所示，并在“图表向导-4步骤之2-图表源数据”对话框中，显示相应的设置。

第 7 －17 页

设置图表选项

单击“下一步”按钮，打开“图表向导-4步骤之3-图表选项”对话框，并进行如下图所示的设置。

第 7 －18 页

选择图表位置

“下一步”按钮，打开“图表向导-4步骤之 4-图表位置”对话框， 进行如下图所示的设置。

修改图表

在图表的制作过程中四步中的任何一步出错，都不必从头开始，只需单击图表，使图表处于激活状态，从 “图表”菜单中，选择对应的命令即可；也可单击图表后，单击鼠标右键，从快捷菜单中，选择对应的命令。 修改图表要求

第 7 －19 页

在“成绩统计表”工作表中，对图表进行如下修改：

将图表类型改为“三维簇状柱形图”； 数据系列产生在“行”，并从图表中删除缺考人数；

在图表中“显示数据表”；

将图表“作为新工作表插入”，并将新工作表命名为“成绩统计图”。 操作步骤略 图表的组成

第 7 －20 页

格式化图表

在“成绩统计图”工作表中，对图表的外观进行如下修饰：

将图表标题设置为“幼圆、22号、蓝色、加粗”；

将图表区的填充效果设置为“羊皮纸”； 将背景墙的填充效果设置为“水滴”； 为图例添加“阴影边框”。 设置图表区的填充效果

在“图表”工具栏上的“图表对象”下拉列表中，选择“图表区”，单击“图表”工具栏上的“图表区格式”按钮，打开“图表区格式”对话框，

在“图案”选项卡中，单击“填充效果”按钮，打开“填充效果”对话框，在“渐变”选项卡中，选择“预设”单选按钮，在“预设颜色”下拉列表中，选择“羊皮纸”选项，在“底纹样式”栏中，选择“中心辐射”单选项，单

第 7 －21 页

击“确定”按钮。“填充效果”对话框的设置，如右图所示。

设置背景墙的填充效果

鼠标指向“背景墙”，单击鼠标右键，在弹出的快捷菜单中选择“背景墙格式”命令，打开“背景墙格式”对话框，在“图案”选项卡中，单击“填充效果”按钮，打开“填充效

第 7 －22 页

果”对话框。

选择“纹理”选项卡，在“纹理”列表框中，选择“水滴”，如右图所示，单击“确定”按钮。

思考题（三）

选择图表的数据源时，如何选择不连续的

第 7 －23 页

数据区域？ 如何修改图表？

对已建立完成的图表，应如何添加“图表标题”？

对图表进行格式化时，应该如何操作？ 将“图表区”的填充效果设置为“雨后初晴”，应如何操作？

案例总结 本章主要介绍了：

统计函数COUNT、COUNTA、COUNTIF；

在使用COUNTIF函数时应该注意，当公式需要复制时，如果参数“Range”的引用区域固定不变，通常应使用绝对引用或区域命名方式实现；如果参数“Criteria”是表达式或字符串，必须在两边加上西文双引号。

第 7 －24 页

逻辑判断函数IF以及嵌套函数的使用； 当逻辑判断给出的条件多于两个时，通常采用IF函数的嵌套。在使用IF函数时，应注意函数多层嵌套时的括号匹配，并且公式中的所有符号必须为西文字符。 设置条件格式；

图表的创建、修改及格式化 特别要注意正确选择源数据。 在图表的制作过程中，“图表类型”、“源数据”、“图表选项”、“位置”四步中的任何一步出错都不必重新开始，只要选定图表使之处于激活状态，就可以在“图表”菜单中选择对应的命令进行修改。

课后练习

第 7 －25 页

打开“工资表(素材).xls”，并另存为“工资表.xls”，参见教材第178～181页，完成对工资表的数据计算及图表绘制。

第 7 －26 页