agwebrunner.com

PROCV é 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. Vamos criar um utilizável Modelo de fatura para uma empresa fictícia.

VLOOKUP é um Excel função. 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 é um base de dados função, 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? Nós vamos, algum 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 uma 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 esclarecer 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ê pode decidir isso ao criar a fórmula.

Se tudo o que você precisa é 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: Modelo de fatura que podemos reutilizar continuamente em nossa empresa fictícia.

Primeiro, iniciamos o Excel e criamos uma fatura em branco:

Funcionará assim: 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 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, vamos localizar 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 pode ser encontrado clicando primeiro no Fórmulas guia 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 "lookup" (porque a função na qual estamos interessados ​​é um olho para cima função). O sistema nos retornaria uma lista de todas as funções relacionadas à pesquisa no Excel. PROCV é o segundo na lista. Selecione com um clique OK.

O Argumentos de função caixa aparece, solicitando-nos para todos os argumentos (ou parametros) necessário para concluir a função PROCV. Você pode pensar nesta caixa como a função que nos faz as seguintes perguntas:

1. Que identificador exclusivo você está procurando no banco de dados?
2. Onde está o banco de dados?
3. Qual informação do banco de dados, associada ao identificador exclusivo, você deseja recuperar para você?

Os primeiros três argumentos são mostrados em negrito, indicando que eles são obrigatório argumentos (a função PROCV 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 Valor_procurado argumento. A função precisa de nós para dizer onde encontrar o identificador único (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:

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

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

Agora precisamos inserir um valor para o Table_array argumento. Em outras palavras, precisamos informar 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 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 este argumento para especificar para VLOOKUP qual informação do banco de dados, associada ao nosso código de item em A11, desejamos que seja retornada para nós. Neste exemplo específico, desejamos que o item Descrição voltou para nós. Se você olhar a planilha do banco de dados, verá que a coluna “Descrição” é o segundo coluna no banco de dados. Isso significa que devemos inserir um valor de “2” no Col_index_num caixa:

É 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 começou na coluna K da planilha, ainda 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, Pesquisa de alcance. Este argumento requer um verdadeiro ou falso valor, ou deve ser deixado em branco. Ao usar PROCV com bancos de dados (como acontece 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 únicos) for classificada alfabeticamente / numericamente em ordem crescente, então é possível inserir um valor de verdadeiro neste argumento ou deixe em branco.

Se a primeira coluna do banco de dados for não classificado, ou é classificado 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 classificado, nós entramos falso neste argumento:

É isso! Inserimos todas as informações necessárias para que VLOOKUP retorne o valor de que precisamos. Clique no OK botão 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 entrarmos em um diferente código do 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 do item:

Podemos realizar um conjunto semelhante de etapas 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:

... e a fórmula será parecida com esta:

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

Se decidirmos comprar 2 desses itens, inseriremos 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 * E1

... 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:

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 recém-criadas exibam mensagens de erro:

   
   

   
   Podemos remediar isso pelo uso criterioso do Excel E SE() e ESTÁ EM BRANCO() funções. Mudamos nossa fórmula a partir disso ... = PROCV (A11, 'Banco de dados do produto'! A2: D7,2, FALSO)…para isso…= IF (ISBLANK (A11), ””, VLOOKUP (A11, ‘Product Database’! A2: D7,2, FALSE))

   
   

2. 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 à tabela do banco de dados. Poderíamos corrigir isso alterando as referências de células do banco de dados para absoluto referências de células. Alternativamente - 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 de célula. A fórmula mudaria a partir disso ... = IF (ISBLANK (A11), ””, VLOOKUP (A11, ‘Product Database’! 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, desbloquear a outro células) e, em seguida, proteger a planilha, para garantir que nossas fórmulas cuidadosamente elaboradas não sejam substituídas acidentalmente quando alguém vier preencher a fatura
4. Gostaríamos de salvar o arquivo como um modelo, para que possa ser reutilizado por todos em nossa empresa

Se estivéssemos 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.

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