Excel 数据分析 - 条件格式

---

---

在 Microsoft Excel 中，您可以使用条件格式进行数据可视化。您需要根据单元格区域的内容指定单元格区域的格式。满足指定条件的单元格将按照您定义的格式进行设置。

示例

在一个包含一组销售人员过去一个季度销售额的区域中，您可以突出显示那些达到定义目标（例如，2500 美元）的单元格。

您可以将条件设置为该人员的总销售额 >= 2500 美元 并指定绿色代码。Excel 检查区域中的每个单元格以确定您指定的条件（即，该人员的总销售额 >= 2500 美元）是否满足。

Excel 将您选择的格式（即绿色）应用于满足条件的所有单元格。如果单元格的内容不满足条件，则单元格的格式保持不变。结果符合预期，只有达到目标的销售人员的单元格才以绿色突出显示 - 快速可视化分析结果。

您可以通过指定规则来指定任意数量的格式条件。您可以从以下选项中选择与您的条件匹配的规则：

• 突出显示单元格规则
• 顶部/底部规则

您还可以定义自己的规则。您可以：

• 添加规则
• 清除现有规则
• 管理已定义的规则

此外，Excel 中有几种格式选项可供选择，这些选项适合您的数据可视化：

• 数据条
• 颜色刻度
• 图标集

条件格式在 Excel 2007、Excel 2010、Excel 2013 等版本中得到了改进。本章中的示例来自 Excel 2013。

在以下部分中，您将了解条件格式规则、格式选项以及如何使用规则。

突出显示单元格规则

您可以使用突出显示单元格规则为内容满足以下任何条件的单元格分配格式：

• 在给定数值范围内的数字：
  • 大于
  • 小于
  • 介于
  • 等于
• 包含给定文本字符串的文本。
• 在相对于当前日期的给定日期范围内的日期：
  • 昨天
  • 今天
  • 明天
  • 过去 7 天内
  • 上周
  • 本周
  • 下周
  • 上个月
  • 本月
  • 下个月
• 重复或唯一的值。

按照以下步骤对单元格进行条件格式设置：

• 选择要进行条件格式设置的区域。

• 单击“开始”选项卡下“样式”组中的条件格式。

• 从下拉菜单中单击突出显示单元格规则。

• 单击大于并指定 >750。选择绿色。

• 单击小于并指定 < 500。选择红色。

• 单击介于并指定 500 和 750。选择黄色。

数据将根据给定的条件和相应的格式进行突出显示。

顶部/底部规则

您可以使用顶部/底部规则为内容满足以下任何条件的单元格分配格式：

• 前 10 个项目 - 排名前 N 的单元格，其中 1 <= N <= 1000。

• 前 10% - 排名前 n% 的单元格，其中 1 <= n <= 100。

• 后 10 个项目 - 排名后 N 的单元格，其中 1 <= N <= 1000。

• 后 10% - 排名后 n% 的单元格，其中 1 <= n <= 100。

• 高于平均值 - 高于所选区域平均值的单元格。

• 低于平均值 - 低于所选区域平均值的单元格。

按照以下步骤分配顶部/底部规则。

• 选择要进行条件格式设置的区域。

• 单击“开始”选项卡下“样式”组中的条件格式。

• 从下拉菜单中单击顶部/底部规则。将显示顶部/底部规则选项。

• 单击前十个项目并指定 5。选择绿色。

• 单击后十个项目并指定 5。选择红色。

数据将根据给定的条件和相应的格式进行突出显示。

• 重复上述三个步骤。

• 单击前十个百分比并指定 5。选择绿色。

• 单击后十个百分比并指定 5。选择红色。

数据将根据给定的条件和相应的格式进行突出显示。

• 重复上述三个步骤。

• 单击高于平均值。选择绿色。

• 单击低于平均值。选择红色。

数据将根据给定的条件和相应的格式进行突出显示。

Explore our latest online courses and learn new skills at your own pace. Enroll and become a certified expert to boost your career.

数据条

您可以使用彩色的数据条来查看单元格中的值相对于其他单元格中的值的相对大小。数据条的长度代表单元格中的值。较长的条代表较高的值，较短的条代表较低的值。您可以从六种纯色中选择数据条的颜色 - 蓝色、绿色、红色、黄色、浅蓝色和紫色。

当您拥有大量数据时，数据条有助于可视化较高、较低和中间值。示例 - 特定月份各个地区的日温度。您可以使用渐变填充色条来可视化单元格中的值相对于其他单元格中的值的相对大小。您可以从六种渐变颜色中选择数据条的颜色 - 蓝色、绿色、红色、黄色、浅蓝色和紫色。

• 选择要进行条件格式设置的区域。

• 单击“开始”选项卡下“样式”组中的条件格式。

• 从下拉菜单中单击数据条。将显示渐变填充选项和填充选项。

单击渐变填充选项中的蓝色数据条。

• 重复前三个步骤。

• 单击纯色填充选项中的蓝色数据条。

您还可以格式化数据条，使数据条从单元格中间开始，对于负值向左延伸，对于正值向右延伸。

颜色刻度

您可以使用颜色刻度来查看单元格中的值相对于给定范围中其他单元格中的值的相对大小。与突出显示单元格规则一样，颜色刻度使用单元格阴影来显示单元格值差异。颜色渐变将应用于一系列单元格。颜色指示每个单元格值在该范围内的位置。

您可以从以下选项中选择：

• 三色刻度：
  • 绿色 - 黄色 - 红色颜色刻度
  • 红色 - 黄色 - 绿色颜色刻度
  • 绿色 - 白色 - 红色颜色刻度
  • 红色 - 白色 - 绿色颜色刻度
  • 蓝色 - 白色 - 红色颜色刻度
  • 红色 - 白色 - 蓝色颜色刻度
• 双色刻度：
  • 白色 - 红色颜色刻度
  • 红色 - 白色颜色刻度
  • 绿色 - 白色颜色刻度
  • 白色 - 绿色颜色刻度
  • 绿色 - 黄色颜色刻度
  • 黄色 - 绿色颜色刻度

按照以下步骤操作：

• 选择要进行条件格式设置的区域。

• 单击“开始”选项卡下“样式”组中的条件格式。

• 从下拉菜单中单击颜色刻度。将显示颜色刻度选项。

• 单击绿色 - 黄色 - 红色颜色刻度。

数据将根据所选区域中的绿色 - 黄色 - 红色颜色刻度进行突出显示。

• 重复前三个步骤。
• 单击绿色 - 白色颜色刻度。

数据将根据所选区域中的绿色 - 白色颜色刻度进行突出显示。

图标集

您可以使用图标集来可视化数值差异。可用的图标集如下：

正如您所看到的，一个图标集包含三个到五个符号。您可以定义标准，将图标与单元格区域中的每个值关联。例如，对于较小的数字使用红色向下箭头，对于较大的数字使用绿色向上箭头，对于中间值使用黄色水平箭头。

• 选择要进行条件格式设置的区域。

• 单击“开始”选项卡下“样式”组中的条件格式。

• 从下拉菜单中单击图标集。将显示图标集选项。

• 单击彩色三个箭头。

根据所选区域中的值，彩色箭头将显示在数据的旁边。

• 重复前三个步骤。将显示图标集选项。

• 选择 5 个评级。根据所选区域中的值，评级图标将显示在数据的旁边。

新建规则

您可以使用新建规则创建您自己的公式作为条件，以按照您定义的方式设置单元格格式。

有两种方法可以使用新建规则：

• 使用下拉菜单中的新建规则选项

• 使用规则管理器对话框中的新建规则按钮

使用下拉菜单中的新建规则选项

• 选择要进行条件格式设置的区域。

• 单击“开始”选项卡下“样式”组中的条件格式。

• 从下拉菜单中单击新建规则。

将显示新建格式规则对话框。

• 从“选择规则类型”框中，选择“使用公式确定要设置格式的单元格”。将显示编辑规则说明框。

• 在“设置格式值为真的公式”中键入公式。

• 单击格式按钮，然后单击确定。

包含公式为 TRUE 的值的单元格将按照定义的格式进行设置。

使用规则管理器对话框中的新建规则按钮

• 选择要进行条件格式设置的区域。

• 单击“开始”选项卡下“样式”组中的条件格式。

• 从下拉菜单中单击规则管理器。

将显示条件格式规则管理器对话框。

单击新建规则按钮。

将显示新建格式规则对话框。

重复上述步骤以定义您的公式和格式。

将显示条件格式规则管理器对话框，其中突出显示已定义的新建规则。单击应用按钮。

包含公式为 TRUE 的值的单元格将按照定义的格式进行设置。

清除规则

您可以清除规则以删除您为以下对象创建的所有条件格式：

• 选定的单元格
• 当前工作表
• 选定的表格
• 选定的数据透视表

按照以下步骤操作：

• 选择区域/点击工作表/点击需要移除条件格式规则的表格>数据透视表。

• 单击“开始”选项卡下“样式”组中的条件格式。

• 点击下拉菜单中的清除规则。将显示“清除规则”选项。

选择合适的选项。条件格式将从区域/工作表/表格/数据透视表中清除。

管理规则

您可以通过条件格式规则管理器窗口管理规则。您可以查看当前选定区域、当前工作表整体、工作簿中的其他工作表或工作簿中的表格或数据透视表的格式规则。

• 单击“开始”选项卡下“样式”组中的条件格式。

• 从下拉菜单中单击规则管理器。

将显示条件格式规则管理器对话框。

点击显示格式规则旁边的列表框中的箭头。如果存在带有条件格式规则的“当前选定区域”、“此工作表”和其他工作表、表格、数据透视表，则会显示。

从下拉列表中选择此工作表。当前工作表上的格式规则将按应用顺序显示。您可以使用向上和向下箭头更改此顺序。

您可以添加新规则、编辑规则和删除规则。

• 您已在前面部分看到新建规则。您可以选择规则并点击删除规则来删除规则。高亮的规则将被删除。

• 要编辑规则，请选择规则并点击编辑规则。“编辑格式规则”对话框将出现。

• 您可以

  • 选择规则类型

  • 编辑规则描述

  • 编辑格式

• 完成后，点击确定。

• 规则的更改将反映在条件格式规则管理器对话框中。点击应用。

• 数据将根据修改后的条件格式规则进行高亮显示。