Como contar valores únicos no Excel: O que você precisa saber

Se você trabalha com dados, a contagem de valores exclusivos é obrigatória, pois dados limpos produzem insights melhores. Neste guia, mostrarei a você como contar valores exclusivos no Excel. Também mostrarei a você casos especiais, como lidar com a diferenciação de maiúsculas e minúsculas ou como contar valores exclusivos com base em uma ou mais condições.

Como as ideias às vezes se confundem, vou dedicar algum tempo para desfazer qualquer mal-entendido que você possa ter sobre a diferença entre valores exclusivos e distintos no Excel e, caso você queira realizar uma contagem distinta, mostrarei como fazer isso no final deste artigo. 

Por fim, antes de começarmos, gostaria de salientar que esses são apenas alguns dos tipos de assuntos abordados em nosso curso de habilidades em Fundamentos do Excel, portanto, inscreva-se hoje mesmo para se tornar um especialista.

Uma observação sobre valores exclusivos vs. valores de mercado. Valores distintos no Excel

Às vezes, os termos valores exclusivos e valores distintos são usados de forma intercambiável, mas não são a mesma coisa. Então, aqui está uma rápida comparação para ajudar você a entender a diferença entre eles.

Valores distintos são os valores diferentes em um conjunto de dados, com as duplicatas removidas. Por exemplo, na lista A, A, B, C, C, D, os valores distintos são A, B, C, D.

Valores exclusivos, por outro lado, são valores que ocorrem apenas uma vez no conjunto de dados. Usando o mesmo exemplo, os valores exclusivos são B e D (porque A e C aparecem mais de uma vez).

Na primeira metade do artigo, vou me concentrar em diferentes maneiras de contar valores exclusivos no Excel. Mas, se você chegou aqui por acidente e está realmente querendo contar valores distintos, eu também o ajudarei se você quiser rolar para a seção posterior.

Métodos para contar valores únicos no Excel

Há duas maneiras comuns de contar valores exclusivos no Excel.

Método 1: Use a função UNIQUE() com o parâmetro correto

A maneira mais fácil de contar valores exclusivos é usar a função UNIQUE() e a função COUNTA(). A função UNIQUE() extrai todos os valores exclusivos de um intervalo e COUNTA() conta quantos valores exclusivos estão presentes. 

COUNTA(UNIQUE(range, false, true))

Por exemplo, apliquei a seguinte fórmula a um pequeno conjunto de dados e ela me forneceu valores exclusivos.

=COUNTA(UNIQUE(A2:A8, false, true))

Contar valores exclusivos usando as funções UNIQUE() e COUNTA(). Imagem do autor.

Se, em vez disso, você usasse essa sintaxe, =COUNTA(UNIQUE(A2:A8)), que não tem o terceiro parâmetro que definimos como TRUE, obteríamos como resultado uma contagem distinta, não exclusiva. Se você definir TRUE no terceiro argumento, a função retornará uma contagem exclusiva. Ambas as coisas estão na função UNIQUE(), portanto, pode ser um pouco confuso no início.

Método 2: Use SUM() com IF() e COUNTIF()

Se você não tiver certeza das nuances da função UNIQUE(), ou se não tiver o Excel 365, saiba que também podemos combinar SUM() com IF() e COUNTIF() para contar os valores exclusivos.

=SUM(IF(COUNTIF(range, range)=1,1,0))

Por exemplo, tenho dados no intervalo A2:A8 e quero contar os valores exclusivos, então escrevo a seguinte fórmula:

SUM(IF(COUNTIF(A2:A8, A2:A8)=1,1,0))

Aqui, COUNTIF() percorre a lista e verifica quantas vezes cada nome aparece. Se um nome aparecer apenas uma vez, ele será contado como 1. Se aparecer mais de uma vez, você receberá um número maior. Em seguida, o site IF() filtra esses resultados para manter os 1s como estão, mas transforma todo o resto em 0. Por fim, SUM() soma todos os 1s e nos dá a contagem total de valores exclusivos.

Conte valores exclusivos usando SUM(), IF() e COUNTIF(). Imagem do autor.

Contagem de valores numéricos e de texto exclusivos

Às vezes, nosso conjunto de dados contém tipos de dados mistos e precisamos analisá-los de forma independente. Isso pode parecer um pouco assustador no início, mas é possível no Excel. Vamos dar uma olhada em dois métodos: um para contar valores de texto exclusivos e outro para números exclusivos.

Contagem de valores de texto exclusivos

Se você quiser contar valores de texto exclusivos, combine as funções ISTEXT(), COUNTIF() e SUM(). ISTEXT() verifica se um valor é um texto, enquanto COUNTIF() conta quantas vezes cada valor aparece e SUM() totaliza as entradas de texto distintas. 

Apliquei a seguinte fórmula em um exemplo de dados e ela me mostrou instantaneamente quantos valores de texto exclusivos estão presentes.

