袁敏銳

摘 要 根據(jù)Excel數(shù)據(jù)信息，針對(duì)身份證號(hào)碼在錄入后顯示不正常、錄入難免有錯(cuò)誤以及通過(guò)身份證號(hào)碼實(shí)現(xiàn)減少數(shù)據(jù)錄入量，本文介紹了相對(duì)應(yīng)的解決辦法。

關(guān)鍵詞 數(shù)據(jù)信息 身份證號(hào)碼 數(shù)據(jù)有效性設(shè)置 研究

中圖分類(lèi)號(hào)：G71文獻(xiàn)標(biāo)識(shí)碼：A文章編號(hào)：1002-7661（2012）07-0006-02

目前，身份證號(hào)碼在數(shù)據(jù)信息里隨處可見(jiàn)，而且處理不好問(wèn)題不少。如單位小張每年9月份都要負(fù)責(zé)至少兩三千人的新生入學(xué)的學(xué)生信息數(shù)據(jù)的匯總，并上傳到網(wǎng)絡(luò)系統(tǒng)里；而各班班主任錄入學(xué)生信息時(shí)難免有錯(cuò)誤，其中如果學(xué)生身份證號(hào)錄入有誤，就不能正常上傳，還得返回去讓各班主任重新核對(duì)，再交回來(lái)，再重新上傳。這樣來(lái)來(lái)回回的折騰，費(fèi)時(shí)費(fèi)力不說(shuō)，主要是影響工作的進(jìn)度，無(wú)法向領(lǐng)導(dǎo)交差。那么有什么方法可以避免這種錯(cuò)誤，提高工作效率呢？大家可能會(huì)想要求各班主任在錄入數(shù)據(jù)時(shí)認(rèn)認(rèn)真真地錄入不就沒(méi)事了，但是稍不留意錯(cuò)誤就會(huì)出現(xiàn)，不要說(shuō)錄入一個(gè)班幾十個(gè)人的信息了，有時(shí)我們自己寫(xiě)自己的手機(jī)號(hào)或者身份證號(hào)都有可能出現(xiàn)錯(cuò)誤。但是如果能在Excel表格中錄入身份證號(hào)的時(shí)候多一個(gè)提醒功能，相信出錯(cuò)的機(jī)會(huì)就少之又少了，同時(shí)也就可以做到及時(shí)發(fā)現(xiàn)錯(cuò)誤并及時(shí)更正。在Excel表格中簡(jiǎn)單的添加一些功能，就能方便判斷錄入的身份證號(hào)是否存在錄入錯(cuò)誤（如：錄入的身份證號(hào)位數(shù)是多了或少了）或者是否合法，檢驗(yàn)出身份證號(hào)的有效性以及合法性。

一、利用Excel設(shè)置檢驗(yàn)身份證號(hào)碼的有效性及合法性

利用Excel公式對(duì)身份證號(hào)碼的錄入進(jìn)行準(zhǔn)確性檢驗(yàn)，確保身份證號(hào)錄入的準(zhǔn)確性；可以檢驗(yàn)出15位的身份證號(hào)碼錄入時(shí)是否是15位，18位身份證號(hào)碼（目前絕大多數(shù)為二代身份證了，即都是18位號(hào)）的，其最后一位是檢驗(yàn)碼，它是根據(jù)身份證號(hào)前17位數(shù)字依照規(guī)則計(jì)算出來(lái)的（這里不作說(shuō)明），其值0～9或X。一般情況下只要有一位數(shù)字輸入錯(cuò)誤，依照規(guī)則計(jì)算后就會(huì)與第18位數(shù)字不符。當(dāng)然不排除按錯(cuò)誤號(hào)碼計(jì)算后恰好與檢驗(yàn)碼相符的情況，但這種情況出現(xiàn)的可能性較低。所以利用下面介紹的設(shè)置功能，錄入18位身份證號(hào)碼時(shí)除了可以檢測(cè)出錄入位數(shù)的對(duì)錯(cuò)外，同時(shí)還可以檢驗(yàn)其是否合法，及時(shí)在錄入時(shí)提醒錄入者，大大提高身份證號(hào)錄入的準(zhǔn)確率。

（一）利用Excel數(shù)據(jù)有效性設(shè)置自動(dòng)彈出提示框提醒

在錄入身份證號(hào)碼前，先設(shè)置要錄入身份證號(hào)碼的所在列為文本格式，否則在Excel單元格中輸入的數(shù)字位數(shù)超過(guò)11位后，會(huì)變成科學(xué)計(jì)數(shù)的形式，如錄入“123456789012345678”（外面雙引號(hào)不要錄入），18位數(shù)會(huì)顯示成“1.23457E+17”，雙擊進(jìn)去看到的數(shù)字變成“123456789012345000”后三位數(shù)字全變成了“000”是錯(cuò)誤的，如圖1所示。

圖1

1．設(shè)置身份證號(hào)碼所在列的單元格格式為“文本”格式

