引言:用Excel處理身份證號(hào),在我們?nèi)粘9ぷ髦惺窍喈?dāng)普遍的,尤其是對(duì)于做人事行政工作、財(cái)務(wù)工作的同學(xué)來說,更顯得十分重要。那么一個(gè)身份證號(hào),能給予我們多少信息量呢?這也算是一個(gè)老生常談的問題,但是最近在群里又總見到有同學(xué)問及,那么今天我們就再來做一個(gè)總結(jié),看一看Excel函數(shù)對(duì)于身份證號(hào)碼對(duì)應(yīng)的各個(gè)信息的處理方式吧。 ***附加知識(shí): 無論我們需要用Excel處理何種數(shù)據(jù),首先都應(yīng)先對(duì)該數(shù)據(jù)有一個(gè)了解,在不了解它的情況下就直接處理數(shù)據(jù),有可能會(huì)把方向做錯(cuò),那樣的數(shù)據(jù)處理結(jié)果就令人堪憂了。 我們都知道身份證號(hào)碼一共有18位(以前的身份證號(hào)碼是15位的,但是普及二代身份證這么多年,可以說一代的15位身份證已經(jīng)極少使用了,所以今天主要講解二代身份證的內(nèi)容),身份證號(hào)碼按照“號(hào)碼段”區(qū)分大體可以分為6個(gè)信息: ● 1~2位省、自治區(qū)、直轄市代碼; ● 3~4位地級(jí)市、盟、自治州代碼; ● 5~6位縣、縣級(jí)市、區(qū)代碼; ● 7~14位出生年月日;其中 7~10位,代表出生年份 11~12位,代表出生月份 13~14位,代表出生日期 ● 15~17位為順序號(hào),其中17位(倒數(shù)第二位)是奇數(shù)則為男性,偶數(shù)為女性; ● 18位為校驗(yàn)碼,0-9和X。 我們輔以圖示更加直觀的記憶一下: 應(yīng)該說我們的身份證號(hào)中能有這么多的信息量,當(dāng)初研究出這套編碼規(guī)則的人也真是相當(dāng)?shù)牟蝗菀琢恕?/p> ********* 有了上面對(duì)于身份證的一個(gè)了解,我們就可以通過這個(gè)號(hào)碼提取很多的內(nèi)容了。 一、省、市、縣自動(dòng)提取 函數(shù)解析: 對(duì)于這個(gè)函數(shù)來說,本身并不復(fù)雜,關(guān)鍵是這幾張信息表。 不是每一個(gè)同學(xué)都有的,現(xiàn)在你也可以擁有這么一套代碼對(duì)照表了,有需要的同學(xué),可以在看完此文篇文章之后,加入下方提供的部落窩QQ群,下載即可。 公式中所使用的MID函數(shù)是典型的字符串分段取值的函數(shù)。 MID函數(shù)語法: =MID(text,start_num,num_chars) 翻譯成白話就是在一個(gè)字符串(text)中,從某個(gè)序號(hào)位置(start_num)開始,提取之后連續(xù)的幾個(gè)(num_chars)字符。 那我們?cè)賮砜匆幌律厦娴暮瘮?shù): B5單元格=VLOOKUP(MID($B$2,1,2),省級(jí)碼對(duì)照表!A:B,2,0) MID函數(shù)在B2單元格的身份證號(hào)中,提取出從第1位開始的連續(xù)兩位字符,就是“51”;再把“51”帶入到VLOOKUP函數(shù)中,并在《省級(jí)碼對(duì)照表》中的A、B兩列處進(jìn)行索引即可。B7、B9單元格的市、縣索引方式也是一樣的,但是一定要記住,索引省,用到身份證的前兩位;索引市,要用到身份證的前4位。索引出XX省XX市的格式,不能直接使用第3~4位,因?yàn)闀?huì)和其他的省市碼重復(fù);縣級(jí)代碼同理,需要索引身份證號(hào)的前6位,不可單獨(dú)索引第5~6位。 當(dāng)然有些同學(xué)會(huì)問,用其他函數(shù)可以嗎?針對(duì)第一個(gè)問題,用LEFT函數(shù)也可以,=VLOOKUP(LEFT($B$2,2),省級(jí)碼對(duì)照表!A:B,2,0) 但是建議同學(xué)們?cè)谏矸葑C處理問題上,還是只使用MID函數(shù)來提取號(hào)碼段,便于對(duì)身份證信息這一類問題的統(tǒng)一解決,而LEFT函數(shù)或者RIGHT函數(shù),只適合左右兩端的取值,對(duì)于中間部分的取值相對(duì)于MID函數(shù)就顯得有些麻煩了。 ***對(duì)于市、縣提取的更高要求: 剛才我們說過了,如果直接用第3~4位,或者第5~6位的代碼去索引,由于號(hào)碼有重復(fù),所以索引出的內(nèi)容有可能就是錯(cuò)誤的。但是在人事工作中,有時(shí)我們需要精準(zhǔn)的提取出XX市、XX區(qū)縣,那我們就可以使用下面的方式來處理。 B14單元格函數(shù): =SUBSTITUTE(VLOOKUP(MID($B$2,1,4),省市碼對(duì)照表!A:B,2,0),VLOOKUP(MID($B$2,1,2),省級(jí)碼對(duì)照表!A:B,2,0),"") 聰明的你,是不是一眼就可以看出來,紅色的VLOOKUP函數(shù),提取的是XX省XX市;藍(lán)色的VLOOKUP函數(shù),提取的是XX省,我們?cè)儆肧UBSTITUTE函數(shù),將紅色索引值中的XX省部分替換為空文本即可。縣的提取也是同理,很簡(jiǎn)單的思路,但是在工作中十分實(shí)用,趕緊學(xué)起來吧! 二、生日提取及應(yīng)用拓展 函數(shù)解析: 同樣使用MID函數(shù)提取代表出生日期的7~14位,再用TEXT函數(shù)改變格式為“0000-00-00”,最后以減負(fù)運(yùn)算將文本格式的日期改成數(shù)值形態(tài),再調(diào)整單元格格式,就是我們需要的“出生日期”。說著很簡(jiǎn)單,如果需要掌握這個(gè)函數(shù)的話,還是需要多運(yùn)用來幫助記憶才行的。關(guān)于TEXT詳細(xì)的使用方法,小伙伴們可以參考之前的教程《如果函數(shù)有職業(yè),TEXT絕對(duì)是變裝女皇!》,在此就不細(xì)說了。 當(dāng)然,我們也可以用下面的方式提取: 圖中“生日2”的公式分別利用MID函數(shù)在身份證號(hào)中提取出代表“出生年月日”的各個(gè)代碼段,再分別代入到DATE函數(shù)的year、month、day參數(shù)中即可。 雖說兩種方法的結(jié)果一樣,但是這是兩種思路,一個(gè)是整體提取,一個(gè)是分開提取,這在我們的實(shí)際工作中,有著很重要的作用。 ***對(duì)出生日期的應(yīng)用拓展: 1、通過身份證得出年齡 “年齡1”的算法依然使用了MID函數(shù),今年的年份【YEAR(TODAY())】減去出生的年份【MID(B2,7,4)】就是年齡了; “年齡2”的算法利用了DATEDIF函數(shù),計(jì)算兩個(gè)日期的年差,而開始日期的表示方法【--TEXT(MID(B2,7,8),"0000-00-00")】就是我們剛才用的提取出生日期的函數(shù)。關(guān)于DATEDIF詳細(xì)的使用方法,小伙伴們可以參考之前的教程《用上DATEDIF,您永不再缺席那些重要的日子!》,在此就不細(xì)說了。 2、判斷生肖(以身份證公歷生日判斷) 生肖每12年一個(gè)輪回,大家沒有必要背上面的對(duì)照碼,教給大家一個(gè)小技巧,以自己的出生年份除以12的余數(shù)作為基準(zhǔn),再順序排列其他11個(gè)生肖的對(duì)照碼就好了。 B13單元格函數(shù): =VLOOKUP(MOD(MID(B2,7,4),12),A16:B27,2,0) 又是利用MID函數(shù)提取年份的四位,再通過MOD函數(shù)得到除以12的余數(shù)作為索引值,接著利用VLOOKUP函數(shù)得到對(duì)照碼對(duì)應(yīng)的生肖。 3、判斷星座 使用MID函數(shù)提取身份證中的月份和日期四位數(shù)字的文本格式【MID(B2,11,4)】,再使用減負(fù)運(yùn)算,將其變更為數(shù)值形態(tài)。例如我們這個(gè)身份證號(hào)的11~14位是0322,數(shù)值形態(tài)就是322。值得注意的是,此時(shí)我們利用的并不是日期的序列值,而是一個(gè)常規(guī)形態(tài)的數(shù)值,通過對(duì)應(yīng)碼區(qū)域,利用VLOOKUP的模糊查詢得到對(duì)應(yīng)的星座。 ********* 編后語:介于篇幅問題,以上即為上篇,關(guān)于生日提取的拓展思路還沒有講完,下篇內(nèi)容我們繼續(xù)。 ****部落窩教育-excel數(shù)位函數(shù)技巧**** 原創(chuàng):E圖表述/部落窩教育(未經(jīng)同意,請(qǐng)勿轉(zhuǎn)載) |
|