Uno degli avvisi di errore più comuni riscontrati dagli utenti 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, creando un ciclo infinito di calcoli.
Ad esempio, hai due valori nelle celle A1 e A2. Quando la formula=A1 + A2 viene inserita in A2, crea un riferimento circolare; la formula in A2 si ricalcola ripetutamente perché ogni volta che esegue il calcolo, il valore A2 è cambiato.
La maggior parte dei riferimenti circolari sono errori non intenzionali; Excel ti avviserà di questi. Tuttavia, ci sono anche riferimenti circolari intesi, che vengono utilizzati per eseguire calcoli iterativi. Riferimenti circolari involontari nella cartella di lavoro potrebbero causare un calcolo errato della formula.
Pertanto, in questo articolo, tratteremo tutto ciò che è necessario sapere sui riferimenti circolari e su 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, accade quando la tua formula sta cercando di calcolare se stessa.
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 non è possibile. Quindi, riceverai il seguente avviso di riferimento circolare:
Dopo aver ricevuto il messaggio di avviso sopra riportato, puoi fare clic su”Guida”per ulteriori informazioni oppure chiudere la finestra del messaggio facendo clic sul pulsante”OK”o”X”e ottenere”0″come risultato.
Questi cicli di riferimento circolari possono bloccare il calcolo o rallentare le prestazioni del foglio di lavoro. Possono anche portare a una serie di altri problemi, che non sono 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 in una cella fa riferimento direttamente alla propria cella.
Ad esempio, la formula nella cella A2 di seguito si riferisce direttamente alla propria cella (A2).
Quando viene visualizzato il messaggio di avviso, l’utente può fare clic su”OK”, ma il risultato sarà solo”0″.
Riferimento circolare indiretto
Un riferimento circolare indiretto in Excel imposta quando un valore in una formula fa riferimento alla propria cella, non direttamente ma a un certo livello. In altre parole, il riferimento circolare può essere stabilito da due celle che si mirano a vicenda.
Vediamo un semplice esempio.
Ora il valore inizia da A1 che ha valore 20.
Successivamente, la cella C3 fa riferimento 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 di qualsiasi altra cella in A1, verrà creato un avviso di riferimento circolare. Perché la formula in A1 si riferisce alla cella A5, che si riferisce a C3, e la cella C3 si riferisce ad A1, da cui 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 nell’immagine sottostante. Possiamo utilizzare questa funzione per trovare e correggere facilmente i riferimenti circolari.
Come abilitare/disabilitare i riferimenti circolari in Excel
Per impostazione predefinita, i calcoli iterativi sono disabilitati in Excel. I calcoli iterativi sono calcoli ripetuti fino a quando non viene soddisfatta una condizione specifica. Quando è disabilitato, Excel restituisce un prompt di riferimento circolare e restituisce uno 0 come risultato.
Tuttavia, ci sono alcuni rari casi in cui sono necessari riferimenti circolari per eseguire un ciclo. Per utilizzare il riferimento circolare, è necessario abilitare i calcoli iterativi nella cartella di lavoro di Excel e consentirà di eseguire i calcoli. Ora 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 consentirà il riferimento circolare.
Per ottenere ciò nelle precedenti versioni di Excel, segui 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> Calcolo scheda.
Numero massimo di iterazioni e numero massimo di parametri di modifica
Dopo aver abilitato i calcoli iterativi, è possibile controllare i calcoli iterativi, specificando due opzioni disponibili nella sezione Abilita calcolo iterativo come mostrato nello screenshot seguente.
- Numero massimo di iterazioni : specifica quante volte la formula deve essere ricalcolata prima di fornire il risultato finale. Come possiamo vedere qui, il valore predefinito è 100. Ciò significa che Excel ripeterà i calcoli 100 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.
- Modifica massima : determina la variazione massima tra i risultati del calcolo. Riguarda l’accuratezza del risultato. Più piccolo è il numero, più accurato sarà il risultato e più tempo sarà necessario per calcolare il foglio di lavoro.
Dopodiché, non riceverai alcun avviso ogni volta che nel foglio di lavoro è presente un riferimento circolare. Abilita il calcolo interattivo solo quando è assolutamente necessario.
Trova riferimento circolare in Excel
Supponi di avere un set di dati di grandi dimensioni e ricevi l’avviso di riferimento circolare, dovrai comunque scoprire in quale cella si è verificato l’errore per risolverlo. Per trovare riferimenti circolari in Excel, procedere con i seguenti passaggi:
Utilizzo dello strumento di controllo degli errori
Innanzitutto, apri il foglio di lavoro in cui si è verificato il riferimento circolare. Vai alla scheda”Formula”, fai clic sulla freccia accanto allo strumento”Controllo errori”. Quindi posiziona il cursore sull’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 e ti porterà a quella particolare cella per risolvere il problema.
Utilizzo della barra di stato
Un altro modo semplice per trovare il riferimento circolare è guardare la 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 poche cose che dovresti sapere quando lavori con riferimenti circolari:
- La barra di stato non mostrerà l’indirizzo della cella di riferimento circolare quando l’opzione Calcolo iterativo è abilitata, quindi è necessario disattivarla prima di iniziare a controllare la cartella di lavoro per i riferimenti circolari.
- In se il riferimento circolare non viene trovato nel foglio attivo, la barra di stato mostra solo”Riferimenti circolari”senza indirizzo di cella.
- Riceverai un avviso di riferimento circolare solo una volta e, dopo averlo chiuso, non lo sarà mostra di nuovo il prompt la volta successiva.
- Se la cartella di lavoro ha riferimenti circolari, ti mostrerà il prompt ogni volta che lo apri finché non risolverai il riferimento circolare o finché non attivi il calcolo iterativo.
Rimuovi un riferimento circolare in Excel
Trovare riferimenti circolari è facile, ma correggerlo non è così semplice. Sfortunatamente, non c’è nulla in Excel che ti consenta di rimuovere tutti i riferimenti circolari contemporaneamente.
Per correggere i riferimenti circolari, devi trovare ogni riferimento circolare individualmente 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 è riadattare i parametri della formula in modo che non faccia riferimento a se stessa. Ad esempio, modificare la formula in B6 in=SUM (B1: B5) * A5 (cambiando B6 in B5).
Restituirà il risultato del calcolo come”756″.
Nei casi in cui un riferimento circolare di Excel è difficile da trovare, è possibile utilizzare le funzionalità Trace Precedents e Trace Dependents per risalire all’origine e risolverlo uno per uno. La freccia indica quali celle sono interessate dalla cella attiva.
Esistono due metodi di tracciamento che ti consentiranno di eliminare i riferimenti circolari tracciando 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 influenzano il 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 influenzano il valore B6 sono B1: B6 e A5. Come puoi vedere di seguito, è inclusa anche la cella B6, che lo rende un riferimento circolare e fa sì che la formula restituisca 0.
Questo può essere facilmente risolto sostituendo B6 con B5 nell’argomento SUM:=SUM (B1: B5).
Trace Dependents
La funzione trace dependents traccia le celle che dipendono dalla cella attiva. Questa funzione traccia delle linee che indicano quali celle sono interessate dalla cella selezionata. In altre parole, mostra 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 è influenzata da B4. Dipende da B4 perché il suo valore produca risultati. Quindi, Trace dipendente disegna una linea blu da B4 a D3, indicando che D3 dipende da B4.
Uso deliberato di riferimenti circolari in Excel
L’uso deliberato di riferimenti circolari non è consigliato, ma potrebbero esserci alcuni casi specifici in cui è necessario un riferimento circolare in modo da ottenere il risultato desiderato.
Cerchiamo di spiegarlo utilizzando un esempio.
Per cominciare, abilita il”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 che tu abbia deciso di dare una commissione del 2% sul costo totale al tuo agente. Il costo totale verrà calcolato nella cella B6 e la percentuale di commissione (commissione agente) verrà 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.
Immettere la formula per calcolare il costo totale nella cella B6:
=SUM (B1: B4)
Poiché il costo totale include la commissione dell’agente, abbiamo incluso B4 nella formula 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 (inclusa la commissione 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.
Il calcolo iterativo è solitamente disabilitato per impostazione predefinita. Se non l’hai attivato e quando inserisci la formula in B4, verrà creato un riferimento circolare. Excel emetterà l’avviso e quando fai clic su”OK”, verrà visualizzata la freccia di traccia.
Questo è tutto. Questo era tutto ciò che c’era da sapere sui riferimenti circolari in Excel.
