用好身份证中的信息

　　我们知道，身份证号码包含着很多重要的个人信息，比如，性别、年龄、生日、所在地。在Excel2013中，我们可以借助函数方便地从身份证中提取相关信息。

　　中国人民共和国公民第二代身份证号码，按照顺序从左至右依次为：6位数字地址码、8位数字出生年月码、3位数字顺序码和1位数字检验码。

　　提取出生年月

　　身份证号码中，从第7位开始就是出生年月日，15位身份证号包含6位生日码，18位身份证号包含8位生日码，只要确定身份证号的位数，就可以使用Excel中的函数获取具体的性别。

　　将身份证号码输入Excel电子表格中，在生日一列中输入如下函数：=TEXT(RIGHT(19&MID(B2，7，6+(LEN(B2)=18)*2)，8)，“0-00-00”)+0这里的B 2表示身份证号码所在列，MID(B2，7，6+(LEN(B2)=18)*2)是判断身份证号码的位数，18位取8位，不然则提取6位，如果是6位则自动在前面添加19，最后通过通过t e x t函数将8位日期文本转换成日期格式的数据。

　　如果身份证号是18位，那提取生日的公式就是：=TEXT(MID(B2，7，8)，“0-00-00”)+0

　　判定个人性别

　　身份证号码中出生年月的后3位是顺序码，其中奇数表示男性，偶数表示女性，因此可以根据顺序码自动判定性别。

　　加入身份证号码从B2列开始，则公式应该这么写：=IF(MOD(MID(B2，15+(LEN(B2)=18)*2，1)，2)，“男”，“女”)这里主要是通过MOD对顺序码进行判断，奇数自动判定为男性，偶数自动判定为女性。

　　获取真实年龄

　　我们还可以根据身份证号码获取真实年龄，这里可通过DATEDIF函数从身份证号码中获取真实年龄。

　　打开Excel文档，在年龄一列，输入如下函数：=DATEDIF(TEXT(MID(B2，7，LEN(B2)/2-1)，“0-00-00”)，NOW()，“y”)识别所在地区身份证的前6位是地址码，地址码中包含常住户口所在行政区代码，行政区域代码可以到网上搜索获取，比如，江苏省的行政区域代码就是32，北京市的行政区域代码是11，具体可参阅《国家行政区域的划分及省份代码》：http：//url.cn/KdZ1TL。

　　在Excel中创建2个工作表，其中一个是个人信息，另外一个是区域代码，全选“区域代码”单元格，在“名称框”中输入Province，这样就可以通过身份中的行政代码判定所在省份。切换到个人信息工作表，在省份所在列中输入：=VLOOKUP(LEFT(B2，2)，Province，2，0)

　　借助Province区域代码表，通过函数就能自动配对并识别判定所在省份。

　　升级老身份证

　　对于15位的身份证号码，我们可以根据一代身份证省级至二代身份证的规则和算法，将15位身份证号码升级为18位身份证号码。

　　打开电子表格，在18位身份证号码中输入以下函数：=REPLACE(B2，7，，19)&MID(“10X98765432”，1+MOD(SUMPRODUCT(MID(REPLACE(B2，7，，19)，ROW($1：$17)，1)*MOD(2^(18-ROW($1：$17))，11))，11)，1)

　　这样我们可以看到，新身份证号码会增加在日期增加19，在最后增加识别码，非常智能。

　　身份证号码包含的信息，与Excel中函数结合起来应用，可以满足我们更多需求，特别是在统计个人信息时，我们只要有身份证号码，就可以自动获取到她的性别、生日等，可以事半功倍地进行高效办公。

　　出生年月所在列的格式要设置为“日期”，选中所在列，右击选择“设置单元格格式”，在“数字”下选择“日期”，再选择一种日期的类型。

　　文/江苏 沈海燕