編按：

在使用自定義條件格式時，常常遇到明明很簡單卻無法正確顯示結(jié)果的情況？那，很有可能是你的相對引用和絕對引用弄錯了 。這是個小問題，卻總能讓你和成功失之交臂。那么，我們究竟要如何避免重蹈覆轍？

自定義條件格式，是使用Excel時經(jīng)常會用到的一個功能。但是，筆者總能聽到有小伙伴說，在實際操作過程中，經(jīng)常做不出預期的效果，要么是一些不滿足條件的單元格變成了自定義的格式，要么是整個工作表都變成了自定義的格式。這究竟是為什么呢？其主要原因，是沒有用好自定義條件格式中的相對引用和絕對引用。今天，大家一起來探索其中的奧秘吧！

以某學校的成績表為例，A至E列分別是序號、姓名、班級、數(shù)學成績、語文成績，如下圖所示。

問題：如何通過自定義條件格式，將數(shù)學和語文都大于等于90分的成績，設置成淺綠色填充？

對條件格式有所了解的小伙伴肯定知道，設置自定義條件格式的第一步，就是選定要設置條件格式的區(qū)域。

常常使用的錯誤方案一：

本例中，由于要設置自定義條件格式的是數(shù)學成績和語文成績兩種成績，所以我們首先選中D2:E12（注意：不要把表頭字段D1、E1選中）。然后，依次點擊“開始”－“條件格式”－“新建規(guī)則”，彈出“新建格式規(guī)則”對話框，再點擊其中的“使用公式確定要設置格式的單元格”。

在“為符合此公式的值設置格式”下面的函數(shù)框中輸入“=AND(D2>=60,E2>=90)”，如下圖所示。（注：點擊本界面中右下角的“格式”按鈕后，彈出“設置單元格格式”對話框后，點擊“填充”選項卡，然后在下面的備選顏色中，選擇淺綠色，再點擊“確定”，即可將填充顏色設置為淺綠色。）

點擊“確定”之后，在彈出的“條件格式規(guī)則管理器”對話框中可以看到，“應用于”下面的函數(shù)框中顯示的是“=$D$2:$E$12”，這就是大家之前選中的D2:E12區(qū)域，即應用自定義條件格式的區(qū)域，不過Excel自動為它加上了絕對引用符號，這個不用理它。

點擊“確定”后，“條件格式規(guī)則管理器”對話框消失。這時，表格中有些數(shù)據(jù)已經(jīng)被標綠了，如下圖所示。仔細觀察，其所標綠的單元格，沒能滿足我們的預期要求，如E2、E4、E10，都大于等于90，應該被標綠，但未被標綠。自定義格式操作沒成功！

常常使用的錯誤方案二：

大家先通過“開始”－“條件格式”－“清除規(guī)則”－“清除整個工作表的規(guī)則”，將剛才自定義的條件格式刪除，然后重新設置自定義條件格式。

這次，大家在函數(shù)框中，將AND函數(shù)中的D2、E2變成絕對引用，在函數(shù)框中輸入“=AND($D$2>=90,$E$2>=90)”。（注：也可通過“開始”－“條件格式”－“管理規(guī)則”－“編輯規(guī)則”來修改函數(shù)，但是為了讓之前的操作不影響到我們接下來的操作，筆者還是建議清除規(guī)則之后重新建立規(guī)則。）

使用此種方法設置條件格式之后，做出來的效果如下圖所示，D2:E12全部標綠。又沒成功！

正確使用方法：

將剛才自定義的條件格式刪除，完成前面步驟以后，大家將函數(shù)框中的函數(shù)D2、E2改為混合引用，在函數(shù)框中輸入 “=AND($D2>=90,$E2>=90)”。

使用此種方法設置自定義條件格式之后，做出來的效果如下圖所示，達到了大家的預期要求。成功了！喜大普奔！

以上三種方案的思路解析：

上面一共做了三次嘗試，在函數(shù)框中寫入的函數(shù)分別是“=AND(D2>=60,E2>=90)”、“=AND($D$2>=90,$E$2>=90)”、“=AND($D2>=90,$E2>=90)”。

熟悉Excel的小伙伴，都知道“$”是Excel中標識絕對引用的符號，如果不寫“$”，則為相對引用。

一般情況下，大家在Excel中寫函數(shù)的時候，隨著單元格向下或者向右復制，在單元格中是可以看到相對引用和絕對引用隨著單元格位置的變化而變化的。但是，在自定義條件格式的時候，大家在函數(shù)框中只能輸入一次函數(shù)，是無法看到絕對引用或者相對引用的變化的，這就常常讓人很費解，感覺看不見、摸不著。

那么，大家該怎么理解這個問題呢？確保自己的引用沒有使用錯誤呢？

大家要明白自定義條件格式中一個很重要的對應關系：在函數(shù)框中輸入的公式，其參數(shù)的引用關系是對應于所選擇的自定義條件格式區(qū)域中的第一個單元格（即最左上角的那個單元格）來寫的；同時，函數(shù)是帶著引用關系自動擴展到應用自定義條件格式區(qū)域中的其他單元格的。

比如，本例中，在我們輸入的AND公式中，公式參數(shù)中的引用關系是針對D2單元格寫的，但是它會自動擴展到E2、D3，E3、D4、E4、……、D12、E12，擴展時引用會發(fā)生變化。

下面，筆者將依次剖析一下三種方案，其中每一次單元格引用的變化。

方案一的錯誤解析：

第一種方案中，大家使用的是純相對引用，公式是以針對D2寫的，當公式擴展E2、D3:E12時，每一次都是對單元格右側(cè)的兩個單元格進行判斷，如果二者都大于等于90，則標綠。

① 以D2為例，它判斷的是D2、E2是否同時大于等于90。本例中，由于第二行中A1同學的數(shù)學成績和語文成績均滿足條件，故D2被標綠。

② 以E2為例，由于函數(shù)中使用的是純相對引用，所以,E2單元格判斷的是E2和F2是否同時大于等于90。本例中，E2為99，但是F2中沒有內(nèi)容， E2、F2同時大于等于90這個條件不成立，故E2未被標綠。

其他單元格，以此類推，如下圖所示：

方案二的錯誤解析：

第二種方案中，大家使用的是純絕對引用，當公式擴展到其他單元格時，都是對D2和E2的關系進行判斷。

① 以D2為例，函數(shù)判斷的是D2、E2是否同時大于等于90。本例中，D2、E2均大于等于90，所以D2被標綠。

② 以E2為例，函數(shù)判斷的也是D2、E2是否同時大于等于90。本例中，D2、E2均大于等于90，所以E2被標綠。

D3:E12中的每一個單元格，判斷條件都是D2、E2否同時大于等于90，本例中，由于D2、E2這兩個單格均大于等于90，所以就出現(xiàn)了D2:E12全部被標綠的效果。

方案三的正確引用解析：

第三種方案中，大家使用的是混合引用，用“$”鎖定的是列，沒鎖定行，當公式擴展到其他單元格時，列不變，行變。

① 以D2為例，函數(shù)判斷的是D2、E2是否同時大于等于90，本例中，D2、E2均大于等于90，滿足條件，故標綠；

② 以E2為例，函數(shù)判斷的是D2、E2是否同時大于等于90，本例中，D2、E2均大于等于90，滿足條件，故標綠；

以D3為例，函數(shù)判斷的是D3、E3是否同時大于等于90，本例中，D3、E3均小于等于90，不滿足條件，故未標綠；

以E3為例，函數(shù)判斷的是D3、E3是否同時大于90等于，本例中，D3、E3均小于等于90，不滿足條件，故未標綠。

其他單元格，以此類推，如下圖所示。

好了，小伙伴們，講了這么多，自定義條件格式中的相對引用和絕對引用，你弄明白了嗎？一定要多操作幾次，自己模擬一下數(shù)據(jù)，才能更好地掌握哦！

留一個小練習：如下圖所示，請通過自定義條件格式，將第1列A2:A16單元格與第1行B1:E1單元格中字母一致的單元格標綠！

操作方法如下圖所示，你做出來了嗎？

本文配套的練習課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關注公眾號，可隨時隨地學習Excel：

相關推薦：

絕對引用混合引用都不懂？難怪你總是公式填充錯誤！

這14種Excel表格問題都可以用自定義單元格格式解決！

2個實例讓你學會帶函數(shù)公式的條件格式

數(shù)據(jù)有效性只能引用一列數(shù)據(jù)？但他這樣用1000列也行！

版權申明：

本文作者阿碩；同時部落窩教育享有專有使用權。若需轉(zhuǎn)載請聯(lián)系部落窩教育。