专注办公软件培训

　　
　　当我们在Excel里录入身份证号码后，往往需要获得一些相关的信息，通常在一个身份证号码里，我们可以直接获取一些信息，但是这需要先来了解一下身份证号码的相关知识：
 

　　18位身份证号码的编码含义
 

　　1-2位省、自治区、直辖市代码;
 

　　3-4位地级市、盟、自治州代码;
 

　　5-6位县、县级市、区代码;
 

　　7-14位出生年月日，比如19670401代表1967年4月1日;
 

　　15-17位为顺序号，其中17位(倒数第二位)男为奇数，女为偶数;
 

　　18位为校验码，0-9和X。作为尾号的校验码，是由把前十七位数字带入统一的公式计算出来的，计算的结果是0-10，如果某人的尾号是0-9，都不会出现X，但如果尾号是10，那么就得用X来代替，因为如果用10做尾号，那么此人的身份证就变成了19位。X是的10，用X来代替10。
 

　　通过以上的介绍，可以知道，在一个身份证号码里，我们直接获得的信息有这么几个：省份、地区(市-区)、出生日期、性别。
 

　　还可以间接获得与出生日期有关的信息，例如：年龄、生肖等等，甚至利用校验位的计算规则来判断身份证号码的真伪。
 

　　说了这么多，到底在Excel里如何实现呢?接下来一一为大家介绍……

　　如图，根据身份证号码可以得到后面的七个信息，分别来看看：
 

　　1、所属省：
 

　　公式：=VLOOKUP(LEFT(D2,2),A:B,2,)

　　一个很简单的vlookup引用，需要说明的是AB两列是一个地区编码对照表。这个对照表可以去网上下载，也可以找我要!
 

　　2、所属地区
 

　　公式=VLOOKUP(LEFT(D2,6),A:B,2,)

　　这个方法同所属省的原理。
 

　　3、出生日期
 

　　关于出生日期，公式就五花八门，会有很多的解法：
 

　　公式1：=--TEXT(MID(D2,7,8),"0-00-00")

　　使用这个公式需要修改单元格格式，否则结果显示为数字。
 

　　公式2：=DATE(MID(D2,7,4),MID(D2,11,2),MID(D2,13,2))

　　这是最标准也最容易理解的一个公式，得到的结果就是标准日期格式。
 

　　公式3：=TEXT(MID(D2,7,11)-500,"0-00-00,")

　　这个公式就有点不好理解了，需要对数字与日期的关系非常了解，同时要注意到一个细节，“0-00-00,”这里的逗号非常关键。
 

　　4、年龄：
 

　　公式=DATEDIF(G2,NOW(),"y")

　　因为有了出生日期，当然可以直接利用，否则就把出生日期的公式代入公式即可。
 

　　5、性别：
 

　　公式=TEXT(-1^MID(D2,9,9),"女;男")

　　6、生肖：
 

　　公式=MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(MID(D2,7,4)-4,12)+1,1)

　　公式不算难，明白mod函数和mid函数的用法以后自己慢慢理解思路吧。

　　7、判断真假

　　最后这个真伪验证的公式，非常难以解释，简单来说就是根据前面的17位，按照校验码的生成规则与单元格内的身份证号码最后一位进行比较，一致则为真，不一致则为假，同时这是一个数组公式，需要三键结束。有需要这个公式可以直接拿去套用：
 

　　=IF(RIGHT(D2)=MID("10X98765432",MOD(SUM(MID(D2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),"真","假")
 

　　相信通过今天的分享，你一定会有很多收获吧!最后再来分享一个利用分列直接在身份证号码里获取生日的GIF操作分享吧!
 

欢迎大家查看更多的办公软件技巧学习。