Aplicação e definição desta função Excel
O VLOOKUP é uma função do Excel com a qual o usuário pode pesquisar e avaliar o conteúdo da tabela. Esta função está disponível nas versões do Excel 2007 para Windows e Mac.
O que é VLOOKUP?
Os possíveis usos de PROCV devem ser explicados aqui usando um exemplo: Neste você é um grande fã de literatura e, portanto, criou sua própria planilha Excel na qual pode classificar cuidadosamente os livros que coletou. Cada trabalho é inserido com informações sobre as seguintes categorias:
-
autor
-
título
-
Número de página
-
Ano de publicação
Agora você gostaria de dar a um amigo uma dica de livro para levar com você em sua próxima reunião. Infelizmente, você só pode pensar no autor, não no título do livro. É aqui que VLOOKUP entra em jogo, pois ele pode usar esse valor de entrada para descartar as informações que você está procurando de uma só vez.
Como VLOOKUP é usado?
Antes mesmo de pensar em formular fórmulas, deve-se determinar onde o campo de entrada e os diversos campos de saída estarão localizados posteriormente. Para fazer isso, faz sentido criar uma tabela separada que esteja inicialmente vazia e, portanto, permita espaço para as informações mencionadas. Se você projetar esta nova tabela com base no exemplo da tabela existente, terá uma vantagem de economia de tempo mais tarde.
Com base nisso, a fórmula VLOOKUP pode ser criada manualmente ou gerada automaticamente pelo Excel. Para iniciantes, vale a pena usar a última abordagem para conhecer gradualmente a estrutura e o efeito da fórmula. Para fazer isso, o botão para “Inserir função” é selecionado na guia “Fórmulas”. A VLOOKUP está oculta na janela que é aberta. Após a confirmação, uma janela é aberta novamente na qual os quatro parâmetros da fórmula podem ser preenchidos. Estes são:
-
Critério de pesquisa
-
matriz
-
Índice de coluna
-
Area_reference
O rascunho bruto da fórmula, portanto, se parece com isto:
= VLOOKUP (critério de pesquisa, matriz, índice de coluna, range_link)
e em um aplicativo possível como este:
= PROCV (H3; A3: E40; 5)
Critério de pesquisa
Para que a função saiba qual valor deve ser usado como ponto de partida, a linha que foi selecionada como campo de entrada duas etapas anteriores é anotada no campo "Critério de pesquisa". Em nosso exemplo, o nome do autor do livro “Phillip Pulmann” é inserido lá. Isso torna a fórmula flexível e não precisa ser ajustada novamente assim que o valor inserido muda.
matriz
O campo de entrada "Matriz" descreve a tabela na qual as informações a serem geradas podem ser encontradas. Esta matriz especial também contém as colunas para o título do livro, o número da página e o ano de publicação.
A matriz é completamente selecionada uma vez sem os títulos da margem superior esquerda para a margem inferior direita. Desta forma, o Excel sabe qual conteúdo deve ser levado em consideração na avaliação.
Índice de coluna
O campo de entrada para o "índice da coluna" solicita ao usuário a definição da coluna da matriz na qual apenas o valor buscado é listado. A atribuição das colunas é numerada cronologicamente. Isso significa que a primeira coluna da tabela recebe o valor 1, a segunda o valor 2, etc. Em nosso exemplo, isso corresponde ao índice da coluna 1 para o autor, índice da coluna 2 para o título, índice da coluna 3 para o número da página e índice de coluna 4 para o ano de publicação.
Para manter a tabela o mais flexível possível, o título da coluna pode ser vinculado em vez do número. Isso tem a vantagem de que a fórmula também pode ser transferida para outras linhas sem problemas, pois o cabeçalho da coluna pode ser adaptado de forma flexível a cada vez.
Atenção: O VLOOKUP lê a matriz da esquerda para a direita, por isso o índice da coluna deve ser colocado à direita da coluna para o critério de pesquisa para ser levado em consideração pela função!
Area_reference
O parâmetro "Range_Lookup" completa a fórmula VLOOKUP especificando a precisão com a qual a tabela é avaliada. No entanto, ele difere dos componentes da fórmula mencionados anteriormente porque é opcional. Se o valor 0 for inserido para "incorreto", o Excel pesquisará apenas o valor que foi especificado como critério de pesquisa. Com o valor 1 para "verdadeiro", no entanto, a busca continua por valores óbvios se o valor exato não puder ser encontrado.
Especificar este parâmetro é opcional porque o valor 1 é definido por padrão. Esta configuração será útil posteriormente no PROCV avançado com vários critérios de pesquisa.
A fusão
Assim que todos os parâmetros necessários forem configurados, VLOOKUP pode ser usado. Depois de inserir o critério de pesquisa e confirmar a função, o valor que você está procurando aparece na linha que foi definida como um campo de saída.
Em nosso exemplo, o título do livro “The Golden Compass” agora é exibido, o que corresponde ao autor inserido. Para descobrir rapidamente o número da página e o ano de publicação, nada mais precisa ser feito do que arrastar a fórmula VLOOKUP existente para as células subsequentes. Isso é tão fácil porque o índice da coluna de VLOOKUP foi vinculado ao título da coluna da primeira tabela e a segunda tabela também está estruturada na mesma ordem.
No caso de as tabelas serem diferentes umas das outras ou de ocorrer um erro apesar de tudo, a fórmula VLOOKUP também pode ser alterada manualmente. Para fazer isso, o penúltimo dígito para o índice da coluna deve corresponder à coluna do novo valor a ser gerado.
PROCV com vários critérios de pesquisa
Muitas vezes acontece que um único critério de pesquisa não é suficiente para avaliar com precisão uma grande tabela do Excel. Então, faz sentido executar VLOOKUP com vários critérios de pesquisa. Para fazer isso, a fórmula existente deve ser complementada com uma função IF adicional. Desta forma, até oito critérios de pesquisa diferentes podem ser considerados durante a aplicação.
PROCV em várias planilhas do Excel
Se o critério de pesquisa não puder ser encontrado apenas em uma tabela, mas possivelmente também em outra, a fórmula VLOOKUP pode ser ajustada de acordo. Para isso, tanto uma função if quanto uma função ISERROR devem ser colocadas na frente da fórmula existente. Cinco parâmetros são necessários para isso:
-
Critério de pesquisa
-
Matrix1 e Matrix 2
-
Coluna índice1 e coluna índice2
O resultado é o seguinte:
= SE (ISERROR (VLOOKUP (critério de pesquisa, matriz1, índice de coluna1, 0));
VLOOKUP (critério de pesquisa; matriz2; índice da coluna 2,0); VLOOKUP (critério de pesquisa; matriz1; índice da coluna 1;))
e em um aplicativo possível como este:
= SE (ISERROR (VLOOKUP (E5, A5: B9,2, 0)), VLOOKUP (E5, A13: B17,2, 0), VLOOKUP (E5, A5: B9,2, 0))
O critério de pesquisa é usado para inserir o valor a ser pesquisado nas duas tabelas. Matriz1 e Matriz2 definem as respectivas áreas de células das duas tabelas. A coluna index1 e a coluna index2 são usadas para definir com mais detalhes quais colunas das respectivas tabelas devem ser pesquisadas.
Se o valor que você está procurando ocorrer em ambas as tabelas, o Excel produzirá o resultado da primeira tabela. No entanto, se o valor não for encontrado em nenhuma das duas tabelas, será exibida uma mensagem de erro. A vantagem da fórmula é que as duas listas não precisam ter a mesma estrutura ou ter o mesmo tamanho.
Atribua valores a categorias usando VLOOKUP
Uma função adicional de VLOOKUP permite que os valores listados sejam divididos automaticamente em letras e predicados de sua escolha. Em nosso exemplo anterior, uma coluna de tabela adicional deve ser inserida para o tipo de livro. Os livros com até 50 páginas devem se enquadrar no gênero do conto, enquanto os livros de 51 a 150 páginas são atribuídos à novela e de 151 páginas ao romance. Para que isso seja possível, nenhuma fórmula adicional é necessária no VLOOKUP, apenas o uso das chaves “{}”. A fórmula final é parecida com esta:
= PROCV (B1; {1. "Conto"; 51. "Novela"; 151. "Romance"}; 2)
O conteúdo das chaves indica uma matriz que define a área de um respectivo tipo de livro. A atribuição do comprimento lateral ao gênero apropriado é, portanto, alojada entre as chaves. A fórmula usa pares de valores, cada um separado por um ponto. A matriz {1. "História curta"; 51. "Novela"; 151. "Romance"} é lida da seguinte forma:
"De 1 mostre um conto, de 51 mostre uma novela, de 151 mostre um romance."
Esta matriz pode ser facilmente adaptada a diferentes tarefas. Isso diz respeito, por um lado, ao tamanho e ao número das matrizes, bem como à sua designação. Portanto, é possível gerar strings ou números como resultado, em vez de letras individuais. Tudo que você precisa fazer é ajustar as letras na fórmula.
PROCV em várias planilhas
Outra função de VLOOKUP permite que seus usuários vinculem conteúdo localizado em planilhas diferentes. Para o nosso exemplo, esta opção pode ser útil quando as informações são classificadas primeiro em planilhas diferentes e depois atualizadas em uma tabela de resumo.
Imagine que, além de seus livros, você também relacione seus filmes coletados em uma planilha do Excel. Em seguida, você combina as duas coleções em uma grande mesa.
A vantagem desse procedimento reside não apenas no aumento da ordem, mas também em evitar possíveis erros. Se você deseja criar uma nova entrada ou atualizar uma existente, não precisa pesquisar na tabela grande, mas pode acessar as menores. Os valores são então transferidos automaticamente para a tabela de compactação do Excel. Isso torna supérflua a reescrita na mesa grande, o que, na melhor das hipóteses, evita uma jogada infeliz e um subsequente encadeamento de mensagens de erro.
Qual é a aparência da fórmula?
Esta função é possível novamente inserindo outra fórmula. Embora a pesquisa com vários critérios exigisse uma fórmula IF adicional, trabalhar com várias planilhas exige uma fórmula INDIRETA. Isso permite que um intervalo de outra planilha seja especificado para a matriz VLOOKUP.
= PROCV (critério de pesquisa; INDIRETO (matriz); índice da coluna; intervalo_link)
Atenção: Esta fórmula só funcionará se as tabelas individuais nas várias planilhas tiverem os mesmos nomes que os títulos das colunas da tabela geral. Tabelas inteiras podem ser nomeadas no "campo Nome" no canto superior esquerdo acima da grade de células. As tabelas que já foram nomeadas podem ser visualizadas com a combinação de teclas Ctrl + F3.
Lidando com mensagens de erro emergentes
Trabalhar com tabelas vinculadas do Excel pode causar problemas indesejados. Isso inclui, em particular, a saída de valores errados. Caso seja gerado o valor 0 incorreto, há um pequeno problema nas configurações do Excel, que pode ser corrigido rapidamente.
A mensagem de erro comum #NV, por outro lado, é uma função deliberada de VLOOKUP, que indica ao usuário que o valor exigido não está disponível. Esta nota pode ser desenhada de maneira diferente com a ajuda de uma fórmula.
VLOOKUP - uma visão geral
VLOOKUP é uma função Excel útil que pode ser usada para pesquisar e avaliar tabelas. Suas vantagens são evidentes em sua aplicação fácil e flexível. Dessa forma, qualquer pessoa que trabalhe regularmente com tabelas do Excel pode se beneficiar com a função. Seja o colecionador particular que cria suas próprias pequenas tabelas ou a grande empresa que processa conjuntos de dados significativamente mais substanciais.
Se, por outro lado, você ainda tiver solicitações não respondidas que VLOOKUP não conseguiu atender, você pode esperar uma opção adicional do Excel: a Microsoft oferece aos usuários do Excel 365 o novo XLOOKUP desde o início de 2022-2023. Isso se baseia nas competências de VLOOKUP e as complementa com funções adicionais, às vezes até mais simples. Portanto, uma nova rotina de avaliação de dados também se abre neste ponto.
