Excel应用精粹教学课件06成绩分析与处理

1 LOGO信息处理与分析Excel应用精粹 2 目录页 CONTENTS PAGE 一制作成绩统计表二 制作成绩分析表项目六 成绩分析与处理任务Contents Page目录页 过渡页 TRANSITION PAGE 3 掌握数据分析的基本方法。掌握数据透视表在汇总报表中的应用。掌握利用Word的查找和替换功能制作文本短消息的方法。了解图表的组成元素与特点。掌握制作简单图表并对其进行编辑的方法。掌握对数据进行排序的方法。掌握排名和统计函数的使用方法。知识技能目标Contents Page目录页 过渡页 TRANSITION PAGE 4 项目流程确定成绩报表要素制作成绩汇总表制作成绩分析图表Contents Page目录页 过渡页 TRANSITION PAGE 5 一、数据分析二、处理数据透视表的方法制作成绩统计表任务一知识窗知识窗知识窗知识窗三、Word的查找和替换四、排名函数RANK.EQ6 LOGO知识窗知识窗本次任务的目标是对成绩流水数据进行综合分析后，以班级为单位生成各班成绩报表，完成成绩的汇总与年级排名。任务一制作成绩统计表7 LOGO知识窗知识窗任务一数据分析是指用适当的统计、分析方法对收集的大量数据进行分析，将它们加以汇总、理解并消化，以求最大化地开发数据的功能，发挥数据的作用。数据分析的目的是把隐藏在一大批看似杂乱无章的数据背后的信息集中和提炼出来，总结出所研究对象的内在规律。在实际工作中，数据分析能够帮助管理者进行判断和决策，以便采取适当策略与行动。数据分析的过程主要包括6个既相对独立又互有联系的阶段。它们是：明确分析目的和思路、数据收集、数据处理、数据分析、数据展现、报告撰写等6步。一、数据分析制作成绩统计表8 LOGO知识窗知识窗任务一1浏览数据u展开和折叠数据，并显示与值有关的基本明细。u对字段和选项进行排序、筛选和分组。u更改汇总函数，并且添加自定义计算和公式。2更改窗体布局和字段排列u更改数据透视表的显示形式：如以压缩、大纲或表格的形式显示。u添加、重新排列和删除字段。u更改字段或项的顺序。二、处理数据透视表的方法制作成绩统计表9 LOGO知识窗知识窗任务一3更改列、行和分类汇总的布局u打开或关闭列和行字段标题，或者显示或隐藏空行。u在其行上方或下方显示分类汇总。u刷新时调整列宽。u将列字段移动到行区域或将行字段移动到列区域。u为外部行和列项合并单元格或取消单元格合并。二、处理数据透视表的方法制作成绩统计表10 LOGO知识窗知识窗任务一4更改空白和错误单元格的显示方式u更改错误和空单元格的显示方式。u更改不含数据的项和标签的显示方式。u显示或隐藏空白行。5更改格式u对单元格和单元格区域进行手动和有条件的格式设置。u更改整个数据透视表的样式。u更改字段的数字格式。二、处理数据透视表的方法制作成绩统计表11 LOGO知识窗知识窗任务一Word中的查找和替换功能最常用的是用它来查找和替换文字。实际上，它还可用来查找和替换格式、段落标记、分页符和其他项目，并且还可以使用通配符和代码来扩展搜索。单击“开始”选项卡上“编辑”组中的“查找”或“替换”按钮，打开“查找和替换”对话框。单击“更多”按钮，展开对话框。三、Word的查找与替换制作成绩统计表12 LOGO知识窗知识窗任务一在“查找内容”编辑框中，可执行下列操作之一：若只搜索文字，而不考虑特定的格式，则直接输入文字。若要搜索带有特定格式的文字，可先输入文字，再单击“格式”按钮，在展开的列表中选择格式类型，再在打开的对话框中进行设置即可。若只搜索特定格式的文字，可删除所有文字，然后单击“格式”按钮，在展开的列表中选择格式类型，再在打开的对话框中进行设置即可。三、Word的查找与替换制作成绩统计表13 LOGO知识窗知识窗任务一 若要从列表中选择项目，如查找和替换段落标记、分页符和其他项目等，则单击“特殊格式”按钮，然后在展开的列表中单击所需项目。设置完毕，单击“查找”或“查找下一处”按钮。若要替换该内容，可在“替换为”编辑框中输入替换内容，同样可以为替换内容设置格式，或替换为特殊格式，然后单击“查找下一处”、“替换”或“全部替换”按钮。若选中“使用通配符”复选框，Word只查找与指定文本精确匹配的文本。其中问号“？”代表任意一个字符；“*”号代表任意多个字符。三、Word的查找与替换制作成绩统计表14 LOGO知识窗知识窗任务一RANK.EQ函数的功能是返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）。语法：RANK.EQ(number,ref,order)Number 必需。需要找到排位的数字。Ref 必需。数字列表数组或对数字列表的引用。Ref 中的非数值型值将被忽略。Order 可选。一数字，指明数字排位的方式。如果order为0（零）或省略，Excel对数字的排位是基于ref为按照降序排列的列表；如果order 不为零，Excel对数字的排位是基于ref为按照升序排列的列表。四、排名函数RANK.EQ制作成绩统计表15 LOGO操作步骤操作步骤步骤步骤1 1打开本书配套素材“素材与实例”“项目六”“成绩数据”工作簿，然后将其另存为“成绩统计与分析”。一、按年级和专业制作成绩报表任务一制作成绩统计表16 LOGO操作步骤操作步骤步骤步骤2 2单击数据工作表中的任意单元格，然后单击“数据”选项卡“排序和筛选”组中的“筛选”按钮，再单击“年级”右侧的筛选按钮，展开列表，单击“全选”复选框，取消对所有数据的选中。对年级进行筛选，以施工专业为例，在列表中选中“11级春施工”专业的所有班级（需向下拖动滚动条进行选择），然后单击“确定”按钮，在“班级”列中筛选年级数据。一、按年级和专业制作成绩报表任务一制作成绩统计表17 LOGO操作步骤操作步骤步骤步骤3 3将“Sheet2”重命名为“11级成绩”，对当前筛选的数据进行复制，再粘贴到该工作表中。一、按年级和专业制作成绩报表任务一制作成绩统计表18 LOGO操作步骤操作步骤步骤步骤4 4在“11级成绩”工作表单击任意一个有数据的单元格，再单击“插入”选项卡“表格“组中的“数据透视表”按钮，打开“创建数据透视表”对话框，选择相应的数据区域，同时指定存放数据透视表的位置，此处保持默认。一、按年级和专业制作成绩报表任务一制作成绩统计表19 LOGO操作步骤操作步骤步骤步骤5 5单击“确定”按钮，在新工作表中插入一个新的数据透视表框架。一、按年级和专业制作成绩报表任务一制作成绩统计表20 LOGO操作步骤操作步骤步骤步骤6 6拖动“考号”字段到“行标签”区域，“科目”到“列标签”区域，“成绩”到“数值”区域，完成数据透视表的布局。在数据透视表外单击，完成数据透视表的创建。一、按年级和专业制作成绩报表任务一制作成绩统计表21 LOGO操作步骤操作步骤步骤步骤7 7将“Sheet3”重命名为“11级施工成绩报表”，把数据透视表中第4行到第481行中的所有数据复制，以“值”的方式粘贴到该工作表中。一、按年级和专业制作成绩报表任务一制作成绩统计表22 LOGO操作步骤操作步骤步骤步骤8 8将A1单元格内容改为“考号”，在B列前插入一列，输入“姓名”，将G1单元格内容改为“总分”，在H1单元格输入“年级排名”。一、按年级和专业制作成绩报表任务一制作成绩统计表23 LOGO操作步骤操作步骤步骤步骤9 9由于报表中考号数据和姓名数据顺序不一致，需要用VLOOKUP函数实现逆向查找姓名。为此，单击B2单元格后单击“公式”选项卡“函数库”组中的“查找和引用”按钮，在展开的列表中选择“VLOOKUP”函数，打开“函数参数”对话框，编辑公式“=VLOOKUP(A2,IF(1,0,11级成绩!$C$2:$C$2474,11级成绩!$B$2:$B$2474),2,0)”，即分别在各参数编辑框中输入函数参数。一、按年级和专业制作成绩报表任务一制作成绩统计表24 LOGO操作步骤操作步骤步骤步骤1010单击“确定”按钮后双击B2单元格右下角的填充柄，复制公式对所有数据进行引用，得到结果。一、按年级和专业制作成绩报表任务一制作成绩统计表25 LOGO操作步骤操作步骤步骤步骤1111定位到H2单元格，然后单击“公式”选项卡“函数库”组中的“其他函数”按钮，在展开的列表中选择“统计”“RANK.EQ”函数，打开“函数参数”对话框，编辑公式“=RANK.EQ(G2,$G$2:$G$478)”，按“总分”进行年级排名。一、按年级和专业制作成绩报表任务一制作成绩统计表26 LOGO操作步骤操作步骤步骤步骤1212单击“确定”按钮后双击H2单元格右下角的填充柄，复制公式对所有数据进行引用。一、按年级和专业制作成绩报表任务一制作成绩统计表27 LOGO操作步骤操作步骤步骤步骤1 1新建“短信成绩”工作表，把“11级施工成绩报表”中除“考号”列外的数据复制，即B1:H478单元格区域，并将其以“值”的方式粘贴到“成绩短信”工作表中。二、制作基于文本的成绩发送短信表任务一制作成绩统计表28 LOGO操作步骤操作步骤步骤步骤2 2在B列前插入一新列，并把“计量计价”文本从C1单元格移到B2单元格，然后双击单元格右下角的填充柄进行快速填充。二、制作基于文本的成绩发送短信表任务一制作成绩统计表29 LOGO操作步骤操作步骤步骤步骤2 2用同样的方法，在此后各列前如B列样设置并填充，然后调整各列的宽度为最合适。二、制作基于文本的成绩发送短信表任务一制作成绩统计表30 LOGO操作步骤操作步骤步骤步骤3 3新建“学生成绩通知”Word文档，单击“页面布局”选项卡“页面设置”组中的“页边距”按钮，在展开的列表中选择“适中”项。二、制作基于文本的成绩发送短信表任务一制作成绩统计表31 LOGO操作步骤操作步骤步骤步骤4 4在Excel中复制“短信成绩”工作表除第1行外的所有数据，将其以文本格式粘贴到Word中。二、制作基于文本的成绩发送短信表任务一制作成绩统计表32 LOGO操作步骤操作步骤步骤步骤5 5单击“开始”选项卡“编辑”组中的“替换”按钮，打开“查找和替换”对话框并显示“替换”选项卡，单击“更多”按钮展开对话框。在“查找内容”编辑框中单击，然后单击“特殊格式”按钮，在打开的列表选择“制表符”项，再在“替换为”编辑框中输入“：”。二、制作基于文本的成绩发送短信表任务一制作成绩统计表33 LOGO操作步骤操作步骤步骤步骤6 6单击“全部替换”按钮，替换完毕，在打开的提示对话框中单击“确定”按钮，退出“查找和替换”对话框，结果如图6-21所示。老师可以将替换后的文本复制到手机或短信平台中进行发送，至此，完成学生成绩通知的发布工作。二、制作基于文本的成绩发送短信表任务一制作成绩统计表Contents Page目录页 过渡页 TRANSITION PAGE 34 一、统计函数二、Excel图表制作成绩分析表任务二知识窗知识窗知识窗知识窗35 LOGO知识窗知识窗本任务的目标是对各班成绩进行分析，并对各分数段进行统计，然后生成相应的统计图表，便于调整教学，找出问题所在。任务二制作成绩分析表36 LOGO知识窗知识窗1COUNTIF函数COUNTIF函数的功能是对区域中满足单个指定条件的单元格进行计数。例如，可以对以某一字母开头的所有单元格进行计数，也可以对大于或小于某一指定数字的所有单元格进行计数。语法：COUNTIF(range,criteria)range 必需。要对其进行计数的一个或多个单元格，其中包括数字或名称、数组或包含数字的引用。空值和文本值将被忽略。criteria 必需。用于定义将对哪些单元格进行计数的数字、表达式、单元格引用或文本字符串。一、统计函数任务二制作成绩分析表37 LOGO知识窗知识窗2MAX函数MAX函数的功能是返回一组值中的最大值。语法：MAX(number1,number2,)Number1,number2,Number1是必需的，后续数值是可选的。这些是要从