如何將Excel函數(shù)運(yùn)用到身份證的查詢(xún)上
對(duì)于身份證大家肯定不會(huì)陌生，在Excel中經(jīng)常需要根據(jù)身份證號(hào)碼來(lái)提取一些信息，或者進(jìn)行判斷。光靠眼力去看，顯然不夠效率。以下是學(xué)習(xí)啦小編為您帶來(lái)的關(guān)于Excel函數(shù)身份證的查詢(xún)，希望對(duì)您有所幫助。
Excel函數(shù)身份證的查詢(xún)
類(lèi)型格式
15位身份證：前2位表示所屬省份代碼，3~6位表示所屬城市和區(qū)縣代碼，7～12表示出生日期，格式為YYMMDD，13～15位是個(gè)人順序碼，其中第15位可以標(biāo)識(shí)性別，為奇數(shù)表示男性，為偶數(shù)表示女性。
18位身份證：前2位表示所屬省份代碼，3~6位表示所屬城市和區(qū)縣代碼，7～14表示出生日期，格式為YYYYMMDD，15～17位是個(gè)人順序碼，其中第17位可以標(biāo)識(shí)性別，為奇數(shù)表示男性，為偶數(shù)表示女性。第18位是校驗(yàn)位，由前17位通過(guò)計(jì)算求得。
正確輸入
因?yàn)镋xcel單元格只支持15位有效數(shù)字，輸入多了就不能完全顯示。這樣就導(dǎo)致新一代身份證號(hào)碼輸入時(shí)不便，為了解決這個(gè)問(wèn)題，我們可以采取文本輸入的方法，可以在輸入號(hào)碼之前，先添加一個(gè)半角的單引號(hào)再輸入其他數(shù)字，這樣完成輸入的結(jié)果就是一個(gè)文本型數(shù)據(jù)。或者也可以在輸入之前事先將單元格格式設(shè)置為文本再行輸入。但是如果在輸入完成以后再更改單元格格式就不會(huì)有效果。
自動(dòng)驗(yàn)證輸入
①條件1：輸入長(zhǎng)度為15位或18位，函數(shù)公式可以這樣寫(xiě)：=OR(LEN(A1)=15,LEN(A1)=18)
②條件2：前17位必須都是數(shù)字，公式：=ISNUMBER(-LEFT(A1,17))
③條件3：如果不全都是數(shù)字，那么它只能是18位，并且末尾字符是字母“X”，公式：=OR(ISNUMBER(-A1),AND(LEN(A1)=18,RIGHT(A1)="X"))
提取生日
①提取6位或8位生日數(shù)字，=MID(A1,7,IF(LEN(A1)=15,6,8))
②對(duì)于15位號(hào)碼，需要補(bǔ)足前面兩位“19”數(shù)字，=RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8)
③將上面得到的8位數(shù)字轉(zhuǎn)換成真實(shí)日期數(shù)值，=TEXT(RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8),"0-00-00")+0
計(jì)算年齡
年齡的計(jì)算實(shí)際上就是通過(guò)前面得到的出生日期來(lái)用DATEDIF函數(shù)計(jì)算到當(dāng)前所相差的年份數(shù)(周歲)：
=DATEDIF(TEXT(RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8),"0-00-00"),NOW(),"Y")
判斷性別
①提取數(shù)字：=MID(A1,15,3)，對(duì)于15位身份證號(hào)碼，上述公式提取到是其末位數(shù)字，不包含其他字符占位。而對(duì)于18位的身份證號(hào)碼，上述公式提取到的是其15～17位數(shù)字。
②判斷奇偶性：=IF(MOD(MID(A1,15,3),2), "男","女")。通過(guò)MOD函數(shù)除以2取余數(shù)來(lái)進(jìn)行奇偶判斷，如果余數(shù)為1，表示奇數(shù)，得到男性判斷，如果余數(shù)為0，得到女性判斷。
上面大篇幅的介紹了15位和18位身份證號(hào)碼的函數(shù)公式運(yùn)用，自從2013年1月1日開(kāi)始，第一代身份證已經(jīng)不允許屬于，這樣我們函數(shù)公式也能夠簡(jiǎn)化：
驗(yàn)證輸入：=AND(LEN(A1)=18,ISNUMBER(-LEFT(A1,17)),OR(ISNUMBER(-A1),RIGHT(A1)="X"))
提取生日：=TEXT(MID(A1,7,8),"0-00-00")+0
計(jì)算年齡：=DATEDIF(TEXT(MID(A1,7,8),"0-00-00"),NOW(),"Y")
判別性別：=IF(-1^MID(A1,15,3)=1,"女","男")
猜你喜歡：
如何將Excel函數(shù)運(yùn)用到身份證的查詢(xún)上
