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

Função para extrair números de células
27 de setembro de 2010
Planilha que fala
Planilha que fala
2 de outubro de 2010

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.

 

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

5 Comentários

  1. […] Veja como fazer a conciliação de uma forma extremamente rápida e eficiente neste artigo aonde você pode tanto ver o vídeo, quanto ir passo-a-passo: http://guiadoexcel.com.br/conciliacao-de-dados-com-excel-video. […]

  2. José Tavares disse:

    Boa noite: Parabéns pela explicação. Peço desculpa pelo atrevimento, mas gostaria de pedir a sua ajuda para concluir um ficheiro de que preciso muito, para conciliar os meus lançamentos contabilísticos com os lançamentos efectuados pelo banco. Trabalho num gabinete de contabilidade e tenho um ficheiro no Excel 2003 que preciso me diga quais os valores lançados na coluna H, que é o extracto de conta enviado pelo banco, que têm correspondência com os valores lançados por mim na minha conta corrente, que estão na coluna C e (ou) E.
    Por exemplo: o valor 1.678,95, situado na célula H10, está lançado na c/c do banco, mas não está lançado na minha c/c, por isso não tem correspondência, está correcto;
    Outro exemplo: o valor 7.380,00, situado na célula H11, tem correspondência na célula C24, daí ambos terem o número 1 nas células G11 e B24, respectivamente, está correcto;
    Já o valor 0,34 (H12), com o número corresponde 2, deveria ter esse mesmo número na célula D10, já que se correspondem e como está no ficheiro, está errado;
    Agora vou pedir para acrescentar o valor 0,34 na célula O35 e novamente na célula O36: verifica-se que na coluna G aparecem 3 vezes o correspondente 2, eu pretendia que só uma das vezes o valor 0.34 fosse assinalado na Coluna G, já que as outras vezes que ele aparece na coluna H, não tem correspondência em qualquer outra coluna.
    Como não sei fazer isso, peço ajuda
    Segue o link para o ficheiro: http://www.sendspace.com/file/kelqdp
    Desde já agradeço a ajuda que me possa dar
    Cumprimentos
    José Tavares
    jomamata@sapo.pt

  3. […] de dados de forma manual, esta planilha faz a mesma tarefa, mas de forma automática. O artigo http://guiadoexcel.com.br/conciliacao-de-dados-com-excel-video explica como fazer a conciliação e como esta funciona, sugiro que veja também este artigo para […]

  4. Wander Vinhas disse:

    Olá. Anos quebrando a cabeça e tentando fazer uma planilha automática de conciliação automática, tentando possibilidades de uma somatória entre duas ou mais células da lista 2, serem referenciadas em soma de uma ou mais células da lista 1. Pode me ajudar?

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.