Google Sheet條件式格式自訂公式：數據趨勢分析的終極指南
前言
在當今數據驅動的決策環境中，Google Sheets已成為許多企業和個人分析數據的重要工具。其中，條件式格式功能尤其強大，能讓您的數據視覺化，快速識別趨勢和異常值。本文將深入探討如何利用自訂公式來進階應用條件式格式，讓您的數據分析更上一層樓，符合臺灣使用者習慣的操作指南。
什麼是條件式格式？
條件式格式（Conditional Formatting）是Google Sheets中一項強大的功能，它能根據您設定的規則自動改變儲存格的外觀（如顏色、字體樣式等）。這讓您無需手動標記，就能直觀地看到數據中的模式、趨勢和異常情況。
基本條件式格式應用
開始探討自訂公式前，先了解基礎應用：
- 選取要格式化的儲存格範圍
- 點擊工具列上的「格式」>「條件式格式」
- 在右側面板中，選擇條件類型（如「儲存格值」或「自訂公式」）
- 設定格式樣式（顏色、粗體等）
為什麼要使用自訂公式？
標準的條件式格式選項（如大於、小於、介於等）雖然方便，但功能有限。自訂公式讓您能夠：
- 實現更複雜的邏輯判斷
- 交叉參考其他工作表或範圍的數據
- 創建基於動態計算的格式化規則
- 實現數據條（data bars）和色階（color scales）無法達到的效果
自訂公式基本語法
要使用自訂公式進行條件式格式，您需要了解一些基本規則：
- 公式必須返回TRUE或FALSE值（TRUE時應用格式）
- 公式中的儲存格引用相對於所選範圍的第一個儲存格
- 使用絕對引用（$A$1）固定行列，相對引用（A1）會隨位置改變
範例1：標記高於平均值的數據
假設您有一列銷售數據（A2:A100），要標記高於平均值的項目：
excel
=A2>AVERAGE($A$2:$A$100)
說明：此公式會比較每個儲存格是否大於整個範圍的平均值，是則套用格式。
進階數據趨勢分析技巧
1. 識別上升/下降趨勢
要視覺化數據的趨勢變化，您可以：
方法一：與前一期比較
excel
=AND(A2<>"", A2>B2) // 上升趨勢（當前大於前一）
=AND(A2<>"", A2<B2) // 下降趨勢（當前小於前一）
方法二：移動平均比較
excel
=AND(A2<>"", A2>AVERAGE(B2:D2)) // 高於近期平均
=AND(A2<>"", A2<AVERAGE(B2:D2)) // 低於近期平均
2. 熱力圖（Heat Map）創建
熱力圖能直觀顯示數據高低分布，使用色階或自訂公式：
excel
=A2>=PERCENTILE($A$2:$A$100,0.8) // 前20%高值
=A2<=PERCENTILE($A$2:$A$100,0.2) // 後20%低值
3. 突顯異常值（Outliers）
識別數據中的異常值對品質控制特別有用：
excel
=OR(A2>AVERAGE($A$2:$A$100)+2*STDEV($A$2:$A$100),
A2<AVERAGE($A$2:$A$100)-2*STDEV($A$2:$A$100))
說明：此公式標記偏離平均值兩個標準差以上的數據點。
4. 項目進度追蹤
對於專案管理，可視覺化任務進度：
excel
=AND(TODAY()>=A2, TODAY()<=B2) // 進行中任務
=TODAY()>B2 // 逾期任務
=ISBLANK(B2) // 未開始任務
實戰案例：銷售數據趨勢分析
讓我們透過一個完整案例，展示如何分析月度銷售數據：
步驟1：準備數據
假設您的數據如下：
| 月份 | 銷售額 | 目標 |
|--------|--------|------|
| 1月 | 120000 |100000|
| 2月 | 95000 |100000|
| ... | ... |... |
步驟2：設定條件式格式規則
-
達成目標：綠色背景
excel
=B2>=C2
-
未達目標但達90%：黃色背景
excel
=AND(B2<C2, B2>=0.9*C2)
-
嚴重未達標（低於80%）：紅色背景
excel
=B2<0.8*C2
-
連續三個月增長：藍色邊框
excel
=AND(B2>B3, B3>B4, B4>B5)
步驟3：趨勢箭頭標記
您甚至可以用特殊字元顯示趨勢方向：
- 新增「趨勢」列
- 使用公式計算趨勢：
excel
=IF(B2>B3, "↑", IF(B2<B3, "↓", "→"))
- 對「趨勢」列設定文字顏色條件：
- ↑：綠色
- ↓：紅色
- →：灰色
跨工作表與動態範圍參考
進階應用常需要參考其他工作表或動態範圍：
跨工作表參考
excel
=INDIRECT("Sheet2!A1")>10000
動態範圍（排除標題）
excel
=AND(ROW(A2)>1, A2>10000)
常見錯誤與疑難排解
- 公式不生效：
- 檢查是否使用相對/絕對引用正確
- 確定公式返回的是TRUE/FALSE值
-
確認範圍選擇正確
-
效能問題：
- 避免過多複雜公式條件
- 限制條件式格式的範圍大小
-
使用ARRAYFORMULA整合多個條件
-
條件衝突：
- 注意條件的優先順序（由上到下評估）
- 使用「停止若為真」選項控制評估流程
進階技巧與組合應用
1. 基於其他列的條件格式
excel
=AND($B2>10000, $C2="重要")
說明：當B列值大於10,000且C列標記為"重要"時套用格式。
2. 整行突顯
要根據某列條件突顯整行：
- 選取整個數據範圍（如A2:Z100）
- 使用公式：
excel
=$B2>10000
說明：B列值大於10,000時，整行套用格式。
3. 交替行顏色（無需公式）
雖然這不必用自訂公式，但很有用：
- 使用內建的「交替顏色」功能
- 或自訂公式：
excel
=MOD(ROW(),2)=0
Google Sheets條件式格式的限制與替代方案
雖然功能強大，但仍有以下限制：
- 每個規則最多只能設定3種格式（文字色、背景色、字體樣式）
- 過多條件會影響效能
- 無法實現Excel中的「數據條」或「圖標集」所有效果
替代或補充方案：
- 使用Google Apps Script創建更複雜的格式邏輯
- 結合圖表（如折線圖、柱狀圖）輔助分析
- 使用QUERY或FILTER函數先篩選數據
結語
掌握Google Sheets條件式格式的自訂公式，能讓您的數據分析工作如虎添翼。從簡單的數值比較到複雜的趨勢識別，這些技巧可以幫助您：
- 即時識別關鍵數據點
- 發現隱藏的數據模式
- 提高報表的可讀性和專業性
- 加速決策過程
記住，有效的數據視覺化不在於使用最炫的技巧，而在於選擇最適合您數據特徵和分析目標的方法。現在就打開您的Google Sheets，開始應用這些技巧吧！
附錄：常用條件式格式公式速查表
| 用途 | 公式範例 |
|--------------------|----------------------------------------|
| 標記最大值 | =A2=MAX($A$2:$A$100) |
| 標記重複值 | =COUNTIF($A$2:$A$100,A2)>1 |
| 週末日期 | =OR(WEEKDAY(A2)=1, WEEKDAY(A2)=7) |
| 即將到期（7天內） | =AND(A2-TODAY()<=7, A2>=TODAY()) |
| 空值但必填 | =AND(ISBLANK(A2), $B2="必填") |
