2019-06-13
Conditional format alternate between 2 colours for groups of rows based on value change
源自: stackoverflow
问题

I have a spreadsheet that is in Google Sheets which has a column that I want to format conditionally. I want to format it, such that groups of rows which all hold the same value are dark grey, while they next group of rows of all the same value are light grey. For example, I want the sheet to look something like this:

column

The numbers will not neccesarily be grouped in order, so I can't just use =mod(a1,2)=1 for the conditional format. I also would like it to update so that no matter how the other columns are filtered, this column remains such that every time the value in the next row is different, it and its duplicates are all a new color, mainly for visualization, to see which data is grouped together.

If I'm to use the formulae =isodd(countunique(a$2:a2)) and =isodd(countunique(a$2:a2)) and then apply some filter, then I get the following, which doesn't alternate the colors based on what is shown.

enter image description here

Any help would be great. Thanks.

答案
1

Try

=isodd(countunique(a$1:a1))

for the light grey and

=iseven(countunique(a$1:a1))

for the dark grey

enter image description here

EDIT

The same question was asked in Excel recently and I realised it would be more straightforward just to count the changes of value like this:

=ISEVEN(SUMPRODUCT(--(A$1:A1<>A$2:A2)))

It doesn't solve the filtering question though.

Conditional format alternate between 2 colours for groups of rows based on value change
查看原网页