Körkörös hivatkozás megtalálása az Excelben (gyors és egyszerű)

Amikor Excel képletekkel dolgozik, előfordulhat, hogy a következő figyelmeztető üzenet jelenik meg.

Ez a felszólítás azt jelzi, hogy a munkalapon körkörös hivatkozás található, és ez vezethet, vagy helytelen számítást végezhet a képletekkel. Azt is kéri, hogy foglalkozzon ezzel a körkörös hivatkozási kérdéssel és rendezze azt.

Ebben az oktatóanyagban kitérek mindenre, amit a körkörös hivatkozásról tudni kell, valamint hogyan találhat és távolíthat el körkörös hivatkozásokat az Excelben.

Kezdjük hát el!

Mi a körkörös hivatkozás az Excelben?

Egyszerű szavakkal, körkörös hivatkozás történik, amikor a végén egy képlet van egy cellában - amely önmagában a cellát (amelybe be lett írva) használja a számításhoz.

Hadd próbáljam meg ezt egy egyszerű példával elmagyarázni.

Tegyük fel, hogy az adathalmaz az A1: A5 cellában van, és az alábbi képletet használja az A6 cellában:

= ÖSSZES (A1: A6)

Ezzel körkörös referencia figyelmeztetést kap.

Ennek az az oka, hogy az A1: A6 cella értékeit összegezni szeretné, és az eredménynek az A6 cellában kell lennie.

Ez hurkot hoz létre, amikor az Excel folyamatosan hozzáadja az új értéket az A6 cellához, amely folyamatosan változik (tehát körkörös referenciahurok).

Hogyan találhat körkörös hivatkozásokat az Excelben?

Bár a körkörös referencia figyelmeztető üzenet elég kedves ahhoz, hogy megmondja, hogy létezik a munkalapon, nem mondja meg, hogy hol történik, és milyen cellahivatkozások okozzák.

Tehát, ha kerek hivatkozásokat szeretne megtalálni és kezelni a munkalapon, tudnia kell, hogyan találhatja meg ezeket.

Az alábbi lépésekkel kerek hivatkozást találhat az Excelben:

1. Aktiválja a körlapot tartalmazó munkalapot
2. Kattintson a Képletek fülre
3. A Képletszerkesztő csoportban kattintson a Hibaellenőrzés legördülő ikonra (kis lefelé mutató nyíl a jobb oldalon)
4. Vigye a kurzort a Körkörös hivatkozások lehetőségre. Megmutatja azt a cellát, amely körkörös hivatkozással rendelkezik a munkalapon
5. Kattintson a cella címére (amely megjelenik), és a munkalap adott cellájába kerül.

Miután megoldotta a problémát, ismét elvégezheti a fenti lépéseket, és további cellahivatkozások jelennek meg, amelyek körkörös hivatkozással rendelkeznek. Ha nincs, akkor nem lát cellahivatkozást,

Egy másik gyors és egyszerű módja a körkörös hivatkozás megkeresésének, ha megnézi az állapotsort. Ennek bal oldalán megjelenik a Körkörös hivatkozás szöveg a cella címével együtt.

Néhány dolgot tudnia kell, amikor körkörös hivatkozásokkal dolgozik:

1. Ha az iteratív számítás engedélyezve van (az oktatóanyag későbbi részében tárgyaljuk), akkor az állapotsor nem mutatja a körkörös referenciacella címét
2. Ha a körkörös hivatkozás nincs az aktív lapon (de ugyanazon munkafüzet más lapjain), akkor csak a körkörös hivatkozást fogja megjeleníteni, és nem a cella címét
3. Abban az esetben, ha egyszer körkörös hivatkozási figyelmeztetést kap, és elveti, a következő alkalommal nem jelenik meg újra.
4. Ha megnyit egy olyan munkafüzetet, amely körkörös hivatkozással rendelkezik, akkor a munkafüzet megnyitásakor azonnal megjelenik a kérdés.

Hogyan lehet eltávolítani a körkörös hivatkozást az Excelben?

Miután megállapította, hogy körlapi hivatkozások vannak a lapon, ideje eltávolítani ezeket (kivéve, ha valamilyen okból szeretné, hogy ott legyenek).

Sajnos ez nem olyan egyszerű, mint a törlés gomb megnyomása. Mivel ezek képletektől függenek, és minden képlet más, ezt eseti alapon kell elemeznie.

Abban az esetben, ha csak a cellahivatkozás okozza a hibát, egyszerűen korrigálhatja a referencia beállításával.

De néha ez nem ilyen egyszerű.

A körkörös hivatkozást több sejt alapján is előidézhetjük, amelyek sok szinten táplálkoznak egymásba.

Hadd mutassak egy példát.

Az alábbiakban körkörös hivatkozás található a C6 cellában, de ez nem csak az önreferencia egyszerű esete. Többszintű, ahol a számításokban használt cellák is hivatkoznak egymásra.

• Az A6 cella képletei = SUM (A1: A5)+C6
• A képlet a C1 cella = A6*0,1
• A C6 cella képlete = A6+C1

A fenti példában a C6 cella eredménye az A6 és C1 cella értékeitől függ, amelyek viszont a C6 cellától függenek (ezáltal a körkörös referencia hibát okozva)

És ismét egy nagyon egyszerű példát választottam csak demo célokra. A valóságban ezeket meglehetősen nehéz kitalálni, és talán messze vannak ugyanazon a munkalapon, vagy akár több munkalapon is elszórtan.

Ilyen esetben van egy módja annak, hogy azonosítsuk a körkörös hivatkozást okozó sejteket, majd kezeljük ezeket.

A Precedentsek nyomon követése opció használatával.

