2. 计算机
  3. 人事部专员小金负责本公司员工档案的日常管理，以及员工每年各项基本社会保险费用的计算。按照下列要求帮助小金完成相关数据的整理、计算、统计和分析工作： 在工作表“身份证校对”中按照下列规则及要求对员工的身份证号进行正误校对： ①中国公民的身份证号由1

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

admin2019-10-09  56
问题     人事部专员小金负责本公司员工档案的日常管理，以及员工每年各项基本社会保险费用的计算。按照下列要求帮助小金完成相关数据的整理、计算、统计和分析工作：
在工作表“身份证校对”中按照下列规则及要求对员工的身份证号进行正误校对：
    ①中国公民的身份证号由18位组成，最后一位即第18位为校验码，通过前17位计算得出。第18位校验码的计算方法是：
    将身份证的前17位数分别与对应系数相乘，将乘积之和除以11，所得余数与最后一位校验码一一对应。从第1位到第17位的对应系数以及余数与校验码对应关系参见工作表“校对参数”中所列。
    ②首先在工作表“身份证校对”中将身份证号的18位数字自左向右分拆到对应列。
    ③通过前17位数字以及工作表“校对参数”中的校对系数计算出校验码，填入V列中。
    ④将原身份证号的第18位与计算出的校验码进行对比，比对结果填入W列，要求比对相符时输入文本“正确”，不符时输入“错误”。
    ⑤如果校对结果错误，则通过设置条件格式将错误身份证号所在的数据行以“红色”文字、浅绿类型的颜色填充。
