Resolvendo Referências Circulares do Excel

[AZuc]

Caros,

Recentemente tive que usar o Alteryx para solucionar um caso que somente era resolvido com o uso de Cálculo Iterativo no Excel, dado que o problema gerou uma fórmula com referência circular.

E o que seriam referências circulares?

Vamos explicar com um exemplo. O caso abaixo é uma simplificação do problema original, mas tem como base um caso real de cálculo de imposto.

Os campos que têm valor conhecido são:

Valor de Venda ou Valor Contábil: Valor que consta na nota fiscal como valor final da operação.

Pauta Fiscal: Valor de referência do produto, estabelecido por cada estado brasileiro, obtido através de pesquisas de preço.

Alíquota do ICMS: percentual de imposto aplicado sobre o Preço Líquido.

Alíquota de outros impostos: para efeitos de simplificação, unifiquei os demais impostos neste campo.

E precisaremos calcular outros campos:

Valor do ICMS: Alíquota do ICMS * Preço Líquido.

Valor dos Outros Impostos: Alíquotas de outros impostos * Preço Líquido.

Valor do ICMS-ST: (Pauta Fiscal * Alíquota do ICMS) - Valor do ICMS

Preço Líquido: Valor Contábil - Valor do ICMS - Valor dos Outros Impostos - Valor do ICMS-ST

O objetivo principal é conhecer o Preço Líquido, sob o qual todos os demais cálculos serão realizados. No entanto, não é possível calcular este campo sem se saber o Valor do ICMS-ST, que, por sua vez, precisa do Valor do ICMS, e este último precisa do Preço Líquido. Chegamos então à situação de expressão circular.

Os campos em azul representam valores conhecidos.

Conversando com um colega de equipe, chegamos à conclusão de que poderíamos utilizar uma macro iterativa, fixando uma das variáveis em cada iteração (no nosso caso, o Preço Líquido), e validando se o valor contábil calculado era igual ao Valor Contábil, caso não fosse, iríamos incrementando (ou decrementando) o Preço Líquido, até que as formulas alcançassem o Valor Contábil esperado.

O problema passou a ser como incrementar. A primeira ideia foi incrementar de 1 em 1 centavo, até atender ao Valor Contábil. Considerando aproximações, chegamos ao Primeiro Fluxo, em anexo. Como primeiro “chute” para o Preço Líquido, considerei a metade do Valor Contábil.

Na primeira execução, tomei um erro de limite de iterações atingido. Aumentei a quantidade de iterações para 10.000 e adicionei o campo #iteracoes (número de iterações, baseado no campo constante [Engine.IterationNumber]) para ter ideia de quantos valores seriam testados até se chegar ao valor correto.

Fiz o teste com apenas 1 registro com os seguintes valores:

Valor Contábil: 100

Pauta Fiscal: 110

Aliq ICMS: 0.3

Aliq Outros: 0.16

Preço Líquido (primeiro Preço Líquido a ser testado): 50 (metade de Valor Contábil).

O fluxo demorou 4.4 segundos, e gerou 777 iterações.

Apesar do resultado ter sido atingido, achei melhor validar certas premissas com o cliente. Na reunião obtive duas informações preocupantes: o volume de dados a ser processado era na casa de 500 mil registros e; o nível de precisão teria que ser na 4ª casa decimal.

Fui testar a mesma macro com precisão na 4ª casa decimal, ou seja, em vez de incrementar em 0.01 em 0.01 (1 em 1 centavo), teria que incrementar de 0.0001 em 0.0001. O fluxo demorou 4:23 minutos e executou 77.587 iterações. O resultado, apesar de correto para 1 registro, tornava-se inviável para o volume de 500 mil cálculos.

Então procurei outras abordagens para reduzir o número de iterações. Me lembrei de uma técnica que utilizei há anos, ainda com linguagem de programação (no caso foi com VBA de Excel), chamada pesquisa binária.

A ideia básica da técnica consiste em buscar os valores sempre à metade da distância dos extremos. Novamente, vamos buscar um exemplo para ilustrar.

Imagine que seus valores variem de 0 a 100, e você quer procurar o valor 70. Na primeira iteração buscamos a média entre o máximo e o mínimo, ou seja, (100+0)/2, que dá 50. 50 é maior ou menor do que 70? É menor, então o novo mínimo passa a ser 50. Vamos detalhar cada passo na lógica abaixo:

1ª iteração: Minimo=0, Máximo=100, Média=50, 50 < 70? Sim! Novo mínimo=50

