王紅然 王書旺

摘要 基于公民身份證編碼規(guī)則，本文主要介紹了利用Excel函數(shù)提取個人基本信息的方法，對身份證號碼有效性的驗證進行案例研究，并進一步探討防止身份證號碼輸入錯誤的具體方法。

【關鍵詞】Excel函數(shù) 身份證號碼 數(shù)據(jù)有效性

在用Excel進行個人基本信息錄入時，除了姓名、性別、出生日期外，身份證號碼也是至關重要的內(nèi)容。但是由于各種原因，經(jīng)常會出現(xiàn)身份證號碼位數(shù)不對、性別或出生日期與身份證號碼對應信息不一致的情況。Excel函數(shù)為我們提供了解決這類問題的有效途徑。

1 個人基本信息的提取

1.1 出生日期的提取

出生日期位于身份證號碼的7-14位，通常需要使用字符串提取函數(shù)來取出這8位。Excel中常用的字符串提取函數(shù)有LEFT、RIGHT和MID。其中LEFT是從左邊提取，RIGHT是從右邊提取，而MID是從中間提取。出生日期位于身份證號碼的中間位置，因此用MID函數(shù)來提取。假設身份證號碼位于B2單元格，出生日期位于D2單元格，可在D2單元格輸入公式“-MID（B2，7，8）”，得到的結果是文本格式。為了便于后續(xù)數(shù)據(jù)處理，可以先將年月日分別提取出來，然后用DATE函數(shù)組合成日期格式，即在D2單元格輸入公式“=DATE（MID（B2.7，4），MID（B2.11.2）.MID（B2，13，2》”。

1.2 性別的提取

根據(jù)身份證編碼規(guī)則，順序碼的奇數(shù)分配給男性，偶數(shù)分配給女性。順序碼位于身份證號碼的第15-17位，而判斷一個數(shù)的奇偶性只需要判斷該數(shù)個位的奇偶性。因此，根據(jù)身份證號碼的第17位就可以判斷性別。先用MID函數(shù)提取身份證號碼的第17位，然后用ISODD函數(shù)判讀該位的奇偶性，最后用IF函數(shù)判斷性別。假設性別位于C2單元格，可在C2單元格輸入公式“=IF（ISODD（MID（B2，17，1》，“男”，“女”）”。

1.3 身份證地址的提取

身份證地址對應的是身份證號碼的地址碼，而地址碼位于身份證號碼的前6位，要取出這6位可以用MID函數(shù)，也可以用LEFT函數(shù)。假設身份證地址位于E2單元格，可在E2單元格輸入公式“-LEFT（B2，6）”。得到的結果是6位地址碼，要想得到身份證地址信息，需要查詢身份證地址碼對照表。假設身份證地址碼對照表位于另一張名為“地址碼對照表”的工作表，地址碼和地址的數(shù)據(jù)范圍為A2：B3466，可在E2單元格輸入公式“-VLOOKUP（LEFT（B2，6），地址碼對照表！A2：B3466，2，0）”。輸入測試身份證號碼，提取結果如圖1所示。

2 身份證號碼的有效性驗證

根據(jù)[中華人民共和國國家標準GB11643-1999]及“IS0 7064：1983.MOD 11-2校驗碼計算法”，位于身份證號碼第18位的校驗碼是根據(jù)前面17位數(shù)字碼按照一定的算法生成的。如果通過Excel函數(shù)計算得到的校驗碼和從身份證號碼中提取出的校驗碼一致，則該身份證號碼就是一個有效的號碼。

2.1 身份證校驗碼的計算方法

基本的身份證校驗碼計算方法如下：

（l）將身份證號碼的前17位數(shù)分別乘以不同的系數(shù)，第1-17位的系數(shù)分別為：7 9 105 842163 7910 5 8 42;

（2）將得到的17個乘積相加;

（3）將相加后的和除以11并得到余數(shù);

（4）余數(shù)有11種情況：0123456789 10，它們對應的身份證最后一位校驗碼為1OX98765432。

2.2 基于Excel函數(shù)的身份證校驗碼計算

根據(jù)上述身份證校驗碼的計算方法，用Excel函數(shù)進行計算。

第一步，新建一個工作表，設計工作表結構如圖2所示。其中，A列為序號，B列為身份證號碼，C-T列分別為身份證號碼的第1-18位，U列為身份證校驗碼計算結果，V列顯示身份證是否有效。為了便于處理，將第1-17位的系數(shù)放在C14：S14中，將乘加結果放在X列，將取余結果放在Y列，將余數(shù)和校驗碼對照表放在AA2：AB12中。

第二步，取出身份證號碼的第1-17位。為了統(tǒng)一處理，這里采用MID函數(shù)。在C2單元格輸入公式“-MID（A2，1，1）”，得到的結果是文本型。為了便于后面自動填充，可將第一個參數(shù)地址改成引用A列，第二個參數(shù)地址改成引用第一行，即把公式改為“-MID（$A2，B$1，1）”。為了下一步進行乘加運算，需要用VALUE函數(shù)將該結果轉換成數(shù)值型，即把公式改為“-VALUE（MID（$A2，B$1，l》”。向右填充到S2即可得到身份證號碼的第1-17位。

