用Excel自动解读身份证号码信息的方法
一个身份证号，用Excel能提取多少信息？（含批量校对错误）
一个身份证号，用Excel能提取多少信息？（含批量校对错误）每个人都有身份证号码。
作为疾控人的你，知道从身份证号码上可以提取多少信息吗？那么打开你的excel，手把手的交给你如何批量提取有效信息。
相关理论基础详见《如何10秒校对1万个身份证号有无错误？》第一部分批量提取出生省份/地市/区县民政部官网上有2020年12月中华人民共和国县以上行政区划代码，上面的六位代码就是我们身份证号的前六位，可以通过mid函数+vlookup函数+数组实现自动提取出生省份查询，在C2中输入=VLOOKUP(MID(B2,1,2)*1,{11,'北京市';12,'天津市';13,'河北省';14,'山西省';15,'内蒙古自治区';21,'辽宁省';22,'吉林省';23,'黑龙江省';31,'上海市';32,'江苏省';33,'浙江省';34,'安徽省';35,'福建省';36,'江西省';37,'山东省';41,'河南省';42,'湖北省';43,'湖南省';44,'广东省';45,'广西壮族自治区';46,'海南省';50,'重庆市';51,'四川省';52,'贵州省';53,'云南省';54,'西藏自治区';61,'陕西省';62,'甘肃省';63,'青海省';64,'宁夏回族自治区';65,'新疆维吾尔自治区';71,'台湾省';81,'香港特别行政区';82,'澳门特别行政区'},2,0)其中MID(B2,1,2)*1是因为MID(B2,1,2)提取的是文本值，乘1之后可以转为数值。
给你5个实用Excel函数公式搞定盘踞在Excel表格中的身份证
给你5个实用Excel函数公式搞定盘踞在Excel表格中的身份证在工作中，将身份证输入表格的大多是财务或是HR，本以为只要输入数字就可以了，可是，当大家做这事儿的时候，有多少人发现，这个身份证真的很抽风……如果你正做着有关工作，那么就一起来看一下以下内容吧，让你的Excel操作轻松碾压各种有关身份证的信息。
以下是一组和身份证有关的函数公式，如果你是财务人员或是一名HR，那就一起来看一下啦：1、查找重复身份证号码在Excel表格上方输入【=IF(COUNTIF(A:A,A1&"*")>1,"重复","")】，其中“A1”可以自定义修改。
说明：COUNTIF函数在计算文本型数字时，会默认按数值型进行处理，但Excel中的数字精度只有15位，但身份证号码是18位，因此会把前15位相同的身份证全部识别为相同的内容。
在第二参数后加上一个星号 &"*"，就是告诉Excel，要查找包含A1单元格内容的文本，通过这样变通的手段，COUTNIF函数就会乖乖听话啦。
2、出生年月来可计算年龄在Excel表格上方输入【=DATEDIF(A4,TODAY(),"y")】，然后按回车键，即可在其中“A4”可以自定义修改。
说明：DATEDIF函数用于计算两个日期之间的间隔。
=DATEDIF（开始日期,结束日期,指定要返回的类型)；第三参数Y表示年，M则表示月。
TODAY函数返回系统当前的日期。
DATEDIF函数是隐藏函数，输入时没有屏幕提示，需要手工录入。
3、根据身份证号计算出生年月在Excel表格上方输入【=--TEXT(MID(A2,7,8),"0!/00!/00")】，然后按回车键，即可在其中“A2”可以自定义修改。
说明：身份证号码中的第8位到15位是出生年月信息。
先用MID函数从A2单元格的第7位开始，提取出8个字符，即可得到身份证号中的出生年月日信息。
(完整word版)如何用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位数不符。
如何在Excel中填写身份证号码后自动提取出生年月日、年龄、性别
如何在Excel中填写身份证号码后自动提取出生年月日、年龄、性别本公式只适合于18位身份证号码即二代居民身份证。
假如：A1输入身份证号码、B1显示性别、C1显示出生日期、D1显示周岁。
（要注意A1、B1、C1、D1分别对应A列1行B列1行C列1行D列1行，如你的A1单元格不为你的表格第一项身份证编号则需将A1替换成你选取的单元格）需要设置：A1单元格为文本格式、B1和D1为常规格式、C1为日期格式。
（选中列右键设置单元格格式可选取格式）1、显示性别：B1输入公式：=IF(A1="","",IF(AND(LEN(A1)<>15,LEN(A1)<>18),"错误",IF(ISERROR(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"))),"错误",IF(OR((1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))<VALUE ("1905-01-01"),(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))> TODAY()),"错误",IF(MOD(MID(A1,15+(LEN(A1)=18)*2,1),2)=0,"女","男")))))2、C1输入公式：=IF(A1="","",IF(AND(LEN(A1)<>15,LEN(A1)<>18),"错误",IF(ISERROR(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"))),"错误",IF(OR((1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))<VALUE ("1905-01-01"),(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))> TODAY()),"错误",VALUE(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#年00月00日"))))))3、D1输入公式：=IF(ISERROR(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"))),"错误",IF(OR((1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))<VALUE ("1905-01-01"),(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")))> TODAY()),"错误",DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),TODAY(), "y")))注意：A1、B1、C1、D1分别对应A列1行B列1行C列1行D列1行，如你的A1单元格不为你的表格第一项身份证编号则需将A1替换成你选取的单元格如下图那么上述B1、C1、D1输入函数中应将A1替换成F4即可。
利用Excel从身份证号中提取出生年月和年龄等信息
利用Excel从身份证号中提取出生年月和年龄等信息利用Excel从身份证号中提取出生年月和年龄等信息一、分析身份证号码其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。
15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。
18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。
例如，某员工的身份证号码(15位)是320521*********，那么表示1972年8月7日出生，性别为女。
如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。
二、提取个人信息这里，我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息。
如图1所示，其中员工的身份证号码信息已输入完毕(C列)，出生年月信息填写在D列，性别信息填写在B列。
图1 输入身份证号码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位数字。
让EXCEL自动提取身份证号码中的个人信息
让EXCEL⾃动提取⾝份证号码中的个⼈信息
让EXCEL⾃动提取⾝份证号码中的个⼈信息
张朝辉;魏涛
【摘要】在⽇常办⼯中我们常常需要往表格中输⼊⼈员的姓名、性别、⾝份证号码、出⽣年⽉、籍贯、出⽣地等信息,如果数据量⼤,则是很费事的.其实,让功能强⼤的Excel来做这些事是毫不费⼒的.我们知道,⾝份证号码与⼀个⼈的性别、出⽣年⽉、出⽣地等信息密切相关,⽆论是15位的⾝份证还是18位的⾝份证,其中都保存了相关的个⼈信息.因此,根据在⾝份证号码中包含着持证⼈的最基本信息的特点,如在15位（18位）⾝份证号码中,第7⾄12位（14位）号码代表持证⼈的出⽣⽇期,第15位（17位）数字代表持证⼈的性别,奇数为男,偶数为⼥.于是我们就可以利⽤Excel中⾃带的强⼤的函数功能来提取出⽣⽇期、性别、居住地等信息,这对于我们⽇常繁杂的⼯作来说是⾮常有⽤的.当然也可以利⽤Excel的⾃定义函数功能,定制⼀个提取出⽣⽇期和判断性别的函数,然后再结合其他函数（如⽤VLOOKUP函数对持证⼈的居住地做出判断）,就可以让Excel⾃动从⾝份证号码中提取你需要的相关信息.这⾥以Excel⾃带的函数和⽂本操作函数来说明如何从⾝份证号码中提取出⽣⽇期和性别.
【期刊名称】《办公⾃动化（综合版）》
【年(卷),期】2005(000)011
【总页数】2页(P55-56)
【关键词】⾝份证号码;⾃动提取;个⼈信息;EXCEL;VLOOKUP函数;Excel;函数功能;基本信息;操作函数;相关信息
【作者】张朝辉;魏涛。
用Excel从身份证号提取地区，生日，性别，年龄，生肖，星座等
用Excel从身份证号提取地区，生日，性别，年龄，生肖，星座等身份证号非常重要，里面隐藏了很多信息，这些信息发生泄露，就很危险，今天我们用Excel来提取身份证号里面的各种信息1、从身份证中提取地区，省市县身份证号的前6位中就隐藏着这些信息，1-2位表示省，第3-4位表示市，第5-6位表示县，我们首先下载一份身份证地区码然后我们对A列的身份证号进行提取数字，然后进行查找匹配地区表在B2查找省份输入公式：=VLOOKUP(LEFT($A2,2)&'*',身份证地区码!$A:$B,2,0)在C2查找城市输入公式：=VLOOKUP(LEFT($A2,4)&'*',身份证地区码!$A:$B,2,0)在D2查找区县输入公式：=VLOOKUP(LEFT($A2,6),身份证地区码!$A:$B,2,0)身份证号是模拟的，倒数34位用X隐藏2、提取生日身份证号的第7位至第13位是表格出生的年月日，在E2单元格中输入公式：=TEXT(MID(A2,7,8),'0-00-00')3、提取年龄已经把出生年月日提取出来了，我们只需要使用=DATEDIF(E2,TODAY(),'Y')来计算年龄4、提取性别身份证号的倒数第2位，也就是第17位，如果是奇数，那么为男性，如果为偶数，则为女性，所以在G2单元格中输入公式：=IF(MOD(MID(A2,17,1),2),'男','女')5、提取生肖每12年生肖进行一次的轮换，所以使用公式：=MID('猴鸡狗猪鼠牛虎兔龙蛇马羊”',MOD(MID(A2,7,4),12)+1,1)6、提取星座首先我们要知道星座的时间区间如下所示：根据上面的规则，我们在Excel里面K:L列建立辅助项，如下所示：然后我们再使用VLOOKUP函数的模糊查找功能，输入公式：=VLOOKUP(MID(A2,11,4)*1,K:L,2,1)如果不想有辅助列，那么数据中间的数据源K:L可以使用一个数组来替换输入的公式是：=VLOOKUP(MID(A2,11,4)*1,{0,'摩羯座';120,'水瓶座';219,'双鱼座';321,'白羊座';420,'金牛座';521,'双子座';622,'巨蟹座';723,'狮子座';823,'处女座';923,'天秤座';1024,'天蝎座';1123,'射手座';1222,'摩羯座'},2,1)所以仅仅根据一个身份证号，比如：33018319931224XX41，就可以知道这个人是浙江杭州市富阳区的25岁的女生，属鸡，摩羯座，生日是1993年12月24号。
Excel解读身份证号码信息
Excel解读身份证号码信息利用Excel自动解读身份证号码信息的方法。
这个自动解读表可以将身份证表格中的出生日期、性别自动解读出来。
如果您愿意，当然还可以用挖掘出来的信息做更多的数据统计和计算。
众所周知，15位（18位）身份证号码的第7—12位（14位）数字代表着持证人的出生年、月、日；第15位（17位）数字代表着持证人的性别（奇数为“男”，偶数为“女”）。
下面，我们就利用Excel强大的函数功能，从身份证号码中将这些信息挖掘出来，显示在相应的单元格中。
本例涉及到的Excel函数如下：1、DATE(year,month,day)2、IF(Logical,Value_if_true,Value_if_false)3、LEN(text)4、MID(text,start_num,num_chars)5、MOD(number,divisor)在这里，我们假定身份证号码保存在C列（从C2单元格开始）中，性别和出生年、月、日分别保存在D列和E列中。
一、性别的判断选中D2单元格，输入公式：=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=0,"女","男")或=IF(MOD(IF(LEN(C7)=15,MID(C7,15,1),MID(C7,17,1)),2)=1,"男","女")，输入完成后，按下“Enter”键进行确认，第1位持证人的性别则自动显示在D2单元格中。
上述第一个公式的含义分三层来理解：①如果身份证号码是15位（LEN(C2)=15），则取其15位数字（MID(C2,15,1)），否则取其17位数字（MID(C2,17,1)）。
②然后求取出来的数值除2后的余数（MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)）。
③如果（IF）余数为“0”（MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=0），则显示“女”，否则显示“男”。
Excel函数技巧：巧用Excel函数公式检验身份证号码
Excel函数技巧：巧用Excel函数公式检验身份证号码小编有话说：小编还是第一次了解到身份证的编码规则呢，原来并不复杂，根据这个编码规则，我们自己也可以辨别身份证的真假，太牛逼了！不过说真的，要是公司员工的身份证号码写错了，而我们还没发现，真的会给公司带来很大的麻烦，所以今天我们就来和大家分享一个核查身份证号码是否有错误的公式。
一般来说，身份证号码会出现两类错误，长度错误和内容错误。
出现错误的原因大多数都输入过程中发生的，对于长度错误来说，目前都是18位的号码，如果长度出现错误，比较容易发现，但是如果其中的某一个数字出现错误，那就非常难以发现，以后就有可能会造成一些严重的后果，因此，今天重点就是核查内容的正确性。
一、身份证号组成原理首先需要了解一点关于身份证号码的编码规则：18位身份证号码组成：ddddddyyyymmddxxsp共18位，其中：1-6位是地区编码，7-14为出生日期，15-17这三位是性别代码，最后一位是根据前面的17个数字得到的校验位。
校验位的计算规则比较复杂：（1）前十七位数字本体码加权求和公式S = Sum(Ai * Wi), i = 0, ... , 16，先对前17位数字的权求和Ai:表示第i位置上的身份证号码数字值Wi:表示第i位置上的加权因子Wi: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2（2）计算模Y = mod(S, 11)（3）通过模得到对应的校验码Y: 0 1 2 3 4 5 6 7 8 9 10校验码: 1 0 X 9 8 7 6 5 4 3 2要检验一个身份证号码是否有误，原理就是按照这个规则计算出校验码，再与最后一位数字去比较，如果不一致就是有问题了。
对于这个规则理解起来还是比较费劲的，我们举个例子来看看：1、将前面的身份证号码17位数分别乘以不同的系数。
从第一位到第十七位的系数分别为：7－9－10－5－8－4－2－1－6－3－7－9－10－5－8－4－2。
4、EXCEL：身份证号码包含的信息
EXCEL：身份证号码包含的信息
------微信公众号：OFFICE说
一、工作场景：
提供一个表格，里面仅有身份证号码，现需解决以下问题：
1、因业务需要，须填写对应的性别，出生年月日；
2、已有性别，需要检查表格中填写的性别是否正确；
3、判断身份证所对应的人年龄是否合适。
二、TT教你做：
证件号码所在单元格为A2。
1、身份证号码的长度：
一代身份证号码长度为15位，二代证长度为18位，我们可以使用LEN函数，检查属于哪一代证还是二代证：
=LEN（A2）
2、以18位二代证为例填写性别：
（1）使用MID字符串函数提取身份证号码的顺数第17位，倒数第2位数字；
（2）使用IF如果函数和MOD余数函数判断倒数第二位数字的奇偶性，如偶数为“女”，奇数为“男”。
=IF(MOD(MID(A2,17,1),2)=0,"女","男")
（3）若表格中已有性别，需检查填写是否正确，可使用IF函数把从身份证号码得出的性别与已有的性别进行对比，信息相符则正确，信息不符则错误。
3、以18位二代证为例填写出生年月日：
使用MID字符串函数，取身份证号码顺数第7位开始的8位日期。
=MID(A2，7，8)
4、以18位二代证为例判断是否满16周岁：
使用IF判断函数，把出生年月日（D2）与当前日期进行相比，大于或等于160000则满16周岁，否则则未满16周岁。
=IF(VALUE(20161218-D2)>=160000,"满16周岁","未满16周岁")。
在excel中巧妙运用身份证号码输入身份证号如何做到自动显示姓别_年龄及出生年月日
1、在excel中输入身份证号如何做到自动显示姓别,年龄及出生年月日15位和18位的身份证号都合适的了。
设内容如下：........A列......B列......C列......D列....1 身份证号性别出生年月年龄2（输入身份证号）算年龄的：=IF(OR(LEN(A2)=15,LEN(A2)=18),RIGHT(IF(IF(LEN(A2)=15,MID(A2,9,2),MID(A2,1 1,2))*100+IF(LEN(A2)=15,MID(A2,11,2),MID(A2,13,2))>MONTH(TODAY())*100+DAY (TODAY()),YEAR(TODAY())-IF(LEN(A2)=15,MID(A2,7,2),MID(A2,9,2))-1,YEAR(TOD AY())-IF(LEN(A2)=15,MID(A2,7,2),MID(A2,9,2))),2),"证号错误")算性别的：=IF(LEN(A2)=15,(IF(OR(ABS(RIGHT(A2))=1,ABS(RIGHT(A2))=3,ABS(RIGHT(A2))=5, ABS(RIGHT(A2))=7,ABS(RIGHT(A2))=9),"男","女")),(IF(OR(ABS(MID(A2,17,1))=1,ABS(MID(A2,17,1))=3,ABS(MID(A2,17,1))=5,AB S(MID(A2,17,1))=7,ABS(MID(A2,17,1))=9),"男","女")))算出生年月的：=DATE(IF(LEN(A2)=15,MID(A2,7,2),MID(A2,9,2)),IF(LEN(A2)=15,MID(A2,9,2),MI D(A2,11,2)),IF(LEN(A2)=15,MID(A2,11,2),MID(A2,13,2)))【身份证信息提取】从身份证号码中提取出生年月日=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1=IF(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,) 显示格式均为yyyy-m-d。
在Excel中自动提取身份证中生日和性别
在Excel中自动提取身份证中生日和性别上一页下一页每年新入学的一年级学生，都需要向上级教育部门上报一份包含身份证号、出生年月等内容的电子表格，以备建立全省统一的电子学籍档案。
数百个新生，就得输入数百行相应数据，这可不是个轻松活儿。
有没有什么办法能减轻一下输入工作量、提高一下效率呢?其实，我们只需在Excel中将学生的身份证号完整地输入后，它就可以帮我们自动填好出生日期和性别。
现在学生的身份证号已经全部都是18位的新一代身份证了，里面的数字都是有规律的。
前6位数字是户籍所在地的代码，7-14位就是出生日期。
第17位“2”代表的是性别，偶数为女性，奇数为男性。
我们要做的就是把其中的部分数字想法“提取出来”。
1，转换身份证号码格式我们先将学生的身份证号完整地输入到Excel2003表格中，这时默认为“数字”格式(单元格内显示的是科学记数法的格式)，需要更改一下数字格式。
选中该列中的所有身份证号后，右击鼠标，选择“设置单元格格式”。
在弹出对话框中“数字”标签内的“分类”设为“文本”，然后点击确定。
2，“提取出”出生日期将光标指针放到“出生日期”列的单元格内，这里以C2单元格为例。
然后输入“=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"”(注意：外侧的双引号不用输入，函数式中的引号和逗号等符号应在英文状态下输入)。
回车后，你会发现在C2单元格内已经出现了该学生的出生日期。
然后，选中该单元格后拖动填充柄，其它单元格内就会出现相应的出生日期。
如图1 。
图1 通过上述方法，系统自动获取了出生年月日信息小提示：MID函数是Excel提供的一个“从字符串中提取部分字符”的函数命令，具体使用格式在EXCEL中输入MID后会出现提示。
3，判断性别“男女”选中“性别”列的单元格，如D2。
Excel自动从身份证中提取信息方法
Excel自动从身份证中提取生日、性别、年龄、生肖每年新入学的一年级学生，都需要向上级教育部门上报一份包含身份证号、出生年月等内容的电子表格，以备建立全省统一的电子学籍档案。
数百个新生，就得输入数百行相应数据，这可不是个轻松活儿。
有没有什么办法能减轻一下输入工作量、提高一下效率呢？其实，我们只需在Excel2003中将学生的身份证号完整地输入后，它就可以帮我们自动填好出生日期和性别。
现在学生的身份证号已经全部都是18位的新一代身份证了，里面的数字都是有规律的。
前6位数字是户籍所在地的代码，7－14位就是出生日期。
第17位“2”代表的是性别，偶数为女性，奇数为男性。
我们要做的就是把其中的部分数字想法“提取出来”。
STEP1，转换身份证号码格式我们先将学生的身份证号完整地输入到Excel2003表格中，这时默认为“数字”格式（单元格内显示的是科学记数法的格式），需要更改一下数字格式。
选中该列中的所有身份证号后，右击鼠标，选择“设置单元格格式”。
在弹出对话框中“数字”标签内的“分类”设为“文本”，然后点击确定。
STEP2，“提取出”出生日期将光标指针放到“出生日期”列的单元格内，这里以C2单元格为例。
然后输入“=MID（B2，7，4）&"年"&MID（B2，11，2）&"月"&MID（B2，13，2）&"日"”（注意：外侧的双引号不用输入，函数式中的引号和逗号等符号应在英文状态下输入）。
回车后，你会发现在C2单元格内已经出现了该学生的出生日期。
然后，选中该单元格后拖动填充柄，其它单元格内就会出现相应的出生日期。
如图1。
图1 通过上述方法，系统自动获取了出生年月日信息小提示：MID函数是EXCEL提供的一个“从字符串中提取部分字符”的函数命令，具体使用格式在EXCEL中输入MID后会出现提示。
STEP3，判断性别“男女”选中“性别”列的单元格，如D2。
Excel帮你轻松获取身份证号码隐藏的信息，轻松统计人员基本信息
Excel帮你轻松获取身份证号码隐藏的信息，轻松统计人员基本信息在实际工作中，我们可能会对一些人员的基本信息进行统计，往往我们可能会一项项的输入，其实呢，在Excel中如果我们知道了身份证号，可以利用简单的公式统计出一些具体的个人信息，你比如省份，具体所在地区，出生日期，年龄，性别等。
首先呢，我们先了解一下我国身份证号的编号规律：1-2位代码是指省、自治区、直辖市代码3-4位代码是指地级市、盟、自治州代码5-6位代码是指县、县级市、区代码7-14位代码是指出生年月日15-17位为顺序号，其中第17位如果是奇数指男性，偶数指女性最后一位就是校验码知道这些之后，接下来我们看一下怎样用Excel得到相关信息一、如何得到省份信息。
首先我们需要准备好一份地区代码对照表，这个可以从网上下载，或者是私信我，我发给你们电子版。
将对照表复制到Excel表格中，添加上身份证信息。
然后在省份一栏输入公式。
=VLOOKUP(LEFT(C2,2),A:B,2)解释一下公式：首先LEFT函数是用来对单元格里的内容进行截取选择，从左边第一个字符开始截取，截取指定的长度。
这里的LEFT(C2,2) 就是从C2单元格里面的数值，从左往右截取两位。
VLOOKUP函数就是竖直查找，即列查找。
在指定竖直列区域内查找，找到指定的内容，返回指定列的数据。
这里VLOOKUP(LEFT(C2,2),A:B,2) 就是在A、B列（也就是我们提前准备好的地区代码对照表）查找省份证信息的前两位返回的值是第二列对应的值(这里的第二列就是B列了，不过要注意在使用此函数时，返回第n列的数值不一定对应相应的字母列，这需要我们从选定的区域第一列开始数一下）填完了第一个单元格，鼠标移动到单元格右下角，变成加好时双击或者往下拉就行了。
二、如何得到具体所在地区信息。
公式基本和上面一样，就是利用LEFT函数截取位数时，这次截取前六位的长度。
=VLOOKUP(LEFT(C2,6),A:B,2)三、如何得到出生年月日信息。
EXCEL中如何提取身份证出生日期、性别等
EXCEL中如何提取身份证出生日期、性别、检验身份证号码的正确性（网络收集）2011-09-28 18:24:12| 分类：电脑知识| 标签：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。
Excel表格中身份证号码提取出生年月、性别、年龄等的使用技巧.pdf
Excel表中身份证号码提取出生年月、性别、年龄的使用技巧方法一：1.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数);2.15位身份证号从第7位到第12位是出生年月日，年份用的是2位数。
18位身份证号从第7位到第14位是出生的年月日，年份用的是4位数。
从身份证号码中提取出表示出生年、月、日的数字，用文本函数MI D()可以达到目的。
MID()——从指定位置开始提取指定个数的字符（从左向右）。
对一个身份证号码是15位或是18位进行判断，用逻辑判断函数IF ()和字符个数计算函数LEN()辅助使用可以完成。
综合上述分析，可以通过下述操作，完成形如1978-12-24样式的出生年月日自动提取：假如身份证号数据在A1单元格，在B1单元格中编辑公式=IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2),MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2))回车确认即可。
如果只要“年-月”格式，公式可以修改为=IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2),MID(A1,7,4)&"-"&MID(A1,11,2))3.这是根据身份证号码（15位和18位通用）自动提取性别的自编公式，供需要的朋友参考：说明：公式中的B2是身份证号根据身份证号码求性别：=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女", "男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,1)),2)=0, "女","男"),"身份证错"))根据身份证号码求年龄：=IF(LEN(B2)=15,2007-VALUE(MID(B2,7,2)),if(LEN(B2)=18,2007-VALUE(MID(B2,7,4)),"身份证错"))4.Excel表中用Year\Month\Day函数取相应的年月日数据;方法二：这是根据身份证号码（15位和18位通用）自动提取性别和出生年月的自编公式，供需要的网友参考：说明：公式中的B2是身份证号1、根据身份证号码求性别：=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女", "男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0, "女","男"),"身份证错"))2、根据身份证号码求出生年月：=IF(LEN(B2)=15,CONCATENATE("19",MID(B2,7,2),".",MID(B2,9,2)),IF(LEN(B2)=18,CONCATENATE(MID(B2,7,4),".",MID(B2,11,2)),"身份证错"))3、根据身份证号码求年龄：=IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=18,year(now())-VALUE(MID(B2,7,4)),"身份证错"))早期的身份证号码为15位数字，现在使用的身份证号码为18位数字，它们的含义如下：（1）15位：1-6位为地区代码，7-8位为出生年份（2位），9-10位为出生月份，11-12位为出生日期，第13-15位为顺序号，这3位奇数为男，偶数为女。
利用EXCEL公式鉴别身份证号码 共16页
9
2 EXCEL鉴别公式简介
身份证校验码的计算方法
1、将前面的身份证号码17位数分别乘以不同的系数。第i位对应的数为[2^(18i)]mod11。从第一位到第十七位的系数分别为：7 9 10 5 8 4 2 1 6 3 7 9 10 5 842；
谢谢聆听
15
谢谢你的阅读
知识就是财富 丰富你的人生
IF(LEN(身份证)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(身份证, ROW(INDIRECT("1:17")),1)*2^(18ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(身份证), IF(LEN(身份证)=15,ISNUMBER(--TEXT(19&MID(身份证,7,6),"#-00-00"))))
利用EXCEL公式鉴别 身份证号码正误
1
目录
1 一二代身份证简介 23 EXCEL鉴别公式简介
3
工作相关改善
2
自1984年为中华人民共和国公民颁发的身 份证明性证件，第一阶段采用印刷和照相翻拍技术塑封而成，为聚酯 薄膜密封、单页卡式，15位编码。2019年7月1日起启用新的防伪居 民身份证，采用全息透视塑封套防伪。2019年10月1日起，建立和实 行公民身份号码制度，身份代码是唯一的、终身不变的18位号码。 2019年1月1日，第二代居民身份证开始换发 。
6
目录
1 一二代身份证简介
23 EXCEL鉴别公式简介
3
工作相关改善
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
[编者]前面我们介绍了《Excel常用函数的功能和使用方法》，现在我们学以致用，介绍一系列用这些函数实现的数据统计实例解析。
今天我们介绍用Excel自动解读身份证号码信息的方法。
这个自动解读表可以将身份证表格中的出生日期、性别自动解读出来。
如果您愿意，当然还可以用挖掘出来的信息做更多的数据统计和计算。
文章末尾提供.xls文件供大家下载参考。
众所周知，15位（18位）身份证号码的第7—12位（14位）数字代表着持证人的出生年、月、日；第15位（17位）数字代表着持证人的性别（奇数为“男”，偶数为“女”）。
下面，我们就利用Excel强大的函数功能，从身份证号码中将这些信息挖掘出来，显示在相应的单元格中。
本例涉及到的Excel函数如下：
1、DATE(year,month,day)
2、IF(Logical,Value_if_true,Value_if_false)
3、LEN(text)
4、MID(text,start_num,num_chars)
5、MOD(number,divisor)
在这里，我们假定身份证号码保存在C列（从C2单元格开始）中，性别和出生年、月、日分别保存在D列和E列中。
一、性别的判断
选中D2单元格，输入公式：
=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=0,"女","男")
或
=IF(MOD(IF(LEN(C7)=15,MID(C7,15,1),MID(C7,17,1)),2)=1,"男","女")，
输入完成后，按下“Enter”键进行确认，第1位持证人的性别则自动显示在D2单元格中（如图1）。
上述第一个公式的含义分三层来理解：
①如果身份证号码是15位（LEN(C2)=15），则取其15位数字（MID(C2,15,1)），否则取其17位数字（MID(C2,17,1)）。
②然后求取出来的数值除2后的余数（MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)）。
③如果（IF）余数为“0”（MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=0），则显示“女”，否则显示“男”。
再次选中D2单元格，用“填充柄”将上述公式复制到D列下面的单元格中，即可显示出其他持证人的性别。
注意：如果将上述公式修改为：=IF(IF(LEN(C2)=15,MID(C2,15,1),IF(LEN(C2)=18,MID(C2,17,1),""))="","号码有错",IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),IF(LEN(C2)=18,MID(C2,17,1),"")),2)=1,"男","女"))，当输入的号码不是15位或18位，系统提示“号码有错”（参见图1），则效果更佳。
二、显示出生年、月、日
选中E2单元格，输入公式：
=IF(LEN(C2)=15,DATE(MID(C2,7,2),MID(C2,9,2),MID(C2,11,2)),DATE(MID(C2,7,4),MID(C2,11,2),MID(C2 ,13,2)))，输入完成后，按下“Enter”键进行确认，第1位持证人的出生年、月、日则自动显示在E2单元格中（参见图1）。
上述公式的含义如下：
如果（IF）身份证号码是15位（LEN(C2)=15），则取其7至8位（MID(C2,7,2)）、9至10位（MID(C2,9,2)）、11至12位（MID(C2,11,2)），并以日期的格式显示出来（DATE(MID(C2,7,2),MID(C2,9,2),MID(C2,11,2))）；
反之，取其7至10位（MID(C2,7,4)）、11至12位（MID(C2,11,2)）、13至14位（MID(C2,13,2)），再以日期的格式显示出来（DATE(MID(C2,7,4),MID(C2,11,2),MID(C2,13,2))）。
再次选中E2单元格，用“填充柄”将上述公式复制到E列下面的单元格中，即可显示出其他持证人的出生年、月、日。
选中E列中含有日期的单元格区域，执行“格式→单元格”命令，打开“单元格格式”对话框（如图2），切换到“格式”标签中（通常是默认的标签），先在“格式类别”下面选中“日期”选项，然后在“示例”下面选中一种适合国人习惯的日期样式，确定返回。
注意：①如果不设置单元格格式，也是完全可以的，只不过显示出来的日期样式是“1976-12-17”。
②如果将上述公式修改为：
=IF(LEN(C2)=15,DATE(MID(C2,7,2),MID(C2,9,2),MID(C2,11,2)),IF(LEN(C2)=18,DATE(MID(C2,7,4),MID(
C2,11,2),MID(C2,13,2)),"号码有错"))，当输入的号码不是15位或18位，系统提示“号码有错”（参见图1），则效果更佳。
注意：修改后的公式与上面的公式含义相似。
.xls文件下载
注意：修改后的公式与上面的公式含义相似。
[编者]前面我们介绍了《Excel常用函数的功能和使用方法》，现在我们学以致用，介绍一系列用这些函数实现的数据统计实例解析。
今天我们介绍用Excel自动提取“员工基本情况登记表”中的信息，生成并打印员工简历表。
朋友在做人事工作，常常要打印指定员工的简历表，由于“员工基本情况登记表”是用Excel制作的，我们可以用Excel来自动提取“员工基本情况登记表”中的信息，生成并打印员工简历表，十分方便。
文章末尾提供.xls文件供大家下载参考。
本文涉及的Excel函数如下，点击可查看详细信息：
1、IF (Logical,Value_if_true,Value_if_false)
2、ISERROR (value)
3、VLOOKUP (lookup_value,table_array,col_index_num,range_lookup)
应用过程：
1、启动Excel2003（其他版本请仿照操作），打开“员工基本情况登记表”工作簿（如图1）。
2、切换到Sheet2工作表中，仿照图2的样式制作好简历表的框架。
3、分别选中需要填写内容的单元格（除B2单元格外，如D2、F2等），输入公式：
=IF(ISERROR(VLOOKUP(B2,简历!A3:K12,*,FALSE)),"",VLOOKUP(B2,简历!A3:K12,*,FALSE))。
注意：①上述公式中的“*”号，表示“简历”表中，相应的数据所对应的列数：如“出生时间”在第“5”列（参见图1），在J2单元格中输入公式时，将“*”修改为“5”，即：=IF(ISERROR(VLOOKUP(B2,简历!A3:K12,5,FALSE)),"",VLOOKUP(B2,简历!A3:K12,5,FALSE))。
②公式的含义是：如果公式“VLOOKUP(B2,简历!A3:K12,5,FALSE)”返回错误（ISERROR），则有显示“空”（""），反之显示公式的返回
4、选中J2单元格，再按住Ctrl键，点击C3单元格，同时选中J2和C3单元格，执行“格式→单元格”命令，打开“单元格格式”对话框（如图3），在“数字”标签中，选中“分类”下面的“日期”选项，然后在
右侧选定一种日期类型，确定返回。
5、选中B5单元格，再次打开“单元格格式”对话框，切换到“对齐”标签下（参见图4），将“水平对齐”、“垂直对齐”分别设置成“靠左”、“靠上”，并选中“自动换行”选项，确定返回。
6、设置好表格的字体、字号等格式。
以后，需要打印某位员工简历表时，只要在B2单元格中输入员工的姓名，确认后，该员工的简历情况，自动填入表格相应的单元格中（参见图2），打印即可。