VLOOKUP é uma das funções mais úteis do Excel e também uma das menos compreendidas. Neste artigo, desmistificamos VLOOKUP por meio de um exemplo da vida real. Criaremos um modelo de fatura utilizável para uma empresa fictícia.
VLOOKUP é uma função do Excel . Este artigo presumirá que o leitor já tem uma compreensão geral das funções do Excel e pode usar funções básicas como SOMA, MÉDIA e TODAY. Em seu uso mais comum, VLOOKUP é uma função de banco de dados , o que significa que 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, ou produtos, ou clientes, ou CDs em sua coleção de CDs, ou estrelas no céu noturno. Realmente não importa.
Aqui está um exemplo de lista ou banco de dados. Neste caso, é uma lista de produtos que nossa empresa fictícia vende:
Normalmente, listas como essa têm algum tipo de identificador exclusivo para cada item da lista. Nesse caso, o identificador único está na coluna “Código do item”. Nota: Para a função PROCV funcionar com um banco de dados / lista, essa lista deve ter uma coluna contendo o identificador único (ou “chave” ou “ID”), e essa coluna deve ser a primeira coluna da tabela . Nosso banco de dados de exemplo acima satisfaz esse critério.
A parte mais difícil de usar VLOOKUP é entender exatamente para que serve. Então, vamos ver se podemos deixar isso claro primeiro:
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 PROCV em outra planilha com um código de item, e ela 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 você receberá de volta? Bem, você tem que decidir isso ao criar a fórmula.
Se tudo o que você precisa é de uma informação do banco de dados, seria muito trabalhoso construir uma fórmula com uma função PROCV nela. Normalmente, você usaria esse tipo de funcionalidade em uma planilha reutilizável, como um modelo. Cada vez que alguém insere 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: Um modelo de fatura que podemos reutilizar continuamente em nossa empresa fictícia.
Primeiro, iniciamos o Excel e criamos uma fatura em branco:
Funcionará da seguinte maneira: a pessoa que usa o modelo de fatura preencherá uma série de códigos de item na coluna “A”, e o sistema irá recuperar a descrição e o preço de cada item de nosso banco de dados de produtos. Essa informação será usada para calcular o total da linha para cada item (assumindo que inserimos uma quantidade válida).
Para manter este exemplo simples, localizaremos o banco de dados do produto em uma planilha separada na mesma pasta de trabalho:
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, que se parece com isto:
Para testar a fórmula VLOOKUP que estamos prestes a escrever, primeiro inserimos um código de item válido na célula A11 de nossa fatura em branco:
Em seguida, movemos a célula ativa para a célula na qual queremos que as informações recuperadas do banco de dados por VLOOKUP sejam armazenadas. Curiosamente, essa é a etapa que a maioria das pessoas dá errado. Para explicar melhor: 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 esta descrição seja colocada quando a obtivermos? Na célula B11, é claro. Então é aí que escrevemos a fórmula VLOOKUP: na célula B11. Selecione a célula B11 agora.
Precisamos localizar a lista de todas as funções disponíveis que o Excel tem a oferecer, para que possamos escolher PROCV e obter ajuda no preenchimento da fórmula. Isso é encontrado clicando primeiro na guia Fórmulas e, em seguida, clicando em Inserir Função :
Aparece uma caixa que permite selecionar qualquer uma das funções disponíveis no Excel.
Para encontrar o que estamos procurando, poderíamos digitar um termo de pesquisa como “pesquisa” (porque a função na qual estamos interessados é uma função de pesquisa ). O sistema nos retornaria uma lista de todas as funções relacionadas à pesquisa no Excel. VLOOKUP é o segundo da lista. Selecione-o e clique em OK .
A caixa Argumentos da função é exibida, solicitando todos os argumentos (ou parâmetros ) necessários para concluir a função VLOOKUP. Você pode pensar nesta caixa como a função que nos faz as seguintes perguntas:
- Que identificador exclusivo você está procurando no banco de dados?
- Onde está o banco de dados?
- Qual informação do banco de dados, associada ao identificador exclusivo, você deseja recuperar para você?
Os três primeiros argumentos são mostrados em negrito , indicando que são argumentos obrigatórios (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:
Vamos completar os argumentos em ordem, de cima para baixo.
O primeiro argumento que precisamos completar é o argumento Lookup_value . A função precisa que digamos onde encontrar o identificador exclusivo (o código do item , neste caso) que deve retornar a descrição. Devemos selecionar o código do item que inserimos anteriormente (em A11).
Clique no ícone do seletor à direita do primeiro argumento:
Em seguida, clique uma vez na célula que contém o código do item (A11) e pressione Enter :
O valor de “A11” é inserido no primeiro argumento.
Agora precisamos inserir um valor para o argumento Table_array . Em outras palavras, precisamos dizer a VLOOKUP onde encontrar o banco de dados / lista. Clique no ícone do seletor ao lado do segundo argumento:
Agora localize o banco de dados / lista e selecione a lista inteira – sem incluir a linha de cabeçalho . Em nosso exemplo, o banco de dados está localizado em uma planilha separada, então primeiro clicamos na guia da planilha:
Em seguida, selecionamos todo o banco de dados, sem incluir a linha de cabeçalho:
… E pressione Enter . 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 este argumento para especificar para VLOOKUP qual parte da informação do banco de dados, associada ao nosso código de item em A11, desejamos ter retornado para nós. Neste exemplo específico, desejamos que a descrição do item seja devolvida para nós. Se você olhar a planilha do banco de dados, notará que a coluna “Descrição” é a segunda coluna no banco de dados. Isso significa que devemos inserir um valor de “2” na caixa Col_index_num :
É importante observar que não estamos inserindo um “2” aqui porque a coluna “Descrição” está na coluna B dessa planilha. Se o banco de dados iniciasse na coluna K da planilha, ainda assim inseriríamos um “2” neste campo porque a coluna “Descrição” é a segunda coluna no conjunto de células que selecionamos ao especificar o “Table_array”.
Finalmente, precisamos decidir se devemos inserir um valor no argumento VLOOKUP final, Range_lookup . Este argumento requer um valor verdadeiro ou falso ou deve ser deixado em branco. Ao usar VLOOKUP com bancos de dados (como é verdade 90% das vezes), a maneira de decidir o que colocar neste 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, é possível inserir um valor true neste argumento ou deixá-lo em branco.
Se a primeira coluna do banco de dados não for classificada, ou for classificada em ordem decrescente, você deve inserir o valor false neste argumento
Como a primeira coluna de nosso banco de dados não está classificada, inserimos false neste argumento:
É isso aí! Nós inserimos todas as informações necessárias para PROCV para retornar o valor que precisamos. Clique no botão OK e observe que a descrição correspondente ao código do item “R99245” foi inserida corretamente na célula B11:
A fórmula que foi criada para nós é assim:
Se inserirmos um código de item diferente 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:
Podemos executar um conjunto semelhante de etapas para obter o preço do item de volta na célula E11. Observe que a nova fórmula deve ser criada na célula E11. O resultado será assim:
… e a fórmula ficará assim:
Observe que a única diferença entre as duas fórmulas é que o terceiro argumento ( Col_index_num ) mudou de “2” para “3” (porque queremos os dados recuperados da 3ª 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 …
Preenchendo o modelo de fatura
Aprendemos muito sobre VLOOKUP até agora. Na verdade, aprendemos tudo o que vamos aprender neste artigo. É importante observar que VLOOKUP pode ser usado em outras circunstâncias além de bancos de dados. Isso é menos comum e pode ser abordado em futuros artigos How-To Geek.
Nosso modelo de fatura ainda não está completo. Para completá-lo, faríamos o seguinte:
- 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 recém-criadas exibam mensagens de erro:
Podemos remediar isso usando judiciosamente as funções IF () e ISBLANK () do Excel . Mudamos nossa fórmula deste… = PROCV (A11, ‘Banco de dados do produto’! A2: D7,2, FALSO) … para este… = SE (ISBLANK (A11), ””, VLOOKUP (A11, ‘Banco de dados do produto’! A2 : D7,2, FALSO))
- Copiaríamos as fórmulas nas células B11, E11 e F11 para o restante das linhas de item da fatura. Observe que, se fizermos isso, as fórmulas resultantes não farão mais referência correta à tabela do banco de dados. Poderíamos corrigir isso alterando as referências de células do banco de dados para referências de células absolutas . Como alternativa – e ainda melhor – poderíamos criar um nome de intervalo para todo o banco de dados do produto (como “Produtos”) e usar esse nome de intervalo em vez das referências de células. A fórmula mudaria deste … = IF (ISBLANK (A11), ””, VLOOKUP (A11, ‘Product Database’! A2: D7,2, FALSE)) … para este … = IF (ISBLANK (A11), ”” , PROCV (A11, Produtos, 2, FALSO)) … e então copie as fórmulas para o restante das linhas do item da fatura.
- Provavelmente “bloquearíamos” as células que contêm nossas fórmulas (ou melhor, desbloquearíamos as outras células) e, em seguida, protegeríamos a planilha, a fim de garantir que nossas fórmulas cuidadosamente elaboradas não fossem substituídas acidentalmente quando alguém viesse preencher a fatura.
- Salvaríamos o arquivo como um modelo , para que pudesse ser reutilizado por todos em nossa empresa
Se estivéssemos nos sentindo realmente espertos, criaríamos um banco de dados de todos os nossos clientes em outra planilha e, em seguida, 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.
Se você gostaria de praticar com PROCV ou simplesmente ver nosso Modelo de fatura resultante, ele pode ser baixado aqui .
