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.

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

• Repita as operações acima para criar a Lista 2, conforme a figura.

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.

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

• Desta forma você terá como resultado a lista de dados da figura abaixo.

• Copie a tabela criada para uma nova planilha colando somente valores.
• Exclua as colunas D e E e a linha 1.

• 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:

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.

Marcos Rieper