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

视频讲解

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

题库下载

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

题目要求

晓雨任职人力资源部门，她需要对企业员工Office应用能力考核报告进行完善和分析。按照如下要求帮助晓雨完成数据处理工作。

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

2．在“成绩单”工作表中，设置工作表标签颜色为标准红色；对数据区域套用“表样式浅色16”表格格式，取消镶边行后将其转换为区域。

3．删除“姓名”列中所有汉语拼音字母，只保留汉字。

4．设置“员工编号”列的数据格式为“001,002,…334”；在G3:K336单元格区域中的所有空单元格中输入数值0。

5．计算每个员工5个考核科目（Word、Excel、PowerPoint、Outlook和Visio）的平均成绩，并填写在“平均成绩”列。

6．在“等级”列中计算并填写每位员工的考核成绩等级，等级的计算规则如下：

等级分类	计算规则
不合格	5个考核科目中任一科目成绩低于60分
及格	60分≤平均成绩<75分
良	75分≤平均成绩<85分
优	平均成绩≥85分

7．适当调整“成绩单”工作表B2:M336单元格区域内各列列宽，并将该区域内数据设置为水平、垂直方向均居中对齐。

8．依据自定义序列“研发部→物流部→采购部→行政部→生产部→市场部”的顺序进行排序；如果部门名称相同，则按照平均成绩由高到低的顺序排序。

9．设置“分数段统计”工作表标签颜色为蓝色；参考考生文件夹中的“成绩分布及比例.png”示例，以该工作表B2单元格为起始位置创建数据透视表，计算“成绩单”工作表中平均成绩在各分数段的人数以及所占比例（数据透视表中的数据格式设置以参考示例为准，其中平均成绩各分数段下限包含临界值）；根据数据透视表在单元格区域E2:L17内创建数据透视图（数据透视图图表类型、数据系列、坐标轴、图例等设置以参考示例为准）。

10．根据“成绩单”工作表中的“年龄”和“平均成绩”两列数据，创建名为“成绩与年龄”的图表工作表（参考考生文件夹中的“成绩与年龄.png”示例，图表类型、样式、图表元素均以此示例为准）。设置图表工作表标签颜色为绿色，并将其放置在全部工作表的最右侧。

11．将“成绩单”工作表中的数据区域设置为打印区域，并设置标题行在打印时可以重复出现在每页顶端。

12．将所有工作表的纸张方向都设置为横向，并为所有工作表添加页眉和页脚，页眉中间位置显示“成绩报告”文本，页脚样式为“第1页，共？页”。

文字解析

第22组

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

2．【解析】步骤1：在“成绩单”工作表标签上单击右键，选择“工作表标签颜色”菜单中的标准色“红色”；

步骤2：在有数据区域单击一下，然后按<Ctrl+A>组合键选中所有数据的区域。单击“开始”|“样式”分组中的“套用表格样式”下拉按钮，在下拉列表中选择“表样式浅色16”，完成表格样式套用；

步骤3：单击取消“表格工具”|“设计”|“表格样式选项”分组中的“镶边行”复选框；

步骤4：单击“工具”分组中的“转化为区域”按钮，在弹出的对话框中选择“是”。

3．【解析】在N3单元格中输入“=LEFT(C3,LENB(C3)-LEN(C3))”，然后双击N3单元格右下角填充柄生成一个只保留C列中只有汉字的列。然后选择该列所有数据，选择“复制”命令，在C3单元格上单击鼠标右键，选择“粘贴选项”中的“值”，然后清除N列所有内容。

4．【解析】步骤1：单击选中B列，然后在选中区域单击右键，在下拉菜单中选择“设置单元格格式”命令。在弹出的“设置单元格格式”对话框，单击选中“数字”|“自定义”，然后在类型中输入“000”。

步骤2：选中G3到K336单元格区域，然后单击“开始”|“编辑”分组中“查找和选择”下拉按钮，在下拉列表中单击“定位条件”命令。在打开的“定位条件”对话框中，选择其中的“空值”选项，单击“确定”按钮。然后输入“0”，再按<Ctrl+Enter>组合键，完成空白单元格中输入数值0。

