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. Nós vamos criar 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 uma compreensão imediata das funções do Excel e possa usar funções básicas como SUM, AVERAGE e TODAY. Em seu uso mais comum, o VLOOKUP é um base de dados função, 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 contenha uma lista de funcionários, produtos, clientes, CDs em sua coleção de CDs ou estrelas no céu noturno. Isso não importa realmente.
Veja um exemplo de uma lista ou banco de dados. Nesse caso, é uma lista de produtos que nossa empresa fictícia vende:
Normalmente, listas como essa possuem algum tipo de identificador exclusivo para cada item da lista. Nesse caso, o identificador exclusivo está na coluna "Código do item". Nota: Para a função VLOOKUP trabalhar com um banco de dados / lista, essa lista deve 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 o que serve. Então vamos ver se podemos esclarecer isso primeiro:
VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.
No exemplo acima, você inseriria a função VLOOKUP em outra planilha com um código de item e retornaria a 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ê vai passar de volta? Bem, você decide isso quando está criando a fórmula.
Se tudo que você precisa é de um pedaço de informação do banco de dados, seria muito trabalhoso ir para construir uma fórmula com uma função VLOOKUP 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: Modelo de fatura que podemos reutilizar repetidamente em nossa empresa fictícia.
Primeiro, iniciamos o Excel e criamos uma fatura em branco:
Para fins de manter este exemplo simples, vamos localizar o banco de dados do produto em uma folha separada na mesma pasta de trabalho:
Então, criamos nosso banco de dados de produtos, que é assim:
Precisamos localizar a lista de todas as funções disponíveis que o Excel tem para oferecer, para que possamos escolher o VLOOKUP e obter alguma ajuda para completar a fórmula. Isto é encontrado clicando primeiro no Fórmulas guia e, em seguida, clicando Inserir função:
Aparece uma caixa que nos permite selecionar qualquer uma das funções disponíveis no Excel.
Para encontrar o que procuramos, podemos digitar um termo de pesquisa como "pesquisa" (porque a função em que estamos interessados é um olho para cima função). O sistema nos retornaria uma lista de todas as funções relacionadas à pesquisa no Excel. VLOOKUP é o segundo da lista. Selecione um clique Está bem.
o Argumentos da Função caixa aparece, solicitando-nos para todos os argumentos (ou Parâmetros) necessário para completar a função VLOOKUP. Você pode pensar nessa caixa como a função que nos faz as seguintes perguntas:
- Qual identificador único você está procurando no banco de dados?
- Onde está o banco de dados?
- Que informação do banco de dados, associada ao identificador único, você deseja recuperar para você?
Os três primeiros argumentos são mostrados em negrito, indicando que eles sã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 é negrito, o que significa que é opcional:
O primeiro argumento que precisamos completar é o Lookup_value argumento. A função precisa nos dizer onde encontrar o identificador único (o Código do item neste caso) que deveria estar retornando 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:
Em seguida, clique uma vez na célula que contém o código do item (A11) e pressione Entrar:
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 do seletor ao lado do segundo argumento:
Agora localize o banco de dados / lista e selecione a lista inteira - não incluindo a linha de cabeçalho. Em nosso exemplo, o banco de dados está localizado em uma planilha separada, portanto, primeiro clicamos na guia da planilha:
… E pressione Entrar. O intervalo de células que representa o banco de dados (nesse caso, “Banco de dados de produtos”! A2: D7) é inserido automaticamente no segundo argumento.
Agora precisamos entrar no terceiro argumento, Col_index_num. Usamos este argumento para especificar ao VLOOKUP qual informação do banco de dados, associada ao código do nosso item em A11, desejamos ter retornado para nós. Neste exemplo específico, desejamos 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 de “2” no Col_index_num caixa:
É importante notar que não estamos inserindo um "2" aqui porque a coluna "Descrição" está no B coluna nessa planilha. Se o banco de dados aconteceu para iniciar na coluna K da planilha, ainda digitaríamos um "2" neste campo, porque a coluna "Descrição" é a segunda coluna no conjunto de células que selecionamos quando especificamos a matriz "Table_array".
Finalmente, precisamos decidir se vamos inserir um valor no argumento final do VLOOKUP, Pesquisa de alcance. Este argumento requer um verdade ou falso valor, ou deve ser deixado em branco. Ao usar o VLOOKUP com bancos de dados (como é verdade 90% do tempo), a maneira de decidir o que colocar nesse argumento pode ser pensada da seguinte maneira:
If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.
If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument
Como a primeira coluna do nosso banco de dados é não classificados, entramos falso neste argumento:
É isso aí! Entramos todas as informações necessárias para o VLOOKUP para retornar o valor de que precisamos. Clique no Está bem botão e observe que a descrição correspondente ao código de item “R99245” foi corretamente inserida na célula B11:
Se entrarmos em 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 coincidir com o novo código de item:
Podemos realizar um conjunto de etapas semelhante para obter o item preço voltou para a célula E11. Observe que a nova fórmula deve ser criada na célula E11. O resultado será assim:
Note que a única diferença entre as duas fórmulas é o terceiro argumento (Col_index_num) mudou de “2” para “3” (porque queremos dados recuperados da terceira coluna no banco de dados).
Se decidíssemos comprar 2 desses itens, digitaríamos um “2” na célula D11. Em seguida, inserimos uma fórmula simples na célula F11 para obter o total da linha:
=D11*E11
… O que parece com isso…
Concluindo o modelo de fatura
Nós aprendemos muito sobre o VLOOKUP até agora. Na verdade, aprendemos tudo o que vamos aprender 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 completá-lo, faríamos o seguinte:
-
Nós 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:
Image Podemos remediar isso pelo uso criterioso do Excel E SE() e ESTÁ EM BRANCO() funções. Nós mudamos nossa fórmula disto… = VLOOKUP (A11, 'Product Database'! A2: D7,2, FALSE) …para isso… = SE (ISBLANK (A11),””, VLOOKUP (A11, 'Banco de Dados do Produto'! A2: D7,2, FALSE))
- Copiaríamos as fórmulas nas células B11, E11 e F11 até o restante das linhas de itens da fatura. Observe que, se fizermos isso, as fórmulas resultantes não serão mais corretamente referenciadas à tabela do banco de dados. Podemos consertar isso alterando as referências de célula do banco de dados para absoluto referências de células. Alternativamente - e ainda melhor - poderíamos criar um nome do intervalo para o banco de dados do produto inteiro (como "Produtos") e use esse nome de intervalo em vez das referências de célula. A fórmula mudaria disso… = SE (ISBLANK (A11),””, VLOOKUP (A11, 'Banco de Dados do Produto'! A2: D7,2, FALSE)) …para isso… = IF (ISBLANK (A11),””, VLOOKUP (A11, Produtos, 2, FALSE)) …e então copie as fórmulas para o restante das linhas do item da fatura.
- Nós provavelmente "bloquearíamos" as células que contêm nossas fórmulas (ou melhor, desbloquear a de outros células), e depois proteger a planilha, a fim de garantir que nossas fórmulas cuidadosamente construídas não sejam sobrescritas acidentalmente quando alguém preenche a fatura.
- Nós salvaríamos o arquivo como um modelo, para que possa ser reutilizado por todos na nossa empresa
Se estivéssemos nos sentindo realmente inteligente, 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.
