用Excel提取身份证籍贯信息

Question

在Excel中，我现在有一份全国身份证归属地表格（假定为B表），另外一张Excel中只有身份证号码（假定为A表）,我如何用公式调用B表库来判断出A表的身份证原始籍贯？
假定这是全国身份证库
A B
110227 北京市怀柔县
110228 北京市密云县
110229 北京市延庆县
120000 天津市
120100 天津市市辖区
120101 天津市和平区
120102 天津市河东区
120103 天津市河西区

这是待查询的身份证号码
A B
120110197103070622 #N/A
其中 #N/A是使用公式 =VLOOKUP(MID(B3,1,6),全国身份证!A:A,2) 后的结果，我不知道为什么，请各位老师帮忙解决一下。谢谢

Answer 1

EXCEL中输入身份证号码就能自动显示籍贯的公式编写方法：

思路：身份证的头6位数字表示籍贯

（1）前1、2位数字表示：所在省份的代码； 、

（2）第3、4位数字表示：所在城市的代码； 

（3）第5、6位数字表示：所在区县的代码； 

（4）第7~14位数字表示：出生年、月、日； 

（5）第15、16位数字表示：所在地的派出所的代码； 

（6）第17位数字表示性别：奇数表示男性，偶数表示女性； 

（7）第18位数字是校检码：用来检验身份证的正确性。校检码可以是0~10的数字，10用x表示。

第一步：

确定表格中Sheet1中相应位置如下图所示:

第二步：

找一个省份对应代码表（这里没法上传省份对应代码表，百度自己找吧。）

复制到sheet2中(a列代码，b列地址)如下图：

第三步:

在sheet1表格D2单元格里输入公式=VLOOKUP(--LEFT(A2,6),Sheet2!A:B,2,)

即可显示身份证所属籍贯，可显示到县级单位。

第四步：

最后下拉填充即可。（一定注意要对应自己表格相应数值的位置）

Answer 2

=IFERROR(VLOOKUP(VALUE(MID(B3,1,6)),全国身份证!A:B,2,FALSE),"")

因为你的身份证信息是文本格式，与你数据库中的数字不匹配，需要用VALUE（）转换一下才行，另外我加了一个IFERROR()是防止出错用的，因为我不喜欢看到类似#N/A或#VALUE之类的东东出现，呵呵本回答被提问者采纳

Answer 3

用vlookup+mid两函数联合使用即可

需要查询的身份证号码放在B2单元格
=VLOOKUP(--LEFT(B2,2),省,2,0)&VLOOKUP(--MID(B2,3,2),INDIRECT(VLOOKUP(--LEFT(B2,2),省,2,0)),2,0)&VLOOKUP(--MID(B2,5,2),INDIRECT(VLOOKUP(--MID(B2,3,2),INDIRECT(VLOOKUP(--LEFT(B2,2),省,2,0)),2,0)),2,0)
这个很复杂，只是为了满足你在一个单元格中显示全部内容输入的愿望而已~~~
比如：
“四川省攀枝花市东区”

Answer 4

假设归属地表格为Shhet1，A、B两列分别为六位代码和归属地，Sheet2中的A1为身份证号
=VLOOKUP(--LEFT(A1,6),Sheet1!A:B,2,FALSE)

Answer 5

B列公式应该是：B1=VLOOKUP(LEFT(A1,6),全国身份证!A:B,2)