Excel如何從身份證號碼中提取計算出生日期、性別及年齡呢？

一個身份證號竟然包含這麼多信息，你還敢隨便泄露你的身份證號嗎？

從事人事工作的朋友們少不了與身份證號打交道，數據量大的時候如何批量操作能？

【本文示例文件獲取方法見文末】

今天，技巧君來講講身份證號！國際慣例，先來看下效果：

身份證號構成

身份證號由四部分組成：

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

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

順序碼 ：表示在同一地址碼所標識的區域範圍內，對同年、同月、同日出生的人編定的順序號，順序碼的奇數分配給男性，偶數分配給女性

校驗碼：根據前面十七位數字碼，按照ISO 7064:1983.MOD 11-2校驗碼計算出來的檢驗碼。

所以從身份證號中提取信息，我們需要對身份證號進行分段處理，要從地址碼中對應提取出籍貫，必須有一個地區和編碼之間的編碼表，下面來看一下具體怎麼製作吧！

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

說明：

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

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

c、外層再嵌套IFERROR規避錯誤值

身份證號提取年齡

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

說明：

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

b、TEXT函數將文本處理為日期格式，--減負運算將文本轉化為日期值

c、DATEDIF函數計算出生日期和今天的差值，即得到年齡

身份證號提取出生日期

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

說明：

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

b、TEXT函數處理為日期格式

身份證號提取生肖

函數：MID("鼠牛虎兔龍蛇馬羊猴雞狗豬",MOD(MID(A2,7,4)-4,12)+1,1)

說明：

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

b、MOD函數對12取餘數，剛好對應12生肖的序列代碼，裡面日期的-4和外面的 +1是為了和前面的生肖相對應

身份證號提取星座

函數：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)

說明：這個函數主要是了解星座和日期之間的換算關係，有興趣可以做進一步了解，因計算規則較為複雜，這裡不做詳述

身份證號提取籍貫信息

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

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

注意事項：

1、本文所用所有身份證號均為地區碼+出生日期碼+順序碼+校驗碼隨機組合而成，非真實身份證號，如有雷同，純屬巧合！

2、本文涉及的主要函數有：MID、MOD、TEXT、IF、VLOOKUP、DATEDIF、IFERROR、VALUE、內存數組等，這些公式都為常用公式，這裡只做用途功能介紹，想要詳細學習以上函數的基礎用法，請持續關注 EXCEL精選技巧的後續文章！

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

點擊右上角紅色關注 EXCEL精選技巧→點擊技巧君的綠色頭像→發送私信【身份證號查詢】即可獲取

推薦閱讀：