Come gestire i riferimenti circolari in Excel

In Excel, tipicamente si digitano formule per ottenere risultati ed è abbastanza comune fare riferimento ad altre celle nel processo. Tuttavia, cosa succede se si fa riferimento alla cella stessa, direttamente o indirettamente? Questo significa un ciclo infinito di calcoli! Fortunatamente, Excel ha delle soluzioni per affrontare questo tipo di situazioni.

Riferimento circolare significa che la vostra formula sta cercando di calcolare la cella di origine. Tipicamente, questo è considerato un errore. Tuttavia, ci sono momenti in cui questo errore può effettivamente essere utile e si potrebbe voler creare un riferimento circolare di proposito. Diamo un’occhiata più da vicino a questo fenomeno con alcuni esempi. Potete scaricare la cartella di lavoro di esempio qui.

Riferimento diretto

Se andate nella cella C3, per esempio, e poi digitate “=C3”, confonderete un po’ Excel perché questa cella ora punta a se stessa, creando così un riferimento circolare. Fare la stessa cosa all’interno di una formula avrà lo stesso effetto (cioè =IF(C3,…,…) o =C3+10).

Un altro esempio comune è includere la cella originale quando si seleziona un intervallo da celle adiacenti. Per esempio, includere la cella C4 nella formula di somma =SUM(C2:C4) la trasformerà in un riferimento circolare.

Riferimento indiretto

Una cella che punta ad una cella che punta alla cella di origine creerà un riferimento indiretto. In altre parole, il riferimento circolare può essere stabilito da due celle che si puntano a vicenda. Per esempio, inserendo “=D3” nella cella B3, mentre la cella D3 contiene un riferimento dalla cella B3 (“=B3” in questo caso).

Perché è un problema?

Excel avverte l’utente quando si verifica un riferimento circolare, perché un riferimento circolare significa essenzialmente cicli infiniti. Dire a Excel di ripetere lo stesso calcolo più e più volte per un numero infinito di volte non ha molto senso. Per evitare il congelamento del computer, Excel ignora semplicemente i riferimenti circolari.

Diamo un’occhiata a questo messaggio di avviso. Otterrete il seguente messaggio dopo aver digitato una formula con riferimento circolare,

Nota che Excel non dà lo stesso errore per ulteriori riferimenti di formula circolare. Sulla barra di stato di Excel, verrà mostrata l’ultima cella con un riferimento circolare. Questo significa che potrebbe esserci più di un riferimento di questo tipo in questa scheda. Ecco come appare,

Excel mantiene anche un elenco di tutti i riferimenti circolari, a cui si può accedere dall’opzione Controllo errori nella scheda Formula,

Come liberarsene

Purtroppo, non c’è un modo semplice per rimuovere o correggere i riferimenti circolari indesiderati. Dovrete usare uno dei modi menzionati prima e risolverli uno per uno. Per scoprire cosa sta causando il problema, puoi usare l’opzione Formula Auditing nella scheda Formula. Vedi la nostra guida per imparare a farlo, https://www.spreadsheetweb.com/identifying-analyzing-spreadsheets-formula-auditing/

Possono essere utili?

Sì! Anche se solo in alcune circostanze. Si può volere intenzionalmente calcolare un blocco di formula circolare più di qualche volta. Excel permette questo approccio di calcolo “iterativo” utilizzando i limiti.

Per attivare e utilizzare i riferimenti circolari dovete prima attivarli selezionando l’opzione Enable iterative calculations sotto il menu File.

Vai a File > Opzioni > Formule > Opzioni di calcolo in Excel 2016, Excel 2013 e Excel 2010.

In Excel 2007, è necessario andare al pulsante Office > Opzioni di Excel > Formule > Area di iterazione

In Excel 2003 e precedenti, è necessario andare suMenu > Strumenti > Opzioni > Calcolo

Abilitando i calcoli iterativi si ottengono due input aggiuntivi nello stesso menu,

• Maximum Iterations determina quante volte Excel deve ricalcolare la cartella di lavoro
• Maximum Change determina la massima differenza tra i valori delle formule iterative. Si noti che un numero più piccolo significa risultati accurati.

I calcoli iterativi si fermano quando una delle condizioni definite sopra viene soddisfatta.

Casi d’uso

Calcolo del valore futuro di un investimento

Immaginiamo di avere $10.000 e di voler investire questi soldi in un conto CD. L’interesse mensile in questa opzione è 1,25%. Scaricate la cartella di lavoro di esempio per questo caso d’uso qui. Per calcolare il valore totale alla fine del 21° mese, calcoleremo ogni mese e aggiungeremo l’interesse al mese precedente.

Prima, inserite il denaro iniziale, l’interesse e la funzione di base del valore totale,

=valore * ( 1 + tasso di interesse)

Poi, selezionate la cella con il valore iniziale in contanti e aggiungete il riferimento della funzione valore totale.

Questa mossa causerà un avviso di riferimento circolare se i calcoli iterativi non sono abilitati. Se non lo avete ancora fatto, abilitate questa opzione e impostate Maximum Iterations a 20 per trovare l’interesse al 21° mese.

Premete OK.

Timbro automatico

I riferimenti circolari possono anche essere usati per aggiungere il timbro temporale nelle celle. Scaricate la cartella di lavoro di esempio per questo caso d’uso qui. Supponiamo di voler aggiungere una marca temporale agli ordini nella tabella qui sotto,

Possiamo usare i riferimenti circolari per aggiungere una marca temporale quando vengono inserite le informazioni di un nuovo ordine.

Iniziamo con l’aggiungere una nuova colonna nella tabella per inserire le marche temporali. Digitate la formula, =IF(A2<>””,IF(I2<>”,I2,NOW()),””)

Questa formula controllerà se il numero d’ordine (cella A2) è stato inserito. Se c’è un valore inserito e la cella timestamp è vuota, allora la formula restituirà la funzione NOW().

Nota che premendo il pulsante Invio si avrà un avviso di riferimento circolare se i calcoli iterativi non sono abilitati. Questa volta i numeri Maximum Iterations o Maximum Change non significano molto perché abbiamo bisogno di una sola iterazione, quindi potete lasciare questi due input nei loro valori di default.

Ora, ogni volta che inseriamo un nuovo ordine e creiamo una nuova riga, un timestamp verrà automaticamente stampato sulla colonna Timestamp.