Excel 条件格式完全指南:从基础规则到自定义公式

一、什么是条件格式

条件格式是 Excel 提供的一种基于数据值或公式结果自动应用单元格格式的功能。当数据满足预设条件时，Excel 会自动为单元格添加颜色填充、字体格式、边框、数据条、色阶或图标集等可视化元素。

条件格式的核心价值在于动态响应，即当源数据发生变化时，格式会自动更新，无需手动重新设置。这使得条件格式特别适用于以下场景：

• 销售报表中突出显示业绩达标或未达标的项目
• 库存表中识别低于安全库存的商品
• 项目进度表中按完成状态着色
• 财务报表中通过色阶直观展示数值分布

条件格式入口位于 开始 选项卡 → 样式 组 → 条件格式 按钮。

点击后展开的菜单包含 8 个主要功能模块：突出显示单元格规则、最前/最后规则、数据条、色阶、图标集、新建规则、清除规则、管理规则。

二、条件格式的 8 大类型

1. 突出显示单元格规则

该类型用于根据单一数值条件对单元格进行标记，适用于快速定位符合特定阈值的单元格。

支持的条件类型包括：

• 数值比较：大于、小于、介于、等于、不等于
• 文本匹配：包含、不包含、开头为、结尾为（部分结合公式）
• 日期判断：昨天、今天、明天、本周、本月等
• 重复值检测：标记重复值或唯一值

示例：在销售表中标记销售额大于 10000 的单元格为浅绿色填充深绿色文字。

操作步骤：选中数据列 → 条件格式 → 突出显示单元格规则 → 大于 → 输入 10000 → 设置格式 → 确定。

2. 最前/最后规则

该类型基于排名或百分位进行标记，适合识别数据中的极端值。

支持的条件：

• 前 10 项 / 后 10 项（数量可自定义）
• 前 10% / 后 10%
• 高于平均值 / 低于平均值

示例：在员工销售业绩表中，将前 5 名的单元格标记为绿色，后 5 名标记为红色。

需要注意的是，"前/后 N 项"是基于整个数据范围计算的，若数据中存在并列值，实际标记数量可能略多于设定值。

3. 数据条

数据条在单元格内部以条形长度反映数值大小，是一种简洁的横向对比工具。

数据条的特点：

• 长度与数值成正比
• 仅显示条形，无需额外文字说明
• 适合横向对比同一指标下的多个对象

示例：在产品销量表中，对各产品销量应用数据条，可一眼看出销量差异。

Excel 提供两种数据条类型：

• 渐变填充：
  条形末端渐变消失，视觉效果柔和
• 实心填充：
  条形边界清晰，对比更强烈

4. 色阶

色阶使用颜色渐变表示数值大小，适用于在大量数据中识别数值分布。

类型分为：

• 2 色阶：低值一种颜色，高值另一种颜色
• 3 色阶：低值、中间值、高值各对应一种颜色

常见配色方案：

• 红-黄-绿（数值越低越红，越高越绿）
• 白-红（数值越高颜色越深）
• 蓝-白-红（双向色阶，中间值为白色）

示例：在学生成绩表中应用 3 色阶（红-黄-绿），可直观看到成绩分布的热力图效果。

色阶是处理大范围数据时的理想选择，例如查看几千行销售数据的整体表现。

5. 图标集

图标集使用图标符号对数据进行分类标记，每种图标对应一个数值区间。

常用的图标集类型：

• 方向箭头：上下右 表示上升、下降、持平
• 三色旗：红黄绿三色
• 交通灯：红黄绿信号灯
• 评级符号：五角星等级
• 形状符号：三角形等几何形状

示例：在库存表中使用三色交通灯，库存充足显示绿色，安全库存显示黄色，缺货显示红色。

使用图标集时，建议设置"仅显示图标"选项，以节省单元格宽度。

6. 新建规则

新建规则是条件格式中最灵活的功能，允许通过自定义公式设置条件。

建议先在单元格编辑公式测试公式有效性，然后将公式复制到条件格式公式栏。

自定义公式条件格式的核心要点：

• 公式返回值为 TRUE 时应用格式
• 公式需根据选区的左上角单元格编写（即公式应用于左上角第一个单元格）
• 引用方式影响规则的适用范围

关键引用规则：

示例 1：整行变色

当 A 列为"周末"时，整行显示为绿色：

选中A2:B12单元格区域，然后条件格式输入下面的公式，该公式应用于选中区域左上角第一个单元格A2。

B列单元格会随着单元格相对位置发生变更，公式引用的A2也会随着变。但是A2是列锁定，所以只会在A列上下移动，左右不会移动。即B列应用条件格式，也是基于A列的日期判断的。

公式：=weekday($a2,2)>5

示例 2：基于其他单元格的判断

根据 F1 单元格中的部门名称，对该部门所有员工行着色（筛选特定值标记）：

选中A2:C11单元格区域，然后条件格式选择新建格式规则

公式：=$C2=$F$1

7. 清除规则

