在Ｅｘｃｅｌ　２００７中身份證號碼的妙用

張　鵬

摘要：介紹在Excel2007中身份證號碼的輸入，以及提取生日、判斷性別等信息。

關鍵詞：Excel2007；身份證號碼；妙用

我們在使用Excel2007進行人事檔案管理，需要在單元格中輸入身份證號碼，會碰到如何輸入身份證號碼的問題。我們又知道身份證號碼里包含了每個人的出生日期、性別等信息，那么如何從身份證號碼里提取有用的信息呢?

1身份證號碼的輸入

在Excel2007中，在單元格中輸入15位數(shù)字的時候，Excel2007會自動將其轉換成科學記數(shù)法顯示，執(zhí)行“格式”菜單→“單元格”命令，設置數(shù)值格式，就能顯示15位的老身份證號碼。現(xiàn)在新的身份證號碼一般為18位，用同樣的方法，15位以后的數(shù)字會變?yōu)?。例如123456789012345678，回車，設置數(shù)值格式就會變?yōu)?23456789012345000。那么如何輸入18位身份證號碼呢?

方法一：把18位身份證號碼作為數(shù)字文本輸入，在輸入數(shù)字前先輸入單引號。這種方法在輸入前總要輸入單引號，顯得有點繁瑣。

方法二：在輸入數(shù)字之前，先選定需要輸入的單元格，然后單擊“格式”菜單中的“單元格”命令，找到“數(shù)字”項中的“自定義”命令，然后在其右面的“類型”框中選擇“＠”項，“確定”之后再輸入即可。

2避免重復輸入身份證號碼

俗話說得好，解決問題要從根本抓起。在需要輸入學號、身份證號碼等具有唯一性的數(shù)據(jù)時，完全有條件也應該做到不出現(xiàn)重復的數(shù)據(jù)。過去這么難：打開“條件格式”對話框，在右側的輸入框中輸入類似于“=COUNTIF($A$2:$A$21，A2)＞1”這樣的公式，當然還需要通過“格式→圖案”設置相關的顏色，確認后才能避免在A2:A21的單元格區(qū)域中出現(xiàn)重復數(shù)據(jù)。

2007更簡單：首先選中A2:A21單元格區(qū)域，接下來切換到“開始”標簽頁，在“條件格式”下拉菜單中選擇“突出顯示單元格規(guī)則→重復值”，此時會打開“重復值”對話框，在這里可以自行定義重復值單元格的顏色，例如設置為醒目的紅色，確認后即可生效。以后，每當輸入重復數(shù)據(jù)時，當前單元格會立即變成紅色。

如果木已成舟，大量的數(shù)據(jù)已經(jīng)輸入完畢，此時你需要做的恐怕就是將相關的重復數(shù)據(jù)找出來后將其刪除。過去這么難：需要通過高級篩選隱藏重復項，然后再將不重復的項復制到其他的工作表中，前前后后需要好幾個步驟才能解決問題，操作也頗為麻煩。

2007很簡單：選中需要刪除重復項的單元格區(qū)域，然后切換到“數(shù)據(jù)”標簽頁，直接單擊工具欄中的“刪除重復項”，此時會打開“刪除重復項”對話框，確認后即可將指定區(qū)域中的重復項一次性清除。注意，選擇處理區(qū)域時最好不要選擇標題行。

3身份證號碼中提取生日

大家都知道，身份證號碼已經(jīng)包含了每個人的出生年月日，對于老式的15位身份證而言，7-12位即個人的出生年月日，對于新式的18位身份證而言，7-14位代表個人的出身年月日。根據(jù)身份證號碼的這種排列規(guī)律，結合Excel2007的有關函數(shù)，我們就能實現(xiàn)利用身份證號碼自動輸入出生年月日。

假設在A列單元格中輸入身份證號碼，在B列單元格中輸入“=IF(LEN(A2)=15.CONCATENATE(“19”，MOD(A2,7,2)，“年”MID(A2,9,2)，“月”，MID(A2,11,2)，“日”)，CONCATENATE(MID(A2,7,4)，“年”，MID(A2,11,2)，“月”，MID(A2,13,2)，“日”))”這樣的公式，很容易提取到生日。共用到四個函數(shù)，作用分別為：

(1)IF(10gical_test，value_if_true，vat-ue_iUalse)：根據(jù)邏輯表達式測試的結果，返回相應的值。IF函數(shù)允許嵌套；

(2)LEN(text)：返回文本字符串中字符個數(shù)；

(2)CONCA7ENATE(textl，text2……)：將若干個文字項合并至一個文字項中；

(4)M[D(textstart-num,num_chars)：從文本字符串中指定的起始位置起，返回指定長度的字符。

4判斷性別

身份證號碼中，老式的15位的身份證號碼最后一位為性別(奇數(shù)為男，偶數(shù)為女)。對于新式的18位身份證，17位(即倒數(shù)第二位)為性別(同樣奇數(shù)為男，偶數(shù)為女)。只要輸入“=IF(LEN(A2)=15,1F(MOD(MID(A2,15,1)，2)=1，“男”，“女”)，1F(MOD(MID(A2,17.1)，2)=1”，“男”，：女”))”這樣的公式，即可從身份證號碼中判斷性別。用到一新函數(shù)MOD(number，divisor)：返回兩數(shù)相除后的余數(shù)。

5身份證號碼中算年齡

根據(jù)前面出生年月日求年齡，我們可以編輯“＝(YEAR(TODAY())-YEAR(A1))“這樣的公式。A1中存放的是出生年月日，并用到兩個函數(shù)，作用分別為：

(1)TODAY()：返回系統(tǒng)的日期；

(2)YEAR()：提取日期中的年份。