欢迎光临
我们一直在努力

Excel数据有效性应用-身份证号长度验证 身份证长度

身份证号长度验证

设置身份证号长度的验证,你觉得要怎么下手比较好?
有经验的人会发现我们的身份证号有两个特点:
1、身份证位数(是否为15位或18位)

  • 编码:ABCDEFYYYYMMDDXXXR
  • 地址码(ABCDEF):表示编码对象常住户口所在县(市、旗、区)的行政区划代码。
  • 出生日期码(YYYYMMDD)
  • 顺序码(XXX):顺序码的奇数分配给男性,偶数分配给女性。
  • 4.校验码(R):一位数字。
  • 15位身份证号码:第7、8位为出生年份(两位数),第9、10位为出生月份,第11、12位代表出生日期,第15位代表性别,奇数为男,偶数为女。

2、日期是否合法(主要是判断月份是否在1-12之间,日期是否超出当月的天数等)
所以我们的函数就是要解决这两个问题,判断的函数比较多,比较长。在公式输入框中输入=AND(OR(LEN(A1)=15,LEN(A1)=18),1*TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"))

搭档函数:TEXT文本格式函数,MID中间截取函数,LEN文本长度函数,OR或函数,AND与函数。
这一串这么长的函数嵌套什么意思呢?我们来分解一下:
首先,我们来解决身份证位数问题,判断是否是15位还是18位。
在这里我们用OR(LEN(A1)=15,LEN(A1)=18),意思是要输入的数字必须满足15位或18位都可以,这个没问题吧?
其次,我们知道身份证号里包含了我们的出生日期,这个格式是否合理。在这里我们就要把身份证号中的出生日期提取出来,判断它的格式是否满足日期格式。由于身份证号有15位和18位,如果是15位的话,年份里是没有19的,所以我们要考虑到这一点。
我们用1*TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")来表示。

  1.  若LEN(A1)=15为真,即身份证号为15位,则(LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2)的运算=1*19&MID(A1,7,6+0*2)=1*19&MID(A1,7,6),MID(A1,7,6)表示从身份证号的第7位开始,截取6位,这6位的位置刚好是出生年月日的位置,由于15位的身份证号是没有19的,所以前面用&连接19。
  2. 若LEN(A1)=15为假,即身份证号为18位,则(LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2)的运算=0*19&MID(A1,7,6+1*2)=0*19&MID(A1,7,8),MID(A1,7,8)表示从身份证号的第7位开始,截取8位,这8位的位置刚好是出生年月日的位置。
  3. 最后使用TEXT函数把这8位数转换成日期格式 ,再用1*TEXT()强制转换成数值,这样才能判断真假,非0的数值都是返回TRUE。

最后,我们要以上两个条件同时满足才算数据是符合要求的,所以用AND连接起来。

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
赞(0) 打赏
分享到: 更多 (0)

轻博客

联系我们联系我们

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