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之后可以转为数值。
EXCEL中-提取身份证当中所包含的信息
EXCEL中-提取身份证当中所包含的信息姓名身份证号码提取性别提取出生日期提取现在年龄提取生肖(从出身日期中提取)身份证号码是否正确提取所属省份(从地址码提取)提取所属地区(从地址码提取)张三110221*********女1965-8-1549蛇TRUE北京市(市辖区)昌平李四510221************男1974-12-139虎TRUE四川省重庆市王五132426*********男1959-6-2055猪TRUE河北省保定地区完县周六140121*********女1970-2-2844狗TRUE山西省太原市清徐县钱七350583************男1978-10-1236马TRUE福建省泉州市南安市彭九511801*********女1952-9-2562龙TRUE四川省雅安市市辖区刘二620123*********女1979-5-1335羊TRUE甘肃省兰州市榆中县说明如下:以B2单元格为例提取生日1965-8-15此为公式中第1种方法的结果公式1=DATE(MID(B2,7,IF(LEN(B2)=18,4,2)),MID(B2,IF(LEN(B2)=1 8,11,9),2),MID(B2,IF(LEN(B2=--TEXT(MID(B2,7,6+(LEN(B2)=18)*2),"0-00-00")3=TEXT(RIGHT(TEXT((0&MID(B2,7,11))-500,"1900-00-00,;!0"),10),"e-mm-dd;;;错误")4=TEXT(TEXT((0&MID(B2,7,11))-500,"00-00-00,;!0"),"e-mm-dd;;;错误")5=TEXT(MID(REPLACE(B2,7,,IF(LEN(B2)=15,19,)),7,8),"0000-00-00")6=TEXT(REPLACE(MID(B2,7,6+(LEN(B2)=18)*2),1,,IF(LEN(MI D(B2,7,6+(LEN(B2)=18)*2))=67=TEXT(TEXT(MID(B2,7,6+(LEN(B2)=18)*2),"0-00-00"),"emmdd")8=IF(LEN(B2)=15,"19"&MID(B2,7,6),MID(B2,7,8))9=IF(LEN(B2)=15,19,"")&MID(B2,7,6+(LEN(B2)=18)*2)提取性别女此为公式中第1种方法的结果公式1=IF(MOD(IF(LEN(B2)=15,MID(B2,15,1),MID(B2,17,1)),2)=1,"男","女")2=IF(MOD(RIGHT(LEFT(B2,17)),2),"男","女")提取生肖蛇此实例引用的是出生日期公式=MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(YEAR(D2)-4,12)+1,1)15位改18位110221************注意此公式是数组。
利用Excel加载宏轻松提取身份证省份信息
在建立企事业单位员工Excel表格过程中，员工的身份证号、所属省份、性别等信息都是必须要输入的。
如果单位员工众多的话，相关个人信息的输入工作量无疑是相当大的。
那么，我们能不能让繁琐的个人信息输入过程变得更轻松以提高自己的工作效率呢？答案当然是肯定的。
因为只须利用一个Excel加载宏，我们就可以很方便地达到上述目的。
先下载Excel加载宏“根据身份证求省市”，然后将下载的RAR格式同名压缩包解压到硬盘的任意位置(如“C:加载宏”目录下)，接着运行“Excel 2007”(其他版本操作类似)并单击其左上角的圆形按钮，选择“Excel选项”，打开“Excel选项”对话框的“加载项”分支;再单击该分支下的“转到”按钮，以打开“加载宏”对话框(如图1);最后勾选，A处新出现的“根据身份证求省市”复选框并单击“确定”按钮即可。
经过以上步骤的操作，我们就完成了Excel加载宏“根据身份证求省市.xla”在Excel 2007中的添加操作，接着打开某个已经输入了姓名信息的“某单位员工信息表.xlsx”工作簿(如图2)，接着在图2中的A2单元格里边输入函数“=sfz(B2，“DQ”)”(不包括最外侧引号，下同)，回车之后往下拖动填充手柄以完成所有员工“所属省份”户籍地信息的快速录入操作，此时就可以看到效果了，如A处。
当然，依此类推在B2单元格中输入“=sfz(B2，“SR”)”、在C2单元格中输入“=sfz(B2，“XB”)”并往下拖动填充手柄，还可以快速地完成“出生日期”和“性别”等信息的快速录入操作，如B、C两处。
教你用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身份证提取户籍所在省份地区：Excel函数不求人
准备一份归属地数据库
准备一份居民归属地数据库，这个数据上面有身份证前6位数对应的所在省份与地区。
将数据放到Sheet2工作簿中。
当然，如果没有的话，我这里在文章结尾也会提供给大家的。
（绝对是好东西，不要问我从哪里搞到的！）
使用函数公式
在Sheet2工作簿中是放数据的地方，在Sheet1工作簿中是我们用来输入公式获取身份地区的地方。
我们在A2单元格输入好身份证号码后，在B2单元格中输入函数公式：【=VLOOKUP(LEFT(A2,6),Sheet2!A:B,2,1)】
函数说明：
VLOOKUP函数：查找函数，VLOOKUP是按列查找，最终返回该列所需查询列序所对应的值。
LEFT：左边的意思，(LEFT(A2,6)：是获取A2单元格从左开始6位数。
Sheet2!A:B,2：Sheet2工作簿中A、B两列。
最后一个1是TRUE，也就是模糊查找。
联合起来的意思就是：通过A2单元格中前6位数去查找Sheet2
工作簿中A、B两列中类似的数据，找到后返回该单元格对应列中的数据。
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是一款功能强大的电子表格软件，广泛应用于各个领域。
在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中提取身份证号码及姓别excel中怎么快速输入复杂序号有时候我们需要输入一些比较长的产品序号，如1987542230001、1987542230002、1987542230003……，前面的数字都是一样的，只是后面的按照序号进行变化。
对于这样的序号我们也可以进行快速输入。
选中要输入这些复杂序号的单元格，接着点击菜单“格式”-“单元格”，在弹出的对话框中点击“数字”标签，在分类下选择“自定义”，然后输入“"198754223"0000”完成后点击“确定”按钮。
以后只要在选中的单元格中输入1、2、3……序号时，就会自动变成设置的复杂序号了。
excel自动提取身份证中生日和性别每年新入学的一年级学生，都需要向上级教育部门上报一份包含身份证号、出生年月等内容的电子表格，以备建立全省统一的电子学籍档案。
数百个新生，就得输入数百行相应数据，这可不是个轻松活儿。
有没有什么办法能减轻一下输入工作量、提高一下效率呢？其实，我们只需在excel20xx 中将学生的身份证号完整地输入后，它就可以帮我们自动填好出生日期和性别。
现在学生的身份证号已经全部都是18位的新一代身份证了，里面的数字都是有规律的。
前6位数字是户籍所在地的代码，7－14位就是出生日期。
第17位“2”代表的是性别，偶数为女性，奇数为男性。
我们要做的就是把其中的部分数字想法“提取出来”。
step1，转换身份证号码格式我们先将学生的身份证号完整地输入到excel20xx表格中，这时默认为“数字”格式（单元格内显示的是科学记数法的格式），需要更改一下数字格式。
选中该列中的所有身份证号后，右击鼠标，选择“设置单元格格式”。
在弹出对话框中“数字”标签内的“分类”设为“文本”，然后点击确定。
step2，“提取出”出生日期将光标指针放到“出生日期”列的单元格内，这里以c2单元格为例。
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技巧之根据⾝份证号码提取对应信息⼤家好，今天跟⼤家分享的是⼀个⾝份证号的案例。
⼤家都知道⾝份证号是唯⼀的，⽽且⾝份证号可以提供多种信息，今天我们就根据⾝份证号，提取出出⽣年⽉，性别这两项简单的。
⽬前⾝份证号码都是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整除，但是奇数不能可以看⼀下现在的结果。
- 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文件内，输入公式的那一栏的，单元格格式一定为常规。