你不曾知道的Excel條件格式秘密

我在《珍藏，最全面+最完善的Excel條件格式使用手冊》一文中，從0到1完全講解了Excel條件格式的前世今生，在所有條件規則中，用公式控制格式是最靈活、功能最強大的，因此我再開一篇教程，深入講解用公式控制格式的玩法！

坐好，開車！

001 合同到期提醒

舉例：有一項合同管理的工作，規則是如果7天內到期：則設置黃色底紋填充、白色加粗字體凸顯；如果合同已經到期：則設置為紅色底紋填充、白色加粗字體凸顯。

▍步驟：

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

Step2：設置公式1：=$E3-TODAY()<7

Step3：設置黃色底紋填充、白色加粗字體格式

Step4：重複Step1-Step2，設置公式=$E3<TODAY()，然後設置紅色底紋填充、白色加粗字體格式

這個案例比較簡單，我們在初級教程中已經學過，其實就是多規則並存的條件格式設置。

002 報表美化之顯示底紋

當表格數據行較多，我們為了讓顯示效果更加醒目，可以讓工作表間隔固定行顯示底紋，即每隔一行添加一個底色。

要隔行增加底紋，最好的辦法是使用「套用表格格式」，套用表格格式之後，會將區域轉化為智能表格（Excel table），如果不想將單元格區域轉化為表格，使用條件格式設置隔行底紋是個不錯的選擇。

▍步驟：

Step1：選中需要設置格式的單元格區域，新建格式規則

Step2：設置公式：=MOD(ROW(),2)=0

Step3：設置灰色底紋填充

MOD(ROW(),2)是判斷當前行數是否能被2整除，滿足整除的行數被設置為灰色填充，所以最終效果為偶數行填充為灰色。

GIF>>

提示：函數MOD(number,divisor)返回兩數相除的餘數，其中Number為被除數，Divisor為除數。函數ROW(reference)返回引用的行號。其中Reference為需要得到其行號的單元格或單元格區域，如果省略 reference，則假定是對函數 ROW 所在單元格的引用。

003 查詢聚光燈效果

這裡的聚光燈指的是查詢聚光燈，和WPS中的時時聚光燈（滑鼠點在哪個單元格，對應的單元格的行和列會高亮）不太一樣。Excel中也可以實現時時聚光燈，思路是一樣的，只不過需要藉助VBA實現再計算。

這裡的查詢聚光燈的意思是，根據已知的條件，查找到數據，會在原始是數據表高亮查詢到的數據，以便於核對。

GIF>>

Step1：在G1、G2單元格分別設置姓名和學科的下拉菜單

（方法很簡單，我也寫過專門的教程，看這裡：高能，這一篇讓你完全掌握Excel下拉菜單！）

Step2：在G3單元格寫入公式=VLOOKUP($G$1,$A$1:$D$10,MATCH($G$2,$A$1:$D$1,0),0)

（註：本公式中的引用也可以用相對引用）

這是交叉引用的典型公式，公式中，VLOOKUP函數以G1單元格的姓名為查詢值，查詢區域為$A$1:$D$10列，因為有三個科目的成績，需要根據科目的不同，返回不同列的值。

因此，VLOOKUP函數的第三個參數由公式MATCH($G$2,$A$1:$D$1,0)充當，它函數查詢出G2在$A$1:$D$1單元格區域的位置。

公式嵌套的結果就是：G1單元格中的值，確定查找的姓名；G2單元格中值確定對應的科目，也就能返回姓名所在行和科目所在列交叉位置的成績值。

Step3：選中B2:D10單元格區域，設置條件格式

公式為=OR($A2=$G$1,B$1=$G$2)，格式為：淡紅色底紋填充。

此條規則的意思就是：如果單元格所在行的行標題（即姓名）等於G1中的姓名，或者列標題（即科目）等於G2中的科目，兩個條件滿足其一，那麼對B2：D10滿足條件的區域設置淡紅色底紋填充。

Step4：對成績所在單元格設置條件格式：

選中B2:D10單元格區域，公式為=AND($A2=$G$1,B$1=$G$2)，格式為：紅色底紋，白色加粗字體。

