什麼時候需要校驗身份證號碼合法性呢？
- 1.防止輸入人員隨手輸入一個18位數字冒充身份證號碼
- 2.防止輸入手誤,有時候並非故意輸錯
- 注意本方法無法檢測身份證是否真實存在,只能從格式上檢查號碼是否合法
身份證號碼校驗基本原理
第1步：把號碼的前17位分別乘以不同的係數,係數表如下
第2步：把17位數字和係數相乘的結果相加，加和值再除以11，取其餘數。用得到的餘數與身份證第18位號碼進行對照,餘數與身份證號碼第18位對應關係如下表
例如：某男士的身份證號碼是622924199605211012。首先我們得出前17位與係數的乘積和是320，除以11的餘數是1，對應第18位數是0。所以這是一個不合法的身份證號碼。
判斷身份證號碼是否合法的函數公式
計算第18位數
=IF(LEN($C2)=18,MID("10X98765432",MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)+1,1),"長度錯誤")
判斷是否合法
=IF(LEN($C2)=18,IF(MID("10X98765432",MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)+1,1)=RIGHT($C2,1),"合法","不合法"),"長度錯誤")
其中，C2為身份證號碼，$B$2:$B$18為第1到17個係數。這個一般存到另一個sheet裡或者頂一個名稱,與實際使用的表分開
函數分步講解
- VALUE(MID($C2,ROW($1:$17),1))
ROW函數返回一個1到17的數組，配個mid函數依次取出C2號碼中前17個數字，並將截取的文本用value轉換為數值
- SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)
用SUMPRODUCT函數返回MID($C2,ROW($1:$17),1)數組和$B$2:$B$18(校驗表)區域對應相乘的和
- MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)
用mod對上一步求乘積之取11的餘數
MID("10X98765432",MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)+1,1)
用MID函數截取10X98765432中的1位數，即身份證號碼的第18位。例如餘數是0，0+1=1，截取第1位；餘數是2，2+1=3，截取第3位
IF(MID("10X98765432",MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)+1,1)=RIGHT($C2,1),"合法","不合法")
IF函數判斷計算值是否等於身份證號碼第18位，相等則合法，不相等則不合法
=IF(LEN($C2)=18,IF(MID("10X98765432",MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)+1,1)=RIGHT($C2,1),"合法","不合法"),"長度錯誤")
在計算第18位數之前先判斷輸入的身份證號碼是否是18位，如果是則往下計算，不是則直接顯示長度錯誤
這個就是用Excel檢查身份證號碼合法性的方法了，實際使用可直接複製公式，$C2改為身份證所在單元格,$B$2:$B$18改為你自己存放的對照表區域或者自定義名稱
=IF(LEN($C2)=18,IF(MID("10X98765432",MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)+1,1)=RIGHT($C2,1),"合法","不合法"),"長度錯誤")
PS:在頭條搜索中直接搜索 "Excel880 關鍵字",例如搜索 "Excel880 圖表" 就可以看到我發表過和圖表有關的文章和視頻
鳴謝：看到就是緣分，請各位看官多多點贊、評論和分享哦！定製表格可私信EXCEL880A
閱讀更多 Excel880實例教學 的文章
