Todos os segredos da função VLOOKUP do Excel para localizar dados em uma tabela e extraí-los para outra

2024 Autor: Malcolm Clapton | [email protected]. Última modificação: 2023-12-17 04:08

Depois de ler o artigo, você aprenderá não apenas como localizar dados em uma planilha do Excel e extraí-los em outra, mas também técnicas que podem ser usadas em conjunto com a função PROCV.

Ao trabalhar no Excel, muitas vezes é necessário localizar os dados de uma tabela e extraí-los em outra. Se você ainda não sabe como fazer isso, depois de ler o artigo, você não apenas aprenderá como fazer, mas também descobrirá em que condições pode extrair o desempenho máximo do sistema. A maioria das técnicas muito eficazes que devem ser usadas em conjunto com a função VLOOKUP são consideradas.

Mesmo que você use a função PROCV por anos, com um alto grau de probabilidade este artigo será útil para você e não o deixará indiferente. Por exemplo, sendo um especialista em TI, e depois chefe em TI, uso o VLOOKUP há 15 anos, mas só consegui lidar com todas as nuances agora, quando comecei a ensinar Excel profissionalmente às pessoas.

PROCV é uma abreviatura para vvertical NSinspeção. Da mesma forma, VLOOKUP - Vertical LOOKUP. O próprio nome da função nos indica que ela pesquisa nas linhas da tabela (verticalmente - iterando nas linhas e fixando a coluna), e não nas colunas (horizontalmente - iterando nas colunas e corrigindo a linha). Deve-se notar que VLOOKUP tem uma irmã - um patinho feio que nunca se tornará um cisne - esta é a função HLOOKUP. HLOOKUP, ao contrário de VLOOKUP, realiza pesquisas horizontais, mas o conceito de Excel (e de fato o conceito de organização de dados) implica que suas tabelas têm menos colunas e muito mais linhas. É por isso que precisamos pesquisar por linhas com muito mais frequência do que por colunas. Se você usa a função HLO com muita frequência no Excel, é provável que não tenha entendido algo nesta vida.

Sintaxe

A função VLOOKUP tem quatro parâmetros:

= PROCV (;; [;]), aqui:

- o valor desejado (raramente) ou uma referência a uma célula contendo o valor desejado (a grande maioria dos casos);

- referência a um intervalo de células (array bidimensional), na coluna PRIMEIRA (!) da qual o valor do parâmetro será pesquisado;

- número da coluna no intervalo a partir do qual o valor será retornado;

- este é um parâmetro muito importante que responde à pergunta se a primeira coluna do intervalo está classificada em ordem crescente. Se a matriz for classificada, especificamos o valor TRUE ou 1, caso contrário - FALSE ou 0. Se este parâmetro for omitido, o padrão é 1.

Aposto que muitos dos que conhecem a função VLOOKUP como escamosa, depois de ler a descrição do quarto parâmetro, podem se sentir desconfortáveis, pois estão acostumados a vê-lo de uma forma um pouco diferente: geralmente estão falando sobre uma correspondência exata quando procurando (FALSE ou 0) ou uma varredura de intervalo (TRUE ou 1).

Agora você precisa se esforçar e ler o próximo parágrafo várias vezes até sentir o significado do que foi dito no final. Cada palavra é importante aí. Os exemplos o ajudarão a descobrir.

Como funciona exatamente a fórmula VLOOKUP?

• Fórmula tipo I. Se o último parâmetro for omitido ou especificado igual a 1, então VLOOKUP assume que a primeira coluna está classificada em ordem crescente, então a pesquisa pára na linha que precede imediatamente a linha que contém o valor maior que o desejado … Se nenhuma string for encontrada, a última linha do intervalo será retornada.

  

• Fórmula II. Se o último parâmetro for especificado como 0, o VLOOKUP verifica a primeira coluna da matriz sequencialmente e interrompe imediatamente a pesquisa quando a primeira correspondência exata com o parâmetro for encontrada, caso contrário, o código de erro # N / A (# N / A) é devolvido.

  

