Uno degli avvisi di errore più comuni che gli utenti incontrano in Excel è il "Riferimento circolare". Migliaia di utenti hanno lo stesso problema e si verifica quando una formula fa riferimento alla propria cella direttamente o indirettamente, causando un ciclo infinito di calcoli.
Ad esempio, hai due valori nelle celle B1 e B2. Quando si inserisce la formula =B1+B2 in B2, si crea un riferimento circolare; la formula in B2 si ricalcola ripetutamente perché ogni volta che calcola, il valore B2 è cambiato.
La maggior parte dei riferimenti circolari sono errori non intenzionali; Excel ti avviserà di questi. Tuttavia, sono previsti anche riferimenti circolari, che vengono utilizzati per eseguire calcoli iterativi. Riferimenti circolari involontari nel foglio di lavoro potrebbero causare il calcolo errato della formula.
Pertanto, in questo articolo, spiegheremo tutto ciò che devi sapere sui riferimenti circolari e come trovare, correggere, rimuovere e utilizzare i riferimenti circolari in Excel.
Come trovare e gestire riferimenti circolari in Excel
Quando si lavora con Excel, a volte si verificano errori di riferimento circolare che si verificano quando si immette una formula che include la cella in cui risiede la formula. Fondamentalmente, succede quando la tua formula sta cercando di calcolarsi.
Ad esempio, hai una colonna di numeri nella cella A1:A4 e stai utilizzando la funzione SOMMA (=SOMMA(A1:A5)) nella cella A5. La cella A5 si riferisce direttamente alla propria cella, il che è sbagliato. Quindi, otterrai il seguente avviso di riferimento circolare:
Una volta ricevuto il messaggio di avviso sopra, puoi fare clic sul pulsante "Aiuto" per saperne di più sull'errore o chiudere la finestra del messaggio di errore facendo clic sul pulsante "OK" o "X" e ottenere "0" come risultato.
A volte i cicli di riferimento circolari possono causare l'arresto anomalo del calcolo o rallentare le prestazioni del foglio di lavoro. Il riferimento circolare può anche portare a una serie di altri problemi, che non saranno immediatamente evidenti. Quindi, è meglio evitarli.
Riferimenti circolari diretti e indiretti
I riferimenti circolari possono essere classificati in due tipi: riferimenti circolari diretti e riferimenti circolari indiretti.
Riferimento diretto
Un riferimento circolare diretto è piuttosto semplice. Il messaggio di avviso di riferimento circolare diretto viene visualizzato quando la formula fa riferimento direttamente alla propria cella.
Nell'esempio seguente, la formula nella cella A2 fa riferimento direttamente alla propria cella (A2).
Una volta visualizzato il messaggio di avviso, puoi fare clic su "OK", ma risulterà solo in "0".
Riferimento circolare indiretto
Un riferimento circolare indiretto in Excel si verifica quando un valore in una formula fa riferimento alla propria cella, ma non direttamente. In altre parole, il riferimento circolare può essere formato da due celle che fanno riferimento l'una all'altra.
Spieghiamoci con questo semplice esempio.
Ora il valore inizia da A1 che ha il valore 20.
Successivamente, la cella C3 si riferisce alla cella A1.
Quindi, la cella A5 si riferisce alla cella C3.
Ora sostituisci il valore 20 nella cella A1 con la formula come mostrato di seguito. Ogni altra cella dipende dalla cella A1. Quando si utilizza un riferimento a qualsiasi altra cella della formula precedente in A1, verrà generato un avviso di riferimento circolare. Perché, la formula in A1 fa riferimento alla cella A5, che fa riferimento a C3 e la cella C3 fa riferimento ad A1, quindi il riferimento circolare.
Quando si fa clic su "OK", si ottiene un valore 0 nella cella A1 ed Excel crea una linea collegata che mostra i precedenti della traccia e i dipendenti della traccia come mostrato di seguito. Possiamo usare questa funzione per trovare e correggere/rimuovere facilmente i riferimenti circolari.
Come abilitare/disabilitare i riferimenti circolari in Excel
Per impostazione predefinita, i calcoli iterativi sono disattivati (disabilitati) in Excel. I calcoli iterativi sono calcoli ripetitivi finché non soddisfa una condizione specifica. Quando è disabilitato, Excel mostra un messaggio di riferimento circolare e restituisce uno 0 come risultato.
Tuttavia, a volte sono necessari riferimenti circolari per calcolare un ciclo. Per utilizzare il riferimento circolare, devi abilitare i calcoli iterativi nel tuo Excel e ti consentirà di eseguire i tuoi calcoli. Ora, lascia che ti mostriamo come abilitare o disabilitare i calcoli iterativi.
In Excel 2010, Excel 2013, Excel 2016, Excel 2019 e Microsoft 365, vai alla scheda "File" nell'angolo in alto a sinistra di Excel, quindi fai clic su "Opzioni" nel riquadro di sinistra.
Nella finestra Opzioni di Excel, vai alla scheda "Formula" e seleziona la casella di controllo "Abilita calcolo iterativo" nella sezione "Opzioni di calcolo". Quindi fare clic su "OK" per salvare le modifiche.
Ciò consentirà il calcolo iterativo e quindi il riferimento circolare.
Per ottenere ciò nelle versioni precedenti di Excel, seguire questi passaggi:
- In Excel 2007, fare clic sul pulsante Office > Opzioni di Excel > Formule > Area di iterazione.
- In Excel 2003 e versioni precedenti, è necessario accedere a Menu > Strumenti > Opzioni > scheda Calcolo.
Iterazioni massime e parametri di modifica massima
Una volta abilitati i calcoli iterativi, è possibile controllare i calcoli iterativi, specificando due opzioni disponibili nella sezione Abilita calcolo iterativo come mostrato nello screenshot seguente.
- Iterazioni massime – Questo numero specifica quante volte la formula deve essere ricalcolata prima di darti il risultato finale. Il valore predefinito è 100. Se lo cambi in "50", Excel ripeterà i calcoli 50 volte prima di darti il risultato finale. Ricorda che maggiore è il numero di iterazioni, maggiori sono le risorse e il tempo necessari per il calcolo.
- Cambio massimo – Determina la variazione massima tra i risultati del calcolo. Questo valore determina l'accuratezza del risultato. Più piccolo è il numero, più accurato sarà il risultato e più tempo ci vorrà per calcolare il foglio di lavoro.
Se l'opzione dei calcoli iterativi è abilitata, non riceverai alcun avviso ogni volta che è presente un riferimento circolare nel foglio di lavoro. Abilita il calcolo interattivo solo quando è assolutamente necessario.
Trova riferimento circolare in Excel
Supponiamo che tu abbia un set di dati di grandi dimensioni e che tu abbia ricevuto l'avviso di riferimento circolare, dovrai comunque scoprire dove (in quale cella) si è verificato l'errore per risolverlo. Per trovare riferimenti circolari in Excel, segui questi passaggi:
Utilizzo dello strumento di controllo degli errori
Innanzitutto, apri il foglio di lavoro in cui è avvenuto il riferimento circolare. Vai alla scheda "Formula", fai clic sulla freccia accanto allo strumento "Controllo errori". Quindi passa il cursore sopra l'opzione "Riferimenti circolari", Excel ti mostrerà l'elenco di tutte le celle coinvolte nel riferimento circolare come mostrato di seguito.
Fai clic sull'indirizzo di cella che desideri nell'elenco e ti porterà a quell'indirizzo di cella per risolvere il problema.
Utilizzo della barra di stato
Puoi trovare il riferimento circolare anche nella barra di stato. Sulla barra di stato di Excel, ti mostrerà l'ultimo indirizzo di cella con un riferimento circolare, come "Riferimenti circolari: B6" (vedi screenshot sotto).
Ci sono alcune cose che dovresti sapere quando gestisci i riferimenti circolari:
- La barra di stato non mostrerà l'indirizzo della cella di riferimento circolare quando l'opzione Calcolo iterativo è abilitata, quindi è necessario disabilitarla prima di iniziare a guardare la cartella di lavoro per i riferimenti circolari.
- Nel caso in cui il riferimento circolare non venga trovato nel foglio attivo, la barra di stato mostra solo "Riferimenti circolari" senza indirizzo di cella.
- Riceverai solo una richiesta di riferimento circolare una volta e dopo aver fatto clic su "OK", non mostrerà più la richiesta la volta successiva.
- Se la tua cartella di lavoro ha riferimenti circolari, ti mostrerà il prompt ogni volta che la apri finché non risolvi il riferimento circolare o finché non attivi il calcolo iterativo.
Rimuovere un riferimento circolare in Excel
Trovare i riferimenti circolari è facile, ma risolverli non è così semplice. Sfortunatamente, non esiste alcuna opzione in Excel che ti consenta di rimuovere tutti i riferimenti circolari contemporaneamente.
Per correggere i riferimenti circolari, devi trovare ogni riferimento circolare singolarmente e provare a modificarlo, rimuovere del tutto la formula circolare o sostituirla con un'altra.
A volte, in formule semplici, tutto ciò che devi fare è riaggiustare i parametri della formula in modo che non faccia riferimento a se stessa. Ad esempio, cambia la formula in B6 in =SUM(B1:B5)*A5 (cambia B6 in B5).
Restituirà il risultato del calcolo come "756".
Nei casi in cui è difficile trovare un riferimento circolare di Excel, è possibile utilizzare le funzionalità Trace Precedenti e Trace Dependents per risalire all'origine e risolverlo uno per uno. La freccia mostra quali celle sono interessate dalla cella attiva.
Esistono due metodi di tracciamento che possono aiutarti a eliminare i riferimenti circolari mostrando le relazioni tra formule e celle.
Per accedere ai metodi di tracciamento, vai alla scheda "Formule", quindi fai clic su "Traccia precedenti" o "Traccia dipendenti" nel gruppo Controllo formula.
Traccia precedenti
Quando selezioni questa opzione, tiene traccia delle celle che influiscono sul valore della cella attiva. Disegna una linea blu che indica quali celle influenzano la cella corrente. Il tasto di scelta rapida per utilizzare i precedenti di traccia è Alt + T U T.
Nell'esempio seguente, la freccia blu mostra che le celle che influiscono sul valore B6 sono B1: B6 e A5. Come puoi vedere di seguito, anche la cella B6 fa parte della formula, il che la rende un riferimento circolare e fa sì che la formula restituisca "0" come risultato.
Questo può essere facilmente risolto sostituendo B6 con B5 nell'argomento di SUM: =SUM(B1:B5).
Dipendenti dalla traccia
La funzionalità di traccia dipendenti traccia le celle che dipendono dalla cella selezionata. Questa funzione disegna una linea blu che indica quali celle sono interessate dalla cella selezionata. Vale a dire, visualizza quali celle contengono formule che fanno riferimento alla cella attiva. Il tasto di scelta rapida per utilizzare i dipendenti è Alt + T U D.
Nell'esempio seguente, la cella D3 è interessata da B4. Dipende da B4 per il suo valore per produrre risultati. Quindi, traccia dipendente disegna una linea blu da B4 a D3, indicando che D3 dipende da B4.
Utilizzo deliberato di riferimenti circolari in Excel
L'utilizzo di riferimenti circolari deliberatamente non è consigliato, ma potrebbero esserci alcuni rari casi in cui è necessario un riferimento circolare in modo da poter ottenere l'output desiderato.
Spieghiamolo usando un esempio.
Per cominciare, abilita "Calcolo iterativo" nella cartella di lavoro di Excel. Dopo aver abilitato il calcolo iterativo, puoi iniziare a utilizzare i riferimenti circolari a tuo vantaggio.
Supponiamo che tu stia acquistando una casa e tu voglia dare una commissione del 2% sul costo totale della casa al tuo agente. Il costo totale sarà calcolato nella cella B6 e la percentuale di commissione (commissione agente) è calcolata in B4. La commissione viene calcolata dal costo totale e il costo totale include la commissione. Poiché le celle B4 e B6 dipendono l'una dall'altra, crea un riferimento circolare.
Inserisci la formula per calcolare il costo totale nella cella B6:
=SOMMA(B1:B4)
Poiché il costo totale include la commissione dell'agente, abbiamo incluso B4 nella formula di cui sopra.
Per calcolare la Commissione dell'Agente del 2%, inserisci questa formula in B4:
=B6*2%Ora la formula nella cella B4 dipende dal valore di B6 per calcolare il 2% della commissione totale e la formula in B6 dipende da B4 per calcolare il costo totale (compresa la commissione dell'agente), da cui il riferimento circolare.
Se il calcolo iterativo è abilitato, Excel non ti darà un avviso o uno 0 nel risultato. Invece, il risultato delle celle B6 e B4 verrà calcolato come mostrato sopra.
L'opzione dei calcoli iterativi è solitamente disabilitata per impostazione predefinita. Se non l'hai acceso e quando inserisci la formula in B4 che creerà un riferimento circolare. Excel emetterà l'avviso e quando fai clic su "OK", verrà mostrata la freccia del tracciante.
Questo è tutto. Questo era tutto ciò che devi sapere sui riferimenti circolari in Excel.