Como conciliar dados no Excel?

É comum termos a necessidade de encontrar diferenças mas como conciliar dados no Excel para comparar estes dados e identificar em que ponto estão estas diferenças?

Neste artigo você verá um exemplo passo-a-passo de como conciliar dados no Excel.

No exemplo iremos comparar um relatório de lançamentos contábeis com um relatório financeiro e identificar as diferenças entre notas fiscais automaticamente.

O que você irá precisar para conciliar no Excel?

Inicialmente você irá precisar de uma versão do Excel que tenha o PowerQuery.

Esta funcionalidade está disponível desde a versão 2010 como suplemento e se você tem o Excel 2013 em diante já está integrado no Excel, apenas mudando o local.

No nosso exemplo iremos utilizar o Excel da versão Office 365, porém o processo é semelhante para outras versões do Excel.

Relatórios para conciliação

No nosso exemplo temos um batimento entre o Financeiro e o Contábil, que é uma situação muito normal nas empresas.

Este procedimento que iremos fazer será com dois relatórios em formato texto tabulado, mas poderia ser qualquer formato.

Poderiam ser arquivos csv, txt, banco de dados, xml, qualquer tipo de importação permitida pelo PowerQuery do Excel.

Mas no nosso exemplo temos o relatório abaixo que é do departamento contábil e tem os dados de lançamentos contábeis.

Na conciliação temos então a comparação com o departamento Financeiro, aonde temos o contas a receber.

O relatório do departamento financeiro tem o seguinte formato:

Importante, não altere o relatório antes de importar no powerquery, sempre deixe ele no formato original antes de importar o relatório.

Isto é muito recomendado porque assim teremos uma garantia maior de que o tratamento de dados no Excel ocorra da forma correta.

Importar relatórios para conciliar no Excel

A primeira parte da conciliação dos dados é a importação das origens de dados no Powerquery do Excel.

No nosso exemplo criamos duas pastas no Windows aonde colocamos o relatório contábil e o relatório financeiro, cada qual em sua devida pasta.

Em seguida realizamos a importação dos dados.

No exemplo iremos em Dados->Obter Dados->De Arquivo->Da Pasta

Em seguida selecionamos a pasta aonde estão os arquivos de importação, para cada uma das pastas.

No caso temos a pasta de lançamentos contábeis e outra pasta para Relatórios financeiros.

Em seguida devem ser realizados os tratamentos no Powerquery para cada um dos relatórios.

De modo que fiquem conforme abaixo:

Abaixo temos o relatório financeiro e acima o contábil.

Depois de todo o tratamento defina os nomes de cada consulta do powerquery que se tratarem de campos semelhantes, deve definir o nome igual para cada uma das colunas.

Os campos Nota fiscal, Valor e Cliente devem ter o mesmo nome para que estes dados sejam colocados na mesma coluna.

Para cada uma das consultas é necessário incluir uma Coluna personalizada, que chamaremos de Origem.

Esta coluna de Origem receberá "Contábil" no relatório contábil e "Financeiro" no relatório Financeiro.

Estas colunas de origem serão utilizadas para realizarmos então a comparação dos dados no Excel.

Por fim você deve então acrescentar as consultas, unificando-as.

Para isso clique em Página Inicial e em Acrescentar Consultas

Selecionamos então o relatório financeiro e adicionamos na consulta contábil.

Desta forma teremos a integração dos dados com os dados de cada uma das consultas uma abaixo de cada uma das colunas.

E por fim clique em Fechar e Carregar Para...

Selecione Apenas criar Conexão

E em seguida clique com o botão direito sobre a consulta e clique em Criar Tabela Dinâmica.

Selecione os campos em Linhas: Nota fiscal, em Colunas: Origem e em Valores: Valor.

Download

Realize o download do arquivo deste exemplo e da vídeo-aula acima neste botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.

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: