Localizar, destacar, filtrar, contar e eliminar duplicados no Excel

Ao procurar células duplicadas apenas numa coluna ou lista, podemos utilizar a função CONTAR.SE para identificar e contar rapidamente os dados duplicados com facilidade.
1. Selecione uma célula vazia junto à coluna em que irá encontrar duplicatas.

2. Introduza a fórmula =CONTAR.SE($C$3:$C$12; C3)>1 e prima a tecla Enter.

3. Arraste a alça de autorreenchimento da célula que contém a fórmula para copiá-la para outras células.

Notas: Na fórmula =COUNTIF($C$3:$C$12, C3)>1,
(1) $C$3:$C$12 é a coluna ou lista na qual irá procurar valores duplicados. Como a coluna permanece estática ao copiar a fórmula para outras células, trata-se geralmente de uma referência absoluta, indicada pelo símbolo «$».
(2) C3 é a primeira célula da coluna especificada. Trata-se de uma referência relativa, pois precisa de se ajustar automaticamente ao copiar a fórmula para outras células.
(3) Esta fórmula devolve VERDADEIRO ou FALSO. VERDADEIRO significa que o valor correspondente é duplicado, enquanto FALSO indica que é único na coluna.
(4) Por vezes, os valores VERDADEIRO ou FALSO podem não ser intuitivos. Pode combinar a fórmula original com a função SE para obter diretamente Duplicados.
=IF(COUNTIF($C$3:$C$12, C3)>1,«Duplicates»,«»)

Em alguns casos, é necessário comparar duas colunas e identificar os valores duplicados. Por exemplo, tem duas listas de nomes e pretende remover da segunda lista os nomes que já existem na primeira. Pode concluir facilmente esta tarefa utilizando as funções PROCV e SEERRO.

1. Selecione uma célula vazia junto à segunda lista de nomes.

2. Introduza a fórmula =IFERROR(VLOOKUP(D3,$B$3:$B$18,1,0),«») e prima a tecla Enter.

3. Arraste a alça de autorreenchimento da célula que contém a fórmula para copiá-la para outras células, conforme necessário.

Notas: Na fórmula acima,
(1) D3 é a primeira célula da segunda lista de nomes. A referência tem de mudar automaticamente ao copiar a fórmula para outras células; por conseguinte, trata-se de uma referência relativa.
(2) $B$3:$B$18 é a lista Primeiro Nome. Trata-se de uma referência absoluta, pois o intervalo deve permanecer estático ao copiar a fórmula para outras células.
(3) Quando um nome for duplicado em relação aos nomes da primeira lista, a fórmula devolve o nome; caso contrário, devolve uma célula vazia.
(4) Também pode aplicar a fórmula =IF(COUNTIF($B$3:$B$18,D3)>0,«Duplicates»,«») para identificar e remover nomes duplicados na segunda lista em comparação com a primeira. Esta fórmula devolve «Duplicados» se o nome correspondente for duplicado.

(5) Caso precise de comparar dois intervalos situados em folhas diferentes, basta adicionar o nome da folha antes da referência da coluna a comparar. No nosso exemplo, basta alterar $B$3:$B$18 para Folha1!$B$3:$B$18 na fórmula.

As fórmulas apresentadas anteriormente não diferenciam maiúsculas de minúsculas ao encontrar duplicatas — ou seja, «apple» é considerado duplicado de «APPLE». Para identificar valores duplicados numa única coluna com distinção entre maiúsculas e minúsculas, pode utilizar uma fórmula matricial.

1. Selecione uma célula vazia junto à coluna em que irá encontrar duplicatas.

2. Introduza a fórmula matricial =IF(SUM((--EXACT($C$3:$C$12,C3)))<=1,«»,«Duplicate») e prima as teclas Ctrl + Shift + Enter.

3. Arraste a alça de autorreenchimento da célula que contém a fórmula para copiar a fórmula matricial para outras células.

Notas: Na fórmula matricial acima,
(1) $C$3:$C$12 é a coluna onde precisa de encontrar valores duplicados. A referência é absoluta porque permanece estática ao copiar a fórmula matricial para outras células.
(2) C4 é a primeira célula da coluna. A referência é relativa, pois tem de mudar automaticamente ao copiar a fórmula matricial para outras células.
(3) Se a célula correspondente for duplicada, a fórmula matricial devolve «Duplicado»; caso contrário, devolve uma célula vazia.

Pode utilizar a funcionalidade Usar Formatação Condicional para destacar rapidamente células duplicadas numa coluna ou num intervalo.

1. Selecione a coluna em que pretende destacar as células duplicadas.

2. Clique em Base > Usar Formatação Condicional > Regras para Destacar Células > Valores Duplicados. Veja a imagem abaixo:

3. Na caixa de diálogo Valores Duplicados que surge, selecione Duplicado na primeira lista suspensa, escolha um cenário de destaque na segunda lista suspensa e clique no botão OK.

Notas: Caso os cenários de destaque predefinidos não satisfaçam as suas necessidades, pode selecionar Formato personalizado na segunda lista suspensa e, em seguida, escolher a cor de destaque, o tipo de letra e os contornos das células conforme necessário na caixa de diálogo Definir formato de célula que surge.

De seguida, verá todas as células duplicadas destacadas na coluna selecionada, tal como ilustrado na imagem seguinte.

Notas:
(1) Depois de destacar as células duplicadas, podemos filtrá-las facilmente. ()Clique para ver como)
(2) Depois de destacar as células duplicadas, também podemos eliminá-las em massa de forma simples. ()Clique para ver como)

