Como usar o VLOOKUP no Excel


0

O VLOOKUP é uma das funções mais úteis do Excel e também uma das menos compreendidas. Neste artigo, desmistificamos o VLOOKUP por meio de um exemplo da vida real. Criaremos um utilizável Modelo de fatura para uma empresa fictícia.

VLOOKUP é um Excel função. Este artigo pressupõe que o leitor já tenha um bom entendimento das funções do Excel e pode usar funções básicas como SUM, MÉDIA e HOJE. Em seu uso mais comum, o VLOOKUP é um base de dados , o que significa que ele funciona com tabelas de banco de dados – ou, mais simplesmente, listas de coisas em uma planilha do Excel. Que tipo de coisas? Bem, qualquer tipo de coisa. Você pode ter uma planilha que contém uma lista de funcionários, produtos, clientes ou CDs em sua coleção de CDs ou estrelas no céu noturno. Isso realmente não importa.

Aqui está um exemplo de uma lista ou banco de dados. Nesse caso, é uma lista de produtos que nossa empresa fictícia vende:

503x210xdatabase.png.pagespeed.gp + jp + jw + pj + js + rj + rp + rw + ri + cp + md.ic.u3M88bovhQ

Geralmente, listas como essa têm algum tipo de identificador exclusivo para cada item da lista. Nesse caso, o identificador exclusivo está na coluna “Código do item”. Nota: Para que a função VLOOKUP funcione com um banco de dados / lista, essa lista devo ter uma coluna contendo o identificador exclusivo (ou “chave” ou “ID”) e essa coluna deve ser a primeira coluna na tabela. Nosso banco de dados de amostra acima satisfaz esse critério.

A parte mais difícil de usar o VLOOKUP é entender exatamente para que serve. Então, vamos ver se podemos deixar isso claro primeiro:

O VLOOKUP recupera informações de um banco de dados / lista com base em uma instância fornecida do identificador exclusivo.

No exemplo acima, você inseriria a função VLOOKUP em outra planilha com um código de item e retornaria para você a descrição do item correspondente, seu preço ou sua disponibilidade (sua quantidade “Em estoque”) conforme descrito em seu original Lista. Quais dessas informações serão repassadas a você? Bem, você decide isso quando cria a fórmula.

Se tudo o que você precisa é de uma informação do banco de dados, seria muito difícil construir uma fórmula com uma função VLOOKUP. Normalmente, você usaria esse tipo de funcionalidade em uma planilha reutilizável, como um modelo. Cada vez que alguém digita um código de item válido, o sistema recupera todas as informações necessárias sobre o item correspondente.

Vamos criar um exemplo disso: Modelo de fatura que podemos reutilizar repetidamente em nossa empresa fictícia.

Primeiro, iniciamos o Excel e criamos para nós uma fatura em branco:

fatura

É assim que funciona: a pessoa que usa o modelo de fatura preencherá uma série de códigos de item na coluna “A” e o sistema recuperará a descrição e o preço de cada item no banco de dados de produtos. Essas informações serão usadas para calcular o total de linhas para cada item (supondo que inserimos uma quantidade válida).

Para manter esse exemplo simples, localizaremos o banco de dados do produto em uma folha separada na mesma pasta de trabalho:

folha de seleção

Na realidade, é mais provável que o banco de dados do produto esteja localizado em uma pasta de trabalho separada. Faz pouca diferença para a função VLOOKUP, que realmente não se importa se o banco de dados está localizado na mesma planilha, em uma planilha diferente ou em uma pasta de trabalho completamente diferente.

Então, criamos nosso banco de dados de produtos, com a seguinte aparência:

503x210xdatabase.png.pagespeed.gp + jp + jw + pj + js + rj + rp + rw + ri + cp + md.ic.u3M88bovhQ

Para testar a fórmula VLOOKUP que estamos prestes a escrever, primeiro inserimos um código de item válido na célula A11 da nossa fatura em branco:

Código do item

Em seguida, movemos a célula ativa para a célula na qual queremos que as informações recuperadas do banco de dados pelo VLOOKUP sejam armazenadas. Curiosamente, este é o passo que a maioria das pessoas erra. Para explicar mais: Estamos prestes a criar uma fórmula VLOOKUP que recuperará a descrição que corresponde ao código do item na célula A11. Onde queremos que essa descrição seja colocada quando a obtivermos? Na célula B11, é claro. Então é aqui que escrevemos a fórmula VLOOKUP: na célula B11. Selecione a célula B11 agora.

