书籍 / 用 AI 搞定 Excel：从公式到自动化
第 9 章

条件格式——让表格自己变色报警

第9章：条件格式——让数据自动说话

你有没有盯过一张几百行的数据表，试图用肉眼找出哪些数值偏高、哪些任务要超时、哪些库存告急？条件格式就是把这件事交给 Excel 自动完成的工具。它的核心逻辑很简单：当某个条件成立时，自动给单元格变色/加粗/加图标——让数据自己"说话"，而不需要人工盯屏幕。

条件格式的核心价值

一张经过精心设计的条件格式表，能在10秒内让任何人看出数据的关键信息，而不需要阅读每一个数字。这就是条件格式最核心的价值：把数字转化成视觉信号。

三个典型应用场景：

ℹ️ Note

**入口：**选中单元格区域 → 开始（Home）选项卡 → 条件格式（Conditional Formatting）。基本操作：新建规则（New Rule）、管理规则（Manage Rules）、清除规则（Clear Rules）。

内置规则：快速上手的五个场景

Excel 内置了多种开箱即用的条件格式类型，无需写公式，几步就能设置好。

案例1：销售额高于平均值标绿（突出显示单元格规则）

场景：月度销售数据，希望直观看出哪些销售员高于平均水平

1
选中销售额列（如 C2:C50）


2
条件格式 → 突出显示单元格规则 → 大于（Greater Than）


3
值填入：=AVERAGE($C$2:$C$50)，格式选"绿色填充深绿色文本"

关键：值框里可以直接填函数公式，不必是固定数字。
    =AVERAGE($C$2:$C$50) 会动态计算当前列的平均值，数据更新后高亮范围自动调整。
    同理可设第二条规则：小于 AVERAGE 的标红——用"管理规则"叠加两条规则。

案例2：库存量数据条（直观看多少）

场景：产品库存表，不想只看数字，希望直观看到各产品库存的相对多少

1
选中库存数量列


2
条件格式 → 数据条（Data Bars）→ 选择颜色样式

效果：每个单元格里显示一个与数值成比例的横向进度条，库存越多条越长。
    数字本身依然可见，数据条是叠加在单元格背景上的。
    进阶设置：右键数据条规则 → 编辑规则 → 可以设置最小值/最大值的基准（如把最大值固定为1000，让超过1000的也显示满格）。

案例3：温度计色阶（完成率从红到绿）

场景：项目完成率列，0% 显示红色，50% 显示黄色，100% 显示绿色，中间平滑过渡

1
选中完成率列（数据是 0-1 或 0%-100% 的小数）


2
条件格式 → 色阶（Color Scales）→ 选"红-黄-绿"三色阶

效果：自动根据数值在范围内的相对位置赋予颜色，最小值红色、中间值黄色、最大值绿色。
    自定义设置：编辑规则可以把最小值固定为0、中间值固定为0.5、最大值固定为1，而不是用列内的相对最大最小值——这样即使当前最大完成率只有70%，也不会显示成绿色。

案例4：箭头图标集（同比上升/下降/持平）

场景：月度报表，同比增长率列，用绿色向上箭头/红色向下箭头/黄色横箭头直观表示趋势

1
选中同比增长率列（正数表示增长，负数表示下降）


2
条件格式 → 图标集（Icon Sets）→ 方向类 → 3个箭头（彩色）


3
编辑规则：绿色箭头 > 0（大于0），黄色箭头 = 0（等于0），红色箭头

自定义图标分配逻辑：
    绿色向上箭头：值 > 0（增长）
    黄色横箭头：值 = 0（持平）
    红色向下箭头：值
    可选：勾选"仅显示图标"，隐藏数字只显示箭头——适合纯视觉汇报场景。

案例5：AI 帮你选择最合适的条件格式类型

我有一张 Excel 销售报表，包含以下几列：
- A列：销售员姓名
- B列：本月销售额（万元，范围大概 5-80 万）
- C列：完成率（0%-150%，100% 为达标）
- D列：同比增长率（-50% 到 +100%）
- E列：排名（1-20名）

我想让这张表在汇报时让老板一眼就看出重点。请推荐每列最适合的条件格式类型，说明原因，并给出具体的设置参数。

