條件格式效果錯誤，不是自己想要的，原因是什么呢？用公式設(shè)置條件格式需要遵循什么樣的規(guī)則才能避免錯誤呢？文章分別講了四種需求的條件格式設(shè)置規(guī)則。

1.條件格式效果錯誤原因

為何有時用公式設(shè)置的條件格式效果達不到想要的結(jié)果,是錯的？譬如下方。

需要：如果E列中數(shù)據(jù)大于100，其所在行整行數(shù)據(jù)突出顯示。

實際：

用公式設(shè)置條件格式得到錯誤結(jié)果，主要有3個原因。

（1）條件單元格的引用方式（絕對引用或相對引用）錯誤

（2）條件單元格的引用地址錯誤

（3）公式錯誤

3個原因中，前方兩個錯誤最迷惑人，下面具體說說。

2.用公式設(shè)置條件格式的單元格引用規(guī)則

1)需求1：當單元格條件為真，數(shù)據(jù)所在整行突顯

整行突顯，意味著公式右拉被引用單元格也要不變，所以需要鎖定列。

規(guī)則：條件單元格的列絕對引用，行數(shù)等于應(yīng)用范圍中的最小行，列數(shù)在應(yīng)用范圍中按需指定。

譬如， D列中<100的數(shù)據(jù)所在行突出顯示，應(yīng)用范圍C2:G11。

格式公式=$D2<100。

條件單元格D2的列絕對引用，行號是應(yīng)用范圍中的小值2。

再譬如： D列中重復(fù)的數(shù)值所在行突出顯示，應(yīng)用范圍C2:G11。

格式公式=COUNTIF($D$2:$D$12,$D2)>1

條件單元格D2是列鎖定，行數(shù)為范圍最小值2。

公式中的其他部分，如$D$2:$D$12，應(yīng)按在單元格中輸入的同判斷的可下拉右拉填充公式書寫。

譬如，此處判斷公式寫作“COUNTIF($D$2:$D$12,$D2)>1”，因此格式公式中這部分就是$D$2:$D$12。

注：條件格式中通常不適合用“D:D”等整列整行引用方式。

又譬如，突顯D列中最小數(shù)據(jù)所在行。

格式公式=$D2=MIN($D$2:$D$11)

若直接在H2輸入可下拉右拉公式判斷，=$D2=MIN($D$2:$D$11)。

2）需求2：單元格條件為真，數(shù)據(jù)所在整列突顯

整列突顯，意味著公式下拉被引用單元格也要不變，所以需要鎖定行。

規(guī)則：條件單元格的行絕對引用，地址等于應(yīng)用范圍中第一個單元格。

譬如，第2行中>100的數(shù)字所在列突出顯示，應(yīng)用范圍C2:G11。

格式公式=C$2>100

條件單元格C2的行絕對引用，地址是應(yīng)用范圍中第一個單元格C2。

再譬如， C2:G11內(nèi)各列的列首數(shù)據(jù)有重復(fù)的，則數(shù)據(jù)所在整列突出顯示。

格式公式=COUNTIF(C$2:C$11,C$2)>1

條件單元格C2的行絕對引用，地址等于應(yīng)用范圍中第一個單元格C2。

若直接在單元格中輸入下拉右拉公式判斷各列的首值是否重復(fù)，公式=COUNTIF(C$2:C$11,C$2)>1。

3)需求3：如果條件為真，數(shù)據(jù)所在單元格突顯

規(guī)則：單元格為相對引用，行數(shù)為應(yīng)用范圍最小行，列數(shù)為應(yīng)用范圍第一列。

譬如，F列中大于50小于110的數(shù)據(jù)突出顯示。

=AND(F2>50,F2<110)

條件單元格F2，行列都是相對引用，行號是應(yīng)用范圍F2:F11中最小行數(shù)2。

再譬如：在C2:G11內(nèi)，如果數(shù)字在列內(nèi)存在重復(fù)，則重復(fù)數(shù)字突顯。

格式公式=COUNTIF(C$2:C$11,C2)>1

條件單元格C2，行列都是相對引用，行號是應(yīng)用范圍最小行2，列是應(yīng)用范圍首列C。

4)需求4：如果條件為真，應(yīng)用范圍中所有數(shù)據(jù)突顯

規(guī)則：單元格為絕對引用，具體行列數(shù)按需指定。

譬如,若H1等于TRUE，則E列數(shù)據(jù)突出顯示。

格式公式=$H$1=TRUE

條件單元格H1的行列都絕對引用。

再譬如,若G5等于G列最小值，則C2:G11全部突顯。

格式公式=$G$5=MIN($G$2:$G$11)

到此，你知道文章最初部分格式效果錯誤的原因嗎？

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

做Excel高手，快速提升工作效率，部落窩教育Excel精品好課任你選擇！

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)Excel：

相關(guān)推薦：

Excel里的條件格式圖標集，你會用嗎？

用條件格式查找數(shù)據(jù)

用條件格式建立報價查詢系統(tǒng)、自律打卡表

半圓狀的柱形圖

版權(quán)申明：

本文作者小窩；部落窩教育享有稿件專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。