使用表格中的条件格式设置Use conditional formatting in tables

通过 Power BI Desktop 中表格的条件格式设置,你可以根据单元格值指定自定义单元格背景色,包括使用渐变色。With conditional formatting for tables in Power BI Desktop, you can specify customized cell colors, including color gradients, based on field values. 还可以用数据栏、KPI 图标或 web 链接来表示单元格值。You can also represent cell values with data bars or KPI icons, or as active web links. 可以将条件格式应用到任何文本或数据字段,只要你基于具有数字、颜色名称或十六进制代码的字段,或 web URL 值来设置格式即可。You can apply conditional formatting to any text or data field, as long as you base the formatting on a field that has numeric, color name or hex code, or web URL values.

若要应用条件格式,请在 Power BI Desktop 中选择“表”和“矩阵”可视化效果 。To apply conditional formatting, select a Table or Matrix visualization in Power BI Desktop. 在“可视化效果”窗格的“字段”部分中,右键单击或选择要进行格式设置的“值”井中字段旁边的向下箭头 。In the Fields section of the Visualizations pane, right-click or select the down-arrow next to the field in the Values well that you want to format. 选择“条件格式”,然后选择要应用的格式设置类型。Select Conditional formatting, and then select the type of formatting to apply.

“条件格式”菜单

备注

条件格式将覆盖任何应用于有条件格式的单元的自定义背景或字体颜色。Conditional formatting overrides any custom background or font color you apply to the conditionally formatted cell.

若要从可视化效果中删除条件格式,请从字段的下拉菜单中选择“删除条件格式”,然后选择要删除的格式类型。To remove conditional formatting from a visualization, select Remove conditional formatting from the field's drop-down menu, and then select the type of formatting to remove.

删除条件格式菜单

以下各节介绍了每个条件格式选项。The following sections describe each conditional formatting option. 可以将多个选项组合到一个表列里。You can combine more than one option in a single table column.

设置背景或字体颜色Format background or font color

若要设置背景或字体颜色,请为字段选择“条件格式”,然后从下拉菜单中选择“背景颜色”或“字体颜色” 。To format cell background or font color, select Conditional formatting for a field, and then select either Background color or Font color from the drop-down menu.

选择“背景颜色”或“字体颜色”

“背景颜色”或“字体颜色”对话框将会打开,并在标题中显示正在进行格式设置的字段的名称 。The Background color or Font color dialog box opens, with the name of the field you're formatting in the title. 在选择条件格式选择后,选择“确定”。After selecting conditional formatting options, select OK.

“背景颜色”和“字体颜色”对话框

“背景颜色”和“字体颜色”选项相同,但分别影响单元格背景颜色和字体颜色 。The Background color and Font color options are the same, but affect the cell background color and font color, respectively. 可以将相同或不同的条件格式应用到字段的字体设置和背景设置。You can apply the same or different conditional formatting to a field's font color and background color. 如果将字段的字体和背景设置为相同的颜色,则该字体会与背景融为一体,因此表列只显示颜色。If you make a field's font and background the same color, the font blends into the background so the table column shows only the colors.

按色阶设置颜色Color by color scale

若要按色阶设置单元格背景或字体颜色,请在“背景颜色”或“字体颜色”的“格式设置依据”字段中选择“色阶” 。To format cell background or font color by color scale, in the Format by field of the Background color or Font color dialog box, select Color scale. 在“基于字段”下,选择要作为格式设置基础的字段。Under Based on field, select the field to base the formatting on. 可基于当前字段或模型中具有数字或颜色数据的任何字段设置格式。You can base the formatting on the current field, or on any field in your model that has numerical or color data.

在“汇总”下,指定要用于所选字段的聚合类型。Under Summarization, specify the aggregation type you want to use for the selected field. 在“默认格式设置”下,选择要用于空白值的格式。Under Default formatting, select a formatting to apply to blank values.

在“最小值”和“最大值”下,选择是要基于最低和最高字段值,还是是要基于输入的自定义值应用配色方案 。Under Minimum and Maximum, choose whether to apply the color scheme based on the lowest and highest field values, or on custom values you enter. 下拉然后选择要应用到最小值和最大值的色卡。Drop down and select the colors swatches you want to apply to the minimum and maximum values. 选择“散射”复选框,以指定“中心”值和颜色 。Select the Diverging check box to also specify a Center value and color.

按色阶设置单元格背景

“可购性”列上按色阶设置背景格式的示例表如下:An example table with color scale background formatting on the Affordability column looks like this:

采用散射背景色阶的示例表

“可购性”列上按色阶设置字体格式的示例表如下:The example table with color scale font formatting on the Affordability column looks like this:

采用散射字体色阶的示例表

按规则切换颜色Color by rules

若要按规则设置单元格背景或字体颜色,请在“背景颜色”或“字体颜色”的“格式设置依据”字段中选择“规则” 。To format cell background or font color by rules, in the Format by field of the Background color or Font color dialog box, select Rules. 再次强调,“基于字段”显示格式设置基于的字段,“汇总”显示字段的聚合类型 。Again, Based on field shows the field to base the formatting on, and Summarization shows the aggregation type for the field.

