Excel案例分享：利用函數公式檢驗身分證號碼的真假

在之前的文章《Excel案例分享：5個只靠「拖曳」就實現的高效技巧》中，我們學習了5個Excel表格只靠「拖曳」就實現的高效技巧。而今天我們來聊聊Excel檢驗身分證號碼真假的函數公式，快來看看！

說起假疫苗、毒奶粉這些駭人聽聞的事情，真的是憤怒加無奈，對於這類事情，作為普通人的我們，只能希望有關部門能夠加大管理檢查力度，杜絕再次發生這樣的惡性事件。同時對於自己的工作也要更加細心，在許多容易出現錯誤的環節反覆核查，避免產生一些不良的後果，例如員工檔案中的身分證號碼就是一個非常容易發生問題的重災區！

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

一般來說，身分證號碼會出現兩類錯誤，長度錯誤和內容錯誤。

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

一、身分證號組成原則

#首先需要了解一點關於身分證號碼的編碼規則：

#18位身分證號碼組成：ddddddyyyymmddxxsp共18位，其中：1-6位是地區編碼，7-14為出生日期，15-17這三位是性別代碼，最後一位是根據前面的17個數字得到的校驗位。

校驗位的計算規則比較複雜：

（1）前十七位數字本體碼加權求和公式

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

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

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

要檢驗一個身分證號碼是否有誤，原理就是依照這個規則計算出校驗碼，再與最後一位數字去比較，如果不一致就是有問題了。對於這個規則理解起來還是比較費力的，我們舉個例子來看看：

1、將前面的身分證號碼17位數分別乘以不同的係數。從第一位到第十七位的係數分別為：

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

2、將這17位元數字和係數相乘的結果相加。

3、用加出來和除以11，看餘數是多少？

4、餘數只可能有0－1－2－3－4－5－6－7－8－9－10這11個數字。其分別對應的最後一位身分證的號碼為1－0－X －9－8－7－6－5－4－3－2。

5、透過校驗碼比較得知如果餘數是3，就會在身分證的第18位數字上出現的是9。如果對應的數字是10，則身分證的最後一位號碼就是羅馬數字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 ，也就是說其餘數是2。最後透過對應規則就可以知道餘數2對應的檢驗碼是X，所以，可以判定這是一個正確的身分證號碼。

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

#原理明白了，現在的問題就是如何將這個計算過程用Excel的公式來表示出來，在這裡將公式分享給大家：

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

#注意這是個陣列公式，輸入或貼上公式後，需要同時按住Ctrl和shift鍵再回車。

公式看起來很複雜，使用的時候只需要把兩個A2修改為自己表格中身分證號對應的位置。

簡單來說一下公式的想法吧，核心部分就是

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

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

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

結果是假。

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

相關學習推薦：excel教學

以上是Excel案例分享：利用函數公式檢驗身分證號碼的真假的詳細內容。更多資訊請關注PHP中文網其他相關文章！