selectdescription

Precisamos localizar a lista de todas as funções disponíveis que o Excel oferece, para que possamos escolher o VLOOKUP e obter ajuda para concluir a fórmula. Isso é encontrado clicando primeiro no Fórmulas guia e, em seguida, clicando em Inserir Função:

fórmulas

Aparece uma caixa que permite selecionar qualquer uma das funções disponíveis no Excel.

insertfunctionbox

Para encontrar o que estamos procurando, podemos digitar um termo de pesquisa como “pesquisa” (porque a função na qual estamos interessados ​​é um olho para cima função). O sistema retornaria uma lista de todas as funções relacionadas à pesquisa no Excel. VLOOKUP é o segundo na lista. Selecione um clique Está bem.

findlookup

o Argumentos de Função aparece, solicitando todos os argumentos (ou parâmetros) necessário para concluir a função VLOOKUP. Você pode pensar nessa caixa como a função que faz as seguintes perguntas:

  1. Qual identificador exclusivo você está procurando no banco de dados?
  2. Onde fica o banco de dados?
  3. Quais informações do banco de dados, associadas ao identificador exclusivo, você deseja recuperar para você?

Os três primeiros argumentos são mostrados em negrito, indicando que eles estão obrigatório argumentos (a função VLOOKUP está incompleta sem eles e não retornará um valor válido). O quarto argumento não está em negrito, o que significa que é opcional:

funcarguments

Concluiremos os argumentos em ordem, de cima para baixo.

O primeiro argumento que precisamos concluir é o Lookup_value argumento. A função precisa que seja indicado onde encontrar o identificador exclusivo (o Código do item neste caso) que deve retornar a descrição de. Devemos selecionar o código do item que inserimos anteriormente (em A11).

Clique no ícone do seletor à direita do primeiro argumento:

funcarguments1

Em seguida, clique uma vez na célula que contém o código do item (A11) e pressione Entrar:

selectarg1

O valor de “A11” é inserido no primeiro argumento.

Agora precisamos inserir um valor para o Table_array argumento. Em outras palavras, precisamos informar ao VLOOKUP onde encontrar o banco de dados / lista. Clique no ícone seletor ao lado do segundo argumento:

funcarguments2

Agora localize o banco de dados / lista e selecione a lista inteira – não incluindo a linha do cabeçalho. Em nosso exemplo, o banco de dados está localizado em uma planilha separada; portanto, primeiro clicamos na guia dessa planilha:

folha de seleção

Em seguida, selecionamos o banco de dados inteiro, sem incluir a linha do cabeçalho:

640x284xselectarg2.png.pagespeed.gp + jp + jw + pj + js + rj + rp + rw + ri + cp + md.ic.4EDJIujZoM

… E pressione Entrar. O intervalo de células que representa o banco de dados (neste caso, “'Banco de dados do produto'! A2: D7”) é inserido automaticamente para nós no segundo argumento.

Agora precisamos inserir o terceiro argumento, Col_index_num. Usamos esse argumento para especificar ao VLOOKUP quais informações do banco de dados, associadas ao nosso código de item no A11, desejamos ter retornado para nós. Neste exemplo em particular, queremos ter o item descrição voltou para nós. Se você olhar na planilha do banco de dados, perceberá que a coluna “Descrição” é a segundo coluna no banco de dados. Isso significa que devemos inserir um valor “2” no Col_index_num caixa:

arg3

É importante observar que não estamos inserindo um “2” aqui porque a coluna “Descrição” está no B coluna nessa planilha. Se o banco de dados tiver iniciado na coluna K da planilha, ainda inseriríamos um “2” nesse campo, porque a coluna “Descrição” é a segunda coluna no conjunto de células que selecionamos ao especificar o “Table_array”.

Por fim, precisamos decidir se um valor será inserido no argumento final do VLOOKUP, Pesquisa de alcance. Esse argumento requer um verdade ou falso valor ou deve ser deixado em branco. Ao usar o VLOOKUP com bancos de dados (como acontece 90% das vezes), a maneira de decidir o que colocar nesse argumento pode ser pensada da seguinte maneira:

Se a primeira coluna do banco de dados (a coluna que contém os identificadores exclusivos) for classificada alfabeticamente / numericamente em ordem crescente, será possível inserir um valor de verdade nesse argumento ou deixe em branco.

Se a primeira coluna do banco de dados for não classificados ou em ordem decrescente, então você devo insira um valor de falso neste argumento

