0044-Contando (e listando) registros exclusivos

Imagine que você possui uma lista em que há diversos valores repetidos e que gostaria de saber quantos são os registros exclusivos.

Vamos a um exemplo:

Poderíamos contar os registros manualmente, mas ao invés disto, vamos inserir na célula B1 a fórmula =SOMARPRODUTO(1/CONT.SE(A1:A17;A1:A17)) e obter o valor a seguir:

E realmente são sete valores distintos: a,b,c,d,e, f e g.

Mas como o “truque” funciona?

Para determinar quantas vezes cada registro aparece, podemos colocar em B1 a fórmula =CONT.SE($A$1;$A$17;A1), copiando-a em seguida para o intervalo B2:B17, obtendo o seguinte conjunto de valores:

Se invertermos estes valores, teremos as frequências relativas de cada um dos conjuntos de dados. Na coluna C, colocaremos a partir de C1, a fórmula =1/B1

Ora, a soma das frequências relativas de cada conjunto é igual a 1. Portanto ao somarmos todos os conjuntos teremos o resultado do número de valores exclusivos(SOMA(C1:C17)).

Todos os passos mostrados, podem ser resumidos a uma única fórmula geral para a contagem de registros exclusivos: =SOMARPRODUTO(1/CONT.SE(Intervalo_Valores; Intervalo_Valores)).

Editado em 20/05/2011 @ 23:40

Nos comentários deste post surgiu uma dúvida sobre como retornar a listagem dos valores exclusivos.
Para responder a esta pergunta é preciso lançar mão de uma fórmula matricial que ficaria difícil de postar aqui.
No arquivo Valores Exclusivos está a fórmula que pode ser utilizada e as etapas para a sua construção.

Anúncios

In Excel We Trust

