2015年2月28日 星期六

Excel-身分證字號驗證

網友想要驗證身分證字號是否正確,要如何使用 Excel 來幫這個忙呢?

首先要來理解中華民國國民身份證字號的編碼規則,你可以參考維基百科:

http://zh.wikipedia.org/wiki/中華民國國民身分證

其中第一碼的對應轉換字元:

有些目前已不使用:

其驗證規則:

根據以上的規則,設計以下的公式:

【設計公式】

儲存格E5:=IF(MOD(INT(VLOOKUP(LEFT(E2,1),A2:B27,2,FALSE)/10)+
MOD(VLOOKUP(LEFT(E2,1),A2:B27,2,FALSE),10)*9+SUMPRODUCT(VALUE(
MID(E2,ROW(1:9)+1,1)),{8;7;6;5;4;3;2;1;1}),10)=0,"正確","錯誤")

公式很長很嚇人,分解來看:

(1)

VLOOKUP(LEFT(E2,1),A2:B27,2,FALSE):利用 VLOOKUP 函數來查詢第一個字母所對應的數字為多少。

(2)

INT(VLOOKUP(LEFT(E2,1),A2:B27,2,FALSE)/10):將上式求得的數字取其十位數。

(3)

MOD(VLOOKUP(LEFT(E2,1),A2:B27,2,FALSE),10):將上式求得的數字取其個位數。

(4)

(INT(VLOOKUP(LEFT(E2,1),A2:B27,2,FALSE)/10)+MOD(VLOOKUP(LEFT
(E2,1),A2:B27,2,FALSE),10)*9

根據規則,上式執行:(2)X1+(3)X9

(5)

SUMPRODUCT(VALUE(MID(E2,ROW(1:9)+1,1)),{8;7;6;5;4;3;2;1;1}):依序取出身分證字號中的每個數字,並對應乘以 8, 7, 6, 5, 4, 3, 2, 1, 1,並且予以加總。

(6)

公式:=IF(MOD(第(4)式+第(5)式,10)=0,"正確","錯誤")

將第(4)式和第(5)式的和除以 10,若餘數為0表示為正確的身分證編碼,否則為錯誤編碼。

沒有留言:

張貼留言

檢視其他文章

好康東東