Excel数据分析-条件格式

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

例子

在包含一组销售人员上一季度的销售数据的范围中，您可以突出显示那些代表已达到定义目标（例如 2500 美元）的单元格。

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

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

您可以通过指定Rules来指定任意数量的格式化条件。您可以从以下位置选择符合您条件的规则

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

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

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

此外，Excel 中有多个格式选项可供您选择适合您的数据可视化的选项 -

数据栏
色阶
图标集

条件格式已在 Excel 2007、Excel 2010、Excel 2013 版本中得到提升。本章中的示例来自 Excel 2013。

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

突出显示单元格规则

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

给定数值范围内的数字 -
- 比...更棒
- 少于
- 之间
- 等于
包含给定文本字符串的文本。
相对于当前日期的给定日期范围内发生的日期 -
- 昨天
- 今天
- 明天
- 在过去 7 天内
- 上星期
- 本星期
- 下周
- 上个月
- 这个月
- 下个月
重复或唯一的值。

按照以下步骤有条件地设置单元格格式 -

选择要设置条件格式的范围。
单击“开始”选项卡下“样式”组中的“条件格式”。
从下拉菜单中单击突出显示单元格规则。

单击大于并指定 >750。选择绿色。
单击小于并指定 < 500。选择红色。
单击“介于”并指定 500 和 750。选择黄色。

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

顶部/底部规则

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

Top 10 items - 排名前 N 的单元格，其中 1 <= N <= 1000。
Top 10% - 排名前 n% 的单元格，其中 1 <= n <= 100。
Bottom 10 items - 排名最后 N 的单元格，其中 1 <= N <= 1000。
Bottom 10% - 排名最后 n% 的单元格，其中 1 <= n <= 100。
高于平均水平- 选定范围内高于平均水平的单元格。
低于平均水平- 所选范围内低于平均水平的单元格。

按照下面给出的步骤分配顶部/底部规则。

选择要设置条件格式的范围。
单击“开始”选项卡下“样式”组中的“条件格式”。
从下拉菜单中单击顶部/底部规则。出现顶部/底部规则选项。

单击前十项并指定 5. 选择绿色。
单击“底部十个项目”并指定 5。选择红色。

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

重复上面给出的前三个步骤。
单击前 10%并指定 5。选择绿色。
单击“底部 10%”并指定 5。选择红色。

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

重复上面给出的前三个步骤。
单击高于平均水平。选择绿色。
单击低于平均水平。选择红色。

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

数据栏

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

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

选择要有条件格式化的范围。
单击“开始”选项卡下“样式”组中的“条件格式”。
从下拉菜单中单击数据栏。将出现“渐变填充”选项和“填充”选项。

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

重复前三个步骤。
单击“实体填充”选项中的蓝色数据栏。

您还可以设置数据栏的格式，使数据栏从单元格的中间开始，并在负值时向左延伸，在正值时向右延伸。

色阶

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

您可以选择 -

三色阶 -
- 绿色 – 黄色 – 红色色阶
- 红-黄-绿色阶
- 绿-白-红色阶
- 红-白-绿色阶
- 蓝-白-红色阶
- 红-白-蓝色阶
双色标尺 -
- 白-红色阶
- 红-白色阶
- 绿-白色阶
- 白-绿色阶
- 绿色-黄色色阶
- 黄-绿色标

请按照以下步骤操作 -

选择要设置条件格式的范围。
单击“开始”选项卡下“样式”组中的“条件格式”。
从下拉菜单中单击色阶。出现色阶选项。
单击绿色 - 黄色 - 红色色阶。

数据将根据所选范围内的绿色 - 黄色 - 红色色阶突出显示。

重复前三个步骤。
单击绿色 - 白色色标。

数据将根据所选范围内的绿色 - 白色色阶突出显示。

图标集

您可以使用图标集来可视化数字差异。以下图标集可用 -

正如您所观察到的，图标集由三到五个符号组成。您可以定义条件将图标与单元格区域中的每个值关联。例如，红色向下箭头表示小数字，绿色向上箭头表示大数字，黄色水平箭头表示中间值。

选择要设置条件格式的范围。
单击“开始”选项卡下“样式”组中的“条件格式”。
从下拉菜单中单击图标集。将出现图标集选项。
单击彩色三个箭头。

基于所选范围内的值，彩色箭头出现在数据旁边。

重复前三个步骤。将出现图标集选项。
选择 5 个评级。评级图标显示在基于所选范围内的值的数据旁边。

新规则

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

有两种使用新规则的方法 -

使用下拉菜单中的“新规则”选项
使用“管理规则”对话框中的“新建规则”按钮

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

选择要设置条件格式的范围。
单击“开始”选项卡下“样式”组中的“条件格式”。
从下拉菜单中单击“新建规则” 。

将出现“新格式规则”对话框。

从“选择规则类型”框中，选择“使用公式确定要设置格式的单元格”。将出现编辑规则描述框。
在该公式成立的格式值中：键入公式。
单击格式按钮，然后单击确定。

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

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

选择要设置条件格式的范围。
单击“开始”选项卡下“样式”组中的“条件格式”。
从下拉菜单中单击管理规则。

将出现“条件格式规则管理器”对话框。

单击新建规则按钮。

将出现“新格式规则”对话框。

重复上面给出的步骤来定义您的公式和格式。

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

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

明确的规则

您可以清除规则来删除您为其创建的所有条件格式

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

按照给定的步骤操作 -

选择范围/单击工作表/单击表格 > 需要删除条件格式规则的数据透视表。
单击“开始”选项卡下“样式”组中的“条件格式”。
从下拉菜单中单击清除规则。将出现“清除规则”选项。

选择适当的选项。条件格式已从范围/工作表/表/数据透视表中清除。

管理规则

您可以从条件格式规则管理器窗口管理规则。您可以查看当前选择、整个当前工作表、工作簿中的其他工作表或工作簿中的表或数据透视表的格式设置规则。

单击“开始”选项卡下“样式”组中的“条件格式”。
从下拉菜单中单击管理规则。

将出现“条件格式规则管理器”对话框。

单击“显示当前选择的格式设置规则”旁边的列表框中的箭头，此工作表和其他工作表、表格、数据透视表（如果存在条件格式规则）将出现。

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

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

您已经在前面的部分中看到了新规则。您可以通过选择规则并单击删除规则来删除规则。突出显示的规则被删除。
要编辑规则，请选择规则并单击编辑规则。将出现编辑格式规则对话框。
你可以
- 选择规则类型
- 编辑规则说明
- 编辑格式
完成更改后，单击“确定”。
规则的更改将反映在“条件格式规则管理器”对话框中。单击“应用”。
数据将根据修改后的条件格式规则突出显示。