Durante l'utilizzo delle formule di Excel, a volte potresti visualizzare il seguente messaggio di avviso.
Questo prompt 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 questo problema di riferimento circolare e di ordinarlo.
In questo tutorial, tratterò tutto ciò che devi sapere sui riferimenti circolari e anche come trovare e rimuovere riferimenti circolari in Excel.
Quindi iniziamo!
Che cos'è il riferimento circolare in Excel?
In parole semplici, un riferimento circolare si verifica 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 di avere il set di dati nella cella A1: A5 e di utilizzare la formula seguente nella cella A6:
=SOMMA(A1:A6)
Questo ti darà un avviso di riferimento circolare.
Questo perché vuoi sommare i valori nella cella A1:A6 e il risultato dovrebbe essere nella cella A6.
Questo crea un ciclo poiché 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 il prompt di avviso di riferimento circolare è così gentile da dirti che esiste nel tuo foglio di lavoro, non ti dice dove sta succedendo e quali riferimenti di cella lo stanno causando.
Quindi, se stai cercando di trovare e gestire riferimenti circolari nel foglio di lavoro, devi conoscere un modo per trovarli in qualche modo.
Di seguito sono riportati i passaggi per trovare un riferimento circolare in Excel:
- Attiva il foglio di lavoro che ha il riferimento circolare
- Fare clic sulla scheda Formule
- Nel gruppo Modifica formula, fare clic sull'icona a discesa Controllo errori (piccola freccia rivolta verso il basso a destra)
- Passa il cursore sopra l'opzione Riferimenti circolari. Ti mostrerà la cella che ha un riferimento circolare nel foglio di lavoro
- Fai clic sull'indirizzo della cella (che viene visualizzato) e ti porterà a quella cella nel foglio di lavoro.
Una volta risolto il problema, puoi nuovamente seguire gli stessi passaggi sopra e mostrerà più riferimenti di cella che hanno il riferimento circolare. Se non ce n'è, 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 esso, ti mostrerà il testo Riferimento circolare insieme all'indirizzo della cella.
Ci sono alcune cose che devi sapere quando lavori con i riferimenti circolari:
- Nel caso in cui sia abilitato il calcolo iterativo (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 sia nel foglio attivo (ma in altri fogli nella stessa cartella di lavoro), mostrerà solo il riferimento circolare e non l'indirizzo della cella
- Nel caso in cui si riceva una volta un messaggio di avviso di riferimento circolare e lo si scarti, non verrà più visualizzato il messaggio la volta successiva.
- Se apri una cartella di lavoro che ha il riferimento circolare, ti mostrerà il prompt non appena si apre la cartella di lavoro.
Come rimuovere un riferimento circolare in Excel?
Una volta che hai identificato che ci sono riferimenti circolari nel tuo foglio, è il momento di rimuoverli (a meno che tu non voglia che siano lì per un motivo).
Sfortunatamente, non è così semplice come premere il tasto Canc. Poiché dipendono dalle formule e ogni formula è diversa, è necessario analizzarla caso per caso.
Nel caso in cui si tratti solo di avere il riferimento di cella che causa il problema per errore, puoi semplicemente correggere regolando il riferimento.
Ma a volte, non è così semplice.
Il riferimento circolare può anche essere causato sulla base di più celle che si alimentano l'una nell'altra a molti livelli.
Lascia che ti mostri un esempio.
Sotto c'è un riferimento circolare nella cella C6, ma non è solo un semplice caso di autoreferenzialità. È multilivello in cui anche le celle che utilizza nei calcoli fanno riferimento l'una all'altra.
- Le formule nella cella A6 sono =SOMMA(A1:A5)+C6
- La formula è la cella C1 è =A6*0.1
- La formula nella cella C6 è =A6+C1
Nell'esempio sopra, 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 piuttosto difficili da capire e forse lontani nello stesso foglio di lavoro o addirittura sparsi su più fogli di lavoro.
In tal caso, esiste un modo per identificare le celle che causano il riferimento circolare e quindi trattarle.
È usando l'opzione Traccia precedenti.
Di seguito sono riportati i passaggi per utilizzare i precedenti di traccia per trovare celle che alimentano la cella che ha il riferimento circolare:
- Seleziona la cella che ha il riferimento circolare
- Fare clic sulla scheda Formule
- Fare clic su Traccia precedenti
I passaggi precedenti ti mostrerebbero le frecce blu che ti diranno quali celle stanno alimentando nella formula nella cella selezionata. In questo modo, puoi ispezionare le formule e le celle ed eliminare il riferimento circolare.
Nel caso in cui lavori con modelli finanziari complessi, è possibile che anche questi precedenti vadano in profondità a più livelli.
Funziona bene se hai tutte le formule che fanno riferimento alle 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 hai un riferimento circolare in una cella, per prima cosa ricevi il messaggio di avviso come mostrato di seguito e, se chiudi questa finestra di dialogo, ti darà 0 come risultato nella cella.
Questo perché quando c'è un riferimento circolare, è un ciclo infinito ed Excel non vuole essere coinvolto in esso. Quindi restituisce uno 0.
Ma in alcuni casi, potresti voler effettivamente che il riferimento circolare sia attivo e che esegua un paio di iterazioni. In tal caso, invece di un ciclo infinito, e puoi decidere quante volte deve essere eseguito il ciclo.
Questo è chiamato calcolo iterativo in Excel.
Di seguito sono riportati i passaggi per abilitare e configurare i calcoli iterativi in Excel:
- Fare clic sulla scheda File
- Fare clic su Opzioni. Questo aprirà la finestra di dialogo Opzioni di Excel
- Seleziona Formula nel riquadro di sinistra
- Nella sezione Opzioni di calcolo, seleziona la casella "Abilita calcolo iterativo". Qui puoi specificare le iterazioni massime e il valore di modifica massimo
Questo è tutto! I passaggi precedenti consentirebbero il calcolo iterativo in Excel.
Permettetemi anche di spiegare rapidamente le due opzioni nel calcolo iterativo:
- Iterazioni massime: Questo è il numero massimo di volte che vuoi che Excel calcoli prima di darti il risultato finale. Quindi, se lo specifichi come 100, Excel eseguirà il ciclo 100 volte prima di darti il risultato finale.
- Cambio massimo: Questa è la variazione massima che, se non raggiunta tra le iterazioni, il calcolo verrebbe interrotto. Per impostazione predefinita, il valore è .001. Più basso è questo valore, più accurato sarà il risultato.
Ricorda che più volte vengono eseguite le iterazioni, più tempo e risorse impiegano Excel per farlo. Nel caso in cui mantieni alte le iterazioni massime, il tuo Excel potrebbe rallentare o bloccarsi.
Nota: quando i calcoli iterativi sono abilitati, Excel non mostrerà il prompt di avviso di riferimento circolare e ora lo mostrerà anche nella barra di stato.Utilizzo deliberato di riferimenti circolari
Nella maggior parte dei casi, la presenza di un riferimento circolare nel foglio di lavoro sarebbe un errore. Ed è per questo che Excel ti mostra un prompt che dice: "Prova a rimuovere o modificare questi riferimenti o spostare le formule in celle diverse".
Ma potrebbero esserci alcuni casi specifici in cui è necessario un riferimento circolare in modo da poter ottenere il risultato desiderato.
Uno di questi casi specifici l'ho già scritto ottenendo il timestamp in una cella in una cella in Excel.
Ad esempio, supponiamo di voler creare una formula in modo che ogni volta che viene inserita una voce in una cella nella colonna A, il timestamp appaia nella colonna B (come mostrato di seguito):
Mentre puoi inserire facilmente inserisci un timestamp usando la formula seguente:
=SE(A2"",SE(B2"",B2,ORA()),"")
Il problema con la formula sopra è che aggiornerebbe tutti i timestamp non appena viene apportata una modifica nel foglio di lavoro o se il foglio di lavoro viene riaperto (poiché la formula ORA è volatile)
Per aggirare questo problema, puoi utilizzare un metodo di riferimento circolare. Usa la stessa formula, ma abilita il calcolo iterativo.
Ci sono anche altri casi in cui si desidera avere la possibilità di utilizzare il riferimento circolare (puoi trovare un esempio qui).
Nota: sebbene possano esserci alcuni casi in cui è possibile utilizzare il riferimento circolare, ritengo che sia meglio evitare di utilizzarlo. I riferimenti circolari possono anche incidere sulle prestazioni della cartella di lavoro e rallentarla. Nei rari casi in cui ne hai bisogno, preferisco sempre usare i codici VBA per portare a termine il lavoro.Spero che tu abbia trovato utile questo tutorial!
Altri tutorial di Excel che potresti trovare utili:
- #RIF! Errore in Excel; Come correggere l'errore di riferimento!
- Gestione degli errori VBA di Excel
- Usa IFERROR con VLOOKUP per sbarazzarti degli errori #N/A
- Come fare riferimento a un altro foglio o cartella di lavoro in Excel (con esempi)
- Riferimenti di cella assoluti, relativi e misti in Excel