此條規則的意思是：如果單元格所在行的行標題（即姓名）等於G1中的姓名，且列標題（即科目）等於G2中的科目，兩個條件同時滿足，則高亮顯示，因為同時滿足這兩個條件的單元格就是行和列的交叉點（即成績所在的單元格）。

這裡有一個非常容易出錯的點，就是說如果設置條件格式前選中的區域（這個區域其實就是推薦格式的作用區域）是A1：D10，那麼公式應該這樣寫=OR($A1=$G$1,A$1=$G$2)，注意條件判斷的單元格起點變了。

能理解這一點，你的條件格式高級應用就出師了，幾乎沒有什麼能難倒你的了。

004 易錯點剖析

上面的三個案例，是條件格式比較高級的應用，相對燒腦，相信按照上面的教程，大家都能一步一步完成，但是如果想要舉一反三，那麼你還需要避開以下幾個坑。

▍01、公式中引用方式出錯

條件格式是逐個單元格進行判斷的，在條件格式中，針對活動單元格的設置，將被作用到所選區域的每一個單元格。

正是因為會自動擴展進行判斷，所以一定要注意相對引用和絕對引用的正確使用。

舉例：

我們在003小結的Step3中，設置的公式為=OR($A2=$G$1,B$1=$G$2)

這就是因為我們設置的條件格式區域為：B2：D10這樣一個多行多列的區域，在條件判斷時，對於姓名，要確保在A列從A2開始到A10逐一和G2單元格中的值進行判斷。

所以，$A2採用列絕對引用（確保不會偏向B列、C列……），行相對引用（確保可以逐行進行判斷）；而$G$1必須採用行列絕對引用，確保不會偏移。

對於科目，是同樣的道理。

這個公式中，任何行列引用錯誤，都的不出來正確的結果。

▍02 作用範圍設置錯誤

我們知道，設置條件格式的作用範圍，有兩種方法：

①設置條件格式之前，先框選範圍；

②設置條件格式之前，不選擇範圍，最後在「條件格式規則管理器」中「應用於」設置作用範圍，如圖所示：

對於方法①，那麼框選區域的方向不能反了，否則，條件格式會出錯。

比如要設置：如果科目二成績大於科目一，則C、D兩列填充紅色底紋。

作為範圍的框選方向為從D10到C2

那麼錯誤公式寫法為：=$D2>$C2

這樣設置後公式會自動變為：

=$D1048570>$C1048570

錯誤原因：

公式中引用的單元格必須為框選區域起始位置一致。本例框選的其實位置為D10，所以公式的寫法為：=$D10>$C10

注意：正常情況下，我們框選區域，都是自上而下，自左而右，這樣就不會碰到本例中的錯誤了。

▍03、優先順序出錯

在規則管理窗口中，優先執行排在上面的規則。在多規則並存的情況下，如果他們的作用區域是相同的，且設置的格式有衝突（比如規則一設置為紅色底紋，規則二設置為黃色底紋），這時只會執行優先順序高的規則；如果沒有衝突，則兩者都執行。

舉例：

在003聚光燈效果中：

規則一：公式為=OR($A2=$G$1,B$1=$G$2)，格式為：淡紅色底紋填充

規則二：公式為=AND($A2=$G$1,B$1=$G$2)，格式為：紅色底紋，白色加粗字體

他們的優先順序為：規則二優先順序>規則一優先順序

這很容易理解，因為規則一包含了規則二，如果先執行規則一，那麼規則二也是滿足規則一的，所以他們衝突的格式，就不會執行規則二的了。

我們來看一下，將規則一和規則二的優先順序的順序進行調換：

結果是這樣的：

深紅色的底紋沒有顯示，因為和規則一的淡紅色底紋衝突。

最後，想說的是，從本教程的三個案例可以看出，條件格式最高級的應用，難點其實是公式的編寫。事實上Excel很多功能，都是構建在高超的公式基礎之上的，拼到最後拼的都是公式的能力。

·The End·

https://item.jd.com/12125533.html (二維碼自動識別)

關注「精進Excel」公眾號，如果人意點開三篇文章，沒有你想要的知識，算我耍流氓！

推薦閱讀：