Fluxos de trabalho de fórmulas

VPR tipo I

VPR tipo II

Corolários para fórmulas da forma I

1. As fórmulas podem ser usadas para distribuir valores entre intervalos.
2. Se a primeira coluna contiver valores duplicados e for classificada corretamente, a última das linhas com valores duplicados será retornada.
3. Se você pesquisar um valor que é obviamente maior do que a primeira coluna pode conter, você poderá encontrar facilmente a última linha da tabela, que pode ser bastante valiosa.
4. Esta visualização retornará o erro # N / A somente se não encontrar um valor menor ou igual ao desejado.
5. É bastante difícil entender que a fórmula retorna os valores errados se sua matriz não estiver classificada.

Corolários para fórmulas do tipo II

Se o valor desejado ocorrer várias vezes na primeira coluna da matriz, a fórmula selecionará a primeira linha para recuperação de dados subsequente.

Desempenho VLOOKUP

Você atingiu o clímax do artigo. Parece, bem, qual é a diferença se eu especificar zero ou um como o último parâmetro? Basicamente, todo mundo indica, é claro, zero, já que isso é bastante prático: você não precisa se preocupar em ordenar a primeira coluna do array, você pode ver imediatamente se o valor foi encontrado ou não. Mas se você tiver vários milhares de fórmulas VLOOKUP em sua planilha, notará que VLOOKUP II é lento. Ao mesmo tempo, geralmente todos começam a pensar:

• Preciso de um computador mais potente;
• Eu preciso de uma fórmula mais rápida, por exemplo, muitas pessoas sabem sobre INDEX + MATCH, que é supostamente mais rápido por míseros 5-10%.

E poucas pessoas pensam que assim que você começar a usar VLOOKUP do tipo I e garantir que a primeira coluna seja classificada por qualquer meio, a velocidade de VLOOKUP aumentará 57 vezes. Estou escrevendo em palavras - CINQUENTA E SETE VEZES! Não 57%, mas 5.700%. Eu verifiquei esse fato de forma bastante confiável.

O segredo de um trabalho tão rápido está no fato de que um algoritmo de busca extremamente eficiente pode ser aplicado em uma matriz ordenada, que é chamada de busca binária (método da redução pela metade, método da dicotomia). Então VLOOKUP do tipo I o aplica, e VLOOKUP do tipo II pesquisa sem nenhuma otimização. O mesmo é verdadeiro para a função MATCH, que inclui um parâmetro semelhante, e a função LOOKUP, que só funciona em matrizes classificadas e é incluída no Excel para compatibilidade com o Lotus 1-2-3.

Desvantagens da fórmula

As desvantagens de PROCV são óbvias: em primeiro lugar, ele pesquisa apenas na primeira coluna do array especificado e, em segundo lugar, apenas à direita desta coluna. E, como você entende, pode muito bem acontecer que a coluna que contém as informações necessárias fique à esquerda da coluna que estaremos procurando. A já mencionada combinação de fórmulas INDEX + MATCH não tem este inconveniente, o que a torna a solução mais flexível para extrair dados de tabelas em comparação com VLOOKUP (VLOOKUP).

Alguns aspectos da aplicação da fórmula na vida real

Pesquisa de alcance

Uma ilustração clássica de uma pesquisa de faixa é a tarefa de determinar um desconto por tamanho do pedido.

Pesquisa por strings de texto

Obviamente, PROCV procura não apenas números, mas também texto. Deve-se ter em mente que a fórmula não distingue entre o caso de personagens. Se você usar curingas, poderá organizar uma pesquisa difusa. Existem dois curingas: "?" - substitui qualquer caractere em uma string de texto, "*" - substitui qualquer número de qualquer caractere.

Espaços de combate

