来自：Excel卓越之道（ID：ExcelBro）
作者：徐军泰
条件格式，是Excel中我最喜欢的一项功能。
所谓条件格式（Conditional Formatting），简单说就是让符合条件的单元格自动显示为提前设定的格式。
比如——
“让成绩表中所有大于90分的单元格都自动用黄色底色进行高亮显示”，在这条规则中，条件是：单元格分数>90，格式是：黄色底色。
“让身份证列表中所有不是18位的单元格自动显示为黑底白字”，这条规则中，条件是：单元格中身份证号位数不等于18，格式是：黑底白字。
条件格式常用于数据预警和数据强调，在一定程度上提升了Excel报表的智能化程度。
下面我们通过案例来了解条件格式在Excel中的应用。
数据预警
所谓数据预警，就是将我们关注的数据自动加上格式进行高亮或强调。比如：
项目管理表中未能按时完成的任务
银行风险报表中不良率高于2%的数据
考试成绩表中不及格的学生
……
这些都是我们常常要关注的数据，下面通过GIF来看如何对数据进行预警。
▼ 成绩不及格的数据
说明：对于简单的数量关系判断，我们可以自己编写公式，也可以使用条件格式菜单中的“突出显示单元格规则”。
▼ 前10名、后10名
说明：在“项目选取规则”中可以根据排名或与平均值的关系对数据进行预警。这个案例中，成绩分布在C、E、G三列，也可以自定义公式，但必须先将排名区域定义为名称。
▼ 身份证号不等于18位的
说明：LEN函数可以计算文本的字符个数，<>为不等号。
▼ 将暂停的工作事项自动着色
▼ 将已发放的优惠券自动变灰
说明：这个GIF案例是我在为会员发放动态图表视频课优惠码时制作的，A列是提前生成的优惠码列表，一个码只能供一个人用。所以，每发出一个优惠码，我就在G列双击完成打勾，这时对应行就会自动标灰，表示此码已使用。
报表美化
关于报表美化，常见的一个案例是隔行底纹的制作，即每隔一行添加一个底色。
当然，简单的报表我们手动也可以完成。但是当我们对报表数据进行筛选、排序等操作后，手动制作的隔行底纹效果很容易被破坏。
而使用条件格式添加隔行底纹，有两个好处：一是快，不管报表有多少行，写一个公式瞬间搞定；二是格式不会受到排序、增删行等报表操作的影响。
▼ 隔行底纹的制作
说明：除了用MOD嵌套ROW函数，也可以直接用ISEVEN或ISODD嵌套ROW函数快速设置隔行底纹效果。
图表模拟
▼ 方块图的制作
如上图所示，方块图常用于百分比数据（如进度、占比、完成率、增长率）的表达。方块图通常由10*10总共100个小方块组成，每个小方块代表1%。这样，要表达的数据是百分之多少，就有多少个小方块被设置前景色。
你能想象在Excel中如何制作方块图吗？
我在《左手数据，右手图表》中提供了两种制作方法，一种是用散点图，另一种就是条件格式。相对而言，条件格式法更为简单、灵活，下面简单作介绍。
首先，构造一个10*10的单元格区域，每个单元格按次序填充数据0.01至1，并全部加上前景色，边框线改为白色。
然后，对此区域添加条件格式：=A4>$A$1，设置单元格颜色为灰色。
这样，所有大于A1（即0.75）的单元格就会自动变为灰色。
最后，用自定义格式（;;;）将单元格内的小数全部隐藏，并使用Excel照相机功能拍成图片，设置图片长宽比为1：1即可。
▼ 甘特图模拟
用同样的方法，我们还可以在项目管理工作中模拟甘特图，GIF效果如下：
这个案例留给大家自己思考，这里就不赘述了。
动态表格
如果说以上这些案例只是条件格式的简单应用，那下面这两个案例理解起来可能需要动点脑筋了。
当条件格式与下拉菜单、控件等功能结合在一起的时候，我们可以让报表的交互性实现一个质的突破。
▼ VLOOKUP函数的动态演示
说明：本例聚光灯效果用条件格式完成，用以演示VLOOKUP函数的查询过程。
▼ 像控制开关一样控件条件格式
说明：给条件格式添加一个开关
数据可视化
最后，我们再来看条件格式最重要的一个应用，即数据可视化——通过添加数据条或图标让数据更直观。
▼ 用条件格式生成数据条
说明：最简单的可视化就是用条件格式生成数据条，可以直接生成在数据单元格，也可以多复制一列，一列显示数据，一列显示数据条。
▼ 为数据添加颜色标记
说明：本例中K列的圆点用于标记优良差，根据O列计算的季度考核系数自动进行判断，考核系数大于1.3，代表“优”，用红色表示；考核系数小于1.2代表“差”，用浅红色表示；介于1.2到1.3则为“良”，用中间的红色表示。注意，在设置条件格式的时候只需添加两条规则就可以了。
总结
从以上案例我们可以看到，条件格式本身并不难掌握，但要想用好条件格式，关键还是在于函数的运用——也就是如何将条件用函数的语言表达出来。
条件格式中的函数运用，有三个点需要特别注意：
1、明确条件格式中等号“=”的含义
在条件格式中编写条件跟在单元格中编写函数一样，都是用等号开头，但是一定要注意其中的区别。
在函数中等号代表返回一个函数或公式计算结果，而条件格式中的等号则为判断之意，等号后面是要判断的条件，条件结果要么是TRUE，要么是FALSE。
2、条件格式中函数的引用方式
除了等号，条件格式中的函数用法与单元格中一样，但是务必要注意条件格式中的单元格引用方式。
由于条件格式是逐个单元格判断的，所以如果整个一列单元格如果都是条件单元格，那么在条件中引用的时候务必要锁定列而不锁定行，比如前面这个案例。
这个过程跟公式的复制有点类似，向下复制的时候行不能被锁定。
3、条件单元格与作用范围
最后一点，要弄清楚条件格式的作用范围。有时候，你的判断条件只是针对某一列数据，但是最后设置条件格式的时候要求满足条件的整行数据都要加上格式。
后台回复“动画教程”，获取《全套Excel原创动画教程》
后台回复“简历模板”，获取《100份简历模板》
后台回复“面试题”， 获取《100份名企笔试、面试题》
后台回复“报表模板”，获取《全套财务报表模板》
想要更多精品干货，请持续关注我们的微信公众号 Excel技巧精选（ID：ExcelSkill）↓↓↓
●进入公众号输入772发送，即可阅读本文
●进入公众号输入m发送，获取全部文章目录
