如何根據值在 Excel 中醒目提示儲存格
若要根據數值在 Excel 中醒目提示儲存格,請選取儲存格,然後選擇「常用」>「條件式格式設定」>「醒目提示儲存格規則」,再挑選一項規則,例如「大於」。顏色會隨著數值改變,因此數字變動時顏色也會跟著變動。本指南涵蓋內建規則、使用公式的規則,以及如何醒目提示整列。
1. 使用內建規則
選取儲存格,開啟「常用」>「條件式格式設定」>「醒目提示儲存格規則」,然後選擇「大於」、「小於」、「介於」、「等於」、「文字包含」或「重複的值」。輸入數值、選擇顏色,然後按一下「確定」。「頂端/底端規則」會醒目提示最高或最低的值,或高於平均值的數值,不需要你輸入數字。
2. 用資料橫條與色階表示大小
「資料橫條」會在每個儲存格中畫出一根橫條,長度隨數值變化。「色階」會將儲存格從一種顏色漸層到另一種顏色,例如高值為綠色、低值為紅色。「圖示集」會加上箭頭或紅綠燈。當讀者想要掃視出最高與最低的數值時,這些功能用在一欄數字上很有效。
3. 用公式撰寫自己的規則
若內建規則無法滿足需求,請選擇「條件式格式設定」>「新增規則」>「使用公式決定要格式化的儲存格」。請為選取範圍的第一個儲存格撰寫公式,且公式需傳回 TRUE 或 FALSE。Excel 會在結果為 TRUE 的每個儲存格上套用格式,並為其他儲存格調整參照。
範例:銷售額高於目標
| A | B | C | |
|---|---|---|---|
| 1 | 銷售額 | 目標 | 是否醒目提示? |
| 2 | 120 | 100 | TRUE |
120 大於 100,因此此規則為 TRUE,該儲存格會套用顏色。對從 A2 開始的選取範圍,使用與規則相同的公式即可。
4. 醒目提示整列
若要在某一欄符合條件時為整列上色,請選取表格的所有列,並在撰寫規則時於欄字母前加上美元符號,例如 =$B2="延遲"。$ 會鎖定欄,使每個儲存格都檢查 B 欄,而列號會向下移動。這種混合參照與「儲存格參照」指南中的概念相同。
5. 找出重複的值
公式規則可以標示出現超過一次的值。COUNTIF 會計算該值在範圍中出現的次數,結果大於 1 就代表重複。
範例:這個名稱是否重複?
| A | B | C | |
|---|---|---|---|
| 1 | 名稱 | 是否重複? | |
| 2 | Ana | TRUE | |
| 3 | Ben | ||
| 4 | Ana | ||
| 5 | Cam | ||
| 6 | Ben | ||
| 7 | Dee |
Ana 在範圍中出現兩次,因此計數為 2,規則為 TRUE。
常見錯誤
為錯誤的儲存格撰寫規則。 公式會被當作位於選取範圍左上角的儲存格來讀取。為其他儲存格撰寫的規則,會醒目提示錯誤的列。
修正: 先選取範圍,然後為它的第一個儲存格撰寫公式。
缺少美元符號。 沒有 $ 的話,列規則會往右滑動,檢查到錯誤的欄。
修正: 用 $ 鎖定欄,例如 $B2,但讓列保持不鎖定。
複製貼上後規則不斷堆疊。 貼上儲存格時,它們的規則也會一起複製,清單會變得很長而難以閱讀。
修正: 開啟「條件式格式設定」>「管理規則」來閱讀、重新排序或刪除規則。使用「清除規則」可以全部移除。
常見問題
如何醒目提示包含特定文字的儲存格?
選擇「醒目提示儲存格規則」>「文字包含」,然後輸入文字。若需要更多控制,請使用搭配 COUNTIF 或 SEARCH 的公式規則。
數值改變時,醒目提示會跟著改變嗎?
會。條件式格式設定會隨工作表重新計算,這與你手動套用的填滿顏色不同。
如何醒目提示空白儲存格?
選擇「新增規則」>「只格式化包含下列內容的儲存格」>「空白」。或使用公式規則,例如 =A2=""。
相關函數
- COUNTIFCOUNTIF 會計算範圍中符合一個條件的儲存格:文字、數字、像 ">100" 這樣的比較,或萬用字元。包含語法、範例與常見錯誤。.
- ANDAND 只有在每個條件都為真時才會傳回 TRUE。包含語法、單獨使用與放在 IF 中的範例、檢查某個範圍是否介於兩個值之間,以及常見錯誤。.
- OROR 在至少一個條件為真時傳回 TRUE。包含語法、單獨使用與放在 IF 中的範例、檢查允許值清單,以及常見錯誤。.
- IFIF 會檢查一個條件:為真時傳回一個值,為假時傳回另一個值。包含語法、文字與數字的範例、巢狀 IF,以及常見錯誤。.
更多關於 條件式格式設定
本指南涵蓋規則與公式規則。本文補充規則套用的順序、如何醒目標示日期,以及如何建立以另一個儲存格作為門檻的規則。
規則重疊時會發生什麼事
多個規則可以套用到同一個儲存格。請開啟「條件式格式設定」>「管理規則」,從上到下查看規則清單。當兩個規則設定相同的屬性(例如填滿色彩)時,清單中較高的規則會優先。用箭頭把最重要的規則移到最上方。若希望符合條件的儲存格不再套用後面的規則,請勾選該規則的「如果成立則停止」。
可以變動的日期與門檻
在規則中寫死 100 這類數字,日後很難修改。請把門檻放在儲存格中,例如 F1,並用鎖定參照來參照它。之後只要變更 F1,整個表格的上色就會跟著改變。
=A2>$F$1日期的運作方式相同。像 =$C2<TODAY() 這樣的規則會醒目標示到期日已過的列,而且每天都會重新計算。若要標示接下來七天,請用 AND 加上兩個條件。
保持易讀
顏色太多會讓讀者習慣忽略它們。每一種含意只使用一種醒目色彩,並在表格附近用註解寫明其含意。請以黑白檢查結果,因為許多讀者無法分辨紅色與綠色。[條件計數課程](/learn/conditional-counting)說明如何用公式計算醒目標示的列數。