Excel条件格式错误原因分析：判断公式编写规则注意事项

编按：条件格式效果错误，不是自己想要的，原因是什么呢？用公式设置条件格式需要遵循什么样的规则才能避免错误呢？文章分别讲了四种需求的条件格式设置规则。

1、条件格式效果错误原因

为何有时用公式设置的条件格式效果达不到想要的结果,是错的？譬如下方。

需要：如果E列中数据大于100，其所在行整行数据突出显示。

实际：

用公式设置条件格式得到错误结果，主要有3个原因。

（1）条件单元格的引用方式（绝对引用或相对引用）错误

（2）条件单元格的引用地址错误

（3）公式错误

3个原因中，前方两个错误最迷惑人，下面具体说说。

2、用公式设置条件格式的单元格引用规则

1)需求1：当单元格条件为真，数据所在整行突显

整行突显，意味着公式右拉被引用单元格也要不变，所以需要锁定列。

规则：条件单元格的列绝对引用，行数等于应用范围中的最小行，列数在应用范围中按需指定。

譬如， D列中<100的数据所在行突出显示，应用范围C2:G11。

格式公式=$D2<100。

条件单元格D2的列绝对引用，行号是应用范围中的小值2。

再譬如： D列中重复的数值所在行突出显示，应用范围C2:G11。

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

条件单元格D2是列锁定，行数为范围最小值2。

公式中的其他部分，如$D$2:$D$12，应按在单元格中输入的同判断的可下拉右拉填充公式书写。

譬如，此处判断公式写作“COUNTIF($D$2:$D$12,$D2)>1”，因此格式公式中这部分就是$D$2:$D$12。

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

又譬如，突显D列中最小数据所在行。

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

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

2）需求2：单元格条件为真，数据所在整列突显

整列突显，意味着公式下拉被引用单元格也要不变，所以需要锁定行。

规则：条件单元格的行绝对引用，地址等于应用范围中第一个单元格。

譬如，第2行中>100的数字所在列突出显示，应用范围C2:G11。

格式公式=C$2>100

条件单元格C2的行绝对引用，地址是应用范围中第一个单元格C2。

再譬如， C2:G11内各列的列首数据有重复的，则数据所在整列突出显示。

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

条件单元格C2的行绝对引用，地址等于应用范围中第一个单元格C2。

若直接在单元格中输入下拉右拉公式判断各列的首值是否重复，公式=COUNTIF(C$2:C$11,C$2)>1。

3)需求3：如果条件为真，数据所在单元格突显

规则：单元格为相对引用，行数为应用范围最小行，列数为应用范围第一列。

譬如，F列中大于50小于110的数据突出显示。

=AND(F2>50,F2<110)

条件单元格F2，行列都是相对引用，行号是应用范围F2:F11中最小行数2。

再譬如：在C2:G11内，如果数字在列内存在重复，则重复数字突显。

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

条件单元格C2，行列都是相对引用，行号是应用范围最小行2，列是应用范围首列C。

4)需求4：如果条件为真，应用范围中所有数据突显

规则：单元格为绝对引用，具体行列数按需指定。

譬如,若H1等于TRUE，则E列数据突出显示。

格式公式=$H$1=TRUE

条件单元格H1的行列都绝对引用。

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

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

到此，你知道文章最初部分格式效果错误的原因吗？

做Excel高手，快速提升工作效率，部落窝教育Excel精品好课任你选择！