清除规则用于移除已应用的条件格式，提供三个清除范围：

• 清除所选单元格的规则：
  仅清除当前选中区域的规则
• 清除整个工作表的规则：
  清除当前工作表所有条件格式
• 清除此表的规则：
  清除指定表格（Table 对象）内的规则

8. 管理规则

管理规则是条件格式的集中管理界面，可以查看当前工作簿中所有的条件格式规则。

在管理规则中可以：

• 查看规则的应用范围和当前规则优先级
• 编辑规则的条件、格式、应用范围
• 调整规则的优先级顺序
• 删除不需要的规则
• 设置"如果为真则停止"选项

优先级说明：当多个规则同时应用于同一单元格时，优先级高的规则优先生效。可以通过对话框中的上下箭头按钮调整顺序。

三、5 个实战应用案例

案例 1：销售业绩排行榜

需求：在销售表中识别业绩前 10% 的员工和后 10% 的员工。

实现方案：

1. 选中销售额列
2. 应用两个条件格式：前 10% → 绿色填充；后 10% → 红色填充
3. 结合数据条显示整体分布

效果：一眼识别业绩顶尖员工和需要辅导的员工。

案例 2：项目进度甘特图

需求：在项目计划表中，根据开始日期、结束日期、当前日期显示进度条。

实现方案：

1. 选中日期范围单元格区域（E4:R11）
2. 应用数据条条件格式
3. 自定义公式：

   公式：=AND(E$3>=$C4, E$3<=$D4)

   其中 B 列为开始日期，C 列为结束日期。

效果：模拟甘特图效果，直观展示项目时间线。

案例 3：库存预警系统

需求：根据安全库存和当前库存，自动标记库存状态。

实现方案：

使用图标集（交通灯）配合自定义数值范围：库存 / 安全库存 > 1.5 视为充足（绿色），1.0 - 1.5 视为预警（黄色），小于 1.0 视为缺货（红色）。

实现步骤：

1. 添加辅助列E列计算库存/安全库存比率
2. 令F列=E列
3. 对F列应用三色图标集（不显示数值，勾选'仅显示图标'）
4. 设置图标阈值确认
5. F列图标单元格设置居中对齐

案例 4：考勤异常自动标记

需求：在考勤表中标记迟到、缺勤、旷工等异常情况。

实现方案：

使用"突出显示单元格规则"中的"文本包含"功能（分多次设置）：

• 文本包含"迟到" → 黄色
• 文本包含"缺勤"或"旷工" → 红色
• 文本包含"调休"或"请假" → 蓝色

案例 5：财务报表盈亏可视化

需求：在利润表中直观展示各项目盈亏情况。

实现方案：

1. 选中利润列
2. 应用 3 色阶：红-白-绿
3. 负数为红色，零附近为白色，正数为绿色
4. 在管理规则中调整中点值为数字 0

效果：整个表格变成热力图，盈亏分布一目了然。

四、常见问题与进阶技巧

1. 自定义公式不生效

可能原因：

• 引用方式错误，应使用相对引用还是绝对引用未明确
• 公式语法错误，例如中文标点符号
• 应用范围设置错误

解决方法：

• 公式中的基准列或行使用 $ 锁定
• 检查公式在选区左上角单元格是否返回 TRUE

2. 整行变色与逐单元格变色的区别

整行变色的关键在于锁定基准列的引用，使公式在向右填充时仍以同一列作为判断依据。

3. 条件格式与筛选功能的配合

应用条件格式后，使用筛选功能可以仅显示满足特定条件的行。例如在销售表中先应用"大于 10000"的高亮规则，再筛选"按颜色筛选"绿色单元格，可快速提取业绩达标的记录。

4. 性能优化建议

• 避免在大范围（如全列引用）应用复杂的自定义公式
• 删除不再使用的条件格式规则
• 多个简单条件可分多个规则应用，避免单个规则过于复杂

5. 与数据验证的配合使用

数据验证用于限制输入，条件格式用于标记结果。两者结合可构建完整的"输入校验 + 视觉反馈"系统。例如：数据验证限制库存数量必须 ≥ 0，条件格式当库存小于安全库存时标红。

五、总结

条件格式是 Excel 中投入产出比最高的功能之一。掌握以下要点即可应对大多数工作场景：

1. 基础场景：使用突出显示规则、最前/最后规则快速标记
2. 可视化场景：使用数据条、色阶、图标集进行视觉化呈现
3. 复杂场景：使用自定义公式实现灵活的条件判断
4. 管理维护：通过管理规则统一调整，避免规则混乱

建议结合实际工作场景练习，从简单规则开始，逐步过渡到自定义公式的应用。

附录：常用自定义公式参考

本文适用于 Microsoft Excel 2016 及以上版本，部分功能在 WPS Office 中名称可能略有差异。

示例文件已整理为模板，可在公众号后台回复“Excel”获取下载链接。

条件格式应有如有疑问，可文章下留言~

感谢各位朋友的点赞支持~