excel数据有效性校验身份证号长度和生日

　　本站站长擅长用python自动处理excel，请多关注本站。

　　感谢沈俊伟excel-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")来表示。

　　若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。

　　若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位的位置刚好是出生年月日的位置。

　　最后使用TEXT函数把这8位数转换成日期格式 ，再用1*TEXT（）强制转换成数值，这样才能判断真假，非0的数值都是返回TRUE。

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

　　​​​​​​​

　　​​​​​​​

很赞哦！

python编程网提示：转载请注明来源www.python66.com。
有宝贵意见可添加站长微信(底部)，获取技术资料请到公众号(底部)。同行交流请加群