身份证号码函数

详细制作步骤

要想了解身份证的秘密，首先让我们了解一下我国现行的公民身份证标准。

1. 我国现行使用公民身份证号码有两种标准。 2. 15位身份证号码（六位数字地址码，六位数字出生日期码，三位数字顺序码）。 3. 18位身份证号码（六位数字地址码，八位数字出生日期码，三位数字顺序码和

一位数字校验码）。 4.

5. 地址码表示编码对象常住户口所在县(市、旗、区)的行政区划代码。

6. 日期码表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、

日之间不用分隔符。

7. 顺序码表示同一范围内对同年、月、日出生的人员编定的顺序号。奇数分给男

性，偶数分给女性。

8. 校验码是根据前面十七位数字码计算出来的检验码。

复制代码

了解了以上知识后，让我们来制作Excel表。

1. 新建一个Excel文档，

2. 将Sheet1重命名为“身份证信息”，在第1行各列中依次输入“身份证号码、

十五位身份证号码、十八位身份证号码、性别、出生日期、年龄、省份、市、区县”等文字，并将“出生日期”列设置为日期（yyyy.mm.dd）格式，其余列设置为文本格式。

3. 将Sheet2重命名为“区域信息”，从国家统计局下载到最新县及县以上行政

区划代码，经整理后分别导入到“区域信息”的A、B两列中，以便查询。

复制代码

到此，Excel表框架搭建完成，我们来写入函数完成查询。

第一步 判断号码是否为正确的身份证号

一个正确的身份证号码，要符合以下几个标准： ①应为15位或18位；

②要包含数字（0－9）或字符（X）；

③18位身份证号最后一位是真正的校验码。

如果上面三个条件都满足，则是正确的身份证号。

我们利用Excel单元格的“有效性”限制来完成校验。 具体方法是选择“身份证信息”表，全选“身份证号码”列，选择“数据－有效性”菜单命令，在弹出的窗口“设置”标签中，将有效性条件的“允许”设置为“自定义”、“公式”

设置为 ＝

OR(LEN(A2)=15,IF(LEN(A2)=18,MID(\"10X98765432\\")),1)*2^(18-ROW(INDIRECT(\"1:17\")))),11)+1,1)=RIGHT(A2)))，如图一。

在“出错警告”标签中，设置出错提示信息，如图二。

第二步 15位和18位身份证号码的互换

具体方法是选择“身份证信息”表，在B2单元格中输入

=IF(LEN(A2)=15,A2,LEFT(REPLACE(A2,7,2,),15))； 在C2单元格中输入

{=IF(LEN(A3)=15,REPLACE(A3,7,,19)&MID(\"10X98765432\9),ROW(INDIRECT(\"1:17\")),1)*2^(18-ROW(INDIRECT(\"1:17\")))),11)+1,1),A3)} 这里要注意是数组！

第三步 判断性别

如果是15位的，则取最后一位； 如果是18位的号，则取倒数第二位； 最后判断该数值的奇偶性即可。

具体方法是选择“身份证信息”表，在D2单元格中输入=IF(A2<>\"\男\女\"),)

第四步 判断出生日期

如果是15位的，则取第7至12位，在年份数前加上“19”； 如果是18位的，则直接取第7至14位；

具体方法是选择“身份证信息”表，在E2单元格中输入

=IF(A2<>\"\

第五步 判断年龄

这里利用一个Excel的隐藏函数 DATEDIF（）

基本语法： =DATEDIF(开始日期，结束日期，单位代码），用“Y”来表示年份。 具体方法是选择“身份证信息”表，在F2单元格中输入

=IF(A2<>\"\ODAY(),\"Y\"),)

第六步 判断籍贯，包括省份、市、区县

这是本期比赛最难的一部分，关键在于如何多重判断省份、市、区县信息。

首先要知道地址码的构成。代码前两位是省或直辖市代码，中间两位是市代码，最后两位是区县代码。

其次是数据存放。“区域信息”表中已经存放了查询所需的数据，将数据区命名为“Code”以便查询。如图三

最后利用VLOOKUP函数来查询数据。

基本语法：＝VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

具体方法是选择“身份证信息”表，

在G2单元格中输入=IF(A2<>\"\在G2单元格中输入=IF(A2<>\"\在G2单元格中输入=IF(A2<>\"\

最后一步 根据需要拖拽

刚才所写的公式只是在第2行写入的，大家可以根据需要向下拖拽，具体方法略去。