Freqüentemente, surge a questão de como resolver o problema de espaços extras durante a pesquisa. Se a tabela de pesquisa ainda puder ser limpa, o primeiro parâmetro da fórmula VLOOKUP nem sempre depende de você. Portanto, se houver o risco de obstruir as células com espaços extras, você pode usar a função TRIM para eliminá-lo.

Formato de dados diferente

Se o primeiro parâmetro da função VLOOKUP se referir a uma célula que contém um número, mas que está armazenada na célula como texto, e a primeira coluna da matriz contiver números no formato correto, a pesquisa falhará. A situação oposta também é possível. O problema pode ser facilmente resolvido traduzindo o parâmetro 1 para o formato necessário:

= PROCV (−− D7; Produtos! $ A $ 2: $ C $ 5; 3; 0) - se D7 contém texto e a tabela contém números;

= PROCV (D7 & ""); Produtos! $ A $ 2: $ C $ 5; 3; 0) - e vice-versa.

A propósito, você pode traduzir texto em um número de várias maneiras ao mesmo tempo, escolha:

• Negação dupla -D7.
• Multiplicação por um D7 * 1.
• Soma zero D7 + 0.
• Elevando à primeira potência D7 ^ 1.

A conversão de um número em texto é feita por meio de concatenação com uma string vazia, o que força o Excel a converter o tipo de dados.

Como suprimir # N / A

Isso é muito conveniente para fazer com a função IFERROR.

Por exemplo: = IFERROR (VLOOKUP (D7; Produtos! $ A $ 2: $ C $ 5; 3; 0); "").

Se VLOOKUP retornar o código de erro # N / A, então IFERROR irá interceptá-lo e substituir o parâmetro 2 (neste caso, uma string vazia), e se nenhum erro ocorrer, esta função fingirá que não existe, mas há apenas VLOOKUP que retornou um resultado normal.

Variedade

Freqüentemente, eles se esquecem de tornar a referência da matriz absoluta e, ao esticar, a matriz "flutua". Lembre-se de usar $ A $ 2: $ C $ 5 em vez de A2: C5.

É uma boa ideia colocar a matriz de referência em uma folha separada da pasta de trabalho. Não fica sob os pés e será mais seguro.

Uma ideia ainda melhor seria declarar esse array como um intervalo nomeado.

Muitos usuários, ao especificar um array, usam uma construção como A: C, especificando colunas inteiras. Essa abordagem tem o direito de existir, já que você não precisa acompanhar o fato de que seu array inclui todas as strings necessárias. Se você adicionar linhas à folha com a matriz original, o intervalo especificado como A: C não precisa ser ajustado. Claro, essa construção sintática força o Excel a fazer um pouco mais de trabalho do que especificar o intervalo exatamente, mas essa sobrecarga pode ser negligenciada. Estamos falando de centésimos de segundo.

Bem, à beira do gênio - para organizar a matriz na forma.

Usando a função COLUMN para especificar a coluna a extrair

Se a tabela na qual você está recuperando dados usando VLOOKUP tiver a mesma estrutura da tabela de pesquisa, mas simplesmente contiver menos linhas, você poderá usar a função COLUMN () em VLOOKUP para calcular automaticamente os números das colunas a serem recuperadas. Neste caso, todas as fórmulas VLOOKUP serão iguais (ajustadas para o primeiro parâmetro, que muda automaticamente)! Observe que o primeiro parâmetro possui uma coordenada de coluna absoluta.

Criando uma chave composta com & "|" &

Se for necessário pesquisar por várias colunas ao mesmo tempo, é necessário fazer uma chave composta para a pesquisa. Se o valor de retorno não fosse textual (como é o caso com o campo "Código"), mas numérico, então a fórmula SOMASE mais conveniente seria adequada para isso e a chave de coluna composta não seria necessária.

Este é meu primeiro artigo para um Lifehacker. Se gostou, convido você a visitar, e também com prazer ler nos comentários sobre seus segredos de usar a função VLOOKUP e similares. Obrigado.:)