1 / 11
文档名称:

EXCEL提取身份证信息以及鉴别身份证真伪的技术文件.doc

格式:doc   页数:11页
下载后只包含 1 个 DOC 格式的文档,没有任何的图纸或源代码,查看文件列表

如果您已付费下载过本站文档,您可以点这里二次下载

分享

预览

EXCEL提取身份证信息以及鉴别身份证真伪的技术文件.doc

上传人:xxj16588 2016/6/1 文件大小:0 KB

下载得到文件列表

EXCEL提取身份证信息以及鉴别身份证真伪的技术文件.doc

文档介绍

文档介绍:EXCEL 中如何提取身份证出生日期、性别、检验身份证号码的正确性中国居民身份证号码是一组特征组合码,原为15位,现升级为18位,其编码规则为: 15位:6位数字常住户口所在县市的行政区划代码,6位数字出生日期代码,3位数字顺序码。 18位:6位数字常住户口所在县市的行政区划代码,8位数字出生日期代码,3位数字顺序码和 1位检验码。其中3位数字顺序码,是为同一地址码的同年同月同日出生人员编制的顺序号,偶数的为女性,奇数的为男性。 1、提取籍贯地区的行政区划代码( A2 为身份证号,下同) 15与18位通用: =LEFT(A2,6) 如果有一个编码和省份地区的对照表,可以用 VLOOKUP 函数来提取地区信息。 2、提取出生日期信息 15位: =--TEXT(19&MID(A2,7,6),"#-00-00") 18位: =--TEXT(MID(A2,7,8),"#-00-00") 15与18位通用: =--TEXT(IF(LEN(A2)=15,19,"")&MID(A2,7,6+IF(LEN(A2)=18,2,0)),"#-00-00") 简化公式: =--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00") ( 请将输入公式的单元格格式设置为日期格式) 3、提取性别信息 15位: =IF(MOD(RIGHT(A2),2)=1," 男"," 女") 18位: =IF(MOD(MID(A2),17,1)=1," 男"," 女") 15与18位通用: =IF(MOD(MID(A2,IF(LEN(A2)=15,15,17),1),2)=1," 男"," 女") 简化公式: =IF(MOD(RIGHT(LEFT(A2,17)),2)," 男"," 女") 4、检验身份证号码的正确性 18位身份证号码的最后一位是检验码, 它是根据身份证前17 位数字依照规则计算出来的, 其值0~ 9或 X 。一般情况只要有一位数字输入错误,依照规则计算后就会与第 18 位数不符。当然不排除按错误号码计算后恰好与检验码相符的情况,但这种情况出现的可能性较低。因此,对18位号码的验证采用如下公式: =MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17 ")))),11)+1,1)=RIGHT(A2,1) 对于15位身份证, 由于没有检验码, 我们只能简单地去判断出生日期代码是否是一个有效的日期, 避免输入一些像“ 731302 ”或“ 980230 “等这样不存在的日期。=ISNUMBER(--TEXT(19&MID(A2,7,6),"#-00-00")) 综合15位和18位后的通用公式为: =IF(LEN(A2)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW( INDIRECT("1:17")))),11)+1,1)=RIGHT(A2),IF(LEN(A2)=15,ISNUMBER(--TEXT(19&MID(A2,7,6),"#-00- 00")))) 由于目前15位身份证号码已经很少了,如果对15位的号码不需要作进一步的判断,则公式可以简化成: =IF(LEN(A2)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW( INDIRECT("1:17")))),11)+1,1)=RIGHT(A2),LEN(A2)=15) 将上面的公式放到 B2 单元格,如果结果为 TRUE ,则身份证号是正确的,结果为 FALSE 则是错误的。你也可以将上述公式放在数据有效性中, 防止录入错误的身份证号。操作方法: 选择需要输入身份证的全部单元格区域, 比如 A2:A10, 点菜单" 数据"-" 有效性",在" 允许" 的下拉框中选择" 自定义",在" 公式" 输入上面的 15 位和 18 位通用公式, 确定以后即可。注意: 公式里的"A2" 是你刚才选定要输入身份证的单元格区域的第一个单元格, 如果你是要在 C3:C20 输入身份证号, 则将公式里的"A2" 改为"C3" 。另外, 你也可以先设置好某单个单元格的数据有效性( 这时公式的 A2 改为选定的单元格), 再用格式刷将其格式刷到其他需要相同设置的单元格。 5、15位升为18位=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUMPRODUCT(MI

最近更新

2025年度节能减排工程建设监理合同 9页

2025年开车抽烟怎么处罚会扣分吗 3页

2025年度船舶保险合同标准文本 8页

2025年开放大学(电大)《现代金融业务》期末试.. 25页

2025年开心的春节小学生作文 8页

2025年度航空航天研发合作购买厂房及实验室合.. 9页

2025年度航空航天技术装备购销印花税合同标准.. 9页

2025年开展爱国卫生运动活动方案5篇 11页

2025年度航空器维护合伙协议合同 9页

2025年度能源领域技术转移合同模板汇编 9页

2025年度能源行业职工职业病防治劳动保险协议.. 8页

2025年开展工会文体活动方案 6页

2025年甲状腺作用与影响解析 19页

环保节能门窗工程承包合同 7页

2025年度股权代持及退出机制服务协议 7页

头孢沙定对革兰阳性细菌的作用研究-全面剖析 29页

2025年开学自我介绍万能 14页

2025年度美容美发店铺员工劳动合同 7页

生物反馈在康复训练中的作用-全面剖析 28页

2025年度网络电影制作场记聘用合同 8页

2025年大学中国新能源电动汽车消费者调研报告.. 24页

艺术舞蹈老师简历模板 1页

煤炭资源地质勘查设计编写提纲 14页

硫酸铵生产硫酸钾的可行性方案 31页

2022年首都经济贸易大学工商管理专业《管理学.. 22页

学生5mm坐标纸(虚线 word版)直接打印 2页

(完整版)天津大学非线性信息处理技术 12页

中式烹调工艺 2页

燃烧器控制器LFL说明 10页

通信导航及气象工程监理细则 15页