Referência circular no Excel é uma célula que depende de si mesma, direto ou por um caminho de outras células. Em calculadora de preço, ela quase sempre nasce do mesmo lugar: o preço de venda define a faixa de frete, e a faixa de frete entra na formação do preço de venda.
Se a sua calculadora de preço travou nisso, a gente refaz a conta do jeito certo: planilha sob medida para formação de preço.
Quem mexe nessa planilha no dia a dia costuma ser a pessoa que abre o arquivo de manhã, digita a taxa do portal na mão, digita o frete na mão e passa para o próximo produto. O laço não aparece para ela em lugar nenhum, e a margem só some no fim do mês.
A gente encontrou exatamente isso numa empresa que vende em marketplace. A calculadora de preço tinha taxa de portal e frete preenchidos na mão, um por um, e o arquivo dava referência circular entre o preço e a faixa de frete.
O tamanho do problema: entre 5.000 e 7.000 códigos de produto por empresa. Ninguém abre 7.000 linhas para descobrir em qual delas a fórmula está presa no próprio resultado.
O que é referência circular, sem rodeio
A célula B10 soma B2 até B10. Ela precisa do próprio resultado para calcular o próprio resultado. O Excel não consegue fechar a conta e avisa.
O caso da calculadora de preço é mais discreto, porque a volta é longa. Ela passa por quatro células antes de voltar ao ponto de partida.
O preço de venda entra numa tabela de faixas. A faixa devolve o valor do frete daquele portal. O frete entra no custo total do produto. O custo total é a base para calcular o preço de venda.
Cada fórmula, olhada sozinha, está certa. O problema está no encadeamento delas, que foi crescendo aos poucos, sem ninguém ter planejado o caminho inteiro. Numa planilha sob medida com o cálculo desenhado antes de ser escrito, esse laço não chega a nascer.
Por que isso aparece justo na precificação de marketplace
Porque o portal cobra frete por faixa de preço, e não por produto.
Nessa empresa, dois portais cobravam frete fixo de R$ 5,00. Um terceiro cobrava R$ 4,00. O quarto trabalhava com faixa, de R$ 4,50 a R$ 6,75, conforme a faixa em que o anúncio caía.
Nos três primeiros, não existe circularidade. O frete é um número que não depende de nada.
No quarto, existe. Você precisa do preço para saber a faixa, e precisa da faixa para chegar no preço, então as duas pontas dependem uma da outra.
A distância entre o piso e o teto da faixa é R$ 2,25. Dividindo, R$ 6,75 sobre R$ 4,50 dá 1,5. O teto é 50% maior que o piso, então errar a faixa tira R$ 2,25 de cada venda.
Como o erro passa despercebido
Existem dois jeitos de essa planilha continuar rodando e entregando número errado.
O zero que ninguém lê
Com o cálculo iterativo desligado, o Excel avisa uma vez, na abertura, e devolve 0 na célula circular.
Zero no frete reduz o custo total e, com isso, o preço calculado. O valor aparece formatado e plausível na tela, só que abaixo do que deveria ser cobrado.
Em 7.000 códigos, o aviso aparece uma vez e a pessoa fecha. As 7.000 linhas continuam calculando.
O cálculo iterativo, que é pior
Se alguém ligou o cálculo iterativo nas opções do Excel, a mensagem some. E aí a planilha passa a devolver um número.
Esse número depende de quantas voltas o Excel deu e de onde ele começou. Recalcular o arquivo pode mudar o valor. Abrir amanhã pode mudar o valor de novo.
Nesse estado a planilha não acusa erro nenhum e entrega um preço que muda sozinho a cada recálculo, o que é bem mais difícil de perceber do que a mensagem de aviso.
Saída 1: quebrar a dependência com faixa fixa por regra
A saída mais simples é tirar o preço de dentro da definição da faixa.
O frete deixa de sair do preço final e passa a sair de um atributo que não muda quando o preço muda. Peso, dimensão, categoria do produto ou um preço de tabela congelado, definido antes da precificação.
A sequência fica em linha reta: o atributo define a faixa, a faixa define o frete, o frete entra no custo e o custo forma o preço, sem que o preço volte para o começo.
Na prática, isso vira uma tabela de duas colunas por portal, com a regra escrita: qual atributo lê e qual valor devolve. Nos portais de frete fixo, a tabela tem uma linha só.
Você perde um pouco de precisão nos produtos que ficam na fronteira de duas faixas, e em troca a planilha fecha sempre e o número dá para explicar para o time.
Saída 2: calcular em duas passadas
Quando a diferença entre faixas é grande demais para arredondar, o caminho é separar o cálculo em dois momentos.
Passada 1. Calcule um preço candidato usando o piso da faixa, ou um frete provisório. Nessa etapa, o frete é um valor de entrada, não um resultado.
Leitura. Com o preço candidato na mão, leia em qual faixa ele caiu. Isso é uma consulta de tabela, não uma fórmula circular.
Passada 2. Recalcule o preço com o frete real daquela faixa. Agora o número está fechado.
Falta a regra de parada, e é ela que a maioria esquece. Se o preço da passada 2 pular para outra faixa, você não pode ficar recalculando para sempre. Escolha uma das duas regras e escreva ela na planilha: trava na faixa mais cara das duas, ou faz uma terceira passada e para ali, marcando a linha como divergente.
Sem regra de parada, você recriou a circularidade em duas colunas.
As duas saídas lado a lado
| Faixa fixa por regra | Duas passadas | |
|---|---|---|
| O que define o frete | peso, dimensão ou categoria | o preço candidato da passada 1 |
| Colunas a mais na planilha | 1 | 3 e uma marcação de divergência |
| Precisão na fronteira da faixa | menor | maior |
| Chance de voltar a girar | nenhuma | existe, se faltar regra de parada |
| Quem consegue manter | qualquer pessoa do time | quem entendeu o desenho |
| Melhor para | portal de frete fixo e base grande | portal de faixa larga e mix de preço espalhado |
Nessa empresa, três dos quatro portais resolveram pela primeira saída, porque o frete deles já era fixo. Só o portal da faixa de R$ 4,50 a R$ 6,75 precisou das duas passadas.
Quanto custa isso em 7.000 códigos
São duas contas, a do tempo de preenchimento e a da margem perdida.
A conta do tempo. Preencher taxa e frete na mão, a 40 segundos por código, dá 280.000 segundos em 7.000 itens. São 77,8 horas, quase dez dias de trabalho de oito horas. Cada vez que um portal mexe na regra de frete, o relógio começa de novo.
A conta da margem. Suponha um código que vende 30 unidades no mês e está com R$ 2,25 de frete a menos do que o real. São R$ 67,50 no mês. Se cem códigos da base estiverem nessa situação, dá R$ 6.750 no mês e R$ 81.000 no ano.
Cem de 7.000 é pouco mais de 1% da base, uma fatia pequena o bastante para ninguém ir procurar.
Como se descobre em cinco minutos
Abra a planilha e olhe a barra de status, embaixo, à esquerda. Se existir referência circular ativa, o Excel escreve ali e mostra o endereço da célula.
Depois vá em Fórmulas, Verificação de Erros, Referências Circulares. A lista aparece inteira.
Confira também se o cálculo iterativo está ligado, em Arquivo, Opções, Fórmulas. Se estiver, desligue e veja quantas células viram zero. Cada zero desse era um preço errado circulando sem aviso.
Por último, um teste de sanidade que pega o resto. Escolha três produtos com preços bem diferentes, calcule o preço na calculadora e refaça na mão, no papel. Se os três baterem, o encadeamento das fórmulas está certo.
Perguntas frequentes
O que significa referência circular no Excel
É uma célula que depende de si mesma, direto ou por um caminho de outras células. Em calculadora de preço a volta costuma passar por quatro células: o preço define a faixa de frete, a faixa devolve o valor, o frete entra no custo e o custo forma o preço de novo.
Como resolver referência circular na planilha de preço
Duas saídas. Ou o frete passa a depender de peso, dimensão ou categoria, atributos que não mudam quando o preço muda, ou o cálculo acontece em duas passadas com regra de parada escrita. Na empresa do exemplo, três dos quatro portais resolveram pela primeira saída, porque o frete deles já era fixo em R$ 5,00 e R$ 4,00.
Posso ligar o cálculo iterativo do Excel para resolver?
Não é boa ideia numa planilha de preço. Ligar o cálculo iterativo apaga a mensagem de aviso e devolve um número que depende de quantas voltas o Excel deu e de onde ele começou. Recalcular o arquivo pode mudar o valor, e abrir amanhã pode mudar de novo. Com o recurso desligado, a célula devolve zero e some R$ 6,75 de frete do custo.
Quanto custa deixar a referência circular na planilha de preço?
Cem códigos com R$ 2,25 de frete a menos, vendendo 30 unidades por mês cada, custam R$ 6.750 por mês e R$ 81.000 no ano. Cem itens em 7.000 é pouco mais de 1% da base, a fatia que ninguém procura. Some as 77,8 horas gastas para preencher taxa e frete na mão a cada mudança de regra.
Leia também
- Cálculo de preço de venda em marketplace por SKU
- Preço errado no marketplace: onde some a margem
- Fórmula quebrada na planilha: quanto custa o erro
Escolha uma das duas saídas e escreva a regra
Referência circular se resolve escolhendo um dos dois caminhos: ou o frete para de depender do preço, ou o cálculo acontece em dois momentos com regra de parada escrita.
Escolha a que cabe no seu mix e deixe a regra escrita dentro do arquivo, numa aba de leitura. Quem abrir a planilha daqui a seis meses precisa saber por que aquela coluna existe.
A gente refaz essa planilha sob medida, com as faixas dos seus portais, o seu volume de códigos e o número clicável até a origem. Manda uma mensagem contando quantos códigos você precifica hoje e em quantos portais.
Opa,
o que você achou dessa aula? Conta pra mim 👇