Alguns utilizadores preferem Intervalo de Linha Destacada com base em células duplicadas numa determinada coluna. Nesta situação, podemos criar uma regra personalizada de Usar Formatação Condicional para concluir esta tarefa.

1. Selecione o intervalo (excluindo a linha do cabeçalho) que será destacado com base em células duplicadas.

2. Clique em Base > Usar Formatação Condicional > Nova Regra.

3. Na caixa de diálogo Nova Regra de Formatação,
(1) selecione a opção Utilizar uma fórmula para determinar quais as células a formatar;
(2) introduza a fórmula =COUNTIF($C$3:$C$12,$C3)>1 na caixa Formatar valores em que esta fórmula é verdadeira;
Sugestão: Na fórmula, $C$3:$C$12 é a coluna que contém as células duplicadas e $C3 é a primeira célula da coluna.
(3) Clique no botão Formatar.

4. Na caixa de diálogo Definir Formato de Célula, especifique a cor de preenchimento, o tipo de letra e os contornos das células conforme necessário e clique sucessivamente nos botões OK para guardar as definições.

Até agora, no Selecionar intervalo, as linhas são destacadas com base em células duplicadas na coluna especificada. Veja a imagem:

Para destacar linhas duplicadas num determinado intervalo, pode também utilizar a funcionalidade Usar Formatação Condicional.

1. Selecione o intervalo, exceto a linha de cabeçalho.

2. Clique em Base > Usar Formatação Condicional > Nova Regra.

3. Na caixa de diálogo Nova Regra de Formatação:
(1) Clique para selecionar a opção Utilizar uma fórmula para determinar quais as células a formatar;
(2) Na caixa Formatar valores em que esta fórmula é verdadeira, introduza a fórmula =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1;
(3) Clique no botão Formatar.

Notas: Na fórmula =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1:
(1) $B$3:$B$12 é a primeira coluna do intervalo e $B3 é a primeira célula dessa coluna;
(2) $C$3:$C$12 é a segunda coluna do intervalo e $C3 é a primeira célula dessa coluna;
(3) $D$3:$D$12 é a terceira coluna do intervalo e $D3 é a primeira célula dessa coluna;
(4) Se o seu intervalo contiver mais colunas, pode adicionar sucessivamente as referências dessas colunas e as respetivas primeiras células à fórmula.

4. Na caixa de diálogo Definir formato de célula, especifique a cor de destaque, o tipo de letra, as bordas das células, etc., conforme necessário, e clique sucessivamente nos botões OK para guardar a definição.

Até agora, os Linha duplicada foram identificados e destacados no Selecionar intervalo. Veja a captura de ecrã:

Poderá ter notado que todos os duplicados são identificados ou destacados com os métodos acima. Por vezes, pretende ver apenas os itens duplicados, excluindo as primeiras ocorrências. Pode realizar esta tarefa utilizando a funcionalidade Usar Formatação Condicional e uma fórmula diferente.

1. Selecione a coluna que inclui o cabeçalho.
Dicas: Se pretender aplicar um Intervalo de Linha Destacada com base em valores duplicados numa coluna — excluindo as primeiras ocorrências — selecione o intervalo sem a linha do cabeçalho.

2. Clique em Base > Usar Formatação Condicional > Nova Regra.

3. Na caixa de diálogo Nova Regra de Formatação:
(1) Clique para destacar a opção Utilizar uma fórmula para determinar quais as células a formatar;
(2) Na caixa Formatar valores em que esta fórmula é verdadeira, introduza a fórmula =COUNTIF($C$3:$C3, C3)>1;
Dicas: Para destacar um intervalo de linhas com base em duplicados numa coluna, introduza a fórmula =COUNTIF($C$3:$C3, $C3)>1.
(3) Clique no botão Formatar.

4. Na caixa de diálogo Definir formato de célula que surge, especifique a cor de destaque, o tipo de letra, as bordas das células, etc., conforme necessário, e clique nos botões OK para guardar as definições.

Em seguida, verá as células duplicadas, exceto as primeiras ocorrências na coluna selecionada (ou linhas com base em duplicados na coluna especificada), destacadas. Veja a captura de ecrã:

Quando destacamos células ou linhas duplicadas com a funcionalidade Usar Formatação Condicional, todos os duplicados são assinalados com a mesma cor. No entanto, se diferentes conjuntos de valores duplicados forem destacados com cores distintas, torna-se muito mais fácil ler e identificar as diferenças. Aqui, um código VBA pode ajudá-lo a realizar esta tarefa no Excel.

1. Prima em simultâneo as teclas Alt+F11 para abrir a janela Microsoft Visual Basic for Applications.

2. Na janela, clique em Inserir > Módulo e cole o código seguinte na nova janela do módulo.

VBA: Destacar células duplicadas com cores diferentes:

