Q1：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精選技巧→點擊技巧君的綠色頭像→發送私信【身份證號查詢】即可獲取
關注EXCEL精選技巧，每天學習3分鍾，堅持一個月，你將大不同Q2：excel中知道身份證號如何計算年齡
身份證號碼在A1，那麼可以用下面的公式計算年齡：
=DATEDIF(TEXT(MID(A1,7,INT(LEN(A1)/2-1)),"#-00-00"),TODAY(),"Y")www.∴BazhiShI.com
Q3：excel中知道身份證號如何計算年齡？
1、可以用YEAR函數來取得當前的年份=YEAR(NOW())
2、18位身份證號從第7位開始的8位數字代表出生日期。出生的年份是從第7位開始的4位數字，用MID函數將出生年份提取出來
3、Mid是VisualBasic和MicrosoftExcel中的一個字符串函數，作用是從一個字符串中截取出指定數量的字符
4、將上述兩個部分合並，就得到了B2的最終公式：
=YEAR(NOW())-MID(A2,7,4)，
具體操作步驟
1、打開要操作的excel文檔
2、在要計算年齡的單元格內輸入YEAR(TODAY())-MID(A2,7,4)，其中A2為身份證號的單元格
3、輸入計算公式後，按回車鍵，即可計算出年齡
4、向下填充。即可得到年齡
Q4：excel里用身份證號計算年齡怎麼出來全是#VALUE!?
你是不是用mid(a1,7,8)這樣的公式提取的出生日期？
那樣得到的是文本，不能用於日期計算，改一下：
=DATEDIF(TEXT(19*(LEN(A1)=15)&MID(A1,7,8-(LEN(A1)=15)*2),"0-00-00"),TODAY(),"y")
（15、18位號碼通用）
Q5：在EXCEL里有出生月份或者身份證號了 怎麼算出年齡
設A1文本格式的18位身份證號,B1輸入如下公式,即可算出當天的年齡.
=YEAR(TODAY()-DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)))-1900&"歲"&MONTH(TODAY()-DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)))-1&"月"&DAY(TODAY()-DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)))&"天"
如要算指定日期的年齡,則設A1文本格式的18位身份證號,B1為指定日期,在C1輸入公式如下:
=YEAR(B1-DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)))-1900&"歲"&MONTH(B1-DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)))-1&"月"&DAY(B1-DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)))&"天"
Q6：excel中怎樣用身份證號計算年齡
excel中使用身份證號計算年齡，可透過YEAR，TODAY獲得當前年份，使用MID函數獲得身份證的出身年份，兩者相減即可得到年齡。
方法步驟如下：
1、打開需要操作的EXCEL表格，在任意單元格函數編輯框輸入：=YEAR（TODY（））-MID（，然後點擊選擇身份證號碼所在單元格。
2、在公式編輯框繼續輸入7，4），並按回車完成公式的輸入。
3、返回EXCEL表格，可發現已成功透過身份證號碼計算出年齡。
WwW.BazhIshi.Co☆m
Q7：Excel中，如何根據身份證號提取年齡、生日、性別信息？
身份證號中包含個人的生日、年齡、性別、籍貫等信息，利用Excel能夠從身份證號中批量提取！
文末有示例文件獲取方法哦
身份證號的構成身份證號有18位，排列順序從左至右依次為：六位數字地址碼，八位數字出生日期碼，三位數字順序碼和一位數字校驗碼。順序碼的奇數分給男性，偶數分給女性。
從這個構成中，我們可以發現，只需要提取對應的編碼就可以提取出身份證號中的個人信息！
提取性別=IF(MOD(MID(B2,17,1),2),"男","女")說明：MID(B2,17,1)，提取第17位數字MOD(MID(B2,17,1),2)，對2取余，奇數余數得1，偶數余數為0提取出生年月=--TEXT(MID(B2,7,8),"0-00-00")說明：MID(B2,7,8)，提取第7位開始後面的8位數字，即出生年月日編碼TEXT(MID(B2,7,8),"0-00-00")，轉換為年-月-日的格式--TEXT(MID(B2,7,8),"0-00-00")，減負運算換算為真正的日期提取實歲=DATEDIF(TEXT(MID(B2,7,8),"0-00-00"),TODAY(),"Y")說明：DATEDIF(TEXT(MID(B2,7,8),"0-00-00"),TODAY(),"Y")，計算當前日期和出生日期的年差值，即實際歲數提取虛歲=(YEAR(NOW())-MID(B2,7,4))說明：MID(B2,7,4)：提取第7位數開始的4位數字，即出生年份YEAR(NOW())：計算當前日期的年份(YEAR(NOW())-MID(B2,7,4))：虛歲=當前年份-出生年份提取籍貫=VLOOKUP(--MID(B2,1,6),籍貫編碼!B:C,2,0)說明：--MID(B2,1,6)，提取前六位地址編碼VLOOKUP(--MID(B2,1,6),籍貫編碼!B:C,2,0)，在地址編碼表里找到籍貫示例文件獲取：點擊右上角紅色按鈕關注“Excel精選技巧”，然後私信發送【身份證號提取個人信息】即可
關注Excel精選技巧，每天學習3分鍾，堅持一個月，你將大不同！WwW.∽baZHISHi.CoM
