用 Google 試算表驗證身分證字號是否正確
在資料驗證（三）：自訂公式的教學，我寫了一串可以驗證身分證字號的公式：
如果 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 = 88 * 7 = 560 * 6 = 03 * 5 = 158 * 4 = 320 * 3 = 02 * 2 = 45 * 1 = 5
我這邊用 MID 來一個個拿這些數字，從第二個字元（也就是第一個數字）抓到第九個字元（也就是第八個數字），在一個個乘以 8、7、6、5、4、3、2、1：
VALUE(MID(A1, 2, 1)) * 8VALUE(MID(A1, 3, 1)) * 7VALUE(MID(A1, 4, 1)) * 6VALUE(MID(A1, 5, 1)) * 5VALUE(MID(A1, 6, 1)) * 4VALUE(MID(A1, 7, 1)) * 3VALUE(MID(A1, 8, 1)) * 2VALUE(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，我們下個教學見！
此篇文章會顯示動態置底廣告
為什麼會看到廣告
14.2K會員
148內容數
簡潔，快速，有效，
讓你的日常生活、工作生產力大提升！
＿＿＿
快按「加入」，馬上追蹤所有喜特先生的更新，有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱，還有 Google 試算表疑難雜症的解題分享唷！💪
留言0
查看全部
你可能也想看
Google News 追蹤
給定一個9x9的輸入陣列代表數獨題目已經 部分作答 的狀態，
請驗證已經作答的部分是否為合法的Sudoku的輸入。
註:
合法的Sudoku輸入必須滿足這些規則
1~9每一直排恰好出現一次。
1~9每一橫排恰好出現一次。
1~9在3x3的小方陣裏恰好出現一次。
申請攜碼優惠前，先讓我們看看這些常見疑問！
攜碼需要準備的資料
身分證和健保卡或駕照
如果委託他人代辦，需攜帶雙方的身分證和健保卡或駕照，以及門號登記者的印章
公司戶申辦需要攜帶營業登記證、變更登記表（擇一）、負責人身分證和健保卡或駕照，以及公司大小章
是否需要先解約？
只要原電信業者的
第 1 條
印信之製發及使用，依本條例行之。
第 2 條
印信之種類如左：
一、國璽。
二、印。
三、關防。
四、職章。
五、圖記。
題目敘述
輸入給定一個鏈結串列，整體看代表一個十進位的數字，各別看每個節點代表每個digit，分別從最高位~最低位個位數。
要求我們把原本的數字乘以二，並且以鏈結串列的形式返回答案。
原本的英文題目敘述
※ 條件判斷語法
決策中需要處理分歧的狀況，就會用到「if」、「else if」、「else」。
※ 語法結構：
條件式使用小括號()，裡面放判斷式。
要執行的程式碼放在大括號{}裡。
條件式只會有 true 或 false 兩種結果。
※ 常用的比較運算子：
> 大於
< 小於
※ 質數判斷
質數：除了1跟本身,沒有其他的因數。
因數：可以整除的數字。
用到的運算符號：「%」 mod 求餘數。
整除： mod完為0，沒有餘數。
※ 使用for迴圈
let x = 97 // 可以修改成其他數值來測試
//flag標記
let isNotPrime = false
題目敘述
題目會給我們兩個輸入，字串s和字串t，要求我們判定s是否為t的子序列(Subsequence)?
題目的原文敘述
測試範例
Example 1:
Input: s = "abc", t = "ahbgdc"
Output: true
Example 2:
Input:
題目敘述
題目會給定我們兩個字串word1 和 word2。
允許我們不限制次數進行下列兩種操作:
任意調換其中兩個字元的位置。
把字串中的某個字元全部置換成另一個字元，同時把另一個字元同時置換成某個字元。(例如把字串中原本的a都換成b，把原本的b都換成a)
問我們能不能通過上述兩項操作，
題目敘述
題目會給我們一個輸入字串s，題目還保證字串s的長度一定是偶數。
要求我們判定字串s的前半部和後半部是否相似?
在本題中，兩個字串相似的定義為兩個字串都擁有相同的母音英文字母:
註: 母音英文字母為a, e, i, o, u, A, E, I, O, U
題目的原文敘述
測試
給定一個9x9的輸入陣列代表數獨題目已經 部分作答 的狀態，
請驗證已經作答的部分是否為合法的Sudoku的輸入。
註:
合法的Sudoku輸入必須滿足這些規則
1~9每一直排恰好出現一次。
1~9每一橫排恰好出現一次。
1~9在3x3的小方陣裏恰好出現一次。
申請攜碼優惠前，先讓我們看看這些常見疑問！
攜碼需要準備的資料
身分證和健保卡或駕照
如果委託他人代辦，需攜帶雙方的身分證和健保卡或駕照，以及門號登記者的印章
公司戶申辦需要攜帶營業登記證、變更登記表（擇一）、負責人身分證和健保卡或駕照，以及公司大小章
是否需要先解約？
只要原電信業者的
第 1 條
印信之製發及使用，依本條例行之。
第 2 條
印信之種類如左：
一、國璽。
二、印。
三、關防。
四、職章。
五、圖記。
題目敘述
輸入給定一個鏈結串列，整體看代表一個十進位的數字，各別看每個節點代表每個digit，分別從最高位~最低位個位數。
要求我們把原本的數字乘以二，並且以鏈結串列的形式返回答案。
原本的英文題目敘述
※ 條件判斷語法
決策中需要處理分歧的狀況，就會用到「if」、「else if」、「else」。
※ 語法結構：
條件式使用小括號()，裡面放判斷式。
要執行的程式碼放在大括號{}裡。
條件式只會有 true 或 false 兩種結果。
※ 常用的比較運算子：
> 大於
< 小於
※ 質數判斷
質數：除了1跟本身,沒有其他的因數。
因數：可以整除的數字。
用到的運算符號：「%」 mod 求餘數。
整除： mod完為0，沒有餘數。
※ 使用for迴圈
let x = 97 // 可以修改成其他數值來測試
//flag標記
let isNotPrime = false
題目敘述
題目會給我們兩個輸入，字串s和字串t，要求我們判定s是否為t的子序列(Subsequence)?
題目的原文敘述
測試範例
Example 1:
Input: s = "abc", t = "ahbgdc"
Output: true
Example 2:
Input:
題目敘述
題目會給定我們兩個字串word1 和 word2。
允許我們不限制次數進行下列兩種操作:
任意調換其中兩個字元的位置。
把字串中的某個字元全部置換成另一個字元，同時把另一個字元同時置換成某個字元。(例如把字串中原本的a都換成b，把原本的b都換成a)
問我們能不能通過上述兩項操作，
題目敘述
題目會給我們一個輸入字串s，題目還保證字串s的長度一定是偶數。
要求我們判定字串s的前半部和後半部是否相似?
在本題中，兩個字串相似的定義為兩個字串都擁有相同的母音英文字母:
註: 母音英文字母為a, e, i, o, u, A, E, I, O, U
題目的原文敘述
測試