條件式格式（二）：自訂公式
2023/07/04閱讀時間約 8 分鐘
之前在
條件式格式（一）
提到了怎麼用條件式格式的功能，把想看到的資料、以更醒目的方法顯示，更方便我們判讀數據，像是這樣：
這邊的表格用了條件式格式的功能，讓試算表在成績是低於 60 分的儲存格上顯示紅底白字、100 分的儲存格顯示綠底白字等格式。
不過，如果想要設定比較複雜一點、更客製化一點的條件，可以怎麼做呢？這就要提到條件式格式稍稍進階的設定，叫做「自訂公式」，今天我想介紹這個！
你可以用自訂公式做這些：
- 當儲存格內的數字是奇數時，讓儲存格的字變藍色
- 當儲存格內的文字數大於 5 個字時，讓儲存格的字變紅色
- 當儲存格內的身份證字號符合規則時，讓儲存格的背景變綠色
當然還有更多可能性，可以試試看！還不太確定條件式格式是什麼、該怎麼設定的話，可以回到之前的教學喔（延伸閱讀：
條件式格式（一）：基本概念
）。
我們開始囉 (*´ω`)人(´ω`*)
「自訂公式」在哪？
一樣按照之前的方式，你有兩個方法可以做。
你可以到目錄的「格式」>「條件式格式設定」：
又或是直接在要設定的儲存格（或範圍上）按右鍵，「查看更多儲存格動作」>「條件式格式設定」：
不論你用哪個方法，你都會看到視窗的右手邊會出現一個窗格：
在「格式規則」那邊的下拉式選單，選擇選單最後面的「自訂公式」：
你右手邊的窗格應該就會像這樣：
到這邊設定就告一個段落了！
接下來問題來了，「自訂公式」這邊要怎麼寫？
跟「資料驗證」很像
我想提到之前寫的「
資料驗證（三）：自訂公式
」開關的概念。
資料驗證像成一個只有「真（True）、假（False）」的開關，像電燈一樣，往上按就會亮、往下按就會暗。舉個例子，我們可以在資料驗證的下拉式選單裡，請使用者輸入一個值：
上圖的清單裡有台灣所有的縣市。
如果我在這邊輸入了「台北市」，因為它在清單裡面，所以符合資料驗證的規則，沒出現錯誤：
不過如果我寫了「東京都」，因為這並不在清單裡面、不符合資料驗證的規則，就會出現錯誤：
也就是說：
- 輸入清單內有的資訊 → 符合資料驗證條件 → 真假開關「真」（True）
- 輸入清單內沒有的資訊 → 不符合資料驗證條件 → 真假開關「假」（False）
而條件式格式的概念其實也是這樣的，就是「儲存格只要符合特定條件就會套用格式」，像是開頭提到的成績表：
這邊的開關就是：
- 成績是低於 60 分時
→ 符合條件式格式的條件 → 真假開關「真」（True）→ 儲存格上顯示紅底白字 - 成績是 100 分時
→ 符合條件式格式的條件 → 真假開關「真」（True）→ 儲存格上顯示綠底白字 - 不符合任何條件時 → 真假開關「假」（False）→ 儲存格上顯示原樣
所以我們等等要寫的自訂公式，其實也是製作一個開關，做一個有真、有假的開關。我們可以利用這些函式跟運算子來製作這樣的公式：
或是只要你的函式結果能回傳 TRUE 或 FALSE 也可以，像是：
=IF(A1 > 50, TRUE, FALSE)
=IFNA(VLOOKUP(A1, Range!A:C, 2, FALSE), FALSE)
=IFERROR(MOD(A1, 5), FALSE)
如果還是不太清楚怎麼實際操作，那也沒關係，跟著教學一起做做看！
你可以先到雲端硬碟開一個新的試算表檔案，或是點這裡也可以。
我們今天會試著在儲存格 A1 做資料驗證，所以下面自訂公式的儲存格參照都會寫 A1。如果你想在別的地方做，那記得儲存格參照也記得改一下。當然，如果你有絕對參照、或是相對參照的需求，你也可以補上 $ 字（延伸閱讀：
相對參照跟絕對參照
）。
如果這是你不確定怎麼下手的話，可以先想兩件事：
- 要指定的條件是什麼？
- 要製作的格式是什麼？
我們等等都會用這個思路，帶著你做下去，慢慢跟著來喔！
一起動手做做看！
當儲存格內的數字是奇數時，讓儲存格的字變藍色
- 要指定的條件：數字是奇數
- 要製作的格式：藍色的字
判斷數字是否是奇數的方法有很多，我這邊用簡單的 ISODD 函式來解決。
ISODD 函式很簡單好用，把要判斷的數字放在它後面、再用括號括起來即可。如果是奇數的話會回傳 TRUE、是偶數的話則是 FALSE、非數字的話則會回傳錯誤：
=ISODD(5) --> TRUE=ISODD(8) --> FALSE=ISODD("喜特先生") --> #VALUE! 錯誤
所以同樣道理，我們把要設定的儲存格放進去括號即可：
=ISODD(A1)
再來是格式，往下一點會看到「格式設定樣式」。
點右邊數來第二個（Ａ）的圖示，就可以挑選字的顏色了：
挑個喜歡的藍色吧！另外，現在預設的背景是綠色的，你也可以再把右邊數來第一個「油漆桶」的圖示點開來，換無色。
按下「完成」，到這邊就設定完畢了！
我們可以在 A1 那邊測試，輸入數字看看：
當儲存格內的文字數大於 5 個字時，讓儲存格的字變紅色
- 要指定的條件：文字數大於 5
- 要製作的格式：紅色的字
再來是第二題，我們可以用 LEN 函式來算儲存格的字有幾個，比如說：
=LEN("Hello!") --> 6
=LEN("你好！") --> 3
=LEN("喜特先生") --> 4我們也當然可以把 A1 放進去 LEN 函式裡：
=LEN(A1)
再來，要判斷他是不是超過 5 個字，我們就可以在後面加上大於符號（>）：
=LEN(A1)>5
這樣就寫完了！把這段公式貼過去，像這樣：
條件設定好了，再來是格式的設定，把字體顏色選紅色即可。設定完成後就會像這樣：
一樣來測試看看吧！
當儲存格內的身份證字號符合規則時，讓儲存格的背景變綠色
- 要指定的條件：身份證字號符合規則
- 要製作的格式：背景變綠
我們還可以用自訂公式來驗證身分證字號是否正確！台灣的身分證字號其實有一套驗證機制，可以確保它不會被任意冒用。於是我花了一段時間研究，看看怎麼寫出一套規則，就完成了下面這串：
你可以把它全部複製起來，貼到自訂公式那邊：
再來是格式設定，我把字的顏色調成「無」（也就是自動），然後背景換成綠色：
按下完成，這樣當 A1 是正確的身分證字號時，背景就會是綠色的了。試試看吧！
如果你有興趣了解這串是怎麼製作的，歡迎到這邊看看：
用 Google 試算表驗證身分證字號是否正確
。
所以做到這邊，如果你已經很熟悉資料驗證的自訂公式，你會發現條件式格式的自訂公式也是相當類似的！我也很建議你多方嘗試，試試看不同的條件，創造出不同的效果。
如果你喜歡這次的文章，歡迎你透過這些方法支持我：
・按下愛心、按下儲存
・留言告訴我你的想法
・追蹤喜特先生，即時看到我發布的教學
・追蹤喜特先生的 Facebook
・加入喜特先生的官方沙龍，考慮加入每月小額訂閱方案
・或是按這邊一次性小額贊助我的創作！
想要看更多文章，歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔！
我是喜特先生，Mr. Sheet，我們下個教學見！
留言0
查看全部
