使用条件格式按类别交替数据格式 [英] Using conditional formatting to alternate the formatting of data by category

问题描述

我有一个电子表格，里面有一堆这样的行:

I have a spreadsheet with a bunch of rows like this:

Name    |   ID    |   Category  |    Attributes...
--------------------------------------------------------
name0   |   753   |   cat1      |    ...
name1   |   724   |   cat1      |    ...
name2   |   149   |   cat1      |    ...
name3   |   265   |   cat1      |    ...
name4   |   032   |   cat2      |    ...
name5   |   991   |   cat2      |    ...
name6   |   238   |   cat2      |    ...
name7   |   005   |   cat3      |    ...
name8   |   632   |   cat3      |    ...
name9   |   393   |   cat3      |    ...

我想对其进行格式化，以便更容易区分不同类别的行.因此，我想根据 Category 列中的值交替着色而不是着色.换句话说，在上面的例子中，我希望 cat1 是深色的，然后 cat2 是浅色的，然后是 cat3一直到床单都变黑了.

And I would like to format it so it's a little bit easier to distinguish the rows of separate categories. I'd therefore like to alternate shading and not shading based on the values in the Category column. In other words, in the example above, I would like for the rows with cat1 to be dark, then cat2 to be light, then cat3 to be dark again all the way down the sheet.

这可能吗?

推荐答案

请选择 ColumnsA:D 和 HOME > 样式 - 条件格式，新规则...，使用公式确定要设置格式的单元格 和 格式化该公式为真的值::

Please select ColumnsA:D and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=ISEVEN(SUMPRODUCT(1/COUNTIF($C$1:$C1,$C$1:$C1)))

Format...，选择您选择的格式(深色?)，OK，OK.

Format..., select your choice of formatting (dark?), OK, OK.

对于轻"，我假设没有填充足够轻，或者，如果没有，则应用标准填充以适应(CF，在触发时，将覆盖它).

For 'light' I have assumed that either no fill is light enough or, if not, you apply standard fill to suit (CF, where triggered, will override it).

COUNTIF 是一个顺序计数器，每个类别从 1 开始.将其划分为 1 并应用 SUMPRODUCT 会生成一个 按类别的顺序计数器(仅在类别发生变化时才会更改).ISEVEN 用于选择替代类别.为了研究公式的详细工作原理，M$ 提供了评估公式.

The COUNTIF is a sequential counter that starts at 1 for each category. Dividing that into 1 and applying SUMPRODUCT generates a sequential counter by category (only changes when the category does). ISEVEN is applied to pick alternate categories. To investigate the detailed workings of formulae M$ has provided Evaluate Formula.

这篇关于使用条件格式按类别交替数据格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！