用好身份證中的信息

沈海燕

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

小知識

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

Tips

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

提取出生年月

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

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

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

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

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

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

判定個人性別

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

加入身份證號碼從B2列開始，則公式應該這么寫（如圖2）：

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

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

獲取真實年齡

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

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

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

識別所在地區(qū)

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

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

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

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

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

升級老身份證

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

打開電子表格，在18位身份證號碼中輸入以下函數(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）

這樣我們可以看到，新身份證號碼會增加在日期增加19，在最后增加識別碼，非常智能。

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