用好身份证中的信息

沈海燕

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

小知识

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

Tips

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

提取出生年月

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

将身份证号码输入Excel电子表格中，在生日一列中输入如下函数（如图1）：

=TEXT（RIGHT（19&MID（B2，7，6+（LEN（B2）=18）*2），8），"0-00-00"）+0

这里的B2表示身份证号码所在列，MID（B2，7，6+（LEN（B2）=18）*2）是判断身份证号码的位数，18位取8位，不然则提取6位，如果是6位则自动在前面添加19，最后通过通过text函数将8位日期文本转换成日期格式的数据。

如果身份证号是18位，那提取生日的公式就是：

=TEXT（MID（B2，7，8），"0-00-00"）+0

判定个人性别

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

加入身份证号码从B2列开始，则公式应该这么写（如图2）：

=IF（MOD（MID（B2，15+（LEN（B2）=18）*2，1），2），"男"，"女"）

这里主要是通过MOD对顺序码进行判断，奇数自动判定为男性，偶数自动判定为女性。

获取真实年龄

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

打开Excel文档，在年龄一列，输入如下函数（如图3）：

=DATEDIF（TEXT（MID（B2，7，LEN（B2）/2-1），"0-00-00"），NOW（），"y"）

识别所在地区

身份证的前6位是地址码，地址码中包含常住户口所在行政区代码，行政区域代码可以到网上搜索获取，比如，江苏省的行政区域代码就是32，北京市的行政区域代码是11，具体可参阅《国家行政区域的划分及省份代码》：http：//url.cn/KdZ1TL。

在Excel中创建2个工作表，其中一个是个人信息，另外一个是区域代码（如图4），全选“区域代码”单元格，在“名称框”中输入Province，这样就可以通过身份中的行政代码判定所在省份。

切换到个人信息工作表，在省份所在列中输入（如图5）：

=VLOOKUP（LEFT（B2，2），Province，2，0）

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

升级老身份证

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

打开电子表格，在18位身份证号码中输入以下函数（如图6）：

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