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:
