Conciliação de Dados com Excel – Vídeo

Objetivo: demonstrar a conciliação de dados entre duas listas de dados no Excel.

Para facilitar seu aprendizado também repeti o artigo http://guiadoexcel.com.br/conciliacao-de-dados-utilizando-o-excel.

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 lsitas 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.

Baixe a planilha

 

Marcos Rieper