📌 阶段五 · 报表可视化 · 第15讲 条件格式与公式 · 约 45 分钟

用条件格式让低于安全库存的行整行标红、账龄超期自动高亮

学习目标

  • 会用突出显示单元格规则标记数值、文本和重复值
  • 理解多重条件格式的覆盖规律，安排好规则的先后顺序
  • 掌握用公式定义条件格式的写法，会让整行随单个条件变色
  • 会用数据条直观展示库存量的大小
  • 给进销存系统装上三道预警：缺货标红、账龄超期高亮、库存量数据条

知识点讲解

条件格式在哪、能干什么

条件格式的入口在开始选项卡-条件格式。它的作用是：当单元格满足某个条件时，自动套用你指定的颜色或格式——数据一改，颜色跟着变，不用手动刷。

最常用的是「突出显示单元格规则」：比如要把销售额大于 100000 的值标出来，就选中数据区域，点突出显示单元格规则-大于，在弹出的规则框里输入条件和格式样式；要把文本里含「返修」的单元格标红，就用「文本包含」，输入文字即可。规则随时可加，效果立刻可见。

💡 提示:

  • 设置前先想清楚两件事：对哪个区域设置、满足什么条件

重复值标记与清除规则

条件格式自带「查重」功能：选中要检查的列（比如商品编号列），点条件格式-突出显示单元格规则-重复值，出现两次以上的编号会自动变色——录入档案时防重名、防重复单号特别好用。

设置错了或者不要了，点条件格式-清除规则，可以清除所选单元格的规则，也可以清除整张工作表的规则。

💡 提示:

  • 重复值规则只是「标记」不是「删除」，正式查重可以配合第 9 讲的 COUNTIF 核对数量

数据条、色阶、图标集

突出显示规则之外，条件格式还有三种「可视化」武器：

数据条：在单元格里画一条横条，数值越大条越长，看库存量、销售额的相对大小一目了然；

色阶：用颜色深浅表示数值高低，适合看一片数据的分布；

图标集：给数值配上箭头、信号灯等小图标。

在数据透视表里同样可以加数据条：选中透视表的数值区域，开始-条件格式-数据条，挑一个填充方案即可。透视表还可以插入「切片器」：点击透视表任意单元格-数据透视表分析-插入切片器，勾选想要的字段，就能实现「点字段进行筛选」；不要了直接选中删除。

💡 提示:

  • 数据条适合单列数值的比较，别对整张表乱加，会花

多重条件的覆盖规律

对同一块区域设置多条规则时，后设的规则不会简单替换前面的——两者会叠加生效；但如果后一个条件的范围包含前一个条件，重叠部分就会覆盖。

举个例子：第一次设置「小于 100 万标红」，第二次设置「小于 200 万标蓝」，那么蓝色规则会把原本标红的部分盖掉，红色就看不到了。

想兼容显示的做法是：先设置大的范围标记，再设置小的——小的后设置，重叠处由它说了算，层次就对了。点条件格式-管理规则，能看到当前区域所有规则和它们的优先级顺序。

💡 提示:

  • 规则打架先去「管理规则」里看顺序，必要时调整或删除

用公式定义条件格式（本讲主角）

菜单里的预设规则满足不了「低于安全库存整行标红」这种需求时，就要用公式来定义：选中区域，点条件格式-新建规则-使用公式确定要设置格式的单元格，在输入框写一个公式，结果为 TRUE 的单元格就会套用格式。

两条铁律：

第一，公式要按「选中区域的左上角活动单元格」来写。比如选中的是 B2:B31，公式就以 B2 为基准，Excel 会自动把这个判断复制到区域里的每个单元格。

第二，想让「整行」跟着变色，就要锁列不锁行：写 =$H2<=$I2 这种形式——$ 锁住列号（判断永远看 H 列和 I 列），行号 2 放开（每一行用自己的数据判断）。如果忘了加 $，公式拖到 C 列就变成 C2，整行标红立刻失灵。

=$H2<=$I2

选中 A2:K31 整块区域后新建公式规则：当前库存(H 列)小于等于安全库存(I 列)时整行标红；$H2、$I2 只锁列不锁行，判断跟着每一行走

=B2>100

只选中日期列时的写法：以左上角 B2 为基准，判断同一行相邻列的数量是否大于 100

💡 提示:

  • 整行变色公式，$ 加在列字母前，不加在行号前——记住「锁列不锁行」

WEEKDAY 标记周末

给日期列标记周末，用 WEEKDAY 函数。它返回「一周中的第几天」，第二个参数写 2 时，用数字 1（星期一）到 7（星期日）表示——所以周六返回 6、周日返回 7。

只想标周六：=WEEKDAY(A2,2)=6；周六周日都标，用 OR 串起来：=OR(WEEKDAY(A2,2)=6,WEEKDAY(A2,2)=7)。想整行变色，还是老规矩，把 A 换成 $A2 锁列。

=WEEKDAY(A2,2)=6

第二参数 2 表示「1=周一～7=周日」，等于 6 即周六，条件格式里结果为 TRUE 就变色

=OR(WEEKDAY($A2,2)=6,WEEKDAY($A2,2)=7)

周六或周日任一成立就标色；$A2 锁列，整行一起变

快到期自动提醒：DATEDIF + TODAY

会员生日、合同到期、商品保质期……「快到期的自动提醒」用 DATEDIF 加 TODAY 实现。比如把未来 15 天内过生日的员工姓名标红：忽略年份，只要生日距离今天往后 15 天内相差小于等于 15 天就标色，公式写 =DATEDIF(C2,TODAY()+15,"yd")<=15——第三参数 "yd" 表示忽略年、月，只算天数差。

放进进销存系统，应收账款的「到期提醒」、采购计划的「到货倒计时」都是同一套思路。

=DATEDIF(C2,TODAY()+15,"yd")<=15

C2 是生日（忽略年份比较）：今天往后推 15 天与生日之间的天数差不大于 15，即未来 15 天内要到的日子

=TODAY()

TODAY 函数没有参数，只用一对空括号，返回系统当天日期，每次打开文件自动更新

实战演练

报表会算了，还得会「报警」。本讲给进销存系统装三道预警：库存台账里低于安全库存的商品整行标红、零库存再加一档深红；应收账款表里账龄超过 90 天的客户高亮催款；「当前库存」列加数据条，库存多少一眼看出长短。条件格式是纯格式操作，请在本地 Excel 中跟随步骤完成，网页练习数据用来核对表结构。

  1. 检查库存台账表结构 — 打开库存台账表，确认表头 A1:K1（商品编号、商品名称、类别、单位、期初库存、累计入库、累计出库、当前库存、安全库存、库存状态、周转天数），数据到第 31 行左右。若当前库存(H 列)或安全库存(I 列)还是空的，先按第 10 讲补齐：H2 =E2+F2-G2，I2 =VLOOKUP(A2,商品档案!A:G,7,FALSE)。
  2. 低于安全库存整行标红 — 从 A2 开始拖选整个数据区域 A2:K31（一定从左上角 A2 开始选），点开始-条件格式-新建规则-使用公式确定要设置格式的单元格，输入 =$H2<=$I2，点格式按钮设为「浅红填充色深红色文本」，确定。所有当前库存不高于安全库存的行立刻整行变红。
  3. 零库存再加一档深红 — 保持区域选中，再新建一条公式规则 =$H2=0，格式换成深红填充、白色加粗字体。注意顺序：先设大范围（低于安全库存）、再设小范围（零库存），零库存的深红才能盖过普通红，体现「缺货最紧急」。去条件格式-管理规则确认两条规则都在。
  4. 给当前库存列加数据条 — 单独选中 H2:H31（只选这一列），点条件格式-数据条，选一个渐变填充样式。现在每行都有一根横条，库存量的大小不用读数字也能看出来。若觉得与红色预警互相干扰，可在管理规则里把数据条设为「仅显示数据条」。
  5. 应收账款账龄预警 — 切到「应收账款」表，确认有账龄天数列（如 F 列）。选中数据区域（从 A2 开始选到底），新建公式规则 =$F2>90，格式设为黄色填充。账龄超过 90 天的客户整行变黄——这些就是该发催款函的客户，第 13 讲批量对账单会用到这份名单。
  6. 出库单周末底色（选做） — 在出库单选中日期列 B2:B400，新建公式规则 =OR(WEEKDAY(B2,2)=6,WEEKDAY(B2,2)=7)，设淡紫色填充。周末的销售节奏一眼可见，也为后面画趋势图时分析「周末效应」留个抓手。
  7. 验证与清理 — 到库存台账随便把某行的当前库存数字改小到低于安全库存，整行应立刻变红、数据条同步缩短；恢复数字，红色消失。最后点条件格式-管理规则浏览本表全部规则，把不需要的用清除规则删掉。条件格式是「活」的，数据变它就变——这正是看板的价值。

在线练习

下面的表格可以直接编辑,照着演练步骤在本页练习;需要菜单操作的步骤请下载配套练习文件,在自己电脑的 Excel 中完成。

表格加载中…

验收清单

逐项自查,全部通过即完成本讲:

  • ☐ 库存台账里把某行当前库存改到低于安全库存，整行立即标红，恢复后颜色消失
  • ☐ 零库存行显示的是更醒目的深红，且两条规则在管理规则里都能看到
  • ☐ 「当前库存」列出现数据条，长短随数值变化
  • ☐ 应收账款表账龄超过 90 天的客户行已黄色高亮
  • ☐ 能说出整行标红公式里 $H2 为什么只锁列不锁行
  • ☐ 多重规则并存时，按「先大范围、后小范围」的顺序设置