選中身份證號(hào)碼所在的列“列頭”（本文中為C列）。如圖2所示，然后點(diǎn)擊“鼠標(biāo)”右鍵→選擇“設(shè)置單元格格式”選項(xiàng)→“單元格格式”窗口→選擇“數(shù)字”選項(xiàng)卡→在“分類(lèi)”欄中→選擇“文本”選項(xiàng)→然后點(diǎn)擊“確定”按鈕。這樣該列的所有單元格格式全部設(shè)置為文本格式，再來(lái)錄入身份證號(hào)碼就不會(huì)出現(xiàn)（如圖1）錯(cuò)誤的情況。

圖2

2．設(shè)置要錄入身份證號(hào)碼的所在列（C列）的有效性

設(shè)置要錄入身份證號(hào)碼的所在列（C列）的有效性，讓其能在錄入身份證號(hào)碼不正確的時(shí)候及時(shí)提示。

第1步：選定“C列”列頭，菜單：數(shù)據(jù)→有效性。（如圖3）

圖3

第2步：當(dāng)打開(kāi)“數(shù)據(jù)有效性窗口”后，選擇“設(shè)置”選項(xiàng)卡→允許→選擇“自定義”。公式引用的位置，把下面的公式輸入（或者把公式復(fù)制）進(jìn)去：（如圖4）

=OR(LEN(C1)=15，IF(LEN(C1)=18，MID("10X98765432"，MOD(SUM(MID(C1，ROW(INDIRECT("1:17"))，1)*2^(18-ROW(INDIRECT("1:17"))))，11)+1，1)=RIGHT(C1)))

圖4

第3步：公式輸入完后，選擇“出錯(cuò)警告”選項(xiàng)卡→樣式→選擇“信息”；“標(biāo)題”下面的內(nèi)容，輸入提醒文字如“注意”；“出錯(cuò)信息” 下面的內(nèi)容：輸入“請(qǐng)您再次確認(rèn)，您剛剛錄入的‘身份證號(hào)碼是否準(zhǔn)確！”等類(lèi)型的文字，最后點(diǎn)“確定”（如圖5），數(shù)據(jù)有效性設(shè)置就完成了。這時(shí)錄入身份證號(hào)碼如果出錯(cuò)，馬上就會(huì)自動(dòng)彈出提醒對(duì)話(huà)框，提醒錄入人員重新再核對(duì)，避免了身份證號(hào)出錯(cuò)了還不知道的情況。

圖5

（二）在身份證號(hào)碼所在列旁，增加一列用于設(shè)置文字提醒

如在D列設(shè)置，D1單元格輸入“身份證號(hào)碼對(duì)錯(cuò)”作表頭，在D2單元格輸入下面的公式：

=IF(IF(LEN(C2)=18，MID("10X98765432"，MOD(SUMPRODUCT(MID(C2，ROW(INDIRECT("1:17"))，1)*2^(18-ROW(INDIRECT("1:17"))))，11)+1，1)=RIGHT(C2)，IF(LEN(C2)=15，ISNUMBER(--TEXT(19&MID(C2，7，6)，"#-00-00"))))，"正確"，"錯(cuò)誤")

輸入完后按“Enter（回車(chē)）”，然后向下填充該公式即可。這樣如果身份證號(hào)碼錄入對(duì)了，其對(duì)應(yīng)的右邊D列顯示“正確”，不對(duì)了顯示“錯(cuò)誤”。

在此為了使“錯(cuò)誤”顯示得更顯眼，還可以在D2單元格輸入完公式后，設(shè)置一下D2單元格“條件格式”，再向下填充該公式。通過(guò)單元格“條件格式”，在顯示“錯(cuò)誤”的時(shí)候，字體變成紅色同時(shí)加上底紋顏色以便更好的區(qū)別。

設(shè)置方法（步驟）：在D2單元格輸入完上面的公式后，選中“D2” 單元格→選擇菜單“格式”→選擇“條件格式”選項(xiàng)→“條件1”選擇“單元格數(shù)值”→選擇“等于”→輸入“=“錯(cuò)誤””→選擇“格式”按鈕（在里面，選擇“字體”選項(xiàng)卡→顏色選擇“紅色”→再選擇“圖案”選項(xiàng)卡→單元格底紋→選擇一種底紋顏色“灰色”→點(diǎn)擊“確定”按鈕，退出）→再點(diǎn)擊“條件格式”窗口上的“確定”按鈕，完成設(shè)置。（如圖6）

圖6

再用D2單元格，向下填充公式就會(huì)連“條件格式”一起向下填充了。當(dāng)錄入的身份證號(hào)碼出錯(cuò)時(shí)，其對(duì)應(yīng)的D列上所在的單元格會(huì)顯示出紅色字體的“錯(cuò)誤”兩個(gè)字及該單元格被加上“灰色”底紋的效果，顯得更顯眼。（如圖7）

圖7

不過(guò)，當(dāng)最后全部身份證號(hào)碼錄入完后，如果D列不要了，可以采用隱藏它或者直接刪除都可以。

通過(guò)以上兩種方法的設(shè)置，都可以在錄入身份證號(hào)碼出錯(cuò)時(shí)，馬上提醒錄入人員重新再核對(duì)身份證號(hào)碼，避免了身份證號(hào)錄入錯(cuò)誤了還不知道的情況。這兩種方法可以單獨(dú)使用，也可以一起使用，至于使用哪種方法，可以根據(jù)自己的需要和習(xí)慣來(lái)定。

