pt.phhsnews.com


pt.phhsnews.com / Como usar o VLOOKUP no Excel

Como usar o VLOOKUP no Excel


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

VLOOKUP é uma função do Excel . 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 é uma função do banco de dados , o que significa que ele trabalha com tabelas de banco de dados - ou mais simplesmente, lista de coisas em uma planilha do Excel. Que tipo de coisas? Bem, qualquer 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 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:

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 a função VLOOKUP funcionar 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 do uso do VLOOKUP é entender exatamente para que serve. Então vamos ver se podemos esclarecer isso primeiro:

O VLOOKUP recupera informações de um banco de dados / lista baseado em uma instância fornecida do identificador único.

No exemplo acima, você poderia inserir a função VLOOKUP em outra planilha com um código de item, e ele retornaria para você a descrição do item correspondente, seu preço ou sua disponibilidade (sua quantidade em estoque), conforme descrito na sua lista original. Quais dessas informações você vai passar de volta? Bem, você pode decidir isso quando estiver criando a fórmula.

Se tudo que você precisa é de um pedaço de informação do banco de dados, seria muito difícil ir para construir uma fórmula com uma função VLOOKUP. isto. 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 repetidamente em nosso Em primeiro lugar, iniciamos o Excel e criamos uma fatura em branco:

É 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 do nosso banco de dados de produtos. Essas informações serão usadas para calcular o total de linhas para cada item (assumindo que insira uma quantidade válida).

Para fins de 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 parece assim:

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:

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, este é o passo que a maioria das pessoas erram. 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 essa descrição quando a conseguimos? 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 o VLOOKUP e obter alguma ajuda para completar a fórmula. Isso é encontrado clicando primeiro na guia

Fórmulas e, em seguida, clicando em Inserir Função : Aparece uma caixa que nos permite selecionar qualquer uma das funções disponíveis no Excel.

Para encontrar o que estamos procurando, podemos digitar um termo de pesquisa como "lookup" (porque a função em que estamos interessados ​​é uma função

lookup ). O sistema retornaria uma lista de todas as funções relacionadas à pesquisa no Excel. VLOOKUP é o segundo da lista. Selecione um clique OK . A caixa

Argumentos de função aparece, solicitando a todos os argumentos (ou parâmetros ) necessários para completar a função VLOOKUP. Você pode pensar nessa caixa como a função que faz as seguintes perguntas: Qual identificador exclusivo você está procurando no banco de dados?

  1. Onde está o banco de dados?
  2. Qual informação do banco de dados, associada a o identificador exclusivo, você deseja ter recuperado para você?
  3. Os três primeiros argumentos são mostrados

em negrito , indicando que eles são obrigatórios argumentos (a função VLOOKUP está incompleta sem e não retornará um valor válido). O quarto argumento não é 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 nos 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

Enter : O valor de "A11" é inserido no primeiro argumento.

Agora precisamos inserir um valor para o argumento

Table_array . 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: Em seguida, selecionamos o banco de dados inteiro, sem incluir a linha de cabeçalho:

… e pressione

Digite . O intervalo de células que representa o banco de dados (neste caso, "Banco de Dados de Produto! A2: D7") é inserido automaticamente no segundo argumento. Agora precisamos inserir o 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 que a descrição do item seja devolvida para nós. Se você olhar na planilha do banco de dados, observará que a coluna "Descrição" é a coluna segunda 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á em a coluna

B nessa planilha. Se o banco de dados tivesse começado na coluna K da planilha, ainda digitaríamos um “2” nesse campo, porque a coluna “Descrição” é a segunda coluna no conjunto de células que selecionamos quando especificamos “ Table_array ”. Finalmente, precisamos decidir se devemos inserir um valor no argumento final VLOOKUP,

Range_lookup . Esse argumento requer um valor verdadeiro ou falso ou deve ser deixado em branco. Ao usar 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: Se a primeira coluna do banco de dados (a coluna que contém a única identificadores) é classificado alfabeticamente / numericamente em ordem crescente, então é possível inserir um valor de

true neste argumento, ou deixe-o em branco. Se a primeira coluna do banco de dados for

não classificada ou classificada em ordem decrescente, você deverá inserir um valor de false para este argumento Como a primeira coluna de nosso banco de dados é

não classificada, inserimos falso neste argumento: isto! Entramos todas as informações necessárias para o VLOOKUP 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 corretamente inserida na célula B11: A fórmula que foi criada para nós se parece com isto:

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 é alterada para corresponder ao novo código de item: Podemos executar um conjunto de etapas semelhante para obter o preço

do item retornou para a célula E11. Observe que a nova fórmula deve ser criada na célula E11. O resultado será parecido com isto: … e a fórmula ficará assim:

Observe que a única diferença entre as duas fórmulas é o terceiro argumento (

Col_index_num ) mudou de um “2 ”Para um“ 3 ”(porque queremos dados recuperados da 3ª coluna no banco de dados) Se decidirmos 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

… que se parece com isso…

Conclusão do modelo de fatura

Aprendemos muito sobre o VLOOKUP tão longe. 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 artigos How-To Geek futuros.

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:

  1. Podemos solucionar isso pelo uso criterioso das funções


    IF () e ISBLANK () do Excel. Nós mudamos nossa fórmula disto… = VLOOKUP (A11, 'Banco de Dados de Produto'! A2: D7,2, FALSE) … para isto… = SE (ISBLANK (A11), ””, VLOOKUP (A11, 'Product Database'! 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. Poderíamos corrigir isso alterando as referências de célula para o banco de dados para referências de célula
  2. absolutas . Alternativamente - e ainda melhor - poderíamos criar um nome de intervalo para o banco de dados do produto inteiro (como "Produtos") e usar esse nome de intervalo em vez das referências de célula. A fórmula mudaria disso… = IF (ISBLANK (A11), ””, VLOOKUP (A11, 'Banco de Dados de Produto'! A2: D7,2, FALSE)) … para este… = SE (ISBLANK (A11), ””, VLOOKUP (A11, Produtos, 2, FALSO)) … e e copie as fórmulas para o restante das linhas do item da fatura. provavelmente "bloquear" as células que contêm nossas fórmulas (ou melhor,
  3. desbloquear as outras células) e proteger a planilha, a fim de garantir que nossas fórmulas cuidadosamente construídas não sejam acidentalmente sobrescrito quando alguém chega para preencher a fatura Salvaríamos o arquivo como um
  4. modelo , para que ele 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 depois 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. gostaria de praticar com o VLOOKUP ou simplesmente ver nossa fatura resultante Emplate, ele pode ser baixado aqui.


O Tor é realmente anônimo e seguro?

O Tor é realmente anônimo e seguro?

Algumas pessoas acreditam que o Tor é uma forma completamente anônima, privada e segura de acessar a Internet sem que ninguém possa monitorar sua navegação e rastreá-la até você - mas é isso? Não é tão simples assim. Tor não é a solução perfeita de anonimato e privacidade. Ele tem várias limitações e riscos importantes, que você deve saber se vai usá-lo.

(how-to)

5 tarefas que deveriam ter sido mais simples no Windows 8

5 tarefas que deveriam ter sido mais simples no Windows 8

Eu tenho usado o Windows 8 por um tempo agora e mesmo que eu goste de usá-lo, ainda existem alguns aspectos realmente irritantes do sistema operacional que eu simplesmente não entendo. Por exemplo, por que é tão difícil fazer coisas que costumavam ser tão fáceis? Reinicie seu computador? Imprimir a partir de um aplicativo do Windows? O sis

(How-to)