Excel

Destacar linhas duplicadas

Highlight Duplicate Rows

Fórmula do Excel: destaque as linhas duplicadasFórmula genérica
= COUNTIFS (A:A,$A1,B:B,$B1,C:C,$C1)
Resumo

O Excel contém uma predefinição interna para destacar valores duplicados com formatação condicional, mas funciona apenas no nível da célula. Se quiser destacar linhas inteiras que são duplicadas, você precisará usar sua própria fórmula, conforme explicado a seguir.

Se quiser destacar linhas duplicadas em um conjunto de dados não classificado e não quiser adicionar uma coluna auxiliar, você pode usar uma fórmula que usa a função COUNTIFS para contar valores duplicados em cada coluna dos dados.



Por exemplo, se você tem valores nas células B4: D11 e deseja destacar linhas duplicadas inteiras, pode usar uma fórmula bastante feia:



 
= COUNTIFS ($B:$B,$B4,$C:$C,$C4,$D:$D,$D4)>1

Intervalos nomeados para uma sintaxe mais limpa

A razão pela qual a fórmula acima é tão feia é que precisamos bloquear totalmente cada intervalo de coluna e, em seguida, usar uma referência mista para testar cada célula em cada coluna. Se você criar intervalos nomeados para cada coluna nos dados: col_a, col_b e col_c, a fórmula pode ser escrita com uma sintaxe muito mais limpa:

 
= COUNTIFS (col_b,$B4,col_c,$C4,col_d,$D4)>1
Explicação

Na fórmula, COUNTIFS conta o número de vezes que cada valor em uma célula aparece em sua coluna 'pai'. Por definição, cada valor deve aparecer pelo menos uma vez, portanto, quando a contagem> 1, o valor deve ser uma duplicata. As referências são bloqueadas com cuidado para que a fórmula retorne verdadeira apenas quando todas as 3 células em uma linha aparecerem mais de uma vez em suas respectivas colunas.



como usar concatenar no excel

A opção da coluna auxiliar 'cheats' combinando todos os valores em uma linha juntos em uma única célula usando concatenação. Em seguida, COUNTIF simplesmente conta o número de vezes que esse valor concatenado aparece na coluna D.

Coluna auxiliar + concatenação

Se você não se importa em adicionar um coluna auxiliar aos seus dados, você pode simplificar um pouco a fórmula de formatação condicional. Em uma coluna auxiliar, concatene os valores de todas as colunas. Por exemplo, adicione uma fórmula na coluna E semelhante a esta:

 
=B4&C4&D4

Em seguida, use a seguinte fórmula na regra de formatação condicional:



 
= COUNTIF ($E:$E,$E4)>1

Esta é uma regra muito mais simples e você pode ocultar a coluna auxiliar se desejar.

converter data em mês e ano no excel

Se você tiver um número realmente grande de colunas, poderá usar a função TEXTJOIN (Excel 2016 365) para realizar a concatenação usando um intervalo:

 
= TEXTJOIN (',',TRUE,A1:Z1)

Você pode então usar CONT.SE como acima.

SUMPRODUTO

Se você estiver usando uma versão do Excel anterior a 2007, poderá usar o SUMPRODUCT desta forma:

 
= SUMPRODUCT ((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
Começo rápido | Mais exemplos | Solução de problemas | Treinamento Autor Dave Bruns


^