Quando una formula in Excel deve dipendere anche dal proprio risultato, il foglio entra in un ciclo che il calcolo standard non riesce a risolvere. Il calcolo iterativo in Excel serve proprio a gestire questi riferimenti circolari, ma va configurato con criterio per evitare risultati instabili, rallentamenti o modelli difficili da verificare.
La configurazione corretta rende gestibili anche i modelli circolari
- Attivazione nelle opzioni delle formule, alla voce relativa al calcolo iterativo.
- Iterazioni massime per stabilire quante volte Excel può ripetere il calcolo.
- Variazione massima per definire quando il risultato è sufficientemente stabile.
- Riferimenti circolari intenzionali da distinguere dagli errori di progettazione.
- Controlli indipendenti indispensabili per verificare che il risultato abbia senso.
Come attivare il calcolo iterativo in Excel
Per abilitare questa funzione nella versione desktop, apro il menu File, entro in Opzioni e seleziono Formule. Nella sezione dedicata alle opzioni di calcolo attivo la casella Abilita calcolo iterativo. I nomi possono cambiare leggermente tra Windows, Mac e le diverse lingue di Microsoft 365.
Subito sotto compaiono due parametri. Il primo è il numero massimo di iterazioni, cioè il limite dei cicli che Excel può eseguire. Il secondo è la variazione massima, la soglia sotto la quale Excel considera stabilizzato il risultato. La documentazione di Microsoft Support descrive questi due valori come i principali strumenti per controllare il comportamento del calcolo circolare.
Quali valori impostare
Non esiste un valore ideale per ogni foglio. Per un modello finanziario semplice, partirei da 100 iterazioni e da una variazione massima di 0,001, poi controllerei la stabilità del risultato. Se lavoro con importi molto grandi o con percentuali visualizzate con due decimali, la soglia deve essere coerente con la precisione realmente necessaria.
| Parametro | Che cosa controlla | Indicazione pratica |
|---|---|---|
| Iterazioni massime | Il numero massimo di ricalcoli consecutivi | 100 è un buon punto di partenza per molti modelli |
| Variazione massima | La differenza ammessa tra un risultato e il successivo | Usare una soglia più piccola quando serve maggiore precisione |
Le impostazioni di iterazione possono influenzare l’applicazione Excel e non soltanto il file aperto. Per questo, quando passo un modello a un collega, documento sempre i valori utilizzati e verifico il comportamento del file su una seconda installazione. È un dettaglio poco visibile, ma può spiegare perché due persone ottengano risultati diversi dallo stesso workbook.
Che cosa significano iterazioni e variazione massima
Con una formula normale Excel calcola il risultato seguendo le dipendenze tra le celle. Con un riferimento circolare, invece, una cella dipende direttamente o indirettamente da se stessa. Excel calcola una prima stima, la reinserisce nella formula e ripete il processo fino a raggiungere la convergenza oppure uno dei due limiti configurati.
Immaginiamo che il risultato iniziale sia 1.000, quello dopo il primo ciclo 1.050 e quello successivo 1.052,50. Se la variazione massima impostata è 0,001, Excel continuerà a lavorare finché la differenza tra due risultati consecutivi non sarà inferiore a quella soglia, oppure finché non avrà raggiunto il numero massimo di iterazioni.
Il limite che viene raggiunto per primo
Excel interrompe il processo quando si verifica una delle due condizioni. Se il modello converge rapidamente, può fermarsi molto prima del numero massimo di iterazioni. Se invece la formula oscilla o cambia troppo lentamente, raggiungerà il limite dei cicli senza avere necessariamente trovato un risultato affidabile.
Per me questa distinzione è fondamentale. Arrivare a un numero non significa aver risolto correttamente il problema: un valore può essere soltanto l’ultima stima prodotta da Excel prima dello stop.
Un esempio pratico con una formula circolare
Consideriamo un caso volutamente semplice. In B2 inserisco la base di calcolo, pari a 1.000. In B3 inserisco un tasso del 5%. In B4 inserisco la formula:
=B2+B4*B3
B4 contiene quindi un riferimento a se stessa. In forma matematica, il risultato è 1.000 più il 5% dello stesso risultato. Con il calcolo iterativo attivo, Excel converge verso circa 1.052,63.
Come verificare il risultato
Non mi affiderei mai soltanto al valore mostrato dalla cella circolare. In una cella separata inserirei il calcolo equivalente senza iterazioni:
=B2/(1-B3)
Se i due valori coincidono entro la precisione desiderata, ho una prima conferma. Posso anche controllare l’errore residuo con una formula come:
=B4-(B2+B4*B3)
Il risultato dovrebbe essere vicino a zero. Questo piccolo controllo è molto utile nei modelli di budgeting, nei prospetti di liquidità e nelle simulazioni dove una variazione minima può propagarsi su molte celle.
Un esempio più vicino alla pratica finanziaria
Un modello di cassa può calcolare gli interessi in base al saldo medio, mentre il saldo finale dipende dagli interessi stessi. In questo caso la circolarità può essere intenzionale. La formula può essere ragionevole se rappresenta davvero il processo economico, ma va accompagnata da ipotesi documentate, come il tasso applicato, il periodo di riferimento e il criterio usato per il saldo medio.
Se riesco a trasformare il problema in una formula diretta, preferisco farlo. La soluzione algebrica è generalmente più veloce, trasparente e semplice da verificare. Il calcolo iterativo diventa interessante quando il modello contiene più relazioni interdipendenti o quando una formula diretta sarebbe troppo complessa da mantenere.
Quando usarlo e quando evitarlo
Il calcolo iterativo è appropriato quando la circolarità descrive un fenomeno reale. Succede, per esempio, con alcuni modelli di interessi, imposte, capitale circolante, commissioni o allocazioni che dipendono dal risultato finale.
| Situazione | Scelta consigliata | Motivo |
|---|---|---|
| Formula che dipende da se stessa per errore | Correggere il riferimento | Attivare l’iterazione nasconderebbe il problema |
| Modello con feedback economico reale | Usare il calcolo iterativo | La circolarità rappresenta il processo analizzato |
| Ricerca di un valore obiettivo | Valutare Ricerca obiettivo | È più adatta a modificare un input per raggiungere un target |
| Ottimizzazione con vincoli | Valutare Solver | Serve quando bisogna ottimizzare più variabili |
Il primo controllo da fare è quindi semplice. Se Excel segnala un riferimento circolare e non avevo previsto alcun ciclo, non attivo la funzione per far sparire l’avviso. Cerco invece la cella coinvolta usando la sezione Controllo errori e verifico i precedenti e i dipendenti della formula.
Ricerca obiettivo e calcolo iterativo risolvono problemi diversi. La prima modifica un valore di input per ottenere un risultato preciso, mentre il secondo aggiorna automaticamente un sistema di formule che si influenzano a vicenda. Confonderli porta spesso a modelli più difficili del necessario.
Gli errori tipici che compromettono il risultato
Attivare l’iterazione per ogni riferimento circolare
È l’errore più comune. Un riferimento circolare accidentale può nascere da un intervallo scritto male, da una somma che include la propria cella o da una formula copiata nella riga sbagliata. Prima di accettare il ciclo, verifico sempre se esiste una dipendenza logica reale.
Impostare una soglia troppo ampia
Una variazione massima elevata può far terminare il calcolo in fretta, ma con un risultato ancora approssimativo. In un prospetto espresso in euro, una differenza di pochi centesimi può essere irrilevante per una stima preliminare e inaccettabile per una registrazione contabile. La precisione va definita in base all’uso del modello, non scelta automaticamente.
Aumentare le iterazioni senza controllare la convergenza
Portare il limite a migliaia di cicli non risolve una formula instabile. Se il risultato alterna 100 e 200, oppure cresce senza stabilizzarsi, il problema è nella struttura della formula o nei dati di partenza. Un numero più alto di iterazioni può soltanto rallentare il workbook e rendere più difficile capire che cosa non funziona.
Leggi anche: File Excel pesante? Come renderlo più veloce e leggero
Ignorare i valori iniziali
Alcuni modelli convergono verso lo stesso valore indipendentemente dalla stima iniziale. Altri possono convergere verso risultati diversi o non convergere affatto. Per questo salvo una copia del file, provo almeno due valori iniziali plausibili e confronto l’esito.
Durante i test controllo anche i tempi di ricalcolo. Se un foglio diventa lento dopo l’attivazione, limito le formule circolari alle aree indispensabili, sostituisco i riferimenti ripetuti con celle di appoggio e riduco la complessità delle funzioni annidate.
Come rendere il modello più solido nel tempo
Un file con calcolo iterativo dovrebbe dichiarare chiaramente quali celle partecipano al ciclo. Inserisco una piccola area di controllo con la soglia utilizzata, il numero massimo di iterazioni, il risultato residuo e una nota sulle ipotesi economiche o operative.
- Conservo una versione senza formule circolari per confrontare i risultati.
- Testo valori iniziali diversi per verificare la stabilità.
- Controllo che il residuo della formula sia vicino a zero.
- Uso nomi chiari per tassi, aliquote e parametri.
- Proteggo le celle di input e separo ipotesi, calcoli e risultati.
Quando il modello cresce, considero anche una struttura alternativa. A volte è meglio calcolare ogni periodo in una riga distinta, usare una tabella di supporto o spostare la simulazione in Power Query, VBA o Python. Non perché il calcolo iterativo sia sbagliato, ma perché la trasparenza del modello vale quanto il risultato numerico.
La regola che seguo è questa: attivo l’iterazione solo quando la circolarità descrive davvero il problema, imposto una tolleranza coerente con la precisione richiesta e verifico il risultato con almeno un calcolo indipendente. In questo modo Excel resta uno strumento utile per l’analisi dei dati, invece di diventare una scatola nera che restituisce numeri difficili da difendere.
