Excel 条件格式完全指南:从基础规则到自定义公式
- 2026-11-09 20:54:09
一、什么是条件格式
条件格式是 Excel 提供的一种基于数据值或公式结果自动应用单元格格式的功能。当数据满足预设条件时，Excel 会自动为单元格添加颜色填充、字体格式、边框、数据条、色阶或图标集等可视化元素。
条件格式的核心价值在于动态响应，即当源数据发生变化时，格式会自动更新，无需手动重新设置。这使得条件格式特别适用于以下场景：
销售报表中突出显示业绩达标或未达标的项目 库存表中识别低于安全库存的商品 项目进度表中按完成状态着色 财务报表中通过色阶直观展示数值分布
条件格式入口位于 开始 选项卡 → 样式 组 → 条件格式 按钮。
点击后展开的菜单包含 8 个主要功能模块：突出显示单元格规则、最前/最后规则、数据条、色阶、图标集、新建规则、清除规则、管理规则。
二、条件格式的 8 大类型
1. 突出显示单元格规则
该类型用于根据单一数值条件对单元格进行标记，适用于快速定位符合特定阈值的单元格。
支持的条件类型包括：
- 数值比较：大于、小于、介于、等于、不等于
- 文本匹配：包含、不包含、开头为、结尾为（部分结合公式）
- 日期判断：昨天、今天、明天、本周、本月等
- 重复值检测：标记重复值或唯一值
示例：在销售表中标记销售额大于 10000 的单元格为浅绿色填充深绿色文字。
操作步骤：选中数据列 → 条件格式 → 突出显示单元格规则 → 大于 → 输入 10000 → 设置格式 → 确定。
2. 最前/最后规则
该类型基于排名或百分位进行标记，适合识别数据中的极端值。
支持的条件：
前 10 项 / 后 10 项（数量可自定义） 前 10% / 后 10% 高于平均值 / 低于平均值
示例：在员工销售业绩表中，将前 5 名的单元格标记为绿色，后 5 名标记为红色。
需要注意的是，"前/后 N 项"是基于整个数据范围计算的，若数据中存在并列值，实际标记数量可能略多于设定值。
3. 数据条
数据条在单元格内部以条形长度反映数值大小，是一种简洁的横向对比工具。
数据条的特点：
长度与数值成正比 仅显示条形，无需额外文字说明 适合横向对比同一指标下的多个对象
示例：在产品销量表中，对各产品销量应用数据条，可一眼看出销量差异。
Excel 提供两种数据条类型：
- 渐变填充：
条形末端渐变消失，视觉效果柔和 - 实心填充：
条形边界清晰，对比更强烈
4. 色阶
色阶使用颜色渐变表示数值大小，适用于在大量数据中识别数值分布。
类型分为：
- 2 色阶：低值一种颜色，高值另一种颜色
- 3 色阶：低值、中间值、高值各对应一种颜色
常见配色方案：
红-黄-绿（数值越低越红，越高越绿） 白-红（数值越高颜色越深） 蓝-白-红（双向色阶，中间值为白色）
示例：在学生成绩表中应用 3 色阶（红-黄-绿），可直观看到成绩分布的热力图效果。
色阶是处理大范围数据时的理想选择，例如查看几千行销售数据的整体表现。
5. 图标集
图标集使用图标符号对数据进行分类标记，每种图标对应一个数值区间。
常用的图标集类型：
- 方向箭头：上下右 表示上升、下降、持平
- 三色旗：红黄绿三色
- 交通灯：红黄绿信号灯
- 评级符号：五角星等级
- 形状符号：三角形等几何形状
示例：在库存表中使用三色交通灯，库存充足显示绿色，安全库存显示黄色，缺货显示红色。
使用图标集时，建议设置"仅显示图标"选项，以节省单元格宽度。
6. 新建规则
新建规则是条件格式中最灵活的功能，允许通过自定义公式设置条件。
建议先在单元格编辑公式测试公式有效性，然后将公式复制到条件格式公式栏。
自定义公式条件格式的核心要点：
公式返回值为 TRUE时应用格式公式需根据选区的左上角单元格编写（即公式应用于左上角第一个单元格） 引用方式影响规则的适用范围
关键引用规则：
A1 | ||
$A1 | ||
A$1 | ||
$A$1 |
示例 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% 的员工。
实现方案：
选中销售额列 应用两个条件格式：前 10% → 绿色填充；后 10% → 红色填充 结合数据条显示整体分布
效果：一眼识别业绩顶尖员工和需要辅导的员工。
案例 2：项目进度甘特图
需求：在项目计划表中，根据开始日期、结束日期、当前日期显示进度条。
实现方案：
选中日期范围单元格区域（E4:R11） 应用数据条条件格式 自定义公式： 公式：=AND(E$3>=$C4, E$3<=$D4)
其中 B 列为开始日期，C 列为结束日期。
效果：模拟甘特图效果，直观展示项目时间线。
案例 3：库存预警系统
需求：根据安全库存和当前库存，自动标记库存状态。
实现方案：
使用图标集（交通灯）配合自定义数值范围：库存 / 安全库存 > 1.5 视为充足（绿色），1.0 - 1.5 视为预警（黄色），小于 1.0 视为缺货（红色）。
实现步骤：
添加辅助列E列计算库存/安全库存比率 令F列=E列 对F列应用三色图标集（不显示数值，勾选'仅显示图标'） 设置图标阈值确认 F列图标单元格设置居中对齐
案例 4：考勤异常自动标记
需求：在考勤表中标记迟到、缺勤、旷工等异常情况。
实现方案：
使用"突出显示单元格规则"中的"文本包含"功能（分多次设置）：
文本包含"迟到" → 黄色 文本包含"缺勤"或"旷工" → 红色 文本包含"调休"或"请假" → 蓝色
案例 5：财务报表盈亏可视化
需求：在利润表中直观展示各项目盈亏情况。
实现方案：
选中利润列 应用 3 色阶：红-白-绿 负数为红色，零附近为白色，正数为绿色 在管理规则中调整中点值为数字 0
效果：整个表格变成热力图，盈亏分布一目了然。
四、常见问题与进阶技巧
1. 自定义公式不生效
可能原因：
引用方式错误，应使用相对引用还是绝对引用未明确 公式语法错误，例如中文标点符号 应用范围设置错误
解决方法：
公式中的基准列或行使用 $锁定检查公式在选区左上角单元格是否返回 TRUE
2. 整行变色与逐单元格变色的区别
=B2>1000 | ||
=$B2>1000 |
整行变色的关键在于锁定基准列的引用，使公式在向右填充时仍以同一列作为判断依据。
3. 条件格式与筛选功能的配合
应用条件格式后，使用筛选功能可以仅显示满足特定条件的行。例如在销售表中先应用"大于 10000"的高亮规则，再筛选"按颜色筛选"绿色单元格，可快速提取业绩达标的记录。
4. 性能优化建议
避免在大范围（如全列引用）应用复杂的自定义公式 删除不再使用的条件格式规则 多个简单条件可分多个规则应用，避免单个规则过于复杂
5. 与数据验证的配合使用
数据验证用于限制输入，条件格式用于标记结果。两者结合可构建完整的"输入校验 + 视觉反馈"系统。例如：数据验证限制库存数量必须 ≥ 0，条件格式当库存小于安全库存时标红。
五、总结
条件格式是 Excel 中投入产出比最高的功能之一。掌握以下要点即可应对大多数工作场景：
- 基础场景：使用突出显示规则、最前/最后规则快速标记
- 可视化场景：使用数据条、色阶、图标集进行视觉化呈现
- 复杂场景：使用自定义公式实现灵活的条件判断
- 管理维护：通过管理规则统一调整，避免规则混乱
建议结合实际工作场景练习，从简单规则开始，逐步过渡到自定义公式的应用。
附录：常用自定义公式参考
=$A2="完成" | |
=B2>1000 | |
=ISNUMBER(SEARCH("关键词",A2)) | |
=AND(B2>=100, B2<=500) | |
=AND(条件1, 条件2) | |
=OR(条件1, 条件2) | |
=A2>Sheet2!$B$1 | |
=ISBLANK(A2) | |
=COUNTIF($A:$A, A2)>1 | |
=WEEKDAY(A2,2)>5 |
本文适用于 Microsoft Excel 2016 及以上版本，部分功能在 WPS Office 中名称可能略有差异。
示例文件已整理为模板，可在公众号后台回复“Excel”获取下载链接。
条件格式应有如有疑问，可文章下留言~
