巧用excel管理身份证号码

第一篇：巧用excel管理身份证号码

巧用excel管理身份证号码

员工的身份证号码信息是HR部门最重要的信息之一，在很多HR管理的表中都可以看到它（见图表1）。由于身份号码中可以提取出很多HR所需的有用的信息，所以利用Excel对身份证号码的管理和运算是HR管理者必备的技能。

身份证号码的管理

我们先来说说身份证号码的管理。身份证号码信息是一组长编码，因此在管理这类信息时要注意以下两个方面的设置：

设置文本格式

在输入身份证号码前，要先将单元格设置成“文本”格式，然后才能输入信息（顺序不能颠倒），否则会造成输入的身份号码为默认的“常规”数值类型。这样一来，一个18位的身份号码会变成“科学计数法”显示，并且由于Excel自身的15位数据精度的问题，会使身份证号码的后3位自动转换成“0”。设置数据有效性

为了防止在输入身份证号码的过程中出现多输入或少输入的错误，还应在设置了“文本”格式后再为这些单元格设置“数据有效性”的条件。当前，由于身份号码普遍都已经升级为二代身份证号码，也就是18位编号。所以若没有例外情况，只需按如下操作即可完成。

第一步：选中所有待输入身份证号码的单元格，然后选择“数据”选项卡中的“数据有效性”按钮，打开“数据有效性”对话框。

第二步：将“有效性条件”中的“任何值”更改成“文本长度”条件，再将“数据”中的“介于”更改成“等于”，然后在下面的“长度”框中输入“18”，单击“确定”按钮

倘若在员工统计表中存在一代身份证号码（15位）和二代身份号码（18位）混杂的情况，那么就只能使用数据有效性中的“自定义”条件才能进行严格的限制了，操作如下：

第一步：选中所有待输入身份证号码的单元格，然后选择“数据”选项卡中的“数据有效性”按钮，打开“数据有效性”对话框。

第二步：将“有效性条件”中的“任何值”更改成“自定义”条件，再在下方的“公式”栏中输入函数“=OR（LEN（A1）=15，LEN（A1）=18）”，打开“出错警告”—“样式”—“信息”自行填写出错提示信息！然后单击“确定”按钮。

通过前面的设置，我们已经做好了输入前的准备，这样一来，单元格既是文本格式，又有了非常严谨的有效性的限制，下面就可以放心地输入每位员工的身份证号码了。

身份号码的运算

由于身份证号码有着很强的规律信息特征，所以对于HR来说应该很好地利用它的这个特点。当身份证号码一旦输入完成，便可自动计算出员工的“出生日期”、“年龄”和“性别”等信息。

首先，让我们了解一下身份证号码的规律。对于一个18位的身份证号码，前面6位是这个人的户籍所在地，前3位是省，后3位是城市（对于直辖市来说，前3位是市，后3位是区），从第7位到14位是这个人的出生日期，第15位到17位是顺序号，其中第17位是性别码，奇数为男，偶数为女，最后一位是校验码，由数字0-9和罗马数字的Ⅹ（10）11个数字组成。

这样一个带有规律的信息编码，只要身份证号码填写无误，那么利用Excel的“文本提取”函数就可计算“出生日期”，利用日期计算的公式就可以计算出“年龄”，再利用奇偶数的判断就可自动计算出“性别”信息。

自动计算出生日期

由于出生日期是身份证号码中的第7位至第14位，所以我们使用“文本提取”函数和“文本合并”的公式就可自动将出生日期计算出来，操作如下：

第一步：选中第1个人员的出生日期单元格。

第二步：然后在单元格中输入“=MID（A1，7，4）&“-”&MID（A1，11，2）&“-”&MID（A1，13，2）”，单击“Enter”键即可得到第1个人的出生日期信息。

第三步：向下填充公式至最后一个人。

自动计算年龄

出生的日期有了，就可以使用“TODAY”函数配合自动计算每个员工的实际年龄了，操作如下：

第一步：选中第1个人员的年龄单元格。

第二步：然后在单元格中输入“=INT（（TODAY（）-D1）/365）”，单击“Enter”键即可得到第1个人的年龄信息。

第三步：向下填充公式至最后一个人。

自动计算性别

性别码是身份证号码中的第17位，奇数位男，偶数为女，所以只要把第17位提取出来进行奇偶判断，就可自动得到性别信息了，操作如下：

