Como comparar listas de nomes no excell

Oi pessoal. Tudo Excelente com vocês?

Índice Show

A solução
PROCV 1000x mais rápido
PROCV VÁRIOS MAIS DE UM RESULTADO
Curso Excel Completo – Do Básico ao VBA

Como o PROCV é requisitado na construção de nossas fórmulas, não é mesmo?

Podemos solucionar muitas de nossas tarefas mais árduas, fazendo o uso dessa função de Pesquisa e Referência do Excel em nossas tabelas. É claro que eu não poderia deixar de falar sobre ela aqui, afinal esse é um site de Excel ou não? (risos)

Para ser mais exato, já abordei esse tema aqui outras vezes, como no artigo Aprenda a usar PROCV para pesquisar na planilha, que é bem detalhado e faz parte do conteúdo do meu ebook Aprenda as 7 Fórmulas do Excel Que Podem Salvar Sua Vida.

Na dica de hoje, no entanto, vou abordar uma usabilidade específica para a capacidade da função PROCV.

Recebi uma questão outro dia, onde o leitor gostaria de comparar duas listas de produtos, sendo uma de sua loja e a outra, do seu estoque. Sua finalidade seria verificar os produtos zerados para que fosse realizada a reposição daqueles itens.

Poderia ser feito com macro para agilizar e não pesar a atualização de dados? Poderia sim! Embora muitos usuários ainda não tenham domínio do VBA, seria interessante por questões de performance. Mas, com o PROCV podemos resolver bem rapidamente e com um grau menor de dificuldade.

Por isso, hoje vou mostrar como solucionar essa questão usando o PROCV, certo!

O problema

Vamos simular um cenário:

Planilha Lista Loja – Contém a lista de todos os produtos da Loja. Possui as colunas Código do Produto, Descrição e Quantidade.

Planilha Lista Estoque – Contém a lista de todos os produtos do Estoque. Possui as colunas Código do Produto, Descrição e Quantidade.

Planilha Lista Geral – Contém a lista de todos os produtos (Lista Loja e Lista Estoque). Nela será feita a comparação das duas listas anteriores. Possui as colunas Código do Produto, Descrição e Quantidade da Loja e Quantidade do Estoque.

Com base nessas três planilhas poderemos desenvolver a solução.

A solução

Como já expliquei como funciona o PROCV e como usá-lo, vamos direto ao ponto. Na planilha Lista Geral, digite, na célula C2, a fórmula a seguir:

=PROCV($A2;’Lista Loja’!$A:$C;3;0)

Agora, copie a fórmula para as linhas seguintes. Essa fórmula busca as quantidade de cada produto na planilha da loja.

Faça o mesmo para a coluna D, na célula D2, digite:

=PROCV($A2;’Lista Estoque’!$A:$C;3;0)

Copie a fórmula para as linhas seguintes, exibindo as quantidades dos produtos no estoque.

Dessa forma, você pode manipular essas informações da melhor maneira para consolidar os seus dados. Pode ser criando em outra célula um fórmula condicional de acordo com os valores de cada coluna quantidade. Ou, aplicando um auto-filtro para os valores que forem 0 (zero). Enfim, vai depender do que você precisar.

Então é isso pessoal. Espero que gostem! E se gostarem, por favor, compartilhem com seus amigos pelo Facebook, Twitter, ok!

Até mais!

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