利用Excel玩转身份证号码，判断其正确性

1、在互联网上搜索得到身份证前6位对应的省市，并做在表格中。

2、设计好表格形式。

当我们输入身份证号码时，会判断该身份证号码是否正确，一起获得对应的籍贯，生日，年龄，性别等信息。

3、我们知道，身份证包含下面信息：

第1-6位表示省、市、县等信息。

第7-14位表示出生年、月、日。

第15-16位表示对应的派出所编码。

第17位表示性别。

第18位表示校验信息。

我们先从最难的部分（第18位）开始。

校检码可以是0-9的数字，有时也用X表示。X为罗马数字10。

从第1位到第17位的数字，依次乘以它们的权重，然后求和，再除以11，取余数。

查找校验表格，以该余数作为序号，查得的校验值作为身份证第18位的结果。

A2单元格存储我们的身份证信息，一定要存储位文本格式(因为Excel最大精度只有15位，没有办法用数值的方式存储我们的身份证号码）。

首先，我们利用mid(A2,i,1)取得第i位的身份证数字。

4、计算加权因子。

计算方法为：2的n次方，再除以11取余数。其中n为18-i。

那么第一位为2的(18-1=17)次方，然后除以11取余数，结果为7。

依次类推，第17位位2的(18-17=1）次方，然后除以11取余数，结果为2。

然后取第i位的数字乘以他们的权重。结果为28,18，...，0,10

5、把所有计算好的结果加起来（28+18+...+0+10），得到251。

用251除以11，取余数得到9

6、通过查找校验表格，对应9的位置，得到结果为3。

查找时使用函数HLOOKUP，校验表格对应在J9:T10区域中，需要查找的值9位于J7单元格中。通过使用HLOOKUP(J7,J9:T10,2)，得到结果为3。

所以该身份证号码第18位位3。

7、如果校验位正确，那么接着查找其他信息。

如果校验位不正确，那么我们在remark位置显示“身份证号码有误”，不再查找其他信息。

在校验位信息正确的情况下，我们使用mid(A2,i,j)的方式查找需要的信息。

比如MID(A2,1,6)来获得地区位。用MID(A2,7,4)来获得出生年份等信息。

8、如果校验位正确，那么在B2单元格显示Y，否则显示N。

如果B2单元格为Y的情况下，查找area工作表来获得省市信息，采用的函数为VLOOKUP(J16,area!A:B,2)，其中J16为地区位，而area!A:B存储着所有地区位对应的地区全名。在我们的实例中，查到的结果位湖北省孝感市孝昌县。

通过DATE(year, month, day)来拼接出生日信息。

获取当天日期，然后使用YEARFRAC来计算年龄，保留一位小数。具体为YEARFRAC(D2,TODAY())，其中D2单元格存储着生日信息。

身份证第17位如果为奇数的话为男性，否则为女性，所以使用IF(MOD(J21,2),"男","女")来得到结果。其中J21单元格存储着身份证的第17位数字。

最后显示效果如下图

9、如果身份证号码不正确的话，那么显示如下错误。

为了方便，该Excel实例文件存储在百度云上，具体网址为http://pan.baidu.com/s/1i4XXEaL。