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
- Separe os dados das suas listas e identifique os campos chave e o campo de valor que será comparado entre as duas.
- 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.
- 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.
- 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.
- Digite e copie para todos os registros desta lista “Lista 1“.
- 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.
- Agora vamos criar o relatório de conciliação. Selecione a célula E1 da planilha da lista conjunta.
- Clique em Inserir->Tabela dinâmica e selecione as colunas A á C em selecionar uma tabela ou intervalo, conforme abaixo e clique em OK.
- Na tabela dinâmica criada posicione os campos conforme a imagem.
- Ao lado da última coluna digite no cabeçalho Diferença e abaixo a fórmula =F3-G3 e arraste até a última linha.
- 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.
- Filtre os dados pela coluna Diferença selecionando apenas os valores que tenham valores diferentes de 0.
- 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:
- Ao abrir a planilha marque a opção Habilitar Macros da tarja em amarelo que pode aparecer acima da planilha.
- Clique no botão Lista A e coloque os campos chave e valor, a origem é preenchida automaticamente pelo sistema.
- Repita a operação na lista B, colocando os dados da lista 2 que você deseja comparar.
- Clique no botão Conciliar Listas e veja que todo o trabalho que tivemos antes é criado automaticamente no Excel.
- 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.
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:
