行政部李强负责本公司员工档案的日常管理,以及员工每年各项基本社会保险费用的计算。按照下列要求帮助李强完成相关数据的整理、计算、统计和分析工作: 在工作表“身份证校对”中按照下列规则及要求对员工的身份证号进行正误校对: ①中国公民的身份证号由18位

admin2021-01-15  30

问题     行政部李强负责本公司员工档案的日常管理,以及员工每年各项基本社会保险费用的计算。按照下列要求帮助李强完成相关数据的整理、计算、统计和分析工作:
在工作表“身份证校对”中按照下列规则及要求对员工的身份证号进行正误校对:
    ①中国公民的身份证号由18位组成,最后一位即第18位为校验码,通过前17位计算得出。第18位校验码的计算方法是:
    将身份证的前17位数分别与对应系数相乘,将乘积之和除以11,所得余数与最后一位校验码一一对应。从第1位到第17位的对应系数以及余数与校验码对应关系参见工作表“校对参数”中所列。
    ②首先在工作表“身份证校对”中将身份号的18位数字自左向右分拆到对应列。
    ③通过前17位数字以及工作表“校对参数”中的校对系数计算出校验码,填入V列中。
    ④将原证号的第18位与计算出的校验码进行对比,比对结果填入W列,要求比对相符时输入文本“正确”,不符时输入“错误”。
    ⑤如果校对结果错误,则通过设置条件格式将错误证号所在的数据行以“红色”文字、浅绿类型的颜色填充。

选项

答案步骤1:在“身份证校对”工作表的D3单元格中输入公式“=MID($C3,COLUMN(D2)-3,1)”,输入完成后按键盘上的Enter键结束输入,向右拖动填充句柄填充到U3单元格,然后双击U3单元格的填充句柄向下自动填充到U122单元格(此处是按行进行填充)。 步骤2:在V3单元格中输入公式“=TEXT(VLOOKUP(MOD(SUMPRODUCT(D3:T3*校对参数!$E$5:$U$5),11),校对参数!$B$5:$C$15,2,0),”@”)”,输入完成后按键盘上的Enter键结束输入,向下拖动填充句柄填充到V122单元格。 步骤3:在’W3单元格中输入公式“=IF(U3=V3,”正确”,”错误”)”,输入完成后按键盘上的Enter键结束输入,向下拖动填充句柄填充W122单元格。 步骤4:选中W3单元格,单击【开始】选项卡下【样式】功能组中的“条件格式”按钮,在下拉列表中选择“新建规则”命令,弹出“新建格式规则”对话框,在“选择规格类型”列表框中选择“使用公式确定要设置格式的单元格”,在下方的编辑框中输入公式“=IF($W3="错误",TRUE,FALSE)”;单击“格式”按钮,弹出“设置单元格格式”对话框,在“字体”选项卡下将“字体颜色”设置为“标准色/红色”,在“填充”选项卡下将“背景颜色”设置为“浅绿色”,如图2.9所示,最后单击“确定”按钮关闭对话框。 [*] 步骤5:继续单击“条件格式”按钮,在下拉列表中选择“管理规则”命令,弹出“条件格式规则管理器”对话框,将“应用于”设置为“=$B$3:$W$122”,如图2.10所示,单击“确定”按钮关闭对话框。 [*]

解析
转载请注明原文地址:https://kaotiyun.com/show/LVip777K
0

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