Excel学生成绩分析第一个案例.pdf

1 案例名称： 创建一个有关“青海民族大学计算机学院08 计本班考试成绩统计分析”的 工作薄，并对其中的各类数据作相应的统计分析。 （一）创建学生成绩统计分析表 一、操作要求： 1. 创建如图 5-1 所示的青海民族大学计算机学院08 计本班考试成绩统计分析 表。 2. 使用 Excel 公式或函数计算出表中的总分、平均分、按总分求名次、各科成 绩的最高分、最低分、不及格人数和及格率。 3. 表中用 Excel 公式或函数计算出的数据用“加粗”显示，单科成绩低于60 分的用“红色”显示。 图 5-1 青海民族大学计算机学院08 计本班考试成绩统计分析表 二、 操作结果： 如图 5-2 所示： 2 图 5-2 使用公式或函数后的学生成绩统计表 三、操作步骤： 1.按图 5-1 所示，创建一个工作薄，命名为“青海民族大学计算机学院08 计 本班考试成绩统计分析表” ，并输入学号、姓名及各科成绩。 2.利用求和函数“ =SUM(C3:G3) ”计算 H3单元格中的总分，然后用填充功能实 现 H4:H12各单元格的总分。 3.利用求平均函数“ =AVERAGE(C3:G3)”计算 I3 单元格中的平均分（注意，不 包括 H3单元格） ，然后用填充功能实现I4:I12各单元格的平均分。 4.求总分的名次，利用RANK函数计算机J3 单元格中的名次，J3 中输入 “=RANK(H3,$H$3:$H$12) ” ，得到名次为 2，然后用填充功能实现J4:J12 各 单元格的名次。注意公式中的绝对引用及相对引用。 5.C13单元格中显示高等数学的最高分，其计算公式为 “=MAX(C3:C12) ” ，然后 用填充功能实现 C13:G13各单元格的最大值。 6.C14单元格中显示高等数学的最低分，其计算公式为 “=MIN(C3:C12)” ，然后 用填充功能实现 C13:G13各单元格的最小值。 7.C15 单 元 格 中 显 示 高 等 数 学 的 不 及 格 人 数 ， 其 计 算 公 式 为 “=COUNTIF(C3:C12,“60“)/COUNT(C3:C12) ” ， 然后用填充功能实现C16:G16 各单元格中各科成绩的及格率。选中C16:G16各单元格数据，右击，在弹出 的快捷菜单中选择并单击“设置单元格格式”, 弹出如图 5-3 所示的对话 框。选择“数字”标签，并将“小数位数”设置为1, 单击“确定”按钮。 图“单元格格式”对话框 9.快速计算 D13:G16单元格中的数据还可以用以下方式完成： 用上面的第 58 步分别计算出 C13:C16中的数据，然后单击C13 ，按住鼠 标向下拖动到 C16 ，将鼠标移动到 C16的填充柄，如图 5-4 所示，向右拖动，一 直到 G16 ，所有科目的最高分、 最低分、不及格人数和及格率都一次计算出来了。 4 图 5-4快速拖动计算 10. 选中 C3:G12中的数据，选择“格式”菜单中的“条件格式”, 显示图 5-5 所示的“条件格式”对话框，设“单元格数值小于60”为红色。 图 5 “条件格式”对话框 11. 把计算出的总分、平均分、名次、最高分、最低分等数据“加粗”显示。 完成以上步骤，即可得到图5-2 所示的学生成绩统计表。 5 （二）创建学生成绩通知单 一、操作要求： 将上述成绩表作为数据源，通过Word 的邮件合并功能，制作每位学生的成 绩通知单。 二、操作结果： 学生成绩通知书 李明同学家长： 您好！本学期已经结束，您孩子本学期的学业成绩如下： 高等数学大学英语高级语言计算机网络大学物理总分名次 87 75 90 88 92 432 2 我校定于 1 月 10 日正式放假，下学期定于2 月 25、26 日报到注册， 27 日 正式上课。 对不及格的科目，请督促其认真复习，争取在下学期的考试中考出好成绩。 青海民族大学计算机学院 2012年 1 月 11日 王小平同学家长： 您好！本学期已经结束，您孩子本学期的学业成绩如下： 高等数学大学英语高级语言计算机网络大学物理总分名次 90866875713903 我校定于 1 月 10 日正式放假，下学期定于2 月 25、26 日报到注册， 27 日 正式上课。 对不及格的科目，请督促其认真复习，争取在下学期的考试中考出好成绩。 青海民族大学计算机学院 2012年 1 月 11日 三、操作步骤： . 启动 Word 应用程序，显示邮件合并工具栏。 6 单击菜单中的“工具”，选择“信函与邮件”、 “显示邮件合并工具栏” 。如图 5-6 所示。 图 -显示邮件合并工具栏 2. 确定主文档类型 单击“邮件合并工具栏”上的第一个图标，选择主文档类型为“普通Word 文档” 。保存该文档， 文件取名为 “青海民族大学计算机学院08 计本考试成绩通 知单” ，如图 5-7 所示。 图 5-7确定主文档类型 3. 打开数据源 单击“邮件合并工具栏”上的第二个图标，双击Excel 文档“青海民族大 7 学计算机学院 08 计本考试成绩统计分析表” ，选择它作为数据源。 图 -8 打开数据源 该 Excel 文档中有三个工作表，所需成绩表在第1 个工作表中，所以要选择 工作表 sheet1 ，如图 5-9 所示。 图 5-9 选择工作表 3. 编辑学生成绩通知单正文 学生成绩通知书 同学家长： 您好！本学期已经结束，您孩子本学期的学业成绩如下： 高等数学大学英语高级语言计算机网络大学物理总分名次 我校定于 1 月 10 日正式放假，下学期定于2 月 25、26 日报到注册， 27 日 正式上课。 对不及格的科目，请督促其认真复习，争取在下学期的考试中考出好成绩。 8 青海民族大学计算机学院 2012年 1 月 11日 4. 插入域 将插入点定位于 “同学家长” 前，单击“邮件合并工具栏” 上的第六个图标， 双击对话框中的“姓名”字段，如图5-10 所示。 图 5-10 插入姓名域 9 类似地，在学生成绩通知单的相应位置分别插入其它的域，如图5-11所示。 5. 合并到新文档 将插入点定位于“同学家长”前，指向“邮件合并工具栏”上的倒数第四个 图标“合并到新文档” ，如图 5-12 所示。 图 5-11依次插入其它的域 10 图 5-12 “合并到新文档”工具 单击“合并到新文档”工具，弹出“合并到新文档”的对话框，如图5-13 所示， “合并记录”选择“全部” 。 图 5-13 “合并到新文档”对话框 单击“确定”后，生成邮件文档，该文档为数据源中的每一条记录生成 一份学生成绩通知单。结果见前面的“操作结果”。 （三）冻结窗格 11 当使用 Excel 编辑工作表时，在编辑靠后或靠右的工作表单元格内容时，可 能由于看不到表格的标题栏， 搞不清对应于哪一个标题信息。 如果希望不论如何 滚动工作表，表格的标题栏始终位于屏幕的可视区域。可使用Excel 的“冻结窗 格”功能。 一、操作要求： 锁定“青海民族大学计算机学院08 计本班考试成绩统计分析表”中的前两 行（表格标题和字段名）及前两列（学号和姓名）。 二、操作结果： 窗格冻结后的操作结果如图5-14 所示,，看不到表格中的第条记录， 看不到各记录的高等数学和大学英语成绩。 图 5-14冻结窗格后的操作结果 三、操作步骤： 1.把需要锁定表格标题栏的工作表切换为当前工作表。 2.如果要锁定表格顶部的标题栏，则需单击标题栏下面一行的行号，以选定 该行。如果要锁定表格左侧的标题栏，则需单击标题栏右面一列的列标，以选定 该列。如果要同时锁定表格顶部和左侧的标题栏，则需单击两个标题栏相交处右 下方的单元格。现锁定表中的第1、2 行和第 A、B 列，则单击 C3 单元格。 12 3.单击“窗口”菜单，选择菜单中的 “冻结窗格” 命令即可， 如图 5-15 所示。 图 5-15冻结窗格 之后，无论你怎样滚动工作表， 标题栏都不会因表格移动而滚出屏幕的可视 区域，这就极大地方便了对大工作表的编辑。 如果要取消对标题栏的锁定，则可单击“窗口”菜单中的“撤消冻结窗格” 命令。