使用条件格式来按类别替换数据格式 [英] 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      |    ...

我想对其进行格式化，以便区分行单独的类别。因此，我想根据类别列中的值来替换底纹而不是底纹。换句话说，在上面的示例中，我想将 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..., 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屋！