資料驗證(三):自訂公式

更新於 發佈於 閱讀時間約 8 分鐘

上兩週我們介紹了資料驗證是什麼,要怎麼利用它來製作下拉式選單驗證使用者是否輸入特定的文字、日期和數字,還有當輸入不符合驗證規則的時候、試算表會怎麼樣提醒使用者的設定方式。那麼,這次我們要來接著介紹怎麼用「自訂公式」來設定資料驗證。

簡單來說,它可以讓你用自己想要的函式,客製化資料驗證的規則,讓你除了系統預設的資料驗證外,還有更多可能的資料驗證規則可以應用,例如:

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

如果你有些希望我做的東西,歡迎你在文章下方留言,我也會盡力幫你想想看!


要先知道的事

在設計自訂公式之前,我們先來了解資料驗證是怎麼運作的。我們可以把資料驗證想像成一個只有「真(True)、假(False)」的開關,像電燈一樣,往上按就會亮、往下按就會暗。

舉個例子,我們可以在下拉式選單請使用者輸入一個值:

raw-image

上圖的清單裡有台灣所有的縣市。

如果我在這邊輸入了「台北市」,因為它在清單裡面,所以符合資料驗證的規則,沒出現錯誤:

raw-image

不過如果我寫了「東京都」,因為這並不在清單裡面、不符合資料驗證的規則,就會出現錯誤:

raw-image

也就是說:

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

同樣的道理,如果我們在某儲存格做了一個「輸入正確格式的電子郵件」的資料驗證,那麼:

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

這就是資料驗證背後運作的道理。

所以如果要用自訂公式來做資料驗證的條件,我們要製作一個回傳結果是「真」或是「假」的公式,讓試算表幫忙你驗證資料。

我們可以利用這些函式跟運算子來製作這樣的公式:

raw-image
raw-image

或是只要你的函式結果能回傳 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 上面點右鍵 > 「查看更多儲存格動作」>「資料驗證」,右邊就會看到一個窗格:

raw-image

點選「新增規則」後,條件改成「自訂公式:」,就會像是這樣:

raw-image

接下來我們就會在條件下面「公式」的欄位,填入自己設定的公式,然後做好其他的設定(像是針對無效資料的應對方法、還有拒絕輸入內容視窗的驗證說明文字)再按下儲存就可以了。


驗證輸入的值是否超過 5 個字

我們可以用 LEN 函式來算儲存格的字有幾個,比如說:

=LEN("Hello!")  --> 6
=LEN("你好!") --> 3
=LEN("喜特先生") --> 4

我們也當然可以把 A1 放進去 LEN 函式裡:

=LEN(A1)

再來,要判斷他是不是超過 5 個字,我們就可以在後面加上大於符號(>):

=LEN(A1)>5

這樣就寫完了!把這段公式貼過去,像這樣:

raw-image

那麼這邊的開關就是:

  • 如果 A1 的長度超過 5 → 符合資料驗證條件 → 真假開關「真」(True)
  • 如果 A1 的長度不超過 5 → 不符合資料驗證條件 → 真假開關「假」(False)

按下「完成」,完成設定。

來測試看看效果吧!我在 A1 輸入「Hello!」,因為是六個字(Hello + 驚嘆號),所以符合資料驗證規則,就不會出現警告:

raw-image

可是如果我輸入的是「喜特先生」,只有四個字,就會出現警告訊息:

raw-image


驗證輸入的日期是否晚於今天

我們有一個可以回傳今天日期的函式,叫做 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)

我們一樣把這串貼到剛剛的視窗:

raw-image

下面的「今天」是以 2022 年 12 月 18 號為準。如果我在 A1 輸入了「2030/12/31」,就會晚於今天,所以符合資料驗證規則:

raw-image

反之,如果我寫了 2010/01/01,就會早於今天,也就不符合資料驗證規則了:

raw-image


驗證身分證字號

我們還可以用自訂公式來驗證身分證字號是否正確!台灣的身分證字號其實有一套驗證機制,可以確保它不會被任意冒用。於是我花了一段時間研究,看看怎麼寫出一套規則,就完成了下面這串:

你可以把它全部複製起來,貼到剛剛的視窗:

raw-image

按下儲存,這樣 A1 就可以驗證身分證字號的真假了。如果你有興趣了解這個是怎麼製作的,歡迎到這邊看看:用 Google 試算表驗證身分證字號是否正確



當然,這樣的自訂公式還有很多很多可能,你可以試著參考剛剛的表格,排列組合看看、製作出適合你的資料驗證。

如果你喜歡這次的文章,歡迎你透過這些方法支持我:
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍,即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍,加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作!

