Come trovare riferimenti circolari in Excel

Quando si lavora con Microsoft Excel , è possibile incontrare occasionalmente ciò che è noto come riferimento circolare. Questo è quando una formula in una cella fa riferimento a se stessa, causando un ciclo infinito nel processo di calcolo.

Per trovare riferimenti circolari in Excel, utilizzare lo strumento "Controllo errori" nella scheda "Formule" per identificare le celle con una dipendenza circolare.

Questo articolo mostra come trovare riferimenti circolari in Excel e correggerli. Imparerai anche come prevenirli quando lavori con fogli di lavoro grandi o complessi.

Sommario

• Cosa sono i riferimenti circolari
• La finestra del messaggio di errore del riferimento circolare
• Come trovare riferimenti circolari in Excel
• Come trovare riferimenti circolari con la barra di stato di Excel
• Come trovare riferimenti circolari nascosti in Excel
  • Come utilizzare lo strumento dipendente dalla traccia
  • Strumento Traccia precedenti
• 2 modi per rimuovere i riferimenti circolari in Excel
  • 2. Usa formule alternative
• 5 suggerimenti per prevenire i riferimenti circolari
  • 1. Fai attenzione quando selezioni gli intervalli
  • 2. Pianifica attentamente le tue formule
  • 3. Usa formule alternative
  • 4. Suddividi le formule complesse in parti più piccole
  • 5. Risolvi i problemi man mano che procedi
• Come abilitare il calcolo iterativo e perché preoccuparsi?

Cosa sono i riferimenti circolari

I riferimenti circolari si verificano in Excel quando il calcolo di una formula in una cella dipende dal valore della cella. Questo crea un loop e porta a risultati errati o imprevedibili.

Questo è più facile da capire con un semplice esempio.

Supponiamo di avere due numeri nelle celle A1 e B1. Si desidera utilizzare una formula nella cella A3 che somma i due numeri. La formula nella cella A3 dovrebbe essere simile a questa:

=LA1 + SI1

Tuttavia, se commetti un errore e includi C1 nei calcoli, ora hai un riferimento circolare:

=LA1 + SI1 + DO1

Questo può essere un ovvio problema da evitare quando si ha a che fare con due celle. Tuttavia, quando si lavora con grandi intervalli di celle, gli errori diventano più probabili.

Per fortuna, Excel è progettato per gestire tali situazioni visualizzando un messaggio di avviso quando viene rilevato un riferimento circolare.

Tuttavia, in alcuni casi, potresti riscontrare un riferimento circolare senza ricevere alcun avviso. Pertanto, è importante sapere come trovare e risolvere questi problemi manualmente.

La finestra del messaggio di errore del riferimento circolare

Per impostazione predefinita, Excel monitora i riferimenti circolari mentre inserisci le formule nelle celle. Quando si preme Invio in una cella con un problema, una finestra pop-up visualizzerà il messaggio di errore di riferimento circolare.

Questo il testo esplicativo:

Esistono uno o più riferimenti circolari in cui una formula fa riferimento alla propria cella direttamente o indirettamente. Ciò potrebbe causare calcoli errati. Prova a rimuovere o modificare questi riferimenti o a spostare le formule in celle diverse.

Quando vedi questa finestra di dialogo, devi fare clic su "OK" e utilizzare i metodi in questo articolo per risolvere il problema.

Tuttavia, non è necessario correggere immediatamente l'errore. Puoi continuare a lavorare con il foglio di calcolo con l'intenzione di risolvere il problema in un secondo momento.

In tal caso, devi sapere come trovare i riferimenti circolari in Excel dopo aver eliminato la casella di avviso.

Come trovare riferimenti circolari in Excel

Excel ha uno strumento di controllo degli errori integrato che trova diversi tipi di errori, inclusi i riferimenti circolari. Si accede allo strumento nella scheda Formule.

Questa immagine mostra la posizione all'interno del gruppo Formula Auditing.

Ora sai come trovare lo strumento, ecco i passaggi dettagliati per usarlo:

1. Vai alla scheda Formule.

1. Fare clic sull'elenco a discesa Controllo errori nel gruppo Controllo formule.

2. Selezionare "Riferimenti circolari" dall'elenco a discesa.

3. Fai clic sul collegamento per spostare il cursore sulla prima cella con un problema.

L'immagine seguente mostra che lo strumento ha trovato un errore circolare nella cella G1. La cella visualizzata è un collegamento ipertestuale.

Quando fai clic sul collegamento, il cursore si sposta sulla cella. Questo è molto utile con un grande volume di dati.

Una volta risolto il primo riferimento circolare, puoi tornare allo strumento Controllo errori per trovare quello successivo sul foglio.

