近段時(shí)間，各校都忙壞了，學(xué)生城鎮(zhèn)居民醫(yī)療保險(xiǎn)材料要保，今年必須提供每位學(xué)生的身份證號(hào)碼，還有性別、出生日期等信息；新生學(xué)籍要報(bào)，學(xué)籍信息包含50多項(xiàng)，其中也有身份證號(hào)碼、性別、出生日期等信息……。為了簡(jiǎn)化工作，提高效率，特整理出從身份證號(hào)碼里提取出生年月日及性別等信息的方法，希望對(duì)大家有所幫助。
一、分析身份證號(hào)碼
其實(shí)，身份證號(hào)碼與一個(gè)人的性別、出生年月、籍貫等信息是緊密相連的，無(wú)論是15位還是18位的身份證號(hào)碼，其中都保存了相關(guān)的個(gè)人信息。
15位身份證號(hào)碼：第7、8位為出生年份(兩位數(shù))，第9、10位為出生月份，第11、12位代表出生日期，第15位代表性別，奇數(shù)為男，偶數(shù)為女。
18位身份證號(hào)碼：第7、8、9、10位為出生年份(四位數(shù))，第11、第12位為出生月份，第13、14位代表出生日期，第17位代表性別，奇數(shù)為男，偶數(shù)為女。
例如，某學(xué)生的身份證號(hào)碼(15位)是320521198908070242，那么表示1989年8月7日出生，性別為女。如果能想辦法從這些身份證號(hào)碼中將上述個(gè)人信息提取出來(lái)，不僅快速簡(jiǎn)便，而且不容易出錯(cuò)，核對(duì)時(shí)也只需要對(duì)身份證號(hào)碼進(jìn)行檢查，肯定可以大大提高工作效率。
二、轉(zhuǎn)換身份證號(hào)碼格式
我們先將學(xué)生的身份證號(hào)完整地輸入到EXCEL表格中，這時(shí)默認(rèn)為“數(shù)字”格式（單元格內(nèi)顯示的是科學(xué)記數(shù)法的格式），需要更改一下數(shù)字格式。選中該列中的所有身份證號(hào)后，右擊鼠標(biāo)，選擇“設(shè)置單元格格式”。在彈出對(duì)話(huà)框中“數(shù)字”標(biāo)簽內(nèi)的“分類(lèi)”設(shè)為“文本”，然后點(diǎn)擊確定。
三、提取個(gè)人信息
這里，我們需要使用IF、LEN、MOD、MID、DATE等函數(shù)從身份證號(hào)碼中提取個(gè)人信息。假設(shè)學(xué)生的身份證號(hào)碼信息已輸入完畢(C列)，出生年月信息填寫(xiě)在D列，性別信息填寫(xiě)在B列。
1.提取出生年月信息
如果上交報(bào)表時(shí)需要填寫(xiě)出生年月日，我們需要關(guān)心身份證號(hào)碼的相應(yīng)部位即可，即顯示為“19890807”這樣的信息。在D2單元格中輸入公式
=IF(LEN(C2)=15,"19"&MID(C2,7,6),MID(C2,7,8))，其中：
文本函數(shù)MID返回文本字符串中從指定位置開(kāi)始的特定數(shù)目的字符，該數(shù)目由用戶(hù)指定。語(yǔ)法：MID(text,start_num,num_chars)即MID(文本,開(kāi)始字符,所取字符數(shù))，
Text 是包含要提取字符的文本字符串。
Start_num 是文本中要提取的第一個(gè)字符的位置。文本中第一個(gè)字符的start_num 為1，以此類(lèi)推。
Num_chars 指定希望 MID 從文本中返回字符的個(gè)數(shù)。
字符個(gè)數(shù)計(jì)算函數(shù)LEN返回文本字符串中的字符數(shù)。語(yǔ)法：LEN(text)
Text 是要查找其長(zhǎng)度的文本。空格將作為字符進(jìn)行計(jì)數(shù)。
LEN(C2)=15：檢查C2單元格中字符串的字符數(shù)目，本例的含義是檢查身份證號(hào)碼的長(zhǎng)度是否是15位。
MID(C2,7,4)：從C2單元格中字符串的第7位開(kāi)始提取6位數(shù)字，本例中表示提取15位身份證號(hào)碼的第7、8、9、10、11、12位數(shù)字。
"19"&:表示如果身份證號(hào)碼是15位，在年份前加“19”。
MID(C2,7,8)：從C2單元格中字符串的第7位開(kāi)始提取8位數(shù)字，本例中表示提取18位身份證號(hào)碼的第7、8、9、10、11、12、13、14位數(shù)字。
IF(LEN(C2)=15,MID(C2,7,6),MID(C2,7,8))：IF是一個(gè)邏輯判斷函數(shù)，表示如果C2單元格是15位，則提取第7位開(kāi)始的6位數(shù)字并在年份前加“19”，如果不是15位則提取自第7位開(kāi)始的8位數(shù)字。
如果需要顯示為“1989-08-07”這樣的格式，可以把上面的公式稍加修改即可：
=IF(LEN(C2)=15,"19"&MID(C2,7,2)&"-"&MID(C2,9,2)&"-"&MID(C2,11,2),MID(C2,7,4)&"-"&MID(C2,11,2)&"-"&MID(C2,13,2))。
解釋?zhuān)喝绻矸葑C號(hào)碼是15位，則從第7位開(kāi)始取兩位數(shù)字，并在這兩位數(shù)字前加“19”，后面加“-”,從第9位開(kāi)始取兩位數(shù)字，并在這兩位數(shù)字后面加“-”,從第11位開(kāi)始取兩位數(shù)字。否則，從第7位開(kāi)始取四位數(shù)字，并在這四位數(shù)字后面加“-”,從第11位開(kāi)始取兩位數(shù)字，并在這兩位數(shù)字后面加“-”,從第13位開(kāi)始取兩位數(shù)字。
也可以使用DATE格式，并在“單元格格式→日期”中進(jìn)行設(shè)置。
如果學(xué)生的身份證號(hào)全部都是18位的新一代身份證，可以以把上面的公式進(jìn)行簡(jiǎn)化：
=MID(C2,7,4)&"-"&MID(C2,11,2)&"-"&MID(C2,13,2)
2. 提取性別信息
由于報(bào)表中學(xué)生不是按照男、女固定的順序進(jìn)行編排，如果一個(gè)一個(gè)手工輸入的話(huà)，既麻煩又容易出錯(cuò)。
例如性別信息統(tǒng)一在B列填寫(xiě)，可以在B2單元格中輸入公式：
=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女")
其中：
LEN(C2)=15：檢查身份證號(hào)碼的長(zhǎng)度是否是15位。
MID(C2,15,1)：如果身份證號(hào)碼的長(zhǎng)度是15位，那么提取第15位的數(shù)字。
MID(C2,17,1)：如果身份證號(hào)碼的長(zhǎng)度不是15位，即18位身份證號(hào)碼，那么應(yīng)該提取第17位的數(shù)字。
MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)：用于得到給出數(shù)字除以指定數(shù)字后的余數(shù)，本例表示對(duì)提出來(lái)的數(shù)值除以2以后所得到的余數(shù)。
IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女")：如果除以2以后的余數(shù)是1，那么B2單元格顯示為“男”，否則顯示為“女”。
回車(chē)確認(rèn)后，即可在B2單元格顯示正確的性別信息，接下來(lái)就是選中填充柄直接拖曳?，F(xiàn)在這份報(bào)表無(wú)論是提取信息或是核對(duì)，都方便多了。
提取性別也可以使用下面這個(gè)公式（無(wú)論是15位還是18位）：
=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女")
如果學(xué)生的身份證號(hào)全部都是18位的新一代身份證，可以使用下面的公式：
=IF(MID(C2,17,1)/2=TRUNC(MID(C2,17,1)/2),"女","男")
數(shù)學(xué)函數(shù)TRUNC將數(shù)字的小數(shù)部分截去，返回整數(shù)。
語(yǔ)法：TRUNC(number,num_digits)
Number 需要截尾取整的數(shù)字。
Num_digits 用于指定取整精度的數(shù)字。Num_digits 的默認(rèn)值為 0。
說(shuō)明：函數(shù) TRUNC 和函數(shù) INT類(lèi)似，都返回整數(shù)。函數(shù) TRUNC 直接去除數(shù)字的小數(shù)部分，而函數(shù) INT則是依照給定數(shù)的小數(shù)部分的值，將其四舍五入到最接近的整數(shù)。函數(shù) INT 和函數(shù) TRUNC在處理負(fù)數(shù)時(shí)有所不同。
如果身份證號(hào)的輸入已是15或18位，還可以用公式：
=IF(MOD(LEFT(RIGHT(C2,(LEN(C2)=18)+1)),2),"男","女"）
RIGHT 根據(jù)所指定的字符數(shù)返回文本字符串中最后一個(gè)或多個(gè)字符。
語(yǔ)法：RIGHT(text,num_chars)
Text 是包含要提取字符的文本字符串。
Num_chars 指定希望 RIGHT提取的字符數(shù)。
LEFT 基于所指定的字符數(shù)返回文本字符串中的第一個(gè)或前幾個(gè)字符。
語(yǔ)法：LEFT(text,num_chars)
Text 是包含要提取字符的文本字符串。
Num_chars 指定要由 LEFT所提取的字符數(shù)。