Sub HighlightDuplicatesInDifferentColors()
'Update by Extendoffice 20201013
Dim xURg, xRg, xFRg, xRgPre As Range
Dim xAddress As String
Dim xDt As Object
Dim xFNum, xCInt As Long
Dim xBol As Boolean
Dim xWs As Worksheet
Dim xSArr
Set xRg = Application.ActiveWindow.RangeSelection
If xRg.Count > 1 Then
xAddress = xRg.AddressLocal
Else
xAddress = xRg.Worksheet.UsedRange.AddressLocal
End If
On Error Resume Next
Set xURg = Application.InputBox("Select range:", "Kutools for Excel", xAddress, , , , , 8)
If xURg Is Nothing Then Exit Sub
Set xURg = Intersect(xURg.Worksheet.UsedRange, xURg)
Set xDt = CreateObject("scripting.dictionary")
Set xWs = xURg.Worksheet
xCInt = 5
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If Not IsError(xFRg) Then
If xFRg.Value <> "" Then
If xDt.exists(xFRg.Text) Then
xSArr = Split(xDt(xFRg.Text), ";")
If xSArr(1) = "Only" Then
xCInt = xCInt + 1
xSArr(1) = xCInt
Set xRgPre = xWs.Range(xSArr(0))
xRgPre.Interior.ColorIndex = xCInt
xDt(xFRg.Text) = xSArr(0) & ";" & xSArr(1)
End If
xFRg.Interior.ColorIndex = xSArr(1)

Else
xDt(xFRg.Text) = xFRg.Address & ";Only"
End If

End If
End If
Next
xURg.Worksheet.Active
xURg.Select
Application.ScreenUpdating = xBol
End Sub

3. Prima a tecla F5 ou clique no ícone Executar para executar este VBA.

4. Na caixa de diálogo do Kutools para Excel que surge, selecione a coluna em que pretende destacar células duplicadas com cores diferentes e clique no botão OK.

Cada série de Valores Duplicados será agora destacada com uma cor distinta.

A primeira ferramenta que irei apresentar é a funcionalidade Selecionar Células Duplicadas/Únicas, fornecida pelo Kutools para Excel. Esta funcionalidade permite identificar células únicas ou duplicadas de forma rápida e fácil.

1. Selecione a coluna ou o intervalo onde pretende identificar e destacar células duplicadas.

2. Clique em Kutools > Selecionar > Selecionar Células Duplicadas/Únicas.

3. Na caixa de diálogo Selecionar Células Duplicadas/Únicas, marque as opções conforme necessário e clique no botão Ok para concluir a operação.

Notas: Na caixa de diálogo Selecionar Células Duplicadas/Únicas,
(1) Para selecionar ou destacar todos os duplicados exceto as primeiras ocorrências, marque a opção Valores Duplicados (Excluir a primeira correspondência). Caso contrário, marque a opção Valores Duplicados (Incluir a primeira correspondência).
(2) Para destacar os duplicados, assinale a opção Preencher Cor de Fundo e especifique uma cor de destaque conforme necessário.
(3) Para selecionar ou destacar o intervalo de linhas com base em duplicados na coluna selecionada, assinale a opção Selecionar Linha Inteira.
(4) Para selecionar ou destacar Valores Duplicados com diferenciação entre maiúsculas e minúsculas, assinale a opção Diferenciar Maiúsculas de Minúsculas.

Kutools para Excel oferece também uma ferramenta extraordinária – Comparar Células – que nos ajuda a encontrar e destacar facilmente células duplicadas em duas colunas.

1. Clique em Kutools > Comparar Células para abrir a caixa de diálogo Selecionar Células Iguais/Diferentes.

2. Na caixa de diálogo Selecionar Células Iguais/Diferentes, especifique as duas colunas nas caixas Encontrar Valores em e De Acordo com, marque a opção Valores Iguais e selecione outras opções conforme necessário.

Notas:
(1) Se precisar de localizar Linha duplicada, assinale a opção Cada linha; e para encontrar células duplicadas, assinale a opção Célula únicana secção Baseado em;
(2) Assinale a opção Preencher Cor de Fundoe especifique uma cor de destaque se precisar de realçar os Linha duplicada ou as células;
(3) Assinale a opção Selecionar Linha Inteirase precisar de selecionar ou realçar os Linha inteira com base nos duplicados;
(4) Assinale a opção Diferenciar Maiúsculas de Minúsculasse pretender localizar ou realçar duplicados sensíveis a maiúsculas/minúsculas.

3. Clique sucessivamente nos botões OK para concluir as definições.

Em seguida, verá que os duplicados na coluna especificada na caixa Localizar valores em são identificados e realçados.

Este método orienta-o a identificar e realçar células duplicadas com uma regra de Usar Formatação Condicional e, posteriormente, filtrar facilmente por essa cor de destaque no Excel.

1. Utilize a formatação condicional para localizar e realçar duplicados na coluna especificada. ()Clique para ver como)

2. Clique no cabeçalho da coluna pretendida e, em seguida, selecione Dados > Filtrar.

3. Em seguida, clique no ícone de filtro no cabeçalho da coluna, selecione Filtrar por Cor e, depois, escolha a cor de destaque especificada na lista suspensa Usar Formatação Condicional. Veja a captura de ecrã:

Em seguida, verá apenas as linhas com células duplicadas filtradas. Veja a captura de ecrã:

Alternativamente, também podemos Marcar Duplicatas com uma fórmula numa coluna auxiliar e, depois, filtrar facilmente os duplicados com base nessa coluna auxiliar no Excel.

1. Além dos dados originais, adicione uma coluna auxiliar e introduza Duplicado como cabeçalho da coluna.

2. Selecione a primeira célula vazia sob o cabeçalho da coluna, introduza a fórmula =IF(COUNTIF($C$3:$C$12,C3)>1,«Duplicate»,«») e arraste a alça de autorreenchimento dessa célula para copiar a fórmula para as restantes células.

