3.3 考试成绩汇总表
案例背景
每门课程的空白单科成绩表通常需要发放给任课教师，待任课教师录入期中考试、期末考试和总评成绩后再汇总给班主任，最后由班主任根据单科成绩表再创建一份成绩汇总表。由于任课教师的单科成绩表中学生的顺序有可能不同，所以不能简单的用复制、粘贴的方法来制作。而单科成绩表、成绩汇总表工作簿文件和工作表的命名规范，以及单科成绩表、成绩汇总表的样式最好由学校教务处或年级组提前统一制作，以便于任课教师录入、班主任汇总。
现有某中学初三2班班主任需要设计一张单科成绩表发给任课教师录入成绩，待任课教师录入期中考试、期末考试和总评成绩后，再将本班各科成绩集中汇总到到一起，形成本班成绩汇总表。
关键技术点
要实现本案例中的功能，学员应该掌握以下EXCEL技术点。
●基础知识 页面设置，跨工作表引用单元格，选择性粘贴
●函数应用 INIRECT函数、MOD函数、VLOOKUP函数
●公式的设计与操作
最总效果展示
3.3.1创建单科成绩表
Step 1创建工作簿、重名工作表
创建工作簿“语文成绩表.xls”，然后将工作表重命名为“初三2班”，并删除多余工作表。（若该语文任课教师如果同时教初三1班，就再插入一张工作表，将其重命名为“初三1班”即可。）
Step2调整表格列宽
由于准备将A列设置为学号B列为姓名，C：E列为成绩，所以设置A列的列宽为“12.00”， B列的列宽为“8.00” C列的列宽为“6.00”。
Step3输入表格字段标题
①在A1单元格输入“学籍号”。
②在B1单元格输入“姓名”。
③分别在C1、D1和E1单元格输入“期中”、“期末”和“总评”。
④选中单元格区域A1:E1，将字体、字型、字号及对齐方式设置为“黑粗14号”及“居中对齐”。
Step4设置自定义格式
由于学籍号是一个10位数的代码，其中前4位数代表入学时间，中间的第5—7位是学校代码，而只有后3位才是自然顺序号，也就是说学籍号的前7 位数是一样的，只有后三位数是不同的，所以可以通过自定格式直接输入后三位非0有效数字，同时显示的又是10位数的学籍号。
①选中单元格区域A2:A60（假定每班学生不超过59人），按快捷键弹出“单元格格式”度画框，切换到“数字”选项卡。
②在左侧“分类”列表框中选择“自定义”，在右侧的“类型“里拖动鼠标选中默认的”G/通用格式“，输入”“”2005152“000“，然后单击确定按钮，此时“学籍号”的自定义格式就设置好了。
Step5设置条件格式
选定单元格区域C2:E60，设置成绩区域的条件格式，以红色显示数值低于“60”的单元格。
Step6设置表格边框
选定单元格区域A1:E60,设置表格边框。
Step7为“总评”成绩预设公式
①光标移到单元格E2，输入公式“=IF(OR(C2="",D2=""),"",C2*30%+D2*70%)”。
②移动光标到E2单元格的右下角，当光标变为+形状时向下拖曳填充柄至E60单元格，然后松开鼠标即可完成公式的填充。
Step8输入本班学生个人信息
①在单元格区域B1：B42输入本班的41名学生姓名。 ②对应于第一个学生“林俊华”的“学籍号”为“2005152044”，直接输入“44”后，按键确认，在单元格A2显示为“2005152044”，但是这时在编辑栏内显示的仍然是实际输入的“44”。
③参照上面的方法，陆续输入其余学生的学籍号。
Step9快速生成其它课程的成绩表
①单击菜单“文件”→“另存为”打开 “另存为”对话框，在“文件名”文本框中修改文件名为“数学成绩表.xls”，然后单击“保存”按钮。
②参照上面的方法生成“英语成绩表”、“物理成绩表”和“化学成绩表“。
至此“初三2班”的单科成绩表全部制作完毕，接下来可以分发给相关的任课教师，待他们输入期中考试和期末考试成绩后，单科成绩表会自动按照“期中”占30%，”期末”占70%的比例计算出“总评”成绩。输入成绩完毕的工作表的效果如图8所示。
其他年级和班级也可以通过复制使用这张成绩表，只不过文件名保留后三个字“成绩表”，前两字更换成相应课程名，而工作表重命名为相应班级名称即可。大到一个学校，小到一个年级使用统一样式的单科成绩表和汇总表十分必要，按照工作流程，这样便于前后工作地衔接，提高工作效率。随着下一节的学习，请您体会一下使用统一表样的必要性。
3.3.2创建成绩汇总表
班主任从本班任课教师处收回各单科成绩表后，接下来需要根据这些课程成绩表再创建一份班级成绩汇总表，以便全面了解本班学生本学期的学习情况。
Step 1创建工作簿、重名工作表
创建工作簿“成绩汇总表.xls”，然后将工作表重命名为“成绩汇总表”，并删除多余工作表。
Step2调整表格列宽
由于准备将A列设置为学号B列为姓名，C：Q列为成绩，所以设置A列的列宽为“12.00”， B列的列宽为“8.00” C：Q列的列宽为“6.00”。
Step3输入表格标题
选中A1：Q1单元格区域，设置格式为“合并及居中”，输入表头“考试成绩汇总表”然后设置字号为16，子形为“加粗”。
Step4输入表格字段标题
在A2:Q3分别输入各个字段的标题名称，并设置文本居中显示，字体、字号、字形为“标宋12加粗”，将A2:B2设为“合并及居中”。
Step5输入“学籍号“、“姓名”
打开任何一个单科成绩表，如“语文成绩表”,选中单元格区域 A2:B42，按组合键复制，再切换到“成绩汇总表”，选中A2单元格，单击常用工具栏的”粘贴“按钮右侧的下拉箭头，然后在弹出的下拉列表中选择“无边框”。
Step6打开单科成绩表
为了下一步计算INDIRECT函数公式的需要，依次打开需要汇总的“语文成绩表“、“数学成绩表“、“英语成绩表“、“物理成绩表“和“化学成绩表”。再单击工作表右上角的最小化图标，依次将这5个工作表最小化到任务栏中备用，然后让“成绩汇总表“处于活动窗口状态。
Step7调整“按键后移动方向”
单击菜单“工具”→“选项”弹出“选项”对话框，切换到’编辑”选项卡，在“设置”组合框中选中“按键后移动方向”列表框右侧的下箭头按钮，在下拉列表中选择“向右”，然后单击确定按钮。
注：INDIRECT函数的特殊要求
如果INDIRECT函数涉及对另外一个工作簿的引用（外部引用），那么那个工作簿必须打开。如果源工作簿没有打开，INDIRECT函数会返回错误值#REF！。
Step8汇总成绩
选中C4单元格输入以下公式，然后按键确认。
“=VLOOKUP($A4,INDIRECT("'["&C$2&"成绩表.xls]初三2班'!$A:$E"),MOD(COLUMN(),3)+3,0)”
Step9 复制填充公式
①选中C4单元格，然后向右拖曳该单元格右下角的填充柄至Q4单元格。完成第一个学生成绩的汇总。
②选中单元格区域C4：Q4，然后左键双击Q4单元格右下角的填充柄即可完成其余40学生成绩的汇总。
3.3.3美化成绩汇总表
Step1设置单元格文字格式
①选中A4：O44单元格区域，设置文本居中显示，字体、字号为“标宋12”。 ②按键，陆续选中C2，E2:F2，H2:I2，K2:L2，N2:O2，Q2单元格，单击常用工具栏的“文字颜色”的下箭头按钮，从下拉列表框选择白色。这样就使得前景色与背景色一致，在打印时文字设置成白色的单元格就看不见了，但是并不影响公式的复制填充。
Step2设置表格边框
①分别选中A2:B2，C2:E2,F2:H2,I2:K2,L2:N2,O2:Q2设置外框。
②选中单元格区域A3:Q44,为表格设置边框。
Step3打印预览
单击产用工具栏“打印预览”按钮。打开打印预览页面。
Step4设置纸张方向
①单击菜单“文件”“页面设置”，弹出“页面设置”对话框，在“方向”组合框中单击“横向”单选按钮，然后单击“确定”按钮。
Step5设置打印“标题行”
①单击菜单“文件”“页面设置”，弹出“页面设置”对话框，切换到“工作表”选项卡，在“打印标题”组合框中单击“顶端标题行”右边的按钮，
②在弹出的“页面设置-顶端标题行”对话框中用鼠标选取$1:$3，再单击该按钮返回“页面设置”对话框，单击确定按钮即可完成打印“标题行”的设置。
Step6隐藏公式
“成绩汇总表”中的部分单元格含有公式，为了后续工作使用起来方便，需要隐藏公式。
①选中含有公式的单元格区域C4:Q44，单击常用工具栏“复制”按钮，然后单击右键，在弹出的快捷菜单中选择“选择性粘贴”。
②在弹出的“选择性粘贴”对话框中单击“粘贴”选项框中的“数值“单选按钮，然后单击确定按钮。
此时单击C4:Q44的任意一个单元格，可以看到单元格中的公式已被取消了。
|
