[UDF] PROCVN : Um PROCV para trazer um resultado de ordem n-ésima

Quem trabalha com PROCV sabe que ela é extremamente útil para fazer busca entre planilhas, mas sabe que ela é limitada a trazer o primeiro valor encontrado, varrendo a tabela de origem de cima para baixo. No post “Fazendo um “PROCV” invertido” eu já expliquei como fazer quando se quer buscar um registro varrendo a tabela de baixa para cima. Mas agora imagine uma tabela que existem 3 registros com a mesma chave. Com o PROCV você traz o primeiro valor, como o tutorial do PROCV INVERTIDO você traz o terceiro valor, então pergunto: “Como trazer um valor referente ao segundo nome encontrado na tabela?”

Excelmaniacos - PROCVN 1

Neste primeiro tópico sobre UDF, quero mostrar uma forma de resolver este problema.

O significado da sigla UDF é “User defined Function”, ou seja, são funções personalizadas desenvolvidas pelos próprios usuários do Excel.

Normalmente recorre-se a uma UDF quando as funções disponíveis nativamente no Excel não são suficientes para atender a uma necessidade ou a necessidade é tão complexa que dependeria de muitas funções aninhadas ou muitos cálculos de apoio na planilha, ou para necessidades muito específicas que só podem ser resolvidas com programação.

Vamos desenvolver uma UDF que vou chamar de PROCVN, mas que poderia receber qualquer outro nome, conforme gosto do desenvolvedor. O código para a função ficará:

Public Function PROCVN(Val1 As Variant, _
                       Table As Range, _
                       ResultCol As Integer, _
                       Optional Val1Occrnce As Integer = 1)
    Dim i As Integer
    Dim iCount As Integer
    Dim rCol As Range
        For i = 1 To Table.Rows.Count
        If Not (Application.WorksheetFunction.IsError(Table.Cells(i, 1))) Then
            If UCase(Table.Cells(i, 1)) = UCase(Val1) Then
                iCount = iCount + 1
            End If
            If iCount = Val1Occrnce Then
                PROCVN = Table.Cells(i, ResultCol)
                Exit For
                Else
                PROCVN = CVErr(xlErrNA)
            End If
        End If
        Next i
End Function

Para tornar essa UDF disponível na sua planilha, pressione ALT+F11 para abrir o VBA, insira um módulo no seu projeto, e cole o código acima.

Excelmaniacos - PROCVN 2

Feche o VBA e assim a UDF “PROCVN” já estará disponível para ser utilizada.

Falando da sintaxe da UDF, ficará:

=PROCVN(valor_procurado;Matriz_tabela;núm_índice_tabela;ordem_n-ésima)

A diferença do PROCV para esta UDF PROCVN está no 4º argumento que foi substituído pela ordem n-ésima, que é o argumento onde será indicado qual resultado de busca é desejado trazer de resposta, ou seja, é possível indicar qual resultado trazer (ex: 1 para primeiro, 2 para o segundo, 3 para o terceiro e assim por diante).

Note que nesta UDF não foi colocado a opção de VERDADEIRO ou FALSO, que seria referente ao tipo de busca, que quando indicado VERDADEIRO a função PROCV retorna o valor mais próximo encontrado. Como não estamos trabalhando com este argumento, a UDF só retornará a busca de valores exatos.

Dessa forma, para trazer o segundo resultado do nosso exemplo, a função ficará:

=PROCVN(F2;A2:B7;2;2)

Excelmaniacos - PROCVN 4

É importante citar que para deixar a função disponível na sua planilha é preciso salvar o arquivo na extensão .xlsm (pasta de trabalho habilitada para macro do Excel. É possível também deixar a função disponível para qualquer pasta de trabalho se o código for aplicado por meio de um suplemento, assunto que tentarei abordar em algum futuro tópico.

Faça Download do modelo: Modelo – PROCVN

19 comentários em “[UDF] PROCVN : Um PROCV para trazer um resultado de ordem n-ésima

    1. Olá Veronica!
      Você poderia me encaminhar por e-mail mais detalhes da aplicação e se possível o arquivo ou modelo de como você está fazendo. Assim posso analisar melhor o motivo de não estar funcionando corretamente. Abs!

      Curtir

      1. Alguém conseguiu aumentar i limite de linhas? minha plan tem mais de 40000 linhas, só consigo rodar pesquisando ate 32000
        obrigada

        Curtir

    1. Olá Artur! É possível fazer com o uso de técnicas de fórmula matricial. No exemplo acima, uma possibilidade seria: =ÍNDICE(B2:B7;MAIOR(SE(A2:A7=F2;LIN(A2:A7)-1;””);2))
      Sendo que o que vai determinar a ordem de busca é o segundo argumento da função MAIOR, que na fórmula acima é 2, ou seja, vai trazer o segundo valor encontrado para o nome Rafael. Importante ressaltar que essa fórmula só vai funcionar se inserida com CTRL+SHIFT+ENTER.
      Abraços!

      Curtir

      1. Olá, Rafael, obrigado por compartilhar conhecimento. Estou respondendo a este comentário, pois a solução que eu procuro é próxima ao seu exemplo matricial, com um único detlhate (não solucionado até o momento por mim). O detalhe é fazer a fórmula matricial mostrar apenas os dados sem repetição (buscando de baixo para cima).

        Curtir

    1. Estou com o mesmo problema, é um limitador de linhas da matriz que ele consulta, se vc diminuir verá que funciona.
      Preciso aumentar esse limitador da matriz, se alguém souber como fazer.
      Obrigado

      Curtir

  1. OLÁ!
    tenho uma planilha com muitas linhas e algumas delas com dados iguais, segue um exemplo:

    nome tipo data entrada data saída
    1151 md#1 01/01/2000 01/10/2009
    1152 md#1 04/08/2002
    1151 md#2 06/07/2008
    1153 md#1 06/07/1999
    1151 md#1 05/10/2009

    Gostei muito desse código, mas gostaria de saber se existe uma variação dele que funcione da seguinte forma

    tenho a primeira variável de procura (val1) na coluna NOME encontrando-o faço uma segunda procura na coluna TIPO, fazendo assim uma distinção entre as linhas com nome igual, porque se o valor na segunda coluna não for a variável pesquisada, daí ele pula pra próxima e continua a pesquisa, até achar coluna1 e 2 correspondentes à procura. e aí retornar o valor da coluna DATA ENTRADA.

    COMO UM PROV DE 2 CRITÉRIOS OU ALGO DO TIPO.

    Curtir

  2. Inseri seu código em minha planilha mas a mesma não retorna todos os valores, pulando alguns itens de maneira randômica. Podes me indicar como solucionar tal problema?

    Curtir

  3. Eu utilizei a fórmula PROCVN mais algumas outras para retornar o que eu precisava.

    Pois precisava que retornassem todos resultados na mesma célula.

    =SEERRO(CONCATENAR(PROCVn(C15;$C$2:$D$10;2;1);”,”;PROCVn(C15;$C$2:$D$10;2;2);”,”;PROCVn(C15;$C$2:$D$10;2;3));SEERRO(CONCATENAR(PROCVn(C15;$C$2:$D$10;2;1);”,”;PROCVn(C15;$C$2:$D$10;2;2));PROCV(C15;$C$2:$D$10;2;0)))

    Este exemplo é para até 3 resultados.

    Ainda não testei em uma base de dados grande, mas qualquer ajuste é bem-vindo.

    Curtir

Deixe um comentário

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

Gravatar
Logotipo do WordPress.com

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

Foto do Google

Você está comentando utilizando sua conta Google. 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 )

Conectando a %s