电子表格题
小李是北京某政法学院教务处的工作人员，法律系提交了2012级4个法律专业教学班的期末成绩单，为更好地掌握各个教学班学习的整体情况，教务处领导要求她制作成绩分析表，供学院领导掌握宏观情况。
请根据考生文件夹下的“素材.xlsx”工作簿，帮助小李完成2012级法律专业学生期末成绩分析表的制作。具体要求如下。
问答题
12. 将“素材.xlsx”工作簿另存为“年级期末成绩分析.xlsx”工作簿，以下所有操作均基于此新保存的工作簿。
【正确答案】
【解题步骤】
打开考生文件夹下的“素材.xlsx”工作簿，选择【文件】→【另存为】选项，打开“另存为”对话框，在“文件名”文本框中输入“年级期末成绩分析”，单击“保存”按钮，将其保存于考生文件夹下。
【答案解析】
问答题
13. 在“2012级法律”工作表最右侧依次插入“总分”、“平均分”和“年级排名”列；将工作表的第一行根据表格实际情况合并居中为一个单元格，并设置合适的字体、字号，使其成为该工作表的标题。对班级成绩区域套用带标题行的“表样式中等深浅15”的表格格式。设置所有列的对齐方式为居中，其中排名为整数，其他成绩的数值保留1位小数。
【正确答案】【解题步骤】
步骤1：选择M2单元格，输入“总分”；选择N2单元格，输入“平均分”；选择O2单元格，输入“年级排名”。
步骤2：选择A1:O1单元格区域，在【开始】→【对齐方式】组中单击“合并后居中”按钮，即可将工作表的第一行合并居中为一个单元格。
步骤3：保持单元格的选中状态，在【开始】→【字体】组的“字体”下拉列表框中选择“微软雅黑”选项：在“字号”下拉列表框中选择“20”选项。
步骤4：选择A2:O102单元格区域，在【开始】→【样式】组中单击“套用表格样式”按钮，在弹出的下拉列表框的“中等深浅”栏中选择“表样式中等深浅15”选项。打开“套用表格式”对话框，保持默认设置，单击“确定”按钮即可为选中的区域套用表格样式。
步骤5：保持单元格区域的选中状态，在【开始】→【对齐方式】组中单击“居中”按钮，即可将对齐方式设置为居中。
步骤6：选择D3:N102单元格区域，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框。单击“数字”选项卡，在左侧“分类”列表框中选择“数值”选项，在“小数位数”数值框中输入“1”，单击“确定”按钮。
步骤7：选择O3:O102单元格区域，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”选项，打开“设置单元格格式”对话框。单击“数字”选项卡，在左侧“分类”列表框中选择“数值”选项，在“小数位数”数值框中输入“0”，单击“确定”按钮。
步骤1：选择M2单元格，输入“总分”；选择N2单元格，输入“平均分”；选择O2单元格，输入“年级排名”。
步骤2：选择A1:O1单元格区域，在【开始】→【对齐方式】组中单击“合并后居中”按钮，即可将工作表的第一行合并居中为一个单元格。
步骤3：保持单元格的选中状态，在【开始】→【字体】组的“字体”下拉列表框中选择“微软雅黑”选项：在“字号”下拉列表框中选择“20”选项。
步骤4：选择A2:O102单元格区域，在【开始】→【样式】组中单击“套用表格样式”按钮，在弹出的下拉列表框的“中等深浅”栏中选择“表样式中等深浅15”选项。打开“套用表格式”对话框，保持默认设置，单击“确定”按钮即可为选中的区域套用表格样式。
步骤5：保持单元格区域的选中状态，在【开始】→【对齐方式】组中单击“居中”按钮，即可将对齐方式设置为居中。
步骤6：选择D3:N102单元格区域，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框。单击“数字”选项卡，在左侧“分类”列表框中选择“数值”选项，在“小数位数”数值框中输入“1”，单击“确定”按钮。
步骤7：选择O3:O102单元格区域，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”选项，打开“设置单元格格式”对话框。单击“数字”选项卡，在左侧“分类”列表框中选择“数值”选项，在“小数位数”数值框中输入“0”，单击“确定”按钮。
【答案解析】
问答题
14. 在“2012级法律”工作表中，利用公式分别计算“总分”“平均分”“年级排名”列的值。对学生成绩不及格(小于60)的单元格套用格式突出显示为“黄色(标准色)填充色红色(标准色)文本”。
【正确答案】【解题步骤】
步骤1：选择M3单元格，在其对应的编辑栏中输入“=SUM(D3:L3)”，然后按【Enter】键确认。拖动M3单元格右下角的智能填充柄，向下填充至M102单元格。
步骤2：选择N3单元格，在其对应的编辑栏中输入“=AVERAGE(D3:L3)”，然后按【Enter】键确认。拖动N3单元格右下角的智能填充柄，向下填充至N102单元格。
步骤3：选择O3单元格，在其对应的编辑栏中输入“=RANK(M3,M$3:M$102,0)”，然后按【Enter】键确认。拖动O3单元格右下角的智能填充柄，向下填充至O102单元格。
步骤4：选择D3:L102单元格区域，在【开始】→【样式】组中单击“条件格式”按钮，在弹出的下拉列表中选择“突出显示单元格规则”选项，在弹出的子列表中选择“小于”选项。打开“小于”对话框，①在“为小于以下值的单元格设置格式”文本框中输入“60”；②在“设置为”下拉列表中选择“自定义格式”选项；③打开“设置单元格格式”对话框，单击“字体”选项卡；④在“颜色”下拉列表框的“标准色”栏中选择“红色”选项；⑤单击“填充”选项卡；⑥在“背景色”栏的“标准色”栏中选择“黄色”选项；⑦单击“确定”按钮，返回到“小于”对话框中；⑧再次单击“确定”按钮，如图所示。
步骤1：选择M3单元格，在其对应的编辑栏中输入“=SUM(D3:L3)”，然后按【Enter】键确认。拖动M3单元格右下角的智能填充柄，向下填充至M102单元格。
步骤2：选择N3单元格，在其对应的编辑栏中输入“=AVERAGE(D3:L3)”，然后按【Enter】键确认。拖动N3单元格右下角的智能填充柄，向下填充至N102单元格。
步骤3：选择O3单元格，在其对应的编辑栏中输入“=RANK(M3,M$3:M$102,0)”，然后按【Enter】键确认。拖动O3单元格右下角的智能填充柄，向下填充至O102单元格。
步骤4：选择D3:L102单元格区域，在【开始】→【样式】组中单击“条件格式”按钮，在弹出的下拉列表中选择“突出显示单元格规则”选项，在弹出的子列表中选择“小于”选项。打开“小于”对话框，①在“为小于以下值的单元格设置格式”文本框中输入“60”；②在“设置为”下拉列表中选择“自定义格式”选项；③打开“设置单元格格式”对话框，单击“字体”选项卡；④在“颜色”下拉列表框的“标准色”栏中选择“红色”选项；⑤单击“填充”选项卡；⑥在“背景色”栏的“标准色”栏中选择“黄色”选项；⑦单击“确定”按钮，返回到“小于”对话框中；⑧再次单击“确定”按钮，如图所示。
【答案解析】
问答题
15. 在“2012级法律”工作表中，利用公式并根据学生的学号，将其班级的名称填入“班级”列，规则为学号的第3位为专业代码、第4位代表班级序号，即01为“法律一班”，02为“法律二班”，03为“法律三班”，04为“法律四班”。
【正确答案】【解题步骤】
选择A3单元格，在该单元格内输入“=IF(MID(B3,3,2)="01","法律一班",IF(MID(B3,3,2)="02","法律二班",IF(MID(B3,3,2)="03","法律三班","法律四班")))”，按【Enter】键完成操作，拖动A3单元格右下角的智能填充柄，向下填充至A102单元格。
选择A3单元格，在该单元格内输入“=IF(MID(B3,3,2)="01","法律一班",IF(MID(B3,3,2)="02","法律二班",IF(MID(B3,3,2)="03","法律三班","法律四班")))”，按【Enter】键完成操作，拖动A3单元格右下角的智能填充柄，向下填充至A102单元格。
【答案解析】
问答题
16. 根据“2012级法律”工作表，创建一个数据透视表，放置于表名为“班级平均分”的新工作表中，工作表标签颜色设置为红色。要求数据透视表中按照英语、体育、计算机、近代史、法制史、刑法、民法、法律英语、立法法的顺序统计各班各科成绩的平均分，其中行标签为班级。为数据透视表格内容套用带标题行的“数据透视表样式中等深浅15”的表格格式，所有列的对齐方式设为居中，成绩的数值保留1位小数。
【正确答案】【解题步骤】
步骤1：选择A2:O102单元格区域，在【插入】→【表格】组中单击“数据透视表”按钮，打开“创建数据透视表”对话框，选择“数据透视表”选项。在“选择放置数据透视表的位置”栏中单击选中“新工作表”单选项，单击“确定”按钮即可新建一工作表。
步骤2：在新建的工作表标签上双击，输入“班级平均分”，按【Enter】键，在该工作表标签上单击鼠标右键，在弹出的快捷菜单中选择“工作表标签颜色”选项，在弹出的子菜单的“标准色”栏中选择“红色”选项。
步骤3：在该工作表的“数据透视表字段列表”窗格中，将“选择要添加到报表的字段”列表框中的“班级”字段拖动到“行标签”字段框中，将“英语”字段拖动到“数值”字段框中。
步骤4：在“数值”字段框中单击“英语”字段右侧的下拉按钮，在弹出的下拉列表中选择“值字段设置”选项，打开“值字段设置”对话框，在“值汇总方式”选项卡的“计算类型”列表框中选择“平均值”选项，单击“确定”按钮。
步骤5：在“选择要添加到报表的字段”列表框中将“体育”字段拖动到“数值”字段框中，单击“体育”字段右侧的下拉按钮，在弹出的下拉列表中选择“值字段设置”选项，打开“值字段设置”对话框的“值汇总方式”选项卡，在“计算类型”列表框中选择“平均值”选项，单击“确定”按钮。
步骤6：按照同样的方法，在“选择要添加到报表的字段”列表框中，将“计算机”“近代史”“法制史”“刑法”“民法”“法律英语”“立法法”字段拖动到“数值”字段框中，单击该字段右侧的下拉按钮，在弹出的下拉列表中选择“值字段设置”选项，打开“值字段设置”对话框的“值汇总方式”选项卡，在“计算类型”列表框中选择“平均值”选项，单击“确定”按钮。
步骤7：单击“关闭”按钮，关闭“数据透视表字段列表”窗格。选择A3:J8单元格区域，在【数据透视表工具设计】→【数据透视表样式】组的列表框中的单击右侧的“其他”按钮，在弹出的下拉列表框的“中等深浅”栏中选择“数据透视表样式中等深浅15”选项。
步骤8：保持A3:J8单元格区域的选择状态，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”选项，打开“设置单元格格式”对话框的“数字”选项卡，在“分类”列表框中选择“数值”选项，在“小数位数”数值框中输入“1”。单击“对齐”选项卡，在“文本对齐方式”栏的“水平对齐”和“垂直对齐”下拉列表框中都选择“居中”选项，单击“确定”按钮。
步骤1：选择A2:O102单元格区域，在【插入】→【表格】组中单击“数据透视表”按钮，打开“创建数据透视表”对话框，选择“数据透视表”选项。在“选择放置数据透视表的位置”栏中单击选中“新工作表”单选项，单击“确定”按钮即可新建一工作表。
步骤2：在新建的工作表标签上双击，输入“班级平均分”，按【Enter】键，在该工作表标签上单击鼠标右键，在弹出的快捷菜单中选择“工作表标签颜色”选项，在弹出的子菜单的“标准色”栏中选择“红色”选项。
步骤3：在该工作表的“数据透视表字段列表”窗格中，将“选择要添加到报表的字段”列表框中的“班级”字段拖动到“行标签”字段框中，将“英语”字段拖动到“数值”字段框中。
步骤4：在“数值”字段框中单击“英语”字段右侧的下拉按钮，在弹出的下拉列表中选择“值字段设置”选项，打开“值字段设置”对话框，在“值汇总方式”选项卡的“计算类型”列表框中选择“平均值”选项，单击“确定”按钮。
步骤5：在“选择要添加到报表的字段”列表框中将“体育”字段拖动到“数值”字段框中，单击“体育”字段右侧的下拉按钮，在弹出的下拉列表中选择“值字段设置”选项，打开“值字段设置”对话框的“值汇总方式”选项卡，在“计算类型”列表框中选择“平均值”选项，单击“确定”按钮。
步骤6：按照同样的方法，在“选择要添加到报表的字段”列表框中，将“计算机”“近代史”“法制史”“刑法”“民法”“法律英语”“立法法”字段拖动到“数值”字段框中，单击该字段右侧的下拉按钮，在弹出的下拉列表中选择“值字段设置”选项，打开“值字段设置”对话框的“值汇总方式”选项卡，在“计算类型”列表框中选择“平均值”选项，单击“确定”按钮。
步骤7：单击“关闭”按钮，关闭“数据透视表字段列表”窗格。选择A3:J8单元格区域，在【数据透视表工具设计】→【数据透视表样式】组的列表框中的单击右侧的“其他”按钮，在弹出的下拉列表框的“中等深浅”栏中选择“数据透视表样式中等深浅15”选项。
步骤8：保持A3:J8单元格区域的选择状态，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”选项，打开“设置单元格格式”对话框的“数字”选项卡，在“分类”列表框中选择“数值”选项，在“小数位数”数值框中输入“1”。单击“对齐”选项卡，在“文本对齐方式”栏的“水平对齐”和“垂直对齐”下拉列表框中都选择“居中”选项，单击“确定”按钮。
【答案解析】
问答题
17. 在“班级平均分”工作表中，针对各课程的班级平均分创建二维的簇状柱形图，其中水平簇标签为班级，图例项为课程名称，并将图表放置在表格下方的A10:H30单元格区域中。
【正确答案】【解题步骤】
步骤1：选择A3:J7单元格区域，在【插入】→【图表】组中单击“柱形图”按钮，在弹出的下拉列表框的“二维柱形图”栏中选择“簇状柱形图”选项。
步骤2：适当调整柱形图的位置和大小，使其放置在表格下方的A10:H30单元格区域中。在快速访问工具栏中单击“保存”按钮，保存工作簿。
步骤1：选择A3:J7单元格区域，在【插入】→【图表】组中单击“柱形图”按钮，在弹出的下拉列表框的“二维柱形图”栏中选择“簇状柱形图”选项。
步骤2：适当调整柱形图的位置和大小，使其放置在表格下方的A10:H30单元格区域中。在快速访问工具栏中单击“保存”按钮，保存工作簿。
【答案解析】