2010年6月29日 星期二

Excel-身分證號碼驗證

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

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

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

image02

(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","男","女")

image03

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

=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")}

沒有留言:

張貼留言

好康東東