學不完．教不停．用不盡

我國的身分證號碼是基於某一種規則之下產生，如何使用Excel來驗證身份證號碼是否為有效的號碼呢？

身分證號碼的第一碼是英文字母，代表的出生後入籍的縣市的代號，如下表所示：

而第2到第10個碼為阿拉伯數字，其中第2碼代表性別：1為男性、2為女性，第3碼至第9碼為流水號。最後一碼為檢查碼，而它的檢查方法透過以下的公式換算而來：

(1) 驗算是否正確(正確：V，不正確：X)

儲存格B2：=IF(VALUE(RIGHT(A2,1))=10-MOD(SUM(K6:T6),10),"V","X")

(2) 判定縣市別

儲存格C2：=VLOOKUP(LEFT(A2,1),$F$2:$H$25,2)

(3) 判定性別

儲存格D2：=IF(MID(A2,2,1)="1","男","女")

試著使用以下的公式產生一個亂數的號碼：

=CHAR(INT(RAND()*26+65))&INT(RAND()*2+1)&INT(RAND()*8999999+1000000)&INT(RAND()*10)

如果不想透過上述表格換算，而想直接在儲存格中判定，試試以下的公式：(以儲存格B3為例)

=IF(VALUE(RIGHT(A3,1))=MOD(10-MOD(MID(VLOOKUP(LEFT(A3,1),$F$2:$H$25,3),1,1)+MID(VLOOKUP(LEFT(A3,1),$F$2:$H$25,3),2,1)*9+MID(A3,2,1)*8+MID(A3,3,1)*7+MID(A3,4,1)*6++MID(A3,5,1)*5+MID(A3,6,1)*4+MID(A3,7,1)*3+MID(A3,8,1)*2+MID(A3,9,1)*1,10),10),"V","X")

如果想要縮短一點公式的長度，可以試試陣列公式：(以儲存格B4為例)(輸入要按Ctrl+Shift+Enter)

{=IF(VALUE(RIGHT(A4,1))=MOD(10-MOD(MID(VLOOKUP(LEFT(A4,1),$F$2:$H$25,3),1,1)+MID(VLOOKUP(LEFT(A4,1),$F$2:$H$25,3),2,1)*9+SUM(MID(A4,ROW(INDIRECT("2:9")),1)*(10-ROW(INDIRECT("2:9")))),10),10),"V","X")}