2.4 考试成绩分析表
案例背景
某中学高二年级期中考试阅卷，成绩登统之后，需要对整个年级和各个班级的期中考试成绩进行分析对比，要排出每个学生每门课程和总分的年级名次。还要求统计全年级和每个班级各学科的平均分、最高分、最低分与标准差。以便对考试情况进行综合分析。
要完成排名次的工作最简单的方法就是通过排序来完成。前面已经学习了用AVERAGE、MAX、和MIN函数分别求平均分、最高分和最低分，随着学习的深入您会发现要完成一件工作，Excel可能会提供多种技术来实现，我们应该寻求一种更为简捷的方法来解决问题，以提高工作效率。本节将介绍数据的排序和分类汇总解决上述问题。
关键技术点
要实现本案例中的功能，学员应该掌握以下EXCEL技术点。
●基础知识：查找与替换 选择性粘贴 数据的排序，分类汇总
●函数应用： SUM函数
最终效果展示
2.4.1创建考试成绩分析表
Step1 输入表格字段名和原始数据
创建一个新的Excel工作簿，在工作表sheet1中按照“最终效果展示”效果图2输入年级“考试成绩分析表”的表格字段信息和原始数据。
Step2计算总分
①将光标移到单元格H2，单击桌面上的常用工具栏“∑“自动求和按钮，由于从单元格A2到单元格G2存放的全是数字，所以Excel默认的求和区域为A2:G2，这时用鼠标重新选择准备求和的区域C2:G2或直接在编辑栏输入C2:G2，按键确认即可完成第一个学生的总分计算。
②将光标移到单元格H2右下角，当光标由原来的空心十字变为黑色实心十字时，双击左键即可完成公式的复制填充，从而计算出其余学生的总分。
Step3隐藏“总分“的公式
①单击单元格H2，按组合键，从而快速选中矩形单元格区域H2:H121，
②单击常用工具栏按钮”复制“，然后单击菜单“编辑”→”选择性粘贴“，
③从弹出的”选择性粘贴“对话框的”粘贴“选项框中选择“数值”，单击“确定“按钮或按键确认即可完成公式隐藏或者说去掉公式的任务。
有时我们只需要函数公式的计算结果，而并不需要保留计算过程，通过选择性粘贴去除公式，切断函数公式与函数参数的联系，避免后续操作造成计算结果的更动是十分必要的。
Step4设置单元格自动换行
单击行标“1“选中第一行，按组合键弹出”单元格格式“对话框，切换到”对齐“选项卡，在”文本控制“选项组中单击”自动换行“按钮，然后单击确定按钮。
Step5输入语文名次
①单击列标“D”选中D列，单击菜单“插入”→“列”。
②在“语文”成绩列右侧插入一列，在单元格D1中输入字段名“语文名次”，
③将光标移到单元格C2，单击常用工具栏“降序排序”，即可完成数据表按语文成绩从高分到低分的排序。
④在单元格D2输入1，再在单元格D3输入2，
⑤选中单元格区域D2:D3，将光标移到单元格D3右下角，当光标变为+形状时，双击左键即可完成序列填充，从而完成了全年级120名学生语文成绩的排名工作。
Step6计算“数学”、’英语”、”物理”和‘政治“的名次
用同样的插入列，输入字段名，通过按课程成绩降序排序的方法排出每个学生上述四门课程的名次。然后适当N列调整列宽。
Step7计算年级名次
①光标移动到存放“总分”M列的任意一个单元格，如M2，单击常用工具栏“降序排序”，即可完成数据表按总分成绩从高分到低分的排序。
②在N列用同样的方法填充1-120,完成总分排名次任务。
Step8多字段排序
①单击数据菜单→“排序”，从弹出的“排序对话框”的“主要关键字”选项框中选择“班级”，然后单击其右侧的“升序“按钮，从“次要关键字”选项框中选择“学号”，再单击其右侧的“升序“按钮
②单击确定按钮即可完成按班级，学号的排序。
至此语文、数学、英语、物理、政治和总分的排名工作全部完成，使用的方法就是通过成绩降序排序并在旁边的空列中填充数字“1-120“,为全年级120名学生排名次，但是这种排名方法有一个明显的缺陷，就是无法为分数相同的学生设置同样的名次，比如说前5名学生成绩相同，其名次一个是第1名，另一可能是第5名，这是很不科学的，如何解决这个问题呢？在下一节学习Rank函数来解决这个问题。
2.4.2使用“分类汇总”汇总平均分
对于需要进行分类汇总的数据表有以下要求：
◆首先要求该数据表的每个字段都有字段名，也就是说数据库的每列都有列标题。
◆需要进行分类汇总的“分类字段”要有实际意义，在本案例中的所有字段中只有对字段“班级”进行分类汇总有实际意义，其他字段无意义。
◆进行分类汇总前，首先要对 “分类字段”进行排序（升序或降序），这样对该列进行的分类汇总才会按一定次序给出排列结果，否则会出现偏离预期的结果。本案例中首先要按班级排序，然后再进行分类汇总。
Step1 复制粘贴成绩表
在工作表Sheet1中单击表格左上角——行与列交界处的矩形框选中整张工作表区域，单击常用工具栏的“复制”按钮，切换到工作表Sheet2，选中单元格A1，单击常用工具栏“粘贴”按钮，将成绩表粘贴到工作表Sheet2
Step2删除“名次”列
①单击D列的列标，首先选中D列，按下键不放，再分别单击F列、H列、J列L列和N列的列标，分别选中F列、H列、J列L列和N列。
②单击菜单“编辑”→“删除”即可完成各门课程和总分名次列的删除。
Step3使用“替换”功能输入完整班级名称
①单击A列列标选中A列，单击菜单“编辑“→”替换“，
②弹出“查找和替换“对话框，单击”选项“按钮，再次弹出一个选项更多的“查找和替换“对话框，在”查找内容“框中输入1，在”替换为“框中输入”高二1班“，勾选”单元格匹配选项“，单击”全部替换“按钮，
③弹出一个新的提示对话框，告诉操作者已经完成搜素并进行了40处替换，单击确定按钮，然后关闭“查找和替换“对话框，这时系统已经将”班级”列中的”1“全部替换成了”高二1班。
④用同方法把“2“和”3“全部替换成高二2班和高二3班。
Step4以班级为“分类字段”进行汇总
①选中成绩表内任意单元格，单击菜单“数据”→“分类汇总”，
②从弹出的“分类汇总“对话框的”分类字段“选项框中选择”班级“，在”汇总方式” 选项框中选择”平均值“，在”选定汇总项“选项框中勾选”语文“，”数学“，”英语“，”物理“，”政治“和”总分“。
③单击确定按钮或按键确认即可完成统计平均分工作。
Step5分级显示数据
按班级进行平均分的分类汇总后，数据表中的数据将分级显示。其中
◆第一级数据：汇总项的总和。在这里就是年级平均分。
◆第二级数据：分类汇总数据各汇总项的总和。在这里就是各班级平均分和年级平均分。
◆第三级数据：数据清单的原始数据和分类汇总数据各汇总项的总和。在这里就是整个成绩表+。
下面简单介绍一下分类汇总工作表中一些按钮的功能。
◆工作表左上方地数字按钮1、2、3 。用于分级显示数据，单击1 按钮显示一级数据，单击2 按钮显示一级和二级数据，单击3 按钮显示一级、二级和三级数据。
◆工作表左侧的按钮+ ，单击此按钮可以显示明细数据。所谓明细数据是相对汇总数据而言的，实际上就是原始数据。
◆工作表左侧的按钮- 。单击此按钮可以隐藏明细数据。
①单击按钮2 即可显示三个班和年级平均分
2.4.3使用“分类汇总”汇总最高分、最低分和标准差
下面陆续进行最高分、最低分和标准差的汇总，首先在汇总完平均分的基础上汇总最高分
①单击菜单“数据”→“分类汇总”，从弹出的分类汇总对话框中的”汇总方式” 选项框中改选”最大值“，并去掉”替换当前汇总项“中的√。
②单击确定按钮即可完成最高分的汇总。
③用同样方法完成最低分和标准差的汇总。
2.4.4 保存汇总结果
Step1 调整列宽
适当调整第一列“班级“的列宽，使该列内容能够完全显示。
Step2 设置边框
选中单元格区域A1:H137，为汇总表设置边框
Step3复制汇总表
按组合键，复制汇总表
Step4 创建记事本文件
单击“开始“→”所有程序“→”附件“→”记事本“，创建一个文本文件（这种文件可去掉原来文件的所有格式），按组合键，将 “汇总表”粘贴到记事本文件上。用光标选中全部汇总内容，按组合键，准备再将最总表粘贴回Excel工作簿的工作表Sheet3中
Step4粘贴汇总表
将光标切换回Excel工作簿，光标单击工作表标签Sheet3,按组合键，将“汇总表”粘贴到工作表Sheet3中
Step5修设美化汇总表
①更改单元格A1的标题“班级“为”汇总项“。
②删除第二列的空列“学号“列。
③光标选择单元格区域A2:G18，鼠标左键单击菜单→“格式”→“单元格”，弹出对话框，切换到边框选项卡，颜色选项框选择“绿色”，在“线条样式选项框”中，外边框“选择”双线“，”内部“选择”虚线“，为汇总表设置边框。
④单击行标1，插入一行作为标题行，在单元格A1输入“高二年级期中考试成绩分类汇总表”，选中单元格区域A1:G1，单击常用工具栏“合并及居中”，字体为“黑体”，字号为“16”。
⑤选中单元格区域A2:G18，设置字体为宋体、字号为12、居中对齐。
⑥选中横向的字段名区域A2:G2，按下键，再用光标选中纵向的汇总项标题区域A3:A18，单击常用工具栏的 “填充颜色”按钮，选择“灰色-25%”完成图案设置。
⑦选中数据区域B3:G18，单击菜单“格式”→“单元格”，弹出“单元格格式”对话框。
⑧切换到数字选项卡，在“分类”选项框中选择“数字”在“小数位数”选项框中输入2。
⑨按键确认,完成数据保留两位小数的设置。
⑩选中单元格区域A2:A18，通过替换分别将最小值、最大值替换成最低分和最高分。
至此完成“汇总表”的修饰美化工作。
学校可以根据汇总数据的对比评价教师的教学效果，也可以根据各班级和年级的标准差所反映的离散度来了解各班学生各门课程的学习情况，以便制定下一步的教育方案。
|
