身份證號構(gòu)成

身份證號由四部分組成：

地址碼：表示編碼對象常住戶口所在縣（市、旗、區(qū)）的行政區(qū)劃代碼

出生日期碼：表示編碼對象出生的年、月、日

順序碼：表示在同一地址碼所標(biāo)識的區(qū)域范圍內(nèi)，對同年、同月、同日出生的人編定的順序號，順序碼的奇數(shù)分配給男性，偶數(shù)分配給女性

校驗(yàn)碼：根據(jù)前面十七位數(shù)字碼，按照ISO 7064:1983.MOD 11-2校驗(yàn)碼計(jì)算出來的檢驗(yàn)碼。

所以從身份證號中提取信息，我們需要對身份證號進(jìn)行分段處理，要從地址碼中對應(yīng)提取出籍貫，必須有一個地區(qū)和編碼之間的編碼表，下面來看一下具體怎么制作吧！

身份證號提取性別

函數(shù)公式：=IF(MOD(MID(A2,17,1),2),'男','女')

說明：

a、MID(A2,17,1)函數(shù)提取第17位數(shù)

b、MOD函數(shù)對2取余數(shù)，偶數(shù)余數(shù)為0，說明為女性，奇數(shù)余數(shù)為1，說明為男性，再用IF函數(shù)來判斷性別

c、外層再嵌套IFERROR規(guī)避錯誤值

身份證號提取年齡

函數(shù)：DATEDIF(--TEXT(MID(A2,7,8),'0-00-00'),TODAY(),'y')

說明：

a、MID(A2,7,8)提取出生日期碼

b、TEXT函數(shù)將文本處理為日期格式，--減負(fù)運(yùn)算將文本轉(zhuǎn)化為日期值

c、DATEDIF函數(shù)計(jì)算出生日期和今天的差值，即得到年齡

身份證號提取出生日期

函數(shù)：TEXT(MID(A2,7,8),'0年00月00日')

說明：

a、MID(A2,7,8)，提取出生日期碼

b、TEXT函數(shù)處理為日期格式

身份證號提取生肖

函數(shù)：MID('鼠?；⑼谬埳唏R羊猴雞狗豬',MOD(MID(A2,7,4)-4,12)+1,1)

說明：

a、MID(A2,7,4)提取出生年份

b、MOD函數(shù)對12取余數(shù)，剛好對應(yīng)12生肖的序列代碼，里面日期的-4和外面的 +1是為了和前面的生肖相對應(yīng)

身份證號提取星座

函數(shù)：VLOOKUP(VALUE('1900-'&TEXT(MID(A2,LEN(A2)/2+2,4),'#-##')),{1,'摩羯座';20,'水瓶座';50,'雙魚座';81,'白羊座';111,'金牛座';142,'雙子座';174,'巨蟹座';205,'獅子座';236,'處女座';267,'天秤座';298,'天蝎座';328,'射手座';357,'摩羯座'},2,TRUE)

說明：這個函數(shù)主要是了解星座和日期之間的換算關(guān)系，有興趣可以做進(jìn)一步了解，因計(jì)算規(guī)則較為復(fù)雜，這里不做詳述

身份證號提取籍貫信息

函數(shù)：VLOOKUP(LEFT(A2,6),地區(qū)代碼!A:B,2,1)

說明：籍貫信息主要有地區(qū)代碼表，將代碼換算為地區(qū)就可以了

注意事項(xiàng)：

1、本文所用所有身份證號均為地區(qū)碼+出生日期碼+順序碼+校驗(yàn)碼隨機(jī)組合而成，非真實(shí)身份證號，如有雷同，純屬巧合！

2、本文涉及的主要函數(shù)有：MID、MOD、TEXT、IF、VLOOKUP、DATEDIF、IFERROR、VALUE、內(nèi)存數(shù)組等，這些公式都為常用公式，這里只做用途功能介紹，想要詳細(xì)學(xué)習(xí)以上函數(shù)的基礎(chǔ)用法，請持續(xù)關(guān)注 EXCEL精選技巧的后續(xù)文章！

3、VLOOKUP函數(shù)已有文章做了詳細(xì)闡述，請參考技巧君的另一篇文章《連VLOOKUP這些用法都不會，別說你會EXCEL！》點(diǎn)擊技巧君頭像，查看歷史文章即可