Excel Data Analysis 简明教程

Excel Data Analysis - Conditional Formatting

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

In Microsoft Excel, you can use Conditional Formatting for data visualization. You have to specify formatting for a cell range based on the contents of the cell range. The cells that meet the specified conditions would be formatted as you have defined.

Example

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

In a range containing the sales figures of the past quarter for a set of salespersons, you can highlight those cells representing who have met the defined target, say, $2500.

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

You can set the condition as total sales of the person >= $2500 and specify a color code green. Excel checks each cell in the range to determine whether the condition you specified, i.e., total sales of the person >= $2500 is satisfied.

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

Excel applies the format you chose, i.e. the green color to all the cells that satisfy the condition. If the content of a cell does not satisfy the condition, the formatting of the cell remains unchanged. The result is as expected, only for the salespersons who have met the target, the cells are highlighted in green – a quick visualization of the analysis results.

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

You can specify any number of conditions for formatting by specifying Rules. You can pick up the rules that match your conditions from

  1. Highlight cells rules

  2. Top / Bottom rules

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

You can also define your own rules. You can −

  1. Add a rule

  2. Clear an existing rule

  3. Manage the defined rules

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

Further, you have several formatting options in Excel to choose the ones that are appropriate for your Data Visualization −

  1. Data Bars

  2. Color Scales

  3. Icon Sets

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

Conditional formatting has been promoted over the versions Excel 2007, Excel 2010, Excel 2013. The examples you find in this chapter are from Excel 2013.

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

In the following sections, you will understand the conditional formatting rules, formatting options and how to work with rules.

Highlight Cells Rules

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

You can use Highlight Cells rule to assign a format to cells whose contents meet any of the following criteria −

  1. Numbers within a given numerical range − Greater ThanLess ThanBetweenEqual To

  2. Text that contains a given text string.

  3. Date occurring within a given range of dates relative to the current date − YesterdayTodayTomorrowIn the last 7 daysLast weekThis weekNext weekLast monthThis MonthNext month

  4. Values that are duplicate or unique.

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

Follow the steps to conditionally format cells −

  1. Select the range to be conditionally formatted.

  2. Click Conditional Formatting in the Styles group under Home tab.

  3. Click Highlight Cells Rules from the drop-down menu.

highlight cells rules

  1. Click Greater Than and specify >750. Choose green color.

  2. Click Less Than and specify < 500. Choose red color.

  3. Click Between and specify 500 and 750. Choose yellow color.

rules

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

The data will be highlighted based on the given conditions and the corresponding formatting.

data highlighted

Top / Bottom Rules

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

You can use Top / Bottom Rules to assign a format to cells whose contents meet any of the following criteria −

  1. Top 10 items − Cells that rank in the top N, where 1 ⇐ N ⇐ 1000.

  2. Top 10% − Cells that rank in the top n%, where 1 ⇐ n ⇐ 100.

  3. Bottom 10 items − Cells that rank in the bottom N, where 1 ⇐ N ⇐ 1000.

  4. Bottom 10% − Cells that rank in the bottom n%, where 1 ⇐ n ⇐ 100.

  5. Above average − Cells that are above average for the selected range.

  6. Below average − Cells that are below average for the selected range.

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

Follow the steps given below to assign the Top/Bottom rules.

  1. Select the range to be conditionally formatted.

  2. Click Conditional Formatting in the Styles group under Home tab.

  3. Click Top/Bottom Rules from the drop-down menu. Top/Bottom rules options appear.

top bottom rules

  1. Click Top Ten Items and specify 5. Choose green color.

  2. Click Bottom Ten Items and specify 5. Choose red color.

choose green and red color

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

The data will be highlighted based on the given conditions and the corresponding formatting.

d cell formatted conditionally

  1. Repeat the first three steps given above.

  2. Click Top Ten% and specify 5. Choose green color.

  3. Click Bottom Ten% and specify 5. Choose red color.

choose red color for d cell

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

The data will be highlighted based on the given conditions and the corresponding formatting.

d cell highlighted data

  1. Repeat the first three steps given above.

  2. Click Above Average. Choose green color.

  3. Click Below Average. Choose red color.

choose red color for e cell

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

The data will be highlighted based on the given conditions and the corresponding formatting.

e cell highlighted data

Data Bars

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

You can use colored Data Bars to see the value in a cell relative to the values in the other cells. The length of the data bar represents the value in the cell. A longer bar represents a higher value, and a shorter bar represents a lower value. You have six solid colors to choose from for the data bars – blue, green, red, yellow, light blue and purple.

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

Data bars are helpful in visualizing the higher, lower and intermediate values when you have large amounts of data. Example - Day temperatures across regions in a particular month. You can use gradient fill color bars to visualize the value in a cell relative to the values in other cells. You have six Gradient Colors to choose from for the Data Bars – Blue, Green, Red, Yellow, Light Blue and Purple.

  1. Select the range to be formatted conditionally.

  2. Click Conditional Formatting in the Styles group under Home tab.

  3. Click Data Bars from the drop-down menu. The Gradient Fill options and * Fill* options appear.

data bars

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

Click the blue data bar in the Gradient Fill options.

gradient fill

  1. Repeat the first three steps.

  2. Click the blue data bar in the Solid Fill options.

solid fill

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

You can also format data bars such that the data bar starts in the middle of the cell, and stretches to the left for negative values and stretches to the right for positive values.

stretches to left and right

Color Scales

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

You can use Color Scales to see the value in a cell relative to the values in the other cells in a given range. As in the case of Highlight Cells Rules, a Color Scale uses cell shading to display the differences in cell values. A color gradient will be applied to a range of cells. The color indicates where each cell value falls within that range.

