Contando valores únicos dentre valores duplicados

27
7641

Há dois dias postei na fã page do Guru do Excel no facebook um desafio. Criar uma fórmula que contabilizasse quantos valores diferentes se repetiam dentro de um intervalo.

desafio-valores-unicos

Desafio:

Qual a fórmula que a célula C3 contém, que calcula a quantidade de valores únicos no intervalo A1:A10?

Resposta:

{=SOMA(SEERRO(1/CONT.SE(A1:A10;A1:A10);0))}

Entendendo a solução

A solução é uma fórmula matricial. Vejamos a seguir.

Para tornar mais fácil o entendimento, vamos primeiro tentar entender o raciocínio.

Cada nome diferente deve ser contado uma única vez. No nosso exemplo, o nome ‘JOÃO’ se repete 3 vezes, sendo assim, cada vez que o nome ‘JOÃO’ aparecer deve contribuir com 1/3 na somatória, para que ao somar os três nomes o valor totalizado seja 1. Um nome que se repete 4 vezes deve contribuir com 1/4, e assim por diante.

Este cálculo é feito utilizando a fórmula a seguir.

=1/CONT.SE(A1:A10;A1:A10)

A função CONT.SE conta o número de células dentro de um intervalo que atendem a um único critério que você especifica. Porém, perceba que na função acima, no parâmetro critério é passado o intervalo A1:A10, fazendo com que a fórmula retorne um valor para cada célula do intervalo A1:A10. Isso caracteriza essa fórmula como uma matriz. Seria como criar uma coluna com 10 linhas, contendo as seguintes fórmulas:

cont-se-matriz1

É importante observar que, por a função CONT.SE ser o denominador de uma divisão, o resultado da contagem não pode ser igual a zero, o que resultaria no erro #DIV/0! (impossível dividir por zero). Isso ocorrerá se alguma célula no intervalo A1:A10 estiver em branco. Para evitar este erro, utilizamos a função SEERRO,  para que quando o resultado for um erro, seja substituído por zero. A fórmula ficará assim então:

=SEERRO(1/CONT.SE(A1:A10;A1:A10);0)

Agora é somar todos os elementos da matriz:

=SOMA(SEERRO(1/CONT.SE(A1:A10;A1:A10);0))

Não se esqueça de pressionar CTRL + SHIFT + ENTER ao terminar a fórmula, por ser uma fórmula matricial. O resultado na sua barra de fórmulas deverá ser a fórmula a seguir:

{=SOMA(SEERRO(1/CONT.SE(A1:A10;A1:A10);0))}

Qualquer coisa, é só postar nos comentários.

