2015综合应用成绩表的统计与分析.pptVIP

1 Excel 综合应用——成绩表的统计与分析 知识点 统计函数COUNT、COUNTA、COUNTIF 逻辑判断函数IF 条件格式 EXCEL中的图表 任务一 从“各科成绩表”中，将四门课程的“班级平均分”、“班级最高分”、“班级最低分”的的数据引用到“成绩统计表的相应单元格中。 跨工作表的单元格引用 引用方法一：选择性粘贴 在源表复制数据，打开目标文件单元格，选择性粘贴，粘贴链接。 引用方法二：使用命令 在目标单元格输入 =[excel文件名]工作表名!单元格地址 注意：标点都是在英文状态下输入的。 或者 直接在单元格里输入“=”后面直接点击要引用的单元格就行。 任务一 打开“统计表（素材）”工作簿，从“各科成绩表”中，将四门课程的“班级平均分”、“班级最高分”、“班级最低分”的的数据引用到“成绩统计表的相应单元格中。 ★可用填充柄复制其他三门课程的“平均分”、 “最高分”、“最低分”。前提：两张表中四门课程的排列顺序一致 统计函数COUNTA及COUNT 功能类似，都返回指定范围内单元格的个数 区别： COUNTA— 返回列表中非空单元格的个数，单元格类型不限。 COUNT— 返回包含数字以及包含参数列表中数字型单元格的个数。 讨论 “应考人数”、“参考人数”的统计用哪个函数？为什么？ “缺考人数”怎么统计？ 条件统计函数COUNTIF 作用 用来统计指定区域中满足给定条件的单元格的个数。 语法讲解 　　COUNTIF(range,criteria)　 　　Range 为需要计算其中满足条件的单元格数目的单元格区域，即（范围）。 　　Criteria 为指定的条件，其形式可以为数字、表达式或文本，即（条件）。 注意：如果条件是表达式或者字符串，则两边要加上西文双引号。 讨论 “成绩统计表”中有哪些数据需要用“COUNTIF”函数来统计出来？ 公式计算 在“成绩统计表”中，计算各门成绩的及格率和优秀率 计算及格率 ： COUNTIF(各科成绩表!D2:D38,=60)/COUNT(各科成绩表!D2:D38) 或：1-B13/B7 计算优秀率 ： =COUNTIF(各科成绩表!D2:D38,=90)/COUNT(各科成绩表!D2:D38) 或：B9/B7 练习 打开“统计表（素材）”完成数据的引入或计算。 “及格率”和“优秀率”保留两位小数，并设置成百分比格式。 任务二 用IF函数制作“各科等级表”，将卷面分数转化为等级 IF函数的使用 准备工作 （1）将“各科成绩表”复制一份，更名为“各科等级表”， （2）清除“大学英语”、“计算机应用”、“高等数学”及“应用文写作”列的分数值； （3）删除“总分”、“名次”列以及“班级平均分”、“班级最高分”、“班级最低分”行 IF函数的嵌套 格式： =IF(条件表达式1,值1,IF(条件表达式2,值2,值3))?????? 或 =IF(条件表达式1, IF(条件表达式2,值1,值2),值3) 完成：把白庆辉、赵文静两人的成绩改成“缺考”，用IF函数统计三种情况。 IF函数的嵌套举例 =IF(各科成绩表!G5=缺考,缺考,IF(各科成绩表!G5=60,及格,不及格)) 讨论 大学英语、计算机应用、高等数学列的等级如何用IF函数来完成？ 条件 =IF(G260,E,IF(G270,D,IF(G280,C,IF(G290,B,IF(G2=100,A,缺考))))) 练习 按照课件P17 “分数与等级的对应关系表”，利用IF函数的嵌套，将大学英语、计算机应用、高等数学列的等级设置好。 在“各科等级表”中，利用条件格式将所有的“不及格”或等级为E的单元格设置成“黄色底纹红色加粗字体”；将所有“缺考”的单元格设置成“浅绿色底纹玫红色加粗字体” 条件格式中的公式 公式必须以“=”开始，只有返回值是真时，指定格式才应用于选定的单元格。 例：=MOD（ROW（）,2）=0 MOD为求余函数，返回两数相除的余数 ROW（）函数用于测试当前行号 练习：在“各科成绩表”中，将“标题”行及“班级平均分”行之间（不包括这两行）的偶数行数据区域设置为“冰蓝色底纹深蓝色字体” 。 任务三、制作各科成绩统计图 图表比数据更易于表达数据之间的关系以及数据变化的趋势。 “成绩统计图”可以直观地表现各门课程各分数段人数。 使用“图表向导”创建图表 在“成绩统计表”中，根据各分数段人数及缺考人数制作图表。要求如下： 图表类型为“簇状柱形图”； 数据系列产生在“列”； 图表标题为“成绩统计