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

《Excel综合应用1——成绩表的统计与分析.ppt》由会员分享，可在线阅读，更多相关《Excel综合应用1——成绩表的统计与分析.ppt（35页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Excel 综合应用综合应用成绩表的统计与分析成绩表的统计与分析1知识点统计函数COUNT、COUNTA、COUNTIF逻辑判断函数IF条件格式EXCEL中的图表2任务一从“各科成绩表”中，将四门课程的“班级平均分”、“班级最高分”、“班级最低分”的的数据引用到“成绩统计表的相应单元格中。成绩统计表课程大学英语计算机应用高等数学应用文写作班级平均分班级最高分班级最低分应考人数参考人数缺考人数90-100(人)80-89(人)70-79(人)60-69(人)小于60(人)及格率优秀率3跨工作表的单元格引用引用方法一：选择性粘贴在源表复制数据，打开目标文件单元格，选择性在源表复制数据，打开目标文件

2、单元格，选择性粘贴，粘贴链接。粘贴，粘贴链接。引用方法二：使用命令在目标单元格输入在目标单元格输入 =excel文件名文件名工作表名工作表名!单元格地址单元格地址 注意：标点都是在英文状态下输入的。注意：标点都是在英文状态下输入的。或者或者 直接在单元格里输入直接在单元格里输入“=”后面直接点击要引后面直接点击要引用的单元格就行。用的单元格就行。4任务一打开“统计表（素材）”工作簿，从“各科成绩表”中，将四门课程的“班级平均分”、“班级最高分”、“班级最低分”的的数据引用到“成绩统计表的相应单元格中。可用填充柄复制其他三门课程的“平均分”、“最高分”、“最低分”。前提：两张表中四门课程的排列顺

3、序一致5统计函数COUNTA及COUNT功能类似，都返回指定范围内单元格的个数区别：COUNTA返回列表中非空单元格的个数，单元格类型不限。COUNT返回包含数字以及包含参数列表中数字型单元格的个数。6讨论课程大学英语计算机应用高等数学应用文写作班级平均分班级最高分班级最低分应考人数参考人数缺考人数“应考人数”、“参考人数”的统计用哪个函数？为什么？“缺考人数”怎么统计？7条件统计函数COUNTIF作用作用用来统计指定区域中满足给定条件的单元格的个数。语法讲解语法讲解COUNTIF(range,criteria)Range为需要计算其中满足条件的单元格数目的单元格区域，即（范围）。Criter

4、ia为指定的条件，其形式可以为数字、表达式或文本，即（条件）。注意：如果条件是表达式或者字符串，则两边要加上西文双引号。8讨论“成绩统计表”中有哪些数据需要用“COUNTIF”函数来统计出来？成绩统计表课程大学英语计算机应用高等数学应用文写作班级平均分班级最高分班级最低分应考人数参考人数缺考人数90-100(人)80-89(人)70-79(人)60-69(人)小于60(人)及格率优秀率9公式计算在“成绩统计表”中，计算各门成绩的及格率和优秀率计算及格率：COUNTIF(各科成绩表各科成绩表!D2:D38,=60)/COUNT(各科成绩表各科成绩表!D2:D38)或：1-B13/B7计算优秀率：

5、=COUNTIF(各科成绩表各科成绩表!D2:D38,=90)/COUNT(各科成绩表各科成绩表!D2:D38)或：B9/B710练习打开“统计表（素材）”完成数据的引入或计算。“及格率”和“优秀率”保留两位小数，并设置成百分比格式。成绩统计表课程大学英语计算机应用高等数学应用文写作班级平均分班级最高分班级最低分应考人数参考人数缺考人数90-100(人)80-89(人)70-79(人)60-69(人)小于60(人)及格率优秀率11任务二用IF函数制作“各科等级表”，将卷面分数转化为等级12IF函数的使用IF函数的功能是：判断给出的条件是否满足，如果满足返回一个值，如果不满足则返回另一个值。语法

6、格式：IF(logical_test,value_if_true,value_if_false)共包括三个参数，其中：Logical_test逻辑判断表达式；value_if_true表达式为真时返回的值；value_if_false表达式为假时返回的值。13准备工作（1）将“各科成绩表”复制一份，更名为“各科等级表”，（2）清除“大学英语”、“计算机应用”、“高等数学”及“应用文写作”列的分数值；（3）删除“总分”、“名次”列以及“班级平均分”、“班级最高分”、“班级最低分”行14完成IF函数的使用将“应用文写作”的成绩在60分以上的设置为“及格”，60分以下的设置为“不及格”提问：万一有人

