張　鵬

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

關(guān)鍵詞：Excel2007；身份證號(hào)碼；妙用

我們?cè)谑褂肊xcel2007進(jìn)行人事檔案管理，需要在單元格中輸入身份證號(hào)碼，會(huì)碰到如何輸入身份證號(hào)碼的問題。我們又知道身份證號(hào)碼里包含了每個(gè)人的出生日期、性別等信息，那么如何從身份證號(hào)碼里提取有用的信息呢?

1身份證號(hào)碼的輸入

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

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

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

2避免重復(fù)輸入身份證號(hào)碼

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

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

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

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

3身份證號(hào)碼中提取生日

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

假設(shè)在A列單元格中輸入身份證號(hào)碼，在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)，“日”))”這樣的公式，很容易提取到生日。共用到四個(gè)函數(shù)，作用分別為：

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

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

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

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

4判斷性別

身份證號(hào)碼中，老式的15位的身份證號(hào)碼最后一位為性別(奇數(shù)為男，偶數(shù)為女)。對(duì)于新式的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”，“男”，：女”))”這樣的公式，即可從身份證號(hào)碼中判斷性別。用到一新函數(shù)MOD(number，divisor)：返回兩數(shù)相除后的余數(shù)。

5身份證號(hào)碼中算年齡

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

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

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