晓雨任职人力资源部门,她需要对企业员工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之后只判断了=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快速保存