条件格式：让异常自己跳出来

[TOC] 做表最怕的不是数据多，而是问题藏在数据里看不出来。三千行进货单里，有 4 行单价填错了小数点；两百个客户里，有 7 家回款已经逾期。靠肉眼一行行找，找到眼花也未必找全。条件格式就是让 Excel 替你盯着这些格子：满足条件的自动变色、画条、长图标，异常一出现就自己跳出来。本篇讲清楚内置规则怎么选、自定义规则怎么写、规则多了怎么管。

先弄清楚条件格式在干什么

条件格式的本质是一句话：如果单元格满足某个条件，就给它套上某种格式。重点是它只改变显示效果，不改变单元格里的真实数据。这一点非常关键：你可以放心地把欠款标红、把进度画条，原始数值一个都不会被动到，公式和排序都不受影响。

第二个要点：条件格式是动态的。今天把某个客户的回款补齐了，红色会自动消失，不用你再去手动改颜色。明天新录入的行如果满足条件，也会立刻变色。这比手工填色可靠得多——手工标的颜色是死的，数据一变就成了误导。

第三个要点：它是在原表上叠加的一层渲染。所以别用它代替数据本身。给别人提交正式报表时，条件格式最好只做提示用途，关键结论还是要落到文字和数字上。另外打印的时候，色块通常能保留，但数据条和图标集有可能印不清晰，正式打印件建议以颜色为主，打印的设置细节稍后细讲。

五个内置规则：先学会再折腾

开始选项卡里点“条件格式”，会看到五类内置规则。它们覆盖了八成的日常需求，先把这五类用熟。

使用路径统一是：选中要套用规则的单元格区域、开始、条件格式、选规则类型、填条件、选格式、确定。注意筛选和排序都会带上条件格式一起走，不用担心标好的色排乱了。

最常用的是突出显示单元格规则里的“大于、小于、等于、介于、文本包含、重复值”。给逾期账款标红，就是选中逾期天数列，突出显示单元格规则、大于、输入 30，格式选红色填充加深红文字，确定。三步完成，全表的逾期单立刻浮出来。

项目选取规则里的“前 10 项”可以改数量，“前 10%”适合按比例找头部。核对数据质量时还有个神技：选中客户名列，项目选取规则没有的，用突出显示规则里的“重复值”，所有重名客户秒变粉红，配合排序篇的排序和去重操作，查重效率翻倍。

数据条、色阶和图标集：把趋势画进格子

数字看花眼的时候，图像语言更直接。数据条、色阶、图标集就是干这个的，做完不需要额外画图表。

数据条适合纵向看一列数值的相对大小。选中库存数量列，条件格式、数据条，挑一种颜色，每格出现一根长短按数值比例的条。默认数据条会随数字变化长度，一眼就看出哪个仓库压货最多。有个常用设置：点“其他规则”，勾选“仅显示数据条”，数字被条挡住时这样最干净；也可以设最小值最大值为数字，比如最小值 0、最大值 100，让所有条按统一刻度比较。

数据条还有个细节值得注意：负数默认朝左画。做利润环比时，亏的月份条朝左、赚的朝右，零线对齐，比光看正负号直观得多。如果只想看绝对量级不看方向，在规则里把负值的处理方式改成从零开始即可。另外数据条的颜色尽量选饱和度适中的，太刺眼整列看下来眼睛累，太浅又起不到区分作用，实际试两三个颜色选最舒服的。

色阶适合看一片数据的冷热分布。十二个月乘五个地区的销售矩阵，套上绿黄红三色色阶，哪个月哪个地区旺、哪块塌陷，一秒钟看清。色阶的默认规则是按当前选区的最小最大值映射颜色，如果想跨表统一标准，在管理规则里把最小值最大值改成固定数字，比如最小 0 红、最大 50000 绿，这样几个月看下来标准一致，不会每个月的红色含义都不一样。

图标集更接近状态灯。常用的三种图标：三色交通灯（红黄绿）看健康度，三向箭头看环比升降，勾叉圈看合格与否。图标集最容易踩的坑是阈值：默认按百分比三分，可能和你的业务标准对不上。比如完成率要用 60 及格、90 优秀，点其他规则，把图标改成三色旗，阈值类型从百分比改成数字，设 60 和 90 两档，出来的结果才符合业务口径。

