成绩表统计与分析.doc

EXCEL实训课件2 ——成绩表统计与分析 任务一、 利用公式与函数制作“各科成绩统计表” 图1 1．准备工作 请打开“统计表（素材）.xls”工作簿，将文件另存为你自己学号后2位+姓名（统计表），如“18张三（统计表）.xls”，选择“成绩统计表”工作表，参见“统计表（样例）.xls”，并参考教材P158～P162，完成各项操作。 2．跨工作表的单元格引用 从“各科成绩表”中，将四门课程的“班级平均分”、“班级最高分”和“班级最低分”的数据引用到“成绩统计表”中的相应单元格中。 (注意 在“成绩统计表”中，四门课程的排列顺序必须与被引用的工作表“各科成绩表”中四门课程的排列顺序相同。 3．统计函数COUNT及COUNTA的使用 将“各科成绩表”中各门课程的“应考人数”和“参考人数”的统计结果放置到“成绩统计表”中的相应单元格中。 4．条件统计函数COUNTIF的使用（重点） 用COUNTIF函数将“各科成绩表”中各门课程的“缺考人数”以及各分数段人数的统计结果放置到“成绩统计表”中的相应单元格中。 5．公式计算 在“成绩统计表”中，计算各门课程的及格率和优秀率。 其中： 及格率=60分以上的人数/参考总人数； 优秀率=90分以上的人数/参考总人数； (要点回顾 1．如何引用同一工作簿中不同工作表的数据？ 2．引用单元格的数据与复制粘贴数据有何区别？ 3．统计函数COUNT与COUNTA的区别是什么？ 4．条件统计函数COUNTIF的应用场合是什么？其中的两个参数各代表什么？ 5．在“各科成绩表”工作表中分别统计出男、女生人数应该用什么函数？试写出函数的表达式。 任务二、制作“各科等级表” 图2 请参见P162～P169，完成对“各科等级表”工作表的操作，要求如下： 1．复制工作表 将“各科成绩表”工作表复制一份，并将复制后的工作表“各科成绩表 (2)”更名为“各科等级表”。 2．单元格数据的删除与清除 清除“大学英语”、“计算机应用”、“高等数学”以及“应用文写作”列中的分数内容，并清除“总分”、“名次”列的所有属性；删除“班级平均分”、“班级最高分”、“班级最低分”所在单元格区域。 3．逻辑判断函数IF的使用（重点、难点） （1）利用If函数对“各科成绩表”中的“应用文写作”成绩在60分以上的，在“各科等级表”中“应用文写作”的对应位置，设置为“及格”，否则为“不及格”。 （2）在（1）的基础上，利用If嵌套函数对“各科成绩表”中的“应用文写作”添加一个条件：对于“缺考”的考生，仍设置为“缺考”。 （3）利用If嵌套函数对“各科成绩表”中的“大学英语”、“计算机应用”、“高等数学”三门课的成绩，在“各科等级表”中的对应科目中进行相应的等级设置，要求如下： 成绩在90分以上（含90分，下同）的设置为“A”； 成绩在90分以下，80分以上的设置为“B”； 成绩在80分以下，70分以上的设置为“C”； 成绩在70分以下，60分以上的设置为“D”； 成绩在60分以下的设置为“E”； 对于没有成绩的（即“缺考”的考生）仍设置为“缺考”。 4．条件格式 （1）在“各科等级表”中，利用条件格式将“不及格”或等级为“E”的单元格设置成“黄色底纹红色加粗字体”；将“缺考”的单元格设置为“浅青绿底纹梅红色加粗字体”。 （2）在“各科成绩表”中，将“标题”行及“班级平均分”行之间（不包括这两行）的偶数行数据区域设置为“冰蓝色底纹深蓝色字体”（选做） (要点回顾 1．对单元格数据的清除和删除操作有何区别？ 2．IF函数有几个参数，每个参数的意义是什么？ 3．IF函数最多可以有几层嵌套？ 4．如何设置条件格式？ (问题辨析 在下图所示的单元格中，有“分数”、“等级”两列数据，其中“等级”数据是根据“分数”按以下条件计算所得： 条件 分数 等级 分数=90 优秀 90分数=80 良好 80分数=70 中等 70分数=60 及格 分数60 不及格 请分析以下表达式能否根据给定条件求出正确的结果？如果有错，请说明出错原因和改正方法。 =if(A2=90,优秀,if(90A2=80,良好,if(80A2=70,中等,if(70A2=60,及格,if(A260,不及格,false))))) 任务三、制作各科成绩统计图 图3 请参见“统计表（样例）.xls”中的“成绩统计图”工作表，并参阅P169～P173，完成以下操作： 1．使用“图表向导”创建图表 在“成绩统计表”中，根据各分数段人数及缺考人数制作图表，要求如下： 图表类型为“簇状柱形图”； 数据系列产生在“列”； 图表标题为“成绩统计图”； 分类（X）轴为“等级”、数值（Y）轴为“人数”； 将图表“作为其中的对象插入”到“成绩统计表”中。 2．修改图表 在“成绩统计表”