條件式格式（二）：自訂公式
閱讀時間約 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，我們下個教學見！
11.2K會員
147內容數
簡潔，快速，有效，
讓你的日常生活、工作生產力大提升！
＿＿＿
快按「加入」，馬上追蹤所有喜特先生的更新，有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱，還有 Google 試算表疑難雜症的解題分享唷！💪
留言0
查看全部
你可能也想看
Google News 追蹤
接下來第二部分我們持續討論美國總統大選如何佈局, 以及選前一週到年底的操作策略建議
分析兩位候選人政策利多/ 利空的板塊和股票
🤔為什麼團長的能力是死亡筆記本?
🤔為什麼像是死亡筆記本呢？
🤨作者巧思-讓妮翁死亡合理的幾個伏筆
重點不是技術分析的壓力在哪裡，而是股市是否具備持續漲升的條件？
股市漲升的條件是企業獲利與資金條件，今年企業獲利應該不會太好看，匯率與貨幣供給狀況，不具有推升股市大規模行情的資金條件。
今天一位馬拉松牙醫師問我，妳女兒還好嗎？(上週二確診，由我撩落去照顧)
我說，姐姐第一天發燒一直睡，第二天退燒，輕微頭暈，但第三天就想回學校了。之後會輕微咳嗽，但精神不錯。
「那你們家很快！(不論是症狀或者恢復力吧）」他說。此期間，已經不止一個人對我說「你們家的反應都好快」，為什麼？
在目前任職的工作中感受到極大壓力，無奈合約未滿，只能期許自己在未來換工作時，能夠找到符合自己專業的職場，才不會浪費過去這些年的努力。
【案主心得】
被喊卡其實沒關係，我已經全部有頭緒了，現在的工作瓶頸怎麼來的，我真正在意的是什麼，我真正討厭的地方在哪裡。
攝影師：Katerina Holmes，連結：Pexels
為了準備情感教育的演講內容，我無意間找到一個「男生擇偶條件大哉問」的影片，我的投影片小助理提議可以拿來做演講開頭的討論，結果效果出奇地好，也可以漸漸感受到現在年輕孩子們擇偶條件的變化。
這個街頭訪談針對路上的男大學生進行調查，關於選擇伴
二胎房貸是什麼? 二胎房貸有哪些優點?
當一棟房子首次設定抵押權來申請貸款時，則稱為一胎房貸，也稱為一順位房貸。若是在房子有「一胎房貸」的情況下，再度把房子拿去銀行或民間機構進行抵押，則稱為「二胎房貸」，又稱為二順位房貸。
相對於信用貸款需要付出的高利率，以及負債比22倍的額度限制，二胎房貸不受
唐小鴨，男生，智能障礙輕度，他講話很沙啞、音量不大，身高155公分、體重只有40公斤，非常瘦小。沒有情緒或行為的問題，陪同面試一個負責現場的產品組裝、檢驗、包裝出貨等，談勞動條件時，我提到...薪資大概就是勞基法規定的時薪...
沒想到，老闆娘這時臉色有點改變
條件式可以說是程式重要的能力，也是判斷作業流程細心程度的地方。
條件式 (Condition) ，顧名思義，只有符合條件才做事。
身為一個好士兵，就要執行自家長官的命令。如果是來自敵方隊伍、屬下、路人的命令，就可以快樂的無視他們。
關鍵程式碼大概就會是這樣：
if (發令者 == 自家長官) {執行
過去大半年來港警的瘋言狂行，已經令人忘記「亞洲最佳」曾經的稱號，今時今日我們還能夠面對戲中自稱「正義」的香港警察嗎？所以只能向外求吧，最近期就是由木村拓哉主演，以日本警校為背景的富士新春特別劇《教場》，日本播出不久，香港觀眾就可以免費收看，或者冥冥中自有天意。
接下來第二部分我們持續討論美國總統大選如何佈局, 以及選前一週到年底的操作策略建議
分析兩位候選人政策利多/ 利空的板塊和股票
🤔為什麼團長的能力是死亡筆記本?
🤔為什麼像是死亡筆記本呢？
🤨作者巧思-讓妮翁死亡合理的幾個伏筆
重點不是技術分析的壓力在哪裡，而是股市是否具備持續漲升的條件？
股市漲升的條件是企業獲利與資金條件，今年企業獲利應該不會太好看，匯率與貨幣供給狀況，不具有推升股市大規模行情的資金條件。
今天一位馬拉松牙醫師問我，妳女兒還好嗎？(上週二確診，由我撩落去照顧)
我說，姐姐第一天發燒一直睡，第二天退燒，輕微頭暈，但第三天就想回學校了。之後會輕微咳嗽，但精神不錯。
「那你們家很快！(不論是症狀或者恢復力吧）」他說。此期間，已經不止一個人對我說「你們家的反應都好快」，為什麼？
在目前任職的工作中感受到極大壓力，無奈合約未滿，只能期許自己在未來換工作時，能夠找到符合自己專業的職場，才不會浪費過去這些年的努力。
【案主心得】
被喊卡其實沒關係，我已經全部有頭緒了，現在的工作瓶頸怎麼來的，我真正在意的是什麼，我真正討厭的地方在哪裡。
攝影師：Katerina Holmes，連結：Pexels
為了準備情感教育的演講內容，我無意間找到一個「男生擇偶條件大哉問」的影片，我的投影片小助理提議可以拿來做演講開頭的討論，結果效果出奇地好，也可以漸漸感受到現在年輕孩子們擇偶條件的變化。
這個街頭訪談針對路上的男大學生進行調查，關於選擇伴
二胎房貸是什麼? 二胎房貸有哪些優點?
當一棟房子首次設定抵押權來申請貸款時，則稱為一胎房貸，也稱為一順位房貸。若是在房子有「一胎房貸」的情況下，再度把房子拿去銀行或民間機構進行抵押，則稱為「二胎房貸」，又稱為二順位房貸。
相對於信用貸款需要付出的高利率，以及負債比22倍的額度限制，二胎房貸不受
唐小鴨，男生，智能障礙輕度，他講話很沙啞、音量不大，身高155公分、體重只有40公斤，非常瘦小。沒有情緒或行為的問題，陪同面試一個負責現場的產品組裝、檢驗、包裝出貨等，談勞動條件時，我提到...薪資大概就是勞基法規定的時薪...
沒想到，老闆娘這時臉色有點改變
條件式可以說是程式重要的能力，也是判斷作業流程細心程度的地方。
條件式 (Condition) ，顧名思義，只有符合條件才做事。
身為一個好士兵，就要執行自家長官的命令。如果是來自敵方隊伍、屬下、路人的命令，就可以快樂的無視他們。
關鍵程式碼大概就會是這樣：
if (發令者 == 自家長官) {執行
過去大半年來港警的瘋言狂行，已經令人忘記「亞洲最佳」曾經的稱號，今時今日我們還能夠面對戲中自稱「正義」的香港警察嗎？所以只能向外求吧，最近期就是由木村拓哉主演，以日本警校為背景的富士新春特別劇《教場》，日本播出不久，香港觀眾就可以免費收看，或者冥冥中自有天意。