Como manusear referências circulares em Excel
No Excel, tipicamente digita-se em fórmulas para obter resultados e é bastante comum referenciar outras células no processo. Contudo, o que acontece se fizer referência à própria célula, quer directa quer indirectamente? Isto significa um loop infinito de cálculos! Felizmente, o Excel tem soluções para lidar com este tipo de situações.
Circular Reference significa que a sua fórmula está a tentar calcular a célula de origem. Tipicamente, isto é considerado um erro. Contudo, há alturas em que este erro pode ser realmente útil e pode querer criar uma referência circular de propósito. Vamos analisar mais de perto este fenómeno com alguns exemplos. Pode descarregar a pasta de trabalho de amostra aqui.
Referência Directa
Se for à célula C3, por exemplo, e depois digitar “=C3”, confundiria um pouco o Excel porque esta é uma célula que agora aponta para si própria, criando assim uma referência circular. Fazer o mesmo dentro de uma fórmula também terá o mesmo efeito (isto é =IF(C3,…,…) ou =C3+10).
Outro exemplo comum é incluir a célula original ao seleccionar um intervalo entre células adjacentes. Por exemplo, a inclusão da célula C4 na fórmula de soma =SUM(C2:C4) transformará isto numa referência circular.
Referência Indirecta
Uma célula que está a apontar para uma célula que está a apontar para a célula de origem criará uma referência indirecta. Por outras palavras, a referência circular pode ser estabelecida por duas células que se visem uma à outra. Por exemplo, introduzindo “=D3” na célula B3, enquanto que a célula D3 contém uma referência da célula B3 (“=B3” neste caso).
Porquê é que isto é um problema?
Excel avisa o utilizador quando ocorre uma referência circular, porque uma referência circular significa essencialmente loops infinitos. Dizer ao Excel para repetir o mesmo cálculo vezes sem conta por um número infinito de vezes não faz muito sentido. Para evitar o congelamento do seu computador, o Excel simplesmente ignora as referências circulares.
Vamos dar uma vista de olhos a esta mensagem de aviso. Receberá a seguinte mensagem após digitar uma fórmula circular referenciada,
Nota que o Excel não dá o mesmo erro para referências de fórmula circular adicionais. Na barra de estado do Excel, será mostrada a última célula com uma referência circular. Isto significa que poderá haver mais do que uma dessas referências neste separador. Eis como se parece,
Excel também mantém uma lista de todas as referências circulares, que podem ser acedidas a partir da opção Verificação de Erros na tabulação Fórmula,
Como se livrar deles
Felizmente, não existe uma forma fácil de remover ou corrigir referências circulares indesejadas. Vai precisar de utilizar uma das formas mencionadas anteriormente e resolvê-las uma a uma. Para saber o que está a causar o problema, pode usar a opção Fórmula de Auditoria na tabulação Fórmula. Consulte o nosso guia para saber como fazer isto, https://www.spreadsheetweb.com/identifying-analyzing-spreadsheets-formula-auditing/
Pode ser útil?
Sim! Embora, apenas em algumas circunstâncias. Pode querer intencionalmente calcular um bloco de fórmula circular mais do que poucas vezes. O Excel permite esta abordagem de cálculo “iterativo” usando limites.
Para activar e usar referências circulares, deve primeiro activá-las, verificando a opção Activar cálculos iterativos sob o menu Ficheiro.
Ir para Ficheiro > Opções > Fórmulas > Opções de cálculo no Excel 2016, Excel 2013 e Excel 2010.
no Excel 2007, é necessário ir ao botão Office > Opções Excel > Fórmulas > Área de Iteração
No Excel 2003 e anteriores, precisa de ir aMenu > Ferramentas > Opções > Cálculo
Activar cálculos iterativos irá trazer duas entradas adicionais no mesmo menu,
- Iterações Máximas determina quantas vezes o Excel deve recalcular a pasta de trabalho
- Mudança Máxima determina a diferença máxima entre os valores das fórmulas iterativas. Note-se que um número menor significa resultados precisos.
Os cálculos iterativos param quando uma das condições definidas acima é igualada.
Casos de utilização
Cálculo do Valor Futuro de um Investimento
Vamos assumir que temos $10.000 a querer investir este dinheiro numa conta de CD. O interesse mensal nesta opção é de 1,25%. Descarregue a pasta de trabalho de amostra para este caso de utilização aqui. Para calcular o valor total no final do 21º mês, vamos calcular cada mês e adicionar os juros ao mês anterior.
Primeiro, introduza o dinheiro inicial, os juros, e a função básica do valor total,
=valor * ( 1 + taxa de juro)
Em seguida, seleccione a célula com o valor monetário inicial e adicione a referência da função do valor total.
Este movimento causará um aviso de referência circular se os cálculos iterativos não estiverem activados. Se ainda não o fez, active esta opção e defina Iterações Máximas para 20 para encontrar o interesse no 21º mês.
Pression OK.
Automatic Timestamp
Referências circulares também podem ser usadas para adicionar carimbo de tempo nas células. Descarregue aqui a pasta de trabalho de amostra para este caso de utilização. Vamos assumir que queremos adicionar carimbos de tempo às encomendas na tabela abaixo,
Podemos usar referências circulares para adicionar um carimbo de tempo quando uma nova informação de encomenda é introduzida.
Inicie com a adição de uma nova coluna na tabela para colocar os carimbos de tempo. Escreva na fórmula, =IF(A2<>””,IF(I2<>””,I2,NOW()),””)
Esta fórmula irá verificar se o Número de Ordem (célula A2) foi introduzido. Se houver um valor introduzido e a célula de timestamp estiver vazia, então a fórmula retornará a função NOW().
Nota que premir o botão Enter dará um aviso circular de referência se os cálculos iterativos não estiverem activados. Desta vez as Iterações Máximas ou os números de Alteração Máxima não significam muito porque só precisamos de uma única iteração, pelo que se pode deixar estas duas entradas nos seus valores por defeito.
Agora, cada vez que introduzimos uma nova ordem e criamos uma nova linha, será automaticamente impresso um carimbo da hora na coluna Carimbo da hora.
