Referință circulară și calcul iterativ

Adesea întâlnim mesajul de eroare în Excel că există o referință circulară în formula noastră particulară, deci acest lucru poate duce la un calcul incorect și Excel ne cere să eliminăm aceste referințe. Uneori, totuși, dorim intenționat să efectuăm un calcul în acest mod, cel mai adesea în modelele financiare, acesta este un calcul iterativ, iterație.

Referință circulară

De aici puteți descărca fișierul utilizat în sarcină.

Mesajul de eroare al legăturii circulare arată astfel:

Acest lucru se face în majoritatea cazurilor printr-o eroare aleatorie, cum ar fi atunci când includem celula în care dorim să calculăm rezultatul într-o funcție SUM.

În acest caz, gama funcției SUM conține și celula B23, deci Excel nu poate calcula rezultatul solicitat pentru noi și 0 ca rezultat. Când am terminat de editat formula, am primit mesajul de eroare de mai sus, așa că Excel „spune” că a apărut o eroare.

Dacă deschideți un fișier pe care nu l-ați editat, veți vedea în continuare acest mesaj de eroare când îl deschideți, dar acesta nu vă va arăta exact unde este eroarea.

Pentru aceasta, utilizați comanda Verificare formulă -> Verificare erori -> Legături circulare sub elementul din meniul Formule.

Dacă faceți clic, se vor afișa celulele în care veți găsi un link circular și veți naviga la acesta atunci când faceți clic pe adresa celulei. Dacă eroarea se află pe o altă foaie de lucru, referința la celulă conține și numele foii de lucru.

Iterare, calcul iterativ

Pe lângă erorile accidentale, desigur, pot exista și cazuri în care dorim să efectuăm un astfel de calcul în mod intenționat, cum ar fi în cazul modelului următor.

Aici calculăm prețul brut al unui produs. Costurile sunt date și, în funcție de preț, trebuie să adăugăm profitul și TVA pentru a obține prețul brut al produsului. Coloana formulelor arată clar că atât profitul, cât și TVA pot fi obținute din prețul brut de C13, în timp ce funcția SUM din celula C13 conține și aceste cantități. Astfel, acest calcul conduce, de asemenea, la o referință circulară, care, în acest caz, chiar și săgeata albastră indică între ce celule există această eroare.

Matematic, calculul ar fi după cum urmează:

1000 + 1500 + 120 + 0,1 * PREȚ + 0,27 * PREȚ = PREȚ

În acest caz, problema poate fi ușor rezolvată chiar matematic, dar în cazul modelelor mai serioase, mai complexe, nu mai este sigur că o putem evita.
Din fericire, Excel poate gestiona și aceste calcule, trebuie doar să îi permitem să efectueze calcule într-un mod iterativ, aproximativ. Dacă efectuați această setare, calculul va funcționa și nu va da un mesaj de eroare din cauza unei referințe circulare.

Activați calculul iterativ

Din fericire, Excel poate gestiona și aceste calcule, trebuie doar să îi permitem să efectueze calcule într-un mod iterativ, aproximativ. Dacă faceți această setare, calculul va funcționa și nu va da un mesaj de eroare din cauza unei referințe circulare.

Această setare poate fi găsită în Fișier -> Preferințe din fila Formule. În dreapta este elementul Activare abordare, care, dacă este selectat, va fi calculat de Excel în acest fel.

În acest caz, calculele din model au fost făcute fără probleme și au calculat prețul brut al produsului.

Natural trebuie să fim foarte atenți cu această setare, deoarece dacă o lăsăm la fel, vor fi calculate și referințele circulare bazate pe o eroare aleatorie și nu vom primi niciun mesaj de eroare, care ne-ar avertiza. Prin urmare, este recomandat, după calcularea fiecărui astfel de model, restaurați Excel în modul său original, pentru a evita problemele în altă parte.

Dacă ți s-a părut util și ți-a plăcut, te rog să-l împărtășești cu alții!:)