在“规则”下,输入一个或多个值范围,并设置每个值范围的颜色。Under Rules, enter one or more value ranges, and set a color for each one. 每个值范围都有 If 值条件、and 值条件和一种颜色 。Each value range has an If value condition, an and value condition, and a color. 单元格背景或每个值范围中的字体都以给定的颜色着色。Cell backgrounds or fonts in each value range are colored with the given color. 以下示例具有三个规则:The following example has three rules:

按规则切换颜色

“可购性”列上基于规则设置背景颜色格式的示例表如下:An example table with rules-based background color formatting on the Affordability column looks like this:

使用“根据规则着色”选项的示例表

根据颜色值着色Color by color values

如果具有使用颜色名称或十六进制值数据的字段或度量值,则可以使用条件格式自动将这些颜色应用于列的背景或字体颜色。If you have a field or measure with color name or hex value data, you can use conditional formatting to automatically apply those colors to a column's background or font color. 还可以使用自定义逻辑向字体或背景应用颜色。You can also use custom logic to apply colors to the font or background.

该字段可以使用 https://www.w3.org/TR/css-color-3/ 处的 CSS 颜色规范中列出的任何颜色值。The field can use any color values listed in the CSS color spec at https://www.w3.org/TR/css-color-3/. 这些颜色值可以包括:These color values can include:

  • 3、6 或 8 位十六进制代码,例如 #3E4AFF。3, 6 or 8-digit hex codes, for example #3E4AFF. 确保代码的开头包含 # 符号。Make sure you include the # symbol at the start of the code.
  • RGB 或 RGBA 值,如 RGBA(234, 234, 234, 0.5)。RGB or RGBA values, like RGBA(234, 234, 234, 0.5).
  • HSL 或 HSLA 值,如 HSLA(123, 75%, 75%, 0.5)。HSL or HSLA values, like HSLA(123, 75%, 75%, 0.5).
  • 颜色名称,如 Green、SkyBlue 或 PeachPuff。Color names, such as Green, SkyBlue, or PeachPuff.

下表具有与每个状态关联的颜色名称:The following table has a color name associated with each state:

具有颜色名称的状态表

若要根据字段值对“颜色”列进行格式设置,请为“颜色”字段选择“条件格式”,然后选择“背景颜色”或“字体颜色” 。To format the Color column based on its field values, select Conditional formatting for the Color field, and then select Background color or Font color.

在“背景颜色”或“字体颜色”对话框中,从“格式设置依据”下拉字段中选择“字段值” 。In the Background color or Font color dialog box, select Field value from the Format by drop-down field.

按字段值设置格式

“颜色”字段上基于颜色字段值设置背景颜色格式的示例表如下 :An example table with color field value-based Background color formatting on the Color field looks like this:

按字段值设置背景格式的示例表

如果还使用“字段值”设置列的“字体颜色”,则结果为“颜色”列中的纯色 :If you also use Field value to format the column's Font color, the result is a solid color in the Color column:

按字段值设置背景和字体格式

基于计算设置颜色Color based on a calculation

可以创建计算,基于你选择的业务逻辑条件输出不同的值。You can create a calculation that outputs different values based on business logic conditions you select. 创建公式通常比在条件格式对话框中创建多个规则更快。Creating a formula is usually faster than creating multiple rules in the conditional formatting dialog.

例如,以下公式基于现有的“可购性”列值将十六进制颜色值应用到新的“可购性排名”列 :For example, the following formula applies hex color values to a new Affordability rank column, based on existing Affordability column values:

公式计算

若要应用颜色,请为“可购性”列选择“背景颜色”或“字体颜色”条件格式,并基于“可购性排名”列的“字段值”进行格式设置 。To apply the colors, select Background color or Font color conditional formatting for the Affordability column, and base the formatting on the Field value of the Affordability rank column.

基于计算列设置背景颜色

“可购性”背景颜色基于计算的“可购性排名”的示例表如下所示 :The example table with Affordability background color based on calculated Affordability rank looks like this:

具有基于计算值的颜色的示例表

可以创建多个版本,只需动用你的想象力和一些计算即可。You can create many more variations, just by using your imagination and some calculations.

添加数据栏Add data bars

若要基于单元格值显示数据栏,请为“可购性”字段选择“条件格式”,然后从下拉菜单中选择“数据栏” 。To show data bars based on cell values, select Conditional formatting for the Affordability field, and then select Data bars from the drop-down menu.

在“数据栏”对话框中,默认情况下“仅显示数据条”选项处于未选中状态,因此表格单元格同时显示数据条和实际值 。In the Data bars dialog, the Show bar only option is unchecked by default, so the table cells show both the bars and the actual values. 若要仅显示数据栏,请选择“仅显示栏”复选框。To show the data bars only, select the Show bar only check box.

可以指定“最小”和“最大”值、数据栏颜色和方向,以及轴颜色 。You can specify Minimum and Maximum values, data bar colors and direction, and axis color.

数据条对话框

将数据栏应用到“可购性”列的示例如下所示:With data bars applied to the Affordability column, the example table looks like this:

使用数据栏的示例