COMPARTILHE
Próximo artigoCriando um Sudoku no Excel
Engenheiro Civil, amante do Excel sendo certificado Microsoft Excel Specialit Expert. É um aficionado por números e desafios lógicos. É o idealizador do site Guru do Excel
  • Thiago Rodrigues

    Show

    • Guru do Excel

      Olá Daniel. Não entendi. Os créditos por qual resposta?

      • Guru do Excel

        Ah, entendi agora. Você está falando com o Thiago, pela resposta que ele postou na página no facebook. 😀 http://on.fb.me/1f6KPvC

        Daniel, sinta-se a vontade para responder nossos desafios. Veja o desafio atual em: http://on.fb.me/1iyhWYU

        Grande Abraço.

        • Valmir Storti

          Ah, por favor… E se no lugar de CONT.SE eu precisar usar CONT.SES por necessitar condicionar a contagem aos elementos de duas colunas diferentes? (contar quantas vezes aparece números de 1 a 31 na coluna A sendo que na coluna C eu tenho 10 variáveis de nomes de garçons, por exemplo para saber quantos dias cada um trabalhou)

          • Valmir, você pode utilizar uma função se, para comparar os valores, e somar, dentro da matriz.

  • Ok… agora os créditos devidos pela resposta, ‘miguxo’.

  • Fernando

    E se eu precisar usar isso num SUBTOTAL

    Por exemplo: em uma tabela eu uso uma coluna para fornecedor e outra para produtos… uso um filtro para filtrar um determinado produto, por exemplo caderno… quero que a fórmula me informe quantos são os fornecedores de caderno…

  • Daniel Moraes

    Muito bom, o exemplo mais fácil que eu encontrei. Valeu pela dica!!!

  • Shigeru

    função muito útil, gostaria de saber se há possibilidade de utilizar com filtro

  • Filipe de Oliveira

    Muito inteligente! Obrigado!

  • mariafalcato

    Olá
    Muito obrigado!
    Mas precisava de combinar esta função com outra coluna, por exemplo de género (feminino ou masculino), ou seja queria contar cada uma destas variáveis com o valor único (nome). É possível?
    Obrigado

  • Thiago lopes

    Eu tenho uma planilha que tem data e essas datas elas se repetem junto com a quantidades iguais: exemplo 02/09 1
    02/09 1 =formola tem que ter o valor 02/09 com n°1
    02/09 1

  • Cidnei

    Excelente Laennder.

  • Jean Felipe Prado

    Laennder, boa tarde
    Bem útil e didática sua explicação, mas ainda não me atendeu numa questão:
    Preciso verificar a frequencia dos resultados, considerando a combinação de duas ou três colunas. Quero contar o número de processos numa planilha, mas como existem várias entradas pro mesmo processo, preciso que cada um seja contado apenas uma vez, porém pra cada processo, tem a coluna com o número, a coluna com o ano e outra com o dígito verificador.
    Não sei se consegui me explicar bem, mas saberia me dizer como montar a fórmula?
    Obrigado,

  • Norma Rodrigues

    perfeito, o único que funcionou como eu queria. Valeu

  • Nathalia Braile

    Adorei o exemplo e super funcionou comigo! Obrigada!
    Queria saber se há como adicionar mais um critério…
    Por exemplo:
    Quero saber a quantidade de valores únicos no ano de 2015, ou seja, faria essa fórmula e colocaria mais o intervalo de tempo pra que o excel retorne os valores únicos ocorridos só em 2015. Existe alguma fórmula para fazer isso?

  • Jânio Souza

    Show de bola esta formula. Quero saber se consigo usar esta fórmula com mais um critério. Ex.:

    Quero saber se consigo informar na célula da coluna B os valores únicos da coluna D se os valores da coluna C for igual ao valor da coluna A?

  • Leandro Lima

    Eu consigo fazer com critério?

  • Eduardo Martins

    Olá! Como ficaria esta mesma fórmula para contar datas dentro de um especificado mês, sendo que existe várias datas (vários meses) na mesma lista? Gostaria de criar este critério.

  • Angélica Afinovitch

    Olá. Tenho uma planilha com o nome de cada produto em uma linha diferente,e registro numa coluna ao lado a data da venda e o nome do cliente que comprou. Se por exemplo, no mesmo dia o cliente comprou 3 chaveiros e 2 refrigerantes, colocarei ao lado de cada um destes 5 produtos vendidos, a data e o nome do cliente que os comprou. Em outro dia ele vem e compra outros produtos diferentes, anotarei ao lado de cada produto a data de venda e o nome do comprador. Após um ano, quero saber quantos dias diferentes o cliente realizou compras na loja. Por exemplo, um cliente esteve na loja 15 vezes durante um ano, e comprou 45 produtos ao todo. A fórmula para contar o total de produtos que foram comprados por cada cliente durante o ano, eu já consegui, mas não consegui elaborar a fórmula que conte quantas vezes cada cliente esteve na loja, pois cada dia que veio, comprou uma quantidade diferente de produtos. Acho que seriam duas condições, a primeira seria localizar o nome do cliente na lista de produtos vendidos, e a segunda seria contar quantas vezes aparece cada data registrada. agradeço se puder me ajudar.

  • Paulo Fabiano de Toledo

    Cara, muito, mas muito bom mesmo! Valeu!

  • Eduardo Expósito

    Bom Dia !! eu preciso resolver um problema de soma de itens numéricos de uma coluna que tem numeros repetidos, colei esta formula e não resolve aparece como resultado ” 0 “. Tem alguma variável para este caso??

    13826672 1412 Cunha,Ulivaci Dias da,Sr
    13826698 1413 Alvares,Marja Ramos de Andrade
    13826705 1414 *Nielander,Cristina Von
    13826883 1414 *Rizzolli,Larissa Rodrigues Thomas
    13826700 1612 Fernandes,Aleandra Cangussu,Sra

    TOTAIS 0

    • Vai na célula da fórmula, pressiona o F2 e depois Crtl+Shif+Enter para ficar matricial. Aí vai aparecer o número.

  • Lucas

    e pra funcionar com autofiltro?

    • Lucas

      =SOMA(SE(FREQÜÊNCIA(M4:M16;M4:M16);1)) eu uso normalmente essa aqui

  • Muito boa a solução. Eu só tenho um probleminha a mais que não consigo solucionar. Eu tenho uma lista contendo em uma coluna as placas de vários veículos. Alguns retornam, mas eles não podem ser contados, pois quero saber o número de veículos que atendi, portanto quando o veículo retorna a fórmula não deve contá-lo novamente. Até aí beleza! No entanto eu tenho veículos em serviço (cuja coluna “data de saída” está em branco) e veículos já entregues (cuja coluna “data de saída” está preenchida). Eu preciso fazer duas contagens. Uma é das placas exclusivas em serviço e outra das placas exclusivas já entregues. Eu só consigo com sua fórmula contar a totalidade de veículos, mas não consigo colocar um critério que ele só me conte as placas exclusivas com data de saida e placas exclusivas sem data de saída.

    Poderia me ajudar?