excel如何匹配身份证号码归属地
用Excel从身份证号码中提取信息(年龄、性别、出生地)
用Excel从身份证号码中提取信息（年龄、性别、出生地）1.出生年月日信息提取：方法一：在记录列中输入公式：=--TEXT(MID(B2,7,6+IF(LEN(B2)=15,0,2)),"#-00-00")，往下复制，无论15位还是18位身份证号码全部搞定，方法最简单。
方法二：在记录列中输入公式：=--IF(LEN(B2)=15,TEXT(MID(B2,7,6),"##-00-00"),TEXT(MID(B2,7,8),"####-00-00"))，往下复制，无论15位还是18位身份证号码全部搞定，公式增加了几个字符，原理差不多，结果一致。
原理：使用函数text、if、mid、len。
注意：1、B列存放身份证号码。
存放在其它列，则在公式中作相应调整。
2、计算出错（#V ALUE!），说明身份证号码有错。
3、日期显示格式，可在单元格格式中设置。
性别信息提取：在记录列中输入公式：=IF(LEN(B2)=15,IF(MOD(RIGHT(B2),2)=0,"女","男"),IF(MOD(LEFT(RIGHT(B2,2)),2)=0,"女","男"))无论15位还是18位身份证号码全部轻松完成。
原理：使用函数IF、LEN、MOD、LEFT、RIGHT。
注意：1、B列存放身份证号码。
存放在其它列，则在公式中作相应调整。
2、计算出错（#V ALUE!），说明身份证号码有错。
出生地信息提取：在记录列中输入公式：=LEFT(B2,6)，往下复制，然后根据代码用VLOOKUP查询发证地或者是出生地信息。
Excel文件模板：从身份证号码中提取信息使用的模板：使用Excel从身份证号码提取信息.xls点击该图标，打开该EXCEL文件，另存为××文件，即可使用。
谢谢你的使用。
身份证号提取生日、年龄、性别、籍贯
身份证号提取生日、年龄、性别、籍贯咱们每个人都有一个唯一的身份证号，其实身份证号中包含了很多的个人信息，你知道怎么从身份证号中提取个人的生日、年龄、性别、籍贯等信息吗？技巧君今天和大家分享一下如何利用Excel从身份证号中提取个人信息！文末有示例文件获取方法哦身份证号的构成身份证号有18位，排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。
顺序码的奇数分给男性，偶数分给女性。
身份证号构成从这个构成中，我们可以发现，只需要提取对应的编码就可以提取出身份证号中的个人信息！提取性别=IF(MOD(MID(B2,17,1),2),'男','女')身份证号提取性别说明：MID(B2,17,1)，提取第17位数字MOD(MID(B2,17,1),2)，对2取余，奇数余数得1，偶数余数为0提取出生年月=--TEXT(MID(B2,7,8),'0-00-00')提取出身年月说明：MID(B2,7,8)，提取第7位开始后面的8位数字，即出生年月日编码TEXT(MID(B2,7,8),'0-00-00')，转换为年-月-日的格式--TEXT(MID(B2,7,8),'0-00-00')，减负运算换算为真正的日期提取实岁=DATEDIF(TEXT(MID(B2,7,8),'0-00-00'),TODAY(),'Y')提取实岁说明：DATEDIF(TEXT(MID(B2,7,8),'0-00-00'),TODAY(),'Y')，计算当前日期和出生日期的年差值，即实际岁数提取虚岁=(YEAR(NOW())-MID(B2,7,4))提取虚岁说明：MID(B2,7,4)：提取第7位数开始的4位数字，即出生年份YEAR(NOW())：计算当前日期的年份(YEAR(NOW())-MID(B2,7,4))：虚岁=当前年份-出生年份提取籍贯=VLOOKUP(--MID(B2,1,6),籍贯编码!B:C,2,0)提取籍贯说明：--MID(B2,1,6)，提取前六位地址编码VLOOKUP(--MID(B2,1,6),籍贯编码!B:C,2,0)，在地址编码表里找到籍贯。
EXCEL表上批量提取身份证“顺序号”的方法
EXCEL表上批量提取身份证“顺序号”的方法
EXCEL表上批量提取身份证“顺序号”的方法,分二种身份证18位和15位。
操作：先在EXCEL表上排序，批量将身份证排成15位和18位。
①在18位上用公式=MID（身份证所在单位格列标号，15，3）。
②在15位上用公式=MID（身份证所在单位格列标号，13，3）就这样完成了。
适合于现建制镇的大表，因为现在镇的村，就是原来的乡。
原来身份证的编码，是以县区为单位，按原来的乡（或派出所）分配的“顺序号”，前2位代表乡或派出所（代码号），后一位是按“同年同月同日”生的，排的先后次序从0-9共10个数，男单女双来定的。
现18位身份证，最后一位是识别码（计算方法略）。
教你用Excel表把身份证号中提取出“出生日期”“性别”“籍贯”
教你⽤Excel表把⾝份证号中提取出“出⽣⽇期”“性别”“籍贯”⾝份证号码中包含每个⼈的“出⽣⽇期”，但⼤多数⼈还不知道这⼀串数字中还包含了很多隐藏的信息,今天,我们就⽤EXCEL从中“探囊取物”，⼀起看个究竟！今天的主题：⽤excel函数和公式，从⾝份证号中提取出“出⽣⽇期”“性别”“籍贯”，并计算“当前年龄”。
1、从⾝份证号中提取“出⽣⽇期”=--Text(mid($B3,7,8),'0-00-00')使⽤函数：text、mid要点提⽰：（1）利⽤mid函数，从18位⾝份证号码中，取出从第7位开始的8个数字（2）利⽤text函数，将取出的8个数字，⽣成⽇期的数值形式（3）最后，通连续的减号“--”，将数值转换成⽇期的⽂本形式（4）依次往下复制、填充补充：excel中⽇期规范形式：“2018-5-7”、“2018/5/7”，不能写成“20180507”、'2018.5.7'、'2018、5、7'2、从⾝份证号中提取“性别”⽅法⼀：=IF(MID($B3,17,1)/2=TRUNC(MID($B3,17,1)/2),'⼥','男')⽅法⼆：=IF(MOD(MID(M2,17,1),2),'男','⼥')使⽤函数：if 、mid、trunc、mod要点提⽰：（1）利⽤if函数，判断⾮“男”，即“⼥”（2）if函数的“判断条件”，⽤mid提取出的第17位数除以2，看是否等于trunc函数判断第17位数除以2的整数部分（3）或者直接⽤mod函数，对第17位数除以2，求“余数”，>0为“男”，=0为“⼥”（4）依次往下复制、填充补充知识：⾝份证号倒数第2位数，奇数为“男”，偶数为“⼥”3、计算⾝份证号当事⼈当前“年龄”⽅法⼀：插⼊P列、Q列辅助列“P3”单元格输⼊：=year(today())“Q3”单元格输⼊：=mid(O3,7,4)“R3”单元格输⼊：=P3-Q3⽅法⼆：=YEAR(TODAY())-YEAR(--TEXT(MID($B3,7,8),'0-00-00'))使⽤函数：year、today、text、mid要点提⽰：1）today()指的是电脑的当前⽇期，再利⽤year函数，返回当前的年份值2）复杂函数的应⽤，并不是⾼⼿的象征，⽽简便、快速解决问题才是王道！所以，有时辅助列的运⽤会更加简便。
用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如何匹配身份证号码归属地身份证号码都是唯一的，新二代身份证号码为18位，前6位为籍贯。
这里我们来讲下，利用vlookup 来批量填写籍贯1、首先先下载各个地区籍贯前6位代号的集合，这个在百度里面搜索下都有的下载的。
这里里面不能加链接，这里就不添加了。
2、然后我们把刚刚下载的表里面的数据拷贝到你需要添加籍贯的表中“Sheet2”。
“sheet1”为需要填写籍贯的。
3、现在两张表格都在一张表中了。
现在就来讲讲公式怎么用。
把鼠标放到要“籍贯”那一栏，点击“插入函数”。
4、找到“vlookup”函数，点击确定。
会跳出函数参数，我们这里不管，什么都不填，直接点击确定，然后有跳出提示，也点击确定。
这样我们的文本框里面就出现了函数“vlookup”5、最后我们来输入公式，查找对应的籍贯。
“=VLOOKUP(LEFT(C3,6),Sheet2!$A$1:$B$6457,2,0)”这里来解释下。
都代表了什么意思。
LEFT(C3,6)，是表示取第C列第3行前6位的数字。
如，现在我们表中”白悦“身份证号码就在第C列第3行。
Sheet2!，是表示取“Sheet2”里面的值。
$A$1:$B$6457，是表示取“Sheet2”有数值的单元格。
$为绝对值。
2，是表示要取值的列数。
0，是表示比对要”绝对匹配“。
输入完之后，敲回车就好了。
6、现在我们已经输入好一个公式了。
接下来的所有人的籍贯也就简单多了。
只要把鼠标放在刚刚那个输入好的公式文本框右下角，鼠标变成黑十字架，双击鼠标左键。
这样下面所有人的籍贯就都出来了注意事项：这个两张表一定要在同一个excel文件内，输入公式的那一栏的，单元格格式一定为常规。
excel身份证验证公式
excel身份证验证公式
Excel身份证验证公式
1.验证格式：数据校验公式可以验证身份证号码是否正确和合法。
Excel中可以使用“LEN()”函数来确定身份证号码字符串的长度，再与18位数对比来明确证书是否存在。
2.验证位数：只有18位数身份证号码才能使用来进行校验，15位号码已不再使用，可以通过旧省份前两位是否是“35”、“36”或“37”来判断。
3.验证省级代码：通过省份前两位码来验证省级代码，与身份证号码前两位相对应。
4.验证出生日期：可以通过对身份证号码出生日期的部分（第7到14位）与Excel 中“LEFT()”“MID()”等函数结合，来验证身份证号码的出生日期，验证准确。
5.验证顺序号：通过身份证号码的第16位和第17位数字代表性别，第15位和第18位数字代表性别，第15位数字用于最后一位校验码的算法，如果正确，身份证号码可以正确匹配！
6.验证位：计算机通过一组算法，通过身份证号码前17位数字，计算
出一个0-10之间的数字，如果运算结果与最后一位数字相等则表示验证通过！
此外，Excel中对于身份证号码的验证，还可以利用VBA编程语言进行。
其中可以使用“If（）Then（）Else（）”结构计算出一组结果，以决定身份证号码是否要验证成功。
excel技巧之根据身份证号码提取对应信息
excel技巧之根据⾝份证号码提取对应信息⼤家好，今天跟⼤家分享的是⼀个⾝份证号的案例。
⼤家都知道⾝份证号是唯⼀的，⽽且⾝份证号可以提供多种信息，今天我们就根据⾝份证号，提取出出⽣年⽉，性别这两项简单的。
⽬前⾝份证号码都是18位，这18位代表的含义为：前6位表⽰地区；第7位到第14位表⽰出⽣⽇期；第15位到第17位表⽰⼀个顺序号；其中第17位，奇数为男性，偶数为⼥性；第18位为校验码。
然后⼤家来看案例（案例中的⾝份证号是我⽤公式随机⽣成的，只考虑了中间出⽣的⽇期是正确的格式，前⾯的地址和校验码都没有考虑=。
=，反正就是给⼤家看个例⼦）⼀、出⽣年⽉⾸先我们来提取出出⽣年⽉。
⾝份证号码第7位到第14位表⽰出⽣⽇期，所以就要提取出⾝份证号码的这8位即可。
⼀个函数，mid。
MID(text, start_num, num_chars) mid（⽂本，从第⼏个开始截取，截取⼏个字符）所以公式就是=mid（C2，7，8)这时候我们来看⼀下结果虽然已经是⼋位数的字符串，但是不是我们想要的时间格式，所以就需要⽤text来变成时间格式TEXT(MID(C2,7,8),'0000-00-00')这时候，⽇期就变成这样的格式，但是此时本质还是⽂本。
如果想变成真正的⽇期格式，那公式需要改为（其实这⼀步骤公式也很多，我只是随便选取了⼀个）DATE(MID(C2,7,4),MID(C2,11,2),MID(C2,13,2))这个公式属于时间公式，今天就不做分享了。
如果⽤这个公式，会将结果变为真正的⽇期格式，可以通过设置单元格格式来改变样式。
可以通过动图看⼀下这种格式的区别。
⼆、性别⾝份证号的第17位号码代表性别，奇数代表男，偶数代表⼥。
第⼀步先提取第17位数字然后判断奇偶性。
判断奇偶性在excel中⾮常多的⽅法，今天主要分享modmod（被除数，除数）结果返回⼀个余数⽐如：mod（2,1）=2÷1 =2余0 所以返回值是0mod（3,2）=3÷2=1余1 所以返回值是1这时候可以想到奇数与偶数的区别就是偶数可以被2整除，但是奇数不能可以看⼀下现在的结果。
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表格内将身份证号码提取户籍地？
怎样在excel表格内将身份证号码提取户籍地？
一、公式法获取归属地基础信息表，然后用公式直接读取，方法比较简单，如下所示：1、归属地基础信息获取可以在网上搜索，其实很多地方都能找到相关的信息，比如我收藏的直接Excel表格式的。
如下图所示：2、公式读取归属地相关信息有了这个基础的数据表后，根据号码提取归属地地就很简单了，公式如下（假设身份证号码在A2单元格）：=VLOOKUP(LEFT(A2,6),'属地'!A:C,2,0)如下图所示：二、网络直查法上面的公式法虽然简单，但是有一个问题，就是获得的归属地基础信息表无法更新，而且大部分现在能找到的版本都不包含“撤县变市”的情况，所以，有些号码无法识别。
因此，个人更加推荐使用Excel2016的新功能Power Query（Excel2010或Excel2013可到微软官方下相应的插件）进行网络直查，操作简单，而且信息全面。
具体方法如下：输入基本信息，如下：确定后就得到相应的信息：通过简单的编辑，就可以将数据返回Excel里了，如下图所示：当然，如果希望能直接输入，然后刷新出结果，那么需要再学习一些Power Query功能的知识。
以上是个人总结的提取户籍地的两个方法，公式法比较简单，但可能会出现信息更新不及时的情况，使用Excel新功能Power Query进行网络直接查询的方法稍为复杂一点儿，但是也不难，而且信息更加完整。
Excel表格身份证号码提取出生日期地公式
Excel表格身份证号码提取出生日期的公式（B2表示身份证号码所在的列位置）=MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2)回车→向下填充1. Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数);2. 15位身份证号从第7位到第12位是出生年月日，年份用的是2位数。
3. 18位身份证号从第7位到第14位是出生的年月日，年份用的是4位数。
一、提取出生年月：A、15位身份证号码：=MID(B2,7,2)&"-"&MID(B2,9,2)&"-"&MID(B2,11,2) 回车确认即可。
B、18位身份证号码：=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&”日”回车确认即可。
二、提取性别：18位身份证号码：=IF(MOD(MID(B2,17,1),2)=1,"男","女")回车确认即可。
*excel公式中=IF(MOD(MID(E4,17,1),2)=0,"女","男")是什么意思？IF是选择函数，当MOD(MID(E4,17,1),2)=0成立时，单元格显示“女”，否则显示“男”。
MOD是取模函数，即是一个求余函数，求MID(E4,17,1)除以2的余数。
实质是判断MID(E4,17,1)的奇偶性。
MID从一个文本字符串的指定位置开始，截取指定数目的字符。
MID(E4,17,1)是从E4单元格的文本中的第17个字符开始，取一个字符。
三、提取年龄：=year(today())-value(right(left(B2，10),4)) 回车确认即可。
excel中的籍贯公式
excel中的籍贯公式引言：Excel是一款功能强大的电子表格软件，广泛应用于各个领域。
在Excel中，公式是实现数据计算和处理的重要工具之一。
其中，籍贯公式是一种常见的公式，用于根据身份证号码中的地区编码自动计算出个人的籍贯信息。
本文将详细介绍Excel中的籍贯公式，包括其原理、使用方法以及注意事项。
正文：1. 原理1.1 身份证号码结构身份证号码是由18位数字组成的，其中前6位表示地区编码，接下来的8位表示出生日期，然后是3位顺序码，最后一位是校验码。
籍贯公式利用身份证号码中的地区编码来计算出个人的籍贯信息。
1.2 地区编码与籍贯对应关系Excel中可以通过一个地区编码与籍贯对应的数据表来实现籍贯公式。
该数据表包含地区编码与对应的籍贯信息，可以通过VLOOKUP函数来进行查找匹配。
2. 使用方法2.1 准备数据表首先，需要准备一个包含地区编码与籍贯对应关系的数据表。
数据表的结构可以包括两列，一列是地区编码，另一列是对应的籍贯信息。
2.2 编写公式在Excel中，可以使用VLOOKUP函数来实现籍贯公式。
该函数的基本语法如下：VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])其中，lookup_value是要查找的地区编码，table_array是数据表的范围，col_index_num是要返回的数据所在的列数，range_lookup是一个可选参数，用于指定是否进行近似匹配。
在籍贯公式中，我们需要将lookup_value设置为身份证号码中的地区编码。
2.3 填写公式在Excel中，可以选择一个单元格，然后输入公式。
在公式栏中，输入VLOOKUP函数，并填写相应的参数。
例如，假设地区编码在A列，籍贯信息在B列，要计算的身份证号码在C列，可以在C列输入以下公式：=VLOOKUP(LEFT(C2,6), $A$2:$B$100, 2, FALSE)其中，C2表示要计算的身份证号码所在的单元格，$A$2:$B$100表示数据表的范围，2表示要返回的数据所在的列数，FALSE表示进行精确匹配。
Excel函数教程：身份证号码提取户籍所在省份地区，经典实例
Excel函数教程：⾝份证号码提取户籍所在省份地区，经典实例有时我们知道⾝份证号码，怎么提取它的户
籍所在省份地区，这⾥通过Excel可以实现
这⼀功能。
如果想了解⾝份证号怎么提取出⽣年⽉⽇、性别、年龄、⽣肖、星座的朋友，可以去看我之前
发布的教程。
教程开始之前，我们先要准备两⽅⾯的东西：
第⼀、了解常识。
⾝份证前6位数字表⽰的是户籍所在地。
其中1、2位数为各省级的代码，3、4位数为地、市级的
代码，5、6位数为县、区级代码。
第⼆、先准备⼀份户籍所在省份地区参照表。
（⽂后有交代）
第⼆、先准备⼀份户籍所在省份地区参照表。
先来看看效果：
Step1：把需要处理的数据、户籍所在省份地区参照表放在同⼀个⼯作表的不同⼯作簿中。
Step2：公式=VLOOKUP(LEFT(C2,6), 户籍所在地参照表!A:B,2,1)，其中“户籍所在地参照表”是你存放户籍所在省份地区参照表⼯作簿的名字，如果你的有变化，记得修改。
结语：⾄此，关于⾝份证号码提取出⽣年⽉⽇、性别、年龄、⽣肖、星座、户籍所在地的教程都制作完毕。
因为不能发链接，需要
户籍所在地参照表的朋友私信我。
excel表格中如何根据身份证号提取,户籍所在地行政区划代码
竭诚为您提供优质文档/双击可除excel表格中如何根据身份证号提取,户籍所在地行政区划代码篇一：excel中提取身份证号码及姓别excel中怎么快速输入复杂序号有时候我们需要输入一些比较长的产品序号，如1987542230001、1987542230002、1987542230003……，前面的数字都是一样的，只是后面的按照序号进行变化。
对于这样的序号我们也可以进行快速输入。
选中要输入这些复杂序号的单元格，接着点击菜单“格式”-“单元格”，在弹出的对话框中点击“数字”标签，在分类下选择“自定义”，然后输入“"198754223"0000”完成后点击“确定”按钮。
以后只要在选中的单元格中输入1、2、3……序号时，就会自动变成设置的复杂序号了。
excel自动提取身份证中生日和性别每年新入学的一年级学生，都需要向上级教育部门上报一份包含身份证号、出生年月等内容的电子表格，以备建立全省统一的电子学籍档案。
数百个新生，就得输入数百行相应数据，这可不是个轻松活儿。
有没有什么办法能减轻一下输入工作量、提高一下效率呢？其实，我们只需在excel20xx 中将学生的身份证号完整地输入后，它就可以帮我们自动填好出生日期和性别。
现在学生的身份证号已经全部都是18位的新一代身份证了，里面的数字都是有规律的。
前6位数字是户籍所在地的代码，7－14位就是出生日期。
第17位“2”代表的是性别，偶数为女性，奇数为男性。
我们要做的就是把其中的部分数字想法“提取出来”。
step1，转换身份证号码格式我们先将学生的身份证号完整地输入到excel20xx表格中，这时默认为“数字”格式（单元格内显示的是科学记数法的格式），需要更改一下数字格式。
选中该列中的所有身份证号后，右击鼠标，选择“设置单元格格式”。
在弹出对话框中“数字”标签内的“分类”设为“文本”，然后点击确定。
step2，“提取出”出生日期将光标指针放到“出生日期”列的单元格内，这里以c2单元格为例。
Excel应用——从身份证号码中提取基本信息的方法
综合论坛新教师教学日常办公当中，我们常常需要在表格中输入人员的姓名、性别、身份证号码、出生年月日，籍贯等基本信息，如果涉及到人员数量比较多的时候，录入的数据量大，逐项录入非常麻烦。
如果利用功能强大的Excel 来完成这些信息的录入，那么将会变得非常容易。
首先我们来了解一下现在所使用的第二代身份证号码的编码规则，其由18位数字组成，第1到6位为归属地，第7到第10位为出生年份，第11到第12位为出生月份，第13到第14位为出生日期，第17位为性别（其中奇数为男，偶数为女），第18位为校验码。
将下来，本文介绍如何使用Excel2010快速提取“表一”中的这些基本信息。
（表一）AB C DEF 1编号姓名身份证号码出生日期性别年龄2001关娟瀛4507031995020533253002利春铭450705************一、 出生日期的提取方法1.数据分列的使用首先，选中“表一”中要分列的数据区域C2：C3，然后点击“数据→数据工具→分列”，调出分列窗口，在“请选择最合适的文件类型”处选择根据“固定宽度”分列，在“数据预览”区分别在身份证号码的第6和第7位之间及第14和第15位之间点击添加分列线将身份证号码分成三个部分，如“图1”所示；其次，在“数据预览”区点击身份证号码的第一部分前6位，选择“列数据格式”区的“不导入此列（跳过）”，对身份证号码的最后一部分最后4位应用同样的“列数据格式”；再次，在“数据预览”区点击身份证号码中间部分第7到第14位，选择“列数据格式”区的“日期（D ）：YMD ”，将“目标区域”定位D2单元格，如“图2”所示；最后，点击“完成”即可，实现了从身份证号码中提取出生日期。
（图1） （图2）2.函数的使用（MID 函数、DATE 函数、TEXT 函数）根据第二代身份证号码的编码规则，我们只需要将第7到14位的年月日按照需要的格式提取出来即可，方法如下：（1）MID 函数和DATE 函数的组合使用首先设置D2单元格为任一种日期格式，然后在D2单元格中输入公式为：=DATE （MID （C2，7，4），MID （C2，11，2），MID （C2，13，2））然后回车确认，即可提取出该人员的出生日期，再拖动填充柄把公式向下复制，其余人员的出生日期也可提取出来。
Excel表格小技巧：利用公式提取你所在的家乡
昨天我们利用身份证号码提取出生年月性别，计算年龄，今天我们就用一个公式提取身份证号码中籍贯：
打开百度APP看高清图片
身份证前两位数字对应省份（一）
身份证前两位数字对应省份（二）
身份证前两位数字对应省份（三）
在信息表的G2单元格输入以下公式，向下复制。
=VLOOKUP(LEFT(B2,6)*1,身份证代码对照表！A:B,2,0)
首先用LEFT(B2,6)得到身份证号码的前六位，然后使用VLOOKUP函数在代码对照表里查找对应的籍贯信息。
因为LEFT函数得到的是文本型的数字，而咱们的代码表里是数值型的，所以用乘以1的方法转换为数值，否则查询会出错了。
最后咱们再看看查找重复身份证号码的问题。
I2输入以下公式，向下复制。
=IF(COUNTIF(B:B,B2&"*")>1,"有重复","无重复")
身份证号码的查重和常规的数据查重不太一样，在COUNTIF函数的第二参数后要加上一个星号&"*"。
COUNTIF函数在计算文本型数字时，会默认按数值型进行处理，但是Excel 中的数字精度只有15位，而身份证号码是18位，这样就会把前15位相同的身份证全部识别为相同的内容。
咱们利用Excel中的数值不支持通配符的特点，在第二参数后连接上一个通配符星号&"*"。
就是相当于告诉Excel，要查找以B2单元格内容开头的文本，通过这样变通的手段，COUNTIF函数就听话了。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
excel如何匹配身份证号码归属地
身份证号码都是唯一的，新二代身份证号码为18位，前6位为籍贯。
这里我们来讲下，利用vlookup 来批量填写籍贯
1、首先先下载各个地区籍贯前6位代号的集合，这个在百度里面搜索下都有的下载的。
这里里面不能加链接，这里就不添加了。
2、然后我们把刚刚下载的表里面的数据拷贝到你需要添加籍贯的表中“Sheet2”。
“sheet1”为需要填写籍贯的。
3、现在两张表格都在一张表中了。
现在就来讲讲公式怎么用。
把鼠标放到要“籍贯”那一栏，点击“插入函数”。
4、找到“vlookup”函数，点击确定。
会跳出函数参数，我们这里不管，什么都不填，直接点击确定，然后有跳出提示，也点击确定。
这样我们的文本框里面就出现了函数“vlookup”
5、最后我们来输入公式，查找对应的籍贯。
“=VLOOKUP(LEFT(C3,6),Sheet2!$A$1:$B$6457,2,0)”
这里来解释下。
都代表了什么意思。
LEFT(C3,6)，是表示取第C列第3行前6位的数字。
如，现在我们表中”白悦“身份证号码就在第C列第3行。
Sheet2!，是表示取“Sheet2”里面的值。
$A$1:$B$6457，是表示取“Sheet2”有数值的单元格。
$为绝对值。
2，是表示要取值的列数。
0，是表示比对要”绝对匹配“。
输入完之后，敲回车就好了。
6、现在我们已经输入好一个公式了。
接下来的所有人的籍贯也就简单多了。
只要把鼠标放在刚刚那个输入好的公式文本框右下角，鼠标变成黑十字架，双击鼠标左键。
这样下面所有人的籍贯就都出来了
注意事项：这个两张表一定要在同一个excel文件内，输入公式的那一栏的，单元格格式
一定为常规。