【摘 要】本文通過(guò)用Excel處理人事系統(tǒng)中的身份證號(hào)，提取出籍貫、年齡、性別等特定的個(gè)人信息，豐富了Office辦公軟件在日常工作和生活中的應(yīng)用。

【關(guān)鍵詞】Excel2013；人事系統(tǒng)；身份證號(hào)

一.引言

無(wú)論是企事業(yè)單位，還是公司，在人事工作方面，經(jīng)常會(huì)遇到身份證信息提取的相關(guān)問(wèn)題。目前中國(guó)大陸已停止使用一代身份證，廣泛使用二代身份證。二代身份證共18位號(hào)碼，比較于一代身份證，二代身份證補(bǔ)足了四位的年號(hào)日期，并在末尾添加了一位校驗(yàn)字符。

二.身份證號(hào)的主要信息特征

二代身份證號(hào)碼格式如下：前兩位表示證件所屬省份代碼，如“13”代表河北省。3至6位表示證件所屬城市、區(qū)（縣）代碼，如“0283”代表遷安市。7至14表示證件所有人的出生日期，格式為“yyyymmdd”，如19860118。15至17位是個(gè)人分配順序碼，其中第17位標(biāo)識(shí)性別，奇數(shù)表示男性，偶數(shù)表示女性。第18位是校驗(yàn)碼，由計(jì)算機(jī)根據(jù)一定規(guī)則的運(yùn)算產(chǎn)生，目的是檢測(cè)身份證號(hào)碼正確與否。

三.Excel2013在處理身份證號(hào)中的應(yīng)用

（一）.正確輸入身份證號(hào)碼

Excel單元格最多只支持15位有效數(shù)字，當(dāng)輸入18位的身份證號(hào)時(shí)，若不設(shè)置單元格格式，會(huì)默認(rèn)按數(shù)值處理，后三位會(huì)自動(dòng)轉(zhuǎn)換成0，這樣會(huì)導(dǎo)致身份證號(hào)碼輸入錯(cuò)誤。有兩種方法可以避免這種錯(cuò)誤。

方法一：在輸入號(hào)碼前，先輸入一個(gè)半角的單引號(hào)，再輸入身份證號(hào)碼，這樣身份證號(hào)會(huì)按文本型數(shù)據(jù)處理。

方法二：先將要輸入身份證的單元格區(qū)域的格式設(shè)置為文本類(lèi)型，再輸入身份證號(hào)碼。但如果先完成輸入再改單元格格式，仍會(huì)造成后三位自動(dòng)轉(zhuǎn)換成0。接下來(lái)對(duì)身份證號(hào)碼信息的提取，都默認(rèn)是按第二種方法輸入的。

（二）.提取省份區(qū)縣

步驟1：使用LEFT函數(shù)提取身份證號(hào)的前6位。

= LEFT（A2，6）

步驟2：使用VLOOKUP函數(shù)，在“身份證號(hào)前6位編碼表”中找出前6位編碼所對(duì)應(yīng)的省市區(qū)縣，將結(jié)果顯示在B2單元格。

=VLOOKUP（LEFT（A2，6），身份證前6位編碼表！$A$1：$B$1988，2，F(xiàn)ALSE）

敲回車(chē)完成輸入，可以看到身份證號(hào)為130283198601183592的人，籍貫是河北省遷安市。

（三）.提取出生日期

在身份證數(shù)據(jù)信息提取時(shí)，經(jīng)常會(huì)用到從身份證號(hào)里提取出生日期。出生日期位于18位身份證號(hào)碼的7至14位，其中，7～10位表示年份，11、12位表示月份，13、14位表示日。

方法一：

步驟1：用MID函數(shù)分別提取7～10位、11-12位、13-14位。=MID（A2，7，4） =MID（A2，11，2） =MID（A2，13，2）步驟2：用date函數(shù)將年、月、日組成日期，即得出生日期。

操作如下：

選定C2單元格，在公式編輯欄輸入“=DATE（MID（A2，7，4），MID（A2，11，2），MID（A2，13，2））”，敲回車(chē)完成輸入。還可以選定C2單元格，右鍵“設(shè)置單元格格式”，更改日期類(lèi)型的顯示格式。如改成“1986年1月18日”。

方法二：

步驟1：應(yīng)用MID函數(shù)提取身份證的7～14位。

=MID（A2，7，8）

步驟2：應(yīng)用TEXT函數(shù)，將取出的8位字符轉(zhuǎn)換為日期格式。

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

（四）.計(jì)算年齡

在實(shí)際應(yīng)用中還經(jīng)常會(huì)用到根據(jù)身份證號(hào)求員工的年齡。如對(duì)上面身份證號(hào)計(jì)算年齡，有兩種方法：

方法一：

用當(dāng)前的年份減去身份證中提取的年份。公式如下：

=YEAR（TODAY（））-MID（A2，7，4）

結(jié)果為28。

但是這樣計(jì)算年齡并不總是精確，比如，一個(gè)2013年12月31日出生的小朋友，在2014年1月1日出生僅兩天，應(yīng)用上述公式計(jì)算年齡卻為1周歲。所以，為了更精確的計(jì)算年齡（周歲），應(yīng)使用如下方法。

方法二：

用當(dāng)前日期減去圖3提取的“出生日期”，因?yàn)樵贓xcel中，兩個(gè)日期相減得出是兩個(gè)日期間差的天數(shù)，所以，再除以365，最后應(yīng)用INT函數(shù)對(duì)結(jié)果做向下取整，即得出實(shí)際年齡（周歲）。所用公式如下：

=INT（（TODAY（）-C2）/365）

（五）.提取性別

18位身份證號(hào)碼第17位奇數(shù)表示性別為“男”，偶數(shù)表示性別為“女”。所以要提取性別，可采用如下步驟。

步驟1：應(yīng)用MID函數(shù)提取第17位。

= MID（A2，17，1）），2）

步驟2：應(yīng)用MOD函數(shù)將步驟1取出的第17位除以2取余數(shù)。

= MOD（MID（A2，17，1），2）

步驟3：應(yīng)用IF函數(shù)，對(duì)上述結(jié)果進(jìn)行判斷，余數(shù)是1輸出“男”，0輸出“女”。

=IF（MOD（MID（A2，17，1），2），”男\(zhòng)"，\"女\"）

或者，通過(guò)以-1為底的指數(shù)來(lái)進(jìn)行判斷，如果結(jié)果為-1，說(shuō)明其指數(shù)是奇數(shù)，為1說(shuō)明指數(shù)是偶數(shù)。所用公式如下：

=IF（-1^MOD（MID（A2，17，1），2）=-1，”男\(zhòng)"，\"女\"）

四.結(jié)論

Excel2013是微軟最新的Office辦公套裝軟件的一個(gè)重要的組成，它能夠進(jìn)行除了身份證號(hào)之外各種復(fù)雜數(shù)據(jù)的處理、分析統(tǒng)計(jì)等操作，在金融管理、財(cái)經(jīng)統(tǒng)計(jì)、日常生活等眾多領(lǐng)域都有應(yīng)用。