添加图标Add icons

若要显示基于单元格值的图标,请为字段选择“条件格式”,然后从下拉菜单中选择“图标” 。To show icons based on cell values, select Conditional formatting for the field, and then select Icons from the drop-down menu.

在“图标”对话框中的“格式设置依据”下,选择“规则”或“字段值” 。In the Icons dialog, under Format by, select either Rules or Field value.

若要按规则设置格式,请选择“基于字段”、“汇总”方法、“图标布局”、“图标对齐”、“图标样式”和一个或多个“规则” 。To format by rules, select a Based on field, Summarization method, Icon layout, Icon alignment, icon Style, and one or more Rules. 在“规则”下,输入一个或多个包含 If 值条件和 and 值条件的规则,并选择要应用于每个规则的图标 。Under Rules, enter one or more rules with an If value condition and an and value condition, and select an icon to apply to each rule.

若要按字段值设置格式,请选择“基于字段”、“汇总”方法、“图标布局”和“图标对齐” 。To format by field values, select a Based on field, Summarization method, Icon layout, and Icon alignment.

以下示例基于三个规则添加图标:The following example adds icons based on three rules:

图标对话框

选择“确定”。Select OK. 将图标根据规则应用到“可购性”列的示例如下所示:With icons applied to the Affordability column by rules, the example table looks like this:

使用图标的示例

格式设置为 web URLFormat as web URLs

如果有包含网站 URL 的列或度量值,可以使用条件格式将这些 URL 作为活动链接应用到字段。If you have a column or measure that contains website URLs, you can use conditional formatting to apply those URLs to fields as active links. 例如,下表有一个“网站”列,其中包含每个状态的网站 URL:For example, the following table has a Website column with website URLs for each state:

有 web URL 列的表

若要将每个状态名称显示为指向其网站的实时链接,请为“状态”字段选择“条件格式”,然后选择“Web URL” 。To display each state name as a live link to its website, select Conditional formatting for the State field, and then select Web URL. 在“Web URL”对话框中的“基于字段”下,选择“网站”,然后选择“确定” 。In the Web URL dialog box, under Based on field, select Website, and then select OK.

“Web URL”格式应用到“状态”字段后,每个状态名称都是指向其网站的活动链接 。With Web URL formatting applied to the State field, each state name is an active link to its website. 下面的示例表中,“Web URL”格式应用到“状态”列,条件“数据栏”和“背景格式设置”应用到“可购性”列 。The following example table has Web URL formatting applied to the State column, and conditional Data bars and Background formatting applied to the Affordability column.

有 URL、数据栏和背景颜色的表

总计和小计Totals and subtotals

从 2020 年 4 月发行版开始,可以对表和矩阵视觉对象应用条件格式规则,以便进行总计和小计。Beginning with the April 2020 release, you can apply conditional formatting rules to totals and subtotals, for both table and matrix visuals.

可通过使用条件格式中的“应用于”下拉框来应用条件格式规则,如下图所示。You apply the conditional formatting rules by using the Apply to drop-down in conditional formatting, as shown in the following image.

设置总计和小计格式

必须手动设置条件格式规则的阈值和范围。You must manually set the thresholds and ranges for conditional formatting rules. 对于矩阵,值是指矩阵层次结构的最低可见级别。For matrices, Values will refer to the lowest visible level of the matrix hierarchy.

注意事项和限制Considerations and limitations

使用条件表格式设置时需要牢记几个注意事项:There are a few considerations to keep in mind when working with conditional table formatting:

  • 条件格式仅适用于表或矩形视觉对象的值,不适用于任何小计、总计或“共计”行。Conditional formatting applies only to the values of Table or Matrix visuals, and doesn't apply to any subtotals, grand totals, or the Total row.
  • 不包含分组的任何表都显示为不支持条件格式设置的单个行。Any table that doesn't have a grouping is displayed as a single row that doesn't support conditional formatting.
  • 如果数据包含 NaN 值,则无法应用具有自动最大/最小值的渐变格式,或具有百分比规则的基于规则的格式设置。You can't apply gradient formatting with automatic maximum/minimum values, or rule-based formatting with percentage rules, if your data contains NaN values. NaN 表示“不是数字”,最常见的原因是被零除错误。NaN means "Not a number," most commonly caused by a divide by zero error. 可以使用 DIVIDE() DAX 函数来避免这些错误。You can use the DIVIDE() DAX function to avoid these errors.
  • 条件格式需要对值应用聚合或度量值。Conditional formatting needs an aggregation or measure to be applied to the value. 因此“按值着色”示例中显示了“最先”或“最后”。That's why you see 'First' or 'Last' in the Color by value example. 如果要针对 Analysis Services 多维数据集生成报表,则将无法使用属性进行条件格式设置,除非多维数据集所有者生成了提供值的度量值。If you're building your report against an Analysis Service multidimensional cube, you won't be able to use an attribute for conditional formatting unless the cube owner has built a measure that provides the value.

后续步骤Next steps

有关颜色格式设置的详细信息,请参阅 Power BI 中的颜色格式设置提示和技巧For more information about color formatting, see Tips and tricks for color formatting in Power BI