在电子表格的字段中按数据替换行颜色 [英] Alternate row colour by data in fields in a spreadsheet

问题描述

下午好，

我已经搜索过，但找不到答案-如果有答案，请直接给我！

I've had a search but can't find the answer - please direct me if there is one!

我希望使电子表格对用户具有更好的可读性.它需要在工作表外部进行大量手动工作，因此，电子表格所花费的时间越少，效果越好.

I'm looking to make my spreadsheet have better readability to the user. It requires a lot of manual work outside of the sheet, so the less time strain the spreadsheet is, the better.

我知道在条件格式中使用 = mod()，但这不是我想要的

I know to to use =mod() in Conditional Formatting but this isn't what I'm looking for

我也知道打开过滤器下拉列表，单击一个单元格，按下两次并按下空格键(漂洗并重复)-但我不会在1000行以上执行此操作...

I also know about opening the filter drop down, and clicking one cell, pressing down twice and pressing space bar (rinse and repeat) - but I'm not going to do this over 1000 rows...

是否可以在excel中从过滤的名称中替换颜色?

Is there a way to alternate the colours from a filtered name in excel?

例如:

+---------------+---------------+--------------+
|   Site Code   |   Site Name   | Changed Date |
+---------------+---------------+--------------+
| 000020        | Bobs site     | 28/11/18     | <-- colour 1
| 000020        | Bobs site     | 26/11/18     | <-- colour 1
| 059201        | Julian's      | date         | <-- colour 2
| 059201        | Julian's      | date         | <-- colour 2
| 002237        | etc. 1        | date         | <-- colour 1
| 523878        | etc. 2        | date         | <-- colour 2
| 523878        | etc. 3        | date         | <-- colour 2
+---------------+---------------+--------------+

因此，不是按行号而是按名称鲍勃站点"将是一种颜色，列表中的下一种将是另一种颜色，等等

So rather than by line number, it would be by the name "bobs site" would be one colour, the next in the list would be another colour etc

我希望将此方法应用于站点代码和站点名称，因此，当按其中一个进行过滤时，这些行将正确突出显示.

I would love for this to apply to site code and site name, so when filtering by either, the rows are highlighted correctly.

我无法以 = mod()这种方式进行此操作，因为某些站点只有一个条目，大多数站点只有2个，而一些站点最多可以有10个

I can't do this in the =mod() kind of way, as some sites have just one entry, most have 2 and a few can have up to 10

答案的证明供将来参考

Proof of the answer working for future references

推荐答案

可与帮助程序列一起使用，并可以通过 COUNTIF 和 MOD 使用条件格式.

Doable with a helper column and Conditional Formatting with COUNTIF and MOD.

在帮助器列中:

=OR(A2<>A1,B2<>B1)

如果站点代码或站点名称与上一行相比已更改(或未更改)，则返回 TRUE 或 FALSE .

which returns TRUE or FALSE if the site code or site name has changed (or not) compared to the previous row.

然后是2条条件格式设置规则:

Then 2 conditional formatting rules:

1. = MOD(COUNTIF($ D $ 2:$ D2，TRUE)，2)= 0
2. = MOD(COUNTIF($ D $ 2:$ D2，TRUE)，2)= 1

COUNTIF 中的混合引用( $ D $ 2:$ D2 )将允许每个单独的部分以 TRUE 依次相加.

The mixed reference ($D$2:$D2) in the COUNTIF will allow for each separate section to be coloured alternately as the instances of TRUE are successively added up.

这篇关于在电子表格的字段中按数据替换行颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！