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

4

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.
Veja também  Fórmula para determinar a carência do método Price

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.
Veja também  Planilha VBA Pomodoro - Técnica para Organização do Tempo

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.

Veja também  Converter PDF para Excel - Able2Extract 9 download

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

INSCREVA-SE PARA REALIZAR O DOWNLOAD DOS ARQUIVOS E RECEBER NOVIDADES:

Seu nome (obrigatório)

Seu e-mail (obrigatório)

Abraço

Marcos Rieper

4 COMENTÁRIOS

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

Please enter your comment!
Please enter your name here