各位好這裡是亞夜。
昨天介紹了用ROUND函數跟MOD函數來進行位數分離，
今天就加碼介紹一個應用：寫身分證字號驗證器。
注意，
是寫驗證器而不是寫產生器，
請不要拿去做違法用途使用。
要寫驗證器，
我們就需要先了解身分證字號的格式規則，
否則就無法驗證了。
身份證字號大家都知道有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就沒問題啦：
就是這麼簡單。
【後記】
今天算是昨天的延伸，
順便介紹點新東西。
雖然本篇是以身份證字號驗證器做例子，
但也只是教你驗證身份證字號是不是正確的字號罷了，
可沒叫你拿去做犯罪用途嘿！
偽造文書是不可以的喔！
封面圖片：按摩中的鯊鯊
達人
