使用Excel驗(yàn)證身份證號(hào)校驗(yàn)碼的設(shè)計(jì)方法

(天津海運(yùn)職業(yè)學(xué)院，天津 300350)

一、身份證號(hào)校驗(yàn)碼的計(jì)算規(guī)則

根據(jù)【中華人民共和國(guó)國(guó)家標(biāo)準(zhǔn) GB 11643-1999】中有關(guān)公民身份號(hào)碼的規(guī)定，公民身份號(hào)碼是特征組合碼，由十七位數(shù)字本體碼和一位數(shù)字校驗(yàn)碼組成。排列順序從左至右依次為：六位數(shù)字地址碼，八位數(shù)字出生日期碼，三位數(shù)字順序碼和一位數(shù)字校驗(yàn)碼。

地址碼表示編碼對(duì)象常住戶口所在縣(市、旗、區(qū))的行政區(qū)劃代碼，按GB/T2260的規(guī)定執(zhí)行。出生日期碼表示編碼對(duì)象出生的年、月、日，按GB/T7408的規(guī)定執(zhí)行，其中年份用四位數(shù)字表示，年、月、日之間不用分隔符。順序碼表示同一地址碼所標(biāo)識(shí)的區(qū)域范圍內(nèi)，對(duì)同年、月、日出生的人員編定的順序號(hào)。順序碼的奇數(shù)分給男性，偶數(shù)分給女性。校驗(yàn)碼是根據(jù)前面十七位數(shù)字碼，按照ISO 7064:1983.MOD 11-2的校驗(yàn)碼規(guī)則計(jì)算出來(lái)的檢驗(yàn)碼。下面舉例說(shuō)明該校驗(yàn)碼計(jì)算方法。

某男性公民身份號(hào)碼17位本體碼為12010519800101001，首先按照公式⑴計(jì)算：

(1)

公式(1)中：

i—表示號(hào)碼字符從右至左包括校驗(yàn)碼在內(nèi)的位置序號(hào)；Ai—表示第i位置上的號(hào)碼字符值；Wi—表示第i位置上的加權(quán)系數(shù)，其數(shù)值依據(jù)公式Wi=2(i-1)(mod 11)計(jì)算得出。

i181716151413121110987654321Ai12010519800101001A1Wi7910584216379105842Ai×Wi71805020294800905002

根據(jù)公式(1)進(jìn)行計(jì)算：

∑(ai×Wi) =(7+18+0+5+0+20+2+9+48++0+0+9+0+5+0+0+2) = 125

125 ÷ 11 = 11 余數(shù)為4

∑(ai×Wi)(mod 11) = 4

然后根據(jù)計(jì)算結(jié)果的余數(shù)，從下面的表中查出相應(yīng)的校驗(yàn)碼，其中X表示校驗(yàn)碼為10，因?yàn)槊總€(gè)人的身份證號(hào)碼都是18位，所以10只能用羅馬數(shù)字X表示。

計(jì)算結(jié)果的余數(shù)012345678910得到的校驗(yàn)碼10X98765432

根據(jù)上表，查出計(jì)算結(jié)果為4對(duì)應(yīng)的校驗(yàn)碼為8，所以該人員的公民身份證號(hào)碼應(yīng)該為 120105198001010018。

二、教學(xué)設(shè)計(jì)的實(shí)現(xiàn)過(guò)程

(一)加權(quán)系數(shù)的計(jì)算

1.如圖1中所示，在B20單元格內(nèi)輸入2，在B19單元格內(nèi)輸入公式“=B20*2”，然后拖動(dòng)B19單元格右下角的填充柄向上復(fù)制公式到B18:B4，利用公式中相對(duì)地址變化就可以計(jì)算出2(i-1)。

2.在C20單元格內(nèi)輸入公式“=MOD(B20,11)”,也可以用插入菜單——函數(shù)來(lái)實(shí)現(xiàn)，然后拖動(dòng)C20單元格右下角的填充柄向上復(fù)制公式到C19:C4，利用公式中相對(duì)地址變化就可以計(jì)算出加權(quán)系數(shù)Wi=MOD(2(i-1),11),其中i=表示2到18。這樣實(shí)現(xiàn)身份證號(hào)碼對(duì)應(yīng)加權(quán)系數(shù)的自動(dòng)生成不需要死記硬背。

(二)身份證號(hào)碼與加權(quán)系數(shù)分別乘積之和

在E20單元格內(nèi)輸入公式“=SUMPRODUCT(C4:C20,D4:D20)”， 利用SUMPRODUCT函數(shù)求身份證號(hào)碼與對(duì)應(yīng)加權(quán)系數(shù)乘積之和。

也可以在E20單元格內(nèi)利用插入菜單——函數(shù)SUMPRODUCT的對(duì)話框來(lái)實(shí)現(xiàn)，如圖2所示。

圖1 身份證號(hào)校驗(yàn)位計(jì)算

圖2 插入函數(shù)SUMPRODUCT界面

(三)求模取余計(jì)算

在F20單元格內(nèi)輸入公式“=MOD(E20,11)”得到計(jì)算出的余數(shù)。

(四)定義余數(shù)與身份證校驗(yàn)碼對(duì)應(yīng)關(guān)系

1.在K4:L13單元格區(qū)域內(nèi)輸入余數(shù)與身份證校驗(yàn)碼對(duì)應(yīng)關(guān)系，實(shí)現(xiàn)反向移位2位的校驗(yàn)方法。

2.選中K4:L13單元格區(qū)域，利用菜單插入——名稱(chēng)——定義的對(duì)話框來(lái)實(shí)現(xiàn)，定義選中的單元格名稱(chēng)為AAA，名稱(chēng)定義可以實(shí)現(xiàn)公式復(fù)制中的絕對(duì)地址保持不變。

(五)求出校驗(yàn)碼

在F21單元格中輸入公式“=VLOOKUP(F20,AAA,2,TRUE)”求出身份證號(hào)碼的校驗(yàn)位值。也可以在F21單元格內(nèi)利用菜單插入-函數(shù)VLOOKUP來(lái)實(shí)現(xiàn)，如圖2所示。利用VLOOKUP找到余數(shù)對(duì)應(yīng)的校驗(yàn)碼。

圖3 單元格區(qū)域名稱(chēng)

圖4 插入函數(shù)VLOOKUP界面

(六)身份證號(hào)碼的正確性檢驗(yàn)

在E7單元格中輸入IF函數(shù)，“=IF($D$21=$F$21,“您輸入的身份證號(hào)碼:正確”,“您輸入的身份證號(hào)碼:錯(cuò)誤”)”，這樣就可以利用IF函數(shù)判斷輸入身份證號(hào)碼是否正確。

(七)身份證號(hào)校驗(yàn)位計(jì)算

在D4:D21單元格區(qū)域輸入身份證號(hào)18位,在F21單元格就可以計(jì)算出身份證號(hào)的校驗(yàn)位值了，并且能判定你輸入身份證號(hào)碼是否正確。

三、所用Excel函數(shù)介紹

MOD函數(shù)：求模取余，返回兩數(shù)相除的余數(shù)。SUMPRODUCT函數(shù)：返回相應(yīng)數(shù)組或區(qū)域乘積之和。VLOOKUP函數(shù)：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。IF函數(shù)：執(zhí)行條件判斷，根據(jù)邏輯計(jì)算的真假值，返回不同結(jié)果，可以使用函數(shù) IF 對(duì)數(shù)值和公式進(jìn)行條件檢測(cè)。

四、結(jié)束語(yǔ)

在本教學(xué)設(shè)計(jì)中巧妙利用了常見(jiàn)的身份證號(hào)校驗(yàn)位計(jì)算做為實(shí)踐教學(xué)目標(biāo)，能吸引學(xué)生的學(xué)習(xí)興趣，綜合運(yùn)用了公式計(jì)算中相對(duì)地址與絕對(duì)地址、MOD、SUMPRODUCT、VLOOKUP、IF等函數(shù)、公式復(fù)制、單元格區(qū)域名稱(chēng)定義等學(xué)習(xí)內(nèi)容。經(jīng)過(guò)筆者在課堂實(shí)踐教學(xué)中應(yīng)用充分調(diào)動(dòng)了學(xué)生學(xué)習(xí)Excel較復(fù)雜功能的熱情，取得了良好的教學(xué)效果。舉一反三，本教學(xué)設(shè)計(jì)思路同樣可以應(yīng)用在集裝箱箱號(hào)核對(duì)數(shù)字計(jì)算和條形碼校驗(yàn)位計(jì)算上。

參考文獻(xiàn)：

[1]肖波.在EXCEL中添加身份證號(hào)碼校驗(yàn)功能[J].長(zhǎng)沙大學(xué)學(xué)報(bào)，2004,(04).

[2]楊潤(rùn)標(biāo).用Excel生成身份證號(hào)碼校驗(yàn)碼[J].電腦知識(shí)與技術(shù),2010,(22).