1 / 40

项目 8 学生成绩管理与分析

项目 8 学生成绩管理与分析. 教学目标. 会进行数据的选择性粘贴 会创建数据清单，会利用记录单对数据清单进行 编辑或条件查询 会设置工作表数据清单的样式 会设置数据清单快速排序和高级排序 会设置数据自动筛选和高级筛选 会设置数据的分类汇总 会设置数据透视表 会设置数据的图表分析. 8.1 任务要求. 创建名为“ 0801 班学生成绩管理分析 .xls” 的新工作簿，含“ 0801 班学生成绩表”的数据清单，内容来源于项目七中的工作簿文件“ 08 级计算机专业学生成绩表 .xls” 内的工作表“ 0801 班”的相关内容。.

felice
Download Presentation

项目 8 学生成绩管理与分析

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 项目8 学生成绩管理与分析 教学目标 • 会进行数据的选择性粘贴 • 会创建数据清单，会利用记录单对数据清单进行 编辑或条件查询 • 会设置工作表数据清单的样式 • 会设置数据清单快速排序和高级排序 • 会设置数据自动筛选和高级筛选 • 会设置数据的分类汇总 • 会设置数据透视表 • 会设置数据的图表分析

  2. 8.1 任务要求 • 创建名为“0801班学生成绩管理分析.xls”的新工作簿，含“0801班学生成绩表”的数据清单，内容来源于项目七中的工作簿文件“08级计算机专业学生成绩表.xls”内的工作表“0801班”的相关内容。

  3. 能按单科总分或总分进行排序，快速找出单科总分或总分排名情况

  4. 能按多个关键字段进行复合排序，如先按总分排序，如果总分相同，再按计算机基础分数排序等，即实现高级排序能按多个关键字段进行复合排序，如先按总分排序，如果总分相同，再按计算机基础分数排序等，即实现高级排序

  5. 根据指定条件对数据进行筛选，查询符合条件的记录，如筛选出总分大于300分的记录根据指定条件对数据进行筛选，查询符合条件的记录，如筛选出总分大于300分的记录 能进行复合筛选 ，如筛选总分大于300的A组学生

  6. 能按指定字段进行数据分类汇总，如按学习小组分析各组总分平均分、单科平均分等，还能对多个字段进行分类汇总

  7. 能将总分前五名的学生成绩按照单科总分、总分制作柱形图表

  8. 8.2 解决方案 创建名为“0801班学生成绩管理分析.xls”的新工作簿，创建5个工作表，分别命名为“0801班学生成绩数据清单”、“排序”、“筛选”、“汇总”和“图表”等，其中第一个工作表的数据来自于项目七使用的对象文件，故可打开该文件，把相关内容复制粘贴到目标工作表中，因项目七中工作簿的许多数据是通过公式计算生成的，如果直接复制粘贴将会显示错误信息，在此操作过程中必须使用数据的选择性粘贴解决粘贴出错问题。数据清单创建后，使用自动套用样式和记录单进行样式设置和编辑查询，另外还用到前面项目中已学习的工作表复制、工作表标签重命名等操作； 按单科或总分的排序，能进行快速排序和高级排序操作。排序是数据处理常见的操作，使用Excel的数据排序功能可实现对单个字段排序或多个字段复合排序； 设置数据的筛选，能进行自动筛选或高级筛选操作。在Excel中，提供了 数据的自动筛选和高级筛选两种模式，使用它们实现操作； 对数据进行分类汇总和制作数据透视表； 对总分前五名学生按成绩制作柱形图，可以先按总分降序排列学生成绩， 然后利用Excel中插入图表的操作向导逐步完成。

  9. 8.3 实现步骤 8.3.1 创建并格式“0801班学生成绩数据清单” 1．创建名为“0801班学生成绩管理分析.xls”的工作簿 点击桌面【开始】菜单，点击【程序】|【Microsoft Office】|【Microsoft Office Excel 2003】命令，打开Excel 2003 。 单击菜单栏上的【文件】|【保存】命令，打开“另存为”对话框。 保存位置选择在“D：\我的文档”文件夹下。 在文件名编辑栏内输入“0801班学生成绩管理分析”，文件类型选择Excel文件

  10. 在工作表标签“Sheet1”前插入2个工作表 依次将5张工作表进行重命名 重命名后图示

  11. 2．创建工作表“0801班学生成绩数据清单” 在工作表“0801班学生成绩数据清单”的单元格区域A1：H1内，输入标题行 打开“08级计算机专业学生成绩表.xls”工作簿，选定工作表“0801班”中的单元格区域A4:B33，并复制。 在工作表“0801班学生成绩数据清单”中，用鼠标右键单击单元格A2，在弹出的菜单中，点击【粘贴】命令或按Ctrl+V组合键， “学号”和“姓名”的数据粘贴到单元格区域A2:B31中。

  12. 在“08级计算机专业学生成绩表.xls”， 复制工作表“0801班”中的单元格区域E4:E33，即“计算机基础”单科总分。 在工作表“0801班学生成绩数据清单”，选择单元格D2，单击鼠标右键，在弹出的菜单中，点击【选择性粘贴】命令，在【选择性粘贴】对话框的【粘贴】选项栏中，系统默认选项为“全部”，选中“数值”或“值和数字格式”选项，设置为粘贴数值。单击【确定】按钮，完成“计算机基础”分数的复制粘贴。

  13. 为什么选中“数值”或“值和数字格式”选项，而不是选择“全部”呢？为什么选中“数值”或“值和数字格式”选项，而不是选择“全部”呢？ 设置为选中“数值”或“值和数字格式”选项的原因是“08级计算机专业学生成绩表.xls”的工作表“0801班”中单元格区域E4:E33存储的是运算公式，这里只需要复制计算结果即分数，不需要具体的运算公式。 如果使用【选择性粘贴】对话框的【粘贴】中的默认选项，会复制原单元格中存储的公式，公式被粘贴后在新表中重新计算，而运算对象单元格已不是数字，会显示出错信息。 使用“选择性粘贴”功能完成“计算机组装”、“大学英语”、“高等数学”和“总分”数据的复制粘贴

  14. 自动填充柄 拖动C2的填充柄至C7 使用相同操作， 完成其他学习小组的数据录入。 选中数据区域A1：H31，在格式工具栏内选择字体为宋体、字号12磅，单击居中按钮，单击边框按钮，设为无边框线，单击任意单元格，取消全选状态。

  15. 3．复制数据清单至工作表“排序”、“筛选”、“汇总”和“图表”中，以便进行数据的排序、筛选、汇总和创建图表等操作。3．复制数据清单至工作表“排序”、“筛选”、“汇总”和“图表”中，以便进行数据的排序、筛选、汇总和创建图表等操作。 点击工作表“0801班学生成绩数据清单”中全选按钮，工作表区呈选定状态，在工作表区任一处单击鼠标右键，在弹出的菜单中，执行【复制】命令 。 打开“排序”工作表，选中单元格A1，单击鼠标右键，在弹出的菜单中，执行【粘贴】命令。 参照上述操作，依次将数据清单粘贴至 “筛选”、“汇总”和“图表”3个工作表。

  16. 4．设置工作表“0801班学生成绩数据清单”的套用样式4．设置工作表“0801班学生成绩数据清单”的套用样式 将工作表“0801班学生成绩数据清单”格式设置为自动套用“序列1”格式 选中工作表“0801班学生成绩数据清单”的单元格区域A1:H31。 单击菜单栏中的菜单项【格式】|【自动套用格式】命令，打开【自动套用格式】对话框。 在格式列表中，拖动滚动条，选择“序列1”格式，单击【确定】按钮。

  17. 5．利用“记录单”编辑数据清单 单击工作表“0801班学生成绩数据清单”数据清单区域内的任一单元格。 单击菜单栏上【数据】|【记录单】命令，打开【记录单】对话框。 在【记录单】对话框中，显示当前数据清单的首记录。 单击【上一条】或【下一条】按钮或拖动垂直滚动条浏览数据清单中的记录。

  18. 6.利用“记录单”进行条件查询 在“记录单”中查询数据清单中C组总分超过300分的学生记录。 单击【条件】按钮 输入条件 单击【下一条】按钮，显示出符合条件的首记录 单击【下一条】按钮，查看其他满足条件的记录 单击【条件】按钮，启动重新查询，如果单击【关闭】按钮，关闭【记录单】对话框。

  19. 8．3．2设置数据清单排序 1．对工作表“排序”中的数据清单按“总分”字段设置为降序排列。 单击工作表“排序”的标签，打开“排序”工作表。 在“主要关键字”栏中选择“总分”，选择“降序”方式，在“我的数据区域”设置为“有标题行”，单击【确定】按钮 。 单击菜单栏上的菜单项【数据】|【排序】命令 此时每条记录作为一个整体移动到相应的行。

  20. 其他方法：通过工具栏中的升序按钮 和降序按钮进行快速排序 单击降序按钮 。 单击“总分”列中的任意数据单元格。

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

  22. 2．在工作表“排序”中，按“高等数学”列设置为降序排列，如果“高等数学”分数相同，再按照“计算机基础”降序排列，如果“计算机基础”分数也相同，再按照“学号” 升序排列。 在进行多条件的高级排序的操作过程中，弹出了【排序警告】对话框，这是因为工作表区域A列的学号是以文本形式存储的数字 同时对以文本和数字形式存储的数字进行排序。 首先对以数字形式存储的数字进行排序，然后对以文本形式存储的数字进行排序。 因为A列的学号均为文本格式数字，故两种处理方式均可。

  23. 在数据清单中每一列的列标题右侧都会出现【自动筛选】下拉按钮 ，表示进入了自动筛选状态 8．3．3 设置数据筛选 1．筛选出“总分>300”的数据记录 单击工作表“筛选”的标签，打开“筛选”工作表。 单击菜单栏上的菜单项【数据】|【筛选】｜【自动筛选】命令

  24. 单击“总分”字段旁的下拉按钮，弹出下拉框，在弹出的下拉框内选择“自定义”。单击“总分”字段旁的下拉按钮，弹出下拉框，在弹出的下拉框内选择“自定义”。 “总分”下拉列表选择“大于”，后面的编辑栏中输入“>300” 筛选结果

  25. 单击“姓名”【自动筛选】下拉按钮 ，选择“自定义”。 若筛选出“总分>300”的“王”姓学生的数据记录 条件下拉列表选择“等于”，编辑栏输入“王*” 筛选结果

  26. 2.筛选出总分大于300分的A组学生记录，在单元格区域A33：B34内录入筛选条件，筛选结果从单元格A36开始显示，原数据清单仍显示。 在空白单元格区域A33:B34内输入筛选条件。 单击“条件区域”和“复制到”编辑栏右侧的拾取按取分别将条件区域A33：B34和A36填入到编辑栏内 单击菜单栏中【数据】|【筛选】|【高级筛选】命令，打开【高级筛选】对话框。 筛选结果

  27. 8．3．4 设置数据汇总 在工作表“汇总”中，按学习小组统计“计算机基础”、“计算机组装”、 “大学英语”、“高等数学”和“总分”的平均值。 打开“汇总”工作表，选中数据区域内任一单元格。 单击菜单栏中的菜单项【数据】|【分类汇总】命令，打开【分类汇总】对话框 “汇总方式”选择“平均值”，“选定汇总项”选择“计算机基础”、“计算机组装”、“大学英语”、“高等数学”和“总分”

  28. 在工作表“汇总”中，按学习小组统计“计算机基础”、“计算机组装”、“大学英语”、“高等数学”和“总分”的平均值。在工作表“汇总”中，按学习小组统计“计算机基础”、“计算机组装”、“大学英语”、“高等数学”和“总分”的平均值。 单击数据清单左侧的“隐藏明细数据符号” 符号按钮 ，显示“分类汇总”后的数据结果，此时符号按钮变成符号按钮 汇总结果

  29. 8．3．5 设置数据透视表 利用“数据透视表”对“0801班学生成绩数据清单”按学习小组和姓名统计总分。 选中工作表“0801班学生成绩数据单”的数据清单中的任一单元格。 单击【数据】|【数据透视表和数据透视图】命令，打开【数据透视表和数据透视图向导——3步骤之1】对话框 单击【下一步】按钮，打开【数据透视表和数据透视图向导——3步骤之2】对话框。

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

  31. 此时Excel 在当前工作表前插入了一个新工作表“Sheet1”，并自动打开该工作表，显示数据透视表的页面布局，页面布局框的右侧显示【数据透视表字段列表】对话框 。 页字段：在数据透视表中指定为页方向的字段。在页字段中，既可以显示所有项的汇总，也可以一次显示一项，筛选掉其他数据。 列（行）字段：数据透视表中按列（行）显示的字段。 数据项：在数据透视表中要汇总的数据。

  32. 在【数据透视表字段列表】中拖动“学习小组”字段至“请将页字段拖至此处”字样的位置，或先选中“学习小组”字段，在“添加到”的右侧下拉列表中选择“页面区域”，单击【添加到】按钮操作也可。下面，可用拖动方式操作。将“姓名”拖至“将行字段拖至此处”字样的位置，将“总分”拖至“请将数据项拖至此处”字样的位置，得到数据透视表结果 。

  33. 单击单元格B2处的下拉按钮，可以选择学习小组的有效值，如选择“A组”，则在行字段和数据区域中显示A组学生姓名和总分汇总。单击单元格B2处的下拉按钮，可以选择学习小组的有效值，如选择“A组”，则在行字段和数据区域中显示A组学生姓名和总分汇总。 双击数据透视表中的字段单元格，可打开【数据透视表字段】对话框，在框中可设置分类汇总的类型。

  34. 8．3．6 创建数据图表 1．将总分前五名的学生成绩按照单科总分、总分制作柱形图，创建图表 选中工作表“图表”数据清单内的“总分”列的任一单元格，单击工具栏中的降序排列按钮，此时按总分降序进行排列，前五名学生依次显示在第2行至第6行内。 单击【下一步】按钮，打开【数据透视表和数据透视图向导——3步骤之2】对话框。

  35. 选中数据区域内任一单元格，单击【插入】|【图表】命令，打开【图表向导-4步骤之1-图表类型】对话框。选中数据区域内任一单元格，单击【插入】|【图表】命令，打开【图表向导-4步骤之1-图表类型】对话框。 在【标准类型】选项卡的“图表类型”列表中，系统默认为“柱形图”。 “子图表类型”选择“簇状柱形图”。 单击【数据区域】选项卡。 单击“数据区域”编辑栏右侧的拾取按钮，将前五名学生的姓名和单科总分、总分拾取（操作方法：按住Ctrl键，依次选择单击格区域B2:B6和D2:H6）。

  36. 单击【系列】选项卡。 在“系列”列表中，选择“系列1”，在名称框内输入“计算机基础”，仿此操作，依次逐一更名各系列。

  37. 单击【标题】选项卡。 在图表标题栏输入“0801班前五名学生成绩图”。 单击【图例】选项卡。 分类（X）轴栏输入“姓名”，数值（Y）轴栏输入“分数”。 选择位置“靠上”选项。 单击【下一步】按钮。

  38. 选择“作为新工作表插入”，将会插入一个图表。选择“作为新工作表插入”，将会插入一个图表。 选择“作为其中的对象插入”单选项，生成图表插入到“图表”工作表中,本项目选择该项。 单击【完成】按钮。 图表大小控点 图表标题 图例 绘图区 图表区

  39. 8.4 实践总结 在本项目的实践中，通过创建数据清单，完成了数据的排序、筛选、汇总、数据透视和图表的操作，对数据进行快速管理和分析，这些操作应用非常广泛，利用Excel强大的数据管理功能，实现方便地组织、管理和分析大量的数据信息，因此要熟练掌握其操作要领。

More Related