=SUM(IF(ISTEXT(A2:A9)*COUNTIF(A2:A9,A2:A9)=1,1,0))

Contar valores de texto exclusivos. Imagem do autor.

Contagem de valores numéricos exclusivos

Se você quiser contar números exclusivos em vez de texto, use a mesma fórmula, mas substitua ISTEXT() por ISNUMBER(). ISNUMBER() considerará apenas valores numéricos, enquanto COUNTIF() e SUM() tratam da exclusividade. Ajustei a fórmula acima com os mesmos dados e agora ela mostra apenas o número de valores numéricos exclusivos:

=SUM(IF(ISNUMBER(A2:A9)*COUNTIF(A2:A9,A2:A9)=1,1,0))

Contar valores numéricos exclusivos. Imagem do autor.

Contagem de valores únicos com condições e critérios

Agora que já abordamos os métodos básicos de contagem de valores exclusivos, vamos explorar algumas técnicas avançadas.

Contar valores exclusivos com base em condições

Para contar os valores exclusivos com base em uma condição específica, uso esta sintaxe:

=IFERROR(ROWS(UNIQUE(FILTER(range, criteria_range=criteria))), 0)

Nessa fórmula, FILTER() filtra o intervalo com base em critérios específicos, UNIQUE() remove duplicatas, ROWS() conta os resultados e IFERROR() evita erros retornando Not Found se nenhuma correspondência for encontrada.

Digamos que eu tenha uma lista de funcionários e seus departamentos, e preciso contar quantos funcionários únicos trabalham em um departamento específico. Usarei a seguinte fórmula:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A20, B2:B20=F1))), "Not Found")

Aqui, A2:A10 representa o intervalo de nomes de funcionários que desejo filtrar e B2:B10=F1 verifica quais funcionários pertencem ao departamento, que está escrito em F1.

Contar valores exclusivos com uma condição. Imagem do autor.

Contar linhas exclusivas com base em várias colunas

Se eu quiser contar os funcionários únicos em um departamento específico cujos salários sejam inferiores a US$ 50.000, usarei esta fórmula:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C10>F2)))), "Not Found")

Aqui, A2:A10 representa o intervalo de nomes de funcionários, B2:B10=F1 verifica quais funcionários pertencem ao departamento escrito em F1 e C2:C10>F2 verifica se o salário é menor do que o valor fornecido em F2.

Contar valores exclusivos com vários critérios. Imagem do autor.

Contagens únicas que diferenciam maiúsculas de minúsculas

Por padrão, o Excel diferencia maiúsculas de minúsculas. Por exemplo, Apple e APPLE são considerados diferentes. Para identificar esses casos, eu crio uma coluna auxiliar com a seguinte fórmula:

=IF(SUM((--EXACT($A$2:$A$11,A2)))=1,"Unique","Duplicate")

E agora, para obter a contagem de valores exclusivos, uso a função COUNTIF() da seguinte forma:

=COUNTIF(B2:B11,"Unique")

Contar valores exclusivos com distinção entre maiúsculas e minúsculas. Imagem do autor.

Práticas recomendadas para contagem de valores únicos no Excel

Aqui estão algumas das minhas práticas favoritas que ajudarão você a manter as coisas precisas e eficientes:

• Certifique-se de que não haja espaços extras ou formatação inconsistente nos dados. Caso contrário, você receberá uma mensagem de erro.
• Seu intervalo de dados não deve incluir células vazias, pois elas podem atrapalhar o resultado.
• Se os dados mudarem com frequência, use tabelas dinâmicas ou funções de matriz dinâmica para manter tudo atualizado em tempo real. 

Como contar valores distintos em vez disso

Agora, como uma seção final, caso você realmente quisesse contar distintos em vez disso, mostrarei a você vários métodos para contar valores distintos.

Método 1: Use as funções COUNTIF() e SUM()

Para contar valores distintos no Excel, você pode combinar as funções COUNTIF() e SUM(). A função COUNTIF() verifica quantas vezes cada valor aparece em um determinado intervalo. Em seguida, SUM() soma os valores retornados por COUNTIF(), fornecendo o número total de entradas distintas. 

=SUM(1/COUNTIF(range, range))

Digamos que eu queira encontrar os valores distintos no intervalo A2:A8. Eu insiro a seguinte fórmula:

=SUM(1/COUNTIF(A2:A8, A2:A8))

Aqui, a função COUNTIF() verifica quantas vezes cada valor aparece em uma lista.

1/COUNTIF(A2:A8, A2:A8) divide 1 pelo número de vezes que cada valor aparece. Por exemplo, se um número aparecer duas vezes, cada instância se tornará 0,5 (1/2). Se aparecer três vezes, você terá 0,33 (1/3). 

Como não queremos essas frações, podemos usar o SUM() ou SUMPRODUCT() para somar todos os valores. As frações de cada duplicata se combinam em 1, e os valores distintos permanecem 1, pois 1/1 = 1:

=SUMPRODUCT(1/COUNTIF(A2:A8, A2:A8))

Contar valores distintos usando as funções COUNTIF() e SUM(). Imagem do autor.

Método 2: Use tabelas dinâmicas para contagens distintas

O Excel 2013 e o Excel 2016 incluíram um recurso interno para contar valores distintos em uma tabela dinâmica. Para usar isso, primeiro você precisa criar uma tabela dinâmica selecionando os dados: Vá para Insert e selecione PivotTable. Será exibida uma caixa de diálogo - nessa caixa de diálogo, escolha Planilha existente e marque a caixa Adicionar esses dados ao modelo de dados.

Criar uma tabela dinâmica. Imagem do autor.

Em seguida, arraste a coluna que você deseja para o campo Values (Valores ). No meu caso, arrasto a coluna Fruits (Frutas ) porque quero contar seus valores distintos. Em seguida, clique em Value Field Settings (Configurações do campo de valor ) no menu suspenso. Aparecerá uma janela pop-up. Nela, selecione Distinct Count (Contagem distinta ). Você pode até dar a essa coluna um nome personalizado, mas isso é opcional.

Contar valores distintos usando a tabela dinâmica. Imagem do autor.

Método 3: Aplicar filtros avançados

Se você não gosta de trabalhar com fórmulas, use os filtros avançados. 

Neste exemplo, tenho um conjunto de dados e quero encontrar valores distintos usando filtros avançados. Agora, para isso, seleciono o intervalo no qual quero encontrar o valor específico. Em seguida, vou para a guia Data (Dados ) e clico em Advanced (Avançado). 

Agora, na caixa de diálogo Filtro avançado, seleciono Copiar para outro local e, no campo Copiar para, insiro a célula de destino onde quero que minha lista apareça. Em seguida, marque a caixa Unique Records Only (Somente registros exclusivos ) e clique em OK.

Quando você tiver uma lista de valores exclusivos, calcularei a contagem desses valores exclusivos usando a função ROWS():

=ROWS(D2:D5)

Embora a opção na imagem abaixo leia apenas registros únicos, na verdade ela extrai valores distintos. 

Contar valores distintos usando uma opção de filtro avançado. Imagem do autor.

Método 4: Implementar macros VBA

Se você quiser contar valores distintos repetidamente, use o VBA (Visual Basic for Applications), em que escrevemos o código uma vez e ele automatiza todo o processo toda vez que chamamos a função com seu nome.

Veja como você pode usar isso: pressione Alt + F11 e vá para a opção Module (Módulo ) na guia Insert (Inserir ). Um novo módulo será exibido, e você poderá colar seu código aqui. 

Este é o código que criei para contar os valores distintos:

Function CountUnique(rng As Range) As Integer
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    Dim cell As Range
    For Each cell In rng
        If Not dict.exists(cell.Value) And cell.Value <> "" 

Then,
            dict.Add cell.Value, Nothing
        End If
    Next cell
    
    CountUnique = dict.Count
End Function

Escreva um código no editor VBA para localizar valores distintos. Imagem do autor.

Em seguida, pressione Ctrl + S para salvá-lo e ALt + Q para fechar o editor VBA. Agora, você pode chamar essa função personalizada em qualquer lugar da planilha e especificar o intervalo a partir do qual encontrará os valores distintos. No meu caso, ela se chama CountUnique(), portanto, toda vez que chamo essa função com um intervalo especificado, ela me fornece um valor de contagem:

=CountUnique(A2:A8)

Contar valores distintos usando o editor VBA. Imagem do autor.

Tabela de referência

Para referência, criei uma tabela para ajudar você a entender as diferenças entre contagens únicas e distintas. 

Problemas comuns e solução de problemas

Às vezes, mesmo depois de seguir as práticas recomendadas, você pode enfrentar desafios. Mas não há problema, pois isso acontece com a maioria das pessoas.

Às vezes, recebo um erro e me sinto confuso, mas acabo percebendo que o problema são os espaços ocultos. Isso acontece quando alguns valores têm espaços extras no início ou no final e o Excel trata esses casos de forma diferente, mesmo que tenham a mesma aparência. Para corrigir isso, coloco minha fórmula dentro da função TRIM(). Leia meu outro tutorial, Use a função TRIM() no Excel: Corrija qualquer problema de espaçamento se você tiver alguma dúvida sobre TRIM().

Outro problema comum são os erros de fórmula em versões mais antigas do Excel. Por exemplo, o site UNIQUE() não funciona no Excel 2016 ou em suas versões anteriores. Em vez disso, use COUNTIF() ou SUMPRODUCT().

Considerações finais

Abordei fórmulas-chave, tabelas dinâmicas e macros VBA para contar valores únicos e distintos. Cada método tem seus benefícios, mas você deve escolher aquele que atenda às suas necessidades e seja compatível com a sua versão do Excel.

Se você quiser aprimorar suas habilidades em Excel, confira nosso curso de habilidades em Fundamentos do Excel e Preparação de dados no Excel.