珍藏，最全面+最完善的Excel條件格式使用手冊

Excel的條件格式是一個使用頻率非常高的功能，它能夠讓Excel更智能，但是很多人用的不明不白。

今天這篇文章讓你完全掌握Excel條件格式。

什麼事條件格式呢，其實就是讓符合條件的單元格顯示為預設的格式。根據條件使用數據條、色階和圖標集，以突出顯示相關單元格，強調異常值，以及實現數據的可視化效果。

Excel菜單中，一個完整的條件格式稱為一條規則（即條件+格式=規則），Excel條件格式，預設了五種類型的規則，如圖紅框內所示，本篇教程主要講自定義規則，即通過「新建規則」創建的規則。

點擊「新建規則」之後，如圖所示為可以創建的六種規則。

舉個例子：

創建的規則：將銷售額小於5000的單元格設置為紅色加粗字體

在這條規則中，條件是：銷售額小於90，格式是：紅色加粗字體

這六種規則，其實可以分為三類：

001、單元格內可視化（即基於各自值設置所有單元格的格式）

002、數值突出顯示（即新建格式規則中第2到第5項）

003、使用公式控制格式（即使用公式確定要設置格式的單元格）

001 單元格內可視化

單元格內可視化，就是將數值進行圖形化表示，只不過這裡的圖形化指的是單元格的格式。我們通過格式的差異體現出數值的差異，從而將數值巧妙轉化為圖形表達。

單元格內可視化使用第一個規則類型，它又包含四種不同的樣式。

01 用「三色刻度」展示數據分布

「雙色刻度」和「三色刻度」都可以稱為色階，即通過顏色的不同代表不同的數值大小。他們的用法幾乎一致，我們就用「三色刻度」來展示各產品的銷售分布情況。

如圖是各產品的銷量數據，希望通過顏色來展示數據的分布情況。

選中B3：N8數據區域，然後選擇三色刻度樣式，規則說明中會出現三個數值，其中紅色、黃色、綠色分別代表了最小值、中間值、最大值。

（注意：最小值、中間值和最大值的類型都可更改，默認情況下，最小值取得就是數值中的最低值，中間值取得是50%）

設置完成後，銷售數據表就變成顏色填充的表格，從顏色就可以看出銷量的分布情況。

02 用「數據條」展現數值大小

數據條規則可使數據圖形化，即可以通過數據條單純地展示數值的大小，也可以展示數據的進度（將最大值設置為目標值）。

如圖，格式樣式選擇「數據條」，默認情況下，「最小值」和「最大值」的類型都是自動，此時：最大值對應的數據條將充滿單元格。

而本列中，我們將「最大值」的類型更改為數字，並填入目標值3000，此時：大於3000的的數值對應的數據條將充滿，其他值按比列填充數據條。

設置完成後，銷量數據表就像是一個條形圖一樣，一目了然就能看出個數值的大小和進度。

註：數據條「最大值」的「類型」應該如何選擇？

最大值指的是數據條達到滿格應該對應的數值。最大值可由六種類型賦值：最高值、數字、百分比、公式、百分點值、自動。

常用的三種類型的含義如下：

最高值：當前數據列中的最高值

數字：用戶自定義填寫數值

公式：使用公式定義

數據條的另一個經典用法是突出正負值，在表達數據的增長情況時，非常清晰。比如，產品銷量數據表中，我們在「同比增長」一列設置數據條，那麼增長為負值的數據條會顯示為紅色。

關於負值和坐標軸的設置，可以在「編輯規則說明」界面點擊「負值和坐標軸」

然後在彈出的「負值和最標軸設置」中，可以設置負值條形圖的顏色和坐標軸的位置。

03 用「圖標集」展示數據特徵

我們經常會以優良中差來判斷學生的成績，判定成績多數人會想到使用IF函數或者VLOOKUP函數，其實還有一個更好的方法就是「圖標集」。

比如學生的成績：85以上為優秀，用藍色的旗表示；75以上為良好，用黃色的旗表示；低於75為一般，用紅色的棋表示。

Step1：選擇「圖標集」，並選擇合適的「圖標樣式」

Step2：設置規則

應該注意的是，使用圖標集，最多只能設置五個等級圖標，這在大多數情況下夠用了。

002 數值突出顯示

本文開頭說過，在新建規則的第2到第5都屬於數值突出顯示的範疇，雖然有這麼多條，但是其實他們是同一回事：將滿足特定條件（往往是我們希望關注的數據）的數值設置特別的格式，以突出顯示。

這些特點的條件可以是這些：

• 大於（或者小於）某值
• 包含特定文本
• 排名前幾名的數值
• 高於或低於平均值
• 唯一值或者重複值

如果你對Excel的篩選功能比較熟悉的話，你會發現，這些條件和篩選中的條件幾乎完全一樣。

（事實上，對特殊數值進行突出顯示，也屬於篩選的一種，仔細想想，是這個道理吧）

01 突出不及格的成績

選中需要設置條件格式的數據區域，然後再「選擇規則類型」中點選「只為包含以下內容的單元格設置格式」，然後設置條件：單元格的中的值小於60.

然後點擊格式

並設置格式為字體加粗、紅色。

小於60的成績就會自動顯示我我們設置的格式

02 突出前三名和後三名

>>GIF

GIF圖示中只演示了前三名的設置方法，後三名設置方法是一樣的。

03 重複值和唯一值突出顯示

