Excel条件格式完全指南：从基础到高级公式的视觉化数据分析

Excel条件格式完全指南：从基础到高级公式的视觉化数据分析

🎨 第一章：条件格式基础概念

1.1 什么是条件格式？

条件格式 是Excel中根据预设规则自动应用单元格格式的功能。它能够：

• 根据数值、文本、日期等条件自动改变单元格外观

• 提供即时数据可视化反馈

• 突出显示关键信息，提高数据可读性

• 支持多种格式：颜色填充、字体样式、数据条、色阶、图标集

1.2 条件格式的核心价值

四大应用优势：

1. 数据洞察：快速识别数据模式和异常

2. 决策支持：直观展示数据趋势和比较

3. 报告美化：创建专业的数据可视化报告

4. 效率提升：自动化格式设置，减少人工操作

🔢 第二章：比较条件格式实战

2.1 基本比较运算符

比较运算符对照表：

大于：> 示例：>7000

大于等于：>= 示例：>=6000

小于：< 示例：<6000

小于等于：<= 示例：<=7000

不等于：<> 示例：<>6000

介于：指定范围 示例：6000-7000

2.2 数值比较案例详解

案例1：工资大于7000的记录

操作步骤：

1. 选中工资列（B3:B6）

2. 开始 → 条件格式 → 突出显示单元格规则 → 大于

3. 输入：7000

4. 选择格式：浅红色填充深红色文本

5. 确定

结果：白冰夏的7250被高亮显示

案例2：工资在6000-7000之间

操作步骤：

1. 选中工资列

2. 条件格式 → 突出显示单元格规则 → 介于

3. 输入：6000 到 7000

4. 选择格式

5. 确定

结果：樊成(6500)、易南(6000)、颜云(6000)被标记

注意：包含边界值6000和7000

2.3 两列数据比较

案例：找出不同部门名称

操作步骤：

1. 选中C14:C20区域

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

3. 输入公式：=B14<>C14

4. 设置格式：绿色填充

5. 确定 结果：财务部≠财务科、人事部≠人事科被标记

视频演示：

条件格式之突出显示单元格规则（excel技巧）

⚙️ 第三章：规则管理深度解析

3.1 规则管理界面详解

访问路径：

开始 → 条件格式 → 管理规则

开始 → 条件格式 → 清除规则 → 管理规则

规则管理器功能：

四个核心功能：

1. 新建规则：创建新的条件格式

2. 编辑规则：修改现有规则设置

3. 删除规则：移除不需要的规则

4. 规则顺序：调整规则优先级

3.2 规则优先级案例：成绩分段标记

原始数据与需求：

错误做法：同时设置四个规则

设置规则：

规则1：>=0 → 红底

规则2：>=60 → 橙底

规则3：>=80 → 蓝底

规则4：>=90 → 绿底

问题：只显示规则1的效果

原因：默认按从上到下顺序执行，>=0条件对所有数据都为真

正确设置：调整规则顺序

调整后的规则顺序：

1. =90 → 绿底

2. =80 → 蓝底

3. =60 → 橙底

4. =0 → 红底

执行逻辑：

• 先检查是否>=90，是则应用绿底，停止检查

• 否则检查是否>=80，是则应用蓝底，停止检查

• 否则检查是否>=60，是则应用橙底，停止检查

• 否则应用红底

详细操作步骤：

步骤1：创建第一条规则（最高优先级）

1. 选中成绩列

2. 新建规则 → 基于各自值设置所有单元格的格式

3. 格式样式：双色刻度（绿-白）

4. 最小值：90，最大值：100

5. 确定

步骤2：创建后续规则（优先级递减）

重复以上步骤，分别设置：

• 规则2：80-89，蓝底

• 规则3：60-79，橙底

• 规则4：0-59，红底

步骤3：调整规则顺序

1. 管理规则

2. 将>=90规则移到最顶部

3. 依次调整其他规则顺序

4. 勾选所有规则的"如果为真则停止"

5. 确定

视频演示：

条件格式之规则管理（规则顺序与优先级设置）

🔤 第四章：文本条件格式应用

4.1 文本匹配规则

五种文本条件：

包含：包含指定文本

示例：包含"文" → 文浩、仲孙文耀

不包含：不包含指定文本

示例：不包含"文" → 所有不包含"文"的名字

始于：以指定文本开头

示例：始于"李" → 李奇、李凝军

止于：以指定文本结尾

示例：止于"军" → 东军玉、左凝军

通配符：使用?和*进行模式匹配

4.2 文本条件案例详解

案例1：包含特定文字

操作：

1. 选中姓名列

2. 条件格式 → 突出显示单元格规则 → 文本包含

