none
Contar valores repetidos no Excel com VBA RRS feed

  • Pergunta

  • Tenho uma planilha com várias escolas. Eu preciso do número de escolas que estão nessa planilha, independente do número de vezes que as escolas aparecem.

    Exemplo:

    ESCOLA

    STA ISABEL

    STA CRUZ

    ARQUI

    STO AGOSTINHO

    STA ISABEL

    A escola Sta Isabel aparece 2 vezes, enquanto as outras aparecem apenas 1 vez. Logo, o total de escolas unitárias é 4. O total da lista é 5.

    Temos mais de 500 escolas e nossa planilha tem mais de 3000 linhas.

    Alguém pode me ajudar com uma função?

     

    Obrigado desde já.

    quarta-feira, 20 de outubro de 2010 19:45

Respostas

  • Leandro,

     

    Pensando rapidamente aqui, me veio a seguinte idéia:

    Public Function ContarDistinct(intervalo As Range) As Long
      Dim celula, valores As New Collection, valor As Variant, achou As Boolean
      For Each celula In intervalo
       If Trim(celula) <> "" Then
         achou = False
         For Each valor In valores
          If valor = celula Then
            achou = True
            Exit For
          End If
         Next
         If Not achou Then valores.Add celula.Value
       End If
      Next
      ContarDistinct = valores.Count
      Set valores = Nothing
    End Function
    

    O que acha?

    Alexandre

    sexta-feira, 22 de outubro de 2010 03:13