自定义公式条件格式：最强大的用法

内置规则能解决 70% 的场景，但最灵活、最强大的是使用公式来指定条件。几乎任何能用公式表达的逻辑，都可以转化成条件格式规则。

工作原理：公式条件格式的关键概念

使用公式条件格式时，最重要的是理解应用区域和公式中的引用之间的关系。

⚠️ Warning

**核心规则：**公式是针对"应用区域的左上角第一个单元格"写的，然后 Excel 自动将公式扩展到整个区域。因此公式里要区分绝对引用（固定）和相对引用（随行列移动）。

例：应用区域 A2:F50，公式 =$C2>100，表示：C列（绝对，$C固定列）当前行（相对，2会随行变化）大于100时，整行高亮。

案例1：隔行变色（让大表格更易读）

场景：长数据表，希望奇数行和偶数行交替显示不同底色，方便阅读

1
选中整个数据区域（如 A2:H100）


2
条件格式 → 新建规则 → 使用公式确定要设置格式的单元格


3
公式框填入：=MOD(ROW(),2)=0，设置背景色（如浅灰）

公式解析
    =MOD(ROW(),2)=0
    ROW() 返回当前行号
    MOD(ROW(),2) 返回行号除以2的余数
    =0 表示偶数行（余数为0）
    效果：第2、4、6... 行填充背景色，第1、3、5... 行不填充，形成斑马纹效果
    如果想让奇数行变色：=MOD(ROW(),2)=1

案例2：整行高亮（当某列满足条件时整行变色）

场景：销售表，当完成率（C列）低于 80% 时，整行高亮为浅红色 这是公式条件格式最常见且最实用的用法——用一列的值控制整行的格式。

1
选中整个数据区域（如 A2:F50），注意是选整行，不只是 C 列


2
条件格式 → 新建规则 → 使用公式


3
公式填入：=$C2

关键：绝对列 + 相对行
    =$C2
    $C：列锁定（无论格式应用到哪一列，都看C列）
    2：行不锁定（每行看自己那行的C列）
    效果：第2行看C2，第3行看C3……每行都用自己的完成率判断，满足条件则整行变红。
    常见错误：写成 $C$2 则所有行都只看C2，只有第2行能触发条件。

案例3：到期预警（距今天小于7天变红）

场景：任务管理表，截止日期（E列）距今天不足7天的任务，整行变黄色预警

公式（应用于整行数据区域 A2:G100）
    =AND($E2>=TODAY(), $E2
    AND：同时满足两个条件
    $E2>=TODAY()：截止日期还未过（不是已过期的任务）
    $E2
    可叠加第二条规则：=$E2
    效果：即将到期的任务显示黄色，已逾期任务显示红色，形成双重预警体系。

案例4：重复值高亮

场景：订单表中找出重复的订单编号（A列）

公式（应用于 A2:A200）
    =COUNTIF($A$2:$A$200,A2)>1
    原理：COUNTIF 统计当前值在整列出现的次数，大于1说明有重复。
    注意：引用整列用绝对引用 $A$2:$A$200，当前单元格用相对引用 A2。
    效果：所有重复出现的订单编号都会被高亮，包括原始行和重复行。

ℹ️ Note

**与内置重复值规则的区别：**开始 → 条件格式 → 突出显示单元格规则 → 重复值，同样能高亮重复，但只作用于选中区域，且无法自定义复杂逻辑。自定义公式方式更灵活，可以跨列比较、自定义统计范围。

案例5：最大值/最小值标注

场景：销售额列（C2:C50），最高值标金色，最低值标红色

最大值公式（应用于 C2:C50）
    =C2=MAX($C$2:$C$50)
    最小值公式
    =C2=MIN($C$2:$C$50)
    原理：比较当前单元格是否等于整列的最大/最小值，是则触发格式。
    注意：MAX/MIN 的范围用绝对引用，C2（比较对象）用相对引用。
    效果：最高销售额自动高亮金色，最低销售额标红色，数据更新后自动跟进。

案例6：AI 帮你写复杂条件公式

我有一张 Excel 项目跟踪表，列结构如下：
A列：项目名称
B列：负责人
C列：状态（文字：进行中/已完成/已暂停/未开始）
D列：计划完成日期（日期格式）
E列：实际完成日期（日期格式，未完成的为空）
F列：优先级（高/中/低）

我需要以下几个条件格式规则（应用于整行 A2:F100）：
1. 状态是"进行中"且计划完成日期已过（逾期）：整行标深红色
2. 状态是"进行中"且距计划完成日期不足3天：整行标橙色
3. 状态是"已完成"：整行字体变灰色（表示已归档）
4. 优先级是"高"且状态是"未开始"：整行标黄色（提醒尽快启动）

请给我每条规则对应的公式，以及设置时需要注意的绝对/相对引用说明。

多条件叠加与优先级：条件格式规则管理器

当一个区域有多个条件格式规则时，可能出现冲突——比如同一行同时满足"进行中且逾期（深红）"和"优先级高（黄色）"两个规则，Excel 应该显示哪个颜色？

答案由规则优先级决定。排在列表顶部的规则优先级最高。

访问规则管理器

条件格式 → 管理规则（Manage Rules）。在这里可以：

⚠️ Warning

**常见误区：**不勾选"如果为真则停止"时，多个规则可能叠加效果（如同时应用字体颜色和背景颜色）。通常建议：互斥的状态规则都勾选"如果为真则停止"，叠加的格式效果（如背景色+加粗）则不勾选。

条件格式的性能问题

条件格式在数据量大时可能导致 Excel 卡顿。几个常见的性能杀手和对应的优化建议：

性能问题的常见原因

优化策略

问题 优化方法
应用范围是整列 改为实际数据范围，如 A2:A10000
使用了 VLOOKUP 改为辅助列先计算结果，条件格式只引用辅助列
规则太多 合并逻辑，用 AND/OR 组合多个条件为一条规则
大数据量下仍然卡顿 考虑用 VBA 宏代替条件格式，手动触发而不是实时计算
文件频繁变化 设置手动计算模式（公式 → 计算选项 → 手动），完成编辑后再按 F9 刷新

ℹ️ Note

**经验法则：**5000行以下的数据，条件格式性能通常不是问题。超过 20000 行且规则复杂，就要开始考虑优化策略。

综合案例：项目进度看板（用条件格式实现红绿灯状态）

将本章学到的所有技术综合运用，制作一个视觉清晰的项目进度看板。

表格结构设计

A列 B列 C列 D列 E列 F列
项目名称 负责人 状态 计划完成日 完成率 优先级

条件格式规则设计（应用于 A2:F50）

1

  **红灯——逾期未完成**

  公式：=AND($C2<>"已完成",$D2


2

  **橙灯——7天内到期**

  公式：=AND($C2="进行中",$D2>=TODAY(),$D2


3

  **绿灯——已完成**

  公式：=$C2="已完成"

  格式：字体变灰色，背景浅绿



4

  **完成率低预警**（仅 E 列）：=E2

另外，给E列（完成率）单独设置绿-黄-红三色色阶（0%=红，50%=黄，100%=绿），让完成进度可视化。

最终效果：打开看板，红色行需要立即处理，橙色行需要重点关注，灰色行已归档不打扰，绿色完成率直观展示进度——整个项目状态一眼全收。

AI 设计条件格式方案

我需要为一张销售团队月报 Excel 设计条件格式方案，数据结构如下：
A：销售员姓名
B：本月销售额（万元）
C：月度目标（万元）
D：完成率（B/C，百分比格式）
E：同比增长（百分比，有正有负）
F：客户数量
G：本月排名（1-15名）

设计目标：
1. 让看报表的管理者1分钟内找出需要关注的人
2. 突出表现优秀者，不刺激表现欠佳者（用色彩要含蓄）
3. 整体视觉简洁，不要花里胡哨

请给出：
1. 每个指标建议用什么类型的条件格式（内置规则 or 自定义公式）
2. 具体的公式和格式参数（颜色、字体等）
3. 规则优先级顺序
4. 性能注意事项（数据约500行）

上一章 ← 第8章：数据验证与智能下拉菜单 下一章 第10章：Power Query 数据清洗 →

本章评分
4.7  / 5  (35 评分)

💬 留言讨论