Excel公式批量从身份证和手机号码中提取归属地不用一个个查询

Excel公式批量从身份证和手机号码中提取归属地不用一个个查询

客户只留了手机号码或身份证号码而没有联系地址，这是该怎么知道客户的归属地呢？默认的方法大都是上网一个一个的查询，但是这个方法很是麻烦。对于客户MM来说，每天接触最多的便是Excel表格即客户信息表，因此可以借助Excel的强大功能来完成这个需求，本节与大家分享的便是批量从身份证和手机号码中提取归属地的方法。大概思路是用vlookup函数分别提取省、市和区，用&连接在一起构成归属地，身份证的前2位代表省份，前4位代表具体的市，前6位代表具体的区县。另外需要一个身份证归属表，文后有下载地址。

一、从身份证号中提取归属地

身份证的前2位代表省份，前4位代表具体的市，前6位代表具体的区县。为了提取具体信息，我们还需要一个身份证归属表（文后有下载地址），如下图所示。

有了身份证数据库，在客户明细表中就可以用vlookup函数分别提取省、市和区，用&连接在一起构成归属地。

VLOOKUP是一个查找函数，给定一个查找的目标，它就能从指定的查找区域中查找返回想要查找到的值。它的基本语法为：

VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找)

公式：C2

=VLOOKUP(LEFT(B2,2),身份证数据库!A:B,2,0)&VLOOKUP(LEFT(B2,4),身份证数据库!A:B,2,0)&VLOOKUP(LEFT(B2,6),身份证数据库!A:B,2,0)

二、从手机号码中提取归属地

从手机号码中提取归属地，同样也需要手机号归属地的数据表。（文后附下载地址），如下图所示。

从上表看出，只要知道手机的前7位数字，就可以提出对应的省份和城市。所以公式就可以设置为：

公式：C8

=VLOOKUP(LEFT(B8,7),手机数据库!A:C,2,0)&VLOOKUP(LEFT(B8,7),手机数据库!A:C,3,0)

示例下载地址（复制下面链接到电脑浏览器地址栏中，按回车下载）http://pan.baidu.com/s/1eQxrLXS

提取归属地并不难，难的是要有手机和身份证归属地的表。建议大家将按上面的链接复制到浏览器地址栏中下载并收藏，方便以后使用。如果你是一名客服MM那么请不要错过本文，它可以在你工作中起到提高效率的作用。让你从繁忙的工作中脱颖而出，轻松应对一切难题。