Como a primeira coluna do nosso banco de dados é não ordenados, entramos falso neste argumento:

arg4

É isso aí! Inserimos todas as informações necessárias para que o VLOOKUP retorne o valor necessário. Clique no Está bem e observe que a descrição correspondente ao código do item “R99245” foi inserida corretamente na célula B11:

509x149xdescfilledin.png.pagespeed.gp + jp + jw + pj + js + rj + rp + rp + ri + cp + md.ic.oZ5mPW4wRY

A fórmula que foi criada para nós é assim:

Fórmula

Se inserirmos um diferente código de item na célula A11, começaremos a ver o poder da função VLOOKUP: A célula de descrição muda para corresponder ao novo código de item:

changecode

Podemos executar um conjunto semelhante de etapas para obter os itens preço retornado para a célula E11. Observe que a nova fórmula deve ser criada na célula E11. O resultado será semelhante a este:

2ndformula

… E a fórmula ficará assim:

2ndformula

Observe que a única diferença entre as duas fórmulas é o terceiro argumento (Col_index_num) mudou de “2” para “3” (porque queremos que os dados sejam recuperados da terceira coluna no banco de dados).

Se decidíssemos comprar 2 desses itens, inseriríamos um “2” na célula D11. Em seguida, inseriríamos uma fórmula simples na célula F11 para obter o total da linha:

= D11 * E11

… que se parece com isso …

completo

Preenchendo o modelo de fatura

Até agora aprendemos muito sobre o VLOOKUP. De fato, aprendemos tudo o que aprenderemos neste artigo. É importante observar que o VLOOKUP pode ser usado em outras circunstâncias além dos bancos de dados. Isso é menos comum e pode ser abordado em futuros artigos do How-To Geek.

Nosso modelo de fatura ainda não está completo. Para concluir, faríamos o seguinte:

  1. Removeríamos o código do item de amostra da célula A11 e o “2” da célula D11. Isso fará com que nossas fórmulas VLOOKUP criadas recentemente exibam mensagens de erro:
    erros

    Podemos remediar isso usando criteriosamente os recursos do Excel E SE() e ESTÁ EM BRANCO() funções. Nós mudamos nossa fórmula disso … = VLOOKUP (A11, 'Banco de dados do produto'! A2: D7,2, FALSE)…para isso…= SE (ISBLANK (A11), “”, VLOOKUP (A11, 'Banco de dados de produtos'! A2: D7,2, FALSE))

  2. Copiaríamos as fórmulas nas células B11, E11 e F11 até o restante das linhas do item da fatura. Observe que, se fizermos isso, as fórmulas resultantes não se referirão mais corretamente à tabela do banco de dados. Podemos corrigir isso alterando as referências de célula do banco de dados para absoluto referências de célula. Como alternativa – e ainda melhor -, poderíamos criar um nome do intervalo para todo o banco de dados do produto (como “Produtos”) e use esse nome de intervalo em vez das referências à célula. A fórmula mudaria a partir disso … = SE (ISBLANK (A11), “”, VLOOKUP (A11, 'Banco de dados de produtos'! A2: D7,2, FALSE))…para isso… = SE (ISBLANK (A11), ””, VLOOKUP (A11, Produtos, 2, FALSO))…e então copie as fórmulas para o restante das linhas do item da fatura.
  3. Provavelmente “bloquearíamos” as células que contêm nossas fórmulas (ou melhor, destravar a de outros células) e, em seguida, proteja a planilha para garantir que nossas fórmulas cuidadosamente construídas não sejam substituídas acidentalmente quando alguém vier preencher a fatura.
  4. Salvaríamos o arquivo como um modelo, para que pudesse ser reutilizado por todos em nossa empresa

Se estivéssemos nos sentindo realmente inteligente, criaríamos um banco de dados de todos os nossos clientes em outra planilha e usaríamos o ID do cliente inserido na célula F5 para preencher automaticamente o nome e o endereço do cliente nas células B6, B7 e B8.

clientes

Se você deseja praticar com o VLOOKUP, ou simplesmente ver nosso modelo de fatura resultante, ele pode ser baixado aqui.


Like it? Share with your friends!

0

What's Your Reaction?

hate hate
0
hate
confused confused
0
confused
fail fail
0
fail
fun fun
0
fun
geeky geeky
0
geeky
love love
0
love
lol lol
0
lol
omg omg
0
omg
win win
0
win

0 Comments

Your email address will not be published. Required fields are marked *