Utilizzare i passaggi nelle sezioni successive di questo articolo per risolvere il problema e utilizzare lo strumento per passare al problema successivo.

Se non ci sono più problemi circolari, la voce di menu "Riferimenti circolari" nel menu a discesa sarà disattivata. Non hai altro lavoro da fare.

Come trovare riferimenti circolari con la barra di stato di Excel

Un altro modo per individuare un riferimento circolare è guardare la barra di stato nella parte inferiore del foglio di lavoro di Excel.

Quando sono presenti uno o più problemi nel foglio di lavoro, la barra di stato visualizza la posizione di un riferimento di cella del problema.

Ad esempio, se c'è un riferimento circolare nella cella C1 e non ci sono altri problemi con il foglio di calcolo, la barra di stato riporterà: "Riferimenti circolari: C1".

Questa immagine mostra dove vedere il display.

Come trovare riferimenti circolari nascosti in Excel

I riferimenti circolari nascosti in Excel sono riferimenti circolari che non vengono rilevati immediatamente dalle funzionalità di errore integrate. In altre parole, Excel non visualizza l'avviso standard.

Si verificano quando una cella fa riferimento a un'altra cella indirettamente, attraverso una serie di celle intermedie, e alla fine fa riferimento alla cella originale.

Excel dispone di due strumenti che consentono di tracciare le relazioni e analizzare questi problemi:

1. Strumento Trace Dependents

2. Strumento Traccia precedenti

Come utilizzare lo strumento dipendente dalla traccia

Gli utenti esperti di Excel sfruttano lo strumento Trace Dependents per analizzare questi problemi e correggere i riferimenti circolari. Scegliendo una o più celle e utilizzando questo strumento, Excel ti mostrerà le celle dipendenti.

Lo strumento è disponibile nella sezione Formula Auditing della scheda Formule.

Per utilizzare lo strumento, attenersi alla seguente procedura:

1. Fare clic sulla cella o sulle celle per le quali si desidera tracciare i dipendenti.

2. Vai alla scheda "Formule" nella barra multifunzione di Excel.

3. Fare clic sul pulsante "Trace Dependents" nel gruppo "Formula Auditing".

4. Esamina la freccia o le frecce del tracciante che mostrano le dipendenze.

Excel mostra le frecce dal valore della cella attiva a tutte le celle che dipendono da esso. La freccia blu nell'immagine qui sotto mostra che la cella D6 ha una dipendenza dalla cella B2.

Strumento Traccia precedenti

I precedenti sono l'opposto dei dipendenti. Lo strumento Trace Precedents ti consente di scegliere una cella con una formula e vedere tutte le celle da cui dipende, ad esempio i precedenti.

Lo strumento è disponibile nella sezione Formula Auditing della scheda Formule.

Per utilizzare lo strumento, attenersi alla seguente procedura:

1. Fare clic sulla cella contenente la formula che si desidera esaminare.

2. Vai alla scheda Formula nella barra multifunzione di Excel.

3. Fare clic sul pulsante "Trace Precedents" nel gruppo "Formula Auditing".

4. Rivedere le frecce di traccia visualizzate in Excel.

Le frecce blu nell'immagine qui sotto mostrano che la cella D6 dipende dall'intervallo di celle A2:B5.

L'utilizzo di entrambi gli strumenti consente di trovare riferimenti circolari di Excel che non sono immediatamente evidenti. Il prossimo passo è risolvere il problema nella cella selezionata. È quindi possibile continuare a cercare lo stesso problema in altre celle.

2 modi per rimuovere i riferimenti circolari in Excel

Esistono due metodi principali per risolvere i riferimenti circolari:

• rompendo il riferimento circolare

• utilizzando formule alternative

Spieghiamo entrambi i modi.

1. Rompere il riferimento circolare

Rompere il riferimento circolare in Excel è un modo efficiente per risolvere il problema. Ciò richiede di identificare le celle coinvolte nel riferimento circolare e di modificare le loro formule di conseguenza.

Per fare ciò, segui i passaggi descritti in precedenza per trovare l'indirizzo della cella.

Dovresti quindi esaminare la formula nella barra della formula. Cerca un riferimento alla cella stessa.

Nel nostro primo esempio di riferimento circolare, la formula di addizione nella cella C1 includeva un riferimento alla cella stessa:

=LA1 + SI1 + DO1

Questo può essere risolto rimuovendo l'aggiunta finale. Una volta risolto questo problema, si passa a trovare altri riferimenti circolari e ad affrontarli.

2. Usa formule alternative

