學不完．教不停．用不盡

網友想要驗證身分證字號是否正確，要如何使用 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表示為正確的身分證編碼，否則為錯誤編碼。