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

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
Baixe a planilha

Abraço

Marcos Rieper

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:


Marcos Rieper

Pai, marido, professor e consultor em Excel.

Obrigado por ler este artigo, este blog foi criado para difundir o conhecimento em Excel à todos.

Divulgamos novos artigos nas redes sociais, basta clicar nos ícones abaixo.

Excel não precisa ser complicado

Assine nossa newsletter e receba dicas práticas para dominar o excel