Notas: Na fórmula acima, $C$3:$C$12 é a coluna que contém os dados duplicados e C3 é a primeira célula (excluindo a célula do cabeçalho) nessa coluna.

3. Clique no cabeçalho – Duplicados e, em seguida, clique em Dados > Filtrar.

4. Em seguida, clique no ícone de filtro no cabeçalho da coluna, selecione apenas Duplicado e clique no botão OK. Veja a captura de ecrã:

Até agora, apenas as linhas com valores duplicados foram filtradas. Veja a captura de ecrã:

De um modo geral, podemos aplicar a função =CONTAR.SE(intervalo; critério) para contar o número total de ocorrências de um determinado valor num intervalo limitado. Por exemplo, para contar quantas vezes «Apple» aparece na lista A2:A10, basta utilizar a fórmula =COUNTIF(A2:A10, «Apple») e obter imediatamente o número de ocorrências desse valor duplicado.

Contudo, a fórmula =CONTAR.SE(intervalo; critério) conta apenas o valor duplicado especificado. E se pretendermos contar valores duplicados com dois ou mais critérios? E se for necessário contar duplicados sensíveis a maiúsculas/minúsculas com critérios? Os métodos abaixo ajudam-no a resolver estes problemas.

Podemos aplicar uma fórmula matricial para contar Valores Duplicados sensíveis a maiúsculas/minúsculas com critérios no Excel. Por exemplo, para contar quantas vezes o valor «Apple» aparece na lista B2:B21 com Diferenciar Maiúsculas de Minúsculas, pode fazê-lo da seguinte forma:

1. Selecione uma célula vazia.

2. Introduza a fórmula =SOMA(--EXATO(B2:B20;E2)).

3. Prima Ctrl + Shift + Enter para obter o resultado da contagem.

Notas: Na fórmula matricial,
(1)B2:B20é a coluna na qual irá contar os duplicados.
(2)E2é a célula que contém o valor especificado cujo número de ocorrências pretende contar. Pode substituir a referência da célula pelo valor entre aspas, por exemplo «Apple».

Por vezes, poderá precisar de contar duplicados com base em dois ou mais critérios. Nesse caso, pode utilizar a função CONTAR.SES para o fazer.
Por exemplo, imagine que tem uma tabela de vendas de frutas, como mostra a captura de ecrã abaixo. Pretende-se contar quantas vezes a maçã foi vendida em 7/5/2020 com um valor de vendas superior a 300. Pode contar os duplicados com estes critérios da seguinte forma:

1. Selecione uma célula vazia.

2. Introduza a fórmula =COUNTIFS(B3:B20,G4,C3:C20,G3,D3:D20,«>300»).

3. Prima a tecla Enter para obter o resultado da contagem.

Notas: Na fórmula acima,
(1) B3:B20 é a coluna (primeira) de datas e G4 é o critério de data;
(2) C3:C20 é a coluna (segunda) de frutas e G3 é o critério de fruta;
(3) D3:D20 é a coluna (terceira) de montantes e «>300» é o critério de montante.
(4) Se a sua tabela tiver mais colunas e critérios, pode adicionar as referências das colunas e os respetivos critérios.

Para contar todos os duplicados numa coluna, exceto a primeira ocorrência, proceda da seguinte forma:

1. Selecione uma célula vazia junto à coluna.

2. Introduza a fórmula =IF(COUNTIF($B$3:B3,B3)>1,«YES»,«») e, em seguida, arraste a alça de autorreenchimento para baixo para copiar a fórmula para as restantes células.

Notas: Na fórmula acima,
(1) $B$3:B3 é o intervalo em que são contados os duplicados. Em $B$3:B3, B3 ajusta-se automaticamente ao copiar a fórmula para outras células.
(2) B3 é a primeira célula da coluna especificada.
(3) Esta fórmula devolve «SIM» ou deixa em branco. «SIM» indica que o valor correspondente é duplicado, enquanto o campo em branco significa que é único.

Assim, todos os duplicados na coluna especificada são identificados. Podemos contar os resultados da fórmula para obter o número total de duplicados.

3. Selecione uma célula vazia.

4. Introduza a fórmula =COUNTIF(C3:C16,«YES») e prima a tecla Enter.

Notas: Na fórmula acima,
(1) C3:C16 é o intervalo ao qual aplicámos a fórmula Marcar Duplicatas no passo anterior.
(2) YES é o valor devolvido pela última fórmula.

Obtemos então o número total de Valores Duplicados na coluna especificada. O número total de duplicados não inclui as primeiras ocorrências.

Para contar o número de todos os duplicados, incluindo as primeiras ocorrências no Excel, pode aplicar uma fórmula matricial para o conseguir.

1. Selecione uma célula vazia.

2. Introduza a fórmula =ROWS(B3:B16)-SUM(IF(COUNTIF(B3:B16,B3:B16)=1,1,0)).

3. Prima Ctrl + Shift + Enter para obter o resultado da contagem.

Notas: Na fórmula acima, B3:B16 é a coluna especificada em que pretendemos contar os duplicados, incluindo as primeiras ocorrências.

Para simplificar o seu trabalho e libertar-se da tarefa de memorizar longas e aborrecidas fórmulas, experimente a funcionalidade Selecionar Células Duplicadas/Únicas, do Kutools para Excel, que lhe permite contar rapidamente o número de valores duplicados na lista ou coluna especificada — incluindo ou excluindo as primeiras ocorrências.

