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

1、Excel综合应用1成绩表统计与分析河南质量工程职业学院教案首页第7章节需4学时审阅人授课课题第7章Excel综合应用1一成绩表统计与分析学时4授课时间2008年11月5日星期三第1、2节知识目标统计函数COUNT、COUNTIF和逻辑判断函数IF的使用方法，条件格式的设置方法，图表的创建、修改能力目标熟练掌握统计函数COUNT、COUNTIF的使用方法，熟练掌握逻辑判断函数IF的使用方法，掌握条件格式的设置方法，熟练使用图表的创建、修改素质教育目标穿插上机，提高学生的动手能力教学重点统计函数COUNT、COUNTIF和逻辑判断函数IF的使用方法教学难点条件格式的设置方法授课方法多媒体教学仪器

2、投影仪，多媒体电脑教学反馈第73页第7章Excel综合应用1成绩表统计与分析7.1成绩表统计案例分析任务的提出班主任王老师要制作“成绩统计表”、“成绩等级表”及“成绩统计图。国解决方案7.2.1 用统计函数与公式制作“成绩统计表”跨工作表的单元格引用®打开“第7章素材”文件夹下的“统计表（素材）.xls”文件，选择“成绩统计表”工作表。e从“各科成绩表”中，将四门课程的“班级平均分”、“班级最高分”和“班级最低分”的数据引用到如右图所示的“成绩统计表”中的相应单元格中。统计函数COUNTA及COUNT的使用电将“各科成绩表”中各门课程的“应考人数”和“参考人数”的统计结果放置到“成绩

3、统计表”中的相应单元格中。争在“插入函数”对话框中，选择“统计”，在“选择函数”列表框中，选择“COUNTA”函数，单击“确定”按钮。争打开“函数参数”对话框。进行如下图所示的设置。.“COUNT”函数的设置类似（略）。条件统计函数COUNTIF的功能©COUNTIF函数的功能：统计指定区域内满足给定条件的单元格数目。色语法格式:COUNTIF（range,criteria）争其中：Range指定单元格区域）Criteria表示指定的条件表达式。争条件表达式的形式可以为数字、表达式或文本。例如，条件可以表示为60、"60"、”>=90"或"

4、;缺考"等。专用COUNTIF函数，将“各科成绩表”中各门课程的缺考人数以及各分数段人数的统计结果放置到“成绩统计表”中的相应单元格。.在“函数参数”对话框中，进行如下图所示的设置：编辑栏中的函数为=COUNTIF（各科成绩表！D2:D38,”缺考"）。各分数段人数的统计条件对应函数90-100（人）=COUNTIF（各科成绩表!D2:D38,">=90"80-89（人）=COUNTIF（各科成绩表!D2:D38,">=80"）-B970-79（人）=COUNTIF（各科成绩表!D2:D38,">=70&q

5、uot;）-B9-B1060-69（人）=COUNTIF（各科成绩表!D2:D38,">=60"）-B9-B10-B11=COUNTIF（各科成绩表!D2:D38,"<60"）59以卜（人）公式计算其在“成绩统计表”中，计算各门课程的及格率和优秀率。事计算及格率：COUNTIF（各科成绩表!D$2:D$38,">=60"）/COUNT（各科成绩表!D$2:D$38）或：1-B13/B7争计算优秀率：=COUNTIF（各科成绩表!D$2:D$38,">=90"）/COUNT（各科成绩表!D$2

6、:D$38）或：B9/B7思考题（一）念如何引用同一工作簿中不同工作表的数据？©,引用单元格的数据与复制粘贴数据有何区别？®统计函数COUNT与COUNTA的区别是什么？"条件统计函数COUNTIF的应用场合是什么？其中的两个参数各代表什么？1在各科成绩表”工作表中分别统计出男、女生人数应该用什么函数？试写出函数的表达式。单元格数据的删除与清除勤将“各科成绩表”工作表复制一份，并将复制后的工作表更名为“各科等级表”。在“各科等级表”中，清除“大学英语”、“计算机应用”、“高等数学”及“应用文写作”列中的分数内容，并清除“总分”、“名次”列的所有属性；删除“班级平均

7、分”、“班级最高分”、“班级最低分”所在单元格区域。7.2.2 用IF函数与条件格式制作“各科等级表”逻辑判断函数IF的功能©,IF函数的功能是：争判断给出的条件是否满足，如果满足返回一个值，如果不满足则返回另一个值。色语法格式：伞IF(logical_test,value_if_true,value_if_false)®共包括三个参数，其中：Logical_test逻辑判断表达式；船"value_if_true表达式为真时返回的值；$value_if_false表达式为假时返回的值。1利用If函数对“各科成绩表”中的“应用文写作”成绩在60分以上的，在“各科等级

8、表”中“应用文写作”的对应位置设置为“及格”，否则为“不及格”。申选择目标单元格G2,在“函数参数”对话框中进行如下图所示的设置。"按下表所示的分数与等级的对应关系，利用If嵌套函数对“各科成绩表”中的“大学英语”、“计算机应用”、“高等数学”三门课程的分数，在“各科等级表”中的对应科目中进行相应的等级设置。缺考缺考90二分数A80二分数90B70<=分数<80C60<=分数<70D分数60E1按下表所示的分数与等级的对应关系，利用If嵌套函数对“各科成绩表”中的“大学英语”、“计算机应用”、“高等数学”三门课程的分数，在“各科等级表”中的对应科目中进行相应的

