用 Google 試算表驗證身分證字號是否正確
閱讀時間約 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 = 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，我們下個教學見！
12.5K會員
147內容數
簡潔，快速，有效，
讓你的日常生活、工作生產力大提升！
＿＿＿
快按「加入」，馬上追蹤所有喜特先生的更新，有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱，還有 Google 試算表疑難雜症的解題分享唷！💪
留言0
查看全部
你可能也想看
Google News 追蹤
這個秋，Chill 嗨嗨！穿搭美美去賞楓，裝備款款去露營⋯⋯你的秋天怎麼過？秋日 To Do List 等你分享！
秋季全站徵文，我們準備了五個創作主題，參賽還有機會獲得「火烤兩用鍋」，一起來看看如何參加吧～
11/20日NVDA即將公布最新一期的財報, 今天Sell Side的分析師, 開始調高目標價, 市場的股價也開始反應, 未來一週NVDA將重新回到美股市場的焦點, 今天我們要分析NVDA Sell Side怎麼看待這次NVDA的財報預測, 以及實際上Buy Side的倉位及操作, 從
Hi 大家好，我是Ethan😊
相近大家都知道保濕是皮膚保養中最基本，也是最重要的一步。無論是在畫室裡長時間對著畫布，還是在旅途中面對各種氣候變化，保持皮膚的水分平衡對我來說至關重要。保濕化妝水不僅能迅速為皮膚補水，還能提升後續保養品的吸收效率。
曾經，我的保養程序簡單到只包括清潔和隨意上乳液
對於某些資料需要打分數並且還要視覺化呈現的情境，不是用函數就是得用設定格式化條件來達成。
但是對於Google Sheet來說，他就是一個內建的功能。
<🖼️圖文教學>
簡單4個步驟，就可以擁有可以用下拉式選擇的星星評分哦
選取範圍
插入
智慧型方塊
評分
安卓手機的Google RCS服務被詐騙集團利用，將各種形式的詐騙簡訊發送給一般民眾，如果想要減少收取詐騙訊息，可以按照本文教學關閉Google RCS
當工作節奏愈來愈快，有效的時間管理和工作流程至關重要。Google Task 是一款由 Google 提供的待辦事項管理工具。透過 No Code 自動化流程，除了能幫助你更好地組織工作外，也能輕鬆整合待辦事項的前後流程，提高效率並確保不漏掉重要項目！
如果你想調查，國內的民眾對單一事件的看法，那你絕對不能不用
Google 趨勢!
痞客邦、Medium和方格子的搜尋次數趨勢如何？方格子最近發燒的主題又是哪些？和Medium、痞客邦又有什麼差異？曾經最熱門的痞客邦又有什麼危機呢？
很多人應該最近都被 ChatGPT 的問答截圖給刷屏，甚至很多人說他是 Google killer，這篇分享給大家ChatGPT到底能做到什麼，他對Google有什麼影響
4G 夠強，到底要 5G 幹嘛？Google 到底看上什麼？但其實，除了看 4K 影片、聽 Hi-Fi 音樂以外，5G 還可應用在各種生活場域之中，如娛樂方面，雲端串流遊戲可讓玩家不用買主機組電腦，提供順暢網路隨處玩遊戲；看球賽時，不同角度切換 VR 360度環繞效果亦高度仰賴其技術。
前提，這是在使用google work space有設定好日立資源的狀態下(這要請管理員參考GOOGLE說明設定)
如何正確預約會議室資源
我有借會議室成功，對方說我也有借成功，正常狀況下一定是有人選了地點而非會議室
by 七大辯
1.建立會議的時候，應該是要選【會議室】而非地點，地點寫甚麼他都
Google在2022年7月即將全面停止延展型文字廣告(ETA)的建立，為了讓你更了解這件事對投放Google廣告的影響，我們回頭重新檢視Google廣告，了解延展型文字廣告與回應式搜尋廣告(RSA)出現的原因、兩者之間的差異，以及全面採用回應式搜尋廣告(RSA)的影響。
機器學習運算提高性能
這個秋，Chill 嗨嗨！穿搭美美去賞楓，裝備款款去露營⋯⋯你的秋天怎麼過？秋日 To Do List 等你分享！
秋季全站徵文，我們準備了五個創作主題，參賽還有機會獲得「火烤兩用鍋」，一起來看看如何參加吧～
11/20日NVDA即將公布最新一期的財報, 今天Sell Side的分析師, 開始調高目標價, 市場的股價也開始反應, 未來一週NVDA將重新回到美股市場的焦點, 今天我們要分析NVDA Sell Side怎麼看待這次NVDA的財報預測, 以及實際上Buy Side的倉位及操作, 從
Hi 大家好，我是Ethan😊
相近大家都知道保濕是皮膚保養中最基本，也是最重要的一步。無論是在畫室裡長時間對著畫布，還是在旅途中面對各種氣候變化，保持皮膚的水分平衡對我來說至關重要。保濕化妝水不僅能迅速為皮膚補水，還能提升後續保養品的吸收效率。
曾經，我的保養程序簡單到只包括清潔和隨意上乳液
對於某些資料需要打分數並且還要視覺化呈現的情境，不是用函數就是得用設定格式化條件來達成。
但是對於Google Sheet來說，他就是一個內建的功能。
<🖼️圖文教學>
簡單4個步驟，就可以擁有可以用下拉式選擇的星星評分哦
選取範圍
插入
智慧型方塊
評分
安卓手機的Google RCS服務被詐騙集團利用，將各種形式的詐騙簡訊發送給一般民眾，如果想要減少收取詐騙訊息，可以按照本文教學關閉Google RCS
當工作節奏愈來愈快，有效的時間管理和工作流程至關重要。Google Task 是一款由 Google 提供的待辦事項管理工具。透過 No Code 自動化流程，除了能幫助你更好地組織工作外，也能輕鬆整合待辦事項的前後流程，提高效率並確保不漏掉重要項目！
如果你想調查，國內的民眾對單一事件的看法，那你絕對不能不用
Google 趨勢!
痞客邦、Medium和方格子的搜尋次數趨勢如何？方格子最近發燒的主題又是哪些？和Medium、痞客邦又有什麼差異？曾經最熱門的痞客邦又有什麼危機呢？
很多人應該最近都被 ChatGPT 的問答截圖給刷屏，甚至很多人說他是 Google killer，這篇分享給大家ChatGPT到底能做到什麼，他對Google有什麼影響
4G 夠強，到底要 5G 幹嘛？Google 到底看上什麼？但其實，除了看 4K 影片、聽 Hi-Fi 音樂以外，5G 還可應用在各種生活場域之中，如娛樂方面，雲端串流遊戲可讓玩家不用買主機組電腦，提供順暢網路隨處玩遊戲；看球賽時，不同角度切換 VR 360度環繞效果亦高度仰賴其技術。
前提，這是在使用google work space有設定好日立資源的狀態下(這要請管理員參考GOOGLE說明設定)
如何正確預約會議室資源
我有借會議室成功，對方說我也有借成功，正常狀況下一定是有人選了地點而非會議室
by 七大辯
1.建立會議的時候，應該是要選【會議室】而非地點，地點寫甚麼他都
Google在2022年7月即將全面停止延展型文字廣告(ETA)的建立，為了讓你更了解這件事對投放Google廣告的影響，我們回頭重新檢視Google廣告，了解延展型文字廣告與回應式搜尋廣告(RSA)出現的原因、兩者之間的差異，以及全面採用回應式搜尋廣告(RSA)的影響。
機器學習運算提高性能