学生成绩管理与分析教材.ppt

项目项目8 学生成绩管理与分析学生成绩管理与分析Ø会进行数据的选择性粘贴Ø会创建数据清单，会利用记录单对数据清单进行 编辑或条件查询Ø会设置工作表数据清单的样式Ø会设置数据清单快速排序和高级排序Ø会设置数据自动筛选和高级筛选Ø会设置数据的分类汇总Ø会设置数据透视表Ø会设置数据的图表分析 教学目标教学目标8.1 任务要求任务要求• 创建名为“0801班学生成绩管理分析.xls”的新工作簿，含“0801班学生成绩表”的数据清单，内容来源于项目七中的工作簿文件“08级计算机专业学生成绩表.xls”内的工作表“0801班”的相关内容能按单科总分或能按单科总分或总分进行排序，总分进行排序，快速找出单科总快速找出单科总分或总分排名情分或总分排名情况况 能按多个关键字段进行能按多个关键字段进行复合排序，如先按总分复合排序，如先按总分排序，如果总分相同，排序，如果总分相同，再按计算机基础分数排再按计算机基础分数排序等，即实现高级排序序等，即实现高级排序 根据指定条件根据指定条件对数据进行筛对数据进行筛选，查询符合选，查询符合条件的记录，条件的记录，如筛选出总分如筛选出总分大于大于300300分的分的记录记录 能进行复合筛选能进行复合筛选 ，如筛选总分大于，如筛选总分大于300300的的A A组学生组学生 能按指定字段进行数据分类汇总，如按学习小组分析能按指定字段进行数据分类汇总，如按学习小组分析各组总分平均分、单科平均分等，还能对多个字段进各组总分平均分、单科平均分等，还能对多个字段进行分类汇总行分类汇总 能将总分前五名的学生成绩按照单科总分、总分制作能将总分前五名的学生成绩按照单科总分、总分制作柱形图表柱形图表 8.2 解决方案解决方案 创建名为“0801班学生成绩管理分析.xls”的新工作簿，创建5个工作表，分别命名为“0801班学生成绩数据清单”、“排序”、“筛选”、“汇总”和“图表”等，其中第一个工作表的数据来自于项目七使用的对象文件，故可打开该文件，把相关内容复制粘贴到目标工作表中，因项目七中工作簿的许多数据是通过公式计算生成的，如果直接复制粘贴将会显示错误信息，在此操作过程中必须使用数据的选择性粘贴解决粘贴出错问题。

数据清单创建后，使用自动套用样式和记录单进行样式设置和编辑查询，另外还用到前面项目中已学习的工作表复制、工作表标签重命名等操作； 按单科或总分的排序，能进行快速排序和高级排序操作排序是数据处理常见的操作，使用Excel的数据排序功能可实现对单个字段排序或多个字段复合排序； 设置数据的筛选，能进行自动筛选或高级筛选操作在Excel中，提供了数据的自动筛选和高级筛选两种模式，使用它们实现操作； 对数据进行分类汇总和制作数据透视表； 对总分前五名学生按成绩制作柱形图，可以先按总分降序排列学生成绩，然后利用Excel中插入图表的操作向导逐步完成8.3 实现步骤实现步骤1．创建名为“0801班学生成绩管理分析.xls”的工作簿点击桌面【开始】菜单，点击【程序】|【Microsoft Office】|【Microsoft Office Excel 2003】命令，打开Excel 2003 单击菜单栏上的【文件】单击菜单栏上的【文件】| |【保存】命令，打【保存】命令，打开开““另存为另存为””对话框在文件名编辑栏内输入在文件名编辑栏内输入“0801“0801班学生成绩管理分析班学生成绩管理分析””，文，文件类型选择件类型选择ExcelExcel文件文件保存位置选择在保存位置选择在“D“D：：\ \我的文档我的文档””文件夹下。