想要看更多文章,歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔!

我是喜特先生,Mr. Sheet,我們下個教學見!



留言
avatar-img
留言分享你的想法!
喜特先生 Mr. Sheet -avatar-img
發文者
2024/05/25
資料驗證系列文提及了這篇文章,趕快過去看看吧!
喜特先生 Mr. Sheet -avatar-img
發文者
2023/10/02
AND、OR、NOT(一):多條件判斷提及了這篇文章,趕快過去看看吧!
喜特先生 Mr. Sheet -avatar-img
發文者
2023/07/04
條件式格式(二):自訂公式提及了這篇文章,趕快過去看看吧!
avatar-img
喜特先生官方沙龍
18.3K會員
152內容數
簡潔,快速,有效, 讓你的日常生活、工作生產力大提升! ___ 快按「加入」,馬上追蹤所有喜特先生的更新,有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱,還有 Google 試算表疑難雜症的解題分享唷!💪
2025/04/20
請你試著在 Google 試算表的儲存格上打這個,按下 Enter: =WHATTHEFOXSAY() 會有神奇的事情發生喔 ✨
Thumbnail
2025/04/20
請你試著在 Google 試算表的儲存格上打這個,按下 Enter: =WHATTHEFOXSAY() 會有神奇的事情發生喔 ✨
Thumbnail
2024/06/02
上次介紹了 REPLACE 函式,可以用來取代儲存格內的特定文字。其實我們還有一個相似的函式叫 SUBSTITUTE,也有取代的功能,不過當然有一點不一樣的地方!今天會分享語法的範例,還有跟 REPLACE 的差異在哪。一起來看看!
Thumbnail
2024/06/02
上次介紹了 REPLACE 函式,可以用來取代儲存格內的特定文字。其實我們還有一個相似的函式叫 SUBSTITUTE,也有取代的功能,不過當然有一點不一樣的地方!今天會分享語法的範例,還有跟 REPLACE 的差異在哪。一起來看看!
Thumbnail
2024/05/25
這是文字處理基礎函式的第四篇文章,今天要來介紹 REPLACE 函式! REPLACE 可以取代掉儲存格內的文字,今天會分享一下它語法怎麼寫、也有兩個實際應用的案例。一起來看看! REPLACE 語法 REPLACE 的語法長了一點點,有四個參數要設定: =REPLACE(要取代
Thumbnail
2024/05/25
這是文字處理基礎函式的第四篇文章,今天要來介紹 REPLACE 函式! REPLACE 可以取代掉儲存格內的文字,今天會分享一下它語法怎麼寫、也有兩個實際應用的案例。一起來看看! REPLACE 語法 REPLACE 的語法長了一點點,有四個參數要設定: =REPLACE(要取代
Thumbnail
看更多
你可能也想看
Thumbnail
「欸!這是在哪裡買的?求連結 🥺」 誰叫你太有品味,一發就讓大家跟著剁手手? 讓你回購再回購的生活好物,是時候該介紹出場了吧! 「開箱你的美好生活」現正召喚各路好物的開箱使者 🤩
Thumbnail
「欸!這是在哪裡買的?求連結 🥺」 誰叫你太有品味,一發就讓大家跟著剁手手? 讓你回購再回購的生活好物,是時候該介紹出場了吧! 「開箱你的美好生活」現正召喚各路好物的開箱使者 🤩
Thumbnail
介紹朋友新開的蝦皮選物店『10樓2選物店』,並分享方格子與蝦皮合作的分潤計畫,註冊流程簡單,0成本、無綁約,推薦給想增加收入的讀者。
Thumbnail
介紹朋友新開的蝦皮選物店『10樓2選物店』,並分享方格子與蝦皮合作的分潤計畫,註冊流程簡單,0成本、無綁約,推薦給想增加收入的讀者。
Thumbnail
護照更新網路預約教學,包含預約系統操作教學及現場辦理說明
Thumbnail
護照更新網路預約教學,包含預約系統操作教學及現場辦理說明
Thumbnail
上兩週我們介紹了資料驗證是什麼,要怎麼利用它來製作下拉式選單、驗證使用者是否輸入特定的文字、日期和數字,還有當輸入不符合驗證規則的時候、試算表會怎麼樣提醒使用者的設定方式。那麼,這次我們要來接著介紹怎麼用「自訂公式」來設定資料驗證。
Thumbnail
上兩週我們介紹了資料驗證是什麼,要怎麼利用它來製作下拉式選單、驗證使用者是否輸入特定的文字、日期和數字,還有當輸入不符合驗證規則的時候、試算表會怎麼樣提醒使用者的設定方式。那麼,這次我們要來接著介紹怎麼用「自訂公式」來設定資料驗證。
Thumbnail
先確定你住的地方再來這邊填 Visit Japan Web 是讓我們提前辦理入境手續所需資訊,以前我們可能是在飛機上先寫一些入境資訊,現在是在網路上先弄好了,再簡單一點講就是「入境審查」、「海關申報」這些提前填好!可使用的機場為「成田國際機場、羽田機場、關西國際機場、中部國際機場、福岡機場、新千歲機
Thumbnail
先確定你住的地方再來這邊填 Visit Japan Web 是讓我們提前辦理入境手續所需資訊,以前我們可能是在飛機上先寫一些入境資訊,現在是在網路上先弄好了,再簡單一點講就是「入境審查」、「海關申報」這些提前填好!可使用的機場為「成田國際機場、羽田機場、關西國際機場、中部國際機場、福岡機場、新千歲機
Thumbnail
分享台北市鑑定系統 如何一次查詢大量身分證資料筆數的有效期限並輸出成excel(使用:google colab建置)
Thumbnail
分享台北市鑑定系統 如何一次查詢大量身分證資料筆數的有效期限並輸出成excel(使用:google colab建置)
Thumbnail
如果打兩劑高端就只能PCR惹,要有陰性證明!(最重要的就是這個)另外就是最好到衛生福利部申請疫苗接種數位證明(要PCR的可以多申請檢驗結果數位證明!)另外也很建議照著步驟存到手機的檔案裡面,因為後面MySOS上傳時會用到。 1. 下載&填寫 MySOS 這邊的步驟會需要護照、接種證明,疫苗的文件我就
Thumbnail
如果打兩劑高端就只能PCR惹,要有陰性證明!(最重要的就是這個)另外就是最好到衛生福利部申請疫苗接種數位證明(要PCR的可以多申請檢驗結果數位證明!)另外也很建議照著步驟存到手機的檔案裡面,因為後面MySOS上傳時會用到。 1. 下載&填寫 MySOS 這邊的步驟會需要護照、接種證明,疫苗的文件我就
Thumbnail
關於日本打工度假簽證申請的條件跟規則,台日交流協會上面都寫得很清楚,相關表單也請自行到交流協會網站下載使用,比較不會出錯。此篇除了分享填寫資料的心得外,還有交件的流程。一起來看看吧!
Thumbnail
關於日本打工度假簽證申請的條件跟規則,台日交流協會上面都寫得很清楚,相關表單也請自行到交流協會網站下載使用,比較不會出錯。此篇除了分享填寫資料的心得外,還有交件的流程。一起來看看吧!
Thumbnail
IMPORTRANGE 的即時更新很方便,語法也不長,是個很實用的函式。除了單純的匯入資料以外,還有什麼應用呢?有的!ㄧ一起看下去~
Thumbnail
IMPORTRANGE 的即時更新很方便,語法也不長,是個很實用的函式。除了單純的匯入資料以外,還有什麼應用呢?有的!ㄧ一起看下去~
Thumbnail
開啟程式後,會看到初始畫面如下: 這邊幾個功能大概說明一下: 1.檢查版本,可以讓您確認目前使用的是否為線上最新的版本,按一下版本檢查即可 進入到申報系統後,可以看到系統畫面 如果您將視窗最大化,可以在右下角看到版本、申報日期、提示資訊等 當然操作過程中會遇到一些問題,這個部分我們會慢慢介紹
Thumbnail
開啟程式後,會看到初始畫面如下: 這邊幾個功能大概說明一下: 1.檢查版本,可以讓您確認目前使用的是否為線上最新的版本,按一下版本檢查即可 進入到申報系統後,可以看到系統畫面 如果您將視窗最大化,可以在右下角看到版本、申報日期、提示資訊等 當然操作過程中會遇到一些問題,這個部分我們會慢慢介紹
Thumbnail
newsletter 05/17 on demanding a guarantee for your own request 請大家多多支持和鼓勵訂閱這一份電子報:每一天的生活 Daily Michelle Good Evening! 大家,還好嗎?平安!這個週末,非常的不平靜,從原本的二級警戒
Thumbnail
newsletter 05/17 on demanding a guarantee for your own request 請大家多多支持和鼓勵訂閱這一份電子報:每一天的生活 Daily Michelle Good Evening! 大家,還好嗎?平安!這個週末,非常的不平靜,從原本的二級警戒
追蹤感興趣的內容從 Google News 追蹤更多 vocus 的最新精選內容追蹤 Google News