用好身份證中的信息

沈海燕

我們知道，身份證號(hào)碼包含著很多重要的個(gè)人信息，比如，性別、年齡、生日、所在地。在Excel2013中，我們可以借助函數(shù)方便地從身份證中提取相關(guān)信息。

小知識(shí)

中國(guó)人民共和國(guó)公民第二代身份證號(hào)碼，按照順序從左至右依次為：6位數(shù)字地址碼、8位數(shù)字出生年月碼、3位數(shù)字順序碼和1位數(shù)字檢驗(yàn)碼。

Tips

出生年月所在列的格式要設(shè)置為“日期”，選中所在列，右擊選擇“設(shè)置單元格格式”，在“數(shù)字”下選擇“日期”，再選擇一種日期的類型。

提取出生年月

身份證號(hào)碼中，從第7位開始就是出生年月日，15位身份證號(hào)包含6位生日碼，18位身份證號(hào)包含8位生日碼，只要確定身份證號(hào)的位數(shù)，就可以使用Excel中的函數(shù)獲取具體的性別。

將身份證號(hào)碼輸入Excel電子表格中，在生日一列中輸入如下函數(shù)（如圖1）：

=TEXT（RIGHT（19&MID（B2，7，6+（LEN（B2）=18）*2），8），"0-00-00"）+0

這里的B2表示身份證號(hào)碼所在列，MID（B2，7，6+（LEN（B2）=18）*2）是判斷身份證號(hào)碼的位數(shù)，18位取8位，不然則提取6位，如果是6位則自動(dòng)在前面添加19，最后通過通過text函數(shù)將8位日期文本轉(zhuǎn)換成日期格式的數(shù)據(jù)。

如果身份證號(hào)是18位，那提取生日的公式就是：

=TEXT（MID（B2，7，8），"0-00-00"）+0

判定個(gè)人性別

身份證號(hào)碼中出生年月的后3位是順序碼，其中奇數(shù)表示男性，偶數(shù)表示女性，因此可以根據(jù)順序碼自動(dòng)判定性別。

加入身份證號(hào)碼從B2列開始，則公式應(yīng)該這么寫（如圖2）：

=IF（MOD（MID（B2，15+（LEN（B2）=18）*2，1），2），"男"，"女"）

這里主要是通過MOD對(duì)順序碼進(jìn)行判斷，奇數(shù)自動(dòng)判定為男性，偶數(shù)自動(dòng)判定為女性。

獲取真實(shí)年齡

我們還可以根據(jù)身份證號(hào)碼獲取真實(shí)年齡，這里可通過DATEDIF函數(shù)從身份證號(hào)碼中獲取真實(shí)年齡。

打開Excel文檔，在年齡一列，輸入如下函數(shù)（如圖3）：

=DATEDIF（TEXT（MID（B2，7，LEN（B2）/2-1），"0-00-00"），NOW（），"y"）

識(shí)別所在地區(qū)

身份證的前6位是地址碼，地址碼中包含常住戶口所在行政區(qū)代碼，行政區(qū)域代碼可以到網(wǎng)上搜索獲取，比如，江蘇省的行政區(qū)域代碼就是32，北京市的行政區(qū)域代碼是11，具體可參閱《國(guó)家行政區(qū)域的劃分及省份代碼》：http：//url.cn/KdZ1TL。

在Excel中創(chuàng)建2個(gè)工作表，其中一個(gè)是個(gè)人信息，另外一個(gè)是區(qū)域代碼（如圖4），全選“區(qū)域代碼”單元格，在“名稱框”中輸入Province，這樣就可以通過身份中的行政代碼判定所在省份。

切換到個(gè)人信息工作表，在省份所在列中輸入（如圖5）：

=VLOOKUP（LEFT（B2，2），Province，2，0）

借助Province區(qū)域代碼表，通過函數(shù)就能自動(dòng)配對(duì)并識(shí)別判定所在省份。

升級(jí)老身份證

對(duì)于15位的身份證號(hào)碼，我們可以根據(jù)一代身份證省級(jí)至二代身份證的規(guī)則和算法，將15位身份證號(hào)碼升級(jí)為18位身份證號(hào)碼。

打開電子表格，在18位身份證號(hào)碼中輸入以下函數(shù)（如圖6）：

=REPLACE（B2，7，，19）&MID（"10X98765432"，1+MOD（SUMPRODUCT（MID（REPLACE（B2，7，，19），ROW（$1：$17），1）*MOD（2^（18-ROW（$1：$17）），11）），11），1）

這樣我們可以看到，新身份證號(hào)碼會(huì)增加在日期增加19，在最后增加識(shí)別碼，非常智能。

身份證號(hào)碼包含的信息，與Excel中函數(shù)結(jié)合起來應(yīng)用，可以滿足我們更多需求，特別是在統(tǒng)計(jì)個(gè)人信息時(shí)，我們只要有身份證號(hào)碼，就可以自動(dòng)獲取到她的性別、生日等，可以事半功倍地進(jìn)行高效辦公。