比如成績單中，需要檢驗學生姓名是否有重複錄入，如果有重複，則標記出來。

>>GIF

003 使用公式控制格式

使用公式控制格式應該來說是條件格式中最難的：

第一個難點是如何按要求寫出有效的公式；

第二個難點是條件格式的作用有限順序和範圍；

「使用公式確定要設置格式的單元格」這條規則中，「條件」指的是公式的運算結果，只有當條件為「真」時，格式才能生效。這就對公式的結果有一個明確的要求：即公式的結果必須是邏輯值（公式必須返回是或否）。

設置操作方法很簡單，填寫公式、設置格式即可完成，如圖所示。

01 突出顯示每科目的最低成績

Step1：選中C2:E13單元格區域，新建格式規則

Step2：寫入公式，然後設置突出顯示的格式

=C2=MIN(C$2:C$13)

提示：

1、MIN(C$2:C$13)部分計算出公式所在列的最小值，注意這裡的單元格引用C$2:C$13，列相對引用、行絕對引用，這是因為總共有三列科目，作用區域需要自動擴展為D$2:D$13和E$2:E$13

2、然後判斷C2（活動單元格），是否等於公式所在列的最小值。

3、在條件格式中，針對活動單元格的設置，將被作用到所選區域的每一個單元格

02 多條件設置

如圖所示的成績單，將科目一和科目二均大於90的成績，在科目一和科目二兩列標記出來。

應該設置的公式為 =AND($C2>90,$D2>90)

（AND($C2>90,$D2>90)部分表示兩個條件同時滿足）

因為需要標記科目一和科目二兩列，所以條件格式的作用區域為$C$2:$D$13

03 多條件多規則設置

多條件多規則設置就更加複雜了，我們來看一個場景：

如果有一科目不及格，則這一整行標記為黃色底紋；

如果兩個科目不及格，則這一整行標記為紅色底紋。

Step1：設置「如果有一科目不及格，則這一整行標記為黃色底紋」的規則

公式為=OR($C2<60,$D2<60)，並設置填充為「黃色」底紋。

說明：

OR函數是「或」的意思，它的參數中只要有一個為真，則函數的返回值就為真，因此=OR($C2<60,$D2<60)表示兩個科目的成績只要有一個不及格，公式的結果就為真。

因為要對整行填充顏色，需要把「條件格式」設置到整行的區域：可以在設置條件格式之前選擇$A$2:$D$13區域，也可在管理規則中修改「應用於」區域。

點擊【應用】之後，會發現數據區域中只要有一科目成績不及格，就被標記為黃色底紋，其中當然也包含兩科目都不及格的情況，如圖第12行。因此，我們還需要再設置條件，專門為兩個科目都及格的情況設置紅色底紋。

Step2：設置「如果兩個科目不及格，則這一整行標記為紅色底紋」的條件

寫入公式=AND($C2<60,$D2<60)，並設置填充色為紅色，

同樣在管理窗口，將新設置的「條件格式」應用於整個數據區域，如圖所示。在規則管理窗口，可以看到新創建的規則，自動排在了前面，這種排列順序就是「條件格式」的「優先順序」，如果兩個條件有衝突，排在前面的「條件格式」先執行，通過下圖中右上角的三角符號可以調節條件格式的順序。

兩個條件格式的綜合作用結果如圖所示。

提示：

如果公式=OR($C2<60,$D2<60)設置的條件放在第一位，那麼將優先執行此條件，而兩個科目都不及格也是滿足此條件的，所以就不會執行公式=AND($C2<60,$D2<60)設定的條件，因此紅色底紋不會顯示。所以，在多條規則的情況下，一定要注意規則的優先順序。

004 條件格式管理

01 規則管理

我們可以通過「條件格式規則管理器」對條件格式進行管理：

①：選擇要管理的條件格式規則

②：新建規則

③：編輯規則（對規則進行重新設置）

④：規則優先順序調整

⑤：規則應用範圍更改

⑥：終止規則選項

02 條件設置要點

我們知道使用公式控制格式是難點，其核心是如何將條件用函數的表達出來。

1、公式的編寫

首先公式必須以等號=開頭，這個等號的意思是判斷「是否滿足公式的結果」，等號後面的公式是要判斷的條件，公式的返回值要麼是TRUE，要麼是FALSE，不能為其他類型的返回值。

2、公式中單元格區域的引用方式

條件格式是逐個單元格進行判斷的，在條件格式中，針對活動單元格的設置，將被作用到所選區域的每一個單元格。因此，在設置公式的時候，一定要注意相對引用和絕對引用的正確使用。

3、條件區域與作用範圍

設置單元格的條件格式，一定要搞清楚條件所引用的單元格和滿足條件的情況下，需要應用格式的單元格，兩者可以不同也可以相同。

比如：條件區域為課程成績，作為範圍即可以是科目成績，也可以是姓名，甚至可以是整行。

4、規則的優先順序

在規則管理窗口中，優先執行排在上面的規則。在多規則並存的情況下，如果他們的作用區域是相同的，這時候就要考慮規則的優先順序。

作者：安偉星，Excel培訓師，微軟Office認證大師，知乎專欄作者，《競爭力：玩轉職場Excel，從此不加班》圖書作者，原創公眾號：精進Excel（ID：SeniorExcel）

關注公眾號，回復「條件格式」，下載原始素材。

http://weixin.qq.com/r/Kzn64lrEqwfUrR6B92w5 (二維碼自動識別)