Az alábbiakban bemutatjuk azokat a lépéseket, amelyek segítségével nyomkövetési példákat használhat a körkörös hivatkozással rendelkező cellába táplálkozó sejtek megkereséséhez:

1. Válassza ki azt a cellát, amely körkörös hivatkozással rendelkezik
2. Kattintson a Képletek fülre
3. Kattintson az Előzmények nyomon követése lehetőségre

A fenti lépések kék nyilakat mutatnak, amelyek jelzik, hogy mely sejtek táplálják a képletet a kiválasztott cellában. Így ellenőrizheti a képleteket és a cellákat, és megszabadulhat a körkörös hivatkozástól.

Abban az esetben, ha összetett pénzügyi modellekkel dolgozik, előfordulhat, hogy ezek az előzmények több szintre is kiterjednek.

Ez akkor működik jól, ha minden képlete ugyanazon a munkalap celláira vonatkozik. Ha több munkalapon szerepel, ez a módszer nem hatékony.

Iteratív számítások engedélyezése/letiltása az Excelben

Ha körkörös hivatkozással rendelkezik egy cellában, először a figyelmeztető üzenetet kapja, amint az alább látható, és ha bezárja ezt a párbeszédpanelt, akkor 0 lesz az eredmény a cellában.

Ennek az az oka, hogy amikor van körkörös hivatkozás, akkor ez egy végtelen ciklus, és az Excel nem akar belefogni. Tehát 0 -t ad vissza.

De bizonyos esetekben érdemes lehet, hogy a körkörös hivatkozás aktív legyen, és végezzen néhány iterációt. Ebben az esetben a végtelen ciklus helyett, és eldöntheti, hogy hányszor kell futtatni a ciklust.

Ezt nevezik iteratív számítás Excelben.

Az alábbiakban bemutatjuk az iteratív számítások Excelben történő engedélyezésének és konfigurálásának lépéseit:

1. Kattintson a Fájl fülre
2. Kattintson a Beállítások lehetőségre. Ezzel megnyílik az Excel beállításai párbeszédpanel
3. Válassza a Képlet lehetőséget a bal oldali ablaktáblában
4. A Számítási beállítások részben jelölje be az „iteratív számítás engedélyezése” négyzetet. Itt adhatja meg a maximális iterációt és a maximális változási értéket

Ez az! A fenti lépések lehetővé teszik az iteratív számítást Excelben.

Hadd magyarázzam meg gyorsan az iteratív számítás két lehetőségét is:

• Maximális iterációk: Ez az a maximális szám, amelyet az Excelnek ki kell számolnia, mielőtt megadja a végeredményt. Tehát ha ezt 100 -nak adja meg, az Excel 100 -szor futtatja a ciklust, mielőtt megadja a végeredményt.
• Maximális változás: Ez a maximális változás, amelyet ha nem érünk el az iterációk között, a számítás leáll. Alapértelmezés szerint az érték .001. Minél alacsonyabb ez az érték, annál pontosabb lesz az eredmény.

Ne feledje, hogy minél többször futnak az iterációk, annál több időre és erőforrásra van szükség az Excel számára. Ha a maximális iterációt magasan tartja, az Excel lelassulhat vagy összeomolhat.

Megjegyzés: Ha az iteratív számítások engedélyezve vannak, az Excel nem jeleníti meg a körkörös referencia figyelmeztetést, és most az állapotsorban is megjelenik.

Körkörös hivatkozások szándékos használata

A legtöbb esetben a körkörös hivatkozás jelenléte a munkalapon hiba lenne. Ez az oka annak, hogy az Excel megjelenít egy üzenetet: „Próbálja meg eltávolítani vagy módosítani ezeket a hivatkozásokat, vagy áthelyezni a képleteket különböző cellákba.”

Előfordulhat azonban néhány speciális eset, amikor körkörös hivatkozásra van szüksége a kívánt eredmény eléréséhez.

Egy ilyen konkrét esetről már írtam arról, hogy az Excel -ben egy cella cellájában megkapja az időbélyegzőt.

Tegyük fel például, hogy olyan képletet szeretne létrehozni, hogy minden bejegyzés az A oszlop egyik cellájában történjen, az időbélyeg megjelenik a B oszlopban (az alábbiak szerint):

Míg egyszerűen beilleszthet egy időbélyeget az alábbi képlet segítségével:

= HA (A2 "", HA (B2 "", B2, MOST ()), "")

A fenti képlet problémája az, hogy frissíti az összes időbélyeget, amint a munkalapon bármilyen módosítás történik, vagy ha a munkalapot újra megnyitják (mivel a MOST képlet ingadozó)

A probléma kiküszöbölésére körkörös hivatkozási módszert használhat. Használja ugyanazt a képletet, de engedélyezze az iteratív számítást.

Vannak más esetek is, amikor kívánatos a körkörös hivatkozás használata (itt találhat egy példát).

Megjegyzés: Bár előfordulhatnak olyan esetek, amikor körkörös hivatkozást használhat, úgy gondolom, hogy a legjobb elkerülni a használatát. A körkörös hivatkozások szintén ronthatják a munkafüzet teljesítményét, és lassíthatják azt. Ritka esetekben, amikor szüksége van rá, mindig inkább a VBA kódokat használom a munka elvégzéséhez.

Remélem, hasznosnak találta ezt az oktatóanyagot!

Egyéb Excel oktatóanyagok, amelyek hasznosak lehetnek:

• #REF! Hiba az Excelben; Hogyan javítsuk ki a referencia hibát!
• Excel VBA hibakezelés
• Használja az IFERROR -t a VLOOKUP funkcióval, hogy megszabaduljon a #N/A hibáktól
• Hogyan lehet hivatkozni egy másik lapra vagy munkafüzetre Excelben (példákkal)
• Abszolút, relatív és vegyes cella referenciák az Excelben