文件夹下在工作表标签在工作表标签“Sheet1”“Sheet1”前前插入插入2 2个工作表个工作表重命名后图示重命名后图示依次将依次将5 5张工作表进行重命名张工作表进行重命名2．创建工作表“0801班学生成绩数据清单” 在工作表在工作表“0801“0801班学生成绩数据清单班学生成绩数据清单””的单元格区域的单元格区域A1A1：：H1H1内，输入标题内，输入标题行行 打开打开“08“08级级计算机专业计算机专业学生成绩表学生成绩表.xls”.xls”工作工作簿，选定工簿，选定工作表作表“0801“0801班班””中的单中的单元格区域元格区域A4:B33A4:B33，并，并复制 在工作表在工作表“0801“0801班学班学生成绩数据清单生成绩数据清单””中，中，用鼠标右键单击单元用鼠标右键单击单元格格A2A2，在弹出的菜单，在弹出的菜单中，点击【粘贴】命中，点击【粘贴】命令或按令或按Ctrl+VCtrl+V组合键，组合键， ““学号学号””和和““姓名姓名””的数据粘贴到单元格的数据粘贴到单元格区域区域A2:B31A2:B31中中 在在“08“08级级计算机专计算机专业学生成业学生成绩表绩表.xls”.xls”，， 复制工作复制工作表表“0801“0801班班””中的中的单元格区单元格区域域E4:E33E4:E33，即，即““计计算机基础算机基础””单科总单科总分分 。

在工作表在工作表“0801“0801班学生成绩数据清单班学生成绩数据清单””，选择单，选择单元格元格D2D2，单击鼠标右键，在弹出的菜单中，点击，单击鼠标右键，在弹出的菜单中，点击【选择性粘贴】命令，在【选择性粘贴】对话框【选择性粘贴】命令，在【选择性粘贴】对话框的【粘贴】选项栏中，系统默认选项为的【粘贴】选项栏中，系统默认选项为““全部全部””，选中，选中““数值数值””或或““值和数字格式值和数字格式””选项，设置选项，设置为粘贴数值单击【确定】按钮，完成为粘贴数值单击【确定】按钮，完成““计算机计算机基础基础””分数的复制粘贴分数的复制粘贴 为什么选中“数值”或“值和数字格式”选项，而不是选择“全部”呢？ 设置为选中“数值”或“值和数字格式”选项的原因是“08级计算机专业学生成绩表.xls”的工作表“0801班”中单元格区域E4:E33存储的是运算公式，这里只需要复制计算结果即分数，不需要具体的运算公式 如果使用【选择性粘贴】对话框的【粘贴】中的默认选项，会复制原单元格中存储的公式，公式被粘贴后在新表中重新计算，而运算对象单元格已不是数字，会显示出错信息。

使用“选择性粘贴”功能完成“计算机组装”、“大学英语”、“高等数学”和“总分”数据的复制粘贴 自动填充柄自动填充柄拖动C2的填充柄至C7 使用相同操作， 完成其他学习小组的数据录入选中数据区域选中数据区域A1A1：：H31H31，在，在格式工具栏内选择字体为格式工具栏内选择字体为宋体、字号宋体、字号1212磅，单击居磅，单击居中按钮，单击边框按钮，中按钮，单击边框按钮，设为无边框线，单击任意设为无边框线，单击任意单元格，取消全选状态单元格，取消全选状态 3．复制数据清单至工作表“排序”、“筛选”、“汇总”和“图表”中，以便进行数据的排序、筛选、汇总和创建图表等操作点击工作表点击工作表“0801“0801班班学生成绩数据清单学生成绩数据清单””中全选按钮，工作表中全选按钮，工作表区呈选定状态，在工区呈选定状态，在工作表区任一处单击鼠作表区任一处单击鼠标右键，在弹出的菜标右键，在弹出的菜单中，执行【复制】单中，执行【复制】命令命令 打开打开““排序排序””工作表，工作表，选中单元格选中单元格A1A1，单击，单击鼠标右键，在弹出的鼠标右键，在弹出的菜单中，执行【粘贴菜单中，执行【粘贴】命令。

命令 参照上述操作，依次将数据清单粘贴至 “筛选”、“汇总”和“图表”3个工作表4．设置工作表“0801班学生成绩数据清单”的套用样式选中工作表选中工作表“0801“0801班学生成绩数据清单班学生成绩数据清单””的单元格区域的单元格区域A1:H31A1:H31 在格式列表中，拖动在格式列表中，拖动滚动条，选择滚动条，选择““序列序列1”1”格式，单击【确格式，单击【确定】按钮定】按钮 将工作表“0801班学生成绩数据清单”格式设置为自动套用“序列1”格式 单击菜单栏中的菜单项【格式】单击菜单栏中的菜单项【格式】| |【自动套用格式】命令，打【自动套用格式】命令，打开【自动套用格式】对话框开【自动套用格式】对话框 5．利用“记录单”编辑数据清单单击工作表单击工作表“0801“0801班学生成绩数据清单班学生成绩数据清单””数据清单区域内的任一单元格数据清单区域内的任一单元格 在【记录单】对话框中，显在【记录单】对话框中，显示当前数据清单的首记录示当前数据清单的首记录 单击菜单栏上【数据】单击菜单栏上【数据】| |【记录单】命令，打开【记录单】对话框【记录单】命令，打开【记录单】对话框 。

