Conciliação de dados no Excel – Manual e Automático

10-dicas-de-programacao-vba
10 Dicas de como programar em VBA Excel
10 de setembro de 2016
consulta-automatica-simples-nacional-mei-3
Planilha de consulta automática SIMPLES Nacional – MEI
25 de setembro de 2016

Conciliação de dados no Excel – Manual e Automático

Conciliar dados no Excel é normalmente um desafio. Encontrar as diferenças entre listas de valores pode demorar muito tempo, mas não precisa ser assim. Veja a seguir, em vídeo e no artigo, como realizar a conciliação de dados no Excel de forma manual e automática.

Denomina-se “conciliação contábil” a análise do sados das contas contábeis, e sua respectiva movimentação, visando adequar seu saldo à efetiva realidade, promovendo ajustes necessários na escrituração contábil.Portal de contabilidade.

O método de conciliação de dados no Excel que iremos ensinar neste artigo consiste em comparar duas listas de valores e indicar os itens que constam em uma e não na outra e vice-versa e também diferenças de valores entre as duas.

Para que seja possível a conciliação é necessário que a lista contenha pelo menos um campo chave, ou seja, um campo único que identifique o registro e que tenha ligação com a outra lista. Por exemplo o número da nota fiscal e a série, código do cliente, código do fornecedor, ID no banco de dados, etc.

Veja agora passo-a-passo como realizar a conciliação contábil de forma Manual, e em seguida, após entender o conceito, você pode entender nossa planilha de conciliação Automática.

Conciliação no Excel de forma manual

  1. Separe os dados das suas listas e identifique os campos chave e o campo de valor que será comparado entre as duas.conciliar-dados-excel-2-listas
  2. No caso da conciliação de dados do nosso exemplo, estamos trabalhando com duas listas de notas fiscais e o valor total da nota. É necessário criar uma chave única que identifique cada uma, dado que um mesmo número de nota pode ter outra série. Conseguimos isso concatenando os valores utilizando a fórmula: =A2&”-“&B2. Crie um cabeçalho acima desta função e chame-a de Chave, faça o mesmo com a lista 2.conciliar-dados-no-excel-2
  3. Em uma nova aba, copie os dados das colunas Chave e Valor e cole como somente valor nesta nova planilha, não esqueça de copiar o cabeçalho.
  4. Nesta nova tabela criada crie um novo campo ao lado da coluna Chave e chame-a de Origem. Esta coluna serve para identificar a qual lista de dados pertence aquele registro.
  5. Digite e copie para todos os registros desta lista “Lista 1“.conciliar-dados-no-excel-3
  6. Agora que já temos uma lista inicial, vá até a última linha desta lista, copie e cole os dados correspondentes á Chave e ao valor nas colunas desta planilha e digite Lista 2 na coluna Origem.conciliar-dados-no-excel-4
  7. Agora vamos criar o relatório de conciliação. Selecione a célula E1 da planilha da lista conjunta.
  8. Clique em Inserir->Tabela dinâmica e selecione as colunas A á C em selecionar uma tabela ou intervalo, conforme abaixo e clique em OK.conciliar-dados-no-excel-5
  9. Na tabela dinâmica criada posicione os campos conforme a imagem.conciliar-dados-no-excel-criar-tabela-dinamica
  10. Ao lado da última coluna digite no cabeçalho Diferença e abaixo a fórmula =F3-G3 e arraste até a última linha.
  11. Depois clique no cabeçalho da tabela dinâmica, até o cabeçalho Diferença e clique na guia Dados e na opção Filtro.
  12. Filtre os dados pela coluna Diferença selecionando apenas os valores que tenham valores diferentes de 0.conciliar-dados-no-excel-campo-diferenca
  13. Pronto! Agora você pode notar que os registros que estão na Lista 1 e não na Lista 2 e também perceber as diferenças de valores entre os registros das duas listas de dados e a nossa conciliação de dados no Excel está concluída.

Conciliação no Excel de forma automática

O mesmo processo que realizamos de forma manual pode ser feito de forma automática. Como? Utilizando VBA.

No caso temos a seguinte planilha pronta, que foi criada a algum tempo em 2010 e totalmente gratuita para você utilizar. Basta fazer o download ao final do artigo.

Siga as etapas para utilizar a planilha de conciliação automática no Excel:

  1. Ao abrir a planilha marque a opção Habilitar Macros da tarja em amarelo que pode aparecer acima da planilha.
  2. Clique no botão Lista A e coloque os campos chave e valor, a origem é preenchida automaticamente pelo sistema.
  3. Repita a operação na lista B, colocando os dados da lista 2 que você deseja comparar.
  4. Clique no botão Conciliar Listas e veja que todo o trabalho que tivemos antes é criado automaticamente no Excel.conciliar-dados-no-excel-automatico
  5. Pronto, agora você já consegue identificar as diferenças rapidamente utilizando esta planilha automática de conciliação.

Quer aprender a programar em VBA?

Este é apenas um exemplo do que você pode fazer utilizando o VBA no Excel. Trabalhos que demoram horas para serem feitos podem ser feitos automaticamente no Excel em minutos e ainda de forma padronizada e sem chance de erro humano.

As vantagens são mais tempo para você ou seu empregado realizar tarefas de análise e não manuais, segurança na informação, e padronização no trabalho.

Se deseja se tornar um dos poucos profissionais no mercado capazes de realizar automações com VBA no Excel veja detalhes do nosso curso Excel PRO – Excel Avançado + VBA + Lógica de programação.

Curso totalmente em vídeo-aulas e passo-a-passo com uma didática apurada e 21h de vídeos, o equivalente á 120h presenciais.

O professor sou eu Marcos Rieper, com experiência como analista, consultor e professor utilizando Excel há mais de 15 anos.

Curso Excel Avançado VBA Lógica de Programação

Abraço

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

4 Comentários

  1. Herbert Biernath disse:

    Obrigado, Professor Marcos! Acompanho sempre seus artigos.
    Boa dica esta planilha de conciliação de dados,baixei para treinar pois a gente com o tempo esquece aquilo que não pratica.

    Abraços
    Herbert

  2. Riptor disse:

    Fantástico método de construir tutoriais através de gif animados! Até mais pedagógicos que vídeos! Une a praticidade de um tutorial escrito com a visualização prática de uma video aula. Muito bem, marcos!

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.