資料驗證（三）：自訂公式
更新於 發佈於 閱讀時間約 7 分鐘
上兩週我們介紹了資料驗證是什麼，要怎麼利用它來製作下拉式選單、驗證使用者是否輸入特定的文字、日期和數字，還有當輸入不符合驗證規則的時候、試算表會怎麼樣提醒使用者的設定方式。那麼，這次我們要來接著介紹怎麼用「自訂公式」來設定資料驗證。
簡單來說，它可以讓你用自己想要的函式，客製化資料驗證的規則，讓你除了系統預設的資料驗證外，還有更多可能的資料驗證規則可以應用，例如：
- 限定輸入奇數
- 限定輸入某長度的資料
- 限定輸入今天起七天內的日期
- 限定輸入對的身分證字號
- 還可以更進一步延伸製作兩段式的下拉式選單
如果你有些希望我做的東西，歡迎你在文章下方留言，我也會盡力幫你想想看！
要先知道的事
在設計自訂公式之前，我們先來了解資料驗證是怎麼運作的。我們可以把資料驗證想像成一個只有「真（True）、假（False）」的開關，像電燈一樣，往上按就會亮、往下按就會暗。
舉個例子，我們可以在下拉式選單請使用者輸入一個值：
上圖的清單裡有台灣所有的縣市。
如果我在這邊輸入了「台北市」，因為它在清單裡面，所以符合資料驗證的規則，沒出現錯誤：
不過如果我寫了「東京都」，因為這並不在清單裡面、不符合資料驗證的規則，就會出現錯誤：
也就是說：
- 輸入清單內有的資訊 → 符合資料驗證條件 → 真假開關「真」（True）
- 輸入清單內沒有的資訊 → 不符合資料驗證條件 → 真假開關「假」（False）
同樣的道理，如果我們在某儲存格做了一個「輸入正確格式的電子郵件」的資料驗證，那麼：
- 輸入正確格式的電子郵件（aaahelloaaa123@gmail.com）
→ 符合資料驗證條件 → 真假開關「真」（True） - 輸入錯誤格式的電子郵件（www.google.com）
→ 不符合資料驗證條件 → 真假開關「假」（True）
這就是資料驗證背後運作的道理。
所以如果要用自訂公式來做資料驗證的條件，我們要製作一個回傳結果是「真」或是「假」的公式，讓試算表幫忙你驗證資料。
我們可以利用這些函式跟運算子來製作這樣的公式：
或是只要你的函式結果能回傳 TRUE 或 FALSE 也可以，像是：
=IF(A1 > 50, TRUE, FALSE)=IFNA(VLOOKUP(A1, Range!A:C, 2, FALSE), FALSE)=IFERROR(MOD(A1, 5), FALSE)
如果還是不太清楚怎麼實際操作，那也沒關係，跟著教學一起做做看！
練習時間
先到雲端硬碟開一個新的試算表檔案，或是點這裡也可以。
我們今天會試著在儲存格 A1 做資料驗證，所以下面自訂公式的儲存格參照都會寫 A1。如果你想在別的地方做，那記得儲存格參照也記得改一下。當然，如果你有絕對參照、或是相對參照的需求，你也可以補上 $ 字（延伸閱讀：相對參照與絕對參照）。
我們在 A1 上面設定資料驗證，點選「資料」>「資料驗證」，或是在 A1 上面點右鍵 > 「查看更多儲存格動作」>「資料驗證」，右邊就會看到一個窗格：
點選「新增規則」後，條件改成「自訂公式：」，就會像是這樣：
接下來我們就會在條件下面「公式」的欄位，填入自己設定的公式，然後做好其他的設定（像是針對無效資料的應對方法、還有拒絕輸入內容視窗的驗證說明文字）再按下儲存就可以了。
驗證輸入的值是否超過 5 個字
我們可以用 LEN 函式來算儲存格的字有幾個，比如說：
=LEN("Hello!") --> 6=LEN("你好！") --> 3=LEN("喜特先生") --> 4
我們也當然可以把 A1 放進去 LEN 函式裡：
=LEN(A1)
再來，要判斷他是不是超過 5 個字，我們就可以在後面加上大於符號（>）：
=LEN(A1)>5
這樣就寫完了！把這段公式貼過去，像這樣：
那麼這邊的開關就是：
- 如果 A1 的長度超過 5 → 符合資料驗證條件 → 真假開關「真」（True）
- 如果 A1 的長度不超過 5 → 不符合資料驗證條件 → 真假開關「假」（False）
按下「完成」，完成設定。
來測試看看效果吧！我在 A1 輸入「Hello!」，因為是六個字（Hello + 驚嘆號），所以符合資料驗證規則，就不會出現警告：
可是如果我輸入的是「喜特先生」，只有四個字，就會出現警告訊息：
驗證輸入的日期是否晚於今天
我們有一個可以回傳今天日期的函式，叫做 TODAY：
=TODAY()
括號裡面什麼都不用放，直接把上面這串貼在儲存格上就會出現今天的日期。
這個函式的結果會隨著實際的日期更動，比如說我在 1 月 1 號下了 TODAY，結果會出現 1 月 1 號，但我隔天打開試算表的時候，就會看到 1 月 2 號了。
那「晚於」又要怎麼在試算表上呈現呢？先簡單提一下「日期」在試算表裡面代表的意思。
「日期」在試算表裡算是一種數字，我們可以把日期轉換成數字，方便做加減運算和比較。比如說截稿日期的 2022 年 12 月 18 日，在試算表轉換過後會得到「44913」，意思是從 1900 年 1 月 1 日算起，這天是第 44913 天。所以，在試算表的邏輯裡，只要日期越早，值就會越小；日期越晚，值就越大。
好，回來我們要解決的問題。如果要做「A1 日期晚於今天」的資料驗證規則，其實就是「A1 大於今天」：
=A1>TODAY()
輕鬆簡單！我們製作了這樣的開關：
- 如果 A1 的日期晚於今天 → 符合資料驗證條件 → 真假開關「真」（True）
- 如果 A1 的日期不晚於今天 → 不符合資料驗證條件 → 真假開關「假」（False）
我們一樣把這串貼到剛剛的視窗：
下面的「今天」是以 2022 年 12 月 18 號為準。如果我在 A1 輸入了「2030/12/31」，就會晚於今天，所以符合資料驗證規則：
反之，如果我寫了 2010/01/01，就會早於今天，也就不符合資料驗證規則了：
驗證身分證字號
我們還可以用自訂公式來驗證身分證字號是否正確！台灣的身分證字號其實有一套驗證機制，可以確保它不會被任意冒用。於是我花了一段時間研究，看看怎麼寫出一套規則，就完成了下面這串：
你可以把它全部複製起來，貼到剛剛的視窗：
按下儲存，這樣 A1 就可以驗證身分證字號的真假了。如果你有興趣了解這個是怎麼製作的，歡迎到這邊看看：用 Google 試算表驗證身分證字號是否正確。
當然，這樣的自訂公式還有很多很多可能，你可以試著參考剛剛的表格，排列組合看看、製作出適合你的資料驗證。
如果你喜歡這次的文章，歡迎你透過這些方法支持我：
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍，即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍，加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作！
想要看更多文章，歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔！
我是喜特先生，Mr. Sheet，我們下個教學見！
14.5K會員
148內容數
簡潔，快速，有效，
讓你的日常生活、工作生產力大提升！
＿＿＿
快按「加入」，馬上追蹤所有喜特先生的更新，有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱，還有 Google 試算表疑難雜症的解題分享唷！💪
留言0
查看全部
你可能也想看
Google News 追蹤
隨著理財資訊的普及，越來越多台灣人不再將資產侷限於台股，而是將視野拓展到國際市場。特別是美國市場，其豐富的理財選擇，讓不少人開始思考將資金配置於海外市場的可能性。
然而，要參與美國市場並不只是盲目跟隨標的這麼簡單，而是需要策略和方式，尤其對新手而言，除了選股以外還會遇到語言、開戶流程、Ap
嘿，大家新年快樂~ 新年大家都在做什麼呢？
跨年夜的我趕工製作某個外包設計案，在工作告一段落時趕上倒數。
然後和兩個小孩過了一個忙亂的元旦。在深夜時刻，看到朋友傳來的解籤網站，興致勃勃熬夜體驗了一下，覺得非常好玩，或許有人玩過了，但還是想寫上來分享紀錄一下~
申請原因:
房產糾紛時釐清產權所有人和房屋登記狀況
購買中古屋時調查房東身分和房屋登記情況
了解目前房產價值
其他法律或商業需求
常用功能:
建物門牌查詢:通過地址查詢門牌號碼
申請登記謄本:查看產權登記資訊
申請地價謄本:了解房產估值
申請方式:
線上:全國地政電子謄本系統(需
在前一篇我們已經成功地建立簽核表單及簽核節點並關聯回請假表單，而本篇會接著介紹如何管理簽核節點狀態並同步更新簽核表單狀態。
本文介紹瞭如何在後端系統開發時設計不同表單的簽核流程，包括請假表單和採購表單。以及如何動態生成簽核表單，並建立簽核節點。另外還介紹瞭如何利用繼承來簡化簽核流程的設定。
※ 什麼是資料庫正規化？為什麼需要正規化？
什麼是資料庫正規化？
資料庫正規化是一種設計關聯式資料庫的方法，目的是建立良好結構的關聯表，主要目的有二：
去除重複性：建立沒有重複的關聯表。因為重複資料不只浪費資料庫的儲存空間，而且會產生資料維護上的問題。
去除不一致的相依性：資料相依是指關聯表
有些服務建議書或投資計畫書送審後，
經主辦機構專家評審，
會形成正式的評審意見，
有些認真負責的專家，
還會將其個人意見以書面形式提交
如何準確高效地回復專家評審意見，
是一門嚴謹的專業技巧，
意見修改回復對照表若寫得好，
複核人員從表中就能看出報告修改情況，
根本不用再去對
當開啟試算表（EXCEL等）的累加（SUM）及離散度，標準差（STDEV）的運算功能後，逐一統計的累進報票式選票統計表就可以退休了，而且全國一萬七千多所的數據不待一所所列出，就可以用較小選區（例如嘉義市198所，宜蘭縣431所等）的統計過程證明統計結果都是正確的，尤其是將計算式列出（隱藏前面的
這篇文章介紹了一個在 Excel 中將郵遞區號加上括號的公式，並分析了公式的原理。這是一篇對於 Excel 公式拆解感興趣的人有價值的文章。
本篇主要是設計，當【沒有任何數值】與【原本就有數值】這兩種情況結合在一起時的 VBA 解決方案。分享內容包括張忍大師的函數解決方法。文章中包含影片檔案下載以及參考文獻連結。
我看完影片只有一個想法，台灣目前還是用傳統方式開票還有利用人力的方式進行，
我認為這樣並不是很對，因為會增加人為疏失的可能性以外，還有可能造成做票或是小動作的嫌疑，我認為即便有人監票，那種很會『變魔術』的人，也會讓監票員造成困難
我身為一名軟體工程師，是要想 solution 的，
臨近選舉，讓我想到之前在網上看到一篇文，當過選務人員的人們都表示一堆人不懂投票規則，每次都有許多記名票，讓我十分震驚⋯
其中最大宗的就是「投票蓋私章」‼️
我瀏覽了上千則留言，統計出以下情形的人可能會無意地導致自己的票成為無效票：
❶首投族，因為緊張一時忘記。
❷沒看到桌上放的圈選工具。
隨著理財資訊的普及，越來越多台灣人不再將資產侷限於台股，而是將視野拓展到國際市場。特別是美國市場，其豐富的理財選擇，讓不少人開始思考將資金配置於海外市場的可能性。
然而，要參與美國市場並不只是盲目跟隨標的這麼簡單，而是需要策略和方式，尤其對新手而言，除了選股以外還會遇到語言、開戶流程、Ap
嘿，大家新年快樂~ 新年大家都在做什麼呢？
跨年夜的我趕工製作某個外包設計案，在工作告一段落時趕上倒數。
然後和兩個小孩過了一個忙亂的元旦。在深夜時刻，看到朋友傳來的解籤網站，興致勃勃熬夜體驗了一下，覺得非常好玩，或許有人玩過了，但還是想寫上來分享紀錄一下~
申請原因:
房產糾紛時釐清產權所有人和房屋登記狀況
購買中古屋時調查房東身分和房屋登記情況
了解目前房產價值
其他法律或商業需求
常用功能:
建物門牌查詢:通過地址查詢門牌號碼
申請登記謄本:查看產權登記資訊
申請地價謄本:了解房產估值
申請方式:
線上:全國地政電子謄本系統(需
在前一篇我們已經成功地建立簽核表單及簽核節點並關聯回請假表單，而本篇會接著介紹如何管理簽核節點狀態並同步更新簽核表單狀態。
本文介紹瞭如何在後端系統開發時設計不同表單的簽核流程，包括請假表單和採購表單。以及如何動態生成簽核表單，並建立簽核節點。另外還介紹瞭如何利用繼承來簡化簽核流程的設定。
※ 什麼是資料庫正規化？為什麼需要正規化？
什麼是資料庫正規化？
資料庫正規化是一種設計關聯式資料庫的方法，目的是建立良好結構的關聯表，主要目的有二：
去除重複性：建立沒有重複的關聯表。因為重複資料不只浪費資料庫的儲存空間，而且會產生資料維護上的問題。
去除不一致的相依性：資料相依是指關聯表
有些服務建議書或投資計畫書送審後，
經主辦機構專家評審，
會形成正式的評審意見，
有些認真負責的專家，
還會將其個人意見以書面形式提交
如何準確高效地回復專家評審意見，
是一門嚴謹的專業技巧，
意見修改回復對照表若寫得好，
複核人員從表中就能看出報告修改情況，
根本不用再去對
當開啟試算表（EXCEL等）的累加（SUM）及離散度，標準差（STDEV）的運算功能後，逐一統計的累進報票式選票統計表就可以退休了，而且全國一萬七千多所的數據不待一所所列出，就可以用較小選區（例如嘉義市198所，宜蘭縣431所等）的統計過程證明統計結果都是正確的，尤其是將計算式列出（隱藏前面的
這篇文章介紹了一個在 Excel 中將郵遞區號加上括號的公式，並分析了公式的原理。這是一篇對於 Excel 公式拆解感興趣的人有價值的文章。
本篇主要是設計，當【沒有任何數值】與【原本就有數值】這兩種情況結合在一起時的 VBA 解決方案。分享內容包括張忍大師的函數解決方法。文章中包含影片檔案下載以及參考文獻連結。
我看完影片只有一個想法，台灣目前還是用傳統方式開票還有利用人力的方式進行，
我認為這樣並不是很對，因為會增加人為疏失的可能性以外，還有可能造成做票或是小動作的嫌疑，我認為即便有人監票，那種很會『變魔術』的人，也會讓監票員造成困難
我身為一名軟體工程師，是要想 solution 的，
臨近選舉，讓我想到之前在網上看到一篇文，當過選務人員的人們都表示一堆人不懂投票規則，每次都有許多記名票，讓我十分震驚⋯
其中最大宗的就是「投票蓋私章」‼️
我瀏覽了上千則留言，統計出以下情形的人可能會無意地導致自己的票成為無效票：
❶首投族，因為緊張一時忘記。
❷沒看到桌上放的圈選工具。