第三步，取出身份證號碼的第18位。該位是身份證號碼的最后一位，而且可能含有字符，也不需要參與乘加運算，因此不需要用VALUE函數(shù)，只用RIGHT函數(shù)即可，即在T2單元格輸入公式“-RIGHT（B2，1）”。

第四步，乘加運算，即將身份證號碼的前17位分別乘以相應的系數(shù)，然后將得到的17個乘積相加。這里可將身份證號碼的前17位看作一個數(shù)組，這17位對應的系數(shù)看作另一個數(shù)組，顯然，這兩個數(shù)組的維數(shù)是相同的。因此，可以用數(shù)組公式實現(xiàn)乘加運算，即在X2單元格輸入公式“=SUMPRODUCT（B2：R2.B14：R14）”， 為了后面能自動填充，可將系數(shù)數(shù)組地址改為絕對引用，即把公式改為“-SUMPRODUCT（B2：R2，$B$14：$R$141。

第五步，取余運算。用MOD函數(shù)將乘加運算的結果對II取余，即在Y2單元格輸入公式“=MOD（X2，11）”。

第六步， 計算校驗碼。 用VLOOKUP函數(shù)根據(jù)取余運算的結果計算校驗碼，即在U2單元格輸入公式“=VLOOKUP（Y2，AA2：AB12，2.0）。為了后面能自動填充，可將校驗碼對照表地址改為絕對引用，即把公式改為“=VLOOKUP（Y2，$AA$2： $AB$12，2，0）”。 由于身份證號碼第18位的計算結果是文本格式，為了下一步能進行比較，需要把校驗碼改成文本格式，可用連接符“&”連接一個空字符串，即把公式改為“=VLOOKUP（Y2，$AA$2：$AB$12，2，0）&……”。

2.3 身份證號碼的有效性驗證

將計算得到的校驗碼和身份證號碼的第18位進行比較，如果二者一致，則該身份證號碼就是有效的?？梢杂肐F函數(shù)來實現(xiàn)，即在Y2單元格輸入公式“-IF（T2-U2，”有效”，”無效”）”。為了突出顯示無效結果，可在無效后加“×”，即把公式改為“=IF（T2=U2，”有效”，”無效×”）”。輸入一個測試身份證號碼，驗證結果如圖3所示。

3 防止身份證號碼輸入錯誤的方法

身份證號碼比普通數(shù)字位數(shù)多，難以記憶，很容易輸入錯誤。除了在輸入時細心外，還可以借助Excel提供的各種功能來防止身份證號碼輸入錯誤。

3.1 設置單元格格式

直接輸入身份證號碼經(jīng)常會以科學計數(shù)法顯示，可在輸入前將需要輸入身份證號碼的單元格設置成文本格式，數(shù)據(jù)量較少時可在輸入身份證號碼前輸入一個半角的單引號…。

3.2 限制文本長度

目前，我國公民身份證號碼統(tǒng)一為18位?？衫肊xcel提供的數(shù)據(jù)有效性功能限制輸入文本的長度。

3.3 避免重復輸入

有的身份證號碼非常相似，輸入錯誤后不容易發(fā)現(xiàn)?？衫肊xcel提供的數(shù)據(jù)有效性功能避免重復輸入。假設身份證號碼位于B2單元格，在輸入前選中該單元格，設置數(shù)據(jù)有效性，在數(shù)據(jù)有效性窗口的公式欄輸入公式“=COUNTIF（B：B，B2）=1”。其他需要設置數(shù)據(jù)有效性的單元格可以用選擇性粘貼功能，粘貼選項選擇“有效性驗證”項。

3.4 錄入后檢查

如果在設置數(shù)據(jù)有效性之前已經(jīng)錄入了部分身份證號碼，可在錄入完成后選中身份證號碼區(qū)域，設置條件格式，規(guī)則類型選擇“僅對唯一值或重復值設置條件格式”。

4 結論

掌握了身份證號碼的秘密和相關的Excel函數(shù)，就可以利用Excel函數(shù)來進行個人信息處理，這樣不僅減少了大量的數(shù)據(jù)錄入，還避免了因身份證號碼錯誤帶來的諸多問題。其實，生活中有很多證件和卡片的編碼都是有一定規(guī)律的，只要我們掌握了這個規(guī)律，找到了編碼規(guī)則，就可以利用Excel函數(shù)來幫助我們提高工作效率。

參考文獻

[1]閆治良，王宇晨，利用Excel函數(shù)判斷學生身份證信息的準確性[J].電腦編程技巧與維護，2016 （03）：47-48.

[2]申習身，使用Excel驗證身份證號校驗碼的設計方法[J].天津職業(yè)院校聯(lián)合學報，2014 （02）：65-67.

[3]陳大銀，基于Excel的身份證號碼中信息提取的設計[J].滁州職業(yè)技術學院學報，2012 （02）：56-57.