Sokszor találkozunk az Excelben azzal a hibaüzenettel, hogy körkörös hivatkozás van az adott képletünkben, ezért ez helytelen számításhoz vezethet, és arra kér az Excel, hogy távolítsuk el ezeket a hivatkozásokat. Olykor azonban előfordul, hogy szándékosan szeretnénk egy számítást ilyen módon elvégezni, legtöbbször pénzügyi modellekben, ez az iteratív kalkuláció, iteráció.

Körkörös hivatkozás

A feladatban használt fájlt innen tudod letölteni.

A körkörös hivatkozás hibaüzenete így néz ki:

Körkörös hivatkozás

Ez a legtöbb esetben véletlen hiba által történik, mint például, amikor egy SZUM függvénybe belevettük azt a cellát is, ahol az eredményt szeretnénk kiszámítani.

Körkörös hivatkozás

Ebben az esetben a SZUM függvény tartománya tartalmazza a B23-as cellát is, így nem tudja kiszámolni nekünk az Excel a kért eredményt, és 0-át eredményül. Amikor lezárjuk a képlet szerkesztését, akkor a fentebbi hibaüzenet kaptuk meg, tehát az Excel „szól”, hogy hiba történt.

Ha egy olyan fájlt nyitunk meg, amit nem mi szerkesztettünk, akkor is a legtöbb esetben a megnyitáskor jelzi ezt a hibaüzenetet, de nem mutatja meg, hogy pontosan hol is található a hiba.

Erre szolgál a Képletek menüpont alatti Képletvizsgálat -> Hibaellenőrzés -> Körkörös Hivatkozások parancs.

Körkörös hivatkozás

Rákattintva felsorolja azokat a cellákat, amelyekben körkörös hivatkozást talál, és oda is navigál, ha rákattintunk a cella címére. Amennyiben másik munkalapon található a hiba, akkor a cellahivatkozás tartalmazza a munkalap nevét is.

Körkörös hivatkozás és iteratív kalkuláció

Iteráció, iteratív kalkuláció

A véletlenül történt hibák mellett természetesen lehetnek olyan esetek is, amikor szándékosan szeretnénk ilyen számítást végezni, mint például a következő modell esetében.

iteráció

Itt egy termék bruttó árát számítjuk. A költségek adottak, és az ár függvényében kell még a profitot és ÁFÁ-t hozzáadnunk, hogy megkapjuk a termék bruttó árát. A képletek oszlopban jól látszik, hogy a profit és az ÁFA is a C13-as bruttó árból származtatható, miközben a C13-as cellában lévő SZUM függvény tartalmazza is ezeket az összegeket. Így ez a számítás is körkörös hivatkozáshoz vezet, amit ebben az esetben még a kék nyíl is jelez, hogy mely cellák között áll fenn ez a hiba.

Matematikailag a következő lenne a számítás:

1000 + 1500 + 120 + 0,1*ÁR + 0,27*ÁR=ÁR

Ilyen esetben könnyen megoldható még matematikailag is a probléma, de ennél komolyabb, összetettebb modellek esetén már nem biztos, hogy ki tudjuk kerülni.
Az Excel szerencsére tudja kezelni ezeket a számításokat is, csak meg kell engednünk neki, hogy iteratív, közelítő módon végezzen számításokat. Ha ezt a beállítást megtesszük, akkor működni fog a számítás, és nem fog körkörös hivatkozás miatt hibaüzenetet adni.

Iteratív kalkuláció engedélyezése

Az Excel szerencsére tudja kezelni ezeket a számításokat is, csak meg kell engednünk neki, hogy iteratív, közelítő módon végezzen számításokat. Ha ezt a beállítást megtesszük, akkor működni fog a számítás, és nem fog körkörös hivatkozás miatt hibaüzenetet adni.

Ezt a beállítást a Fájl -> Beállítások alatt találjuk, a Képletek fülön. A jobb oldalon található a Közelítés engedélyezése pont, amit ha kiválasztunk, akkor ilyen módon fog számolni az Excel.

iteráció

Ebben az esetben modellben található számítások gond nélkül megtörténtek és kiszámította a termék bruttó árát.

Iteráció

Természetesen ezzel a beállítással nagyon vigyázunk kell, mert ha így hagyjuk, akkor a véletlen hiba alapján elkövetett körkörös hivatkozások is kiszámításra kerülnek és nem kapunk hibaüzenetet sem, ami figyelmeztetne minket. Ezért javasolt, minden ilyen modell kiszámítása után visszaállítani az eredeti módjára az Excel-t, hogy elkerüljük a máshol felmerülő gondokat.

Ha hasznosnak találtad és tetszett, kérlek, oszd meg másokkal is! :)

ISMERD MEG INGYEN AZ EXCEL KIMUTATÁS FUNKCIÓJÁT

Annyira fontos funkció az Excelben a kimutatások ismerete, hogy szeretném, ha Te is megismerhetnéd és használnád a munkád során, így most ajándékként megkapod tőlem! Hét rövid videóból Te magad is végigmehetsz a lépéseken, hogy jobban megértsd működését. Vágj bele most és használd bátran a munkádban! 🙂