Visual Basic em Português

Página pessoal de Jorge Paulino sobre o Visual Basic (VB.NET, ASP.NET, VB6, VBA) e algumas noticias de tecnologia

Excel: Dicas de VBA - vLookupValues() uma Variante do Vlookup()

A função VLOOKUP (PROCV em Português) é uma função muito interessante e muito utilizada pois permite procurar por um valor numa tabela/matriz e devolver o valor que está na coluna ou na coluna à direita, indicando-se para isso o índice. Existe também a função HLOOKUP (PROCH em Português) para pesquisas na horizontal.

Para quem nunca utilizou, este é o sintaxe da função:

=VLOOKUP(valor procurar; tabela; coluna a devolver; resultado aproximado)

Mas vejamos um exemplo simples para mostrar como funciona a função VLOOKUP.

Como podem ver na imagem anterior temos uma tabela com duas colunas. Na coluna A temos a selecção, utilizando um ‘x’ (podíamos procurar por número, ID, etc.) e na coluna B temos nomes de B2 a B9. Se quisermos procurar o nome que está selecionado podíamos usar a seguinte função:

=VLOOKUP("x";A2:B9;2;FALSE)

O resultado na célula onde colocássemos a função seria “Paulo”

Breve descrição dos argumentos:

  • O primeiro argumento (x) indica o valor a procurar;
  • O segundo argumento (A2:B9) indica a matriz, sendo a coluna da esquerda a coluna onde vamos procurar os valores (neste caso a coluna A);
  • O terceiro argumento (2) indica a coluna de onde será devolvido o valor. Neste caso vamos devolver o valor que está na coluna B (2ª coluna), e por isso indicamos o valor 2, mas se tivéssemos mais colunas, por exemplo números de telefone na coluna C, poderíamos indicar como valor 3 e modificar o segundo argumento de A2:B9 para A2:C9.
  • O quarto argumento (FALSE) indica que não queremos resultados aproximados mas sim exactos

Mas por muito interessante e útil que a função seja só nos devolve um resultado, ou seja, se selecionar dois ou três nomes, apenas irá devolver o primeiro que encontrar.

Usando VBA podemos resolver esta “limitação” e devolver múltiplos resultados de acordo com as selecção. Para isso podemos criar um novo módulo e utilizar o seguinte código:

Public Function VLookupValues(lookupValue As String, _
            lookupRange As Range, _
            columnIndex As Integer, _
            Optional distinct As Boolean = False) _
            As String
           
Dim x As Long
Dim result As String
 
On Error GoTo errVLookupValues
 
    ' Ciclo em todas as linhas do range
    For x = 1 To lookupRange.Rows.Count
   
        Dim r As Range
        Set r = lookupRange.Cells(x, 1)
   
        ' Verifica se o valor é igual ao indicado
        If r.Value = lookupValue Then
       
            Dim str As String
           
            ' Utiliza o Offset para ir buscar a coluna indicada
            str = r.Offset(, columnIndex - 1).Value & ";"
           
            ' Caso se pretenda apenas os valores diferentes e 
            ' já  esteja já na lista não faz a concatenação
            If distinct And InStr(1, result, str, vbTextCompare) = 0 _
                        Or Not distinct Then
                result = result & str
            End If
           
        End If
    Next
   
    ' Se não encontrar nada coloca uma mensagem genérica
    If Len(result) > 0 Then
 
        ' Remove o último ";"
        VLookupValues = Left(result, Len(result) - 1)
    Else
        VLookupValues = "Não Encontrado"
    End If
   
Exit Function
errVLookupValues:
    VLookupValues = ""
   
End Function

Para tornar a função mais versátil foi adicionado um argumento opcional que permite indicar se queremos resultados únicos ou não. O sintaxe para utilizar esta função é o seguinte:

=vLookupValues(valor procurar; tabela; coluna a devolver; resultados únicos)

E utilizando a mesma tabela agora com várias selecções:

Se utilizarmos a função =VLookupValues("x";A2:B9;2;FALSE), em qualquer célula, obtemos o resultado: Rui;Ana;Paulo;Paula

Podemos ainda indicar no último argumento se queremos ou não resultado únicos.

Podem descarregar um exemplo para testar!

1 comentários:

ida ramod disse...

Estou com um problema onde aplique a seguinte formula
Primeira condição

=SE(ÉERROS(ÍNDICE(ITEN_MODELOS!$E$2:$E$40000;(CORRESP($X2&BC$3&$AE2;ITENS_MODELOS!$D$2:$D$40000&ITENS_MODELOS!$E$2:$E$40000&ITENS_MODELOS!$J$2:$J$40000;0))));"";ÍNDICE(ITEN_MODELOS!$E$2:$E$40000;(CORRESP($X2&BC$3&$AE2;ITENS_MODELOS!$D$2:$D$40000&ITENS_MODELOS!$E$2:$E$40000&ITENS_MODELOS!$J$2:$J$40000;0))))


Onde a célula X e BC correrá apenas na vertical e fixa na horizontal e AE correra na horizontal e fixa na vertical isso para um range de 700 colunas e 40000 linhas
O meu problema é que colocando nessa condição o Excel fica muito lento para processar, gostaria da sua ajuda para transformar isso em VBA

Segunda condição

=SE(ÉERROS(SOMASES(ITENS_MODELOS!$F$2:$F$40000;ITENS_MODELOS!$D$2:$D$40000;'ITENS AS-400'!$X2;ITENS_MODELOS!$E$2:$E$40000;'ITENS AS-4000'!PX2)*NV$1);0;SOMASES(ITENS_MODELOS!$F$2:$F$40000;ITENS_MODELOS!$D$2:$D$40000;'ITENS AS-400'!$X2;ITENS_MODELOS!$E$2:$E$40000;'ITENS AS-4000'!PX2)*NV$1)

Onde a célula X correrá apenas na vertical e fixa na horizontal e PX correra na horizontal e na vertical isso para um range de 700 colunas e 40000 linhas




O meu problema é que colocando nessa condição o Excel fica muito lento para processar, gostaria da sua ajuda para transformar isso em VBA

Mensagens Recentes



Microsoft Office Especialist

Membro da Comunidade
Experts-Exchange


Administ. da Comunidade
Portugal-a-Programar



Twitter

Artigos no CodeProject

Artigos no CodeProject

Subscrever Novidades

Endereço de Email:

Delivered by FeedBurner

Seguidores

Histórico