您可以选择 −

You can choose from −

  1. Three - Color Scale − Green – Yellow – Red Color ScaleRed – Yellow – Green Color ScaleGreen – White – Red Color ScaleRed – White – Green Color ScaleBlue – White – Red Color ScaleRed – White – Blue Color Scale

  2. Two-Color Scale − White – Red Color ScaleRed – White Color ScaleGreen – White Color ScaleWhite – Green Color ScaleGreen – Yellow Color ScaleYellow – Green Color Scale

按照以下步骤操作 −

Follow the steps given below −

  1. Select the Range to be conditionally formatted.

  2. Click Conditional Formatting in the Styles group under Home tab.

  3. Click Color Scales from the drop-down menu. The Color Scale options appear.

  4. Click the Green – Yellow – Red Color Scale.

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

The Data will be highlighted based on the Green – Yellow – Red color scale in the selected range.

green yellow red color

  1. Repeat the first three steps.

  2. Click the Green – White color scale.

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

The data will be highlighted based on the Green – White color scale in the selected range.

green white color scale

Icon Sets

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

You can use the icon sets to visualize numerical differences. The following icon sets are available −

icon sets

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

As you observe, an icon set consists of three to five symbols. You can define criteria to associate an icon with each value in a cell range. For example, a red down arrow for small numbers, a green up arrow for large numbers, and a yellow horizontal arrow for intermediate values.

  1. Select the range to be conditionally formatted.

  2. Click Conditional Formatting in the Styles group under Home tab.

  3. Click Icon Sets from the drop-down menu. The Icon Sets options appear.

  4. Click the colored three arrows.

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

Colored Arrows appear next to the Data based on the Values in the selected range.

colored arrows

  1. Repeat the first three steps. The Icon Sets options appear.

  2. Select 5 Ratings. The Rating Icons appear next to the data based on the values in the selected range.

rating icons

New Rule

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

You can use New Rule to create your own formula as a condition to format a cell as you define.

使用新规则有两种方法 −

There are two ways to use New Rule −

  1. With New Rule option from the drop-down menu

  2. With New Rule button in Manage Rules dialog box

With New Rule option from the Drop-Down Menu

  1. Select the Range to be conditionally formatted.

  2. Click Conditional Formatting in the Styles group under Home tab.

  3. Click New Rule from the drop-down menu.

drop down menu

New Formatting Rule 对话框显示。

The New Formatting Rule dialog box appears.

  1. From the Select a Rule Type Box, select Use a formula to determine which cells to format. Edit the Rule Description box appears.

  2. In the format values where this formula is true: type the formula.

  3. Click the format button and click OK.

edit rule description

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

Cells that contain values with the formula TRUE, are formatted as defined.

cells with values

With New Rule Button in Manage Rules dialog box

  1. Select the range to be conditionally formatted.

  2. Click Conditional Formatting in the Styles group under Home tab.

  3. Click Manage Rules from the drop-down menu.

new rule button

Conditional Formatting Rules Manager 对话框显示。

The Conditional Formatting Rules Manager dialog box appears.

单击 New Rule 按钮。

Click the New Rule button.

conditional formatting rules manager

New Formatting Rule 对话框显示。

The New Formatting Rule dialog box appears.

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

Repeat the Steps given above to define your formula and format.

define formula and format

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

The Conditional Formatting Rules Manager dialog box appears with defined New Rule highlighted. Click the Apply button.

click apply button

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

Cells that contain values with the formula TRUE, are formatted as defined.

cells

Clear Rules

可以清除规则来删除为

You can Clear Rules to delete all conditional formats you have created for

  1. Selected cells

  2. Current Worksheet

  3. Selected Table

  4. Selected PivotTable

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

Follow the given steps −

  1. Select the Range / Click on a Worksheet / Click the table > PivotTable where conditional formatting rules need to be removed.

  2. Click Conditional Formatting in the Styles group under Home tab.

  3. Click Clear Rules from the drop-down menu. The Clear rules options appear.

clear rules

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

Select the appropriate option. The conditional formatting is cleared from the Range / Worksheet / Table / PivotTable.

Manage Rules

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

You can Manage Rules*from the *Conditional Formatting Rules Manager window. You can see formatting rules for the current selection, for the entire current worksheet, for the other worksheets in the workbook or the tables or PivotTables in the workbook.

  1. Click Conditional Formatting in the Styles group under Home tab.

  2. Click Manage Rules from the drop-down menu.

manage rules

Conditional Formatting Rules Manager 对话框显示。

The Conditional Formatting Rules Manager dialog box appears.

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

Click the arrow in the List Box next to Show formatting rules for Current Selection, This Worksheet and other Sheets, Tables, PivotTable if exist with Conditional Formatting Rules, appear.

show formatting rules

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

Select This Worksheet from the drop-down list. Formatting Rules on the current Worksheet appear in the order that they will be applied. You can change this order by using the up and down arrows.

worksheet dropdown

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

You can add a New Rule, Edit a Rule and Delete a Rule.

add new edit and delete rule

  1. You have already seen New Rule in the earlier section. You can delete a rule by selecting the Rule and clicking Delete Rule. The highlighted Rule is deleted.

  2. To edit a Rule, select the RULE and click on Edit Rule. Edit Formatting Rule dialog box appears.

  3. You can Select a Rule Type Edit the Rule Description Edit Formatting

  4. Once you are done with the changes, click OK.

  5. The changes for the Rule will be reflected in the Conditional Formatting Rules Manager dialog box. Click Apply.

  6. The data will be highlighted based on the modified Conditional Formatting Rules.

modified conditional formatting rules