主題

【教學】Excel函數:用LEFT、RIGHT、IF、VLOOKUP函數寫身分證字號驗證器

愛天使亞夜 | 2021-03-18 21:11:34 | 巴幣 22 | 人氣 205

各位好這裡是亞夜。
今天就加碼介紹一個應用:寫身分證字號驗證器。
注意,
是寫驗證器而不是寫產生器,
請不要拿去做違法用途使用。

要寫驗證器,
我們就需要先了解身分證字號的格式規則,
否則就無法驗證了。

身份證字號大家都知道有1個英文字母+9個數字組成,
當中每一個英文字母對應2個數字,
也就是說,
身份證字號本身可以看成ABCDEFGHIJK這樣11個數字的數列。
如果這個數列滿足:
A+9B+8C+7D+6E+5F+4G+3H+2I+J+K為10的倍數,
那麼這組字號就是有效的;
反之這組字號就是無效的。

規則了解了,
那就可以寫公式了對吧?
可是,
身份證字號帶有一個英文字母導致無法直接做運算耶,
那怎麼辦?
這就是今天的重點了:拆字。

今天會介紹到4個函數:
LEFT(A,B)=回傳A文字從左邊數來B個位元的字符
LEFTB(A,B)=回傳A文字從左邊數來B個雙位元的字符
RIGHT(A,B)=回傳A文字從右邊數來B個位元的字符
RIGHTB(A,B)=回傳A文字從右邊數來B個雙位元的字符

看起來複雜,
其實很簡單。
LEFT就是從左邊數,
RIGHT就是從右邊數。
如果你要找的字符是半形字符就用沒有B的,
如果你要找的字符是全形字符如中文漢字或日本假名等就用有B的。

我們看看例子:
 

我們對「bigbang6」這串字符進行取值,
取左3的場合就是big,
取右3的場合就是ng6,
這樣應該可以理解這函數是在幹嘛了吧?

所以要拆身份證字號應該很好裡解了吧?
左邊1個字符是英文,
右邊9個字符是數字,
因此我們只需要利用上述兩個函數就能拆出需要的數值:


將英文與數字的部分拆出來後,
我們就能利用昨天說的MOD跟ROUNDDOWN函數去抓出各個位數的數字出來了。
如果忘記要怎麼做的,
可以回去看看昨天這篇

好了,
我們把位數拆出來了,
不過還有一件事情要做就是,
把英文轉換成數字。
因為身份證字號的驗證公式當中是把英文字母當成兩個數字來看,
所以我們必須做這個動作。

首先對應表如下:
A B C D E F G
10 11 12 13 14 15 16
H I J K L M N
17 34 18 19 20 21 22
O P Q R S T U
35 23 24 25 26 27 28
V W X Y Z
29 32 30 31 33
(順序為什麼怪怪的?
 其實是有原因的。
 原本規定的時候呢是沒有I/O/W/Z的,
 也就是說26個字母當中實際只使用22個,
 然後定義A為10(最小的二位數)後按流水號排列,
 因此就排出10~31共22個數值。
 接著加入金門縣W跟連江縣Z,
 所以往後接32、33;
 接著因為新竹市O跟嘉義市I升格省轄市,
 所以再往後接34、35。
 現在所有字母都出現過了,
 儘管台中縣L、台南縣R、高雄縣S跟陽明山管理局Y因為縣市合併跟行政區虛位化的緣故消失了)

雖然26個字母對應的數字不一樣,
但是記得我們的驗證規則嗎?
身份證字號的字母部分的兩個數字分別是規則中的A+9B,
因此我們現在就可以直接把數值給改寫出來變成:

A B C D E F G
1 10 19 28 37 46 55
H I J K L M N
64 39 73 82 2 11 20
O P Q R S T U
48 29 38 47 56 65 74
V W X Y Z
83 21 3 12 30

然後,
反正最後都要除以10求餘數,
除以10求餘數是什麼意思?
不就是我們昨天一直講的MOD(X,10)嗎?
那這不就是求值個位數的意思嗎?
所以這個表可以進一步簡化:

A B C D E F G
1 0 9 8 7 6 5
H I J K L M N
4 9 3 2 2 1 0
O P Q R S T U
8 9 8 7 6 5 4
V W X Y Z
3 1 3 2 0

甚至可以反過來寫:
BNZ AMW KLY JVX HU GT FS ER DOQ CIP
0 1 2 3 4 5 6 7 8 9

所以我們可以利用IF()跟OR()函數來轉換。
IF(A,B,C):如果A成立,回傳B值;如果A不成立,回傳C值。
OR(A,B...):如果A或B或...中有一個成立,回傳「成立」,否則回傳「不成立」

簡單的寫法是:
「如果是B或N或Z,就回傳0,否則回傳『如果是AMW,就回傳1,否則……』」
這樣疊10層,
但是比較早期版本的Excel的函數包含數最多只能疊7層,
所以這個做法就……不太好。
Excel2016以後的版本有新的函數叫IFS,
它可以讓你直接把所有條件寫在一起並分別回傳,
那麼就可以這麼做。
具體寫法是:(假設B1儲存格是身份證字號的字母)
=IFS(OR(B1="B",B1="N",B1="Z"),0,OR(B1="A",B1="M",B1="W"),1...)
這樣,
不過函數還是會很長很麻煩。
這時我們還可以用令一種比較簡單的寫法就是:VLOOKUP()
VLOOKUP(A,B,C,D):
 A=輸入值
 B=參考表格
 C=表格位於「第幾個欄位」
 D=是否概略搜尋(TRUE=近似搜尋;FALSE=精確搜尋)

首先我們先建立一個對照表:
這個對照表代表哪個字母對應哪個數字,
這樣我們就方便了。
接著使用VLOOKUP(A,B,C,D)函數,
A是我們的字母所在的儲存格,
B是這個表格的位置(M1:N26)
C是對應值所在欄位,這裡的場合是第二欄
D是精確搜尋,所以要輸入FALSE
這樣就完成了取值。
接下來就只要輸入公式就好。
公式是「A+9B+8C+7D+6E+5F+4G+3H+2I+J+K為10的倍數」,
現在A+9B我們已經取完了,
所以就是把後面的數字分別乘上係數再加總,
最後再把這個值對10取餘數就可以了。
因此:
應該不難理解吧?
公式的結果是10的倍數就代表餘數為0,
因此MOD(公式,10)=0就是我們要的判斷式。
如果判斷式為真就正確,
否則就是不正確,
這樣應該很好裡解了吧?

我們甚至可以亂打一組號碼進去:
我們亂打一組身份證字號進去,
馬上就發現這個不正確被打槍了,
因為公式算出來結果是135,
無法被10整除。
當然,
你想知道下一個可以被10整除的數值是多少還不簡單?
對位數9做加減讓它可以被10整除就好。
以這個例子來說,
位數9是1,
那我們+5上去,
公式就會從135變成140,
因此位數9改成6就沒問題啦:

就是這麼簡單。

【後記】

今天算是昨天的延伸,
順便介紹點新東西。

雖然本篇是以身份證字號驗證器做例子,
但也只是教你驗證身份證字號是不是正確的字號罷了,
可沒叫你拿去做犯罪用途嘿!
偽造文書是不可以的喔!


封面圖片:按摩中的鯊鯊
送禮物贊助創作者 !
0
留言

創作回應

相關創作

更多創作