2ª iteração: Minimo=50, Máximo=100, Média=75, 75 < 70? Não! Novo máximo=75

3ª iteração: Minimo=50, Máximo=75, Média=62.50, 62.50 < 70? Sim! Novo mínimo=62.50

4ª iteração: Minimo=62.50, Máximo=75, Média=68.75, 68.75 < 70? Sim! Novo mínimo=68.75

5ª iteração: Minimo=68.75, Máximo=75, Média=71.88, 71.88 < 70? Não! Novo máximo=71.88

6ª iteração: Minimo=68.75, Máximo=71.88, Média=70.32, 70.32 < 70? Não! Novo máximo=70.32

7ª iteração: Minimo=68.75, Máximo=70.32, Média=69.54, 69.54 < 70? Sim! Novo mínimo=69.54

8ª iteração: Minimo=69.54, Máximo=70.32, Média=69.93, 69.93 < 70? Não! Novo mínimo=69.93

9ª iteração: Minimo=69.93, Máximo=70.32, Média=70.13, 70.13 < 70? Não! Novo máximo=70.13

10ª iteração: Minimo=69.93, Máximo=70.13, Média=70.03, 70.03 < 70? Não! Novo máximo=70.03

11ª iteração: Minimo=69.93, Máximo=70.03, Média=69.53, 69.53 < 70? Sim! Novo mínimo=69.53

12ª iteração: Minimo=69.53, Máximo=70.03, Média=69.78, 69.78 < 70? Não! Novo mínimo=69.78

13ª iteração: Minimo=69.78, Máximo=70.03, Média=69.91, 69.91 < 70? Não! Novo mínimo=69.91

14ª iteração: Minimo=69.91, Máximo=70.03, Média=69.97, 69.97 < 70? Não! Novo mínimo=69.97

15ª iteração: Minimo=69.97, Máximo=70.03, Média=70.00, 70.00 < 70? Não! É igual! Chegamos ao resultado!!!

15 iterações! Extenso, não? Mas observe como a distância até o objetivo diminui agressivamente a cada iteração:

1ª iteração: 50 - 70 = -20

2ª iteração: 75 - 70 = +5

3ª iteração: 62.50 - 70 = -7.5

4ª iteração: 68.75 - 70 = -1.25

5ª iteração: 71.88 - 70 = +1.88

6ª iteração: 70.32 - 70 = +0.32

7ª iteração: 69.54 - 70 = -0.46

8ª iteração: 69.93 - 70 = -0.07

9ª iteração: 70.13 - 70 = +0.13

10ª iteração: 70.03 - 70 = +0.03

11ª iteração: 69.53 - 70 = -0.47

12ª iteração: 69.78 - 70 = -0.22

13ª iteração: 69.91 - 70 = -0.09

14ª iteração: 69.97 - 70 = -0.03

15ª iteração: 70.00 = 70 = 0!!!

Comparado a 777 iterações na 2a casa decimal, 15 parece um número razoável. Mas vamos testar na prática no nosso fluxo.

Fiz uma pequena adaptação na lógica. Em vez de trabalhar com máximo e mínimos absolutos, decidi testar o novo valor do preço líquido a ser testado como a metade da diferença entre os valores contábeis calculado e real.

No primeiro teste, com precisão na 2ª casa decimal, nosso fluxo demorou 1.7 segundos, e realizou 6 iterações até atingir o resultado correto. É uma boa melhoria em relação às 777 iterações originais.

Testei então com precisão na 4ª casa decimal. O fluxo durou 1.1 segundo, com apenas 8 iterações, em vez de 77.587. O Fluxo Final está em anexo para consulta.

Por fim, testamos 500 mil registros com valores aleatórios de Valor Contábil e Pauta Fiscal para as duas abordagens. Vamos aos resultados.

O resultado comparativo foi:

Fluxo Final (com 500 mil registros): 9.8 segundos

Primeiro Fluxo (com 500 mil registros): 3:03 horas

Resumindo a experiência:

Referências circulares são um tema mais amplo do que o tratado aqui no blog. Este é um caso específico resolvido com macro iterativa. Creio que existam outros problemas que possam exigir outras técnicas. Gostaria de saber de outros casos.

A técnica (adaptada) da pesquisa binária foi fundamental para que o caso se tornasse viável. Gostei bastante do resultado e resolvi compartilhar.

Primeiro Fluxo.yxzp

Fluxo Final 500k.yxzp