Veja como comparar listas no Excel de 3 maneiras diferentes neste artigo.
A comparação ou conciliação de dados é um trabalho recorrente em todas as áreas das empresas.
É comum termos que identificar itens não encontrados entre as listas ou ainda com divergência de valores à partir da sua chave.
No vídeo abaixo demos uma aula de como realizar a comparação de listas no Excel e também neste artigo veja o download do exemplo da aula e a descrição com imagens e passo-a-passo de como realizar a comparação de tabelas ou listas.
3 Maneiras de Comparar Listas no Excel
Abaixo listamos 3 maneiras para conciliar dados no Excel:
- Fórmula: A primeira forma de fazer isto é com funções de busca e referência, como a função PROCV que é ensinada neste vídeo e artigo.
- Tabela Dinâmica: Uma tabela dinâmica permite que realizemos resumos de dados. E neste exemplo mostramos como estruturar e fazer este resumo de dados de forma a comparar as informações e encontrar as diferenças.
- Power Query: O Power Query é uma poderosa ferramenta que existe no Excel e permite que realizemos extração, tratamento e carregamento de dados para o Excel, isso tudo automatizando o processo.
Listamos três formas, porque nem sempre você irá querer fazer da forma mais automatizada, pois irá fazer apenas uma vez ou poucas vezes.
Então entendemos que para cada caso há uma forma diferente de você comparar listas.
No nosso exemplo temos a necessidade de comparar duas listas de origens diferentes, FINANCEIRA e CONTÁBIL.
Contábil:
O objetivo é encontrar as notas fiscais que não estão em uma lista e estão na outra ou que tem diferença de valores para que possamos corrigir na fonte.
Mostraremos abaixo como comparar estas listas no Excel destas 3 maneiras diferentes.
Conciliação no Excel com Fórmulas – PROCV
O Procv é uma função de busca e referência que retorna dados à partir de uma busca vertical de informações pela chave, retornando uma determinada coluna.
No exemplo usamos o PROCV, mas poderia ser feito com outras funções como com a união das funções Índice e Corresp ou mesmo a PROCX.
Para isso no nosso exemplo incluímos uma coluna em Financeiro e colocamos a seguinte função:
=SE(ÉNÚM(PROCV([@[Nota Fiscal]];Tabela136[[Nota Fiscal]:[Valor]];2;0));[@Valor]-PROCV([@[Nota Fiscal]];Tabela136[[Nota Fiscal]:[Valor]];2;0);”Sem Contábil”)
Nesta função estamos realizando a consulta pelo PROCV à partir do número da nota fiscal na tabela contábil, e se encontrar o valor, que é visto pelo uso da função ÉNÚM, ela calcula o valor da nota no financeiro, menos o valor da nota no contábil, senão ela retorna SEM CONTÁBIL, pois não a teria encontrado.
Esta função é interessante para questões rápidas, aonde sabemos que uma lista contém todas as informações e a outra pode ter alguma falta.
No entanto se tiver itens diferentes entre as duas, as duas podendo ter itens a mais que a outra, este não é o mais indicado, pois teria que aplicar a mesma fórmula na Contábil e encontrar os itens SEM FINANCEIRO.
Comparação de Tabelas com Tabela Dinâmica no Excel
A tabela dinâmica é um recurso muito utilizado no Excel, com ela conseguimos fazer resumos dos dados de uma forma muito prática, bastando arrastar os campos.
Para fazer a conciliação de dados, comparação de listas no Excel com a tabela dinâmica, nós precisamos estruturar estes dados.
Para isso criamos a tabela com esta estrutura: Nota Fiscal, Valor e Origem.
Colando assim os dados da tabela Financeira e a Contábil todas, juntas na mesma lista ou tabela, diferenciando apenas por uma nova coluna que chamamos de Origem, e definimos ali a origem dos dados.
Após isso clique em Inserir->Tabela dinâmica e selecione esta lista ou tabela, selecionando à partir do cabeçalho até a última linha.
Uma vez feito isto arraste os campos conforme demostrado abaixo:
Com isso temos as informações tabuladas e comparadas automaticamente.
Tire então o total e incluímos a seguinte fórmula:
=SE(G11=””;”Sem Contábil”;SE(H11=””;”Sem Financeiro”;H11-G11))
A fórmula verifique se há dados no financeiro ou contábil, e se não existir retorna Sem Financeiro ou Sem Contábil, e se existir nos dois, retorna a diferença entre Financeiro e Contábil.
E o resultado é o seguinte:
Temos então uma comparação completa dos dados, identificando as diferenças de valores e também os faltantes.
Comparar Listas no Excel com Power Query
Outra forma de realizar a comparação de listas no Excel é usar o Power Query.
Esta é a opção mais automatizada e também que te apresentará mais possibilidades.
No entanto, não é a mais prática, pois demanda mais tempo para criá-la na primeira vez e demanda que as informações estejam sempre da mesma forma em ambas as fontes.
Para isso clique sobre a tabela e depois na guia Dados selecione em Obter e Transformar Dados, De Tabela/Intervalo.
Os dados serão automaticamente carregados para o Editor do Power Query.
Clique em Fechar e Carregar Para e selecione Apenas Criar Conexão.
Faça o mesmo procedimento para carregar a Tabela Contábil.
Você terá ambas conforme acima.
O nosso objetivo agora é comparar as chaves das duas à partir da nota fiscal.
Para isso clique na tabela Financeiro e em Página Inicial na opção Mesclar Consultas.
Selecione conforme acima a coluna Nota fiscal em ambas e em tipo de junção selecione Externa esquerda.
Isso fará com que os dados sejam mesclados, todos com todos, ou seja, havendo ou não havendo dados nas duas.
Depois disto abra a tabela e clique na seta para abrir os campos todos da tabela integrada.
Clique em Adicionar Coluna Condicional e inclua conforme abaixo:
Se Nota fiscal igual a null, então retorna Sem Financeiro se tContabil.Nota Fiscal é igual a null, Sem Contábil, e senão retornar o valor.
Após isso nós alteramos em Editor Avançado e no else colocamos [Valor]-[tContabil.Valor], substituindo assim o campo [Valor].
Após isso nós incluímos uma outra coluna aonde iremos colocar o número da nota fiscal, se a nota fiscal do Financeiro estiver nula retorna Nota Fiscal do contábil, senão só a do Financeiro, uma coluna cheia.
Após isso clique na coluna criada Financeiro-Contábil e desmarcamos o 0, pois assim, retiramos os campos que não tem diferença.
Com isso nós temos então os campos de NFe, Valor contábil e Valor financeiro e Financeiro – Contábil, selecione elas e clique em Remover outras colunas.
Após isso nós temos o retorno abaixo.
Clique então em Fechar e Carregar para Tabela.
Segue abaixo a tabela com os dados com a diferença entre contábil e financeiro:
E com isso nós temos as listas comparadas no Excel.
Esta fonte é à partir de tabelas, mas poderia ser feito à partir de outras fontes de dados como bancos de dados, arquivos txt entre outros.
E com isso temos as diferenças. Você pode atualizar os dados clicando com o botão direito em Atualizar Dados e temos os retornos.
Download planilha comparar listas no Excel
Clique no botão abaixo para realizar o download da planilha de TextoDivisão Excel, com exemplo de dados:
