条件格式不用说是Excel软件中的一个利器,不论是数据核对还是快速突出显示定位关键数据,甚至制作各种智能自动化办公模板,都离不开条件格式。
之前Excel表哥微信公众号分享过两例利用条件格式制作的模板:
今天就来分享一下表哥对条件格式的个人理解和设置方法。
01
什么是条件格式?
条件格式是Excel内置的一项功能,它可以根据用户所设定的条件,对单元格中的数据进行判断,将符合条件的单元格用特殊定义的格式来显示。
简单来说条件格式分为两部分:
▍所谓条件:基于Excel单元格自身的数据运算出来的逻辑值,只有True和False两种结果。
▍所谓格式:包括设置单元格数值格式,字体名称,大小,边框,填充色等。
02
条件格式在哪里?
不同的Excel版本略有差别,Excel2010及以后的版本,条件格式在菜单栏的位置如下:
条件格式内置了多种规则,比如突出显示单元格规则、最前/最后规则、数据条、色阶和图标集等。
除此之外,也支持设置自定义规则。
03
如何理解条件格式的设置规则?
我们可以这么理解条件格式:
所谓条件格式,即指:满足条件,设置格式
因此,条件格式可以理解成是一个逻辑函数
IF(条件=TRUE,设置单元格格式,保持原来的格式)
如果指定单元格中的值满足某一设定的条件,则将单元格设置为设定的格式,否则保持之前的格式不变。
▍需要注意的是:只要条件格式中设置的公式计算结果不为0或者FALSE,都认为是TRUE!
对于条件格式内置的规则,主要是基于单元格各自值或者常见的排序统计情况来设置,不需要用户自己设置公式条件,理解起来非常简单,大家用几次就顺手了。
这里主要分享一下表哥关于条件格式中使用公式确定要设置格式的单元格的理解。
许多读者朋友对自定义公式设置的条件格式规则设置比较头大。
我们通过下面的举例来说一说如何才能更好的理解条件格式的运行机制。
04
自定义公式条件格式设置举例
以我们之前分享的《火爆全网的A4纸上的人生900格》(◀点击链接查看)为例:
下面是未设置条件格式前的表格,在一个30×30的单元格矩阵中,填充了1-900的数字。
假设我们需要高亮显示从B3单元格开始的日期计算,截止B4单元格今日日期已经过去的月份总数,月份累计通过B5单元格的DATEDIF公式计算:
B5单元格公式:=DATEDIF(B3,B4,"m")
那么我们可以按照如下动图来设置条件格式中的公式:
在条件格式->新建规则->使用公式确定要设置格式的单元格->为符合此公式的值设置格式,填写如下的公式:
▍注:此处E3为相对引用,$B$5为绝对引用。
那么应该如何理解条件格式中设置的公式呢?
我们可以将条件格式理解为一个特定的蒙板,蒙板里是设置好的格式,当单元格中的内容满足设定的公式时,蒙板就会显示出来。
下面的动画示意图有助于理解条件格式的执行应用过程:
因此我们可以使用下面的三层图层层叠来理解条件格式的设置过程:
▍注:公式计算蒙板在条件格式运算过程中实际是不存在的,此处是为了方便大家理解。
以蒙板的方式来理解条件格式中公式的设置是非常方便灵活的,公式可以是任何引用方式,条件格式的应用范围和公式蒙板也是完全独立的。
05
条件格式理解难点
理解了条件格式的设置顺序后我们接下来还需要掌握一个重要的知识点。
新手在使用自定义公式设置条件格式时候最让人感到困惑的地方在于:
为什么要编写公式=E3<=$B$5,而不是=E4<=$B$5,或者=AH32<=$B$5?
这其实就是上面动图演示中的第二张蒙板,公式计算蒙板,想象一下或者实际操作中当我们选中区域$E$3:$AH$32时,我们可以看到选中区域中E3单元格和其他颜色不一样。
其他单元格为灰色选中态,而E3虽也被选中,但处于反白显示。
这是因为当我们选中一整片区域时候,系统默认左上角的首个单元格为活动单元格,也就是E3。而我们设置的公式相当于是只在E3单元格里输入了公式。
此时系统会在选中区域的每个单元格根据我们设置的公式自动填充应用所有的公式。
这个过程就好像是我们在公式计算蒙板中从E3单元格左下角拖动自动填充符,将公式拖动应用至整个选中区域$E$3:$AH$32。
▍注:公式计算蒙板在条件格式运算过程中实际是不存在的,此处是为了方便大家理解。
06
多个条件的书写关系
条件格式中的公式运算结果均为逻辑值,需要注意的是:
当公式运算结果为0,表示FALSE,条件格式不成立;
当公式运算结果为非0,则表示TRUE,条件格式成立;
既然如此,那么我们就可以很容易地知道如何书写出具备一定逻辑关系的多个条件格式的公式。
我们可以使用由AND、OR、XOR或者NOT函数进行多条件的逻辑运算,不过一般条件格式中使用到最多的还是逻辑和以及逻辑或这两种关系:
AND(条件1,条件2...,条件n)
OR(条件1,条件2...,条件n)
为了避免过多的公式,其实我们还可以简单的使用算术运算的方法进行等效以简化公式长度,例如AND和OR对应的数学运算分别为:
多个条件同时满足:(条件1)*(条件2)*...*(条件n)多个条件满足其一:(条件1)+(条件2)+...+(条件n)
举个例子:设置人生900格中年龄在(30,60]岁之间的单元格为橘黄色填充。
条件格式中应用到的公式为:
=(E3>360)*(E3<=720)*(E3<=$B$5)
对照上面的分析,这个公式应该就不难理解了吧?
再举一个例子,我们在工作中经常需要设置Excel表格奇偶行隔行填充,那么用条件格式实现则非常方便:
条件格式中应用到的公式如下,大家可以自己理解一下公式的含义。
=MOD(ROW($A1),2)
今天的分享就到这里。为了能有更好的展示效果,表哥连PPT都用上了,希望看了这篇文章的读者朋友能够豁然开朗一些吧~
如果还是不熟练,建议大家多多练习,自己实际操作体会下。表哥只能帮你到这了!
如果还是不太理解,也可以在公众号回复【定制】,邀请表哥成为你的私人顾问~
在条件格式的公式设置中往往离不开绝对引用、相对引用和混合引用,大家也需要对这些单元格引用方式较为熟练才能写出令自己满意的各种公式。
不熟练的读者朋友可以翻看下面的文章~
往期推荐
▎如何理解绝对引用与相对引用方式?
▎给自己的表格增加一盏聚光灯吧。
▎表哥的项目管理模板V3.0,利用条件格式制作
注:本公众号所载原创文章均为作者辛苦创作,转载请联系作者并标明出处。
处处留心皆学问,建议大家可以将这篇推文收藏,以备不时之需。
你点的每个"赞"我都认真当成了喜欢▼