Marcado com: , ,
Publicado em Nível: Avançado
18 comentários em “0044-Contando (e listando) registros exclusivos
  1. flaviox27 disse:

    Esta fórmula é excelente. Agora lá em cima, no segundo quadro, em B1, voce tem o resultado (7) dos não repetidos. Como seria para listar, lá no segundo quadro, a partir de C1, os não repetidos.
    Ou seja, como usar uma fórmula de C1 até C17 que listasse as letras não repetidas (e uma fórmula com “Se(éerros” pra preencher com valores de C1 a C7 e deixar vazio de C8 a C17 ?

    • Boa tarde, Flávio,
      Coloque no intervalo C1:C17 a seguinte fórmula matricial:
      =SEERRO(CARACT(MENOR(N(FREQÜÊNCIA(CORRESP($A$1:$A$17;
      $A$1:$A$17;0);CORRESP($A$1:$A$17;$A$1:$A$17;0))0)*
      CÓDIGO(SE($A$1:$A$18″”;$A$1:$A$18));
      SOMARPRODUTO(N(FREQÜÊNCIA(CORRESP($A$1:$A$17;
      $A$1:$A$17;0);CORRESP($A$1:$A$17;$A$1:$A$17;0))=0))+LIN()));””)

      Abraços!

  2. Flavio disse:

    Ctrl C nesta fórmula e Ctrl V em C1. Depois Ctrl Shift Enter. Deu erro !
    o problema é que eu não sei nem começar a auditar esta fórmula pra ver onde estaria o erro. Excel 2007.
    Depois do erro, ao dar “ok” o cursor pára neste zero depois do ))
    SEERRO(CARACT(MENOR(N(FREQÜÊNCIA(CORRESP($A$1:$A$17;
    $A$1:$A$17;0);CORRESP($A$1:$A$17;$A$1:$A$17;0))0)*etc…

  3. Flavio disse:

    a fórmula do arquivo é ligeiramente diferente da fórmula que voce colocou aí em cima !
    a fórmula da planilha upada funcionou perfeitamente.
    =SEERRO(CARACT(MENOR(N(FREQÜÊNCIA(CORRESP($A$1:$A$17;$A$1:$A$17;0);CORRESP($A$1:$A$17;$A$1:$A$17;0))0)*CÓDIGO(SE($A$1:$A$18″”;$A$1:$A$18));SOMARPRODUTO(N(FREQÜÊNCIA(CORRESP($A$1:$A$17;$A$1:$A$17;0);CORRESP($A$1:$A$17;$A$1:$A$17;0))=0))+LIN()));””)

    Veja que ela é diferente da que vc colocou aí em cima !!
    Valeu demais. Excelente tópico.

  4. Flavio disse:

    Agora o tópico pode mudar de título: “Contando e listando registros exclusivos”

  5. Flavio disse:

    já sei ! vc fez a fórmula certa e colou aqui. Foi o que eu fiz também e o problema é que o sinal de “diferente” os sinais de menor e maior, não estão sendo permitidos aqui ! Muito estranho mas eu colei a fórmula da sua planilha e a minha colagem também ficou com erro. Esta fórmula que eu colei acima também não vai funcionar (por que o sinal de diferente é cortado automaticamente)…
    O fato é que tem que fazer o upload do arquivo e pronto. A Fórmula funcionou perfeitametne. Mas não dá pra colar a fórmula daqui não. “” não está dando certo aqui. Deve ter alguma coisa a ver com código html ou sei lá. Mas valeu demais. A fórmula no arquivo é perfeita.

    • Que bom. Fiz uma atualização do arquivo no Dropdown colocando todas etapas do processo de construção da “Megafórmula”. Veja se você está com a última versão.
      Obrigado pela observação quanto ao problema com as fórmulas em comentários. Vai me evitar dores de cabeça no futuro.
      Editei também o título do post e inseri o link para o arquivo lá também.
      Este vai para os seus favoritos, não?
      Abraços!

  6. flavio disse:

    Já foi.

  7. Flavio disse:

    O “passo a passo” neste arquivo ficou muito bom. Mas muito bom mesmo ! Deu até pra entender a “lógica” da fórmula. Muito bom ! Valeu.

  8. Claudeko disse:

    Preciso de uma fórmula dessas para obter o maior valor repetido e o segundo maior valor repetido

    • Baixe o arquivo disponível para download;

      Digite na coluna A, trecho cinza (A1:A17) os valores da tua lista.
      Na coluna C (C1:C17) serão exibidos apenas os valores repetidos.
      Selecione o trecho C1:C17 e substitua a fórmula que está lá, por essa aqui:

      =SEERRO(1*CARACT(MENOR(N(FREQÜÊNCIA(CORRESP($A$1:$A$17;$A$1:$A$17;0);CORRESP($A$1:$A$17;$A$1:$A$17;0))0)*CÓDIGO(SE($A$1:$A$18″”;$A$1:$A$18));SOMARPRODUTO(N(FREQÜÊNCIA(CORRESP($A$1:$A$17;$A$1:$A$17;0);CORRESP($A$1:$A$17;$A$1:$A$17;0))=0))+LIN()));””)

      Ou se preferir, edite diretamente colocando o trecho destacado acima (1* antes da função CARACT)

      Pressione em seguida CTRL+SHFT+ENTER para dar entrada como fórmula matricial.

      A mudança acima é necessária por que a fórmula original foi criada para retornar valores de texto.

      Agora escreva as fórmulas normais:
      =MAIOR(C1:C17;1) para o maior valor
      =MAIOR(C1:C17;2) para o menor valor

      [ ]s

  9. Carlos Andrade disse:

    cara, procurei a resposta pra este problema, mas achei melhor criar uma forma mais fácil, com coluna de apoio, não sei se da pra entender aqui…
    cont controle tornar único lista com duplicados indice menor lista sem duplicidade
    =SE(CONT.SE($C$1:C2;C2)=0;”2″;CONT.SE($C$1:C2;C2)) =A2+(CONT.SE($A$1:A2;A2)/1000) a =SE(MENOR(‘CONSOLIDADO ANO’!AV:AV;Aux_Gráf!AQ3)>=2;””;PROCV(MENOR(‘CONSOLIDADO ANO’!AV:AV;Aux_Gráf!AQ3);’CONSOLIDADO ANO’!AV:AW;2;0)) =SE(MENOR(B:B;E2)>=2;””;PROCV(MENOR(B:B;E2);B:C;2;0))
    =SE(CONT.SE($C$1:C3;C3)=0;”2″;CONT.SE($C$1:C3;C3)) =A3+(CONT.SE($A$1:A3;A3)/1000) b =E2+1 =SE(MENOR(B:B;E3)>=2;””;PROCV(MENOR(B:B;E3);B:C;2;0))
    =SE(CONT.SE($C$1:C4;C4)=0;”2″;CONT.SE($C$1:C4;C4)) =A4+(CONT.SE($A$1:A4;A4)/1000) c =E3+1 =SE(MENOR(B:B;E4)>=2;””;PROCV(MENOR(B:B;E4);B:C;2;0))
    =SE(CONT.SE($C$1:C5;C5)=0;”2″;CONT.SE($C$1:C5;C5)) =A5+(CONT.SE($A$1:A5;A5)/1000) c =E4+1 =SE(MENOR(B:B;E5)>=2;””;PROCV(MENOR(B:B;E5);B:C;2;0))
    =SE(CONT.SE($C$1:C6;C6)=0;”2″;CONT.SE($C$1:C6;C6)) =A6+(CONT.SE($A$1:A6;A6)/1000) d =E5+1 =SE(MENOR(B:B;E6)>=2;””;PROCV(MENOR(B:B;E6);B:C;2;0))
    =SE(CONT.SE($C$1:C7;C7)=0;”2″;CONT.SE($C$1:C7;C7)) =A7+(CONT.SE($A$1:A7;A7)/1000) a =E6+1 =SE(MENOR(B:B;E7)>=2;””;PROCV(MENOR(B:B;E7);B:C;2;0))
    =SE(CONT.SE($C$1:C8;C8)=0;”2″;CONT.SE($C$1:C8;C8)) =A8+(CONT.SE($A$1:A8;A8)/1000) b =E7+1 =SE(MENOR(B:B;E8)>=2;””;PROCV(MENOR(B:B;E8);B:C;2;0))

  10. andre disse:

    bom dia,
    suponha que eu tenha eu tenha também uma coluna com datas e queira contar os registros exclusivos por mês. Por exemplo, em julho de 2015 tive 3 A, 2 B, em agosto/2015 tive 1 C, 1 D, etc… teria como?
    Obrigado

  11. Pedro disse:

    Queria obter o mesmo resultado só que com palavras inteiras e não somente a primeira letra e que a formula continuasse funcionando caso houvesse um espaço em branco no meio da base.

  12. Alexandre Motta disse:

    Olá, boa tarde!!

    Muito obrigado!! Sensacional a sua explicação e o arquivo também matou a pau com todas as explicações… O problema é que fui tentar reproduzir a mesma planilha que vc fez, porém ela nao funciona aplicando as mesmas fórmulas que vc usou… por exemplo, na coluna F, não tive os mesmo retornos que você usando a formula matricial… e ao usar a mesma fórmula sem o “alt+shift+enter” ela funcionou e retornou os mesmos valores do seu arquivo… porém o mesmo nao aconteceu para as demais colunas… vc saberia me dizer o que posso estar fazendo errado? obrigado mais uma vez!!

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Visitas dos usuários
  • 2,077,129

Clique para assinar este blog e receber notificações de novos artigos por email.

Junte-se a 1.537 outros seguidores

Aumente sua interação

Siga-nos do twitter (@usuariosdoexcel).

Acesse o nosso fórum e compartilhe as suas dúvidas.

Curta nossa página no Facebook.

Junte-se ao grupo do LinkedIn.

Visite-nos utilizando o seu smartphone ou tablet.

Recomende este Blog
Visualizar notícias
Site monitorado:
Real Time Web Analytics

Clicky

%d blogueiros gostam disto: