Excel Data Analysis 简明教程

Excel Data Analysis - Conditional Formatting

在 Microsoft Excel 中，您可以使用 Conditional Formatting 进行数据可视化。您必须根据单元格区域的内容为单元格区域指定格式。符合指定条件的单元格将按照您定义的方式进行格式化。

Example

在包含过去一个季度一系列销售人员销售数据的一个区域中，您可以突出显示代表满足已定义目标（例如 $2500）单元格。

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

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

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

  1. Highlight cells rules

  2. Top / Bottom rules

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

  1. Add a rule

  2. Clear an existing rule

  3. Manage the defined rules

此外，您可以在 Excel 中选择多个格式化选项以选择适合您的数据可视化的选项 −

  1. Data Bars

  2. Color Scales

  3. Icon Sets

条件格式已在 Excel 2007、Excel 2010、Excel 2013 版本中得到推广。您在本章中找到的示例来自 Excel 2013。

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

Highlight Cells Rules

您可以使用 Highlight Cells 规则将格式分配给内容符合以下任意标准的单元格 -

  1. 给定数值范围内的数字 - 大于小于介于等于

  2. 包含给定文本字符串的文本。

  3. 发生在相对于当前日期的给定日期范围内的日期 - 昨天，今天，明天，在过去 7 天内，上周，本周，下周，上个月，本月，下个月

  4. 重复或唯一的数值。

按照步骤条件格式化单元格：

  1. 选择要进行条件格式化的范围。

  2. 单击 Conditional Formatting 选项卡下 Styles 组中的 Home 。

  3. 从下拉菜单中单击 Highlight Cells Rules 。

highlight cells rules

  1. 单击 Greater Than ，并指定 >750。选择绿色。

  2. 单击 Less Than ，并指定 < 500。选择红色。

  3. 单击 Between ，并指定 500 和 750。选择黄色。

rules

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

data highlighted

Top / Bottom Rules

您可以使用 Top / Bottom Rules 将格式分配给内容符合以下任意标准的单元格 -

  1. Top 10 items - 排名在前 N 位的单元格，其中 1 ⇐ N ⇐ 1000。

  2. Top 10% − 排名在 n% 以内的单元格（其中 1 ⇐ n ⇐ 100）。

  3. Bottom 10 items - 排名在后 N 位的单元格，其中 1 ⇐ N ⇐ 1000。

  4. Bottom 10% − 排名在 n% 以外的单元格（其中 1 ⇐ n ⇐ 100）。

  5. Above average - 高于所选范围平均值的单元格。

  6. Below average − 低于所选范围平均值的单元格。

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

  1. 选择要进行条件格式化的范围。

  2. 单击 Conditional Formatting 选项卡下 Styles 组中的 Home 。

  3. 从下拉菜单单击 Top/Bottom Rules 。出现顶部/底部规则选项。

top bottom rules

  1. 单击 Top Ten Items 并指定 5。选择绿色。

  2. 单击 Bottom Ten Items 并指定 5。选择红色。

choose green and red color

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

d cell formatted conditionally

  1. 重复上述前三个步骤。

  2. 单击 Top Ten% 并指定 5。选择绿色。

  3. 单击 Bottom Ten% 并指定 5。选择红色。

choose red color for d cell

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

d cell highlighted data

  1. 重复上述前三个步骤。

  2. 单击 Above Average 。选择绿色。

  3. 单击 Below Average 。选择红色。

choose red color for e cell

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

e cell highlighted data

Data Bars

您可以使用彩色 Data Bars 查看单元格中相对于其他单元格中的值。数据条的长度代表单元格中的值。更长的条代表更高的值，更短的条代表更低的值。您可以为数据条选择六种纯色 - 蓝色、绿色、红色、黄色、淡蓝色和紫色。

当您有大量数据时，数据条有助于可视化更高、更低和中间值。例如 - 特定月份多个区域的日温度。您可以使用渐变填充色条可视化单元格中相对于其他单元格中的值。您可以为数据条选择六种 Gradient Colors - 蓝色、绿色、红色、黄色、淡蓝色和紫色。

  1. 选择要设置条件格式的范围。

  2. 单击 Conditional Formatting 选项卡下 Styles 组中的 Home 。

  3. 从下拉菜单单击 Data Bars 。出现 Gradient Fill 选项和 填充 选项。

data bars

单击 Gradient Fill 选项中的蓝色数据条。

gradient fill

  1. 重复前三个步骤。

  2. 单击 Solid Fill 选项中的蓝色数据条。

solid fill

您还可以设置数据条格式，以便数据条从单元格中间开始，向左延伸表示负值，向右延伸表示正值。

stretches to left and right

Color Scales

您可以使用 Color Scales 查看单元格中相对于给定范围中其他单元格中的值。与 Highlight Cells Rules 一样， Color Scale 使用单元格阴影显示单元格值之间的差异。将向范围的单元格应用颜色渐变。颜色表示每个单元格值在这个范围内所处的位置。

您可以选择 −

  1. 三色比例 − 绿色 – 黄色 – 红色比例红色 – 黄色 – 绿色比例绿色 – 白色 – 红色比例红色 – 白色 – 绿色比例蓝色 – 白色 – 红色比例红色 – 白色 – 蓝色比例

  2. 双色比例 − 白色 – 红色比例红色 – 白色比例绿色 – 白色比例白色 – 绿色比例绿色 – 黄色比例黄色 – 绿色比例

按照以下步骤操作 −

  1. 选择要进行条件格式化的区域。

  2. 单击 Conditional Formatting 选项卡下 Styles 组中的 Home 。

  3. 从下拉菜单中，单击 Color Scales 。 Color Scale 选项将显示。

  4. 单击绿色 – 黄色 – 红色颜色比例。

数据将根据所选区域中的绿色 – 黄色 – 红色颜色比例高亮显示。

green yellow red color

  1. 重复前三个步骤。

  2. 单击 绿色 – 白色颜色比例。

数据将根据所选区域中的绿色 – 白色颜色比例高亮显示。

green white color scale

Icon Sets

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

icon sets

正如您所观察到的，图标集由三到五个符号组成。您可以定义准则，以便将一个图标与单元格范围中的每个值关联起来。例如，小数字对应红色向下箭头，大数字对应绿色向上箭头，介于两者之间的值对应黄色水平箭头。

  1. 选择要进行条件格式化的范围。

  2. 单击 Conditional Formatting 选项卡下 Styles 组中的 Home 。

  3. 从下拉菜单中，单击 Icon Sets 。 Icon Sets 选项将显示。

  4. 单击彩色三箭头。

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

colored arrows

  1. 重复前三个步骤。 Icon Sets 选项将显示。

  2. 选择五星评级。根据所选区域中的值，评级图标将显示在数据旁边。

rating icons

New Rule

您可以使用 New Rule 创建您自己的公式，作为按您定义的条件来格式化单元格。

使用新规则有两种方法 −

  1. 从下拉菜单中选择 New Rule 选项

  2. 使用 Manage Rules 对话框中的 New Rule 按钮

With New Rule option from the Drop-Down Menu

  1. 选择要进行条件格式化的区域。

  2. 单击 Conditional Formatting 选项卡下 Styles 组中的 Home 。

  3. 从下拉菜单中单击 New Rule 。

drop down menu

New Formatting Rule 对话框显示。

  1. 从“选择规则类型”框中，选择“使用公式来确定哪些单元格要以哪种格式设置”。 Edit the Rule Description 框显示。

  2. 在“以真值格式设置时”中输入公式。

  3. 单击格式按钮，然后单击“确定”。

edit rule description

包含真值的单元格按定义的格式设置格式。

cells with values

With New Rule Button in Manage Rules dialog box

  1. 选择要进行条件格式化的范围。

  2. 单击 Conditional Formatting 选项卡下 Styles 组中的 Home 。

  3. 从下拉菜单中单击 Manage Rules 。

new rule button

Conditional Formatting Rules Manager 对话框显示。

单击 New Rule 按钮。

conditional formatting rules manager

New Formatting Rule 对话框显示。

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

define formula and format

Conditional Formatting Rules Manager 对话框显示由 New Rule 高亮显示的定义。单击 Apply 按钮。

click apply button

包含真值的单元格按定义的格式设置格式。

cells

Clear Rules

可以清除规则来删除为

  1. Selected cells

  2. Current Worksheet

  3. Selected Table

  4. Selected PivotTable

按照给定的步骤执行操作 -

  1. 选择区域/单击工作表/单击需要删除条件格式规则的表>数据透视表。

  2. 单击 Conditional Formatting 选项卡下 Styles 组中的 Home 。

  3. 从下拉菜单中单击 Clear Rules 。显示“清除规则”选项。

clear rules

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

Manage Rules

可以选择 Manage Rules*from the *Conditional Formatting Rules Manager 窗口。可以看到当前选择、整个当前工作表、工作簿中的其他工作表或工作簿中的表格或数据透视表的格式化规则。

  1. 单击 Conditional Formatting 选项卡下 Styles 组中的 Home 。

  2. 从下拉菜单中单击 Manage Rules 。

manage rules

Conditional Formatting Rules Manager 对话框显示。

在 Show formatting rules for 当前选择旁边的列表框中单击箭头，将弹出会显示当前工作表和其他工作表、表格、数据透视表（如果存在且具有条件格式规则）。

show formatting rules

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

worksheet dropdown

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

add new edit and delete rule

  1. 您已经在前面的部分中看到了 New Rule 。您可以通过选择规则并单击 Delete Rule 来删除规则。突出显示的规则将被删除。

  2. 要编辑规则，请选择规则并单击 Edit Rule. Edit Formatting Rule 对话框将出现。

  3. 您可以选择规则类型编辑规则描述编辑格式

  4. 完成更改后，单击“确定”。

  5. 规则的更改将反映在 Conditional Formatting Rules Manager 对话框中。单击 Apply 。

  6. 数据将基于修改后的 Conditional Formatting Rules 突出显示。

modified conditional formatting rules