Excel2013在人事處理身份證號碼中的應(yīng)用

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

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

一.引言

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

二.身份證號的主要信息特征

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

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

（一）.正確輸入身份證號碼

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

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

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

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

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

= LEFT（A2，6）

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

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

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

（三）.提取出生日期

在身份證數(shù)據(jù)信息提取時，經(jīng)常會用到從身份證號里提取出生日期。出生日期位于18位身份證號碼的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））”，敲回車完成輸入。還可以選定C2單元格，右鍵“設(shè)置單元格格式”，更改日期類型的顯示格式。如改成“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

（四）.計算年齡

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

方法一：

用當前的年份減去身份證中提取的年份。公式如下：

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

結(jié)果為28。

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

方法二：

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

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

（五）.提取性別

18位身份證號碼第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ù)，對上述結(jié)果進行判斷，余數(shù)是1輸出“男”，0輸出“女”。

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

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

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

四.結(jié)論

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