3. 输入：文

4. 设置格式

5. 确定

结果：文浩、仲孙文耀被标记

案例2：姓名正好三个字

技术难点：

使用???会匹配三个及以上字符

需要使用公式条件

正确方法：

步骤1：设置长度>=3的规则

公式：=LEN(A2)>=3

格式：橙色填充

不勾选"如果为真则停止"

步骤2：设置长度>3的规则（更高优先级）

公式：=LEN(A2)>3

格式：无格式（或与其他规则不同的格式）

必须勾选"如果为真则停止"

结果：正好三个字的姓名显示橙色，其他不显示

视频演示：

条件格式之文本条件（excel技巧）

📅 第五章：日期与特殊值条件格式

5.1 日期条件格式应用

常用日期条件：

1. 标记2025年之前的记录

规则：发生日期 → 之前 → 2025/1/1

2. 标记2025年的记录

规则：发生日期 → 介于 → 2025/1/1 到 2025/12/31

3. 最近3天的记录

规则：发生日期 → 最近3天

4. 去年的记录

规则：发生日期 → 去年

5. 本年度到现在

规则：发生日期 → 本年

日期格式处理技巧：

操作：

1. 选中数据区域

2. 条件格式 → 新建规则

3. 选择"只为包含以下内容的单元格设置格式"

4. 单元格值 → 小于、介于等

5. 设置格式：红色填充

6. 确定

5.2 错误值与空值标记

标记错误值：

操作：

1. 选中数据区域

2. 条件格式 → 新建规则

3. 选择"只为包含以下内容的单元格设置格式"

4. 单元格值 → 错误

5. 设置格式：红色填充

6. 确定

结果：所有#DIV/0!、#N/A等错误值被标记

标记空值：

操作：

1. 选中数据区域

2. 条件格式 → 新建规则

3. 选择"只为包含以下内容的单元格设置格式"

4. 单元格值 → 空值

5. 设置格式：灰色填充

6. 确定

视频演示：

条件格式之日期、空值、错误值（EXCEL技巧）

🏆 第六章：排名与平均值条件格式

6.1 排名相关条件

四种排名条件：

前N项：前N个最大值

示例：前三名 → 标记最高的3个分数

后N项：后N个最小值

示例：后三名 → 标记最低的3个分数

前N%：前百分之N

示例：前20% → 标记最高的20%分数

后N%：后百分之N

示例：后20% → 标记最低的20%分数

案例：标记前三名和后三名

操作步骤：

1. 选中分数列

2. 条件格式 → 最前/最后规则 → 前10项

3. 修改为"前3项"，设置格式

4. 确定

5. 再次条件格式 → 最前/最后规则 → 后10项

6. 修改为"后3项"，设置不同格式

7. 确定

结果：前三名和后三名用不同颜色标记

6.2 平均值条件格式

案例：标记总分高于平均值的记录

操作：

1. 选中总分列

2. 条件格式 → 最前/最后规则 → 高于平均值

3. 选择格式：绿色填充

4. 确定 结果：高于平均总分的记录被标记

视频演示：

条件格式之排名与平均值（excel技巧）

📊 第七章：数据条高级应用

7.1 基础数据条设置

预置数据条：

操作：

1. 选中数值区域

2. 条件格式 → 数据条

3. 选择预设样式（渐变/实心）

4. 自动根据数值大小显示数据条

自定义数据条：

操作：

1. 条件格式 → 数据条 → 其他规则

2. 设置：

• 最小值/最大值类型

• 条形图方向

• 条形图外观

3. 确定

7.2 进阶技巧：负值数据条处理

案例：低于60分显示为红色

原始数据：

姓名 分数 图形

李达 66

钟永风 97

薄水 52 ← 低于60

...（共8人）

技术难点：数据条无法直接显示负值颜色

解决方案：

步骤1：创建辅助列

在C列输入公式：=B2-60

结果：分数变为-60到40范围

步骤2：设置数据条

1. 选中辅助列

2. 数据条 → 其他规则

3. 设置负值格式：

• 负值和坐标轴：单元格中点值

• 负条形图填充颜色：红色

• 坐标轴位置：自动

4. 确定

效果：低于60分显示红色数据条

7.3 动态数据条：光标定位控制

案例：只显示光标所在月份的数据条

需求：光标定位到哪一列，只显示该列数据条

设置方法：

规则1：隐藏非当前列数据条（最高优先级）

公式：=CELL("col")<>COLUMN()

格式：无格式（或白色填充）

勾选"如果为真则停止"

规则2：显示数据条（第二优先级）

设置正常的数据条格式

操作：

1. 按F9刷新单元格

2. 光标移动到某列，该列显示数据条

