身份證號碼的管理

　　我們先來說說身份證號碼的管理。身份證號碼信息是一組長編碼，因此在管理這類信息時要注意以下兩個方面的設置：

　　設置文本格式

　　在輸入身份證號碼前，要先將單元格設置成“文本”格式，然后才能輸入信息（順序不能顛倒），否則會造成輸入的身份證號碼為默認的“常規”數值類型。這樣一來，一個18位的身份證號碼會變成“科學計數法”顯示，并且由于Excel自身的15位數據精度的問題，會使身份證號碼的后3位自動轉換成“0”（見圖表2）。

　　設置數據有效性

　　為了防止在輸入身份證號碼的過程中出現多輸入或少輸入的錯誤，還應在設置了“文本”格式后再為這些單元格設置“數據有效性”的條件。

　　當前，由于身份證號碼普遍都已經升級為二代身份證號碼，也就是18位編號。所以若沒有例外情況，只需按如下操作即可完成。

　　第一步：選中所有待輸入身份證號碼的單元格，然后選擇“數據”選項卡中的“數據有效性”按鈕，打開“數據有效性”對話框。

　　第二步：將“有效性條件”中的“任何值”更改成“文本長度”條件，再將“數據”中的“介于”更改成“等于”，然后在下面的“長度”框中輸入“18”，單擊“確定”按鈕（見圖表3）。

　　倘若在員工統計表中存在一代身份證號碼（15位）和二代身份證號碼（18位）混雜的情況，那么就只能使用數據有效性中的“自定義”條件才能進行嚴格的限制了，操作如下：

　　第一步：選中所有待輸入身份證號碼的單元格，然后選擇“數據”選項卡中的“數據有效性”按鈕，打開“數據有效性”對話框。

　　第二步：將“有效性條件”中的“任何值”更改成“自定義”條件，再在下方的“公式”欄中輸入函數“=OR（LEN（C4）=15，LEN（C4）=18）”，然后單擊“確定”按鈕（圖表4）。

　　通過前面的設置，我們已經做好了輸入前的準備，這樣一來，單元格既是文本格式，又有了非常嚴謹的有效性的限制，下面就可以放心地輸入每位員工的身份證號碼了。

　　身份證號碼的運算

　　由于身份證號碼有著很強的規律信息特征，所以對于HR來說應該很好地利用它的這個特點。當身份證號碼一旦輸入完成，便可自動計算出員工的“出生日期”、“年齡”和“性別”等信息。

　　首先，讓我們了解一下身份證號碼的規律。對于一個18位的身份證號碼，前面6位是這個人的戶籍所在地，前3位是省，后3位是城市（對于直轄市來說，前3位是市，后3位是區），從第7位到14位是這個人的出生日期，第15位到17位是順序號，其中第17位是性別碼，奇數為男，偶數為女，最后一位是校驗碼，由數字0？9和羅馬數字的Ⅹ（10）11個數字組成。

　　這樣一個帶有規律的信息編碼，只要身份證號碼填寫無誤，那么利用Excel的“文本提取”函數就可計算“出生日期”，利用日期計算的公式就可以計算出“年齡”，再利用奇偶數的判斷就可自動計算出“性別”信息。

　　自動計算出生日期

　　由于出生日期是身份證號碼中的第7位至第14位，所以我們使用“文本提取”函數和“文本合并”的公式就可自動將出生日期計算出來，操作如下：

　　第一步：選中第1個人員的出生日期單元格。

　　第二步：然后在單元格中輸入“=MID（C4，7，4）&"-"&MID（C4，11，2）&"-"&MID（C4，13，2）”，單擊“Enter”鍵即可得到第1個人的出生日期信息（見圖表5）。

　　第三步：向下填充公式至最后一個人。

　　自動計算年齡

　　出生的日期有了，就可以使用“TODAY”函數配合自動計算每個員工的實際年齡了，操作如下：

　　第一步：選中第1個人員的年齡單元格。

　　第二步：然后在單元格中輸入“=INT（（TODAY（）-D4）/365）”，單擊“Enter”鍵即可得到第1個人的年齡信息（圖表6）。

　　第三步：向下填充公式至最后一個人。

　　自動計算性別

　　性別碼是身份證號碼中的第17位，奇數位男，偶數為女，所以只要把第17位提取出來進行奇偶判斷，就可自動得到性別信息了，操作如下：

　　第一步：選中第1個人員的性別單元格。

　　第二步：然后在單元格中輸入“=IF（MOD（MID（C4，17，1），2）=0，"女"，"男"）”，單擊“Enter”鍵即可得到第1個人的性別信息（圖表7）。

　　第三步：向下填充公式至最后一個人。