单击【上一条】或【下一条】按单击【上一条】或【下一条】按钮或拖动垂直滚动条浏览数据清钮或拖动垂直滚动条浏览数据清单中的记录单中的记录 6.利用“记录单”进行条件查询单击【条件】按钮单击【条件】按钮输入条件输入条件在“记录单”中查询数据清单中C组总分超过300分的学生记录 单击【下一单击【下一条】按钮，条】按钮，显示出符合显示出符合条件的首记条件的首记录录 单击【下一单击【下一条】按钮，条】按钮，查看其他满查看其他满足条件的记足条件的记录录 单击【条件】按钮，启动重新查询，如果单击【关闭】按钮，关闭【记录单】对话框1．对工作表“排序”中的数据清单按“总分”字段设置为降序排列单击工作表单击工作表““排序排序””的的标签，打开标签，打开““排序排序””工工作表作表 单击菜单栏上的菜单项单击菜单栏上的菜单项【数据】【数据】| |【排序】命令【排序】命令在在““主要关主要关键字键字””栏中栏中选择选择““总分总分””，选择，选择““降序降序””方式，方式，在在““我的数我的数据区域据区域””设设置为置为““有标有标题行题行””，单，单击【确定】击【确定】按钮按钮 此时每条记录作为一个整体移动到相应的行。

其他方法：通过工具栏中的升序按钮和降序按钮进行快速排序 单击单击““总分总分””列中列中的任意数据单元格的任意数据单元格 单击降序按钮单击降序按钮 2．在工作表“排序”中，按“高等数学”列设置为降序排列，如果“高等数学”分数相同，再按照“计算机基础”降序排列，如果“计算机基础”分数也相同，再按照“学号”升序排列打开工作表打开工作表““排序排序””，选中数据区域的任意数据单元格，选中数据区域的任意数据单元格 在在““主要关键字主要关键字””栏中栏中选择选择““高等数学高等数学””，选，选择择““降序降序””方式，在方式，在““次要关键字次要关键字””选择选择““计计算机基础算机基础””，选择，选择““降降序序””方式，在方式，在““第三关第三关键字键字””选择选择““学号学号””，，选择选择““升序升序””方式在““我的数据区域我的数据区域””设置设置为为““有标题行有标题行”” 单击菜单栏中的菜单项【数据】单击菜单栏中的菜单项【数据】| |【排序】命令，打开【【排序】命令，打开【排序】对话框排序】对话框 2．在工作表“排序”中，按“高等数学”列设置为降序排列，如果“高等数学”分数相同，再按照“计算机基础”降序排列，如果“计算机基础”分数也相同，再按照“学号” 升序排列。

首先对以数字首先对以数字形式存储的数形式存储的数字进行排序，字进行排序，然后对以文本然后对以文本形式存储的数形式存储的数字进行排序字进行排序 在进行多条件的高级排序的操作过程中，弹出了【排序警告】对话框，这是因为工作表区域A列的学号是以文本形式存储的数字 数据数据类型1数字10数字11按文本存储的数字5按文本存储的数字数据数据类型1数字5按文本存储的数字10数字11按文本存储的数字同时对以文本同时对以文本和数字形式存和数字形式存储的数字进行储的数字进行排序因为A列的学号均为文本格式数字，故两种处理方式均可1．筛选出“总分>300”的数据记录单击工作表单击工作表““筛选筛选””的的标签，打开标签，打开““筛选筛选””工工作表作表 单击菜单栏上的菜单项单击菜单栏上的菜单项【数据】【数据】| |【筛选】｜【【筛选】｜【自动筛选】命令自动筛选】命令在数据清单中每一列的列标题右在数据清单中每一列的列标题右侧都会出现【自动筛选】下拉按侧都会出现【自动筛选】下拉按钮钮 ，表示进入了自动筛选状态，表示进入了自动筛选状态 单击单击““总分总分””字段旁的字段旁的下拉按钮，下拉按钮，弹出下拉框，弹出下拉框，在弹出的下在弹出的下拉框内选择拉框内选择““自定义自定义”” 。

