對于身份證大家肯定不會陌生，在Excel中經常需要根據身份證號碼來提取一些信息，或者進行判斷。光靠眼力去看，顯然不夠效率。以下是學習啦小編為您帶來的關于Excel函數身份證的查詢，希望對您有所幫助。

　　Excel函數身份證的查詢

　　類型格式

　　15位身份證：前2位表示所屬省份代碼，3~6位表示所屬城市和區(qū)縣代碼，7～12表示出生日期，格式為YYMMDD，13～15位是個人順序碼，其中第15位可以標識性別，為奇數表示男性，為偶數表示女性。

　　18位身份證：前2位表示所屬省份代碼，3~6位表示所屬城市和區(qū)縣代碼，7～14表示出生日期，格式為YYYYMMDD，15～17位是個人順序碼，其中第17位可以標識性別，為奇數表示男性，為偶數表示女性。第18位是校驗位，由前17位通過計算求得。

　　正確輸入

　　因為Excel單元格只支持15位有效數字，輸入多了就不能完全顯示。這樣就導致新一代身份證號碼輸入時不便，為了解決這個問題，我們可以采取文本輸入的方法，可以在輸入號碼之前，先添加一個半角的單引號再輸入其他數字，這樣完成輸入的結果就是一個文本型數據?；蛘咭部梢栽谳斎胫笆孪葘卧窀袷皆O置為文本再行輸入。但是如果在輸入完成以后再更改單元格格式就不會有效果。

　　自動驗證輸入

　?、贄l件1：輸入長度為15位或18位，函數公式可以這樣寫：=OR(LEN(A1)=15,LEN(A1)=18)

　?、跅l件2：前17位必須都是數字，公式：=ISNUMBER(-LEFT(A1,17))

　?、蹢l件3：如果不全都是數字，那么它只能是18位，并且末尾字符是字母“X”，公式：=OR(ISNUMBER(-A1),AND(LEN(A1)=18,RIGHT(A1)="X"))

　　提取生日

　?、偬崛?位或8位生日數字，=MID(A1,7,IF(LEN(A1)=15,6,8))

　?、趯τ?5位號碼，需要補足前面兩位“19”數字，=RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8)

　　③將上面得到的8位數字轉換成真實日期數值，=TEXT(RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8),"0-00-00")+0

　　計算年齡

　　年齡的計算實際上就是通過前面得到的出生日期來用DATEDIF函數計算到當前所相差的年份數(周歲)：

　　=DATEDIF(TEXT(RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8),"0-00-00"),NOW(),"Y")

　　判斷性別

　　①提取數字：=MID(A1,15,3)，對于15位身份證號碼，上述公式提取到是其末位數字，不包含其他字符占位。而對于18位的身份證號碼，上述公式提取到的是其15～17位數字。

　?、谂袛嗥媾夹裕?IF(MOD(MID(A1,15,3),2), "男","女")。通過MOD函數除以2取余數來進行奇偶判斷，如果余數為1，表示奇數，得到男性判斷，如果余數為0，得到女性判斷。

　　上面大篇幅的介紹了15位和18位身份證號碼的函數公式運用，自從2013年1月1日開始，第一代身份證已經不允許屬于，這樣我們函數公式也能夠簡化：

　　驗證輸入：=AND(LEN(A1)=18,ISNUMBER(-LEFT(A1,17)),OR(ISNUMBER(-A1),RIGHT(A1)="X"))

　　提取生日：=TEXT(MID(A1,7,8),"0-00-00")+0

　　計算年齡：=DATEDIF(TEXT(MID(A1,7,8),"0-00-00"),NOW(),"Y")

　　判別性別：=IF(-1^MID(A1,15,3)=1,"女","男")

猜你喜歡：

1.excel 公式獲取行號的方法

2.excel怎么從身份證號碼中提取生日

3.excel身份證號碼格式提取生日

4.Excel2007中進行從身份證號中自動獲取出生日期的方法

5.Excel表格如何顯示完整身份證號