Conciliação de Dados com Excel - Vídeo - Guia do Excel
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
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.
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.
Lista 2 de dados – Clique para ampliar
Criando Uma Lista Única e Analisando os Dados
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
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.
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
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.
Categorias AvançadoTags Avançado, Excel 2007, Excel 2010, excel avançado, Exemplo Macro, Exemplo Planilha, Exercícios, Filtro, Filtro Avançado, filtro de dados Excel, Gravar Macro, Índice Corresp, Intermediário, macete, Macros, Nível, Planilha, Planilha Macro, Planilha Profissional, planilhas, planilhas prontas, programação, rapidez, somar cores, Texto, truques, Tutoriais, VBA | EDIT, VBA AVANÇADO, VBA AVANÇADO DOWNLOAD, VBA DOWNLOADS