总分总分””下拉列表选择下拉列表选择““大于大于””，后面的编辑，后面的编辑栏中输入栏中输入“>300”“>300” 筛选结果筛选结果 若筛选出“总分>300”的“王”姓学生的数据记录条件下拉列表选择条件下拉列表选择““等等于于””，编辑栏输入，编辑栏输入““王王*” *” 单击单击““姓名姓名””【自动筛选】下拉【自动筛选】下拉按钮按钮 ，选择，选择““自定义自定义”” 筛选结果筛选结果 2.筛选出总分大于300分的A组学生记录，在单元格区域A33：B34内录入筛选条件，筛选结果从单元格A36开始显示，原数据清单仍显示 在空白单元在空白单元格区域格区域A33:B34A33:B34内输内输入筛选条件入筛选条件单击菜单栏中【单击菜单栏中【数据】数据】| |【筛选【筛选】】| |【高级筛选【高级筛选】命令，打开【】命令，打开【高级筛选】对话高级筛选】对话框 单击单击““条件区域条件区域””和和““复制到复制到””编辑栏右侧的拾取按取分别将条编辑栏右侧的拾取按取分别将条件区域件区域A33A33：：B34B34和和A36A36填入到编辑填入到编辑栏内栏内 筛选结果筛选结果 打开打开““汇总汇总””工作表，选中数据区域内任一单元格工作表，选中数据区域内任一单元格 。

单击菜单栏中的菜单项【数据】单击菜单栏中的菜单项【数据】| |【分类汇总】命【分类汇总】命令，打开【分类汇总】对话框令，打开【分类汇总】对话框 ““汇总方式汇总方式””选择选择““平均值平均值””，，““选定汇总项选定汇总项””选择选择““计算机基计算机基础础””、、““计算机组装计算机组装””、、““大学大学英语英语””、、““高等数学高等数学””和和““总分总分” ” 在工作表“汇总”中，按学习小组统计“计算机基础”、“计算机组装”、“大学英语”、“高等数学”和“总分”的平均值 在工作表“汇总”中，按学习小组统计“计算机基础”、“计算机组装”、“大学英语”、“高等数学”和“总分”的平均值汇总结果汇总结果 单击数据清单左侧的单击数据清单左侧的““隐隐藏明细数据符号藏明细数据符号” ” 符号按符号按钮钮 ，显示，显示““分类汇总分类汇总””后的数据结果，此时符号后的数据结果，此时符号按钮变成符号按钮按钮变成符号按钮 选中工作表选中工作表“0801“0801班学生成绩数据单班学生成绩数据单””的数据清单中的任一单元格的数据清单中的任一单元格 单击【数据】单击【数据】| |【数据透视表和数据透视图】命令，打开【数据透【数据透视表和数据透视图】命令，打开【数据透视表和数据透视图向导视表和数据透视图向导——3——3步骤之步骤之1 1】对话框】对话框 利用“数据透视表”对“0801班学生成绩数据清单”按学习小组和姓名统计总分。

单击【下单击【下一步】按一步】按钮，打开钮，打开【数据透【数据透视表和数视表和数据透视图据透视图向导向导————3 3步骤之步骤之2 2】对话框】对话框 此步骤用于选取创建数据此步骤用于选取创建数据透视表的数据源区域，系透视表的数据源区域，系统自动设定当前数据清单统自动设定当前数据清单区域如果要重新选定数区域如果要重新选定数据区域，可以通过拾取按据区域，可以通过拾取按钮或直接在编辑栏内输入钮或直接在编辑栏内输入区域，如果选定区域来自区域，如果选定区域来自外部文件可通过单击【浏外部文件可通过单击【浏览】来完成览】来完成 单击【下一步】按钮，打单击【下一步】按钮，打开【数据透视表和数据透开【数据透视表和数据透视图向导视图向导——3——3步骤之步骤之3 3】】对话框，用于设置数据透对话框，用于设置数据透视表的显示位置在此选视表的显示位置在此选择默认值择默认值““新建工作表新建工作表””，单击【完成】按钮，单击【完成】按钮  此时Excel 在当前工作表前插入了一个新工作表“Sheet1”，并自动打开该工作表，显示数据透视表的页面布局，页面布局框的右侧显示【数据透视表字段列表】对话框 。

