Excel 用excel公式從身份證、手機(jī)號(hào)碼查找歸屬地

格子社區(qū)

閱： 13708 | 回： 7

用excel公式從身份證、手機(jī)號(hào)碼查找歸屬地 Excel基礎(chǔ) , 編號(hào):0611

月光小城

發(fā)表于2015/11/22 22:41:46 樓主

回復(fù)樓主

等級(jí):初學(xué)者

積分:0

財(cái)富值:0.00

身份:普通用戶

用excel公式從身份證、手機(jī)號(hào)碼查找歸屬地

對(duì)于客戶MM來(lái)說(shuō)，接觸最多的excel表格就是客戶信息表。有時(shí)客戶只留了手機(jī)號(hào)碼或身份證號(hào)碼而沒(méi)有留聯(lián)系地址。這時(shí)只能一個(gè)個(gè)的去網(wǎng)上查詢，很是麻煩。今天蘭色分享的就是批量從身份證和手機(jī)號(hào)碼中提取歸屬地的方法。

一、從身份證號(hào)中提取歸屬地
身份證的前2位代表省份，前4位代表具體的市，前6位代表具體的區(qū)縣。為了提取具體信息，我們還需要一個(gè)身份證歸屬表（文后有下載地址），如下圖所示。

有了身份證數(shù)據(jù)庫(kù)，在客戶明細(xì)表中就可以用vlookup函數(shù)分別提取省、市和區(qū)，用&連接在一起構(gòu)成歸屬地。
公式：C2
=VLOOKUP(LEFT(B2,2),身份證數(shù)據(jù)庫(kù)!A:B,2,0)&VLOOKUP(LEFT(B2,4),身份證數(shù)據(jù)庫(kù)!A:B,2,0)&VLOOKUP(LEFT(B2,6),身份證數(shù)據(jù)庫(kù)!A:B,2,0)

二、從手機(jī)號(hào)碼中提取歸屬地
從手機(jī)號(hào)碼中提取歸屬地，同樣也需要手機(jī)號(hào)歸屬地的數(shù)據(jù)表。（文件附下載地址），如下圖所示。

從上表看出，只要知道手機(jī)的前7位數(shù)字，就可以提出對(duì)應(yīng)的省份和城市。所以公式就可以設(shè)置為：
公式：C8
=VLOOKUP(LEFT(B8,7),手機(jī)數(shù)據(jù)庫(kù)!A:C,2,0)&VLOOKUP(LEFT(B8,7),手機(jī)數(shù)據(jù)庫(kù)!A:C,3,0)