用 Google 試算表驗證身分證字號是否正確

2022/12/18閱讀時間約 8 分鐘
資料驗證(三):自訂公式的教學,我寫了一串可以驗證身分證字號的公式:
如果 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,我們下個教學見!
3.3K會員
122內容數
簡潔,快速,有效, 讓你的日常生活、工作生產力大提升! ___ 快按「加入」,馬上追蹤所有喜特先生的更新,有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱,還有 Google 試算表疑難雜症的解題分享唷!💪
留言0
查看全部
發表第一個留言支持創作者!