1个公式校验身份证号码的正确性

在工作中涉及身份证号码正确性的校验时,很多人苦苦求索但找不到方法。

其实Excel强大的计算和统计功能可以帮我们自动校验,免除手工排查数据的麻烦。

今天我来结合一个实际案例,介绍1个公式校验身份证号码的正确性的方法。

注:现在的身份证号码都是18位的,由17位数字和末位校验码构成。原来的15位编码方式已经停用,而且15位没有使用校验。最后一位可能出现的X并不是英文字母X,而是希腊数字10的缩写X

B2单元格输入以下数组公式,按<Ctrl+Shift+Enter>组合键结束输入,并向下填充。

=IF(A2="","",IF(AND(LEN(A2)=18,COUNTIF(行政区划代码!A:A,LEFT(A2,6))>0,ISNUMBER(--TEXT(MID(A2,7,8),"0000-00-00")),--TEXT(MID(A2,7,8),"0000-00-00")>1,--TEXT(MID(A2,7,8),"0000-00-00")<TODAY(),RIGHT(A2)=MID("10X98765432",MOD(SUM(MID(MID(A2,1,17),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)),"正确","错误"))

此公式的校验规则包括以下四点。

1、身份证号码前6位数字可以在行政区划代码中找到,工作表‘行政区划代码’中A列放置的是中华人民共和国国家统计局发布的6位行政区划代码。

http://www.stats.gov.cn/tjsj/tjbz/xzqhdm/

2、身份证号码中从第7位开始的8位数字是可以识别的日期格式,日期范围介于1900年1月1日至当前日期之间,且不会出现2月30日或4月31日这类错误日期。

3、身份证号码长度为18位。

4、身份证号码第18位是根据前面十七位数字码,按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。具体计算规则如下。

1)将前面的身份证号码17位数分别乘以不同的系数。从第一位到第十七位的系数分别为:7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2 ;

2)将这17位数字和系数相乘的结果相加;

3)用加出来和除以11,看余数是多少;

4)余数只可能有0 1 2 3 4 5 6 7 8 9 10这11个数字。其分别对应的最后一位身份证的号码为1 0 X 9 8 7 6 5 4 3 2;

5)通过上面得知如果余数是2,就会在身份证的第18位数字上出现罗马数字的X。如果余数是10,身份证的最后一位号码就是2。

(0)

相关推荐

  • 通过Excel可以提取身份证号码哪些信息?看完才知道有多重要

    前面说了关于身份证号码录入的各种问题,今天说说可以从身份证里面提取哪些信息,包括出生地所在省市区.生日.年龄.性别.生肖.星座等等.看看身份证号码包含了多少信息,所以平时要注意保护好我们的身份证哦. ...

  • 检查身份证号码的正确性

    // 项目名称:检查身份证号码的正确性// 开发平台:/*操作系统:Windows 10 X64开发环境:Visual Studio 2019解决方案配置:Debug解决方案平台:X86*/// 作者 ...

  • 1个Excel公式检查身份证号码是否重复

    点击下方 ↓ 关注,每天免费看Excel专业教程 置顶公众号或设为星标 ↑ 才能每天及时收到推送 个人微信号 | (ID:LiRuiExcel520) 微信服务号 | 跟李锐学Excel(ID:LiR ...

  • 1个公式根据身份证号码计算退休日期

    原创作者 | 李锐 微信公众号 | Excel函数与公式(ID:ExcelLiRui) 微信个人号 | (ID:ExcelLiRui520) 1个公式根据身份证号码计算退休日期 身份证号码里包含很多信 ...

  • 1个公式检查身份证号码是否重复

    原创作者 | 李锐 微信公众号 | Excel函数与公式(ID:ExcelLiRui) 微信个人号 | (ID:ExcelLiRui520) 1个公式检查身份证号码是否重复 在工作中涉及身份证号码的表 ...

  • 1个公式根据身份证号码实现生日提醒

    跟李锐学Excel, 高效工作,快乐生活. 1个公式根据身份证号码实现生日提醒 身份证号码里包含很多信息,比如年龄.性别.生日等. 今天我来结合一个实际案例,介绍1个公式从身份证号码中提取生日并提前7 ...

  • 1个公式从身份证号码提取生日

    跟李锐学Excel, 高效工作,快乐生活. 1个公式从身份证号码提取生日 身份证号码里包含很多信息,比如年龄.性别.生日等. 今天我来结合一个实际案例,介绍1个公式从身份证号码提取出生日期. 方法1: ...

  • 转Excel身份证号码计算年龄公式

    在前面的几篇Excel身份证号码的文章中,我已经讲解了如何利用Excel身份证号码来获取出生年月日以及判断男女性别等函数公式,今天,再继续我们的Excel身份证号码的专题.讲解一下利用身份证号码来计算 ...

  • 从身份证号码提取归属地等相关信息,你还在纠结用什么公式?真的out了!

    关于Excel内将身份证号码提取归属地.生日.性别等,有以下两个方法: 一.公式法 获取全国身份证归属地信息表,然后用公式直接读取,方法比较简单,如下所示: 1.身份证归属地获取 可以在网上搜索,其实 ...

  • Excel技巧连载35和36:身份证号码提取生肖的两种公式写法

    一个身份证号码包含了我们的出生日期.性别.籍贯等信息. 昨天有小伙伴在微信群私聊雅雯,询问同样的一个公式,为何有些单元格提取生肖是错误的,可以先从数据源开始检查是否是数据源格式.录入等等有错误. 通过 ...