Google Sheet條件式格式自訂公式15個進階技巧完整教學
前言：為什麼需要掌握Google Sheet條件式格式的自訂公式？
在數據分析與報表製作中，條件式格式(conditional formatting)是非常強大的工具，它能讓我們根據特定條件自動改變儲存格的外觀（如顏色、字型等），幫助我們快速識別數據模式和異常值。而Google Sheets的自訂公式功能，更是將條件式格式的靈活性提升到另一個層次。
根據統計，超過80%的Google Sheets進階使用者會使用自訂公式來實現更複雜的條件格式設定，但真正精通這項功能的人卻不到30%。本文將分享15個實用的進階技巧，幫助您完全掌握Google Sheets條件式格式自訂公式的強大功能。
基礎回顧：條件式格式的基本設定
在進入進階技巧前，讓我們快速回顧一下Google Sheets條件式格式的基本設定方法：
- 選取要套用格式的儲存格範圍
- 點選選單中的「格式」>「條件式格式設定」
- 在右側邊欄中選擇「自訂公式」選項
- 輸入您的條件公式
- 設定符合條件時要套用的格式樣式
- 點擊「完成」按鈕
重要概念：在自訂公式中，公式的計算結果為TRUE時，格式就會被套用。公式應以第一個選取儲存格的角度編寫，其他儲存格會相對應地調整。
15個Google Sheets條件式格式自訂公式進階技巧
技巧1：基於其他工作表的數據設定條件格式
許多使用者不知道，條件式格式自訂公式可以引用其他工作表的數據。這在製作儀表板時特別有用。
markdown
=INDIRECT("Sheet2!A1")>100
這個公式會檢查Sheet2的A1儲存格是否大於100，如果是，則套用格式。如果需要引用一個範圍：
markdown
=A1>INDIRECT("Sheet2!$B$2")
注意事項：使用INDIRECT函數時，必須用引號括住工作表名稱和儲存格引用。
技巧2：根據整行數據設定條件格式
當您想根據某個欄位的值來格式化整行數據時，可以使用混合引用：
markdown
=$B1="緊急"
這個公式會檢查B欄的值是否為"緊急"，如果是，則套用到整行。$B表示固定引用B欄，而1是相對引用，會根據行數自動調整。
技巧3：突顯重複或唯一值（進階版）
Google Sheets內建的重複值識別功能有限，使用自訂公式可以實現更複雜的識別：
markdown
=COUNTIF($A$1:$A$100,A1)>1
這會標記A1:A100範圍內所有重複出現的值。如果想標記唯一值：
markdown
=COUNTIF($A$1:$A$100,A1)=1
進階應用：只標記第二次及之後出現的重複值：
markdown
=COUNTIF($A$1:A1,A1)>1
技巧4：基於日期條件的格式設定
處理日期數據時，條件式格式特別有用：
markdown
=A1<TODAY() // 過期項目
=A1=TODAY() // 今天到期
=AND(A1>=TODAY(),A1<=TODAY()+7) // 未來7天內到期
更進階的應用是標示周末：
markdown
=OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7)
技巧5：根據數據的百分比排名設定格式
要突顯前10%或後20%的數據，可以使用PERCENTRANK函數：
markdown
=PERCENTRANK($A$1:$A$100,A1)>=0.9 // 前10%
=PERCENTRANK($A$1:$A$100,A1)<=0.2 // 後20%
技巧6：動態數據範圍的條件格式
當您的數據範圍會增長時，使用開放式範圍參考：
markdown
=A1>AVERAGE(A:A) // 高於平均值
或者結合COUNTA函數動態確定範圍：
markdown
=A1>AVERAGE($A$1:INDEX($A:$A,COUNTA($A:$A)))
技巧7：基於多個條件的複雜格式設定
使用AND、OR函數組合多個條件：
markdown
=AND(A1>100,B1<50) // A大於100且B小於50
=OR(A1>100,B1<50) // A大於100或B小於50
更複雜的嵌套條件：
markdown
=IFERROR(AND(A1>100,SEARCH("重要",B1)),FALSE)
技巧8：突顯數據驗證錯誤
自動標記不符合數據驗證規則的輸入：
markdown
=ISERROR(DATA_VALIDATION(A1))
或者自訂檢查：
markdown
=AND(ISNUMBER(SEARCH("@",A1)),ISNUMBER(SEARCH(".",A1))) // 基本email格式檢查
技巧9：基於相鄰儲存格變化的條件格式
有時您希望格式根據相鄰儲存格的變化而改變：
markdown
=A1<>B1 // A和B不同時突顯
=A1>OFFSET(A1,0,1) // 比右邊儲存格大時突顯
技巧10：圖示集與自訂公式結合
雖然圖示集通常是預設規則，但結合自訂公式可以實現更靈活的控制：
markdown
=A1>=PERCENTILE($A$1:$A$100,0.8) // 前20%顯示向上箭頭
=A1<=PERCENTILE($A$1:$A$100,0.2) // 後20%顯示向下箭頭
技巧11：突顯公式與手動輸入的差異
在工作表中區分公式計算結果和手動輸入的數據：
markdown
=ISFORMULA(A1) // 標記包含公式的儲存格
=NOT(ISFORMULA(A1)) // 標記手動輸入的儲存格
技巧12：基於數據類型的格式設定
根據儲存格內容的數據類型設定不同格式：
markdown
=ISTEXT(A1) // 文字
=ISNUMBER(A1) // 數字
=ISLOGICAL(A1) // 邏輯值
技巧13：根據數據變化率設定格式
追蹤數據的變化情況：
markdown
=(A1-B1)/B1>0.1 // 比上個月增長超過10%
技巧14：使用正則表達式進行複雜文本匹配
Google Sheets支持REGEXMATCH函數，可用於複雜的文本模式匹配：
markdown
=REGEXMATCH(A1,"^[A-Z]{2}\d{4}$") // 匹配AA1234格式
=REGEXMATCH(A1,"重要|緊急|優先") // 包含特定關鍵詞
技巧15：條件格式的效能優化技巧
當工作表變得龐大時，條件格式可能影響效能。以下是一些優化技巧：
- 避免在開放式範圍（如A:A）上使用條件格式，改用明確範圍（A1:A1000）
- 簡化複雜公式，避免不必要的計算
- 對靜態數據考慮使用腳本批量設置格式，而非動態條件格式
- 合併相似條件，減少條件格式規則總數
實際案例演示
案例1：專案進度追蹤表
假設我們有一個專案追蹤表，包含以下欄位：專案名稱(B)、負責人(C)、開始日期(D)、結束日期(E)、進度%(F)。
我們可以設置以下條件格式：
-
即將到期的專案（結束日期在未來7天內）整行黃色：
markdown
=AND($E1>=TODAY(),$E1<=TODAY()+7)
-
延期的專案（結束日期已過且進度小於100%）整行紅色：
markdown
=AND($E1<TODAY(),$F1<1)
-
高優先級專案（名稱包含"[緊急]"）整行粗體紅色：
markdown
=REGEXMATCH($B1,"\[緊急\]")
案例2：銷售數據分析
在一個銷售數據表中，我們可以設置：
-
銷售額高於區域平均的綠色背景：
markdown
=A1>AVERAGEIF($B$1:$B$100,$B1,$A$1:$A$100)
-
銷售額比上月下降超過10%的紅色邊框：
markdown
=(A1-OFFSET(A1,-1,0))/OFFSET(A1,-1,0)<-0.1
-
重點客戶（客戶名單在Sheet2的A列）金色背景：
markdown
=COUNTIF(INDIRECT("Sheet2!A:A"),A1)>0
常見問題排解
問題1：條件格式沒有正確套用
可能原因：
- 公式的相對/絕對引用設定錯誤
- 範圍選擇不正確
- 多個條件格式規則衝突
解決方法：
1. 檢查公式中的引用是否正確
2. 確認套用範圍
3. 檢視規則管理器中規則的優先順序
問題2：工作表執行速度變慢
可能原因：
- 過多的條件格式規則
- 使用複雜公式在大型範圍上
- 引用其他工作表的慢速函數
解決方法：
1. 合併相似規則
2. 限制條件格式的範圍
3. 考慮使用腳本替代極複雜的條件格式
結語：成為條件式格式高手的關鍵
掌握Google Sheets條件式格式自訂公式需要練習與實驗。建議您：
- 從簡單的條件開始，逐步增加複雜度
- 為常用公式建立模板或片段庫
- 定期檢視和優化現有的條件格式規則
- 學習結合其他Google Sheets函數擴展可能性
透過本文介紹的15個進階技巧，您應該能夠創建出更專業、更智能的數據視覺化報表。記住，好的條件格式應該是直觀且能提供即時洞察的，而不是讓表格變得花俏但難以閱讀。適度使用這些技巧，您的數據分析效率將大幅提升！
