Skip to content

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 趨勢方向;也可搭配 資料驗證 標出驗證擋不到的重複值。