为单元格设置条件格式,是我们工作中常常用到的场景。
比如,判断某个日期是否到期,判断某个分数是否达标,判断某个值是否有重复值等等,最后根据判断结果给满足条件的单元格呈现特定的格式。
Excel本身自带了一些简单的常用的条件格式规则,如,这些内置规则比较简单,一般人用起来也都没什么问题。
今天主要想跟大家分享一下,当用公式来设置条件的时候,不同的引用方式带来截然不同的结果。
对单独的一个单元格设置条件格式
对单一的单元格设置公式条件格式,很简单。逻辑就是对同一行内科目的的分数求和,然后判断,满足条件则呈现指定格式。
逻辑清晰,很简单,没有什么可说的。G列的总和是放在这里验证设置是否正确。
对单列中的多个单元格设置条件格式
首先我们要选中B列中需要设置条件格式的所有单元格,然后设置公式条件格式。
注意了,这个时候,公式发生了微小但是至关重要的变化:SUM函数中的引用区域从"$C$4:$F$4"变成了"C4:F4"。
这里的变化意味着什么呢?
选中多个单元格,然后设置公式条件格式,背后的本质是:先对选中单元格区域中的第一个单元格设置公式条件,然后将公式条件复制到其他单元格去。
(上面这句话一定要读懂)
好,既然本质是复制公式条件,那么就必然涉及到公式的引用方式。
如果我们在这个案例中依然使用"$C$4:$F$4",这是绝对引用方式。那么就意味着同学B,同学C…的单元格条件格式判断依据跟同学A的判断依据一样,依赖于同学A的成绩数据。显然,这是一个明显的逻辑错误。
所以正确的做法是将"$C$4:$F$4"改为"C4:F4",也就是将绝对引用方式改为相对引用方式。然后当公式被复制的时候,同学B,同学C…的单元格条件格式判断依据就会相应地变成"C5:F5","C6:F6"… 。
一定要理解这一点,在条件格式里写公式和我们在单元格上写公式逻辑是一样的。
思考:如果将"$C$4:$F$4"改为"$C4:$F4"可以吗?为什么?
提示:跟在单元格中直接写公式的逻辑是一样的。
答案是可以的,同学B,同学C…的单元格条件格式判断依据会相应地变成"$C5: $F5","$C6: $F6"… 。完全不影响结果对吗?
如果你理解了前面两种场景,那么请接着往下看。
对于整个数据区域设置条件格式
第一步,选中数据区域
第二步,设置公式条件格式
关键点还是在于引用方式的设置。这个案例中,当我们设置公式的时候,是对B4单元格设置,然后复制到其它所有选中的单元格(一定要理解背后的本质)。
现在我们想想,不管我们将B4单元格中的公式复制到数据区域中的其它任意一个单元格,需要进行求和SUM计算的数据都处于C列到F列之间,但是这些被求和的数据又分别处于第5行,第6行…第11行,对吗?
不着急,先将上面这句话彻底理解了再往下看。
…
…
…
那么,这就意味着当我们设置B4单元格公式的时候,
要做到被复制的时候列号(C:F)不能发生变化,需要在列号前面加上$符号用于"锁定";
要做到被复制的时候行号(4)可以变成为(5,6…11),行号前面绝对不能添加$符号(不能锁定)。
到这里,明白了吗?
所以,我们在B4中设置的公式一定要是"=SUM($B4:$F4)>320"。
最后,我们得到想要的结果如下:
总结
其实,对数据区域进行公式条件格式的时候,我们设置的不是一个公式,而是给区域中每一个单元格都分别设置了一个公式。所以,如果你明白了不同的引用方式(尤其是混合引用),以后这类操作就可以游刃有余了。
话说回来,学习Excel函数之前,我们都应该非常透彻地理解三种不同的引用方式:绝对引用,相对引用,混合引用。
看完这篇文章,你get到了吗?
Excel注重动手操作,如果你希望获取文中素材进行练手,请私信我"200226"自动获取下载链接。
又或者,如果你希望更系统化地学习Office,点击左下方的“了解更多”。
我是微软认证讲师MCT.
关注我,一起玩Office。
閱讀更多 玩Office 的文章