7、缺考怎么办？应用文写作65427199886965537467988681477787417577应用文写作6542缺考998869缺考53746798868147778741757715IF函数的嵌套格式：=IF(条件表达式1,值1,IF(条件表达式2,值2,值3)或=IF(条件表达式1,IF(条件表达式2,值1,值2),值3)完成：把白庆辉、赵文静两人的成绩改成“缺考”，用IF函数统计三种情况。16IF函数的嵌套举例=IF(各科成绩表!G5=缺考,缺考,IF(各科成绩表!G5=60,及格,不及格)17讨论大学英语、计算机应用、高等数学列的等级如何用IF函数来完成？分数等级分数等级缺考缺考7

8、0=分数=90A60=分数70D80=分数90B分数60E分数与等级的对应关系表18条件=IF(G260,E,IF(G270,D,IF(G280,C,IF(G290,B,IF(G2=100,A,缺考)19练习按照课件P17“分数与等级的对应关系表”，利用IF函数的嵌套，将大学英语、计算机应用、高等数学列的等级设置好。在“各科等级表”中，利用条件格式将所有的“不及格”或等级为E的单元格设置成“黄色底纹红色加粗字体”；将所有“缺考”的单元格设置成“浅绿色底纹玫红色加粗字体”20条件格式中的公式公式必须以“=”开始，只有返回值是真时，指定格式才应用于选定的单元格。例：=MOD（ROW（）,2）=0M

9、OD为求余函数，返回两数相除的余数ROW（）函数用于测试当前行号练习：在“各科成绩表”中，将“标题”行及“班级平均分”行之间（不包括这两行）的偶数行数据区域设置为“冰蓝色底纹深蓝色字体”。21任务三、制作各科成绩统计图图表比数据更易于表达数据之间的关系以及数据变化的趋势。“成绩统计图”可以直观地表现各门课程各分数段人数。22使用“图表向导”创建图表在“成绩统计表”中，根据各分数段人数及缺考人数制作图表。要求如下：图表类型为“簇状柱形图”；数据系列产生在“列”；图表标题为“成绩统计图”；分类（X）轴为“等级”；数值（Y）轴为“人数”；将图表“作为其中的对象插入”到“成绩统计表”中。23选择图表类

10、型在菜单栏中选择“插入”“图表”命令，打开“图表向导-4步骤之1-图表类型”对话框，进行如右图所示的设置。单击“下一步”按钮24选择源数据按住Ctrl键的同时，分别在当前工作表中选择不连续的两个区域，如下图所示，并在“图表向导-4步骤之2-图表源数据”对话框中，显示相应的设置。25设置图表选项单击“下一步”按钮，打开“图表向导-4步骤之3-图表选项”对话框，并进行如下图所示的设置。26选择图表位置“下一步”按钮，打开“图表向导-4步骤之4-图表位置”对话框，进行如下图所示的设置。27修改图表在图表的制作过程中四步中的任何一步出错，都不必从头开始，只需单击图表，使图表处于激活状态，从“图表”菜单

11、中，选择对应的命令即可；也可单击图表后，单击鼠标右键，从快捷菜单中，选择对应的命令。28修改图表要求在“成绩统计表”工作表中，对图表进行如下修改：将图表类型改为“三维簇状柱形图”；数据系列产生在“行”，并从图表中删除缺考人数；在图表中“显示数据表”；将图表“作为新工作表插入”，并将新工作表命名为“成绩统计图”。29图表的组成30格式化图表在“成绩统计图”工作表中，对图表的外观进行如下修饰：将图表标题设置为“幼圆、22号、蓝色、加粗”；将图表区的填充效果设置为“羊皮纸”；将背景墙的填充效果设置为“水滴”；为图例添加“阴影边框”。31设置图表区的填充效果在“图表”工具栏上的“图表对象”下拉列表中，

12、选择“图表区”，单击“图表”工具栏上的“图表区格式”按钮，打开“图表区格式”对话框，在“图案”选项卡中，单击“填充效果”按钮，打开“填充效果”对话框，在“渐变”选项卡中，选择“预设”单选按钮，在“预设颜色”下拉列表中，选择“羊皮纸”选项，在“底纹样式”栏中，选择“中心辐射”单选项，单击“确定”按钮。“填充效果”对话框的设置，如右图所示。32设置背景墙的填充效果鼠标指向“背景墙”，单击鼠标右键，在弹出的快捷菜单中选择“背景墙格式”命令，打开“背景墙格式”对话框，在“图案”选项卡中，单击“填充效果”按钮，打开“填充效果”对话框。选择“纹理”选项卡，在“纹理”列表框中，选择“水滴”，如右图所示，单击“确定”按钮。33课后练习打开“工资表(素材).xls”，并另存为“工资表.xls”，参见教材第178181页，完成对工资表的数据计算及图表绘制。34课后作业统计函数COUNT与COUNTA的区别是什么？条件统计函数COUNTIF的应用场合是什么？其中的两个参数各代表什么？在“各科成绩表”工作表中分别统计出男、女生人数应该用什么函数？试写出函数的表达式。IF函数有几个参数，每个参数的意义是什么？IF函数最多可以有几层嵌套？选择图表的数据源时，如何选择不连续的数据区域？35