1. Selecione a coluna onde pretende contar o número de valores duplicados e clique em Kutools > Selecionar > Selecionar Células Duplicadas/Únicas.

2. Na caixa de diálogo Selecionar Células Duplicadas/Únicas, marque a opção Valores Duplicados (Excluir a primeira correspondência) ou Valores Duplicados (Incluir a primeira correspondência), conforme necessário, e clique no botão Ok.

3. Em seguida, todos os valores duplicados são selecionados (incluindo ou excluindo as primeiras ocorrências) e, ao mesmo tempo, surge uma caixa de diálogo a indicar quantas células foram selecionadas. Consulte a imagem acima.

Imagine que pretende comparar duas Lista de nomes e contar o número de duplicados entre elas. Como pode resolver este problema rapidamente? Também podemos resolvê-lo com uma fórmula no Excel.

1. Selecione uma célula vazia.

2. Introduza a fórmula =SUMPRODUCT(--(ISNUMBER(MATCH(B3:B12,D3:D18,0)))).

3. Prima a tecla Enter.

Notas: Na fórmula acima,
(1) B3:B12 é a primeira coluna de nomes em que irá contar os duplicados.
(2) D3:D18 é a segunda coluna de nomes com base na qual irá contar os duplicados.

Alternativamente, podemos utilizar um suplemento de terceiros, Kutools para Excel, para contar facilmente o número total de células duplicadas entre duas colunas.

1. Clique em Kutools > Selecionar > Selecionar Células Iguais/Diferentes.

2. Na caixa de diálogo Selecionar Células Iguais e Diferentes,
(1) Especifique as duas colunas nos campos Procurar valores em e De acordo com, respetivamente.
(2) Marque a opção Célula única.
(3) Marque a opção Valores iguais.

4. Clique no botão Ok.

De seguida, todas as células duplicadas na primeira coluna são selecionadas e, ao mesmo tempo, surge uma caixa de diálogo a indicar quantas células duplicadas foram selecionadas. Consulte a imagem:

Notas: Esta funcionalidade conta o número total de valores duplicados na coluna que especificou no campo Procurar valores em na caixa de diálogo Selecionar Células Iguais/Diferentes. Se precisar de contar o número total de valores duplicados na segunda coluna, aplique novamente a funcionalidade Selecionar Células Iguais/Diferentes, especificando a segunda coluna no campo Procurar valores em.

Pode contar rapidamente cada valor duplicado uma única vez com uma fórmula como a seguinte:

1. Selecione uma célula vazia.

2. Introduza a fórmula =SUMPRODUCT((C3:C19<>«»)/COUNTIF(C3:C19,C3:C19&«»)).

3. Prima a tecla Enter para obter o resultado da contagem.

Notas: Na fórmula acima, C3:C19 é a coluna especificada em que pretende contar cada valor duplicado apenas uma vez.

Ao contar uma lista, pode contar cada valor duplicado uma única vez com Diferenciar Maiúsculas de Minúsculas, aplicando uma fórmula matricial no Excel.

1. Selecione uma célula vazia.

2. Introduza a fórmula matricial =SUM(IFERROR(1/IF(C3:C19<>«», FREQUENCY(IF(EXACT(C3:C19, TRANSPOSE(C3:C19)), MATCH(ROW(C3:C19), ROW(C3:C19)), «»), MATCH(ROW(C3:C19), ROW(C3:C19))), 0), 0)).

3. Prima em simultâneo as teclas Ctrl+Shift+Enter para obter o resultado da contagem.

Notas: Na fórmula matricial acima, C3:C19 é a coluna especificada em que cada série de duplicados será contabilizada apenas uma vez, diferenciando maiúsculas de minúsculas.

Se tiver o Kutools para Excel instalado, também pode utilizar a sua funcionalidade Contar células com valores únicos para contar rapidamente cada série de Valores Duplicados uma única vez no Excel.

1. Selecione uma célula vazia.

2. Clique em Kutools > Assistente de Fórmulas > Estatísticas > Contar o número de valores únicos num intervalo (incluindo o primeiro duplicado).

3. Na caixa de diálogo Assistente de Fórmulas, especifique no campo Intervalo a coluna em que pretende contar os duplicados uma única vez e clique no botão Ok.

O resultado da contagem é imediatamente introduzido na célula selecionada.

Podemos utilizar a funcionalidade Subtotal para contar cada série de valores duplicados numa coluna no Excel.

1. Selecione a coluna em que pretende contar cada série de valores duplicados e clique em Dados > Ascendente ou Ordenar de Z a A.

2. Na caixa de diálogo emergente Aviso de Ordenação, marque a opção Expandir a seleção e clique no botão Ordenar.

Em seguida, verá que a seleção foi ordenada pela Valores Duplicados da coluna especificada.

3. Mantenha a seleção ativa e clique em Dados > Subtotal.

4. Na caixa de diálogo Subtotal:
(1) Selecione a coluna pretendida na lista suspensa Em cada alteração em;
(2) Escolha Contar na lista suspensa Utilizar função;
(3) Marque apenas a coluna desejada na lista Adicionar subtotal a;
(4) Clique no botão OK.

Em seguida, verá que cada série de valores duplicados foi contabilizada e o resultado da contagem foi adicionado abaixo de cada série de valores duplicados, conforme ilustrado na imagem anterior.

Também podemos criar uma Tabela Dinâmica para contar rapidamente cada série de valores duplicados de forma fácil no Excel.

1. Selecione o intervalo que contém a coluna especificada e clique em Inserir > Tabela Dinâmica.

2. Na caixa de diálogo Criar Tabela Dinâmica, indique onde pretende colocar a nova Tabela Dinâmica e clique no botão OK.

3. No painel Campos da Tabela Dinâmica, arraste a coluna pretendida simultaneamente para as secções Linhas e Valores. Em seguida, verá que cada conjunto de valores duplicados na coluna especificada foi contabilizado em massa. Consulte a imagem:

Se já tiver instalado o Kutools para Excel, poderá utilizar a sua funcionalidade fácil de usar Mesclar Linhas Avançado para contar rapidamente cada série de valores duplicados na coluna especificada.

Notas: A funcionalidade Linhas Combinadas Avançadas irá modificar o intervalo selecionado e remover linhas com base nos valores duplicados na coluna-chave especificada. Para proteger os seus dados, recomendamos que faça uma cópia de segurança ou copie os dados para outro local antes de executar as operações seguintes.

1. Adicione uma coluna em branco à direita do intervalo de dados original e dê-lhe o nome Contar.

2. Selecione em conjunto o intervalo de dados original e a nova coluna e clique em Kutools > Mesclar e Dividir > Mesclar Linhas Avançado.

3. Na caixa de diálogo Mesclar Linhas Avançado:
(1) Clique para selecionar a coluna especificada cujas séries de valores duplicados pretende contar e, em seguida, clique em Chave Primária.
(2) Clique para selecionar a nova coluna (Contar) e, depois, clique em Calcular > Contar.
(3) Especifique os tipos de combinação ou cálculo para outras colunas, se necessário.
(4) Clique no botão Ok.

Em seguida, verá que cada série de valores duplicados na coluna especificada foi contabilizada em massa. Consulte a imagem:

Imagine que existe uma lista de frutas numa coluna. Nessa lista, algumas frutas aparecem várias vezes. Pretende agora marcar cada fruta duplicada pela ordem em que surge. Como resolver este problema? Esta secção apresenta uma fórmula para o fazer facilmente no Excel.

1. Adicione uma coluna em branco à direita dos dados originais.

2. Introduza a fórmula =IF(COUNTIF($C$3:$C$14,C3)>1,COUNTIF(C$3:C3,C3),«») na primeira célula da coluna em branco adicionada.

3. Arraste a alça de preenchimento automático desta célula com fórmula para copiar a fórmula para outras células.

Notas: Na fórmula acima,
(1) $C$3:$C$14 é a coluna especificada em que pretende contar os valores duplicados por ordem.
(2) C3 é a primeira célula da coluna especificada.
(3) Se o valor correspondente for duplicado, esta fórmula devolve um número de sequência — 1, 2, 3… — consoante a ordem de ocorrência; se for único, devolve uma célula vazia.

Pode utilizar a funcionalidade Remover duplicatas para eliminar todos os valores duplicados, mantendo apenas a primeira ocorrência.

1. Selecione a coluna em que pretende remover todos os valores duplicados, exceto as primeiras ocorrências.

2. Clique em Dados > Remover duplicatas.

3. Na caixa de diálogo Aviso do Remover Duplicatas, marque a opção Continuar com a seleção atual e clique no botão Remover duplicatas.

Dicas: Para remover linhas com base nos valores duplicados na seleção, marque a opção Expandir a seleção.

4. Na caixa de diálogo Remover Duplicatas, selecione apenas a coluna especificada e clique no botão OK.

Dicas: Se tiver selecionado a opção Expandir a seleção no passo anterior, todas as colunas serão listadas aqui. Mesmo assim, deverá marcar apenas a coluna especificada.

5. Em seguida, surge uma caixa de diálogo a indicar quantos valores duplicados foram removidos. Clique no botão OK para fechá-la.

Pode também utilizar a funcionalidade Filtro Avançado para remover facilmente todos os valores duplicados da coluna especificada.

1. Clique em Dados > Avançado.

2. Na caixa de diálogo Filtro Avançado,
(1) Marque a opção Copiar para outra localização;
(2) Na caixa Intervalo da lista, selecione a coluna específica da qual pretende remover os valores duplicados;
(3) Na caixa Copiar para, indique o intervalo onde deseja colar a coluna;
(4) Assinale a opção Apenas registos únicos;
(5) Clique no botão OK.

Em seguida, verá que a coluna especificada foi colada na Intervalo limitado com todos os Valores Duplicados removidos, exceto as primeiras ocorrências. Consulte a imagem:

Também pode utilizar um código VBA para remover rapidamente os Valores Duplicados exceto as primeiras ocorrências de uma coluna no Excel.

1. Prima as teclas Alt + F11 para abrir a janela do Microsoft Visual Basic for Applications.

2. Clique em Inserir > Módulo e, depois, cole o código VBA abaixo na nova janela do módulo.

VBA: Remover valores duplicados exceto as primeiras ocorrências

