Come trovare riferimenti circolari in Excel per evitare dati errati

Hai visto l'errore di riferimento circolare su un foglio Excel e ti sei chiesto se il foglio di lavoro è rotto? Fidati di me! Non sei il primo ad affrontare questo errore di Excel. Centinaia e migliaia di utenti di Excel riscontrano ogni giorno errori di riferimento circolari. Da non preoccuparsi! Ecco come trovare riferimenti circolari in Excel e risolverli.

Essendo un avido utente di Excel, raramente digiti gli stessi dati in molte celle. Invece, si importa un set di dati dall'origine e si utilizza il riferimento di cella per creare formule. Pertanto, crei un foglio di calcolo dinamico.

Quando qualcuno modifica i valori di cella dei riferimenti di cella, il foglio di lavoro si aggiorna automaticamente. Questo si chiama automazione dell'analisi dei dati e gli analisti di dati adorano giocare con i riferimenti di cella.

Tuttavia, il problema si presenta quando digiti intenzionalmente o accidentalmente l'indirizzo della cella in cui stai attualmente eseguendo un calcolo. Oppure, quando crei molte formule in tutto il foglio di lavoro, una cella contenente una formula appare indirettamente in un'altra formula.

In queste e molte altre situazioni simili si ottiene l'errore di riferimento circolare in Excel. Continua a leggere per scoprire cos'è il riferimento circolare, i suoi motivi, perché evitarlo e, soprattutto, come trovare riferimenti circolari in Excel per eliminare definitivamente queste anomalie.

Che cos'è un riferimento circolare in Excel?

Excel rileva un errore di riferimento circolare quando rileva che una o più funzioni del foglio di lavoro fanno riferimento all'indirizzo di cella in cui sono state create le relative funzioni.

Potrebbe sembrarti confuso, quindi pensa a quanto potrebbe essere grave per l'app Excel. In breve, non vuoi mai inserire l'indirizzo della cella in una formula dove lo stai creando. Guarda il seguente esempio per un chiarimento visivo:

Stai cercando di moltiplicare 10 dalla cella B1 e inserire il risultato nella stessa cella B1 .

Ora, nello scenario sopra, se premi OK , Excel chiuderà l'errore e mostrerà zero come risultato calcolato. Questo è solo un semplice esempio.

Che cos'è il riferimento circolare in Excel

Ora, immagina che ci siano centinaia di formule in un foglio di lavoro di contabilità, foglio di lavoro di pianificazione finanziaria, libro paga dei dipendenti, foglio di lavoro di pagamento del fornitore e così via.

Ti fiderai dei dati errati calcolati da Excel a causa dell'errore di riferimento circolare e questo a sua volta avrà un impatto sulla tua attività o sul processo decisionale professionale.

Inoltre, i riferimenti circolari possono ridurre notevolmente la velocità dei calcoli all'interno dell'app Excel. Nei casi più gravi, l'errore stesso può causare prestazioni estremamente lente anche nel PC se non risolto immediatamente.

Tuttavia, se stai lavorando su formule Excel avanzate in cui il tuo modello di dati richiede input intermedi dal riferimento circolare per continuare con una funzione lunga, potrebbe essere necessario utilizzarla. Infatti, Excel ti consente di utilizzare questo metodo di calcolo quando necessario.

Ad esempio, nelle unità di produzione di energia, come i mulini a vento, è necessario calcolare l'energia in uscita riciclata da una frazione dell'energia in ingresso. In alternativa, in un impianto chimico, gli ingegneri utilizzano lo stelo di uscita come ingresso per la stessa o un'altra caldaia o recipiente di processo. Tali calcoli ingegneristici di alto livello richiedono infatti riferimenti circolari per calcoli iterativi.

Quando vedi l'errore di riferimento circolare in Excel?

Chiaramente parlando, il pop-up di riferimento circolare non è un messaggio di errore. È un modo per Excel di informarti che ha incontrato un riferimento circolare in una o più formule e che potrebbe influenzare l'algoritmo di calcolo e le prestazioni.

