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

最近更新

TP801B在8路运动心率遥测仪中的应用 2页

第七章物流配送(1) 50页

Ti—6Al—4V钛合金亚β锻造的探讨 2页

2025年幼儿园大班活动策划方案创意实用方案 11页

2025年幼儿园大班月份工作总结 6页

第8课《爸爸的花儿落了》同步练习(鲁教版六年.. 2页

TBP在水相中活度系数的探讨 2页

2025年幼儿园大班工作计划范本(20篇) 89页

S P震相与地壳厚度关系的再讨论 2页

2025年幼儿园大班中秋节活动方案[实用] 2页

2025年幼儿园大班2025新学期工作计划 8页

2025年幼儿园培训心得体会及感悟 25页

2025年幼儿园圣诞节活动主题 大班圣诞节活动策.. 12页

颅底内外沟通区的显微外科解剖 4页

SLIA-1000型二氧化硅分析仪的改进 2页

2025年幼儿园后勤个人总结 46页

SGLT2抑制剂缓解阿霉素心脏毒性的研究的开题报.. 2页

SF 6中旋转电弧运动特性和形态的试验与分析 2页

Selexol脱除酸性气体用于老厂改造的建议 2页

SAMA图例符号在自控设计中的应用 2页

2025年幼儿园保育随笔优秀范文(篇) 12页

2025年幼儿园保育员简短随笔8篇 12页

2025年幼儿园保育员岗位职责制度细则 27页

2025年幼儿园体育游戏抱树成团教案 15页

2025年幼儿园五大领域总结篇(精华) 56页

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

借款合同模板(电子版) 5页

服装设计合作协议书 5页

全国学前教育普及普惠区创建工作方案 5页

历年山东省烟台市物业公司物业管理基本工作范.. 15页