Conciliação de Dados Utilizando o Excel

LOCALIZANDO DIFERENÇAS ENTRE LISTAS DE DADOS

Para identificar diferenças entre listas de dados, eu trabalho de duas formas básicas, sem envolver VBA. Uma utilizando a fórmula PROCV e outra utilizando tabelas dinâmicas.

Ambas as formas de conciliação funcionam, porém, quando utilizamos a fórmula PROCV para análise de listas que possuem muitos dados, como por exemplo mais de 100.000 linhas o processador irá fazer um trabalho que pode ser muito pesado podendo demorar muito tempo e até travar o computador.

Por esse motivo recomendo a forma de análise de diferenças que será demonstrada agora.

  • O primeiro passo é identificar os dados que poderão ser utilizados como chave, ou seja, que serão coincidentes, caso existam, nas listas. Estas informações poderiam ser o código do cliente, do pedido, da nota fiscal, etc.
  • Em listas de notas fiscais estas informações poderiam ser a série, a nota e o emissor, no nosso caso iremos utilizar apenas a série e a nota fiscal.
Criação da Chave Utilizando Concatenação
Criação da Chave Utilizando Concatenação
  • No nosso caso como disse a chave é formada pela série e o número, para isso concatene estas informações separando com um traço usando a fórmula: =A2&”-“&B2.
  • Arraste a fórmula para toda a lista.
  • Repita esta operação também para a outra lista de dados.

Analisando os dados

  • Com as chaves prontos devemos agora buscar as diferenças, que serão as notas não encontradas em uma lista ou outra, e as diferenças de valor.
  • Abra uma nova planilha e clique em Inserir->Tabela Dinâmica, selecione os dados das colunas de A á D da Lista 1 e clique em OK.
  • Arraste o campo Chave para a área Rótulo de Linha.
  • Arraste o campo Valor para a área Valores, este campo será o sempre o campo aonde você deseja encontrar a diferença.
  • Na tabela dinâmica criada clique sobre um valor da coluna Contagem de Valor com o botão direito, clique no campo Configurações de Campo de Valor e altere para Soma.
  • Na coluna C digite no título Lista e abaixo digite 1 e arraste até a última linha como na figura.
Lista de dados
Lista de dados
  • Repita as operações acima para criar a Lista 2, conforme a figura.
Lista 2 de dados
Lista 2 de dados – Clique para ampliar

Criando Uma Lista Única e Analisando os Dados

  • Crie uma nova planilha
  • Selecione os dados das listas de dados criadas para análise e copie e cole especial Somente Valor nesta nova planilha, desta forma os dados não serão mais tabelas dinâmicas.
  • Agora coloque os dados das listas 1 e 2 uma abaixo da outra, assim você terá uma lista única com todos os dados para a análise.
  • Clique em uma nova célula e clique em Inserir->Tabela Dinâmica, selecione os dados da última lista criada.
Base para análise dos dados
Base para análise dos dados
  • Agora arraste os campos Rótulo de Linha para a área Rótulo de Linha, o campo Soma de Valor para a área Valores e o campo Lista para a área Rótulo de Colunas, conforme a figura.
Campos
Campos
  • Desta forma você terá como resultado a lista de dados da figura abaixo.
Tabela para análise
Tabela para análise
  • Copie a tabela criada para uma nova planilha colando somente valores.
  • Exclua as colunas D e E e a linha 1.
Nova Lista
Nova Lista – Clique para Ampliar
  • Agora digite na célula D2 a palavra Diferença.
  • Na célula D3 digite =B3-C3 e arraste até a última linha. O resultado desta conta deverá dar sempre 0, caso seja diferente, você localizou a diferença de valor ou a nota fiscal que falta e em qual lista.
  • Selecione os dados das colunas e organize pelo campo Diferença, os extremos mostrarão as notas faltantes e as diferenças de valores conforme a figura:
Diferenças Encontradas
Diferenças Encontradas

Está pronta a análise dos dados com as diferenças encontradas e as notas que não constam nas listas.

Então é isso, agradeço todas as colaborações e visitas ao blog e aguardo sugestões.

Rieper

Baixe a planilha

Marcos Rieper

Pai, marido, professor e consultor em Excel.

Obrigado por ler este artigo, este blog foi criado para difundir o conhecimento em Excel à todos.

Divulgamos novos artigos nas redes sociais, basta clicar nos ícones abaixo.

Excel não precisa ser complicado

Assine nossa newsletter e receba dicas práticas para dominar o excel