使用Excel函數判斷身份證號是否輸入正確

劉若冰

【摘要】企事業單位的員工信息表中必不可少的一項數據是身份證號，員工在輸入數據的過程中難免會出現輸入錯誤，使用人工排查錯誤無疑是項繁重的任務，而使用Excel中的函數，例如MID，COLUMN，VLOOKUP，MOD，SUM，IF等可以較為容易的進行錯誤排查。

【關鍵詞】身份證號? 余數校驗? MID? VLOOKUP? MOD? SUM? IF

1.引言

企事業單位的人事部門通常要統計員工的各種信息數據，其中身份證號是必須要統計的一項重要數據，但是員工在輸入數據的過程中難免會出現輸入錯誤，如果想要人工檢查出數據中的錯誤必然是項很繁重的工作，不過這項工作可以使用Excel中的函數，例如MID，COLUMN，VLOOKUP，MOD，SUM，IF等函數來完成。而想要使用函數完成這項信息核對工作，就一定要了解身份證號中的數字所指代的信息。

2.身份證號的結構

2.1身份證號蘊含的信息

每個中國公民自出生之日起就擁有一串獨一無二的、由十八位數字組成的編碼用來標示自己的身份，就是身份證號碼。這十八位數字鐘包含了每個人的出生地區、出生日期以及性別等信息。

其中的第一到六位數字表示出生所在地區，第七到十四位數字表示出生年份、月份和日子，第十五到十七位數字是對同年、月、日出生的人員編定的順序號，第十七位用來表示性別，奇數為男性，偶數為女性，最后一位是數字校驗碼，用來校驗前十七位數字是否正確。

2.2校驗方法

第一步，將身份證號的前十七位，每一位分別乘以不同的校對系數，然后相加求和。

第二步，將第一步得到的和除以11，計算余數。

第三步，在余數和校驗碼的對應關系表中查找余數對應的校驗碼，和員工輸入的身份證號第十八位進行比對，如果一直則輸入正確，否則輸入有誤，需要修改。

3.身份證校驗步驟

在進行校驗之前，假定員工輸入的身份證號碼為37320619980124001X，放在工作表“身份證校對”的C3單元格內，將校對系數放在工作表“校對參數”的E4：U4區域內，將校驗碼放在工作表“校對參數”的B4：C14區域內。

3.1逐位提取身份證號中的數字

首先需要在工作表“身份證校對”中，將C3單元格內的身份證號的第一位到第十八位分別提取出來，逐次放在單元格D3到U3中。需要使用MID函數，MID函數有三個參數，此時的第一個參數是假定條件中的身份證號所在單元格C3，第二個參數需要分別填寫數字1到18，第三個參數是指定長度1。此時D3單元格內的公式為：=MID（C3，1，1），其中只有第二個參數是會發生變化的，這個變化可以使用另一個函數COLUMN來實現，COLUMN的作用是返回單元格所在的列號，那么我們可以用單元格A1到R1分別代替數字1到數字18，此時D3單元格內的公式變為：=MID（C3，COLUMN（A1），1）。確定了D3單元格內的公式后，逐列向右，一直到U3單元格，可以采用自動填充的方式填充公式，但是，在公式填充的過程中公式內的單元格地址C3、A1會發生變化，逐列變為D3、B1，E3、C1……，而此時MID函數的第一個參數應該一直是單元格C3，不能發生變化，所以需要使用$將C3單元格固定住，此時D3單元格內的公式變為：=MID（$C3，COLUMN（A1），1），然后使用自動填充功能計算其他單元格內的數據即可，稍后用來比較校驗結果的第十八位U3單元格內為X。

3.2計算校驗碼

在工作表“身份證校對”中，使用身份證號的第一位到十七位計算校驗碼，放在V3單元格內。需要用到三個函數，分別是VLOOKUP、MOD和SUM函數。

首先，在單元格V3內需要計算單元格區域D3：T3和工作表“校對參數”的單元格區域C4：C14逐次相乘、相加之和，此時，單元格V3內的公式為：=SUM（D3：T3*校對參數！E4：U4）。此時需要注意，由于SUM函數包含數組，所以需要按下ctrl+shift+enter組合鍵才能計算出正確結果，結果為134。

第二步，使用MOD函數對SUM函數計算的和除以11，計算余數。此時的SUM函數變為MOD函數的第一個參數，MOD函數的第二個參數是數字11，此時，單元格V3內的公式為：=MOD（SUM（D3：T3*校對參數！E4：U4），11），結果為2。

第三步，使用VLOOKUP函數，通過MOD函數計算的余數在工作表“校對參數”B4：C14區域內查找對應的校驗碼。

VLOOKUP函數有四個參數，第一個參數是MOD函數，第二個參數是工作表“校對參數”中校驗碼所在的區域B4：C14，第三個參數是第二個參數中滿足條件的值所在的列號，即2，第四個參數指定是否是精確匹配，由于不確定校驗碼區域B4：C14是否經過排序操作，所以采取模糊匹配，取值FALSE。

此時，單元格V3內的公式為：=VLOOKUP（MOD（SUM（D3：T3*校對參數！E4：U4），11），校對參數！B4：C14，2，FALSE），結果為X。

由于員工的數據信息可能比較多，通常不止一行數據，后續所在行需要使用自動填充功能，同樣在公式填充過程中會導致單元格地址的變化，所以，需要將校對系數所在區域E4：U4，校驗碼所在區域B4：C14鎖定。

最后，單元格V3內的公式為：=VLOOKUP（MOD（SUM（D3：T3*校對參數！$E$4：$U$4），11），校對參數！$B$4：$C$14，2，FALSE）。

3.3 比較校驗結果

在工作表“身份證校對”的W3單元格內，比較計算得到的校驗碼和提取的第十八位身份證號，即單元格V3和U3是否相等，如果相等，返回結果“正確”，否則返回結果“錯誤”。需要使用IF函數，由于V3單元格內數據為數值，U3單元格內數據為文本，所以需要使用“V3&""”將V3單元格轉變為文本數據，在W3單元格內輸入公式：=IF（U3=V3&""，"正確"，"錯誤"），判斷結果為正確。

4.總結

判斷身份證號是否輸入正確的過程中用到的函數有MID，COLUMN，VLOOKUP，MOD，SUM，IF，其中SUM雖然常用，但出現了數組計算，所以需要使用ctrl + shift + enter組合鍵確認輸入，在公式自動填充過程中需要使用$將必需固定的單元格地址或者單元格區域固定。

參考文獻：

[1]楊潤標.用Excel生成身份證號碼校驗碼.電腦知識與技術軟.2010（6）.

[2]閆治良.王宇晨.利用Excel函數判斷學生身份證信息的準確性電腦編程技巧與維護.2016（3）.

[3]李慶.身份證號在Excel中的巧妙運用.電腦知識與技術（經驗技巧）.2018（9）.