视频讲解
真题练习40-Excel电子表格_哔哩哔哩_bilibili
题库下载
题目要求
第40套
小许是某企业人力资源部门的工作人员，现在要使用Excel来统计2019年员工情况。根据下列要求，帮助小许运用已有的原始数据完成下列工作。
1．打开考生文件夹下的素材文档Excel.xlsx（“.xlsx”为扩展名），后续操作均基于此文件，否则不得分。
2．在“19年入职”工作表中的A列到E列中，填入19年新入职的员工信息（出现在“19年末”工作表中，但不出现在“19年初”工作表中的记录）。
3．在“19年离职”工作表中的A列到E列中，填入19年离职的员工信息（出现在“19年初”工作表中，但不出现在“19年末”工作表中的记录）。
4．对“19年入职”和“19年离职”工作表中的数据进行排序，首先按照部门拼音首字母升序排序，如果部门相同则按照工号升序排序。
5．在工作表“19年末”中，不要改变记录的顺序，完成下列工作：
① 将“出生日期”列中的数据转换为日期格式，如“1968年10月9日”。
② 在“年龄”列中，计算每位员工在2019年12月31日的年龄，规则为“××岁××个月”（不足1个月按0个月计算），例如“1968年10月9日”出生的员工，年龄显示为“51岁2个月”。
③ 在“电话类型”列中，填入每个员工的电话的类型，如果电话号码是11位填入“手机”，如果电话号码为8位，填入“座机”。
④ 通过设置单元格格式，将“电话”列中的数据显示为星号，如果是手机号码显示11个星号，座机号码显示8个星号。
6．根据“19年末”工作表中的数据，自新的名为“员工数据分析”工作表的A6单元格开始创建数据透视表和数据透视图，要求如下（可参考效果图“数据透视表和数据透视图.png”）：
① 统计每个部门中员工的数量和占比，占比保留整数；
② 按照效果图，修改数据透视表的列标题名称。
③ 在D6:L16单元格区域中，创建数据透视图，按照效果图设置网格线和坐标轴，删除所有字段按钮，并将图例置于底部。
④ 在A1:E5单元格区域中，为“学历”字段插入切片器，显示为5列1行，且按钮从左到右按照“博士、硕士、本科、大专和中专”的顺序显示。
⑤ 将A1:L17单元格区域设置为打印区域，页面方向为横向，确保内容在一个页面中，垂直和水平方向都居中对齐。
7．保护工作表“19年末”，在保护状态下，工作表中的所有单元格都可以被选中，但是“电话号码”列中的数据不会在编辑栏中显示实际号码（注意：不要使用密码）。
文字解析
第40组
1．【解析】打开考生文件夹下的素材文档“Excel.xlsx”。
2．【解析】步骤1：选中“19年末”工作表中的A2:E51单元格区域，然后按<Ctrl+C>复制，进入到“19年入职”工作表，选中A2单元格，然后按<Ctrl+V>粘贴；
步骤2：在F2中使用函数查找：=VLOOKUP(B2,'19年初'!$B$2:$E$55,2,0)，双击智能填充柄，完成其他行填充；
步骤3：选中“19年入职”工作表中所有数据区域，单击【数据】|【排序和筛选】分组中的“排序”按钮，打开“排序”对话框，在主要关键字中选中“列F”，单击“确定”按钮；
步骤4：选中F列中包含“男”和“女”的行并删除，删除F列内容。
3．【解析】步骤1：选中“19年初”工作表中的A2:E55单元格区域，然后按<Ctrl+C>复制，进入到“19年离职”工作表，选中A2单元格，然后按<Ctrl+V>粘贴；
步骤2：在F2中使用函数查找：=VLOOKUP(B2,'19年末'!$B$2:$H$51,2,0)，双击智能填充柄，完成其他行填充；
步骤3：选中“19年离职”工作表中所有数据区域，单击【数据】|【排序和筛选】分组中的“排序”按钮，打开“排序”对话框，在主要关键字中选中“列F”，单击“确定”按钮；
步骤4：选中F列中包含“男”和“女”的行并删除，删除F列内容。
4．【解析】步骤1：选中“19年入职”工作表中所有数据区域，单击【数据】|【排序和筛选】分组中的“排序”按钮，打开“排序”对话框，在主要关键字中选中“部门”，单击“选项”按钮，打开“排序选项”对话框，选中方法中的“字母排序”，次序中选中“升序”，单击“确定”按钮；
步骤2：单击“添加条件”按钮，在次要关键字中选中“工号”，次序中选中“升序”，单击“确定”按钮；
步骤3：选中“19年离职”工作表中所有数据区域，单击【数据】|【排序和筛选】分组中的“排序”按钮，打开“排序”对话框，在主要关键字中选中“部门”，单击“选项”按钮，打开“排序选项”对话框，选中方法中的“字母排序”，次序中选中“升序”，单击“确定”按钮；
步骤4：单击“添加条件”按钮，在次要关键字中选中“工号”，次序中选中“升序”，单击“确定”按钮。
5．【解析】选中“19年末”工作表。
① 步骤1：选中H列，单击【数据】|【数据工具】分组中的“分列”按钮，打开“文本分列向导”对话框；
步骤2：选中“分隔符号”按钮，单击“下一步”按钮，在“分隔符号”中选中“分号”，单击“下一步”按钮，选中“列数据格式”中选中“日期”按钮，在其后的下拉选择框中选中“YMD”，单击“完成”按钮；
步骤3：选中H列，单击【开始】|【数字】分组中的对话框启动器，打开“设置单元格格式”对话框；
步骤4：在分类中选中“日期”，在“类型”中选中“2012年3月14日”，单击“确定”按钮。
步骤5：适当调整H列列宽，使其中内容完全显示。
② 步骤1：在I2单元格中，首先使用函数计算出出生日期到2019年12月31日的相差年份：DATEDIF(H2,"2019-12-31","Y")，然后使用函数计算出出生日期到2019年12月31日的相差月份：DATEDIF(H2,"2019-12-31","YM")，然后将两个函数结合并加上题目要求的格式“××岁××个月”，最后公式写成：=DATEDIF(H2,"2019-12-31","Y")&"岁"&DATEDIF(H2,"2019-12-31","YM")& "个月"。
步骤2：双击I2智能填充柄，完成其他行计算。
③ 步骤1：在G2单元格中，首先使用LEN函数计算F2单元格内容的长度：LEN(F2)，然后用IF函数根据内容长度判断是座机还是手机：=IF(LEN(F2)=11,"手机","座机")；
步骤2：双击G2智能填充柄，完成其他行计算。
④ 步骤1：选中F2:F51单元格区域，单击【开始】|【数字】分组中的对话框启动器，打开“设置单元格格式”对话框；
步骤2：选中“分类”中的“自定义”没在“类型”中输入“[>1000000000]"***********";[<100000000]"********"”，单击“确定”按钮。
6．【解析】步骤1：选中“19年末”工作表中的数据，单击【插入】|【表格】分组中的“数据透视表”按钮，打开“创建数据透视表”对话框，单击“确定”按钮；
步骤2：将新创建的透视工作表名称改为“员工数据分析”，在最前面插入3个空白行，使得自A6单元格开始创建数据透视表和数据透视图；
步骤3：在“选择要添加到报表的字段”中拖动“学历”到“筛选”中，拖动“部门”到“行”中，拖动“姓名”到“值”中，再次拖动“姓名”到“值”中。
① 步骤1：在“姓名2”上单击鼠标右键，选中“值显示方式”级联菜单中的“总计的百分比”；
步骤2：选中“姓名2”列数据区域，单击鼠标右键，然后单击“数字格式”命令，打开“设置单元格格式”对话框，选中分类中的“百分比”，设置小数位数为“2”，单击“确定”按钮。
② 步骤1：双击A6单元格将内容修改为“部门”；
步骤2：双击B6单元格，打开“值字段设置”对话框，将自定义名称改为“人数”，单击“确定”按钮；
步骤3：双击C6单元格，打开“值字段设置”对话框，将自定义名称改为“占比”，单击“确定”按钮。
步骤4：单击“部门”右侧的下拉按钮，在弹出的列表中单击“其他排序选项”按钮，打开“排序(部门)”对话框，然后单击“其他选项”按钮，打开“其他排序选项(部门)”对话框，取消“每次更新报表时自动排序”复选框，选中“方法”中的“字母排序”按钮，单击“确定”按钮；
③ 步骤1：选中A6:C15单元格区域，单击【数据透视表工具】|【分析】|【工具】分组中的“数据透视图”按钮，打开“插入图表”对话框，选中“簇状柱形图”，单击“确定”按钮；
步骤2：选中图表中的一个数据快，单击【数据透视表工具】|【设计】|【类型】分组中的“更改图表类型”按钮，打开“更改图表类型”对话框，设置“占比”的图表类型为“带数据标记的折线图”，并选中其后的“次坐标轴”，单击“确定”按钮；
步骤3：单击【数据透视表工具】|【设计】|【图表布局】分组中的“添加图表元素”下拉按钮，在列表中单击“图例”级联菜单中的“底部”命令；
步骤4：拖放图表到D6:L16单元格区域，取消【数据透视表工具】|【分析】|【显示/隐藏】分组中的“字段按钮”选中状态；
步骤5：单击【数据透视表工具】|【设计】|【图表布局】分组中的“添加图表元素”下拉按钮，在列表中取消“网格线”级联菜单中的选中选项；
步骤6：选中左侧纵坐标，单击【数据透视表工具】|【格式】|【当前所选内容】分组中的“设置所选内容格式”按钮，打开“设置坐标轴格式”窗口，设置边界中的最小值为“0”、最大值为“10”、单位中的大为“2”；
步骤8：选中右侧纵坐标，在“设置坐标轴格式”窗口，设置边界中的最小值为“0”、最大值为“0.2”、单位中的大为“0.05”，设置“数字”分类中的小数位数为“0”。
④ 步骤1：单击【文件】中的“选项”命令；打开“Excel选项”对话框；在左侧选中“高级”，在右侧单击“编辑自定义列表”按钮，打开“自定义序列”对话框，在“输入序列”中输入“博士、硕士、本科、大专和中专”，单击“添加”按钮，单击“确定”按钮，再单击“确定”按钮；
步骤2：将光标定位于@6:C16单元格区域内，单击【数据透视表工具】|【分析】|【筛选】分组中的“插入切片器”按钮，打开“插入切片器”对话框，选中“学历”，然后单击“确定”按钮；
步骤3：在【切片器工具】|【选项】|【按钮】分组中，设置“列”为“5”，拖放切片器到A1:E5单元格区域；
步骤4：在“切片器样式”中选中“浅蓝，切片器样式深色5”；
步骤5：在切片器标题上单击鼠标右键，然后单击弹出菜单中的“降序”，再次在切片器标题上单击鼠标右键，然后单击弹出菜单中的“升序”。
⑤ 步骤1：选中将A1:L17单元格区域，单击【页面布局】|【页面设置】分组中的“打印区域”下拉按钮，在列表中单击“设置打印区域”命令；
步骤2：单击【页面布局】|【页面设置】分组中的“纸张方向”下拉按钮，在列表中单击“横向”命令；
步骤3：单击【页面布局】|【页面设置】分组中的对话框启动器，打开“页面设置”对话框，在“缩放”中选中“调整为”，设置为“1”页宽和“1”页高，单击“页边距”选项卡，在居中方式中选中“水平”和“垂直”复选框，单击“确定”按钮。
7．【解析】步骤1：选中“19年末”工作表中的“F2:F51”单元格区域，单击【开始】|【数字】分组中的对话框启动器，打开“设置单元格格式”对话框，单击“保护”选项卡，选中“隐藏”复选框，单击“确定”按钮；
步骤2：单击【审阅】|【保护】分组中的“保护工作表”按钮，打开“保护工作表”对话框，选中“选定锁定单元格”和“选定解除锁定的单元格”复选框，单击“确定”按钮。
保存并关闭Excel.xslx工作簿。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
