怎樣在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號。

更多精彩內容，可以在頭條主頁查看~