[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

Anúncios

10 comentários sobre “[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. 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

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