2. 计算机
  3. 人事部专员小金负责本公司员工档案的口常管理，以及员工每年各项基本社会保险费用的计算。按照下列要求帮助小金完成相关数据的整理、计算、统计和分析工作： (1)将考生文件夹下的工作簿文档“．Excel素材文件．xlsx”另存为“Excel．xlsx”(“．xls

人事部专员小金负责本公司员工档案的口常管理，以及员工每年各项基本社会保险费用的计算。按照下列要求帮助小金完成相关数据的整理、计算、统计和分析工作： (1)将考生文件夹下的工作簿文档“．Excel素材文件．xlsx”另存为“Excel．xlsx”(“．xls

admin2019-08-11  28
问题 人事部专员小金负责本公司员工档案的口常管理，以及员工每年各项基本社会保险费用的计算。按照下列要求帮助小金完成相关数据的整理、计算、统计和分析工作：
(1)将考生文件夹下的工作簿文档“．Excel素材文件．xlsx”另存为“Excel．xlsx”(“．xlsx”为文件扩展名)，之后所有的操作均基于此文件，否则不得分。操作过程中，不可以随意改变原工作表素材数据的顺序。
(2)在工作表“身份证校对”中按照下列规则及要求对员工的身份证号进行正误校对：
①中国公民的身份证号由18位组成，最后一位即第18位为校验码，通过前17位计算得出。第18位校验码的计算方法是：将身份证的前17位数分别与对应系数相乘，将乘积之和除以11，所得余数与最后一位校验码一一对应。从第1位到第17位的对应系数以及余数与校验码的对应关系参见工作表“校对参数”中所列。
②首先在工作表“身份证校对”中将身份号的18位数字自左向右拆分到对应列。
③通过前17位数字以及工作表“校对参数”中的校对系数计算出校验码，填入V列中。
④将原证号的第18位与计算出的校验码进行对比，比对结果填入W列，要求比对相符时输入文本“正确”，不相符时输入“错误”。
⑤如果校对结果错误，则通过设置条件格式将错误身份证号所在的数据行以“红色”文字、“浅绿”类型的填充颜色显示。
(3)在工作表“员工档案”中，按照下列要求对员工档案数据表进行完善：
①输入每位员工的身份证号，员工编码与身份证号的对应关系见工作表“身份证校对”。如果已校对出错误，应将正确的身份证号填写入工作表“员工档案”中(假设所有错误号码都是由于最后一位校验码输错导致的)。
②计算每位员工截至2016年12月31日的年龄，每满一年才计算一岁，一年按365天计算。
③在“工作状态”列的空白单元格中填入文本“在职”。
④计算每位员工在本公司工作的工龄，要求不足半年按半年计算，超过半年按一年计算，一年按365天计算，保留一位小数。其中，“在职”员工的工龄计算截止于2016年12月31日，离职和退休人员工龄的计算截止于各自离职或退休的时间。
⑤计算每位员工的工龄工资，公式：工龄工资=本公司工龄×50。
⑥计算员工的工资总额，公式：工资总额=工龄工资+签约工资+上年月均奖金。
(4)在工作表“社保计算”中，按照下列要求计算每个员工本年度每月应缴社保金额：
①依据工作表“员工档案”中的数据，筛选出所有“在职”员工的“员工编号”“姓名”和“工资总额”三列数据，依次填入B、C、D列中，并按员工编号由小到大排序。
②本市上年职工平均月工资为7086元，首先将其定义为常量“人均月工资”，然后依据下列规则计算出每位员工的“社保基数”填入相应E列中，计算时需要在公式中调用新定义的常量“人均月工资”。社保基数最低为人均月工资7086元的60％，最高为人均月工资7086元的3倍。

③每个人每个险种的应缴社保费=个人的社保基数×相应的险种费率，按照工作表“社保费率”中所列险种费率分别计算每位在职员工应缴的各险种费用，包括公司负担部分和个人负担部分。其中：医疗个人负担=社保基数×医疗个人负担比例+个人额外费用3元。
④为数据表设置恰当的数字格式，套用一个表格格式并取消自动筛选标记。
(5)以工作表“社保计算”的结果为数据源，参照考生文件夹下“图1．png”示例，自新工作表“透视分析”的A3单元格开始生成数据透视表，要求如下：
①列标题应与示例图相同。
②按图中所示调整工资总额的数字格式。
③改变数据透视表样式。
选项
答案(1)操作步骤为： 【步骤1】双击考生文件下的“Excel素材文件．xlsx”将其打开。 【步骤2】单击“文件”选项卡下的“另存为”命令，弹出“另存为”对话框，在文件名中输入“Excel．xlsx”，保存路径设置为考生文件夹，单击“保存”按钮。 (2)操作步骤为： 【步骤1】在“身份证校对”工作表中选中D3单元格并输入“=MID($C3，1，1)”，往后拖动D3单元格的智能填充柄，直到U3单元格。根据每位对应的位数，修改函数中的第2个参数；选中D3至U3单元格区域，双击智能填充柄，即可完成其他行的填充。 【步骤2】在“身份证校对”工作表中选中V3单元格并输入“=VLOOKUP(MOD(SUMPRODUCT(VALUE(D3：T3)，VALUE(校对参数!$E$5：$U$5))，11)，校对参数!$B$5：$C$15，2，0)”，按“Enter”回车键；双击智能填充柄，即可完成其他单元格的填充。 【步骤3】在“身份证校对”工作表中选中W3单元格并输入“=IF(EXACT(U3，V3)，“正确”，“错误”)”，按“Enter”回车键；双击智能填充柄，即可完成其他单元格的填充。 【步骤4】选中W3至W122单元格区域，单击“开始”选项卡下“样式”分组中的“条件格式”下拉按钮。在下拉列表中选中“突出显示单元格规则|等于”命令，打开“等于”对话框；在“为等于以下值的单元格设置格式：”下方的文本框中输入“错误”，在“设置为”右侧的文本框中单击下拉按钮，在下拉列表中选择“自定义格式”命令，打开“设置单元格格式”对话框；在“字体”选项卡中设置字体颜色为标准色中的“红色”，在“填充”选项卡中设置背景色为“浅绿”，单击“确定”按钮，再次单击“确定”按钮。 (3)操作步骤为： 【步骤1】在“员工档案”工作表中选中C列，单击“开始”选项卡下“数字”分组中的格式下拉按钮，选中其中的“常规”，单击“确定”按钮。 【步骤2】选中C3单元格并输入“=IF(VLOOKUP(A3，身份证校对!$B$3：$W$122，22，0)="正确"，VLOOKUP(A3，身份证校对!$B$3：$W$122，2，0)，MID(VLOOKUP(A3，身份证校对!$B$3：$W$122，2，0)，1，17)&VLOOKUP(A3，身份证校对!$B$3：$W$122，21，0))”，按“Enter”回车键；双击智能填充柄，即可完成其他单元格的填充。 【步骤3】选中F3单元格并输入“=INT(DATEDIF(E3，”2016-12-31”，"D")／365)”，按“Enter”回车键；双击智能填充柄，即可完成其他单元格的填充。 【步骤4】选中“工作状态”列L3到L122单元格，在“开始”选项卡下“编辑”分组中单击“查找和选择”下拉按钮，在下拉列表中选择“定位条件”命令，打开“定位条件”对话框，选择“空值”，单击“确定”按钮，输入“在职”，按下“Ctrl+Enter”组合键填充空白单元格。 【步骤5】选中M3单元格并输入“=CEILING(DATEDIF(J3，IF(1(3＜ ＞""，K3，"2016—12—31")，"D")／365，0．5)”，按“Enter”回车键；双击智能填充柄，即可完成其他单元格的填充。 【步骤6】选中M3：M122单元格区域，通过单击“开始”选项卡下“数字”分组中的“增加小数位数”和“减少小数位数”按钮将小数位调整为“1位”。 【步骤7】选中03单元格并输入“=M3*50”，按“Enter”回车键；双击智能填充柄，即可完成其他单元格的填充。 【步骤8】选中Q3单元格并输入“=O3+N3+P3”，按“Enter”回车键；双击智能填充柄，即可完成其他单元格的填充。 【步骤9】选中N3：Q122单元格区域，单击“开始”选项卡下“数字”分组中的“千分位”按钮，通过单击“增加小数位数”和“减少小数位数”按钮将小数位调整为“1位”。 (4)操作步骤为： 【步骤1】在“员工档案”工作表中单击“工作状态”列的筛选按钮，取消“离职”和“退休”复选框的选中，单击“确定”按钮；复制筛选后的A10：B122单元格数据到“社保计算”工作表的“B4：C103”单元格区域，复制“员工档案”工作表中筛选后的Q10：Q122单元格数据到“社保计算”工作表的“D4：D103”单元格区域；返回“员工档案”工作表单击“工作状态”列的筛选按钮，选中“全选”复选框，单击“确定”按钮。 【步骤2】在“社保计算”工作表中单击“数据”选项卡下“排序和筛选”分组中的“排序”按钮，打开“排序”对话框，在主要关键字中选中“员工编号”，在次序中选中“升序”，单击“确定”按钮。 【步骤3】任意选中一个单元格，然后单击鼠标右键，在弹出的菜单中单击“定义名称”命令，打开“新建名称”对话框，将名称设置为“人均月工资”，将引用位置中的内容设置为“7086”，单击“确定”按钮。 【步骤4】在“社保计算”工作表中选中E4单元格并输入“=IF(D4＜人均月工资*60％，人均月工资*60％，IF(D4＜人均月工资*3，D4，人均月工资*3))”，按“Enter”回车键。 【步骤5】选中F4单元格并输入“=E4*社保费率!$B$4”，按“Enter”回车键。选中G4单元格并输入“=E4*社保费率!$C$4”，按“Enter”回车键。选中H4单元格并输入“=E4*社保费率!$B$5”，按“Enter”回车键。选中I4单元格并输入“=E4*社保费率!$c$5”，按“Enter”回车键。选中J4单元格并输入“=E4*社保费率!$B$6”，按“Enter”回车键。选中K4单元格并输入“=E4*社保费率!$C$6”，按“Enter”回车键。选中L4单元格并输入“=E4*社保费率!$B$7”，按“Enter”回车键。选中M4单元格并输入“=E4*社保费率!$C$7”，按“Enter”回车键。选中N4单元格并输入“=E4*社保费率!$B$8”，按“Enter”回车键。选中O4单元格并输入“=E4*社保费率!$C$8+3”，按“Enter”回车键。 【步骤6】选中F4：O4单元格区域，双击智能填充柄，完成其他单元格数据填充。 【步骤7】选中数据表区域任意一个单元格，然后按“Ctrl+A”组合键，选中所有数据；单击“开始”选项卡下“样式”分组中的“套用表格格式”下拉按钮，在下拉列表中选中一种样式，弹出“套用表格式”对话框，选中“表包含标题”，单击“确定”按钮；单击“数据”选项卡下“排序和筛选”分组中的“筛选”按钮，取消其选中状态。 【步骤8】选中D4：O103单元格区域，单击“开始”选项卡下“数字”分组中的“千分位”按钮，使其数字格式保持一致。 (5)操作步骤为： 【步骤1】选中“社保计算”数据表区域任意一个单元格，然后按“Ctrl+A”组合键。单击“插入”选项卡下“表格”分组中的“数据透视表”下拉列表中的“数据透视表”命令，打开“创建数据透视表”对话框。单击“确定”按钮，此时会创建一个带数据透视表的新工作表。 【步骤2】将新工作表名称重命名为“透视分析”。将“社保基数”字段拖到“行标签”中，在添加的行标签数据上单击鼠标右键，在弹出的快捷菜单中单击“创建组”命令，打开“组合”对话框；设置“起始于”为“4200”、“终止于”为“22200”、“步长”为“3000”，单击“确定”按钮。 【步骤3】拖动“姓名”字段到“数值”中，拖动“工资总额”字段到“数值”中，再次拖动“工资总额”字段到“数值”中。 【步骤4】双击“求和项：工资总额2”标题，打开“值字段设置”对话框，将“自定义名称”设置为“工资总额占比”，单击“值显示方式”选项卡，选中“全部汇总百分比”，单击“确定”按钮；双击“求和项：工资总额”标题，打开“值字段设置”对话框，将“自定义名称”设置为“工资总额(元)”，单击“确定”按钮。 【步骤5】双击“计数项：姓名”标题，打开“值字段设置”对话框，将“自定义名称”设置为“人数”，单击“确定”按钮；双击“行标签”标题，将其设置为“社保基数”。 【步骤6】选中C4：C10单元格区域，单击“开始”选项卡下“数字”分组中的“千分位”按钮。 【步骤7】选中A3：D10单元格区域，单击“设计”选项卡下“数据透视表样式”中的其他下拉按钮，在下拉列表中选中“数据透视表样式中等深浅26”样式。 【步骤8】在“Excel．xlsx”文档中单击“保存”按钮；单击右上方的“关闭”按钮，关闭“Excel．xsx”文档。
解析
转载请注明原文地址:https://kaotiyun.com/show/ebYp777K
本试题收录于： 二级MS Office高级应用题库NCRE全国计算机二级分类
0

二级MS Office高级应用

NCRE全国计算机二级

相关试题推荐
随机试题
最新回复(0)