3. 其他列数据条被隐藏

视频演示：

光标定位的月份设置数据条（条件格式之数据条）

🌈 第八章：色阶条件格式

8.1 基础色阶设置

双色刻度和三色刻度：

双色刻度：两个颜色之间的渐变

示例：黄-绿，数值低→高对应颜色变化

三色刻度：三个颜色之间的渐变

示例：红-黄-绿，低中高对应不同颜色

案例：80-100分显示双色刻度

需求：80-100分显示黄到绿渐变，低于80分不显示颜色

设置方法：

规则1：低于80分无格式（最高优先级）

公式：=B4<80

格式：无格式

勾选"如果为真则停止 "

规则2：80-100分双色刻度（第二优先级）

双色刻度：黄（80）→ 绿（100）

结果：>=80分的单元格显示颜色渐变

8.2 动态行色阶：光标定位控制

案例：光标所在行显示各科成绩色阶

设置方法：

规则1：隐藏非当前行色阶（最高优先级）

公式：=CELL("row")<>ROW()

格式：无格式

勾选"如果为真则停止"

规则2：双色刻度（第二优先级）

设置正常的双色刻度格式

操作：

1. 按F9刷新

2. 光标移动到某行，该行显示色阶

3. 其他行色阶被隐藏

视频演示：

用颜色标记所在行各科成绩（色阶条件格式：双色刻度）

🎯 第九章：图标集条件格式

9.1 基础图标集应用

常用图标类型：

方向箭头：↑ → ↓（表示升降趋势）

形状标志：● ◆ ▲（表示不同等级）

信号标志：🚦 📶（表示信号强度）

等级标志：⭐ ⭐⭐ ⭐⭐⭐（表示评分等级）

9.2 动态列图标集：光标定位控制

案例：光标所在列显示图标集

设置方法（与数据条类似）：

规则1：隐藏非当前列图标集（最高优先级）

公式：=CELL("col")<>COLUMN()

格式：无格式

勾选"如果为真则停止"

规则2：图标集规则（第二优先级）

设置图标集条件：

• =90：绿点

• =80：橙点

• =60：红点

• <60：黑点

操作：按F9刷新，光标列显示图标

视频演示：

条件格式图标集基础篇（excel技巧）

9.3 图标集实际应用案例

案例1：比较月度业绩升降

设置：

1. 选中2月业绩列

2. 条件格式 → 图标集 → 三向箭头（彩色）

3. 编辑规则：

• 类型：数字

• 值：=B2（对应1月业绩）

• 图标：>对应值：绿箭头，=对应值：黄箭头，<对应值：红箭头

4. 确定 结果：箭头显示2月相对1月的升降状态

案例2：影片等级 可视化

设置：

1. 选中等级列

2. 条件格式 → 图标集 → 等级（3个星）

3. 编辑规则：

• =4：三颗星

• =3：两颗星

• =2：一颗星

• <2：无图标

4. 确定 结果：影片等级用星数直观显示

视频演示：

条件格式图标集进阶篇

⚡ 第十章：公式条件格式深度应用

10.1 公式中的引用类型

三种引用方式：

相对引用：A1

• 规则随位置变化

• 适合行或列独立的条件

绝对引用：A1

• 规则固定不变

• 适合基于固定单元格的条件

混合引用：A1或A1

• 行变列不变或列变行不变

• 适合复杂条件判断

10.2 公式条件实战案例

案例1：标记总分>=160的整行

公式设置：

1. 选中数据区域（A3:D8）

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

3. 输入公式：=\$D3>=160

• \$D：锁定列，行相对

• 3：相对行号，随行变化

4. 设置格式：绿色填充

5. 确定 结果：总分>=160的整行标记为绿色

案例2：标记部门包含"销售"的整行

公式设置：

1. 选中数据区域（A13:C24）

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

3. 输入公式：=IFERROR(FIND("销售",\$B13),0)

或简化：=ISNUMBER(FIND("销售",\$B13))

• FIND查找"销售"在B列的位置 - 找到返回位置（数字），找不到返回错误

• IFERROR处理错误情况

• \$B：锁定部门列

4. 设置格式：蓝色填充

5. 确定 结果：部门包含"销售"的整行被标记

视频演示：

标记出部门中包含销售关键字的整行（公式条件格式）

10.3 公式条件高级技巧

多条件组合公式：

AND函数：多个条件同时满足

示例：=AND(C2>=80,C2<=90)

含义：C列值在80-90之间

OR函数：多个条件满足其一

示例：=OR(B2="销售部",B2="市场部")

含义：部门是销售部或市场部

NOT函数：条件不满足

示例：=NOT(ISBLANK(\$A2))