作者:祝洪忠 转自:Excel之家ExcelHome

小伙伴们好啊,今天老祝和大家一起学习一下Excel中的条件格式。

使用Excel的条件格式功能,可以根据单元格中的内容应用指定的格式,改变某些具有指定特征数据的显示效果,使咱们能够直观地查看和分析数据、发现关键问题。

Excel内置的条件格式规则包括“突出显示单元格规则”、“最前/最后规则”、“数据条”、“色阶”和“图标集”,这些操作都比较简单,咱们就不一一细说了。

如果要设置的条件较为复杂,还可以使用函数公式作为条件格式的规则。好了,下面开始划重点:

1、在条件格式中使用函数公式时,如果公式返回的结果为TRUE或是不等于0的任意数值,则应用预先设置的格式效果。如果公式返回的结果为FALSE或是数值0,则不会应用预先设置的格式效果。

2、在条件格式中使用函数公式时,如果选中的是一个单元格区域,可以以活动单元格作为参照编写公式,设置完成后,该规则会应用到所选中范围的全部单元格。

3、如果需要在公式中固定引用某一行某一列,或是固定引用某个单元格的数据,需要特别注意选择不同的引用方式。在条件格式的公式中选择不同引用方式时,可以理解为在所选区域的活动单元格中输入公式,然后将公式复制到所选范围内。

OK,划完重点,接下来就是实操了,先看第一个应用,根据D列的债券到期收益率,对最高收益的债券整行突出显示

excel条件格式的公式怎么写(让条件格式更牛掰)(1)

使用的公式为:

=$D2=MAX($D$2:$D$11)

接下来简单说说公式的意思吧:

先使用MAX($D$2:$D$11)计算出D列的收益率最大值,然后与D2单元格中的数值进行比较,判断该单元格中的数值是否等于该列的最大值。

因为事先选中的是一个多行多列的单元格区域,并且每一行中都要以该行D列的到期收益率作为比对的基础,所以$D2使用列绝对引用。

而每一行每一列中都要以$D$2:$D$11单元格区域的最大值作为判断标准,所以行列都使用了绝对引用方式。

接下来还有一个独家小妙招。

咱们在工作表中输入函数名称时,Excel默认会显示屏幕提示,方便选择适合的函数,而在【编辑格式规则】对话框中输入函数名称时,则不会出现屏幕提示。在条件格式中使用复杂公式时,不够方便有没有?

怎么办?咱们可以先在单元格中编写公式,然后复制到【为符合此公式的值设置格式】编辑框。

请看老祝的操作:

突出显示各部门考核分数最高的记录

excel条件格式的公式怎么写(让条件格式更牛掰)(2)

这里用到的公式是:

=$C2=MAX(IF($A$2:$A$16=$A2,$C$2:$C$16))

怎么样,有点小复杂吧?

咱们简单说说这个公式的意思。

先使用IF函数,判断$A$2:$A$16单元格区域中的部门是否等于$A2单元格的部门,如果条件成立则返回$C$2:$C$16单元格区域中对应的数值,否则返回逻辑值FALSE。

再用MAX函数忽略内存数组中的逻辑值计算出最大的数值。

最后用$C2单元格中的数值与MAX函数的结果进行比对,返回逻辑值TRUR或是FALSE。

大家注意到了吗,在条件格式中使用数组公式时,不需要按<Ctrl Shift Enter>组合键结束哦。

说了几个小复杂的公式应用,小伙伴们有没有晕头转向啊,接下来看几个简单一点的吧:

突出显示重复数据

excel条件格式的公式怎么写(让条件格式更牛掰)(3)

这里用到的公式是:

=$C2=MAX(IF($A$2:$A$16=$A2,$C$2:$C$16))

怎么样,有点小复杂吧?

咱们简单说说这个公式的意思。

先使用IF函数,判断$A$2:$A$16单元格区域中的部门是否等于$A2单元格的部门,如果条件成立则返回$C$2:$C$16单元格区域中对应的数值,否则返回逻辑值FALSE。

再用MAX函数忽略内存数组中的逻辑值计算出最大的数值。

最后用$C2单元格中的数值与MAX函数的结果进行比对,返回逻辑值TRUR或是FALSE。

大家注意到了吗,在条件格式中使用数组公式时,不需要按<Ctrl Shift Enter>组合键结束哦。

说了几个小复杂的公式应用,小伙伴们有没有晕头转向啊,接下来看几个简单一点的吧:

突出显示重复数据

excel条件格式的公式怎么写(让条件格式更牛掰)(4)

公式为:

=COUNTIF(A$2:A2,A2)>1

COUNTIF函数第一参数使用A$2:A2,表示从A2单元格开始至公式所在行的A列。在这个动态扩展的范围中,统计有多少个单元格与公式所在行的内容相同。

突出显示已完成合同

excel条件格式的公式怎么写(让条件格式更牛掰)(5)

公式为:

=WEEKDAY(A$3,2)>5

1、WEEKDAY函数返回某日期为星期几。第二参数使用2,表示以1~7表示星期一到星期日。

2、对第三行中的日期进行判断后,如果数值大于5,即表示该日期为周六或是周日。

生日一周内提醒

excel条件格式的公式怎么写(让条件格式更牛掰)(6)

公式为:

=WEEKDAY(A$3,2)>5

1、WEEKDAY函数返回某日期为星期几。第二参数使用2,表示以1~7表示星期一到星期日。

2、对第三行中的日期进行判断后,如果数值大于5,即表示该日期为周六或是周日。

生日一周内提醒

,