Comparar dados com o PROCV
A função Procv é uma das funções mais conhecidas do Excel que tem por finalidade realizar uma busca vertical de dados.
Uma situação bastante comum é a necessidade de comparar dados entre duas listas, se forem pequenas, até uns 2000 registros pode fazer com o PROCV, senão pode usar a tabela dinâmica que será muito mais eficiente: Conciliar dados no Excel.
Veja por exemplo as nossas listas acima.
Temos a lista de notas fiscais A e B, desejamos identificar as notas fiscais que não constam na lista B e também a diferença de valores se houver cada item.
Para isso foram inclusas duas colunas. A coluna Valor B que é referente ao valor da lista B quando localizado e a coluna Diferença que irá apresentar o valor da diferença ou se não foi localizada a nfe.
Para a fórmula da diferença usamos a função =SEERRO(PROCV(B9;$I:$J;2;0);0), onde temos o PROCV Exato e também a função SEERRO.
A função SEERRO (=SEERRO(valor; valor_se_erro) retorna um valor se encontrar um erro. No nosso caso passamos como parâmetro o valor 0.
Caso o PROCV não encontre o valor #N/D, que é o aviso do Excel de que o valor não está disponível, ou seja, não foi encontrado.
Então para evitar que seja retornado um erro, usamos a função SEERRO na fórmula e trazemos o valor 0.
Por último colocamos na coluna Diferença a fórmula: =SE(D9=0;”Não encontrado”;C9-D9).
Esta fórmula verifica se o valor na célula ao lado D9 é igual a 0 e se for retorna Não encontrado, senão retorna o cálculo C9-D9, o valor da diferença entre o valor da nota fiscal na lista A e na lista B.
PROCV 1000x mais rápido
Esta parte é uma referência ao artigo http://ambienteoffice.com.br/blog/melhorar-o-desempenho-do-procv/, então pode ver mais detalhes neste artigo. Aqui explicarei mais brevemente.
Quando utilizamos o PROCV para uma busca exata esta é feita um a um, considerando que os valores não estão classificados e que é necessário consultar cada item da lista até o encontrar.
Então, considerando uma lista com 5000 itens por exemplo, a função poderá fazer até 5000 buscas até encontrar. E isso é desastroso quando temos muitos itens para busca.
Por isso uma das formas de resolver este problema é utilizando a fórmula que irei explicar em seguida. Ela tem duas partes.
=SE(PROCV(D8;$B$8:$B$1048576;1;1)=D8;PROCV(D8;$B$8:$B$1048576;1;1))
A fórmula funciona da seguinte forma:
A função PROCV com o parâmetro de busca APROXIMADA é extremamente mais rápido, como explicado no artigo do site ambiente office.
ATENÇÃO: É OBRIGATÓRIO QUE OS DADOS DA CHAVE ESTEJAM CLASSIFICADOS DO MENOR PAR AO MAIOR
Por isso nós usamos a seguinte lógica:
Abrimos um SE e:
- Se o valor da chave for localizado, for igual ao valor da chave: PROCV(D8;$B$8:$B$1048576;1;1)=D8
- Então retornar a coluna que desejamos: PROCV(D8;$B$8:$B$1048576;1;1)
Veja que na nossa fórmula usamos duas vezes a função PROCV, isso deveria fazer com que a fórmula ficasse mais lenta, mas mesmo usando duas vezes a fórmula PROCV com o parâmetro aproximado é extremamente mais rápido comparar com este parâmetro do que com o Exato.
Na planilha para download neste artigo há um comparativo. Apague as fórmulas abaixo da linha 8 nas colunas BUSCA e BUSCA 1000, e depois copie e cole a fórmula na primeira coluna e veja o tempo, e depois compare com a segunda lista.
PROCV VÁRIOS MAIS DE UM RESULTADO
A função PROCV é muito útil para relacionarmos dados de planilhas, porém ele possui uma limitação, só traz a primeira correspondência que ele localiza na lista. Por isso a chave de busca deve ser sempre única. De que forma?
Siga o tutorial:
Primeiro você tem uma lista de valores de NFs com os seus respectivos produtos, e você quer saber quais são todos os produtos de cada nota fiscal. Note que as notas fiscais possuem mais do que um produto, ocorrendo o problema que queremos resolver.
Como falamos no início, o primeiro passo é criarmos uma chave única, para isso você pode usar a função =CONT.SE($C$1:C2;C2) que irá realizar a contagem de quantas vezes a Nota fiscal se repete do início da tabela até o a linha atual, criando desta forma um número sequencial.
Feito isso criamos na coluna A a chave concatenando estes dois campos e formando a chave. Para isso utiliza-se a fórmula: =C2&”-“&B2.
Agora vamos criar uma lista onde temos os dados que queremos procurar, para isso copie a coluna C e cole em G3 por exemplo.
Em seguida selecione os dados da coluna G e clique na guia Dados->Remover Duplicatas, assim teremos uma lista com as notas fiscais únicas da planilha.
Na célula G2 digite NF e nas células H1 á L1 digite 1,2,3,4,5, sendo que 5 é o nosso limite de procura, imaginando que teríamos apenas 5 produtos no máximo por nota fiscal por exemplo, você terá a planilha conforme abaixo:
Na célula H3 digite a fórmula: =SEERRO(PROCV($G3&”-“&H$2;$A:$D;4;0);””) e arraste para todas as células da planilha, esta fórmula faz uma busca na coluna A que possui a chave dos dados pela chave que é concatenada ($G3&”-“&H$2) exemplo: 26327-1, 26327-1, etc.
Abraço
Marcos Rieper
Curso Excel Completo – Do Básico ao VBA
Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:
