19 de maio de 2014

Cruzar dados com Excel: como usar a fórmula VLOOKUP (PROCV)

Quando preciso disso?

Se você já se fez alguma dessas perguntas, o PROCV é pra você:
  • Como cruzar dados usando Excel?
  • Como incrementar minha tabela com informações que estão em outra tabela?
  • Como transportar dados de uma tabela para outra usando um campo comum entre elas?
  • Como ir buscar dados em outra página (folha) da planilha?
  • Como cruzar dados no Google Sheets?
Cruzamento de dados em geral é utilizado por quem trabalha com linguagem SQL e bancos de dados. Mas pra quem isso é grego e precisa trabalhar com quantidades de dados menores, o Excel resolve bem com uma fórmula de busca vertical. 

Demora um pouquinho pra pegar o jeito, mas não desista: pode te salvar muito trabalho. Aqui vão dois vídeos de como usar a fórmula, e a explicação detalhada de como ela funciona:

A fórmula: VLOOKUP

Ou PROCV, no Excel em português e no Google Sheets. É a fórmula para busca vertical.

O que ela faz

Busca um determinado valor em um conjunto de células.
O valor em questão deve estar contido nos dois conjuntos (duas tabelas) para poder ser feito o cruzamento de dados. Esse valor comum exerce uma função parecida ao que chamam de identificador único (unique identifier) ou chave primária (primary key) quando se trata de banco de dados.
Se o valor comum for encontrado, a fórmula pode:
  • devolver o mesmo valor (indica que o valor está contido nas duas tabelas)
  • devolver outros valores (que estão em outras colunas) referentes ao valor pesquisado

Para que é útil

  • verificar listas de nomes, se nomes de uma lista estão contidos em outra.
  • incluir colunas a partir de dados em outra tabela

Como usar

👉 Mais vídeos: Dados Finos no Youtube


Sintaxe

VLOOKUP(lookup_value;table_array;col_index_num;range_lookup)

lookup_value:
valor a ser procurado, comum às duas tabelas.
  • Pode ser um valor, ou uma referência de célula.
  • Pode ser selecionado clicando direto na célula enquanto se edita a fórmula.
  • Ex.: A1

table_array:
conjunto de células (range/array) onde o valor comum vai ser pesquisado*
  • Podem ser várias células em uma mesma coluna, ou em várias colunas.
  • Podem estar na mesma planilha, em outra planilha do mesmo arquivo, ou em outro arquivo.
  • Ex.: A1:A30 ou A$1:A$30
  • Quando os dados estiverem em outra planilha/arquivo: ao editar o segundo campo da fórmula, é só mudar de planilha ou arquivo, selecionar as células e voltar para terminar de editar a fórmula. O campo da fórmula continua ativo para digitação mesmo mudando de janela.

col_index_num:
número da coluna que contém o valor desejado para a fórmula retornar.
O número é relativo ao conjunto de células selecionados no table_array.
  • Se você fez uma seleção envolvendo 3 colunas, e o que você quer é o valor que está na 2ª coluna das 3, coloque o número 2. 
  • Em outras palavras: Ao buscar por um nome em uma tabela de 'nomes' e 'idades', a fórmula pode retornar o nome, se o col_index_num for 1, ou pode retornar a idade correspondente, se o col_index_num for 2
  • Eu sempre coloco 1 para começar,  para testar se a fórmula dá certo. Depois de conferido, altero para o número da coluna desejada.

range_lookup:
opcional na fórmula.
define se a busca vai ser por valores exatos ou aproximados.
  • Colocar TRUE para uma busca aproximada, ou FALSE para busca exata.
  • (Se nada é especificado, acho que é realizada uma busca exata, e em seguida uma aproximada; se nenhum valor exato for encontrado, o valor retornado é o da próxima célula com valor maior ao buscado.)
  • Utilize FALSE para valores textuais (nomes, cidades, ...)

Cuidados especiais!

Travar o range (table_array)

Depois de feita a fórmula para a primeira célula, use o quadradinho no canto da célula para arrastá-la, replicando a fórmula para as outras linhas. 
  • Problema: Se o range de células (table_array) na fórmula não estiver travado (A1:A30), o Excel vai mudar a fórmula à medida que ela for sendo duplicada para outras células, como A2:A31, A3:A32, e assim por diante.
  • Coloque o cifrão antes do número das células para que a seleção permaneça a mesma para todas as fórmulas que você duplicar: A$1:A$30.
  • Quando os dados são selecionados em outro arquivo, o Excel já adiciona o cifrão ($) automático no range de células.

Eliminar a fórmula, manter só os valores

Importante para que você não precise mais da tabela original ou quando quiser mudar a posição das células na planilha sem perder os dados recém calculados:
  1. Selecionar os valores
  2. Copiar
  3. Colar Especial (Paste special). Opção disponível com o botão direito do mouse ou no menu Edit.
  4. Selecionar a opção "Valores" (Values)
  5. Agora os valores que você colou não tem mais a fórmula. (pode mover as células sem problemas ou apagar as originais)

Outro exemplo




Documentação oficial da fórmula VLOOKUP
http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx