Cores de linhas alternativas de dados duplicados no Excel

Question

Estou trabalhando com um conjunto de dados no Excel que contém valores duplicados e valores não duplicados que são números. Eu gostaria que cada grupo de valores duplicados tivesse sombras alternadas através da formatação condicional, se possível. Eu tentei criar uma célula "auxiliar" para incrementar para cada grupo duplicado, mas não consigo descobrir isso.

Aqui está uma fatia do conjunto de dados (que está nas linhas de A2 a A30)

1
1
1
2
3
6
8
9
10
11
12
15
15
17
18
18
19
20
20
20
20
20
21
21
24
25
25
25
25

E o resultado esperado seria o 1 ser vermelho, o 15 ser verde, o 18 ser vermelho, o 20 ser verde, o 21 ser vermelho, o 25 ser verde, etc ...

    

Answer 1

Defina B2 para

=IF(A2=A3, 1, -2)

e defina B3 para

=IF($A2=$A3, B2, IF($A3=$A4, IF(B2>0,3-B2,B2+3), IF(B2>0,-B2,B2)))

e arraste-o para B30 (ou a última linha que contém dados, o que quer que seja). Isto irá avaliar para um número positivo se essa linha fizer parte de um grupo de valores duplicados e um valor negativo se não for (ou seja, se a coluna A contiver um valor único). Ao longo do primeiro grupo de valores duplicados, Coluna B será 1; ao longo do segundo, será 2; ao longo do terceiro, será 1 novamente e assim por diante (alternando). Nas linhas com valores exclusivos, a coluna B conterá o negativo do valor do grupo de valores duplicados mais recente.

Passo a passo:

Primeira linha:

• Se A2=A3 , as linhas 2 e 3 fazem parte do mesmo grupo de valores duplicados, e assim B deve ser 1, porque queremos que o primeiro grupo seja numerado 1. Caso contrário (se A2 ≠ A3 ), Linha 2 não é parte de um grupo de valores duplicados (ainda não sabemos sobre a linha 3 ), por isso deve ter um valor negativo. Nós fazemos isto -2, de forma que o primeiro grupo de valor duplicado (quando nós acharmos isto) será numerado 1.

Linhas subsequentes:

• se A2=A3 , então essa linha e a anterior fazem parte do mesmo grupo de valores duplicados, e assim B deve ser o mesmo da linha anterior.
• Caso contrário (se A2 ≠ A3 ), se A3=A4 , então esta linha e a próxima são as duas primeiras linhas de um novo grupo de valores duplicados, e assim B é IF(B2>0,3-B2,B2+3) :
  • se B2>0 , Em seguida, a linha anterior foi a última linha de um grupo de valores duplicados diferente. Então, queremos alternar valores entre 1 e 2 - se a linha anterior era 1, queremos que esta seja 2 e vice-versa. A expressão 3-B2 implementa esse comportamento alternativo: 3-1 é 2 e 3-2 é 1.
  • Caso contrário (se B2 ≤ 0 ), a linha anterior tem um valor exclusivo na coluna A , e Coluna B tem o negativo do valor B do grupo mais recente. Novamente, queremos alternar valores entre 1 e 2 - se a linha anterior for -1, queremos que esta seja 2 e vice-versa. Obtemos isso com B2+3 : -1+3 é 2 e -2+3 é 1.

• Caso contrário (se A3 ≠ A4 ), então essa linha é uma linha de valor exclusiva e, portanto, B é IF(B2>0,-B2,B2) :

  • Se B2>0 , a linha anterior foi a última linha de um grupo de valores duplicados diferente, e queremos que o valor B desta linha seja o negativo disso.
  • Caso contrário (se B2 ≤ 0 ), a linha anterior também é uma linha de valor exclusivo, por isso, queremos manter o mesmo valor B .

  Acho que eu poderia ter dito -ABS(B2) aqui.

Então, agora, obviamente, você usa formatação condicional para colorir as células em vermelho se o valor na coluna B for 1 e verde for 2.