在資料驗證（三）：自訂公式的教學，我寫了一串可以驗證身分證字號的公式：
如果 A1 是正確的身分證字號格式就會是 TRUE、錯誤的話則是 FALSE。
我會在這篇文稍稍解釋一下上面這串是怎麼做的，也歡迎大家在文章下方留言，告訴我你的看法、或是有更好的建議也可以跟我說說！驗證的條件有哪些？
這些條件分別是：
- 條件 1：字串長度必須是 10 個字元
- 條件 2：第一個字元必須是大寫英文字母
- 條件 3：第二個字元必須是 1、2、8、9 其中一個數字
- 條件 4：符合流水號驗證規則
有興趣的話，一起來看看這是怎麼寫的吧！
條件 1：字串長度必須是 10 個字元
IF(LEN(A1) = 10, TRUE, FALSE)身分證字號總共 10 個字元，我們用簡單的 IF 跟 LEN 函式就可以了。只要是 10 個字元，就會回傳 TRUE，反之則是 FALSE。
條件 2：第一個字元必須是大寫英文字母
REGEXMATCH(LEFT(A1, 1), "[A-Z]")先用 LEFT 函式取得整個儲存格的第一個字元：
LEFT(A1, 1)再來用 REGEXMATCH 來檢查這個字元是不是符合規則運算式：
REGEXMATCH(..., "[A-Z]")右手邊的「"[A-Z]"」是指字元必須要符合 A 到 Z 裡面其中一個字母的意思。
條件 3：第二個字元必須是 1、2、8、9 其中一個數字
第二個字元就是英文字母後的第一個數字，目前（2022 年）台灣的規定是允許 1、2、8、9 這四個數字出現。寫成公式之後：
IFERROR(
OR(VALUE(MID(A1, 2, 1)) = 1,
VALUE(MID(A1, 2, 1)) = 2,
VALUE(MID(A1, 2, 1)) = 8,
VALUE(MID(A1, 2, 1)) = 9), FALSE)
先試著用 MID 取 A1 的第二個字元：
MID(A1, 2, 1)不過 MID 取得的字元會辨識成文字，沒辦法直接用「等於」跟數字比較（會產生錯誤），所以要再外面包一層 VALUE 把這個字元轉換成數值。
VALUE(MID(...))再來是外層的 OR，只要取得的數值是 1、2、8 或是 9 的話，就會回傳 TRUE：
OR(VALUE(MID(A1, 2, 1)) = 1,
VALUE(MID(A1, 2, 1)) = 2,
VALUE(MID(A1, 2, 1)) = 8,
VALUE(MID(A1, 2, 1)) = 9)
最後是最外面的 IFERROR。如果在這過程中出現任何錯誤，就會給 FALSE，代表這個字元不在我們要的規則裡面：
IFERROR(OR(...), FALSE)條件 4：符合流水號驗證規則
再來就是稍稍複雜的流水號驗證規則了。這邊有四件事要做：
(a): 處理首字字母
a-1. 先把首字字母轉換成指定的數字
a-2. 把 a-1 得到的數字的個位數乘以 9，然後再加上十位數
(b): 處理第一個數字到第八個數字
b. 把這些數字個別乘以 8、7、6、5、4、3、2、1
(c): 把 (a) 跟 (b) 相加，還要再加上最後一碼驗證碼。
(d): 如果 (c) 的結果是 10 的倍數，就符合驗證規則。
我們來拆解一下。
(a): 處理首字字母
這邊是首字字母轉換成數值的轉換表：
- 如果我的身分證字號是 A 開頭，那就是 10。那麼把 10 這個數字拆開來：
1. 個位數乘以 9 → 0*9 = 0
2. 十位數是 1
所以 0 + 1 = 1。 - 如果我的身分證字號是 Z 開頭，那就是 33。把 33 這個數字拆開來：
1. 個位數乘以 9 → 3*9 = 27
2. 十位數是 3
所以 27 + 3 = 30。
因為資料驗證的自訂公式不方便再寫 VLOOKUP 進去做查表，所以我直接手動先把每個字母的個位數乘好再加上十位數，再用 SWITCH 函式把取得的英文字母做轉換：
SWITCH(LEFT(A1, 1),
"A", 1, "B", 10, "C", 19, "D", 28, "E", 37, "F", 46, "G", 55,
"H", 64, "I", 39, "J", 73, "K", 82, "L", 2, "M", 11, "N", 20,
"O", 48, "P", 29, "Q", 38, "R", 47, "S", 56, "T", 65, "U", 74,
"V", 83, "W", 21, "X", 3, "Y", 12, "Z", 30)
(b): 處理第一個數字到第八個數字
假設有個身分證字號的數字部分是：
180380252我們先把最後一碼（2）拿掉，也就是「18038025」。接下來要把這些數字個別乘以 8、7、6、5、4、3、2、1：
1 * 8 = 8
8 * 7 = 56
0 * 6 = 0
3 * 5 = 15
8 * 4 = 32
0 * 3 = 0
2 * 2 = 4
5 * 1 = 5
我這邊用 MID 來一個個拿這些數字，從第二個字元（也就是第一個數字）抓到第九個字元（也就是第八個數字），在一個個乘以 8、7、6、5、4、3、2、1：
VALUE(MID(A1, 2, 1)) * 8
VALUE(MID(A1, 3, 1)) * 7
VALUE(MID(A1, 4, 1)) * 6
VALUE(MID(A1, 5, 1)) * 5
VALUE(MID(A1, 6, 1)) * 4
VALUE(MID(A1, 7, 1)) * 3
VALUE(MID(A1, 8, 1)) * 2
VALUE(MID(A1, 9, 1)) * 1
(c): 把 (a) 跟 (b) 相加，還要再加上最後一碼驗證碼。
再來就是把前面 (a) 跟 (b) 的結果加起來，再加上最後一碼數字的驗證碼。提到多個數字的相加，用 SUM 當然是首選：
SUM( (a) 的結果, (b) 的結果, 驗證碼)(d): 如果 (c) 的結果是 10 的倍數，就符合驗證規則。
終於到最後一步了！接下來就是看看 SUM 的結果是不是 10 的倍數了。
我們可以簡單用 IF 跟 MOD 這兩個函式，取得 SUM 的結果除以 10 的餘數。如果餘數是 0 的話，那麼恭喜！這個數字符合驗證的規則；如果不是的話，那就是無效的身分證字號了。
IF(MOD(SUM(...), 10) = 0, TRUE, FALSE)最後再以防萬一，如果剛剛 (a)、(b)、(c) 的過程中有任何錯誤，就代表可能處理到不是數字的值，就用 IFERROR 來回傳 FALSE，代表這個 A1 不是有效的身分證字號了。
最後的最後
再來就是把每個條件用 AND 串起來，把所有剛剛的條件規則包起來：
=AND(條件 1, 條件 2, 條件 3, 條件 4)也就是説，只要上面所有條件回傳 TRUE，這串 AND 就會回傳 TRUE。反之，只要有任一個條件是 FALSE，那麼 AND 就會回傳 FALSE 了。
這樣就完成啦！
如果你喜歡這次的文章，歡迎你透過這些方法支持我：
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍，即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍，加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作！
想要看更多文章，歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔！
我是喜特先生，Mr. Sheet，我們下個教學見！