第一步：选中第1个人员的性别单元格。

第二步：然后在单元格中输入“=IF（MOD（MID（$A$1:$A$200，17，1），2）=0，“女”，“男”）”，单击“Enter”键即可得到第1个人的性别信息。

第三步：向下填充公式至最后一个人。

第二篇：身份证号码证明

公民身份证号码更正证明

本辖区公民吴彦（曾用名），女，汉族，河南省泌阳县泌水镇新村居委会住。

原使用的公民身份号码为：

系（重 / 错）号。根据《中华人民共和国居民身份证法》的有关规定及公民身份号码的编制原则，为确保公民身份证号码的准确性和唯一性，自2008 年月日起将原号码更正为：

特此证明

河南省泌阳县泌水派出所

2011 年9 月22 日

注：请为申请人吴彦办理公民身份号码变更的相关事宜。此件为原件，如需要请留存复印件。、

第三篇：身份证号码含义

新的身份证为18位，老的身份证是15位。关于身份证第18是怎么计算的，原理如下：根据〖中华人民共和国国家标准 GB 11643-1999〗中有关公民身份号码的规定，公民身份号码是特征组合码，由十七位数字本体码和一位数字校验码组成。

排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。

地址码（身份证前六位）表示编码对象常住户口所在县(市、旗、区)的行政区划代码。（所有区域的编码可以到这个网站http:///tjbz/index.htm 查询到最新的县及县以上的行政编码资料。）

生日期码（身份证第七位到第十四位）表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符。例如：1981年05月11日就用19810511表示。

顺序码（身份证第十五位到十七位）为同一地址码所标识的区域范围内，对同年、月、日出生的人员编定的顺序号。其中第十七位奇数分给男性，偶数分给女性。

校验码（身份证最后一位）是根据前面十七位数字码，按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。第十八位数字的计算方法为：

1.将前面的身份证号码17位数分别乘以不同的系数。从第一位到第十七位的系数分别为: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2

2.将这17位数字和系数相乘的结果相加。

3.用加出来和除以11，看余数是多少？

4余数只可能有0 1 2 3 4 5 6 7 8 9 10这11个数字。其分别对应的最后一位身份证的号码为1 0 X 9 8 7 6 5 4 3 2。

5.通过上面得知如果余数是2，就会在身份证的第18位数字上出现罗马数字的Ⅹ。如果余数是10，身份证的最后一位号码就是2。

例如：某男性的身份证号码是***01X。我们要看看这个身份证是不是合法的身份证。首先：我们得出，前17位的乘积和是189 然后：用189除以11得出的结果是17 + 2/11，也就是说余数是2。最后：通过对应规则就可以知道余数2对应的数字是x。所以，这是一个合格的身份证号码。例如：某男性的身份证号码是***01X。我们要看看这个身份证是不是合法的身份证。首先：我们得出，前17位的乘积和是189 然后：用189除以11得出的结果是17 + 2/11，也就是说余数是

2。最后：通过对应规则就可以知道余数2对应的数字是x。所以，这是一个合格的身份证号码。

第四篇：身份证号码函数

用vlookup函数，及其他各项功能，通过身份证号得出了出生日期、性别、户籍所在地等多项信息，并加入了身份证号正确性验证机制和15位、18位号码的转换功能。

详细制作步骤

要想了解身份证的秘密，首先让我们了解一下我国现行的公民身份证标准。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”,MOD(SUM(MID(A2,ROW(INDIRECT(“1:17”)),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”,MOD(SUM(MID(REPLACE(A3,7，19),ROW(INDIRECT(“1:17”)),1)*2^(18-ROW(INDIRECT(“1:17”)))),11)+1,1),A3)} 这里要注意是数组！

第三步 判断性别

如果是15位的，则取最后一位；

如果是18位的号，则取倒数第二位；

最后判断该数值的奇偶性即可。

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

=IF(A2<>“",IF(MOD(RIGHT(LEFT(A2,17)),2),”男“,”女“),)

第四步 判断出生日期

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

如果是18位的，则直接取第7至14位；

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

=IF(A2<>”“,TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“)+0,)

第五步 判断年龄

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

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

=IF(A2<>”“,DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“),TODAY(),”Y“),)

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

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

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

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

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

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

具体方法是选择“身份证信息”表，在G2单元格中输入=IF(A2<>”“,VLOOKUP(LEFT(A2,2)&”0000“,code,2,),)

