使用公式控制格式应该来说是条件格式中最难的:

第一个难点是如何按要求写出有效的公式;

第二个难点是条件格式的作用有限顺序和范围;

「使用公式确定要设置格式的单元格」这条规则中,「条件」指的是公式的运算结果,只有当条件为「真」时,格式才能生效。这就对公式的结果有一个明确的要求:即公式的结果必须是逻辑值(公式必须返回是或否)。

设置操作方法很简单,填写公式、设置格式即可完成,如图所示。

使用公式确定要设置格式的单元格.png

▌01 突出显示每科目的最低成绩

Step1:选中C2:E13单元格区域,新建格式规则

突出显示每科目的最低成绩.png

Step2:写入公式,然后设置突出显示的格式,=C2=MIN(C$2:C$13)

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

提示:

1、MIN(C$2:C$13)部分计算出公式所在列的最小值,注意这里的公式单元格引用C$2:C$13,列相对引用、行绝对引用,这是因为总共有三列科目,作用区域需要自动扩展为D$2:D$13和E$2:E$13

2、然后判断C2(活动单元格),是否等于公式所在列的最小值

3、在条件格式中,针对活动单元格的设置,将被作用到所选区域的每一个单元格

▌02 多条件设置

如图所示的成绩单,将科目一和科目二均大于90的成绩,在科目一和科目二两列标记出来。

将科目一和科目二均大于90的成绩,在科目一和科目二两列标记出来.png

应该设置的公式为 =AND($C2>90,$D2>90),(AND($C2>90,$D2>90)部分表示两个条件同时满足)

=AND($C2>90,$D2>90).png

因为需要标记科目一和科目二两列,所以条件格式的作用区域为$C$2:$D$13

条件格式的作用区域为$C$2:$D$13.png

▌03 多条件多规则设置

多条件多规则设置就更加复杂了,我们来看一个场景:

如果有一科目不及格,则这一整行标记为黄色底纹;

如果两个科目不及格,则这一整行标记为红色底纹。

如果有一科目不及格,则这一整行标记为黄色底纹;如果有二科目不及格,则这一整行标记为红色底纹;.png

Step1:设置「如果有一科目不及格,则这一整行标记为黄色底纹」的规则

公式为=OR($C2<60,$D2<60),并设置填充为“黄色”底纹。

公式=OR($C2<60,$D2<60),并设置填充为“黄色”底纹.png

说明:

OR函数是“或”的意思,它的参数中只要有一个为真,则函数的返回值就为真,因此=OR($C2<60,$D2<60)表示两个科目的成绩只要有一个不及格,公式的结果就为真。

因为要对整行填充颜色,需要把“条件格式”设置到整行的区域:可以在设置条件格式之前选择$A$2:$D$13区域,也可在管理规则中修改「应用于」区域。

点击【应用】之后,会发现数据区域中只要有一科目成绩不及格,就被标记为黄色底纹,其中当然也包含两科目都不及格的情况,如图第12行。因此,我们还需要再设置条件,专门为两个科目都及格的情况设置红色底纹。

Step2:设置「如果两个科目不及格,则这一整行标记为红色底纹」的条件,

写入公式=AND($C2<60,$D2<60),并设置填充色为红色,

公式=AND($C2<60,$D2<60),并设置填充色为红色.png

同样在管理窗口,将新设置的「条件格式」应用于整个数据区域,如图所示。在规则管理窗口,可以看到新创建的规则,自动排在了前面,这种排列顺序就是「条件格式」的“优先级”,如果两个条件有冲突,排在前面的「条件格式」先执行,通过下图中右上角的三角符号可以调节条件格式的顺序。

如果两个条件有冲突,排在前面的「条件格式」先执行,通过下图中右上角的三角符号可以调节条件格式的顺序。.png

提示:

如果公式=OR($C2<60,$D2<60)设置的条件放在第一位,那么将优先执行此条件,而两个科目都不及格也是满足此条件的,所以就不会执行公式=AND($C2<60,$D2<60)设定的条件,因此红色底纹不会显示。所以,在多条规则的情况下,一定要注意规则的优先级。

▌004 条件格式管理

01 规则管理

我们可以通过「条件格式规则管理器」对条件格式进行管理:

①:选择要管理的条件格式规则

②:新建规则

③:编辑规则(对规则进行重新设置)

④:规则优先级调整

⑤:规则应用范围更改

⑥:终止规则选项

条件格式管理.png

02 条件设置要点

我们知道使用公式控制格式是难点,其核心是如何将条件用函数的表达出来。

1、公式的编写

首先公式必须以等号=开头,这个等号的意思是判断「是否满足公式的结果」,等号后面的公式是要判断的条件,公式的返回值要么是TRUE,要么是FALSE,不能为其他类型的返回值。

2、公式中单元格区域的引用方式

条件格式是逐个单元格进行判断的,在条件格式中,针对活动单元格的设置,将被作用到所选区域的每一个单元格。因此,在设置公式的时候,一定要注意相对引用和绝对引用的正确使用

3、条件区域与作用范围

设置单元格的条件格式,一定要搞清楚条件所引用的单元格和满足条件的情况下,需要应用格式的单元格,两者可以不同也可以相同。

比如:条件区域为课程成绩,作为范围即可以是科目成绩,也可以是姓名,甚至可以是整行。

4、规则的优先级

在规则管理窗口中,优先执行排在上面的规则。在多规则并存的情况下,如果他们的作用区域是相同的,这时候就要考虑规则的优先级