Mentre lavori con le formule di Excel, a volte puoi vedere il seguente avviso.
Questa richiesta ti dice che c’è un riferimento circolare nel tuo foglio di lavoro e questo può portare o un calcolo errato dalle formule. Ti chiede anche di affrontare il problema del riferimento circolare e di risolverlo.
In questo tutorial, coprirò tutto ciò che devi sapere sul riferimento circolare, e anche come trovare e rimuovere i riferimenti circolari in Excel.
Perciò iniziamo!
Questo tutorial copre:
Cos’è il riferimento circolare in Excel?
In parole semplici, un riferimento circolare avviene quando si finisce per avere una formula in una cella – che di per sé utilizza la cella (in cui è stata inserita) per il calcolo.
Provo a spiegarlo con un semplice esempio.
Supponiamo che abbiate il dataset nella cella A1:A5 e che usiate la seguente formula nella cella A6:
=SUM(A1:A6)
Questo vi darà un avviso di riferimento circolare.
Questo perché volete sommare i valori nella cella A1:A6, e il risultato dovrebbe essere nella cella A6.
Questo crea un ciclo, perché Excel continua ad aggiungere il nuovo valore nella cella A6, che continua a cambiare (quindi, un ciclo di riferimento circolare).
Come trovare riferimenti circolari in Excel?
Mentre l’avviso di riferimento circolare è abbastanza gentile da dirvi che esiste nel vostro foglio di lavoro, non vi dice dove sta accadendo e quali riferimenti di cella lo stanno causando.
Quindi se state cercando di trovare e gestire i riferimenti circolari nel foglio di lavoro, dovete conoscere un modo per trovarli in qualche modo.
Di seguito i passi per trovare un riferimento circolare in Excel:
- Attivate il foglio di lavoro che ha il riferimento circolare
- Fate clic sulla scheda Formule
- Nel gruppo Modifica formula, fate clic sull’icona a discesa Controllo errori (piccola freccia rivolta verso il basso a destra)
- Passate il cursore sull’opzione Riferimenti circolari. Vi mostrerà la cella che ha un riferimento circolare nel foglio di lavoro
- Clicca sull’indirizzo della cella (che viene visualizzato) e vi porterà a quella cella nel foglio di lavoro.
Una volta che avete affrontato il problema, potete di nuovo seguire gli stessi passi sopra e mostrerà più riferimenti di cella che hanno il riferimento circolare. Se non c’è, non vedrai alcun riferimento di cella,
Un altro modo semplice e veloce per trovare il riferimento circolare è guardare la barra di stato. Nella parte sinistra di essa, vi mostrerà il testo Circular Reference insieme all’indirizzo della cella.
Ci sono alcune cose da sapere quando si lavora con i riferimenti circolari:
- Nel caso in cui il calcolo iterativo sia abilitato (trattato più avanti in questo tutorial), la barra di stato non mostrerà l’indirizzo della cella di riferimento circolare
- Nel caso in cui il riferimento circolare non si trovi nel foglio attivo (ma in altri fogli della stessa cartella di lavoro), mostrerà solo Riferimento circolare e non l’indirizzo della cella
- Nel caso in cui si riceva una richiesta di avviso di riferimento circolare una volta e la si ignori, la prossima volta la richiesta non sarà più mostrata.
- Se aprite una cartella di lavoro che ha il riferimento circolare, vi mostrerà la richiesta non appena la cartella di lavoro si apre.
Come rimuovere un riferimento circolare in Excel?
Una volta che avete identificato che ci sono riferimenti circolari nel vostro foglio, è il momento di rimuoverli (a meno che non vogliate che siano lì per un motivo).
Purtroppo, non è semplice come premere il tasto di cancellazione. Poiché questi dipendono dalle formule e ogni formula è diversa, dovete analizzare la cosa caso per caso.
Nel caso in cui sia solo una questione di avere il riferimento alla cella che causa il problema per errore, potete semplicemente correggere regolando il riferimento.
Ma a volte, non è così semplice.
Il riferimento circolare può anche essere causato da più celle che si alimentano a vicenda a molti livelli.
Vi mostro un esempio.
Sotto c’è un riferimento circolare nella cella C6, ma non è un semplice caso di autoreferenza. E’ multilivello dove le celle che usa nei calcoli fanno riferimento anche l’una all’altra.
- La formula nella cella A6 è =SUM(A1:A5)+C6
- La formula nella cella C1 è =A6*0.1
- La formula nella cella C6 è =A6+C1
Nell’esempio precedente, il risultato nella cella C6 dipende dai valori nella cella A6 e C1, che a loro volta dipendono dalla cella C6 (causando così l’errore di riferimento circolare)
E ancora, ho scelto un esempio molto semplice solo a scopo dimostrativo. In realtà, questi potrebbero essere abbastanza difficili da capire e forse lontani nello stesso foglio di lavoro o anche sparsi in più fogli di lavoro.
In tal caso, c’è un modo per identificare le celle che stanno causando il riferimento circolare e quindi trattarle.
E’ usando l’opzione Trace Precedents.
Di seguito sono riportati i passi per utilizzare Trace Precedents per trovare le celle che alimentano la cella che ha il riferimento circolare:
- Selezionate la cella che ha il riferimento circolare
- Fate clic sulla scheda Formule
- Fate clic su Traccia Precedenti
I passi precedenti vi mostreranno delle frecce blu che vi diranno quali celle stanno alimentando la formula nella cella selezionata. In questo modo, potete ispezionare le formule e le celle e sbarazzarvi del riferimento circolare.
Nel caso stiate lavorando con modelli finanziari complessi, è possibile che questi precedenti vadano anche a più livelli di profondità.
Questo funziona bene se avete tutte le formule che si riferiscono a celle nello stesso foglio di lavoro. Se è in più fogli di lavoro, questo metodo non è efficace.
Come abilitare/disabilitare i calcoli iterativi in Excel
Quando si ha un riferimento circolare in una cella, prima si ottiene la richiesta di avvertimento come mostrato qui sotto, e se si chiude questa finestra di dialogo, vi darà 0 come risultato nella cella.
Questo perché quando c’è un riferimento circolare, è un ciclo infinito ed Excel non vuole essere coinvolto. Quindi restituisce uno 0.
Ma in alcuni casi, si può effettivamente volere che il riferimento circolare sia attivo e faccia un paio di iterazioni. In tal caso, invece di un ciclo infinito, si può decidere quante volte il ciclo deve essere eseguito.
Questo è chiamato calcolo iterativo in Excel.
Di seguito sono riportati i passi per abilitare e configurare il calcolo iterativo in Excel:
- Fare clic sulla scheda File
- Fare clic su Opzioni. Questo aprirà la finestra di dialogo Opzioni di Excel
- Selezionare Formula nel pannello di sinistra
- Nella sezione Opzioni di calcolo, selezionare la casella ‘Abilita calcolo iterativo’. Qui è possibile specificare le iterazioni massime e il valore massimo di modifica
Questo è tutto! I passi precedenti abilitano il calcolo iterativo in Excel.
Lasciate che vi spieghi rapidamente le due opzioni del calcolo iterativo:
- iterazioni massime: Questo è il numero massimo di volte che volete che Excel calcoli prima di darvi il risultato finale. Quindi se lo specificate come 100, Excel eseguirà il ciclo 100 volte prima di darvi il risultato finale.
- Cambiamento massimo: Questo è il cambiamento massimo, che se non raggiunto tra le iterazioni, il calcolo verrebbe fermato. Per impostazione predefinita, il valore è .001. Più basso è questo valore, più accurato sarebbe il risultato.
Ricorda che più volte vengono eseguite le iterazioni, più tempo e risorse richiede Excel per farlo. Nel caso in cui tu tenga il numero massimo di iterazioni alto, potrebbe portare il tuo Excel a rallentare o a bloccarsi.
Usare deliberatamente i riferimenti circolari
Nella maggior parte dei casi, la presenza di un riferimento circolare nel tuo foglio di lavoro sarebbe un errore. E questo è il motivo per cui Excel mostra una richiesta che dice – “Prova a rimuovere o cambiare questi riferimenti o a spostare le formule in celle diverse.”
Ma ci potrebbero essere alcuni casi specifici in cui è necessario un riferimento circolare in modo da poter ottenere il risultato desiderato.
Uno di questi casi specifici ho già scritto su come ottenere l’orario in una cella in Excel.
Per esempio, supponiamo che vogliate creare una formula in modo che ogni inserimento in una cella nella colonna A, il timestamp appaia nella colonna B (come mostrato sotto):
Mentre potete inserire facilmente un timestamp usando la formula seguente:
=IF(A2<>"",IF(B2<>"",B2,NOW()),"")
Il problema con la formula di cui sopra è che aggiornerebbe tutti i timestamp non appena viene fatta una qualsiasi modifica nel foglio di lavoro o se il foglio di lavoro viene riaperto (poiché la formula NOW è volatile)
Per aggirare questo problema, potete usare un metodo di riferimento circolare. Usa la stessa formula, ma abilita il calcolo iterativo.
Ci sono anche altri casi in cui è desiderabile avere la possibilità di usare il riferimento circolare (puoi trovare un esempio qui).
Spero che tu abbia trovato utile questo tutorial!
Altri tutorial di Excel che potresti trovare utili: