站長資訊網(wǎng)
最全最豐富的資訊網(wǎng)站

Excel案例分享:利用函數(shù)公式來檢驗身份證號碼的真假

在之前的文章《Excel案例分享:5個只靠“拖拉”就實現(xiàn)的高效技巧》中,我們學習了5個Excel表格只靠“拖拉”就實現(xiàn)的高效技巧。而今天我們聊聊Excel檢驗身份證號碼真假的函數(shù)公式,快來看看!

Excel案例分享:利用函數(shù)公式來檢驗身份證號碼的真假

手機如何做表格:點擊查看

說起假疫苗、毒奶粉這些駭人聽聞的事情,真的是憤怒加無奈,對于這類事情,作為普通人的我們,只能希望有關部門能夠加大管理檢查力度,杜絕再次發(fā)生這樣的惡性事件。同時對于自己的工作也要更加細心,在很多容易出現(xiàn)錯誤的環(huán)節(jié)反復核查,避免產(chǎn)生一些不良的后果,比如員工檔案中的身份證號碼就是一個非常容易發(fā)生問題的重災區(qū)!

今天我們就來和大家分享一個核查身份證號碼是否有錯誤的公式。

一般來說,身份證號碼會出現(xiàn)兩類錯誤,長度錯誤和內容錯誤。

出現(xiàn)錯誤的原因大多數(shù)都輸入過程中發(fā)生的,對于長度錯誤來說,目前都是18位的號碼,如果長度出現(xiàn)錯誤,比較容易發(fā)現(xiàn),但是如果其中的某一個數(shù)字出現(xiàn)錯誤,那就非常難以發(fā)現(xiàn),以后就有可能會造成一些嚴重的后果,因此,今天重點就是核查內容的正確性。

一、身份證號組成原理

首先需要了解一點關于身份證號碼的編碼規(guī)則:

Excel案例分享:利用函數(shù)公式來檢驗身份證號碼的真假

18位身份證號碼組成:ddddddyyyymmddxxsp共18位,其中:1-6位是地區(qū)編碼,7-14為出生日期,15-17這三位是性別代碼,最后一位是根據(jù)前面的17個數(shù)字得到的校驗位。

校驗位的計算規(guī)則比較復雜:

(1)前十七位數(shù)字本體碼加權求和公式

S = Sum(Ai * Wi), i = 0, … , 16 ,先對前17位數(shù)字的權求和

Ai:表示第i位置上的身份證號碼數(shù)字值

Wi:表示第i位置上的加權因子

Wi: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2

(2)計算模

Y = mod(S, 11)

(3)通過模得到對應的校驗碼

Y: 0 1 2 3 4 5 6 7 8 9 10

校驗碼: 1 0 X 9 8 7 6 5 4 3 2

要檢驗一個身份證號碼是否有誤,原理就是按照這個規(guī)則計算出校驗碼,再與最后一位數(shù)字去比較,如果不一致就是有問題了。對于這個規(guī)則理解起來還是比較費勁的,我們舉個例子來看看:

1、將前面的身份證號碼17位數(shù)分別乘以不同的系數(shù)。從第一位到第十七位的系數(shù)分別為:

7-9-10-5-8-4-2-1-6-3-7-9-10-5-8-4-2。

2、將這17位數(shù)字和系數(shù)相乘的結果相加。

3、用加出來和除以11,看余數(shù)是多少?

4、余數(shù)只可能有0-1-2-3-4-5-6-7-8-9-10這11個數(shù)字。其分別對應的最后一位身份證的號碼為1-0-X -9-8-7-6-5-4-3-2。

5、通過校驗碼對比得知如果余數(shù)是3,就會在身份證的第18位數(shù)字上出現(xiàn)的是9。如果對應的數(shù)字是10,身份證的最后一位號碼就是羅馬數(shù)字2。

例如:某男性的身份證號碼為【53010219200508011x】, 我們看看這個身份證是不是合法的身份證。

首先我們得出前17位的乘積和

(5*7)+(3*9)+(0*10)+(1*5)+(0*8)+(2*4)+(1*2)+(9*1)+(2*6)+(0*3)+(0*7)+(5*9)+(0*10)+(8*5)+(0*8)+(1*4)+(1*2)

是189,然后用189除以11得出的結果是189/11=17—-2,也就是說其余數(shù)是2。最后通過對應規(guī)則就可以知道余數(shù)2對應的檢驗碼是X,所以,可以判定這是一個正確的身份證號碼。

二、Excel檢驗身份證號碼真假

原理明白了,現(xiàn)在的問題就是如何將這個計算過程用Excel的公式來表示出來,在這里將公式分享給大家:

=IF(RIGHT(A2)=MID("10X98765432",MOD(SUM(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),"真","假")

注意這是個數(shù)組公式,輸入或者粘貼公式后,需要同時按住Ctrl和shift鍵再回車。

Excel案例分享:利用函數(shù)公式來檢驗身份證號碼的真假

公式看上去非常復雜,使用的時候只需要把兩個A2修改為自己表格中身份證號對應的位置即可。

簡單來說一下公式的思路吧,核心部分就是

MID("10X98765432",MOD(SUM(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)

這一串,作用是將前面的17位數(shù)字按照校驗碼的規(guī)則計算出來,再與RIGHT(A2)做比較,RIGHT(A2)的作用就是從A2中提取最右邊的一位出來,根據(jù)比較結果用if函數(shù)來進行判斷。

我們可以再用這個公式來檢驗一下文章開頭圖片中的身份證號碼是否正確:

Excel案例分享:利用函數(shù)公式來檢驗身份證號碼的真假

結果是假。

不妨用這個公式去檢驗一下你的表格中是否有錯誤的身份證號碼吧,對于判定為錯誤的號碼及時進行更正,將問題扼殺在萌芽階段!

相關學習推薦:excel教程

贊(0)
分享到: 更多 (0)
網(wǎng)站地圖   滬ICP備18035694號-2    滬公網(wǎng)安備31011702889846號
AV无码精品一区二区三区| www亚洲精品久久久乳| 欧日韩国产无码专区| 久久精品福利视频| 2019日韩中文字幕MV| 亚洲精品视频观看| 久久亚洲日韩看片无码| 精品一区二区三区四区在线播放 | 99re6在线精品视频免费播放 | 国产精品龙口护士门在线观看| 亚洲精品无AMM毛片| 久久精品这里热有精品| 欧美日韩精品乱国产| 国产精品青草久久久久婷婷| 最新精品国偷自产在线| 国产精品久久久久影院| 97精品免费视频| 成人精品一区二区三区中文字幕| 国产精品久久久久影院免费| 亚洲福利一区二区精品秒拍| 99久久国产综合精品麻豆| 色婷婷色综合激情国产日韩| 国产精品亚洲片夜色在线| 国产偷久久久精品专区| 日韩精品无码永久免费网站| 大陆精大陆国产国语精品| 在线日韩日本国产亚洲| 国产精品中文字幕在线观看| 精品亚洲综合久久中文字幕| 日韩影片在线观看| 老司机无码精品A| 无码精品人妻一区二区三区免费看| 合区精品中文字幕| 国产麻豆va精品视频| 91精品国产91久久| 久久精品国产一区| 成人国产精品一区二区网站| 日本精品αv中文字幕| 亚洲国产精品网站久久| 国产精品自产拍在线观看花钱看| 国产精品自产拍在线网站|