在G2单元格中输入=IF(A2<>”“,VLOOKUP(LEFT(A2,4)&”00“,code,2,),)

在G2单元格中输入=IF(A2<>”",VLOOKUP(LEFT(A2,6),code,2,),)

最后一步 根据需要拖拽

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

第五篇：居民身份证号码

居民身份证号码 基本简介 编辑本段

居民身份证是国家法定的证明公民个人身份的证件。为了堵塞和制止假居民身份证的流通和使用，在查验和核查居民身份证时可掌握以下几个要点：[2]

1.1

一、有效期限

居民身份证的有效期限分为10年、20年、长期三种。1居民身份证号码6周岁至25周岁的，发给有效期为10年的居民身份证；26周岁至45周岁的，发给有效期为20年的居民身份证；46周岁以上的，发给长期有效的居民身份证。证件有效期限从签发之日起计算。如某人1949年9月20日出生，1984年35周岁时申领居民身份证，签发日期为1984年12月31日，他属于26至45周岁这一年龄段，证件有效期限属于20年这一档次，到2004年12月30日有效期满。查验或检查时，应对照检查证件有效期限与持证人年龄，签发日期三者之间的关系。

1.2

二、编号识别

1、身份证编码规则如下：根据〖中华人民共和国国家标准GB11643-1999〗中有关公民身份号码的规定，公民身份号码是特征组合码，由十七位数字本体码和一位数字校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。地址码（身份证前六位）表示编码对象第一次申领居民身份证时的常住户口所在县（市、旗、区）的行政区划代码。（所有区域的编码可以到网站查询到最新的县及县以上的行政编码资料。）生日期码（身份证第七位到第十四位）表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符。例如：1981年05月11日就用19810511表示。顺序码（身份证第十五位到十七位）是县、区级政府所辖派出所的分配码，每个派出所分配码为10个连续号码，例如“000-009”或“060-069”，其中单数为男性分配码，双数为女性分配码，如遇同年同月同日有两人以上时顺延第二、第三、第四、第五个分配码。如：007的就是个男生，而且和他同年月日生的男生至少有两个，他们的后四位是001*和003*。分配顺序码中“999、998、997、996”四个顺序号分别为男女性百岁以上老人专用的特定编号。校验码（身份证最后一位）是根据前面十七位数字码，按照ISO7064:1983.MOD11-2校验码计算出来的检验码。

2、从1999年10月1日起，全国实行公民身份证号码制度，居民身份证编号由原15位升至18位。前6位为地址码；第七位至14位为出生日期码，此码由6位数改为8位数，其中年份用4位数表示；第15位至17位为顺序码，取消了顺序码中对百岁老人使用的特定编号；第十八位为校验码，主要是为了校验计算机输入公民身份证号码的前17位数字是否正确，其取值范围是0至10，当值等于10时，用罗马数字符χ表示。

1.3

三、校验码的计算方法

1、将前面的身份证号码17位数分别乘以不同的系数。从第一位到第十七位的系数分别为：7－9－10－5－8－4－2－1－6－3－7－9－10－5－8－4－2。

2、将这17位数字和系数相乘的结果相加。

3、用加出来和除以11，看余数是多少？

4、余数只可能有0－1－2－3－4－5－6－7－8－9－10这11个数字。其分别对应的最后一位身份证的号码为1－0－X －9－8－7－6－5－4－3－2。居民身份证号码

5、通过上面得知如果余数是2，身份证的最后一位号码就是罗马数字x。如果余数是10，就会在身份证的第18位数字上出现的是2。例如：某男性的身份证号码是oooooooooooooooooX。我们要看看这个身份证是不是合法的身份证。首先我们得出前17位的乘积和是189，然后用189除以11得出的结果是17+2/11，也就是说其余数是2。最后通过对应规则就可以知道余数2对应的是罗马数字X。所以，可以判定这是一个合格的身份证号码。号码构成 编辑本段

2.1 1 地址码

（身份证号码前六位）表示编码对象常住户口所在县（市、镇、区）的行政区划代码。1-2位省、自治区、直辖市代码； 3-4位地级市、省、自治州代码； 5-6位县、县级市、区代码。

2.2 2 生日期码

（身份证号码第七位到第十四位）表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符。例如：1981年05月11日就用19810511表示。

2.3 3 顺序码

（身份证号码第十五位到十七位）地址码所标识的区域范围内，对同年、月、日出生的人员编定的顺序号。其中第十七位奇数分给男性，偶数分给女性。

2.4 4 校验码

（身份证号码最后一位）是根据前面十七位数字码，按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。作为尾号的校验码，是由号码编制单位按统一的公式计算出来的，如果某人的尾号是0-9，都不会出现X，但如果尾号是10，那么就得用X来代替，因为如果用10做尾号，那么此人的身份证就变成了19位，而19位的号码违反了国家标准，并且中国的计算机应用系统也不承认19位的身份证号码。Ⅹ是罗马数字的10，用X来代替10，可以保证公民的身份证符合国家标准。一般使用 编辑本段

公民在办理下列事务，需要证明身份时，可以出示居民身份证：

（一）选民登记；

（二）户口登记；

（三）兵役登记；

（四）婚姻登记；

（五）入学，就业；

（六）办理公证事务；

（七）前往边境管理区；

（八）办理申请出境手续；

（九）参与诉讼活动；

（十）办理机动车，船驾驶证和行驶证，非机动车执照；居民身份证号码

（十一）办理个体营业执照；

（十二）办理个人信贷事务；

（十三）参加社会保险，领取社会救济；

（十四）办理搭乘民航飞机手续；

（十五）投宿旅店办理登记手续；（十六）提取汇款，邮件；（十七）寄卖物品；（十八）办理其他事务.除公安机关依法对被告人采取强制措施可以扣留居民身份证外，其他任何单位和个人不得扣留公民的居民身份证或者作为抵押.4 查询收费 编辑本段

[1]国家发展改革委日前在其官方网站上发布关于全国公民身份证号码查询服务中心有关收费问题的通知。通知称，对有关单位出示的公民身份信息进行比对，提供“一致”或“不一致”信息服务，可收取公民身份认证服务费，收费标准为：比对内容五项以下（含五项），每次5元；比对内容五项以上，每次10元。通知说，根据用户需求，提供人口数据信息汇总、加工或重新制作等服务，可收取人口信息开发服务费，收费标准为每次50元。对特殊需求的，收费标准由查询中心与用户协商确定。按规定向国家机关、社会福利公益机构和公民提供上述服务不得收取费用。通知指出，查询中心不得提供可能危及国家安全、公共安全、经济安全和社会稳定的有关信息服务。通知强调，公安部门不得强制公民、法人或其他组织到查询中心接受咨询、查询等服务并收取费用。通知称，查询中心收取上述费用应使用税务发票，依法纳税，并严格执行规定的收费项目和收费标准，自觉接受价格主管部门的监督检查。国家发展改革委关于全国公民身份证号码查询服务中心有关收费问题的通知公安部：你部《关于申请核定身份认证服务收费标准的函》（公科信[2010]61号）收悉。考虑到你部所属全国公民身份证号码查询服务中心（以下简称“查询中心”）为自收自支的事业单位，负责全国公民身份信息的收集处理和全国公民身份信息系统的建设、管理、运营，向社会提供公民身份认证等服务，经研究，现就公民身份认证等服务收费标准及有关问题通知如下：

一、查询中心按照自愿有偿原则提供下列服务，不属于政府行为，其收费作为经营服务性收费管理。

（一）对有关单位出示的公民身份信息进行比对，提供“一致”或“不一致”信息服务，可收取公民身份认证服务费，收费标准为：比对内容五项以下（含五项），每次5元；比对内容五项以上，每次10元。

（二）根据用户需求，提供人口数据信息汇总、加工或重新制作等服务，可收取人口信息开发服务费，收费标准为每次50元。对特殊需求的，收费标准由查询中心与用户协商确定。

（三）按规定向国家机关、社会福利公益机构和公民提供上述服务不得收取费用。

二、查询中心不得提供可能危及国家安全、公共安全、经济安全和社会稳定的有关信息服务。

三、公安部门不得强制公民、法人或其他组织到查询中心接受咨询、查询等服务并收取费用。

四、查询中心收取上述费用应使用税务发票，依法纳税，并严格执行规定的收费项目和收费标准，自觉接受价格主管部门的监督检查。

五、上述规定自2010年10月1日起试行，试行期2年，试行期满后由你部报我委重新审批。身份证号查询：通过身份证号码可以查询地区信息，并且可以进行地区定位.