图标集还有个进阶玩法：只显示图标不显示数字。在编辑规则对话框里勾选“仅显示图标”，单元格里的数字被藏起来，只剩图标，做成大屏看板式的状态列特别整齐。注意这样做的代价是格子里的值还在、只是看不见，别人复制走数据时数字照样在，不用担心信息丢失。

四个典型场景，直接照着套

讲再多原理，不如记住几个现成套路。下面四个场景覆盖了大部分日常工作，格式可以直接照搬。

场景一，应收账款预警。表中两列：合同金额和逾期天数。选中逾期天数列，突出显示单元格规则、大于、30，红色填充。再选中整片数据区，用公式规则把逾期超过 60 天的整行套深红：公式 =$G2>60，格式选深红填充白字。这样打开表，哪些客户只是轻逾期、哪些已经红得发紫，扫一眼就分级清楚。

场景二，库存上下限监控。安全库存 200、超储上限 800。选中库存列建两条公式规则：

=$C2<200    格式：红色填充
=$C2>800    格式：黄色填充

低于 200 提示补货，高于 800 提示去库存，正好符合仓储管理的双红线逻辑。两条规则互不冲突，一个格子只会触发其中一条。

场景三，考勤异常标色。考勤表里有上班打卡和下班打卡两列时间。想把迟到、早退、漏打卡分别标出来：迟到用下式标黄，早退用第二式标蓝，漏打卡用第三式标灰。三色一分，月底核对考勤不用再一行行看时间，四种异常各占一色。

=$C2>TIME(9,0,0)            迟到
=$D2<TIME(17,30,0)          早退
=OR(ISBLANK($C2),ISBLANK($D2))    漏打卡

场景四，进度条式的项目表。完成率列套数据条并设百分比刻度，0 到 100，再配一条公式规则 =$E2<0.5 把整行标浅黄。项目表打开就是一张自带红黄绿状态的作战图，周会直接投屏用。

这四个场景的共同点：规则一旦建好，数据怎么变它都自己跟进。月初清空的表，到月底又自动满色，比每次手工标色省下的不只是时间，更是肉眼排查的漏看风险。

规则类型：基于各自值设置所有单元格的格式
图标样式：3 个彩色旗（红、黄、绿）
红色旗   <=  60       （数字）
黄色旗   <=  90       （数字）
绿色旗   >   90       （数字）

用公式写自定义规则

内置规则只认单元格自己的值。一旦涉及横向比较，比如“把超过部门平均值的标出来”“整行标红而不只是一个格子”，就得用公式自定义规则。

整行标色是最典型的需求。一张报销明细，要把金额大于 2000 的整行标黄，不是只标金额格。操作：选中整个数据区（比如 A2:F500），条件格式、新建规则、使用公式确定要设置格式的单元格，公式写：

=$C2>2000

这里的美元符号是灵魂所在：列锁定、行不锁定，所以判断始终看 C 列（金额列），但格式会套到整行。第一个被选中的单元格是 A2，公式就以它为参照写；如果你从 A2 开始选，写出来的判断列会偏移。记住口诀：判断哪列就锁哪列，从哪个单元格开始选就参照哪个单元格。

再看一个跨列比较的例子。计划进度表里有计划值和实际值两列，要把实际没达标的行标红。同样选中 A2:F500，公式写：

=$D2<$C2

即 D 列实际值小于 C 列计划值就标红。规则套上去之后，哪一行落后于计划一目了然，且新录入的数据自动跟进。

公式的边界要提一句：条件格式公式里不能直接用跨工作表引用，需要的话用 INDIRECT 或在当前表做辅助列。同时条件格式公式不要写得太复杂，IF 嵌套三四层就到极限了，真有复杂逻辑，先在辅助列里算出 TRUE 或 FALSE，再让条件格式引用辅助列——这样改条件的时候改公式即可，条件格式里找个错误要翻半天。

规则多了怎么管：清除、优先级和冲突

用条件格式用上瘾的人，最后都会面对同一个问题：一张表贴了七八层规则，互相打架，不知道哪层在起作用。

这时候打开条件格式、管理规则。这个对话框列出当前工作表或当前选中区域的所有规则，每条显示规则内容、格式预览和适用范围。你能做的操作有三个。

一是改优先级。规则列表从上到下就是执行优先级，上面的先生效。两条规则冲突时（比如一个说标红、一个说标绿），靠上面的赢。用上箭头下箭头调整顺序即可。

二是改适用范围。规则套错了区域是常事，本来只想标 C 列，结果把整张表都染了。在管理规则里选中规则，把“应用于”的引用改对，比删了重做快。

三是果断清除。很多人的表里留着半年前试手时建的规则，早没用了，还在每行刷一层颜色。条件格式、清除规则、清除整个工作表的规则，定期清理一遍。清除规则不会删任何数据，放心点。

养成一个交付前的固定动作：把表拉近管理规则对话框从头到尾过一遍，确认每条规则的适用范围和当前数据区一致。表格增删行之后，规则的适用于范围不会自动跟着扩张，三月份建的规则到五月份数据多了二十行，新行往往不在规则范围内，查异常时就会漏掉新数据。把适用范围改成整列引用（比如 C:C 或 C2:C10000）能一劳永逸，代价是空行也被规则罩着，不过空格子不变色，无伤大雅。

还有一个判断冲突的原则：不同类型的格式可以叠加（比如一个规则设背景色、另一个设字体色，两个都会显示），同类型格式只有优先级最高的生效，除非勾选了“如果为真则停止”。勾上这个框后，这条规则成立时就不再往下检查更低优先级的规则，做互斥状态灯时很有用。

常见问题

条件格式为什么不生效，或者只在部分单元格生效？

九成是适用区域没选对。新建规则时选了哪些格子，规则就只作用于哪些格子；在 A 列建的规则不会自动罩住 B 列。另一个常见原因是规则套在了筛选隐藏行上，或者复制粘贴时带过来了旧规则把新规则盖住。排查方法：点中不变的格子，打开管理规则，看左上角的显示范围是不是整个工作表，核对规则列表里有没有覆盖该格子的高优先级规则。还有一个隐藏坑：手动给格子设过背景色，条件格式的颜色会被手设的颜色压住（手设格式优先于条件格式），清除单元格格式即可。

数据条或图标集印出来看不清怎么办？

打印机对细线条和渐变色很不友好。正式报表打印时，把数据条改成实心填充并加深颜色，图标集改用形状类图标（勾叉、圆点），对比度更高。或者干脆换思路：保留色阶用的纯色填充来传达信息，把数据条图标集留给屏幕查看用。打印效果和屏幕显示从来不是一回事，正式交付前一定先打一张样张自己看看，别等打完一摞才发现色块全成了浅灰。

复制行到别的表，条件格式跟过去了怎么删？

复制过来的单元格会把规则一起带过去，这也是条件格式扩散的主要途径。删法：选中新表里那片区域，条件格式、清除规则、清除所选单元格的规则。如果只是想保留数据不要任何格式，用选择性粘贴、只粘贴数值，一开始就断了格式跟过来的可能。养成习惯：从带条件格式的表往外提数据，一律选择性粘贴数值。

规则太多互相打架，怎么快速理清？

打开管理规则，把“显示以下内容的规则”改成当前工作表，所有规则按优先级列出来。逐条检查：公式或条件还对不对、应用于范围是不是已经超出现在的数据区、格式是不是和别的规则重复。没用的立刻删除，还在用的理清优先级，给关键的互斥规则勾上“如果为真则停止”。理完之后，新建一行测试数据验证效果，确认整张表的变色逻辑符合预期再交付。

相关篇章

条件格式让异常跳出来，接下来是把数据整理成报表：见分类汇总和 subtotal：分层看数据学分层汇总，或者数据透视表：拖拽出统计报表直接拖出多维报表。打印交付前记得回顾模板和打印：页面布局不将就，免得漂亮的配色印成一团灰。