如何用Excel验证身份证尾数
身份证是唯一的,尾数校验,性别校验,出生日期提取等,是日常办公和学习中的基本内容,也是计算机,信息系人员的基本素养。
如何巧妙使用Excel函数自动识别尾数校验码,自动识别性别以及自动提取出生日期,请看小编的详细。
操作方法
- 01
第一步:设计表格 使用已安装Office或WPS的计算机,打开Excel,设计一张表格,设计表头。 尾数校验是使用身份证前17位去校验唯一的校验码,所以表头必须有输入的前17位身份证,以及相关校验数据行,如图:
- 02
第二步:输入身份证系数和公式 身份证码位对应的系数,可以在公安系统以及相关网站查询,每一位对应的系数是不变的,输入前17位的系数和值计算公式,填充到Excel第二列,如图: (公式中Left以及mid是提取身份证码位的函数)
- 03
第三步:输入身份证余数公式 根据系数和值,计算余数值,就是用系数和值与11求余,填充到Excel第三列,如图:(MOD是求余函数)
- 04
第四步:输入尾数校验函数 用ExcelIf函数,即可提取余数值,校验尾数校验码,尾数校验码与余数值唯一对应,填充到Excel第四列,如图:
- 05
第五步:填充完整身份证号 计算出唯一尾数校验码后,将前17位身份证号码和尾数校验码,填充到完成身份证号码Excel第五列,如图:(函数=B4&E4)
- 06
第六步:提取出生日期 从完整身份证列,使用mid函数,提取出生年月日并分别填充到Excel第六列,格式自定,如图:(mid函数提取日期)
- 07
第七步:自动识别性别 性别由身份证倒数第二位(第十七位)决定,奇数为男,偶数为女;使用Excel求余公式,计算第十七位与2的余数,即可识别并自动填充到第七列,如图:(Mod,求余函数)
- 08
第八步:验证 在第一列输入身份证前17位,跳出后面的相关参数,查看是否符合逻辑。 (注:验证要使用已知的身份证号,抛去最后一位,输入验证),验证测试是人生一项程序运行之前的必须工作。如图:
- 09
第九步:插入序号列 在第一列之前插入一列,命名为序号,并自动填充序号。在第一行输入1,然后按身份证前17位非空列进行自动增加,如图:
- 10
第十步:锁定单元格 锁定不需要输入的单元格方法: 1、全选——右键——设置单元格格式——保护,然后取消锁定; 2、选中非输入的单元格——右键——设置单元格格式——保护,勾选保护; 3、隐藏:全选——设置单元格格式——保护——隐藏。如图:
- 11
第十一步:保护工作表 保护工作表之后,锁定的单元格将不能操作。 审阅——保护工作表——勾选第一二项,加上密码即可锁定并隐藏单元格内的公式与函数。 保护表格是为了保护表格的结构不被更改,以及锁定函数。如图: