第一篇：excel中身份证信息自动提取
excel中如何根据身份证号，自动求出出生年月？ 很好用！根据我的实际工作需要，又少作修改的方法： 假如身份证号数据在A1单元格，在B1单元格中编辑公式
=IF(LEN(A1)=15,“19”&MID(A1,7,2)&MID(A1,9,2)&MID(A1,11,2),MID(A1,7,4)&MID(A1,11,2)&MID(A1,13,2))这样输出格式就都是19821010这种格式了。excel中如何根据身份证号，自动求出性别？
假定身份证号在A1单元格，则在B1单元格中编辑公式 =IF(AND(LEN(A1)=15,MOD(MID(A1,15,1)*1,2)=1),“男”, IF(AND(LEN(A1)=18,MOD(MID(A1,17,1)*1,2)=1),“男”,“女”))在excel是根据身份证自动填充年龄？
假设A列从A1开始是身份证号，在B列中进行转换，则在B1中输入
=YEAR(TODAY())-IF(LEN(A1)=15,“19”&MID(A1,7,2),MID(A1,7,4))，然后再向下复制即可。公式解析：
一、分析身份证号码
其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。
15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。
18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。
例如，某员工的身份证号码(15位)是***，那么表示1972年8月7日出生，性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。
二、提取个人信息
这里，我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息。如图1所示，其中员工的身份证号码信息已输入完毕(C列)，出生年月信息填写在D列，性别信息填写在B列。
1.提取出生年月信息
由于上交报表时只需要填写出生年月，不需要填写出生日期，因此这里我们只需要关心身份证号码的相应部位即可，即显示为“7208”这样的信息。在D2单元格中输入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”，其中：
LEN(C2)=15：检查C2单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位。
MID(C2,7,4)：从C2单元格中字符串的第7位开始提取四位数字，本例中表示提取15位身份证号码的第7、8、9、10位数字。
MID(C2,9,4)：从C2单元格中字符串的第9位开始提取四位数字，本例中表示提取18位身份证号码的第9、10、11、12位数字。
IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))：IF是一个逻辑判断函数，表示如果C2单元格是15位，则提取第7位开始的四位数字，如果不是15位则提取自第9位开始的四位数字。
如果需要显示为“70年12月”这样的格式，请使用DATE格式，并在“单元格格式→日期”中进行设置。
2.提取性别信息
由于报表中各位员工的序号编排是按照上级核定的编制进行的，因此不可能按照男、女固定的顺序进行编排，如果一个一个手工输入的话，既麻烦又容易出错 例如性别信息统一在B列填写，可以在B2单元格中输入公式“=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,“男”,“女”)”，其中：
LEN(C2)=15：检查身份证号码的长度是否是15位。
MID(C2,15,1)：如果身份证号码的长度是15位，那么提取第15位的数字。
MID(C2,17,1)：如果身份证号码的长度不是15位，即18位身份证号码，那么应该提取第17位的数字。
MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)：用于得到给出数字除以指定数字后的余数，本例表示对提出来的数值除以2以后所得到的余数。
IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,“男”,“女”)：如果除以2以后的余数是1，那么B2单元格显示为“男”，否则显示为“女”。
回车确认后，即可在B2单元格显示正确的性别信息，接下来就是选中填充柄直接拖曳。
第二篇：EXCEL提取身份证出生日期
EXCEL提取身份证出生日期、性别、检验身份证号码正确性
中国居民身份证号码是一组特征组合码，原为１５位，现升级为１８位，其编码规则为： １５位：６位数字常住户口所在县市的行政区划代码，６位数字出生日期代码，３位数字顺序码。
１８位：６位数字常住户口所在县市的行政区划代码，８位数字出生日期代码，３位数字顺序码和１位检验码。
其中３位数字顺序码，是为同一地址码的同年同月同日出生人员编制的顺序号，偶数的为女性，奇数的为男性。
１、提取籍贯地区的行政区划代码（A2为身份证号，下同）１５与１８位通用：=LEFT(A2,6)
如果有一个编码和省份地区的对照表，可以用VLOOKUP函数来提取地区信息。２、提取出生日期信息
１５位：=--TEXT(19&MID(A2,7,6),“#-00-00”)１８位：=--TEXT(MID(A2,7,8),“#-00-00”)１５与１８位通用：=--TEXT(IF(LEN(A2)=15,19,“")&MID(A2,7,6+IF(LEN(A2)=18,2,0)),”#-00-00“)简化公式：=--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“)(请将输入公式的单元格格式设置为日期格式）３、提取性别信息
１５位：=IF(MOD(RIGHT(A2),2)=1,”男“,”女“)１８位：=IF(MOD(MID(A2),17,1)=1,”男“,”女“)
１５与１８位通用：=IF(MOD(MID(A2,IF(LEN(A2)=15,15,17),1),2)=1,”男“,”女“)简化公式：=IF(MOD(RIGHT(LEFT(A2,17)),2),”男“,”女“)４、检验身份证号码的正确性 １８位身份证号码的最后一位是检验码，它是根据身份证前17位数字依照规则计算出来的，其值０～９或X。一般情况只要有一位数字输入错误，依照规则计算后就会与第18位数不符。当然不排除按错误号码计算后恰好与检验码相符的情况，但这种情况出现的可能性较低。因此，对１８位号码的验证采用如下公式：
=MID(”10X98765432“,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1)=RIGHT(A2,1)
对于１５位身份证，由于没有检验码，我们只能简单地去判断出生日期代码是否是一个有效的日期，避免输入一些像“731302”或“980230“等这样不存在的日期。=ISNUMBER(--TEXT(19&MID(A2,7,6),”#-00-00“))综合１５位和１８位后的通用公式为：
=IF(LEN(A2)=18,MID(”10X98765432“,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1)=RIGHT(A2),IF(LEN(A2)=15,ISNUMBER(--TEXT(19&MID(A2,7,6),”#-00-00“))))
由于目前１５位身份证号码已经很少了，如果对１５位的号码不需要作进一步的判断，则公式可以简化成：
=IF(LEN(A2)=18,MID(”10X98765432“,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1)=RIGHT(A2),LEN(A2)=15)
将上面的公式放到B2单元格，如果结果为TRUE，则身份证号是正确的，结果为FALSE则是错误的。
你也可以将上述公式放在数据有效性中，防止录入错误的身份证号。操作方法:选择需要输入身份证的全部单元格区域,比如A2:A10,点菜单”数据“-”有效性“,在”允许“的下拉框中选择”自定义“,在”公式“输入上面的15位和18位通用公式,确定以后即可。注意:公式里的”A2“是你刚才选定要输入身份证的单元格区域的第一个单元格,如果你是要在C3:C20输入身份证号,则将公式里的”A2“改为”C3“。另外,你也可以先设置好某单个单元格的数据有效性(这时公式的A2改为选定的单元格),再用格式刷将其格式刷到其他需要相同设置的单元格。
５、１５位升为１８位
=IF(LEN(A2)=15,REPLACE(A2,7，19)&MID(”10X98765432“,MOD(SUMPRODUCT(MID(REPLACE(A2,7，19),ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1),A2)
６、１８位转换为１５位
=IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)
7、示例
表中公式:
B2 =IF(LEN(A2)=18,MID(”10X98765432“,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1)=RIGHT(A2),IF(LEN(A2)=15,ISNUMBER(--TEXT(19&MID(A2,7,6),”#-00-00“))))
C2 =IF(A2<>”“,TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“)+0,)
D2 =IF(A2<>”“,IF(MOD(RIGHT(LEFT(A2,17)),2),”男“,”女“),)
E2 =IF(A2<>”“,DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“),TODAY(),”y“),)
F2 =IF(A2<>”“,VLOOKUP(LEFT(A2,2),地区表!A:D,2,),)
H2 =IF(LEN(A2)=15,REPLACE(A2,7，19)&MID(”10X98765432“,MOD(SUMPRODUCT(MID(REPLACE(A2,7，19),ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1),A2)
I2 =IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)
把应聘者或员工的个人信息录入Excel，确保信息真实可信是必须解决的问题。一．数据录入快又准
负责录入的个人信息内容如图1所示，除了“序号”、“姓名”和“身份证号码”以外，其余信息设计公式从“身份证号码”中“挖掘”。
1．别让数据变“乱”
“身份证号码”要用“文本”格式。实现这一点的第一种方法是选中D列右击鼠标，选择快捷菜单中的“设置单元格格式”，打开对话框的“数字”选项卡选中，选中“分类”下的“文本”然后“确定”即可。第二种方法是在输入的身份证号码前加一个单引号，Excel就可以把输入的数字变为“文本”了。第三种方法是选中D列，单击“格式”菜单下的“单元格”命令打开对话框，按如图1所示选中“分类”下的“自定义”。然后在“类型”框中输入一个“@”再“确定”即可。
2．录入校验 错误靠边
第一步选中存放身份证号码的数据区域（例如“D2：D800”），单击Excel“数据”菜单下的“有效性”命令，打开“数据有效性”对话框的“设置”选项卡。在“允许”下拉列表中选择“自定义”，接着在如图2所示“公式”框中输入“=COUNTIF（D:D，D2）=1”。
第二步打开“出错警告”选项卡，在“标题”框内输入“数据重复”，并按如图3所示输入更详细的警告信息，单击“确定”按钮将打开的对话框关闭。当然，这一步是可选的，使用时可以根据具体情况取舍。
此后只要在当前单元格中输入了重复数据，Excel就会弹出“数据重复”对话框告知小刘，并拒绝接受已经输入的重复数据。
除了防止录入身份证号码出现重复以外，还要防止小张输入的号码长度不足15位或18位。接下来的第三步仍然是选中录入身份证号码的数据区域（例如“D2：D80”），单击“格式”菜单下的“条件格式”命令打开如图4所示对话框，在“条件一”下拉列表中选择“公式”，然后在中间的框内输入公式“=IF（LEN（D10）<>15，LEN（D10）<>18）”。
第四步单击如图4中的“格式”按钮打开对话框，在“字体”选项卡中选择合适的颜色或删除线等。之后如果D列中输入的数据长度不是15位或18位，其字体就会显示前面选择的颜色（例如红色）。
3．录后检查 万无一失
假如上面的操作执行前已经录入了部分数据，那么有没有办法检查录入的身份证号码是否重复？可以设计一个带有公式的“条件格式”，圆满解决问题。
操作的第一步是选中如图1中的D2单元格，单击“格式”菜单中的“条件格式”命令，打开如图5所示对话框。在“条件1”下拉列表选择“公式”，然后在右边的输入框中输入公式“=COUNTIF（$D:$D，D2）>1”。它的用途是计算D列单元格中的数据是否与D2相同，再进行比较以确定这个结果是否大于1（为“真”）。如果计算结果大于1（即存在相同的身份证号码），就应用右边设置的条件格式，否则保持单元格的格式不变。
第二步是设置比较结果为“真”时应用的条件格式，方法是单击“格式”按钮打开如图6所示对话框，在“颜色”下拉列表选中条件为“真”时显示的字体颜色（例如红色）。也可以根据需要选择其它字形或选中“删除线”，连续两次单击“确定”按钮将打开的对话框关闭。
第三步是将D2单元格中的条件格式应用于D列的其它单元格，方法是选中D2单元格单击工具栏的“复制”按钮。再选中D列中需要应用条件格式的区域（例如D3：D80区域），单击“编辑”菜单中的“选择性粘贴”命令，打开对话框选中“格式”单击“确定”，那么D列中存在的重复数据就会显示前面设置的条件格式，例如用红色带删除线的字体身份证号码。
如果只想看出D列中重复录入的身份证号码，那么应当怎样操作？可以将如图5所示对话框中的公式修改为“=COUNTIF（$D$2:$D2，D2）>1”。这个公式中对数据区域结束单元格（即“$D2”）的引用会随它向下复制的过程而变化，例如格式复制到D3单元格后公式变为“=COUNTIF（$D$2:$D3，D3）>1”。因此查找是否存在重复数据时，公式进行比较的范围是从当前单元格向上直到数据区域第一个单元格（即“$D2”），所以比较的结果只对第一个以后的相同名称的数据应用条件格式。
二．隐藏信息充分“挖掘”
将姓名和身份证号码输入如图1所示的工作表以后，设计的公式马上从身份证号码中“挖掘”出了信息。其基本原理是： 1．性别
根据现行居民身份证号码的编码规定，18位身份证编码的1~6位为归属地（公民第一次申领身份证时常住户口所在地的行政区划，下同）代码，7~10位为4位的出生年份，11~12位为出生月份，13~14位为出生日期，17位（也就是倒数第二位）为性别（奇数为男，偶数为女），18位（也就是最后一位）则是校验位。而早期使用的是15位的身份证编码，它的1~6位为归属地代码，7~8位是2位的出生年份，9~10位为出生月份，11~12位为出生日期，15位（也就是最后一位）是性别（奇数为男，偶数为女）。
为了适应上述情况，性别计算公式要能够适应两种身份证号码的，使用时只须在C2单元格输入“=IF（LEN（D3）=15，IF（MOD（MID（D3，15，1），2）=1，”男“，”女“），IF（MOD（MID（D3，17，1），2）=1，”男“，”女“））”。回车即可得到D2单元格中存储的身份证号码的性别，而后只要把公式复制（选中D2单元格，鼠标指向单元格右下角然后向下拖动）到D3、D4等单元格，即可“挖掘”出其他身份证号码中的“性别”。
上述公式是怎样的工作的？该公式由三个IF函数构成，其中“IF（MOD（MID（D2，15，1），2）=1，”男“，”女“）”和“IF（MOD（MID（D2，17，1），2）=1，”男“，”女“）”作为第一个函数的参数。公式中的“LEN（D2）=15”是一个逻辑判断语句，LEN函数提取D2、D3等单元格中的字符长度，如果该字符的长度等于15，则执行参数中的第一个IF函数，否则就执行第二个IF函数。
在参数IF（MOD（MID（D2，15，1），2）=1，”男“，”女“）中。MID函数从D2的指定位置（第15位）提取1个字符，而MOD函数将该字符与2相除，获取两者的余数。如果两者能够除尽，说明提取出来的字符是0（否则就是1）。逻辑条件MOD（MID（D2，15，1），2）=1不成立，这时就会在D2单元格中填入”女“，反之则会填入”男“。
如果LEN函数提取的D2等单元格中的字符长度不等于15，则会执行第2个IF函数。除了MID函数从D2的指定位置（第17位，即倒数第2位）提取1个字符以外，其它运算过程与上面的介绍相同。
2．生日
仔细看看E2单元格中的公式“=IF（LEN（D2）=15，CONCATENATE（”19“，MID（D2，7，2），”年“，MID（D2，9，2），”月“，MID（D2，11，2），”日“），CONCATENATE（MID（D2，7，4），”年“，MID（D2，11，2），”月“，MID（D2，13，2），”日“））”
上面这个公式中的“LEN（D2）=15”仍然是逻辑判断语句，它可以判断身份证号码是15位的还是18位，从而调用相应的计算语句。对15位的身份证号码来说，左起第7至12个字符表示出生年月日，此时可以使用MID函数从身份证号码的特定位置，分别提取出生年、月、日。然后用CONCATENATE函数将提取出来的文字合并起来，就能得到对应的出生年月日。公式中”19“是针对早期身份证号码中存在2000年问题设计的，它可以在计算出来的出生年份前加上19。对18位的身份证号码的计算思路相同，只是它不存在2000年问题，公式中不用给计算出来的出生年份前加上19。
3．年龄
出生日期计算出来以后很容易得到“当前年龄”，在G2单元格中输入公式“=YEAR（TODAY（））-YEAR（F2）”。由于F2单元格中存储着上面计算出来“出生日期”（例如“1982年03月21日”），若TODAY（）函数返回系统当前日期为“2006年3月1日”，那么G2单元格中计算出来的年龄就是24岁。
上述公式计算出来的是“虚岁”，即每过一个元旦，年龄就增加一岁。“难题”：计算“实岁”，即生日过后满12个月，年龄再增加一岁的公式怎样设计？可以在H2单元格输入“=TRUNC（（G2-TODAY（））/365）”。公式中的“G2-TODAY（）”计算系统当前时间与“出生日期”相差的天数，“（G2-TODAY（））/365）”计算出两个日期之间相差的年数（小数）。最后使用TRUNC去掉结果的小数部分，得到两个日期之间相差的整数（即“实岁”）。
4．籍贯
从身份证号码中“挖掘”籍贯信息是比较复杂的一项工作，身份证号码的第l至6位数是归属地代码，其中前两位是省、直辖市或自治区的代码。例如“北京市居民身份证”的前两位是“11”，“新疆维吾尔自治区居民身份证”的前两位是“65”等。出于保留资源等方面的考虑，各省、直辖市或自治区居民身份证的归属地代码不是连续的，例如“北京”、“天津”、“河北”、“山西”和“内蒙古”五个省区市的归属地代码是从11到15，而“辽宁”、“吉林”、“黑龙江”三个省的归属地代码却是从21到23。因此小张设计的“挖掘”籍贯信息的公式就显得比较长了，使用时首先在E2单元格输入下面的公式，回车以后即可得到D2单元格中存储的归属地信息。再将公式复制到E3、E4等单元格，即可得到对应D3、D4等单元格的身分证归属地（籍贯）信息。
=IF（MID（D2，1，2）<=”15“，CHOOSE（MID（D2，1，2）-10，”北京“，”天津“，”河北“，”山西“，”内蒙古“），IF（MID（D2，1，2）<=”23“，CHOOSE（MID（D2，1，2）-20，”辽宁“，”吉林“，”黑龙江“），IF（MID（D2，1，2）<=”37“，CHOOSE（MID（D2，1，2）-30，”上海“，”江苏“，”浙江“，”安徽“，”福建“，”江西“，”山东“），IF（MID（D2，1，2）<=”46“，CHOOSE（MID（D2，1，2）-40，”河南“，”湖北“，”湖南“，”广东“，”广西“，”海南“），IF（MID（D2，1，2）<=”54“，CHOOSE（MID（D2，1，2）-49，”重庆“，”四川“，”贵州“，”云南“，”西藏“），CHOOSE（MID（D2，1，2）-60，”陕西“，”甘肃“，”青海“，”宁夏“，”新疆“））））））
上面这个公式的结构比较复杂，公式一共使用了五个嵌套的IF函数，第一个IF函数中的逻辑判断式“MID（D2，1，2）<=”15“”用来判断身份证归属地代码是否在11到15之间。如果这个逻辑判断式成立，那么公式就执行参数“CHOOSE（MID（D2，1，2）-10，”北京“，”天津“，”河北“，”山西“，”内蒙古“）”。其中“（MID（D2，1，2）”返回身份证归属地（省市区）的代码（即身份证号码的前两位），如果（MID（D2，1，2）返回的结果是11（即北京市的代码），那么“MID（D2，1，2）-10”返回供CHOOSE函数使用的索引号。当“MID（D2，1，2）-10”的结果是“1”时，CHOOSE函数就会返回后面参数表中的身份证归属地“北京”。如果第一个IF函数中的逻辑判断式“MID（D2，1，2）<=”15“”不成立，公式就执行的第二个IF函数，它的结构和第一个IF函数完全相同。但是逻辑判断式“MID（D2，1，2）<=”15“”中的条件必须修改，由于“辽宁”、“吉林”、“黑龙江”三个省的归属地代码的最大值是23，所以这个逻辑判断式改为“MID（D2，1，2）<=”23“”即可。当这个逻辑判断式得出“真”或“假”的结果以后，第二个IF函数要么执行“CHOOSE（MID（D2，1，2）-20，”辽宁“，”吉林“，”黑龙江"）”，要么执行第三个IF函数，就这样依次执行直至获得正确结果。
三．身份证号码验证
上面的工作完成之后，设计的公式好是好，但是怎么知道某个身份证号码的真假？
1．验证网站
使用身份证号码验证网站和工具就可以了，在IE地址栏输入“http://”（中国居民身份证升级换代|中国居民身份证验证查询）。该网站的主页如图7所示，只要打开它输入15位身份证号码，单击“查询”即可得到需要的结果。
2．验证软件
如果不能上网该怎么办？使用身份证号码验证软件如“身份证信息解读7.5”（http://hbcrc.onlinedown.net:82/down/sfz75.rar）。该软件是一款“绿色软件”，将下载得到的压缩包释放到某个文件夹，执行其中的“身份证信息解读.exe”就可以打开如图8所示界面。它可以查询身份证持有人的各种信息，例如身份证归属地、出生日期和性别，能够校验身份证号码的真实性。
第三篇：身份证相关信息
一、身份证号码执行标准：18位身份证标准在国家质量技术监督局于1999年7月1日实施的GB11643-1999《公民身份号码》。
GB11643-1999《公民身份号码》为GB11643-1989《社会保障号码》的修订版，其中指出将原标准名称“社会保障号码”更名为“公民身份号码”，另外GB11643-1999《公民身份号码》从实施之日起代替GB11643-1989。
二、编码规则：公民身份号码是特征组合码，由十七位数字本体码和一位校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位校验码，可以用字母表示如为ABCDEFYYYYMMDDXXXR。其含义如下：
1.地址码（ABCDEF）：表示编码对象常住户口所在县(市、旗、区)的行政区划代码，按GB/T2260的规定执行。
2.出生日期码（YYYYMMDD）：表示编码对象出生的年、月、日，按GB/T7408的规定执行，年、月、日分别用4位、2位（不足两位加0）、2（同上）位数字表示，之间不用分隔符。
3.顺序码（XXX）：表示在同一地址码所标识的区域范围内，对同年、同月、同日出生的人编定的顺序号，顺序码的奇数分配给男性，偶数分配给女性。
4．校验码（R），一位数字，通过前17位数字根据一定计算得出。
三、关于地址码含义的详细解释：
身份证前六位是地区代码，我们用ABCDEF表示。代码的解释规则如下：
A:国内区域
华北三省二市
东北三省
华东六省一市
华南六省
西南四省一市 6 西北五省
台湾
港澳
B（或者说是AB，就是前2位）:省（直辖市，自治区，特别行政区）代码
按照A划定的分区定义省代码，有直辖市的，直辖市列前，其余按离直辖市的距离排序，没有直辖市的，按离北京的远近排序。
具体省（直辖市，自治区，特别行政区）代码如下：
11-15 京 津 冀 晋 蒙
21-23 辽 吉 黑
31-37 沪 苏 浙 皖 闽 赣 鲁
41-46 豫 鄂 湘 粤 桂 琼
50-54 渝 川 贵 云 藏
61-65 陕 甘 青 宁 新
81-82 港 澳
CD:城市代码
从01开始排，对于直辖市，CD=01表示市辖区，CD=02表示辖县；省的城市代码从省会开始排，比如2101=沈阳 2102=大连„„ 只有地级城市有独立的城市代码，县级市没有。
EF:市辖区、郊区、郊县、县级市代码
如果EF=00，指代这个城市，不特定区县；对于非直辖市，如EF=01，指代市辖区（任意一个区），02开始指代特定的区。
其中：
E=0代表市辖区，E=1代表郊区，E=2代表郊县，E=8代表县级市。
对于直辖市，从01开始就依次排区，没有市区和郊区的代码区分。：
有关更详细的资料见附录。
四、生日期码（YYYYMMDD）表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符。例如：1981年05月11日就用19810511表示。
五、顺序码（XXX）（身份证第十五位到十七位）是县、区级政府所辖派出所的分配码，每个派出所分配码为10个连续号码，例如“000-009”或“060-069”，其中单数为男性分配码，双数为女性分配码，如遇同年同月同日有两人以上时顺延第二、第三、第四、第五个分配码。如：007的就是个男生，而且和他同年月日生的男生至少有两个，他们的后四位是001*和003*。分配顺序码中“999、998、997、996”四个顺序号分别为男女性百岁以上老人专用的特定编号。
六、关于校验码
校验码是通过一系列数学计算得出来的，具体校验的计算方式如下：
1.对前17位数字本体码加权求和
公式为：S = Sum(Ai * Wi), i = 0,..., 16
其中Ai表示第i位置上的身份证号码数字值，Wi表示第i位置上的加权因子，其各位对应的值依次为： 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2
通俗解释：身份证第一位数字X7+第二位X9+第三位X10+第四位X5+第五位X5+第六位X8+第七位X4+第八位X1+第九位X6+第十位X3+十一位X7+十二位X9+十三位X10+十四位X5+十五位X8+十六位X4+十七位X2；计算出总和（用S）表示。2.以11对计算结果取模
Y = mod(S, 11)
通俗解释：用S除以11，看最后的余数。如果除尽，为0；余数为1，则计为1；最大余数为10；全部数字为0-10共11个数字。（用Y表示）。
3.根据模的值得到对应的校验码
对应关系为：
Y值： 0 1 2 3 4 5 6 7 8 9 10
校验码： 1 0 X 9 8 7 6 5 4 3 2
通俗解释：余数为0，则校验码为1；依次类推：余数为1，则校验码对应0；以下：2--X；3--9；4--8；5--7；6--6；7--5；8--4；9-3；10-2。
如果校验码不符合这个规则，则肯定是假号码。
关于18位身份证号码尾数是“X”的一种解释：因为按照上面的规则，校验码有11个，而不是10个，所以不能用0-9表示。所以如果尾号是10，那么就得用X来代替，因为如果用10做尾号，那么此人的身份证就变成了19位，而19位的号码违反了国家标准，并且我国的计算机应用系统也不承认19位的身份证号码。Ⅹ是罗马数字的10，用X来代替10，可以保证公民的身份证符合国家标准。
根据〖中华人民共和国国家标准 GB 11643-1999〗中有关公民身份号码的规定，公民身份号码是特征组合码，由十七位数字本体码和一位数字校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。
第四篇：如何通过函数提取身份证中的出生日期
如何通过函数提取身份证中的出生日期
某公司要改善公司福利，决定在每一个员工过生日时送一个深日蛋糕，为此公司经理要求人事部主管将员工的生日统计下来。如果通过逐一询问员工生日的方式来统计将非常麻烦，此时该主管可以在已有员工身份证信息的基础上，通过“CONCATENATE”函数来提取员工的生日日期。具体的操作步骤如下。
①选中要输入公式的区域，在编辑栏中输入“＝CONCATENATE(MID(F1,7,4),“年”,MID(F1,11,2),“月”,MID(F1,13,2),“日”)”。
②按下【Ctrl】＋【Enter】组合键即可将所有身分证中的出生日期提取出来。
“CONCATENATE”函数的操作对象必须是文本，它的作用就是将几个文本字符串合并为一个文本字符串。“MID(F1,7,4)”代表的是在单元格F1中从第七位开始向右取两个字符，CONCATENATE(MID(F1,7,4),“年”,MID(F1,11,2),“月”,MID(F1,13,2),“日”)代表的是将提取出的字符串与“年”、“月”、“日”合并为一个字符串。
第五篇：Excel输入出生日期、年龄自动计算、身份证提取日期、统计
Excel输入出生日期、年龄自动计算、身份证提取日期、统计
背景：统计在校生需要输入出生年月日，改动出生年月日，统计年龄，从身份证提取年龄等。网上的介绍涉及格式又数字又文本的，有的输入时还自动变成1905或2441年等，很烦恼，而这次总结的办法不会出现上述问题，都是在格式为数字格式的情况计算的，只是涉及小数位数和是否自动插入小数点等问题。
常用公式：
1、=DATEDIF(E4,TODAY(),“y”)
（计算一个学生今年的岁数）
2、=2006-year(d1)(计算一个学生不同学的岁数）
3、择取单元格前面的几位数=LEFT(C15,4）取C15中前4个字符就是你要的年份了
4、提取指定位置，指定长度的字符串
=MID（A2，7,8）
即 =MID（要提取的单元格，起始位数，提取长度）
5、对如：79.1-197901、80.10-198010、2001.1-200101、2001.12-200112都适用。
方案： 找一空闲列辅助，比如原来的出生年月列在A列，E列为空闲列，在E1中输入
=IF(LEN($A1)<4“错误”,CHOOSE(LEN($A1)-3,“19”&LEFT($A1,2)&“0”&RIGHT($A1,1),“19”&LEFT($A1,2)&RIGHT($A1,2),LEFT($A1,4)&“0”&RIGHT($A1,1),LEFT($A1,4)&RIGHT($A1,2)))
6、假设你A1输入的是出生年月B1输入=DATEDIF(A1,today(),“y”)但是这是指你的A1是输入的标准日期，如果输入的是文本格式的那B1输入=year(today())-left(a1,4)
综上所述，总结了两种极为有效的方法，其实原理是一种而已： 方法一：
1、打开excel 2007或2003，出生年月处数据自动插入小数点，格式为数字并保留两位小数，分隔符为“-”
2、提取前四位字符，即出生年份 =LEFT(A2,4）
3、打开2003或另一个2007程序，粘贴，格式为文本格式。
4、计算年龄： =2013-A2(年份所在单元格），拖动年龄列即可
方法二：根据第一种方法总结的经典法
1、打开excel 2007或2003，出生日期处，格式为数字，保留小数两位，高级选项里自动插入小数点两位，分隔符为“-”
2、停止excel自动插入小数点。
3、把年龄单元格改为数值并不保留小数点，输入公式 =year(today())-left(a1,4)即用今年减去年龄单元格的前四位整数，即为年龄。然后拖动年龄列即可。
4、更换学时，学生的年龄是发生变化的，只需改动年龄位置的公式，如1976年2013该学生是37岁，那么2012时该学生应该是36岁，所以公式应该是 =year(today())-left(a1,4)-1，注意减1即可。若是2000，则=year(today())-left(a1,4)-13即可，即为该学生2000年时的岁数。
点击咨询 