7 maneiras de contar valores únicos no excel

Suponha que você tenha uma lista de valores onde cada valor é inserido várias vezes.

E agora…

Você deseja contar os valores exclusivos nesta lista para obter o número real de valores nela.

Para fazer isso, você precisa usar um método que conte o valor apenas uma vez e ignore todas as outras ocorrências da lista.

No Excel, você pode usar métodos diferentes para obter vários valores exclusivos. Depende do tipo de valores que você possui para que possa usar o melhor método para isso.

No artigo de hoje, gostaria de compartilhar com vocês 6 métodos diferentes para contar valores únicos e usar esses métodos dependendo do tipo de valores que você possui.

dados.xlsx

Filtro avançado para obter o número de valores exclusivos

Usar um filtro avançado é uma das maneiras mais fáceis de verificar o número de valores únicos e você nem precisa de fórmulas complexas. Aqui temos uma lista de nomes e dessa lista você precisa contar o número de nomes exclusivos.

Aqui estão as etapas para obter os valores exclusivos:

1. Primeiro, selecione uma das células da lista.
   
2. Depois disso, vá para a guia Dados ➜ Classificar e Filtrar ➜ Clique em Avançado .
   
3. Depois de clicar nele, você verá uma janela pop-up para aplicar filtros avançados.
4. Agora nesta janela selecione “ Copiar para outro local ”.
5. Em “Copiar para”, selecione uma célula em branco onde deseja colar valores exclusivos.
6. Agora marque a caixa “ Somente registros únicos ” e clique em OK.
   
7. Neste ponto você tem uma lista de valores exclusivos .
   
8. Agora vá até a célula abaixo da última célula da lista e insira a seguinte fórmula e pressione Enter.

 =COUNTA(B2:B10)

Ele retornará o número de valores exclusivos nesta lista de nomes.

Agora você tem uma lista de valores exclusivos e também os conta. Este método é simples e fácil de seguir, pois você não precisa escrever nenhuma fórmula complexa para isso.

Combinando SUM e COUNTIF para contar valores únicos

Se quiser encontrar o número de valores exclusivos em uma única célula sem extrair uma lista separada, você pode usar uma combinação de SOMA e CONT.SE.

Neste método, basta consultar a lista de valores e a fórmula retornará o número de valores únicos. Esta é uma fórmula de matriz, então você precisa inseri-la como uma tabela e, ao inseri-la, use Ctrl + Shift + Enter.

E a fórmula é:

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

Quando você insere esta fórmula em formato tabular, ela ficará assim.

 {=SUM(1/COUNTIF(A2:A17,A2:A17))}

Como funciona

Para entender esta fórmula você precisa dividi-la em três partes e apenas lembrar que inserimos esta fórmula em forma tabular e há um total de 16 valores nesta lista, não únicos, mas totais.

Ok, então olhe.

Na primeira parte você usou CONT.SE para contar o número de cada valor começando em 16 e aqui CONT.SE retorna valores como abaixo.

Na segunda parte você divide todos os valores por 1, o que retorna um valor como este.

Digamos que se um valor estiver presente duas vezes na lista ele retornará 0,5 para ambos os valores, de modo que no final ao somar ele se tornará 1 e se um valor estiver presente três vezes retornará 0,333 para cada um.

E, na terceira parte, você simplesmente usou a função SUM para somar todos esses valores e terá uma série de valores únicos.

Esta fórmula é bastante poderosa e pode ajudá-lo a obter a contagem em uma única célula.

Use SUMPRODUCT + COUNTIF para obter uma contagem de valores exclusivos de uma lista

No último método, você usou os métodos SUM e COUNTIF. Mas você também pode usar SUMPRODUCT em vez de SUM.

E, ao usar SUMPRODUCT , você não precisa inserir uma fórmula em formato tabular. Basta editar a célula e inserir a fórmula abaixo.

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

Quando você insere esta fórmula em formato tabular, ela ficará assim.

 {=SUMPRODUCT(1/COUNTIF(A2:A17,A2:A17))}

Como funciona

Esta fórmula funciona exatamente da mesma maneira que você aprendeu no método acima, a diferença é simplesmente que você usou SUMPRODUCT em vez de SUM.

E SUMPRODUCT pode receber um array sem usar Ctrl+Shift+Enter.

Conte apenas valores de texto exclusivos de uma lista

Agora, suponha que você tenha uma lista de nomes na qual também possui números de telefone celular e deseja contar valores únicos apenas a partir de valores de texto. Então, neste caso, você pode usar a fórmula abaixo:

 =SUM(IF(ISTEXT(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))

E quando você insere esta fórmula em forma tabular.

 {=SUM(IF(ISTEXT(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))}

Como funciona

Neste método você usou as funções IF e ISTEXT. ISTEXT primeiro verifica se todos os valores são texto ou não e retorna TRUE se algum valor for texto.

Depois disso, IF aplica CONT.SE em todos os valores de texto onde você tem TRUE e os demais valores permanecem vazios.

E no final SUM retorna a soma de todos os valores únicos que são texto e assim você obtém o número de valores de texto únicos.

Obtenha o número de números exclusivos de uma lista

E se quiser apenas contar números únicos de uma lista de valores, você pode usar a fórmula abaixo.

 =SUM(IF(ISNUMBER(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))

Insira esta fórmula em forma de tabela.

 {=SUM(IF(ISNUMBER(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))}

Como funciona

Neste método, você usou as funções IF e ISNUM. ISNUMBER primeiro verifica se todos os valores são numéricos ou não e retorna TRUE se um valor for um número.

Depois disso, IF aplica CONT.SE em todos os valores numéricos onde você tem TRUE e os demais valores permanecem vazios.

E no final SUM retorna a soma de todos os valores únicos que são números e assim você obtém o número de números únicos.

Conte valores únicos com uma UDF

Aqui tenho o VBA (UDF) que pode te ajudar a contar valores únicos sem usar nenhum tipo de fórmula.

 Function CountUnique(ListRange As Range) As Integer Dim CellValue As Variant Dim UniqueValues As New Collection Application.Volatile On Error Resume Next For Each CellValue In ListRange UniqueValues.Add CellValue, CStr(CellValue) ' add the unique item Next CountUnique = UniqueValues.Count End Function

Insira esta função em seu VBE inserindo um novo módulo, depois vá até sua planilha e insira a seguinte fórmula.

 =CountUnique(range)

Conclusão

A contagem de valores exclusivos pode ser útil ao trabalhar com grandes conjuntos de dados.

A lista de nomes que você usou aqui tinha nomes duplicados e depois de calcular os números únicos obtemos que existem 10 nomes únicos na lista.

Bem, todos os métodos que você aprendeu aqui são úteis em diferentes situações e você pode usar qualquer um daqueles que achar mais adequados para você.

Se você me perguntar, filtro avançado e SUMPRODUCT são meus métodos favoritos, mas agora você precisa me dizer:

Qual deles é o seu favorito?

Por favor, compartilhe sua opinião comigo na seção de comentários, eu adoraria ouvir de você e não se esqueça de compartilhar essa dica com seus amigos.