Função Conciliar Excel - Como Conciliar no Excel

Como conciliar Excel usando uma função.

Veja como pode criar uma função Lambda no Excel para realizar a conciliação automática de dados no Excel.

Estrutura para a Conciliação no Excel

A conciliação consiste em identificar diferenças entre duas listas.

Para realizar a conciliação de dados no Excel uma das formas é usando a tabela dinâmica.

Esta forma funciona em todas as versões que possuem tabela dinâmica, ou seja, todas as mais recentes.

Veja abaixo uma tabela que desejamos comparar com outra.

Esta é uma tabela do financeiro e queremos comparar o número da nota fiscal e encontrar notas com diferenças de valores e faltantes na lista do contábil.

A lista do contábil segue um padrão parecido, mas o importante é que tenha uma mesma chave e uma outra coluna de valor para a comparação.

Com isso coloque os dados um abaixo do outro e uma coluna Identificador, aonde é repetido o nome da origem.

Com essa lista pode ser feita uma tabela dinâmica em Inserir->Dados->Tabela Dinâmica.

Arrastando os dados como acima, temos então a conciliação e colocando uma coluna de diferença dos valores e usando uma simples fórmula de uma coluna menos a outra.

Temos a conciliação identificando as diferenças.

Função Conciliar Excel

A função seguinte foi criada no Excel 365, usando uma função LAMBDA.

A função lambda permite que criemos funções próprias no Excel, como esta que veremos.

O objetivo na função é retornar exatamente uma tabela no formato da que tivemos no exemplo da tabela dinâmica, mas sem ter que usá-la, apenas passando as chaves e valores.

=LAMBDA(Nome1;Valor1;Nome2;Valor2;NomeCol1;NomeCol2;LET(Base1;Nome1;
Valor1;Valor1;
Nome1;DIVIDIRTEXTO(REPT("NomeColuna1"&"!";CONT.VALORES(Valor1));;"!";VERDADEIRO);
Base2;Nome2;
Valor2;Valor2;
Nome2;DIVIDIRTEXTO(REPT("NomeColuna2"&"!";CONT.VALORES(Valor2));;"!";VERDADEIRO);
ColNome;ÚNICO(EMPILHARV(Base1;Base2));
ColVal1;SOMASES(Valor1;Base1;ColNome);
ColVal2;SOMASES(Valor2;Base2;ColNome);
Diferenca;ColVal1-ColVal2;
Titulo;DIVIDIRTEXTO("Nome!"&NomeCol1&"!"&NomeCol2&"!Diferença";"!";;VERDADEIRO);
EMPILHARV(Titulo;HSTACK(ColNome;ColVal1;ColVal2;Diferenca))))(tFinanceiro[Nota Fiscal];tFinanceiro[Valor];tContabilidade[Nota Fiscal];tContabilidade[Valor];"Financeiro";"Contabilidade")

Agora com a função acima copiada, abra o Gerenciador de Nomes: Fórmulas->Definir Nome e crie um nome, escreva fConciliar ou qualquer outro nome para a função e cole em Refere-se a a fórmula acima.

Depois disso já pode usar a função.

Para a usar basta passar os parâmetros:

=fConciliar(NomeCol1;ValorCol1;NomeCol2;ValorCol2;Titulo1;Titulo2)

Como no exemplo abaixo:

=fConciliar(tFinanceiro[Nota Fiscal];tFinanceiro[Valor];tContabilidade[Nota Fiscal];tContabilidade[Valor];B5;H5)

Como resultado temos a conciliação pronta conforme vemos na imagem:

Download Conciliar Guia do Excel

Clique no botão abaixo para realizar o  download do arquivo de exemplo: