真题练习50-Excel电子表格-全国计算机等级考试二级MS Office高级应用与设计考试【汪老师的个人课堂】

题目要求

第50组【类似第3套】第1组

蒋老师负责学生的成绩管理，根据下列要求帮助蒋老师对本次考试成绩进行整理和分析，要求操作过程中不得随意改变原数据表数据的排列顺序。

1．打开考生文件夹下的素材文档Excel.xlsx（“.xlsx”为文件扩展名），后续操作均基于此文件，否则不得分。

2．按照下列要求对工作表“第一学期期末成绩”中的数据列表进行格式化操作：

① 在“学号”的左侧插入一个空白列，列标题为“序号”，然后在该列中依次输入序列号“1、2、3……”，通过设置数字格式令其显示为“001、002、003……”、且能够参与计算。

② 将B列“学号”数据设为文本格式；将所有成绩列的数字格式设为显示两位小数的数值。

③ 适当加大行高、调整列宽，改变字体、字号，并设置对齐方式。

④ 为数据区域添加标准深蓝色的内外边框线，并通过设置条件格式使得数据区域的奇数行填充“金色,个性色4,淡色60%”。

3．将语文、数学、英语三科中不低于110分的成绩所在的单元格以一种颜色填充，其他四科中高于95分的成绩以另一种字体颜色标出，所用颜色的深浅应保证数据能够被清晰识别。

4．学号第3、4位代表学生所在的班级，例如："120105"代表12级1班5号。要求通过公式提取每个学生所在的班级并按下列对应关系填写在“班级”列中。

“学号”的3、4位	对应班级
01	1班
02	2班
03	3班
04	4班

5．分别利用Sum和Average函数计算每一个学生的总分及平均分并填入对应列中。