选项
答案②步骤1：将光标定位于D3单元格中，单击“插入函数”按钮，打开“插入函数”对话框，在选择函数中找到“MID”，单击“确定”按钮，打开“函数参数”对话框； 步骤2：第一个参数是文本参数，表示准备从中提取字符串的文本字符串，这里填“$C3”(为了后面使用智能填充，需要将列标签C加上绝对引用)；第二个参数中输入要开始截取的字符的起始位置，这里填“1”；第三个参数中输入要截取的位数，这里填“1”，单击“确定”按钮，公式写成“=MID($C3，1，1)”； 步骤3：将第二个参数“1”用函数来代替，这里使用“SUBSTITUTE”函数来计算。 在第1个参数中输入“D$2”，从D2单元格中截取位数，采用绝对引用行，是为了方便后面使用智能填充柄；在第2个参数中输入“”第””，指定要替换掉的字符；在第3个参数中输入“”””，指定要替换为的字符为空；在第4个参数默认不填。这样就提取到了字符串“1位”。 再次使用该函数将“位”替换掉，就可以获取截取位数数了。最后公式写成“SUBSTITUTE(SUBSTITUTE(D$2，"第"，"")，”位"，"")”。所以D3单元格最后的公司为“=MID($C3,SUBSTITUTE(SUBSTITUTE(D$2，"第"，"")，"位"，"")，1)”。 步骤4：往右拖动D3单元格智能填充柄至U3单元格，完成其他位填充。 【知识拓展】SUBSTITUTE函数 功能：用于在某一文本字符串中替换指定文本。 语法：SUBSTITUTE(text,old_text，new_text，[instance_num]) 说明：参数(Text)为需要替换其中字符的文本，或对含有文本的单元格的引用；参数(Old_text)为需要替换的旧文本；参数(New_text)用于替换old_text的文本；参数(Instance_num)为一数值，用来指定以new_text替换第几次出现的old_text。如果指定了instance_num，则只有满足要求的old_text被替换；如果缺省则将用new_text替换Text中出现的所有old_text。 小提示：第二个参数也可以不用函数来计算，直接往后拖动D3单元格智能填充柄，直到U3单元格。根据每位对应的位数，修改函数中的第2个参数(例如E3单元格中的第二个参数应改成2)，这样操作速度就慢些： 步骤5：选中D3至U3单元格区域，双击智能填充柄，即可完成其他行的填充。 ③步骤1：本题首先根据算法规则求出“身份证的前17位数分别与对应系数相乘，将乘积之和…”，这里我们可以采用“SUMPRODUCT”函数，公式为：SUMPRODUCT(D3：T3，校对参数!$E$5：$U$5)； 步骤2：由于要求校验参数数组中的元素必须是数值型，因此需要将数组元素转换为数值型，这里采用“VALUE”函数，公式为：SUMPRODUCT(VALUE(D3：T3)，VALUE(校对参数!$E$5：$U$5)) 步骤3：将前两步所求结果除以11所得余数与最后一位校验码一一对应。这就需要使用到两个函数，我们求余采用“MOD”函数，公式为：MOD(SUMPRODUCT(VALUE(D3：T3),VALUE(校对参数!$E$5：$U$5))，11)。 步骤4：当求出余数后，要通过对比找出校验码，这里采用VLOOKUP函数。VLOOKUP是一个查找函数，给定一个查找的目标，它就能从指定的查找区域中查找返回想要查找到的值。本题中“VLOOKUP(MOD(SUMPRODUCT(VALUE(D3：T3)，VALUE(校对参数!$E$5：$U$5))，11)，校对参数!$B$4：$C$15，2，0)”的含义如下： 参数1一查找目标：余数为查找目标，“MOD(SUMPRODUCT(VALUE(D3：T3),VALUE(校对参数!$E$5：$U$5))，11)”。将在参数2指定区域的第1列中查找与余数相同的单元格。 参数2一查找范围：“校对参数!$B$4：$C$15”表示第2个工作表中数据表的全部区域(即B4：C15区域)。注意：查找目标一定要在该区域的第一列。 参数3一返回值的列数：“2”表示参数2中工作表的第2列。如果在参数2中找到与参数1相同的单元格，则返回第2列的内容。 参数4一精确OR模糊查找：“0”。最后一个参数是决定函数精确和模糊查找的关键。精确即完全一样，模糊即包含的意思。 注：参数引用绝对值是考虑到后面要智能填充。 知识拓展：(1)SUMPRODUCT函数 功能：返回相应的数组或区域乘积的和。 语法：SUMPRODUCT(array1,array2，array3，…)说明：Array1,array2,array3…．为2到30个数组，其相应元素需要进行相乘并求和。数组参数必须具有相同的维数，否则，函数SUMPRODUCT将返回错误值#VALUE！。函数SUMPRODUCT将非数值型的数组元素作为0处理(数组内必须是数值型数据)。 (2)VALUE函数 功能：将代表数字的文本字符串转换成数字。 语法： VALUE(text） 说明：Text可以是Microsoft Excel中可识别的任意常数、日期或时间格式。如采Text不为这些格式，则函数VALUE返回错误#VALUE！。 ④步骤1：比较两个单元格内容是否相等，可以使用“EXACT”函数，公式为：EXACT(U3，V3)； 步骤2：题目要求两个单元格内容符合则显示文本“正确”，不符合则显示文本“错误”。因此可以使用IF判断函数来实现，公式为：=IF(EXACT(U3，V3)，"正确"，"错误")。 知识拓展：(1)EXACT函数 功能：用于检测两个字符串是否完全相同。 语法：EXACT(text1,text2) 说明：函数不区分文本型和数值型数字。EXACT函数的参数text1和text2分别表示需要比较的文本字符串，也可以是引用单元格中的文本字符串，如果两个参数完全相同，EXACT函数返回TRUE值；否则返回FALSE值。 ⑤步骤1：选中W3至W122单元格区域，单击“开始”｜“样式”分组中的“条件格式”下拉按钮，在下拉列表中选中“突出显示单元格规则”级联菜单中的“等于”命令，打开“等于”对话框； 步骤2：在条件中输入“错误”，在设置为中单击“自定义格式”命令，打开“设置单元格格式”对话框： 步骤3：在字体选项卡中设置字体颜色为标准色中的“红色”，在填充选项卡中设置背景色为“浅绿”，单击“确定”按钮，再次单击“确定”按钮。
解析
转载请注明原文地址:https://kaotiyun.com/show/TL6D777K
本试题收录于： 二级MS Office高级应用题库NCRE全国计算机二级分类
0

二级MS Office高级应用

NCRE全国计算机二级

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