页字段：在数据透视表页字段：在数据透视表中指定为页方向的字段中指定为页方向的字段在页字段中，既可以显在页字段中，既可以显示所有项的汇总，也可示所有项的汇总，也可以一次显示一项，筛选以一次显示一项，筛选掉其他数据掉其他数据数据项：在数据透视表中要汇总的数数据项：在数据透视表中要汇总的数据 列（行）字段：数据透视表列（行）字段：数据透视表中按列（行）显示的字段中按列（行）显示的字段 在【数据透视表字段列表】中拖动“学习小组”字段至“请将页字段拖至此处”字样的位置，或先选中“学习小组”字段，在“添加到”的右侧下拉列表中选择“页面区域”，单击【添加到】按钮操作也可下面，可用拖动方式操作将“姓名”拖至“将行字段拖至此处”字样的位置，将“总分”拖至“请将数据项拖至此处”字样的位置，得到数据透视表结果 单击单元格单击单元格B2B2处的下拉按钮，可以选择处的下拉按钮，可以选择学习小组的有效值，如选择学习小组的有效值，如选择“A“A组组””，则，则在行字段和数据区域中显示在行字段和数据区域中显示A A组学生姓名组学生姓名和总分汇总和总分汇总 双击数据透视表中的字段单元格，可打双击数据透视表中的字段单元格，可打开【数据透视表字段】对话框，在框中开【数据透视表字段】对话框，在框中可设置分类汇总的类型可设置分类汇总的类型 。

选中工作表选中工作表““图表图表””数据清单内的数据清单内的““总分总分””列的任一单元格，单击工具列的任一单元格，单击工具栏中的降序排列按钮，此时按总分降序进行排列，前五名学生依次显示栏中的降序排列按钮，此时按总分降序进行排列，前五名学生依次显示在第在第2 2行至第行至第6 6行内单击【下一步】按钮，打开【数据透视表和数据透单击【下一步】按钮，打开【数据透视表和数据透视图向导视图向导——3——3步骤之步骤之2 2】对话框】对话框 1．将总分前五名的学生成绩按照单科总分、总分制作柱形图，创建图表选中数据区域内任一单元格，单击【插入】选中数据区域内任一单元格，单击【插入】| |【图表】命令，打开【图表向导【图表】命令，打开【图表向导-4-4步骤之步骤之1-1-图表类型】对话框图表类型】对话框 单击【数据区域】选项卡单击【数据区域】选项卡 在【标准类型】选项卡的在【标准类型】选项卡的““图表类型图表类型””列表中，系统默认为列表中，系统默认为““柱形图柱形图”” 子图表类型子图表类型””选择选择““簇状柱形图簇状柱形图”” 单击单击““数据区域数据区域””编辑栏右侧的拾编辑栏右侧的拾取按钮，将前五名学生的姓名和单取按钮，将前五名学生的姓名和单科总分、总分拾取（操作方法：按科总分、总分拾取（操作方法：按住住CtrlCtrl键，依次选择单击格区域键，依次选择单击格区域B2:B6B2:B6和和D2:H6D2:H6）） 。

单击【系列】选项卡单击【系列】选项卡 在在““系列系列””列表中，选择列表中，选择““系列系列1”1”，在名称框内输入，在名称框内输入““计算机基计算机基础础””，仿此操作，依次逐一更名各，仿此操作，依次逐一更名各系列系列 单击【标题】选项卡单击【标题】选项卡 分类（分类（X X）轴栏输入）轴栏输入““姓名姓名””，，数值（数值（Y Y）轴栏输入）轴栏输入““分数分数”” 在图表标题栏输入在图表标题栏输入“0801“0801班前班前五名学生成绩图五名学生成绩图”” 单击【图例】选项卡单击【图例】选项卡 选择位置选择位置““靠上靠上””选项选项 单击【下一步】按钮单击【下一步】按钮 选择选择““作为其中的对象插入作为其中的对象插入””单选项，生成图表插入到单选项，生成图表插入到““图图表表””工作表中工作表中, ,本项目选择该项本项目选择该项 单击【完成】按钮单击【完成】按钮 图表大小控点图表大小控点图表标题图表标题图例图例绘图区绘图区图表区图表区选择选择““作为新工作表插入作为新工作表插入””，，将会插入一个图表将会插入一个图表8.4 实践总结实践总结 在本项目的实践中，通过创建数据清单，完成了数据的排序、筛选、汇总、数据透视和图表的操作，对数据进行快速管理和分析，这些操作应用非常广泛，利用Excel强大的数据管理功能，实现方便地组织、管理和分析大量的数据信息，因此要熟练掌握其操作要领。