Sempre que vejo gestores usando planilhas de Excel, fica claro pra mim a necessidade de uso do PROCV. Para quem não sabe o que essa sigla é ou não tem muita intimidade com o Excel, ela é uma das fórmulas de busca de informações que o Excel proporciona para sues usuários e é muito pouco usada por parecer “complicada”.
Ao terminar de ler esse post você vai aprender como usar a função PROCV (VLOOKUP em inglês) no Excel para qualquer situação empresarial.
Se você está meio perdido, fique tranquilo, eu vou mostrar alguns exemplos do uso do PROCV, apresentar a função na íntegra com um passo a passo bem detalhado e no final desse post, permitirei que você baixe uma planilha de exercícios com gabarito.
Vamos começar vendo alguns exemplos:
1. Exemplo de uso do PROCV em planilha de Controle de Funcionários
Imagine que você tem uma planilha com um cadastro de todas as informações dos seus funcionários. Basicamente, ela teria uma visualização similar à nossa planilha de Cadastro e Controle de Funcionários abaixo:
Perceba, que todos os funcionários tem uma série de informações cadastradas como número de matrícula na sua empresa, sexo, data de nascimento, idade, mês de aniversário, etc. Agora imagine que você tem uma lista com 100 funcionários e toda vez que você quiser buscar os dados de um deles terá um trabalho monstruoso, tanto para achar os dados, como para utilizá-los.
É por essas e outras que o Excel criou a função PROCV (ou VLOOKUP no inglês). Com ela, você poderá fazer buscas automáticas por informações na sua planilha. Nesse caso, queremos fazer buscas para o nome dos funcionários. Vamos ver como funciona:
Na nossa planilha criamos uma nova aba (3. Consulta de Funcionários) onde você pode escolher o nome do funcionário em uma lista na célula D7 como indicado na imagem acima. A partir dessa escolha, com o uso da fórmula PROCV, automaticamente o excel faz buscas na aba de cadastro dos funcionários e leva todas as informações correspondentes para as células com as fórmulas de busca. No nosso caso, escolhemos a funcionária Amanda Santos. Vamos ver como ficou o resultado:
Então, neste primeiro exemplo, para conseguir a data de nascimento, a idade e o CPF da nossa funcionária, utilizamos a construção das fórmulas dessa maneira:
- Para a data de nascimento:
=PROCV($D$7;’2. Cadastro de funcionários’!$AJ$7:$BM$500;4;0)
- Para a idade:
=PROCV($D$7;’2. Cadastro de funcionários’!$AJ$7:$BM$500;5;0)
- Para o CPF:
=PROCV($D$7;’2. Cadastro de funcionários’!$AJ$7:$BM$500;8;0)
Perceba que a 3ª parte de cada fórmula (itens em azul) possui numerações diferentes para cada item procurado. Isso ocorre porque na nossa aba de cadastro de funcionários, cada uma dessas informações foi colocado em uma coluna diferente, e esta parte da fórmula corresponde às posições de tais colunas.
Você também deve ter percebido que o nome da nossa funcionária selecionada (Amanda Santos) foi informado na célula D7 (em vermelho), e por isso, quando o nome dos funcionários é trocado naquela célula, a fórmula é recalculada automaticamente com base no novo nome adicionado.
Como deu para ver, a função PROCV é bem simples de ser usada e muito funcional. Você deverá usar ela sempre que quiser fazer buscas verticais (em colunas). Ela tem uma variação, a função PROCH, que faz pesquisas pela horizontal (em linhas). Nesse post vamos focar apenas noa função PROCV. Para você decidir qual das duas funções utilizar, observe na sua planilha como estão distribuídos os valores que poderão ser o resultado da busca: se os valores esiverem em uma coluna utilize a função PROCV, e se os valores estiverem em uma linha utilize a função PROCH.
Vamos mostrar mais um exemplo de uso do PROCV.
2. Exemplo de uso de PROCV em planilha de Controle de Estoques
Agora vamos ver o exemplo de uma planilha de controle de estoques. Aqui, você encontrará todas as informações de mercadorias que possui armazenadas em sua empresa. Essa é a visualização que você teria dos seus produtos cadastrados em estoque:
Veja que todos os produtos, assim como na planilha de Cadastro de Funcionários, possuem uma série de dados e informações relativos a eles como estoque mínimo, estoque atual, status, etc. Se você quiser ver informações sobre um determinado produto, basta a função PROCV (ou VLOOKUP no inglês). Nesse caso, queremos fazer buscas para o nome das mercadorias/produtos cadastrados. Vamos ver como funciona:
Na nossa planilha de Controle de Estoques criamos uma nova aba onde você pode escolher o nome do produto em uma lista na célula C7 como indicado na imagem acima.
A partir dessa escolha do produto, após utilizar a fórmula PROCV, o excel fará uma procura na aba de Inventário, levando as informações correspondentes ao produto para as células que criamos. Nesse caso, escolhemos o produto apontador. Vamos ver o resultado:
Neste segundo exemplo, para conseguir o estoque mínimo e a quantidade em estoque do produto apontador, fizemos as fórmulas dessa maneira:
- Para o estoque mínimo:
=PROCV(C7;’2. Inventário’!$C$8:$D$508;2;FALSO)
- Para a quantidade em estoque:
=PROCV(C7;’2. Inventário’!$C$8:$E$508;3;FALSO)
Assim como no nosso primeiro exemplo, a 3ª parte da fórmula PROCV (itens em azul) possui numerações diferentes, já que na aba de Inventário, essas informações foram colocadas em colunas diferentes. Além disso, a 1ª parte da fórmula PROCV (itens em vermelho) é sempre igual, já que é a célula relativa ao valor (nome, produto, etc.) que será procurado.
Outras Aplicações
Você ainda poderia usar a fórmula PROCV do excel para fazer buscas e procuras em praticamente qualquer banco de dados que você possua. Por exemplo, é possível obter mais informações de registros de pedidos de vendas, de tabelas de clientes, para controle de caixa, etc. Abaixo você verá um exemplo de diversas informações financeiras que são buscadas automaticamente com a fórmula PROCV na nossa planilha de plano de marketing:
De acordo com a mídia escolhida, os valores de orçamento disponível, custo, receitas e resultado são alterados automaticamente de acordo com os valores já adicionados em outras abas da planilha de Plano de Marketing.
3. Entendendo a função PROCV conceitualmente no Excel:
O que vem escrito quando você inserir a função PROCV (procura vertical) é PROCV(valor_procurado;matriz_tabela;núm_índice_coluna;intervalo)
Onde:
valor_procurado: é o critério utilizado na procura, ou seja, é o valor a ser localizado na primeira coluna da matriz da tabela. Nos nossos exemplos, eram os itens de referência funcionários (exemplo 1) e produtos (exemplo 2).
matriz_tabela: é todo o intervalo de dados, compreendendo linhas e colunas. Sempre será constituída por duas ou mais colunas de dados. Os valores na primeira coluna de matriz_tabela são aqueles usados para o valor_procurado. Esses valores podem ser texto, números ou valores lógicos. Nos nossos exemplos, a matriz tabela eram os bancos de dados onde colocamos as informações sobre os funcionários (exemplo 1) e sobre os produtos (exemplo 2)
núm_índice_coluna: é a coluna que contém o valor a ser retornado. A primeira coluna tem valor 1, a segunda 2, a terceira 3, e assim suscessivamente. No nosso exemplo da planilha de Funcionários, quando queríamos usar a fórmula PROCV para buscar a data de nascimento que estava na quarta coluna da matriz tabela que utilizamos, colocamos como núm_índice_coluna o valor 4 (que no nosso exemplo estava em azul). Se esse valor for:
- Menor do que 1, PROCV retornará o valor de erro #VALOR!.
- Maior que o número de colunas em matriz_tabela, PROCV fornecerá o valor de erro #REF!.
intervalo: define a precisão da procura. É um valor lógico que especifica se você deseja que a fórmula PROCV localize uma correspondência exata ou aproximada. Se for VERDADEIRO ou 1 uma correspondência aproximada será retornada. Se uma correspondência exata não for encontrada, o valor maior mais próximo que é menor que o valor_procurado será retornado. Se for FALSO ou 0 (zero), PROCV somente localizará uma correspondência exata. Nesse caso, os valores na primeira coluna de matriz_tabela não precisam ser classificados. Se houver dois ou mais valores na primeira coluna de matriz_tabela que corresponderem ao valor_procurado, o primeiro valor encontrado será usado. Se uma correspondência exata não for encontrada, o valor de erro #N/D será retornado.
Nota: o valor a ser procurado sempre deve estar na primeira coluna, caso contrário a função PROCV não irá funcionar.
4. Agora vamos ver o passo a passo na prática de como usar PROCV
Para fazer o passo a passo na prática de como montar o seu PROCV eu vou utilizar a Planilha de Cadastro e Controle de Funcionários da LUZ, mas pense nela apenas como a estrutura geral para aplicar na sua planilha excel.
1. Estrutura
Você vai precisar ter uma estrutura de tabela, com pelo menos 2 colunas. A primeira coluna sempre será a sua referência e a partir da segunda, você terá os valores que poderão ser procurados. Para ficar menos abstrato, vamos ver a tabela da nossa planilha. Para ficar mais fácil, simplificamos para uma tabela com 5 linhas (cabeçalho não conta) e 6 colunas (nome, data de nascimento, email, CPF, RG Órgão Expedidor):
Qualquer tabela que você tenha com informações em linhas e colunas pode ser utilizada para a função PROCV. Só lembre de ter o seu valor de referência como a primeira coluna. Nesse caso que apresentamos aqui, a primeira coluna é a do nome do funcionário.
2. Inserindo a fórmula
A função PROCV busca a ocorrência de um texto em uma determinada coluna e retorna o valor de outra coluna (que você especificará na fórmula) dessa mesma linha. No nosso caso, vamos criar uma estrutura de formulário em outra aba para inserir a fórmula:
Veja que na nossa estrutura, o valor de referência sempre será o nome do funcionário (primeira coluna do nosso banco de dados). Agora que já temos a estrutura, podemos escolher o funcionário (nesse caso vamos colocar o Carlos Carvalho como exemplo) e depois inserir a fórmula. Para isso, você escolherá a célula correspondente ao valor que você quer. Vamos começar com o valor data de nascimento (célula D8).
Nessa célula vamos colocar a fórmula =PROCV($D$7;’2. Cadastro de funcionários’!$B$3:$G$7;2;0)
Nessa fórmula, podemos ver que:
– valor_procurado = $D$7 – que é exatamente o nome do Carlos Carvalho
– matriz_tabela = ‘2.Cadastro de funcionários’$B$3:$G$7 – que representa a tabela que mostramos na imagem acima. O nome da aba aparece nessa fórmula pois estamos usando ela em outra aba. Se estivéssemos fazendo a fórmula na mesma aba bastaria colocar B3:G7 aqui
– núm_índice_coluna – Colocamos o número 2, pois a data de nascimento é a segunda coluna no nosso banco de dados
– [procurar_intervalo] – Colocamos 0 (zero), mas também poderia ser o valor “falso”
Se preferir, use o assistente de fórmulas. Para isso, Clique na guia Fórmulas > Inserir função> PROCV. Lá, você terá esses mesmos 4 campos para preencher:
Ao preencher a fórmula corretamente e apertar a tecla enter, veja que automaticamente a data de nascimento aparecerá no valor correto:
3. A melhor maneira de utilizar
Se você continuar aplicando a fórmula do PROCV para as outras células (email, CPF, RG e Órgão Exp.) teremos a tabela completamente preenchida com todas as informações. Não se contente em ter apenas um resultado, você pode pegar informações completas para uma determinada informação em uma única aba ou quadro conforme exposto abaixo:
Comentários Extras
- Preste atenção aos valores na primeira coluna de matriz_tabela. Sempre verifique possíveis erros de espaçamento à esquerda, uso inconsistente de aspas, data ou números como texto ou caracteres não imprimíveis. Nesses casos, a função PROCV pode fornecer um valor incorreto ou inesperado.
- Para facilitar a vida de quem usa qualquer fórmula do excel, é possível usar o caractere $ para travar a fórmula e depois usar a função de arrastar (ou copiar e colar) células para não ter o retrabalho de criar várias vezes a fórmula de PROCV. Falarei mais sobre como fazer isso em posts futuros
Deixe a sua opinião e sugestões
Fale pra mim o que você mais quer saber sobre o excel. Escreva abaixo nos comentários o que você achou desse post. Gostou dos exemplos? Conseguiu fazer a fórmula PROCV na sua planilha? Sobre quais outras fórmulas e funções você quer aprender mais?
Teste seus conhecimentos
Baixe agora mesmo uma planilha de exercícios sobre a função PROCV com gabarito.
Nós temos algumas planilhas já prontas que utilizam a fórmula PROCV:
1) Avaliação de Desempenho por Competência
2) Cadastro e Controle de Funcionários
Está com dúvidas? Entre no Fórum de Excel da LUZ e envie a sua pergunta!
