資料驗證（三）：自訂公式

• 限定輸入奇數
• 限定輸入某長度的資料
• 限定輸入今天起七天內的日期
• 限定輸入對的身分證字號
• 還可以更進一步延伸製作兩段式的下拉式選單

要先知道的事

• 輸入清單內有的資訊 → 符合資料驗證條件 → 真假開關「真」（True）
• 輸入清單內沒有的資訊 → 不符合資料驗證條件 → 真假開關「假」（False）

• 輸入正確格式的電子郵件（aaahelloaaa123@gmail.com） → 符合資料驗證條件 → 真假開關「真」（True）
• 輸入錯誤格式的電子郵件（www.google.com） → 不符合資料驗證條件 → 真假開關「假」（True）

=IF(A1 > 50, TRUE, FALSE)

=IFNA(VLOOKUP(A1, Range!A:C, 2, FALSE), FALSE)

=IFERROR(MOD(A1, 5), FALSE)

練習時間

驗證輸入的值是否超過 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，我們下個教學見！