6．分别使用不同的方式计算每个学生的总成绩在全年级的排名，并显示为“第N名”。其中N列“年级排名(普通）”中显示美式排名，即成绩相同的名次占用位次（例如若有两个并列第1名，则没有第2名，下一个从第3名继续）；O列“年级排名(中式)”中显示中式排名，即成绩相同的名次不占用位次（例如若有两个并列第1名，下一个仍从第2名继续）。排名时不得改变数据顺序。

7．复制工作表“第一学期期末成绩”，将副本放置到原表右侧；改变该副本表标签的颜色，并重新命名为“分类汇总”。删除工作表“分类汇总”中的两个排名列。

8．在工作表“分类汇总”中，通过分类汇总功能求出每个班各科的平均成绩，并设置每组结果分页显示。

9．以分类汇总结果为基础，参照图片“E图表示例.png”创建一个组合图表，对每个班各科平均成绩进行比较，并将该图表放置在一个名为“柱状分析图”的新工作表的A1:M30单元格区域内。要求图表的类型、系列、坐标轴、图表标题、图例等的格式与内容与示例一致。

文字解析

第50组

1．【解析】双击打开考生文件夹下的“Excel.xlsx”文件。

2．【解析】①步骤1：选中A列单元格，单击“开始”|“单元格”分组中的“插入”下拉按钮，在列表中单击“插入工作表列”命令；

步骤2：在A1单元格中输入“序号”，在A2单元格中输入“1”、A3单元格中输入“2”，选中A2和A3单元格，然后双击智能填充柄，完成其他行填充；

步骤3：选中A2:A121单元格区域，单击“开始”|“数字”分组中的对话框启动器，打开“设置单元格格式”对话框；

步骤4：选中分类中的“自定义”，在类型中输入“000”，单击“确定”按钮。

②步骤1：选中B列单元格，单击“开始”|“数字”分组中的数字格式下拉按钮，在列表中选中“文本”；

步骤2：选中“E2:M121”单元格区域，单击“开始”|“数字”分组中的对话框启动器，打开“设置单元格格式”对话框，选中分类中的“数值”，在小数位数中输入“2”，单击“确定”按钮。

③步骤1：选中整个数据区域，单击“开始”|“单元格”分组中的“格式”下拉按钮，在列表中单击“行高”命令，打开“行高”对话框。将行高数值适当增加，单击“确定”按钮；

步骤2：单击“开始”|“单元格”分组中的“格式”下拉按钮，在列表中单击“列宽”命令，打开“列宽”对话框。输入适当增加的列宽数值，单击“确定”按钮；

步骤3：在“开始”|“字体”分组中改变字体为其他的字体（例如：黑体），改变字号（例如：14）；

步骤4：在“开始”|“对齐方式”分组中修改各个单元格的对齐方式，例如第一行单元格设置为水平和垂直均居中，序号、学号、姓名列设置为水平和垂直均居中。

④步骤1：选中整个数据区域，单击“开始”|“字体”分组中的“边框”下拉按钮，在列表中单击“其他边框”命令，打开“设置单元格格式”对话框；

步骤2：在“边框”选项卡中，在直线样式中选中一种样式（例如单实线），单击“颜色”下拉按钮，在列表中选中标准色中的“蓝色”，选中预置中的“外边框”和“内部”，单击“确定”按钮；

步骤3：选中整个数据区域，单击“开始”|“样式”分组中的“条件格式”下拉按钮，单击“新建规则”按钮，打开“新建格式规则”对话框。

步骤4：在“选择规则类型”列表中选中“使用公式确定要设置格式的单元格”，在“为符合此公式的值设置格式”中输入“=MOD(ROW($A3),2)=1”，单击“格式”按钮，打开“设置单元格格式”对话框；

步骤5：在“填充”选项卡中选中背景色中的“金色,个性色4,淡色60%”，单击“确定”按钮，再次单击“确定”按钮。

3．【解析】步骤1：选中“E2:G121”单元格区域，单击“开始”|“样式”分组中的“条件格式”下拉按钮，在列表中单击“突出显示单元格规则”级联菜单中的“大于”命令，打开“大于”对话框；

步骤2：在数值中输入“109”，在“设置为”中选中一种填充颜色（例如：浅红色填充），单击“确定”按钮；

步骤3：选中“H2:K121”单元格区域，单击“开始”|“样式”分组中的“条件格式”下拉按钮，在列表中单击“突出显示单元格规则”级联菜单中的“大于”命令，打开“大于”对话框；

步骤4：在数值中输入“95”，在“设置为”中选中一种填充颜色（例如：红色文本），单击“确定”按钮。

4．【解析】在D2单元格中输入公式“=VALUE(MID(B2,3,2)) & "班"”，按回车键，双击D2单元格的智能填充柄，完成其他行填充；

5．【解析】步骤1：在L2单元格中输入公式“=SUM(E2:K2)”，按回车键，双击L2单元格的智能填充柄，完成其他行填充；

步骤2：在M2单元格中输入公式“=AVERAGE(E2:K2)”，按回车键，双击M2单元格的智能填充柄，完成其他行填充。

6．【解析】步骤1：在N2单元格中输入公式“="第" & RANK(L2,$L$2:$L$121) & "名"”，按回车键，双击N2单元格的智能填充柄，完成其他行填充；

步骤2：在O2单元格中输入公式“="第" & SUMPRODUCT(($L$2:$L$121>L2)*(1/COUNTIF($L$2:$L$121,$L$2:$L$121)))+1 & "名"”，按回车键，双击N2单元格的智能填充柄，完成其他行填充。

7．【解析】步骤1：在工作表“第一学期期末成绩”标签上单击鼠标右键，在弹出菜单中单击“移动或复制”命令，打开“移动或复制工作表”对话框；

步骤2：选中“下列选定工作表之前”中的“（移至最后）”，选中“建立副本”复选框，单击“确定”按钮；

步骤3：在复制出的工作表标签上单击鼠标右键，然后在弹出菜单中单击“工作表标签颜色”级联菜单的一种颜色（例如：红色）；

步骤4：双击复制出的工作表标签，将工作表名称改为“分类汇总”；

步骤5：选中第N列和第O列，单击“开始”|“单元格”分组中的“删除”按钮。

8．【解析】步骤1：选中“分类汇总”工作表中的数据区域，单击“数据”|“排序和筛选”分组中的“排序”按钮，打开“排序”对话框，在“排序依据“中选中“班级”，单击“确定”按钮；

步骤2：单击“数据”|“分级显示”分组中的“分类汇总”按钮，打开“分类汇总”对话框，在分类字段中选中“班级”、汇总方式中选中“平均值”、选定汇总项中选中“语文”“数学”“英语”“物理”“化学”“地理”和“历史”，选中“每组数据分页”复选框，单击“确定”按钮。

9．【解析】步骤1：选中打开考生文件夹下的“E图表示例.png”；

步骤2：在“分类汇总”工作表的左上方点“2”，将分类汇总结果显示为2级；

步骤3：将“1班 平均值”改成“1班 平均分”，将“2班 平均值”改成“2班 平均分”，将“3班 平均值”改成“3班 平均分”，将“4班 平均值”改成“4班 平均分”，将“总计 平均值”改成“年级 平均分”；

步骤4：选中D1:K126单元格区域，单击“插入”|“图表”分组中的“插入组合图”下拉按钮，在列表中单击“簇状柱形图-折线图”；

步骤5：单击工作表右侧的“加号”新建一个工作表，并将名称改为“柱状分析图”，在“分类汇总“工作表的图表上单击鼠标右键，然后单击“移动图表”命令，打开“移动图表”对话框，在“对象位于”右侧的下拉框中选中“柱状分析图”，单击“确定”按钮；

步骤6：拖放图表到A1:M30单元格区域；

步骤7：单击“图表设计”|“数据”分组中的“切换行/列”按钮；

步骤8：单击“图表设计”|“类型”分组中的“更改图表类型”按钮，打开“更改图表类型”对话框，将“1班 平均分”“2班 平均分”“3班 平均分”和“4班 平均分”的图表类型均改成“簇状柱形图”，选中“年级 平均分”后面的“次坐标轴”复选框，单击“确定”按钮；

步骤9：将图标标题改为“各班各科平均成绩比较”，拖动图表标题到图表标题的右侧；

步骤10：选中左侧“垂直（值）轴”，单击“图表工具”|“格式”|“当前所选内容”分组中的“设置所选内容格式”按钮，打开“设置坐标轴格式”窗口，设置边界的最小值为“50”、最大值为“110”；

步骤11：选中右侧“垂直（值）轴”，在“设置坐标轴格式”窗口，设置边界的最小值为“0”、最大值为“100”；

步骤12：选中折线图，单击“图表设计”|“图表布局”分组中的“添加图表元素”下拉按钮，在列表中选中“数据标签”级联菜单中的“上方”；

步骤13：选中“图表设计”|“图表样式”分组中的“样式6”；

步骤14：双击折线图，在打开的“设置数据点格式”窗口中，单击“填充与线条”选项，设置线条颜色为“红色”，设置标记选项中的内置类型为菱形，适当调整大小，设置填充颜色为“绿色”。关闭“设置数据点格式”窗口。

保存并关闭Excel.xlsx工作簿。

题库下载

全国计算机等级考试题库下载（用电脑下载安装）：请点击

视频讲解

真题练习50-Excel电子表格_哔哩哔哩_bilibili