Per evitare eventuali danni causati dai riferimenti circolari nelle formule di Excel, è necessario sapere quando compaiono e quando no. Trova di seguito le istanze:

• In qualsiasi foglio di lavoro aperto o cartella di lavoro, crei una formula e inserisci il riferimento circolare per la prima volta.
• Tu o qualcuno hai rimosso tutti i riferimenti circolari dal file Excel e lo hai salvato. Ora, di nuovo tu o un altro utente inserite un riferimento circolare nello stesso file.
• Hai chiuso tutte le cartelle di lavoro aperte. Ora, apri una cartella di lavoro diversa e quindi digita una formula contenente un riferimento circolare.

Quando vedi l'errore di riferimento circolare in Excel

• Hai appena aperto una cartella di lavoro che in precedenza conteneva uno o più riferimenti circolari. Vedrai l'avviso.
• Una volta che Excel ti ha mostrato il messaggio di avviso di riferimento circolare una volta e hai fatto clic su OK , non mostrerà più lo stesso popup nella stessa sessione anche se continui a creare formule di riferimento più circolari.

Pertanto, è necessario cercare attentamente le istanze di cui sopra per rilevare gli avvisi di riferimento circolare e risolverli se non si cercano calcoli iterativi.

Tipi di riferimenti circolari in Excel

Se vuoi imparare come trovare riferimenti circolari in Excel, devi comprenderne i tipi. Perché trovare ed eliminare i riferimenti circolari in Excel non è un'attività automatizzata. Trova di seguito i tipi comuni di questo errore:

Un esempio di utilizzo del riferimento circolare

• I riferimenti circolari previsti si verificano quando vengono utilizzati intenzionalmente per scopi di calcolo iterativo. Ad esempio, è necessario inserire un timestamp su un'altra cella quando qualcuno inserisce un valore in una cella di riferimento utilizzando funzioni volatili e logiche come IF, OR, SUMIF, ecc.

Riferimento circolare non intenzionale in Excel

• Riferimenti circolari non intenzionali quando si tenta di lavorare più velocemente su una formula di Excel e si fa riferimento per errore alla cella contenente la formula. Ad esempio, è necessario applicare la funzione SOMMA dalla cella A1 alla A9 e recuperare il risultato nella cella A10 . Ma erroneamente, digiti la formula in questo modo sulla cella A10:

=SOMMA(A2:A10)

• I riferimenti circolari nascosti sono quelli che non mostrano alcun messaggio di avviso quando si crea una formula o si apre il foglio di lavoro per la prima volta. Succede perché qualcuno ha consentito il calcolo iterativo sul file Excel.

Un esempio di riferimento circolare indiretto

• Anche i riferimenti circolari indiretti sono popolari e si verificano quando si crea una nuova formula in una cella qualsiasi, ad esempio la cella A1 , e questa nuova formula utilizzerebbe un'altra funzione in un'altra cella, come la cella B1 , che utilizza già la cella A1 come riferimento .

Ora che hai compreso le basi dei riferimenti circolari in Excel, scopriamo come trovare i riferimenti circolari in Excel di seguito:

Come trovare riferimenti circolari in Excel: dalla barra di stato

A condizione che la funzione di calcolo iterativo sia disattivata, è possibile visualizzare le seguenti notifiche sui riferimenti circolari nella barra di stato di Excel:

• Riferimenti circolari: C3  (o qualsiasi altro riferimento di cella) quando ci si trova su un foglio di lavoro contenente una formula circolare nella cartella di lavoro.

Visualizzazione dei riferimenti circolari nella barra di stato di Excel

• Vedrai Riferimenti circolari solo se ti trovi su un foglio di lavoro che non ne contiene nessuno ma la cartella di lavoro ha alcune di queste formule circolari.

Come trovare riferimenti circolari in Excel: utilizzo del controllo degli errori

È il modo migliore per verificare la presenza di riferimenti circolari in una cartella di lavoro o in un foglio di lavoro. Ecco i passaggi che devi sapere:

• Apri un foglio di lavoro che sospetti contenga formule circolari.
• Fare clic sul menu Formule sulla barra multifunzione di Excel.

Utilizzare il controllo degli errori per Come trovare riferimenti circolari in Excel

• Nel gruppo di comandi Controllo formule , fare clic sull'elenco a discesa Controllo errori  .
• Passa il cursore sull'opzione Riferimenti circolari per visualizzare tutte le istanze disponibili in tutti i fogli di lavoro.
• Seleziona ogni istanza ed Excel individuerà automaticamente quelle celle.

Come trovare riferimenti circolari in Excel: dal tracciamento delle formule

Se non ritieni che il metodo di controllo degli errori sia abbastanza efficiente da rilevare un numero elevato di formule con riferimenti circolari, puoi provare questi passaggi per utilizzare il metodo di traccia della formula:

• Selezionare qualsiasi cella di riferimento o la cella contenente la formula.
• Vai alla scheda Formule nel menu della barra multifunzione di Excel e fai clic su Trace Precedents .
• Excel evidenzierà le celle che influiscono sul risultato della formula sulla cella corrente con le frecce.

Usa la traccia della formula per Come trovare riferimenti circolari in Excel

• Puoi anche fare clic su Trace Dependents per scoprire le celle interessate dal valore del riferimento di cella selezionato.
• Se Excel non mostra alcun errore dopo aver fatto clic sui comandi precedenti, non sono presenti riferimenti circolari nel foglio di lavoro.
• Ripeti i passaggi su tutti i fogli di lavoro per controllare l'intera cartella di lavoro.

Questo metodo è particolarmente importante quando le funzionalità di calcolo iterativo sono attive nel foglio di lavoro di Excel.

Come trovare riferimenti circolari in Excel: utilizzo delle opzioni di Excel

• Fai clic sulla scheda File sulla barra multifunzione di Excel, quindi apri Opzioni di Excel dalla barra laterale visualizzata.
• Nella finestra di dialogo Opzioni di Excel , seleziona il menu Formule dalla barra laterale, quindi deseleziona la casella di controllo Abilita calcolo iterativo .

Come disattivare il calcolo iterativo in Excel

• Fare clic su OK per salvare le modifiche.
• Ora, la cartella di lavoro o il foglio di lavoro ti mostrerà automaticamente l'avviso di riferimento circolare.
• Se non viene visualizzato immediatamente, salva la cartella di lavoro, chiudila e riaprila per visualizzare l'avviso.
• Una volta visualizzato l'avviso, controlla la barra di stato di Excel per sapere quale cella si trova nel riferimento circolare.

Come risolvere i riferimenti circolari in Excel

Il modo sicuro per correggere uno o più errori di riferimento circolare su Excel è modificare manualmente ogni formula che lo contiene. In questo modo, puoi risolvere sia i riferimenti circolari accidentali che quelli indiretti in Excel.

Come risolvere i riferimenti circolari in Excel

Un altro modo semplice è rimuovere la formula dalla cella corrente e posizionarla da qualche altra parte all'interno dello stesso foglio di lavoro.

Come trovare riferimenti circolari in fogli Google (metodo bonus)

Come Excel, Fogli Google non consente riferimenti circolari in una o più formule se non hai attivato la funzione di calcolo iterativo. Pertanto, Fogli Google mostrerà immediatamente il #REF! errore sulla cella che contiene una formula circolare.

Fogli Google Come trovare riferimenti circolari

Pertanto, puoi semplicemente selezionare tutte le celle che contengono il #REF! avviso per trovare e risolvere riferimenti circolari su Fogli Google.

Riferimenti circolari di Excel: parole finali

Quindi, ora conosci tutti i segreti su come trovare riferimenti circolari in Excel. Usa il metodo che preferisci e elimina tutti i riferimenti circolari per assicurarti di non essere confuso da dati inaffidabili da un report Excel o da una dashboard matematica.

Successivamente, i  migliori modelli di calendario di Excel per qualsiasi anno  e i  migliori modelli di budget di Excel .