Alcune delle cause più comuni di riferimenti circolari sono la somma o la media delle celle. L'errore è includere la cella dei risultati nella formula.

Nell'esempio precedente, la formula utilizzava operatori di addizione per calcolare la somma di un intervallo di celle. Il metodo alternativo consiste nell'utilizzare la funzione SOMMA di Excel.

Per correggere l'esempio, sostituisci la formula con:

=SOMMA(A1:B1)

Allo stesso modo, puoi calcolare la media di un intervallo con una formula come =(A1+B1)/2. Tuttavia, è meno probabile che la funzione MEDIA porti a un errore.

=MEDIA(A1:B1)

5 suggerimenti per prevenire i riferimenti circolari

Per evitare riferimenti circolari in Microsoft Excel, è essenziale avere una chiara comprensione di come le formule interagiscono tra loro nel foglio Excel.

Identificando le relazioni tra le celle ed essendo consapevoli di come queste connessioni interagiscono, puoi evitare di creare formule circolari.

Ecco i nostri cinque migliori consigli per aiutarti a prevenire i riferimenti circolari.

1. Fai attenzione quando selezioni gli intervalli

Nella mia esperienza, una delle cause più comuni di riferimenti circolari è quando gli utenti selezionano rapidamente gli intervalli da includere nelle loro formule.

Quando scorri verticalmente e/o orizzontalmente per raggruppare le celle, è facile selezionare un'altra cella adiacente all'intervallo corretto. Se quella cella è anche la cella dei risultati, hai inavvertitamente creato un riferimento circolare.

Questo errore viene in genere commesso quando si lavora con grandi insiemi di dati, come l'esecuzione di test chi-quadrato. La soluzione è fare attenzione con la selezione della gamma.

2. Pianifica attentamente le tue formule

Prima di inserire le formule, pensa alla logica alla base dei tuoi calcoli e al modo in cui dovrebbero scorrere tra le celle.

Puoi mappare i calcoli in anticipo utilizzando strumenti di creazione di diagrammi o una lavagna.

Questo può aiutarti a identificare eventuali loop che potrebbero portare a riferimenti circolari.

3. Usa formule alternative

Alcune funzioni, come INDIRECT o OFFSET , possono creare inavvertitamente riferimenti circolari se utilizzate in modo improprio.

Prendere in considerazione l'utilizzo di funzioni alternative che forniscono funzionalità simili senza il rischio di riferimenti circolari.

4. Suddividi le formule complesse in parti più piccole

Invece di creare un'unica formula complessa con più funzioni nidificate, prendi in considerazione la possibilità di suddividere i tuoi calcoli in parti più piccole e più gestibili.

Ciò non solo semplifica la comprensione e la manutenzione della cartella di lavoro, ma riduce anche le possibilità di introdurre un riferimento circolare.

5. Risolvi i problemi man mano che procedi

Quando si lavora con cartelle di lavoro grandi o complicate, è anche essenziale monitorare i riferimenti circolari. Invece di ignorare i problemi fino a quando non è stato assemblato un ampio set di dati e formule, è meglio correggere ogni errore non appena si presenta.

Puoi scegliere di ignorare un messaggio di avviso di riferimento circolare per un breve periodo. Tuttavia, dovresti utilizzare la funzione Controllo errori per raggiungere i riferimenti circolari esistenti prima che sia troppo lungo.

Ciò li rende più facili da correggere e previene i loro effetti sulla cartella di lavoro.

Come abilitare il calcolo iterativo e perché preoccuparsi?

Di solito vuoi evitare riferimenti circolari nel tuo foglio di lavoro. Tuttavia, ci sono alcuni scenari in cui sono necessari.

Ad esempio, alcuni calcoli ingegneristici o scientifici devono utilizzare il risultato di un calcolo precedente. Questa è nota come soluzione iterativa.

Ciascun ciclo affina i risultati di un calcolo fino al raggiungimento di una soglia o di un obiettivo specifico.

In questi casi, è essenziale abilitare i calcoli iterativi nelle impostazioni di Excel per consentire i riferimenti circolari previsti. Ciò consente al software di ripetere i calcoli un numero specificato di volte, convergendo a una soluzione.

Segui questi passaggi per far funzionare un riferimento circolare nel tuo file Excel:

1. Fare clic sulla scheda File.

2. Seleziona Opzioni.

3. Scegli la categoria Formule.

4. Nella sezione "Opzioni di calcolo", seleziona la casella "Abilita calcolo iterativo".

5. Modificare il numero nella casella delle iterazioni massime o lasciare il valore predefinito di 100.

6. Utilizzare la casella di modifica massima per ridurre le iterazioni in base alle proprie esigenze.