Sub ExtendOffice_RemoveAllDeplicate()
Dim xRg As Range
Dim xURg, xFRg, xFFRg As Range
Dim xI, xFNum, xFFNum As Integer
Dim xDc As Object
Dim xDc_keys
Dim xBol As Boolean
Dim xStr As String
Dim xWs As Worksheet
Dim xURgAddress As String
On Error Resume Next
Set xRg = Application.InputBox("Select range:", "Kutools for Excel", "", , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xURg = Intersect(xRg.Worksheet.UsedRange, xRg)
Set xWs = xURg.Worksheet
Set xDc = CreateObject("scripting.dictionary")
xURgAddress = xURg.Address
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If (Not IsError(xFRg)) Then
If xFRg.Value <> "" And (Not IsError(xFRg)) Then
For xFFNum = xFNum + 1 To xURg.Count
Set xFFRg = xURg.Item(xFFNum)
If Not IsError(xFFRg) Then
If xFFRg.Value = xFRg.Value Then
xDc(xFFRg.Address) = ""
End If
End If
Next
End If
End If
Next
xStr = ""
xDc_keys = xDc.Keys

For xI = 1 To UBound(xDc_keys)
If xStr = "" Then
xStr = xDc_keys(xI)
Set xURg = xWs.Range(xStr)
Else
xStr = xStr & "," & xDc_keys(xI)
Set xURg = Application.Union(xWs.Range(xDc_keys(xI)), xURg)
End If
Next
Debug.Print xStr
xWs.Activate
xURg.Select
Selection.Delete Shift:=xlUp
xWs.Range(xURgAddress).Select
Application.ScreenUpdating = xBol
End Sub

3. Prima a tecla F5 ou clique no botão Executar para executar este VBA.

4. Na caixa de diálogo que surge, especifique o intervalo do qual pretende remover valores duplicados e clique no botão OK.

Todos os Valores Duplicados, exceto as primeiras ocorrências, são removidos imediatamente do Intervalo limitado.

Nota: Este código VBA é Diferenciar Maiúsculas de Minúsculas.

Podemos destacar todas as Valores Duplicados, incluindo as primeiras ocorrências, numa coluna ou lista com uma regra de Usar Formatação Condicional e, em seguida, filtrar todas as Valores Duplicados pela cor de destaque. Depois disso, podemos selecionar todas as células duplicadas filtradas e removê-las em massa.

1. Utilize a formatação condicional para destacar valores duplicados. ()Clique para ver como)

2. Selecione a coluna da qual pretende remover os valores duplicados (incluindo as primeiras ocorrências) e clique em Dados > Filtrar.

3. Clique no ícone de filtro no cabeçalho da coluna pretendida. No menu pendente, selecione Filtrar por Cor e, em seguida, escolha a cor de destaque no submenu.

Então, todas as Valores Duplicados são filtradas.

4. Selecione todas as células filtradas, clique com o botão direito e escolha Excluir Linha no menu de contexto. Em seguida, na caixa de diálogo de confirmação que surge, clique no botão OK para avançar.

5. Em seguida, todos os Valores Duplicados são removidos em massa. Mantenha a lista filtrada selecionada e clique novamente em Filtrar > Dados para cancelar o filtro.

Até agora, verá que todas as células duplicadas, incluindo as primeiras ocorrências, foram removidas em massa, restando apenas valores únicos.

Notas: Este método remove linhas com base em valores duplicados na coluna especificada.

Também podemos aplicar uma fórmula para identificar Valores Duplicados, incluindo as primeiras ocorrências, numa coluna auxiliar, filtrar depois as Valores Duplicados pelos resultados da fórmula e, finalmente, remover essas Valores Duplicados filtradas em massa.

1. Adicione uma coluna auxiliar ao lado da coluna especificada, introduza a fórmula =CONTAR.SE($B$3:$B$11;B3) na primeira célula da coluna auxiliar e, em seguida, arraste a alça de autorreenchimento para baixo para copiar a fórmula para as restantes células. Veja a captura de ecrã:

Nota: Na fórmula acima, $B$3:$B$11 é a coluna especificada da qual irá remover valores duplicados e B3 é a primeira célula dessa mesma coluna.

2. Selecione a coluna auxiliar e clique em Dados > Filtrar.

3. Clique no ícone de filtro no cabeçalho da coluna auxiliar, depois selecione no menu pendente todos os valores exceto 1 e clique no botão OK. Veja a captura de ecrã:

4. Agora, todos os valores duplicados estão filtrados. Selecione as células filtradas na coluna auxiliar, clique com o botão direito e escolha Excluir Linha no menu de contexto.

5. Na caixa de diálogo de confirmação que surge, clique no botão OK para avançar.

6. Agora, todos os Valores Duplicados e as respetivas linhas foram removidos em massa. Para cancelar o filtro, clique novamente em Dados > Filtrar.

Verá então que todas as Valores Duplicados, incluindo as primeiras ocorrências, foram eliminadas em massa.

Se tiver o Kutools para Excel instalado, também pode utilizar a sua funcionalidade Selecionar Células Duplicadas/Únicas para selecionar e eliminar rapidamente os valores duplicados — incluindo ou excluindo as primeiras ocorrências — de forma fácil no Excel.

1. Selecione a coluna da qual pretende remover duplicatas.

2. Clique em Kutools > Selecionar > Selecionar Células Duplicadas/Únicas.

3. Na caixa de diálogo Selecionar Células Duplicadas/Únicas, marque a opção Valores Duplicados (Incluir a primeira correspondência) e clique no botão Ok.

Notas:
(1) Para selecionar e eliminar valores duplicados excluindo as primeiras ocorrências, marque a opção Valores Duplicados (Excluir a primeira correspondência).
(2) Para selecionar e eliminar linhas com base nos valores duplicados na coluna especificada, marque a opção Selecionar Linha Inteira.
(3) Para selecionar e eliminar valores duplicados com diferenciação entre maiúsculas e minúsculas, marque a opção Diferenciar Maiúsculas de Minúsculas.
(4) Para selecionar, destacar e eliminar células ou linhas duplicadas, marque as opções Preencher Cor de Fundo ou Preencher Cor da Fonte e especifique a cor de fundo ou da fonte conforme necessário.