5．【解析】在L3单元格中输入“=AVERAGE(G3:K3)”，然后按回车键，双击L3单元格右下方的填充句柄完成L列其他单元格计算。

6．【解析】在M3输入“=IF(MIN(G3:L3)<60,"不合格",IF(L3<75,"及格",IF(L3<85,"良","优")))”，双击M3单元格右下方的填充句柄完成M列其他单元格计算。

7．【解析】选择有数据的所有列，在选中区域中任意二列交界处双击。使其自动调整列宽，再选择“对齐方式”分组中“垂直居中”和“居中”按钮，将该区域内数据设置为水平、垂直方向均居中对齐。

8．【解析】步骤1：单击有数据的区域，然后按<Ctrl+A>组合键选择所有有数据的区域，单击“数据”|“排序和筛选”分组中的“排序”命令，打开“排序”对话框。在主要关键字中选择“部门”，设置排序依据为“单元格值”，设置次序选择为“自定义序列”。

步骤2：在“自定义序列”对话框中，“输入序列”中输入“研发部”后按回车键，输入“物流部”后按回车键，输入“采购部”后按回车键，输入“行政部”后按回车键，输入“生产部”后按回车键，输入“市场部”后按回车键，单击“添加”按钮，最后单击“确定”按钮。

步骤3：单击“添加条件”按钮，在次要关键字中选中“平均成绩”，次序中选中“降序”，然后单击“确定”按钮。

9．【解析】步骤1：单击“分数段统计”工作表，在工作表标签上单击右键，选中“工作表标签颜色”中的“蓝色”。

步骤2：单击该工作表的B2单元格，单击“插入”|“表格”分组中的“数据透视表”按钮，打开“创建数据透视表”对话框。在“表/区域：”后面框中选择“成绩单”表的“$B$2:$M$336”区域，单击“确定”按钮。

步骤3：选中“数据透视表字段”中的“平均成绩”字段，将其拖动到“行”中，同样的方法将“平均成绩”字段拖动两次到“值”中。

步骤4：选中“行”下所有有数据的单元格，单击“数据透视工具”|“分析”|“组合”分组中的“分组选择”按钮.

步骤5：在打开的对话框中的“起始于：”后面的文本框中输入“60”，在“终止于：”后面的文本框中输入“100”，设置步长为“10”，单击“确定”按钮。

步骤6：单击选中“求和项：平均成绩”，然后单击“数据透视表工具”|“分析”|“活动字段”分组中的“字段设置”下拉按钮。

步骤7：在下拉列表中选择“计数”命令，并将“求和项：平均成绩”在编辑栏中修改为“人数”。

步骤8：单击选中“求和项：平均成绩2”，在“字段设置”下拉按钮中，选择“计数”，单击“计算”分组中的“值显示方式”下拉按钮.

步骤9：在下拉列表中选择“总计的百分比”命令，并在编辑栏中输入“所占比例”。

步骤10：适当调整列宽，选中“所在比例”列数据，单击“开始”|“数字”分组中的“减少小数位”按钮，将“所占比例”单元格格式设置为“百分比”，保留小数点后“1位”。

步骤11：选中所有数据，单击“数据透视表工具”|“分析”|“工具”分组中的“数据透视图”按钮。在弹出的“插入图表”对话框中，选中“柱形图”，然后单击“确定”按钮，插入“数据透视图”。

步骤12：右键单击“所占比例”柱形图，选择“设置数据表系列格式”命令。

步骤13：选择“系列选项”|“系列绘制在”中的“次坐标轴”选项，单击“关闭”按钮。

步骤14：在“所占比例”柱形图上单击右键，选择“更改图表类型”命令，选择“折线图”中“带数据标记的折线图”类型，然后单击“确定”按钮。

步骤15：单击“数据透视图工具”|“设计”|“图表布局”分组中的“添加图表元素”下拉按钮，单击“坐标轴”级联菜单中的“更多轴选项”，打开“设置坐标轴格式”窗口，将“坐标轴选项”中最小值设置为“0”，最大值设置为“0.4”，主要刻度单位为“0.1”，次要刻度单位为“0”，单击“数字”选项卡，设置小数位位数为“0”，单击“关闭”按钮。

