Excel从身份证号码中提取各种信息的汇总教程
Excel从⾝份证号码中提取各种信息的汇总教程
这是⼀篇整合⾝份证号码提取的总结教程！
只需在Excel中输⼊⾝份证号码即可得到以下数据：包含校验输⼊的正确性，提取年龄、性别、出⾝年⽉、所在地区、⽣肖、星座、⽣⽇提醒。
18位⾝份证的号段含义如下：
前2位表⽰所属省份代码，3~6位表⽰所属城市和区县代码，7~14位表⽰出⽣⽇期，格式为YYYYMMDD，15~17位是个⼈顺序码，其中17位可以标识性别，奇数表⽰男性，偶数表⽰⼥性，第18位是校验码，由前17位通过计算求得。
输⼊18位⾝份证显⽰4.45121E+17，WHY？
这是科学计数法，E+17是10的17次⽅。默认情况下，数字超过12位就显⽰科学技术法，数字超过15位后⾯都显⽰0。如图所⽰。
批量改文件名
选择A列，将单元格格式设置为⽂本，再重新输⼊⾝份证即可。
gl小说设置好后，⼜有⼀个问题。如何才能保证⾝份证刚好是18位，同时还得符合⾝份证的所有条件，前17位必须是数字，第18位是X或者是数字。
女孩子生日送什么好
18位可以⽤
=LEN(A2)=18
前17位是数字
营养师怎么考=ISNUMBER(-LEFT(A2,17))
第18位是数字或者字母X
=OR(ISNUMBER(-RIGHT(A2)),RIGHT(A2)="X")
张三链子
综合3个条件，可以进⾏数据有效性设置。
选择A2:A5，设置数据有效性（注：Excel2013版叫数据验证），在“允许”下拉列表框中选择“⾃定义”选项。在“公式”⽂本框输⼊下⾯的公式，点击“确定”按钮。
=AND(LEN(A2)=18,ISNUMBER(-LEFT(A2,17)),OR(ISNUMBER(-RIGHT(A2)),RIGHT(A2)="X"))
数据有效性对于已经输⼊好的⾝份证⽆效，但可以把不满⾜条件的圈出来。对设置好数据有效性后进⾏“圈释⽆效数据”。
把⽆效的数据重新更改即可，然后输⼊正确的⾝份证，以后如果输⼊不正确的话，会提⽰错误，直到你输⼊正确为⽌。如图所⽰。
关于⾝份证的准备⼯作已经做好，后⾯是获取各种信息的关键时刻。
生活环保小知识如图所⽰，是⼀份详细的地区码。
通过提取码可以查询到省份跟地区。
省份就是提取⾝份证前2位，然后去地区码查询对应值。
=VLOOKUP(LEFT(A2,2)，地区码!A:B,2,0)
地区就是提取⾝份证前6位，然后去地区码查询对应值。
=VLOOKUP(LEFT(A2,6)，地区码!A:B,2,0)
出⽣⽇期就是从⾝份证第7位开始提取8位，将提取到的数字转换成0-00-00这种形式。
=--TEXT(MID(A2,7,8),"0-00-00")
性别就是第15-17位，奇数是男，偶数是⼥。
=IF(MOD(MID(A2,15,3),2),"男","⼥")
除了上⾯介绍的，通过出⽣⽇期还可以获取⼀系列问题：周岁、⽣肖、⽣⽇提醒、星座等。现在就以这4个常见问题再进⾏说明。为了⽅便，出⽣⽇期就引⽤D2单元格。
周岁
=DATEDIF(D2,NOW(),"y")
⽣肖
=MID("⿏⽜虎兔龙蛇马⽺猴鸡狗猪",MOD(TEXT(D2,"[$-130000]e")-4,12)+1,1)
TEXT(D2,"[$-130000]e")的作⽤就是将⽇期转换成阴历年份。
⽣⽇提醒
=TEXT(TEXT(D2,"m-d")-TEXT(NOW(),"m-d"),"还有0天⽣⽇;；今天⽣⽇")
先将⽇期转变成⽉⽇再进⾏相减，⼤于0就显⽰还有多少天⽣⽇，⼩于0就不显⽰，0就是今天⽣⽇。
星座
=LOOKUP(--TEXT(D2,"m.dd"),{0,"摩羯座";1.21,"⽔瓶座";2.2,"双鱼座";3.21,"⽩⽺座";4.21,"⾦⽜座";5.22,"双⼦座";6.22,"巨蟹座";7.24,"狮⼦座";8.24,"处⼥座";9.24,"天秤座";10.24,"天蝎座";11.23,"射⼿座";12.23,"摩羯座"})
这个看着很长，其实很简单，只要获取星座对应表就可以。
经过了N步处理，最终效果如图所⽰。
如果你觉得教程不错，请分享给你的朋友们，独乐乐不如众乐乐！
- END -
部落窝推出的原创Word、Excel、PPT、PS、AI、CDR、CAD、3Dmax、PS转⼿绘、⽹店美⼯设计
、⽹页设计等成套基础视频教程，每门只需9.9元，购买后直接发送到你qq邮箱

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。