4. Surge, de seguida, uma caixa de diálogo que indica quantas células foram selecionadas; clique no botão OK para fechá-la.

5. Clique com o botão direito nas células selecionadas e escolha Eliminar no menu de contexto.

6. Na caixa de diálogo Eliminar que surge, marque a opção Deslocar células para cima e clique no botão OK.

Até agora, todas as Valores Duplicados, incluindo as primeiras ocorrências, foram removidas em massa.

Na maioria dos casos, identificamos Valores Duplicados numa coluna e, em seguida, removemos as Linha inteira com base nos Valores Duplicados. Sinceramente, esta operação é bastante semelhante à remoção de Valores Duplicados de uma única coluna. Por conseguinte, podemos utilizar soluções semelhantes para Excluir linhas com base em duplicados na coluna especificada.

O primeiro método consiste em utilizar a funcionalidade incorporada Remover duplicatas para eliminar linhas duplicadas na coluna especificada. Basta selecionar o intervalo do qual pretende remover as linhas, clicar em Dados > Remover duplicatas para ativar a funcionalidade, marcar apenas a coluna pretendida na caixa de diálogo Remover duplicatas e, em seguida, clicar em OKpara concluir a operação.

Também pode utilizar a formatação condicional combinada com o filtro para remover linhas com base em valores duplicados na coluna especificada. Primeiro, destaque o intervalo de linhas com valores duplicados numa determinada coluna através de uma regra de formatação condicional ()clique para ver como). Depois, filtre o intervalo por cor. Em seguida, elimine facilmente todas as linhas filtradas. Por fim, limpe ou remova o filtro e verá que apenas as linhas com valores únicos na coluna especificada permanecem.

Alternativamente, pode adicionar uma coluna auxiliar e aplicar a fórmula =COUNTIF($C$3:$C$21,C3) para marcar duplicatas na coluna especificada. Depois, basta filtrar os valores superiores a 1 na coluna auxiliar e eliminar facilmente todas as linhas filtradas. Após remover o filtro, verá que apenas permanecem as linhas com valores únicos na coluna indicada.

O suplemento de terceiros Kutools para Excel também oferece uma funcionalidade extraordinariamente útil: Selecionar Células Duplicadas/Únicas. Com ela, pode selecionar rapidamente linhas com base em valores duplicados na coluna especificada e, de seguida, removê-las facilmente através do menu de clique direito.

A funcionalidade Mesclar Linhas Avançado do Kutools para Excel também permite remover rapidamente linhas com base em valores duplicados na coluna chave especificada.

Podemos adicionar uma coluna auxiliar e aplicar uma fórmula para identificar os Valores Duplicados entre duas colunas e, em seguida, filtrar e eliminar facilmente os Valores Duplicados.

1. Adicione uma coluna em branco junto à coluna especificada da qual irá remover os valores duplicados.

2. Na primeira célula da coluna auxiliar (excluindo a célula do cabeçalho), introduza a fórmula =IF(ISERROR(MATCH(C2,$A$2:$A$13,0)),«Unique»,«Duplicate») e, em seguida, arraste a alça de autorreenchimento para baixo para copiar a fórmula para as restantes células.

Notas: Na fórmula acima,
(1) C2 é a primeira célula na coluna especificada da qual irá remover valores duplicados;
(2) $A$2:$A$13 é a outra coluna com a qual precisamos comparar;
(3) esta fórmula devolve Duplicado se o valor correspondente for duplicado relativamente aos valores da outra coluna e devolve Único se for diferente dos valores dessa coluna.

3. Selecione a coluna auxiliar e clique em Dados > Filtro.

4. Clique no ícone de filtro no cabeçalho da coluna auxiliar e, no menu pendente, selecione apenas Duplicado e clique no botão OK.

5. Agora, todos os valores duplicados estão filtrados. Selecione as células filtradas, clique com o botão direito e escolha Excluir Linha no menu de contexto. Depois, clique em OK na caixa de diálogo de confirmação que surge.

6. Assim, todos os valores duplicados são removidos da coluna especificada. Para cancelar o filtro, clique novamente em Dados > Filtro.

Verá então que apenas os valores únicos permanecem na coluna especificada. Pode remover a coluna auxiliar conforme necessário.

Notas: Este método remove a linha inteira com base nos valores duplicados na coluna especificada.

Se tiver o Kutools para Excel instalado, pode utilizar a sua extraordinária funcionalidade Selecionar Células Iguais/Diferentes para selecionar rapidamente os valores duplicados entre duas colunas e, em seguida, removê-los facilmente.

1. Clique em Kutools > Selecionar > Selecionar Células Iguais/Diferentes para ativar esta funcionalidade.

2. Na caixa de diálogo Selecionar Células Iguais/Diferentes, especifique ambas as colunas nas caixas Procurar Valores em e De Acordo com, marque as opções Célula única e Mesmos valores e clique no botão Ok. Veja a imagem:

3. Serão então selecionados todos os valores duplicados correspondentes nas duas colunas, na primeira coluna (a que indicou na caixa)Procurar valor em). Clique no botão OK na caixa de diálogo que surge.

4. Pode premir a tecla Eliminar para remover diretamente estes valores duplicados ou clicar com o botão direito e selecionar Eliminar no menu de contexto.