9、等级设置。条件格式勤在“各科等级表”中，利用条件格式将所有“不及格”或所有等级为“E”的单元格设置成“黄色底纹红色、加粗字体”；将所有“缺考”的单元格设置为“浅青绿底纹梅红色、加粗字体”。在菜单栏中选择“格式”一“条件格式”命令，打开“条件格式”对话框，进行如下图所示的设置。只在“各科成绩表”中，将“标题”行及“班级平均分”行之间（不包括这两行）的偶数行数据区域设置为“冰蓝色底纹深蓝色字体”。在菜单栏中选择“格式”一“条件格式”命令，打开“条件格式”对话框，进行如下图所示的设置。思考题（二）©对单元格数据的清除和删除操作有何区别？®IF函数有几个参数，每个参数的意义是什么？

10、®IF函数最多可以有几层嵌套？电如何设置条件格式？问题分析®在下图所示的单元格中，有“分数”、“等级”两列数据，其中“等级”数据是根据“分数”按以下条件计算所得：条件分数等级分数>=90优秀90>分数>=80良好80>分数>=70中等70>分数>=60及格分数<60不及格e请分析以下表达式能否根据给定条件求出正确的结果？如果有错，请说明出错原因和改正方法。=if(A2>=90,优秀，if(90>A2>=80,良好，if(80>A2>=70,中等，if(70>A2>=60,及格,if(A

11、2<60,不及格,false)7.2.3用图表向导制作成绩统计图制作各科成绩统计图图表比数据更易于表达数据之间的关系以及数据变化的趋势。成绩统计图”可以直观地表现各门课程各分数段人数。使用图表向导”创建图表何在成绩统计表”中，根据各分数段人数及缺考人数制作图表。要求如下：事图表类型为“簇状柱形图”；争数据系列产生在“列”；伞图表标题为“成绩统计图”；伞分类（X）轴为“等级”；数值（Y）轴为“人数”；申将图表“作为其中的对象插入”到“成绩统计表”中。选择图表类型勤在菜单栏中选择插入“一图表”命令，打开图表向导-4步骤之1-图表类型”对话框，进行如右图所示的设置。1单击下一步"按钮

12、国表向导-4步舞之1 -图表美星团因在标送类型:定义类型 图表类型©图图图 达面 雷曲强线图 条拼馅图 和画卷下不煎可查看示例也）IM骸世小艰图口比较相交于类剧轴上梗 值大小盟消下一步® >完成。选择源数据勤按住Ctrl键的同时，分别在当前工作表中选择不连续的两个区域，如下图所示，并在图表向导-4步骤之2-图表源数据”对话框中，显示相应的设置设置图表选项其单击下一步”按钮,打开图表向导-4步骤之3-图表选项”对话框，并进行如下图所示的设置。完成笆取消 选择图表位置勤下一步”按钮，打开图表向导-4步骤之4-图表位置”对话框，进行如下图所示的设置。图表向导-4步甄之4-图

13、表位置'?|X|修改图表其在图表的制作过程中四步中的任何一步出错，都不必从头开始，只需单击图表，使图表处于激活状态，从图表”菜单中,选择对应的命令即可；也可单击图表后，单击鼠标右键，从快捷菜单中，选择对应的命令。修改图表要求在成绩统计表”工作表中，对图表进行如下修改：争将图表类型改为“三维簇状柱形图”；争数据系列产生在“行”，并从图表中删除缺考人数；守在图表中“显示数据表”；申将图表“作为新工作表插入”，并将新工作表命名为“成绩统计图”。操作步骤略图表的组成图衣色格式化图表1在成绩统计图”工作表中，对图表的外观进行如下修饰：争将图表标题设置为“幼圆、22号、蓝色、加粗”；争将图表区的填

14、充效果设置为“羊皮纸”；.将背景墙的填充效果设置为“水滴”；专为图例添加“阴影边框”。设置图表区的填充效果1在图表”工具栏上的图表对象”下拉列表中，选择图表区”，单击图表”工具栏上的图表区格式”按钮，打开图表区格式”对话框，勤在图案”选项卡中，单击填充效果”按钮，打开填充效果”对话框，在渐变”选项卡中，选择预设”单选按钮，在预设颜色”下拉列表中，选择羊皮纸”选项，在底纹样式”栏中，选择中心辐射”单选项，单击确定”按钮。填充效果”对话框的设置如右图所示。设置背景墙的填充效果勤鼠标指向背景墙”，单击鼠标右键，在弹出的快捷菜单中选择背景墙格式”命令，打开背景墙格式”对话框，在图案”选项卡中，单击填充

15、效果”按钮，打开填充效果”对话框。色选择纹理”选项卡，在纹理”列表框中，选择水滴”，如右图所示，单击确定”按钮。确定渐变 纹理蟠g取消图案 图片填充效果其他豉豆I水谪示例;思考题（二）1选择图表的数据源时，如何选择不连续的数据区域？如何修改图表？勤对已建立完成的图表，应如何添加图表标题”？对图表进行格式化时，应该如何操作？1将图表区”的填充效果设置为雨后初晴”，应如何操作？案例总结本章主要介绍了：伞统计函数COUNT、COUNTA、COUNTIF;电在使用COUNTI题数时应该注意，当公式需要复制时，如果参数“Ranged的引用区域固定不变，通常应使用绝对引用或区域命名方式实现；如果参数“Criteria”是表达式或字符串，必须在两边加上西文双引号争逻辑判断函数IF以及嵌套函数的使用；争当逻辑判断给出的条件多于两个时，通