C22-【计算机二级】Excel操作题(22)分析企业员工Office应用能力考核报告

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

1.打开素材文档“Excel素材.xlsx” (“.xlsx"为扩展名），将其另存为“Excel.xlsx”，后续操作均基于此文件，否则不得分。

• 考点1：另存为

按F12，将Excel素材.xlsx”另存为“Excel.xlsx”，保存在考生文件夹下。

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

①在“成绩单”工作表中，设置工作表标签颜色为标准红色

• 考点2：修改工作表标签颜色

②对数据区域套用“表样式浅色16”表格格式，取消镶边行后将其转换为区域

• 考点3：套用表格格式

取消镶边行

转换为区域

• 考点4：将表格转换为区域

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

• 考点5：查找替换

利用word当中的查找替换功能来辅助

①将excel表中的姓名列复制粘贴到word当中

②将任意字母替换为空值

②将半角空格替换为空值

③将处理过的数据重新粘贴回excel表中

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

①设置“员工编号”列的数据格式为“001，002，…，334”

• 考点6：设置单元格格式

②在G3:K336单元格区域中的所有空单元格中输入数值0

方法一：将空值替换为0

• 考点7：查找替换

方法二：利用定位条件找出所有空值，输入0后按下Ctrl+回车键完成统一输入

• 考点8：定位条件

输入0，按住Ctrl+回车键

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

• 考点9：AVERAGE函数

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

• 考点10：IF函数和OR函数嵌套

①用OR函数判断考试成绩是否不合格：只有五个科目全部不低于60分才判定为合格，否则都判定为不合格。

②与IF函数嵌套

注意，第二个IF之后只判断了<75而没有判断是否>=60是因为只要五个科目的分数都不低于60，平均分就一定大于60。

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

①调整列宽

②将该区域内数据设置为水平、垂直方向均居中对齐

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

• 考点11：自定义排序

①主要关键字：

依据自定义序列“研发部→物流部→采购部→行政部→生产部→市场部”的顺序进行排序

②次要关键字：

按照平均成绩由高到低的顺序排序

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

成绩分布及比例参考示例

①设置“分数段统计”工作表标签颜色为蓝色

②以该工作表B2单元格为起始位置创建数据透视表

• 考点12：创建数据透视表

③计算“成绩单”工作表中平均成绩在各分数段的人数以及所占比例

将“平均成绩”拖动到行，“姓名”拖动到值（两次）

④依据示例图修改数据透视表的标题，并对成绩进行分段

修改“所占比例”的值显示方式

将“所占的百分比”单元格格式改为保留1位小数

⑤根据数据透视表在单元格区域E2:L17内创建数据透视图

• 考点13：创建数据透视图

设置图例位置靠下

 

刻度线在外部

修改填充颜色

 

设置数据系列格式

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

“成绩与年龄.png”示例

①创建新的

②移动图表

③设置工作表标签为绿色

③修改纵坐标、横坐标

④添加横纵坐标轴标题

记得要添加外部的刻度线

⑤更改纵坐标轴标题方向

⑥删除网格线

⑦添加趋势线和公式

选择线性的趋势线

显示公式，显示R的平方值

将趋势线的线型改为实线，颜色改为黑色，移动图中公式的位置，调整字号大小

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

• 考点14：页面设置

①设置打印区域

全选“成绩单”工作表内容，页面布局→打印区域→设置打印区域

②选择标题行

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

①将所有工作表的纸张方向都设置为横向

②为所有工作表添加页眉和页脚

注意：“成绩与年龄”工作表属于图表工作表，与前两个工作表类型不同，需要分别进行设置

Ⅰ、自定义页眉

Ⅱ、自定义页脚

Ⅲ、成绩与年龄工作表也是用同样的方法设置页眉和页脚

Ctrl+S快速保存