步骤16：单击“数据透视图工具”|“设计”|“图表布局”分组中的“添加图表元素”下拉按钮，单击“图例”级联菜单中的“底部”命令。

步骤17：最后将图表移至E2到L17单元格区域。

10．【解析】步骤1：选中“成绩单”工作表中“年龄”和“平均成绩”两列数据，单击“插入”|“图表”分组中的“散点图”下拉菜单，在下拉列表中选择“散点图”。

步骤2：单击“图表”空白区域，然后单击右键，选择“移动图表”选项，在“新工作表”后面输入“成绩与年龄”工作表，单击“确定”按钮。

步骤3：单击“图表工具”|“设计”|“图表布局”分组中的“添加图表元素”下拉按钮，在列表中单击“图标标题”级联菜单中的“图表上方”命令，然后在图标标题中输入“我单位中青年职工Office应用水平更高”，将图标标题字体设置为“微软雅黑”，字号设置为“28”。

步骤4：单击“图表工具”|“设计”|“图表布局”分组中的“添加图表元素”下拉按钮，在列表中单击“坐标轴标题”级联菜单中的“主要横坐标轴”和“主要纵坐标轴”命令，然后在文本框中输入“年龄”，将字号调整为“16”；然后在文本框中输入“平均成绩”，将字号设置为“16”。

步骤5：单击“图表工具”|“设计”|“图表布局”分组中的“添加图表元素”下拉按钮，在列表中单击“图例”级联菜单中的“无”。

步骤6：在横坐标轴上单击鼠标右键，然后单击“设置坐标轴格式”，打开“设置坐标轴格式”窗口，设置最小值为“20”，设置最大值为“55”，设置主要刻度单位为“5”，设置次要刻度单位为“0”，单击“关闭”按钮。

步骤7：选中纵坐标轴。设置最小值为“40”，最大值为“100”，主要刻度单位为“10”，次要刻度单位为“0”，单击“关闭”按钮。

步骤8：在图表上数据标记点上单击右键，在下拉菜单中选择“设置数据系列格式”，打开“设置数据系列格式”对话框，选择“数据标记选项”，将数据标记类型修改为“内置”选择倒数第三个，将大小设置为“3”，单击“确定”按钮。

步骤9：“图表工具”|“设计”|“图表布局”分组中的“添加图表元素”下拉按钮，在列表中单击“趋势线”级联菜单中的“其他趋势线选项”，选中“趋势线选项”中选中“线性”，选中“显示公式”和显示“R平方值”复选框。单击“关闭”按钮，将公式和R平方值移到适当位置。

步骤10：在“成绩与年龄”工作表标签上单击右键，选择“工作表标签颜色”菜单中的标准色“绿色”；选中“成绩和年龄”工作表，然后将其拖至最右侧。

11．【解析】步骤1：选中“成绩单”工作表中的数据区域，单击“页面布局”选项卡中“页面设置”分组中的“打印区域”下拉按钮，在下拉按钮中选择“设置打印区域”命令，将其设置为打印区域。

步骤2：单击“页面设置”分组中的“打印标题”按钮，弹出“页面设置”对话框，在工作表选项卡中，将光标定位于“顶端标题行”后面的输入框中，并输入“$2:$2”，单击“确定”按钮，完成设置打印时可以重复每页顶端。

12．步骤1：选中“成绩单”工作表，单击“页面设置”分组中的“页面设置”对话框启动器，打开“页面设置”对话框，在“页面”选项卡下，选择方向为“横向”。

步骤2：单击“页眉/页脚”|“自定义页眉”按钮，在“中”输入框中输入“成绩报告”，单击“确定”。

步骤3：单击“页脚”下组合框，在组合框中选择“第1页，共？页”。单击“确定”按钮，完成“成绩单”工作表的页面设置。

步骤4：采用同样的方法完成“分段统计”和“成绩与年龄”工作表页面设置。保存并关闭Excel表格。