Todas as Respostas

  • Leandro,

     

    Pensando rapidamente aqui, me veio a seguinte idéia:

    Public Function ContarDistinct(intervalo As Range) As Long
      Dim celula, valores As New Collection, valor As Variant, achou As Boolean
      For Each celula In intervalo
       If Trim(celula) <> "" Then
         achou = False
         For Each valor In valores
          If valor = celula Then
            achou = True
            Exit For
          End If
         Next
         If Not achou Then valores.Add celula.Value
       End If
      Next
      ContarDistinct = valores.Count
      Set valores = Nothing
    End Function
    

    O que acha?

    Alexandre

    sexta-feira, 22 de outubro de 2010 03:13
  • Leandro,

     

    Pensando rapidamente aqui, me veio a seguinte idéia:

     

    Public Function ContarDistinct(intervalo As Range) As Long
    
     Dim celula, valores As New Collection, valor As Variant, achou As Boolean
    
     For Each celula In intervalo
    
      If Trim(celula) <> "" Then
    
       achou = False
    
       For Each valor In valores
    
       If valor = celula Then
    
        achou = True
    
        Exit For
    
       End If
    
       Next
    
       If Not achou Then valores.Add celula.Value
    
      End If
    
     Next
    
     ContarDistinct = valores.Count
    
     Set valores = Nothing
    
    End Function
    
    

     

    O que acha?

    Alexandre


    Alexandre,

    muito obrigado! Funcionou perfeitamente.

    Deus seja convosco!

    sexta-feira, 22 de outubro de 2010 12:19
  • Olá Alexandre, primeiro gostaria de parabenizá-lo pela Macro, excelente, tinha uma necessidade semelhante e essa macro supri a mesma.

    No entanto eu gostaria de saber contigo como faria nessa mesma macro, uma linha de programação onde eu possa escolher, contar apenas valores numéricos ou texto. Uma ideia mais ou menos assim, na formula 

    ContarDistinct (A:A;1(quando for valor numérico) ou 0 (quando for texto))

    ContarDistinct(A:A;1) - conta apenas valores numéricos, ignorando os valores de texto que possam existir no espaço.

    ContarDistinct (A:A;0) - conta apenas valores de texto, ignorando os valores numéricos que possam existir no espaço.

    Se puder me ajudar agradeço.

    sábado, 7 de junho de 2014 23:03
  • Fala Erivelton,

    Deu saudades da época que postava aqui, vou tentar voltar a ativa por aqui :)

    Seguinte, mudei a função pra aceitar o zero ou um como segundo parâmetro. Ou nenhum segundo parâmetro, aí ele conta tudo, como anteriormente.

    Veja se te atende.

    Qualquer coisa escreva aqui novamente :)

    Public Function ContarDistinct(intervalo As Range, Optional opcao As Integer = -1) As Long
       Dim celula, valores As New Collection, valor As Variant, achou As Boolean
       For Each celula In intervalo
          If Trim(celula) <> "" And _
             ((opcao = -1) Or (opcao = 0 And Not IsNumeric(celula)) Or (opcao = 1 And IsNumeric(celula))) Then
             achou = False
             For Each valor In valores
                If valor = celula Then
                   achou = True
                   Exit For
                End If
             Next
             If Not achou Then valores.Add celula.Value
          End If
       Next
       ContarDistinct = valores.Count
       Set valores = Nothing
    End Function
    

    Alexandre

    domingo, 8 de junho de 2014 04:33
  • Volta mesmo, Atadolfo, porque tá difícil segurar as pontas aqui! rs

    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    domingo, 8 de junho de 2014 13:59
    Moderador
  • Olá Alexandre,

    Agradeço pela resposta rápida.

    Era exatamente isso que imaginava.

    Obrigado.


    domingo, 8 de junho de 2014 22:37
  • Boa noite.

    Após pesquisa na internet, a função ContarDistinct é a melhor que encontrei.

    Estou conseguindo usa-la em uma planilha do Excel.

    Porém no VBA não estou conseguindo inseri-la (fazer funcionar) em uma TextBox.

    Como fazer isto?

    sábado, 14 de março de 2015 01:15
  • Olá Oscar,

    Agradeço o elogio pela função :)

    A forma de usar no VBA é a mesma como na planilha.

    Coloque aqui como você está tentando usar para eu ter ideia de como você quer.

    Ou, se puder, me envie a planilha por email (atadolfo@gmail.com) que fica melhor ainda pra resolver seu problema.

    Alexandre

    domingo, 15 de março de 2015 03:40
  • Boa tarde Alexandre.

    Como comentei anteriormente, usei a sua função na planilha do Excel, onde para experimentar coloquei na coluna A os mesmos (5) dados enviados pelo Leandro no inicio deste forum, e com a sua mesma intenção retornar a quantidade de dados não repetidos (4) no intervalo da coluna A.

    Inseri na célula D10 a sua função, ou seja =ContarDistinct(A1:A10), e retornou a quantidade 4. Funcionou!

    Mas a minha necessidade é usar a sua função no VBA, e não estou conseguindo fazer de modo correto.

    Preciso que ela retorne automaticamente a quantidade da coluna da planilha em uma textbox do formulário.

    Ou para uma futura necessidade retorne em uma range da mesma planilha.

    Para experimentar no VBA, fiz do seguinte modo:

    Na planilha Plan1 coluna A intervalo A1:A10, inseri os mesmos dados enviados pelo Leandro, ou seja 5 nomes com 1 nome repetido, devendo retornar a quantidade de 4 nomes.

    Crie uma UserForm1, na qual inseri uma TextBox1 para o retorno da função.  

    Cliquei na Userform1 para inserir a sua macro.

    Cliquei na TextBox1, para na Private Sub inserir como abaixo:

    Private Sub TextBox1_Change()
         TextBox1.Value = ContarDistinct
    End Sub

    Substitui na macro em For Each celula In intervalo por For Each celula In Range("A1:A10").

    Mas não funcionou. Abro o Userform1 e não aparece nada dentro da Textbox1.

    Qual seria o modo certo?


    domingo, 15 de março de 2015 15:20
  • Olá Oscar,

    Eu acho que não seria uma boa você chamar a função pelo evento Change da caixa.

    Eu criei um botão de comando e dentro do evento click dele coloquei o seguinte:

    TextBox1.Text = ContarDistinct(Range("A1:A10"))

    Como a função recebe uma intervalo para verificar, você precisa passar o intervalo usando a função range, informando o intervalo.

    Veja se te atende. Qualquer coisa fala aí que mudamos tudo :)

    Alexandre

    domingo, 15 de março de 2015 20:31
  • Boa noite Atadolfo.

    Funcionou!

    Usando a sua alteração.

    Criei um botão no formulário, e com o evento click, retornou o valor na texbox1.

    Experimentei o evento Enter, para ao abrir o formulário a textbox apareça com o retorno. Funcionou!

    Também usei o evento Enter para retornar o valor em uma range da planilha. Funcionou!

    Minha duvida é se para os dois últimos retornos, existiria um evento mais correto.

    Muito obrigado por sua ajuda.

    segunda-feira, 16 de março de 2015 01:21
  • Oscar,

    Estamos indo bem, então :)

    Me explique melhor o que você deseja realmente fazer. Digo, o que você tem e o que você precisa no final, para tentar te dar a solução completa.

    Alexandre

    segunda-feira, 16 de março de 2015 02:41
  • Alexandre, boa noite!

    Muito interessante essa sua função, ela quase se adequa perfeitamente à minha necessidade, veja se consegue me ajudar...

    Preciso contar os valores distintos de um determinado intervalo (sua função já faz) mas que atenda a alguns critérios (sua função não faz). Seria a função SOMASES que ao invés de somar faz essa contagem de Valores Distintos que criou. É possível?

    Parabéns pela contribuição!!!

    Att,

    Erick

    sexta-feira, 25 de março de 2016 04:06
  • Fala Erick,

    Posso tentar adaptar sim. Me manda um exemplo de como você quer que seja a chamada pra função que eu mudo o que for preciso.

    Alexandre

    sexta-feira, 25 de março de 2016 05:52
  • Alexandre,

    Agradeço pelo rápido retorno e por se interessar em ajudar!!!

    A função, deveria funcionar assim:

    MODELO
    =CONTARDISTINCTSES(intervalo_contar;[intervalo_critérios1;critérios1];[intervalo_critérios2;critérios2];[intervalo_critérios3;critérios3];[intervalo_critérios4;critérios4];[intervalo_critérios5;critérios5];…)


    EXEMPLO
    =CONTARDISTINCTSES(Tabela1[Nº da Venda];Tabela1[Data];">="&$D$6;Tabela1[Data];"<="&$G$6;Tabela1[Tipo de Venda];"Produto”)

    No exemplo, a função precisa contar os valores distintos da coluna “Nº DA VENDA” mas desde que na coluna “DATA” esteja o intervalo desejado e na coluna “TIPO DE VENDA” esteja “produto”.

    Mas assim como na função SOMASES pode ser que tenha necessidade de colocar mais critérios.

    Valeu!!!

    Erick

    sexta-feira, 25 de março de 2016 15:21
  • Erick,

    Fiz uma função, mas não ficou exatamente como o SOMASES. Estava com dificuldade para realizar a comparação, já que o valor pode ser texto, número, data e etc, então criei mais um parâmetro.

    Após o parâmetro do intervalo a ser contado, ao invés de informar os parâmetros de 2 em 2 (como no SOMASES), deve-se informar de 3 em 3, sendo:

    1º) o intervalo de critérios;

    2º) o tipo da comparação entre aspas (=, <>, >, <, >= ou <=);

    3º) valor para comparação.

    Pelo seu exemplo ficaria:

    =ContarDistinctSeS(Tabela1![Nº da Venda]; Tabela1![Data]; ">="; $D$6; Tabela1![Data]; "<="; $G$6; Tabela1![Tipo de Venda]; "="; "Produto”)

    Public Function ContarDistinctSeS(intervalo As Range, ParamArray parametros() As Variant) As Long
       Dim celula, valores As New Collection, valor As Variant, achou As Boolean, cont As Long
       Dim intCriterio As Range, valido As Boolean, criticas As Long
       cont = 1
       For Each celula In intervalo
          If Trim(celula) <> "" Then
             achou = False
             valido = True
             For criticas = 0 To UBound(parametros) Step 3
                Set intCriterio = parametros(criticas)
                Select Case Trim(parametros(criticas + 1))
                   Case "="
                      valido = valido And intCriterio.Cells(cont, 1) = parametros(criticas + 2)
                   Case "<>"
                      valido = valido And intCriterio.Cells(cont, 1) <> parametros(criticas + 2)
                   Case ">"
                      valido = valido And intCriterio.Cells(cont, 1) > parametros(criticas + 2)
                   Case "<"
                      valido = valido And intCriterio.Cells(cont, 1) < parametros(criticas + 2)
                   Case ">="
                      valido = valido And intCriterio.Cells(cont, 1) >= parametros(criticas + 2)
                   Case "<="
                      valido = valido And intCriterio.Cells(cont, 1) <= parametros(criticas + 2)
                End Select
                If Not valido Then Exit For
             Next
             If valido Then
                For Each valor In valores
                   If valor = celula Then
                      achou = True
                      Exit For
                   End If
                Next
                If Not achou Then valores.Add celula.Value
             End If
          End If
          cont = cont + 1
       Next
       ContarDistinctSeS = valores.Count
       Set valores = Nothing
    End Function


    • Editado Atadolfo segunda-feira, 28 de março de 2016 14:37
    sexta-feira, 25 de março de 2016 23:48
  • Olá Oscar,

    Agradeço o elogio pela função :)

    A forma de usar no VBA é a mesma como na planilha.

    Coloque aqui como você está tentando usar para eu ter ideia de como você quer.

    Ou, se puder, me envie a planilha por email (atadolfo@gmail.com) que fica melhor ainda pra resolver seu problema.

    Alexandre

    Muita boa a função!

    Uma dúvida que não estou conseguindo resolver, se eu quiser que retorne em uma variável?

    Eu sei achar o range que preciso, no caso tenho um export de um sistema, preciso contar quantas linhas na coluna A para achar o range da variável (isso está ok - final = Application.CountA(Range("A:A")), porém gostaria de receber a resposta da função ContarDistinct dentro de uma variável.

    Obrigado!

    terça-feira, 17 de maio de 2016 04:51
  • Olá,

    É só colocar do mesmo jeito que você usou a outra função:

    final = ContarDistinct(Range("A:A"))

    Alexandre

    terça-feira, 17 de maio de 2016 13:28
  • Ei Erick,

    Deu certo?

    terça-feira, 17 de maio de 2016 13:29