外觀
Productivity › 試算表 › 條件式格式
條件式格式 (Conditional Formatting)
依規則自動改變儲存格外觀,讓異常值、截止日、高低分布一眼可見;資料更新後顏色跟著變,不必手動重新上色。Excel(常用 → 設定格式化的條件)與 Google Sheets(格式 → 條件式格式)概念相同。
條件式格式只是視覺提示,不改變資料本身;需要被篩選、分組或計算的判斷,應該另做一個邏輯欄(如
=IF(...)旗標)。
組成
| 部分 | 角色 |
|---|---|
| 套用範圍 (range) | 規則作用在哪些儲存格 |
| 規則 (rule) | 測試的條件 |
| 格式 (format style) | 條件成立時的外觀 |
| 模式 | 單色規則 vs 色階 (color scale) / 資料橫條 (data bar) / 圖示集 (icon set) |
規則類型
| 規則 | 例子 | 適合 |
|---|---|---|
| 大於 / 小於 / 介於 | 負數標紅、低於門檻 | 單色 |
| 文字包含 / 等於 | 狀態為 "Overdue" | 單色 |
| 日期 | 過去 7 天、下個月 | 單色 |
| 重複 / 唯一值 | 找出重複 ID | 單色 |
| 前 / 後 N 項、高於平均 | top 10% 業績 | 單色 |
| 色階、資料橫條 | 分數熱圖、相對大小 | 連續數值 |
| 自訂公式 | 跨欄條件、整列上色 | 任何 |
| 選擇 | 用在 |
|---|---|
| 單色 | 二元或類別狀態:逾期、負值、已完成 |
| 色階 | 大小程度重要:分數高低、熱圖式比較 |
自訂公式
公式以範圍左上角儲存格為基準撰寫,回傳 TRUE 時套用格式;$ 決定哪個方向固定。
| 目的 | 套用範圍 | 公式 |
|---|---|---|
| 整列上色:狀態欄 (E) 為 Overdue | $A$2:$H$100 | =$E2="Overdue" |
| 截止日 (D) 在 7 天內且未完成 | $A$2:$H$100 | =AND($D2-TODAY()<=7, $F2<>"Done") |
| 標出重複 ID | $A$2:$A$100 | =COUNTIF($A:$A, $A2)>1 |
| 標出空白必填欄 | $B$2:$B$100 | =ISBLANK($B2) |
| 隔列底色 | $A$2:$H$100 | =MOD(ROW(), 2)=0 |
整列上色時欄要鎖(
$E2)、列不鎖;忘記鎖欄會讓每一欄都去比對不同的欄位。
設計原則
| 原則 | 說明 |
|---|---|
| 規則少 | 互相競爭的顏色越多,越沒有重點 |
| 強烈顏色留給例外 | 紅色只給真正需要處理的狀態 |
| 避免重疊規則語意不明 | 用「規則管理員」調整順序,必要時勾「如果為 True 則停止」 |
| 不只靠顏色 | 色盲友善:搭配圖示或文字標籤 |
| 用 Table 當範圍 | 新增列時規則自動延伸 |
常見用途:標出缺值或可疑值、截止日與進度風險、前後段表現、KPI 趨勢方向;也可搭配 資料驗證 標出驗證擋不到的重複值。