二、利用身份證號(hào)碼提取相應(yīng)內(nèi)容，減輕信息輸入量

正確而合法的身份證號(hào)碼是每個(gè)公民唯一的身份代號(hào)，并且包含有公民的出生年月日、性別及最初編制該身份證號(hào)碼的所在地區(qū)（目前一般為出生地）或者叫籍貫地區(qū)等信息。我們?cè)阡浫胄畔⒌臅r(shí)候除了要求有姓名、身份證號(hào)碼外，一般情況下還要有出生日期（或年齡）、性別等相關(guān)信息。如果錄入的數(shù)據(jù)量大（人數(shù)多），把這些信息一條條的錄入，不僅費(fèi)時(shí)費(fèi)力，同時(shí)出錯(cuò)也是在所難免的。但是如果我們只錄入姓名及通過(guò)上面介紹的方法保證身份證號(hào)碼的錄入準(zhǔn)確，再利用Excel函數(shù)通過(guò)其身份證號(hào)碼提取其出生日期（或年齡）及性別，就可以大大減少信息的錄入量及出生日期、性別的錄入錯(cuò)誤。

中國(guó)居民身份證號(hào)碼是由一組特征組合碼組成的，最初的一代身份證號(hào)碼為15位，目前大部分都升級(jí)為二代身份證號(hào)碼為18位，其編碼規(guī)則為：

15位身份證號(hào)碼：前6位數(shù)字為籍貫地區(qū)代碼，第7~12位數(shù)字為出生日期代碼，第13~15位數(shù)字為順序碼。18位身份證號(hào)碼：前6位數(shù)字為籍貫地區(qū)代碼，第7~14位數(shù)字為出生日期代碼，第15~17位數(shù)字為順序碼，第18位數(shù)字為檢驗(yàn)碼。

其中３位數(shù)字順序碼，是為同一籍貫地區(qū)（代碼）的同年同月同日出生人員編制的順序號(hào)（如雙包胎其身份證號(hào)碼的區(qū)別），并且３位數(shù)字順序碼的最后一位數(shù)字（即15位的最后一位，18位的倒數(shù)第二位），是偶數(shù)的為女性，是奇數(shù)的為男性。本文中身份證號(hào)碼所在列為C列，而且是從C2單元格開(kāi)始錄入，下面公式提到的C2都是此意思。

1．從身份證號(hào)碼中提取出生日期或年齡

本文中提取“出生日期”放在E列，E2單元格開(kāi)始，那就要先把E2單元格設(shè)置好要求的“日期”格式（按圖2類(lèi)似的設(shè)置方法操作），并把下面的公式輸入E2單元格，并向下填充公式，即可得相應(yīng)的出生日期。（如圖8）

圖8

=IF(C2<>""，TEXT((LEN(C2)=15)*19&MID(C2，7，6+(LEN(C2)=18)*2)，"#-00-00")+0，)

簡(jiǎn)化公式：=--TEXT((LEN(C2)=15)*19&MID(C2，7，6+(LEN(C2)=18)*2)，"#-00-00")

如果提取的出生日期格式（如：19950809）公式采用：

=IF(LEN(C2)=15，19&MID(C2，7，6)，"")&IF(LEN(C2)=18，MID(C2，7，8)，"")

簡(jiǎn)化后公式為：=IF(LEN(C2)=18， MID(C2，7，8)， 19&MID(C2，7，6))

2．從身份證號(hào)碼中提取性別

本文中提取“性別”放在F列，F(xiàn)2單元格開(kāi)始，那就要先把F2單元格設(shè)置好要求的“常規(guī)”格式（按圖2類(lèi)似的設(shè)置方法操作），并把下面的公式輸入F2單元格，并向下填充公式，即可得相應(yīng)的性別。（如圖9）

圖9

=IF(C2<>""，IF(MOD(MID(C2，IF(LEN(C2)=15，15，17)，1)，2)=1，"男"，"女")，)

簡(jiǎn)化后公式為：=IF(MOD(MID(C2，IF(LEN(C2)=15，15，17)，1)，2)=1，"男"，"女")

3．從身份證號(hào)碼中提取年齡

公式：

=DATEDIF(TEXT((LEN(C2)=15)*19&MID(C2，7，6+(LEN(C2)=18)*2)，"#-00-00")，TODAY()，"y")

同理，不管是15位還是18位身份證號(hào)碼前6位為籍貫地區(qū)，如果有一個(gè)編碼和省份地區(qū)的對(duì)照表，也可以利用Excel函數(shù)公式來(lái)提取其相應(yīng)的籍貫地區(qū)信息。

總之，通過(guò)上面介紹的方法設(shè)置后，不僅可以降低身份證號(hào)碼在錄入中的出錯(cuò)率，同時(shí)還大大減少信息的錄入量。利用好Excel本身的功能不但可以解決很多問(wèn)題，還可以大大提高工作效率。

（責(zé)任編輯 劉 紅）