劉若冰

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

【關(guān)鍵詞】身份證號(hào)? 余數(shù)校驗(yàn)? MID? VLOOKUP? MOD? SUM? IF

1.引言

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

2.身份證號(hào)的結(jié)構(gòu)

2.1身份證號(hào)蘊(yùn)含的信息

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

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

2.2校驗(yàn)方法

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

第二步，將第一步得到的和除以11，計(jì)算余數(shù)。

第三步，在余數(shù)和校驗(yàn)碼的對應(yīng)關(guān)系表中查找余數(shù)對應(yīng)的校驗(yàn)碼，和員工輸入的身份證號(hào)第十八位進(jìn)行比對，如果一直則輸入正確，否則輸入有誤，需要修改。

3.身份證校驗(yàn)步驟

在進(jìn)行校驗(yàn)之前，假定員工輸入的身份證號(hào)碼為37320619980124001X，放在工作表“身份證校對”的C3單元格內(nèi)，將校對系數(shù)放在工作表“校對參數(shù)”的E4：U4區(qū)域內(nèi)，將校驗(yàn)碼放在工作表“校對參數(shù)”的B4：C14區(qū)域內(nèi)。

3.1逐位提取身份證號(hào)中的數(shù)字

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

3.2計(jì)算校驗(yàn)碼

在工作表“身份證校對”中，使用身份證號(hào)的第一位到十七位計(jì)算校驗(yàn)碼，放在V3單元格內(nèi)。需要用到三個(gè)函數(shù)，分別是VLOOKUP、MOD和SUM函數(shù)。

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

第二步，使用MOD函數(shù)對SUM函數(shù)計(jì)算的和除以11，計(jì)算余數(shù)。此時(shí)的SUM函數(shù)變?yōu)镸OD函數(shù)的第一個(gè)參數(shù)，MOD函數(shù)的第二個(gè)參數(shù)是數(shù)字11，此時(shí)，單元格V3內(nèi)的公式為：=MOD（SUM（D3：T3*校對參數(shù)！E4：U4），11），結(jié)果為2。

第三步，使用VLOOKUP函數(shù)，通過MOD函數(shù)計(jì)算的余數(shù)在工作表“校對參數(shù)”B4：C14區(qū)域內(nèi)查找對應(yīng)的校驗(yàn)碼。

VLOOKUP函數(shù)有四個(gè)參數(shù)，第一個(gè)參數(shù)是MOD函數(shù)，第二個(gè)參數(shù)是工作表“校對參數(shù)”中校驗(yàn)碼所在的區(qū)域B4：C14，第三個(gè)參數(shù)是第二個(gè)參數(shù)中滿足條件的值所在的列號(hào)，即2，第四個(gè)參數(shù)指定是否是精確匹配，由于不確定校驗(yàn)碼區(qū)域B4：C14是否經(jīng)過排序操作，所以采取模糊匹配，取值FALSE。

此時(shí)，單元格V3內(nèi)的公式為：=VLOOKUP（MOD（SUM（D3：T3*校對參數(shù)！E4：U4），11），校對參數(shù)！B4：C14，2，F(xiàn)ALSE），結(jié)果為X。

由于員工的數(shù)據(jù)信息可能比較多，通常不止一行數(shù)據(jù)，后續(xù)所在行需要使用自動(dòng)填充功能，同樣在公式填充過程中會(huì)導(dǎo)致單元格地址的變化，所以，需要將校對系數(shù)所在區(qū)域E4：U4，校驗(yàn)碼所在區(qū)域B4：C14鎖定。

最后，單元格V3內(nèi)的公式為：=VLOOKUP（MOD（SUM（D3：T3*校對參數(shù)！$E$4：$U$4），11），校對參數(shù)！$B$4：$C$14，2，F(xiàn)ALSE）。

3.3 比較校驗(yàn)結(jié)果

在工作表“身份證校對”的W3單元格內(nèi)，比較計(jì)算得到的校驗(yàn)碼和提取的第十八位身份證號(hào)，即單元格V3和U3是否相等，如果相等，返回結(jié)果“正確”，否則返回結(jié)果“錯(cuò)誤”。需要使用IF函數(shù)，由于V3單元格內(nèi)數(shù)據(jù)為數(shù)值，U3單元格內(nèi)數(shù)據(jù)為文本，所以需要使用“V3&""”將V3單元格轉(zhuǎn)變?yōu)槲谋緮?shù)據(jù)，在W3單元格內(nèi)輸入公式：=IF（U3=V3&""，"正確"，"錯(cuò)誤"），判斷結(jié)果為正確。

4.總結(jié)

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

參考文獻(xiàn)：

[1]楊潤標(biāo).用Excel生成身份證號(hào)碼校驗(yàn)碼.電腦知識(shí)與技術(shù)軟.2010（6）.

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

[3]李慶.身份證號(hào)在Excel中的巧妙運(yùn)用.電腦知識(shí)與技術(shù)（經(jīng)驗(yàn)技巧）.2018（9）.