Hogyan keressünk körleveleket az Excelben

Amikor a -val dolgozik, időnként találkozhat az úgynevezett körkörös hivatkozással. Ilyenkor egy cellában lévő képlet önmagára utal, végtelen hurkot okozva a számítási folyamatban.

Ha körkörös hivatkozásokat szeretne keresni az Excelben, használja a „Képletek” lap „Hibaellenőrzése” eszközét a körkörös függőséggel rendelkező cellák azonosításához.

Ez a cikk bemutatja, hogyan találhat körkörös hivatkozásokat az Excelben, és hogyan javíthatja ki őket. Azt is megtanulja, hogyan lehet megelőzni ezeket, amikor nagy vagy összetett munkalapokkal dolgozik.

Tartalomjegyzék

• Mik azok a körlevelek
• A Circular Reference hibaüzenet ablak
• Hogyan keressünk körleveleket az Excelben
• Körhivatkozások keresése az Excel állapotsorával
• Hogyan találhat rejtett körleveleket az Excelben
  • A Trace Dependent eszköz használata
  • Trace Precedents eszköz
• 2 módszer a körkörös hivatkozások eltávolítására az Excelben
  • 2. Használjon alternatív képleteket
• 5 tipp a körkörös hivatkozások megelőzésére
  • 1. Legyen óvatos a tartományok kiválasztásakor
  • 2. Gondosan tervezze meg képleteit
  • 3. Használjon alternatív képleteket
  • 4. Bontsa fel az összetett képleteket kisebb részekre!
  • 5. Javítsa ki a problémákat menet közben
• Hogyan lehet engedélyezni az iteratív számítást, és miért baj?

Mik azok a körlevelek

A körkörös hivatkozások akkor fordulnak elő az Excelben, ha egy cellában a képletszámítás a cella saját értékétől függ. Ez hurkot hoz létre, és helytelen vagy előre nem látható eredményekhez vezet.

Ezt egy egyszerű példával könnyebb megérteni.

Tegyük fel, hogy két szám van az A1 és B1 cellában. Olyan képletet szeretne használni az A3 cellában, amely összeadja a két számot. Az A3 cellában lévő képletnek így kell kinéznie:

=A1 + B1

Ha azonban hibázik, és beleveszi a C1-et a számításokba, akkor most van egy körkörös hivatkozása:

=A1 + B1 + C1

Ez nyilvánvaló probléma lehet, amelyet el kell kerülni, ha két cellával foglalkozik. Ha azonban nagy cellatartományokkal dolgozik, nagyobb a hibák valószínűsége.

Szerencsére az Excel úgy van kialakítva, hogy kezelje az ilyen helyzeteket úgy, hogy figyelmeztető üzenetet jelenít meg, ha körkörös hivatkozást észlel.

Bizonyos esetekben azonban előfordulhat, hogy figyelmeztetés nélkül körkörös hivatkozással találkozhat. Ezért fontos tudni, hogyan lehet ezeket a problémákat manuálisan megtalálni és megoldani.

A Circular Reference hibaüzenet ablak

Alapértelmezés szerint az Excel figyeli a körkörös hivatkozásokat, amikor képleteket ír be a cellákba. Ha egy problémás cellában megnyomja az Enter billentyűt, egy felugró ablakban megjelenik a körkörös hivatkozási hibaüzenet.

Ez a magyarázó szöveg:

Egy vagy több körkörös hivatkozás létezik, ahol egy képlet közvetlenül vagy közvetve hivatkozik a saját cellájára. Emiatt előfordulhat, hogy helytelenül számolnak. Próbálja meg eltávolítani vagy módosítani ezeket a hivatkozásokat, vagy helyezze át a képleteket más cellákba.

Amikor ezt a párbeszédpanelt látja, kattintson az „OK” gombra, és használja a cikkben leírt módszereket a probléma megoldásához.

A hibát azonban nem kell azonnal kijavítania. A probléma későbbi megoldása érdekében folytathatja a munkát a táblázattal.

Ebben az esetben tudnia kell, hogyan találhat körkörös hivatkozásokat az Excelben, miután elvetette a figyelmeztető mezőt.

Hogyan keressünk körleveleket az Excelben

Az Excel beépített hibaellenőrző eszközzel rendelkezik, amely többféle hibát talál, beleértve a körkörös hivatkozásokat is. Az eszközt a Képletek lapon érheti el.

Ez a kép a Formula Auditing csoporton belüli helyet mutatja.

Most már tudja, hogyan találja meg az eszközt, itt található a használatának részletes lépése:

1. Lépjen a Képletek lapra.

1. Kattintson a Hibaellenőrzés legördülő menüre a Képlet-auditálás csoportban.

2. A legördülő listából válassza a „Circular References” lehetőséget.

3. Kattintson a hivatkozásra, hogy a kurzort az első problémát tartalmazó cellára ugorja.

Az alábbi képen látható, hogy az eszköz körhibát talált a G1 cellában. A megjelenített cella egy hiperhivatkozás.

Amikor a hivatkozásra kattint, a kurzor a cellára ugrik. Ez nagy mennyiségű adat esetén nagyon hasznos.

Miután megoldotta az első körkörös hivatkozást, visszatérhet a Hibaellenőrző eszközhöz, és megkeresheti a következőt a lapon.

A probléma megoldásához kövesse a cikk későbbi szakaszaiban található lépéseket, és használja az eszközt a következő problémához.

Ha nincs több körprobléma, akkor a legördülő menüben a „Circular References” menüpont szürkén jelenik meg. Nincs további teendője.

Körhivatkozások keresése az Excel állapotsorával

A körkörös hivatkozás másik módja az Excel munkalap alján található állapotsor megtekintése.

Ha egy vagy több probléma van a munkalapon, az állapotsor megjeleníti egy problémás cellahivatkozás helyét.

Például, ha a C1 cellában körkörös hivatkozás található, és nincs más probléma a táblázattal, az állapotsoron a következő jelenik meg: „Körhivatkozások: C1”.

Ez a kép azt mutatja, hol kell látni a kijelzőt.

Hogyan találhat rejtett körleveleket az Excelben

A rejtett körkörös hivatkozások olyan körkörös hivatkozások, amelyeket a beépített hibafunkciók nem észlelnek azonnal. Más szóval, az Excel nem jeleníti meg a szabványos figyelmeztetést.

Ezek akkor fordulnak elő, amikor egy cella közvetetten, köztes cellák sorozatán keresztül hivatkozik egy másik cellára, és végül az eredeti cellára hivatkozik.

Az Excel két eszközzel segíti a kapcsolatok nyomon követését és a problémák kivizsgálását:

1. Trace Dependents eszköz

2. Trace Precedents eszköz

A Trace Dependent eszköz használata

A tapasztalt Excel-felhasználók a Trace Dependents eszközt használják a problémák kivizsgálására és a körkörös hivatkozások kijavítására. Egy vagy több cella kiválasztásával és az eszköz használatával az Excel megmutatja a függő cellákat.

Az eszköz a Képletek lap Képlet-auditálási részében érhető el.

Az eszköz használatához kövesse az alábbi lépéseket:

1. Kattintson arra a cellára vagy cellákra, amelyek függőit nyomon szeretné követni.

2. Lépjen a „Képletek” fülre az Excel menüszalagon.

3. Kattintson a „Függők nyomon követése” gombra a „Képlet-auditálás” csoportban.

4. Tekintse át a függőséget mutató nyomkövető nyilat vagy nyilakat.

Az Excel nyilakat jelenít meg az aktív cellaértéktől az attól függő cellákig. Az alábbi képen látható kék nyíl azt mutatja, hogy a D6 cella függ a B2 cellától.

Trace Precedents eszköz

A precedensek az eltartottak ellentétei. A Trace Precedents eszköz segítségével kiválaszthat egy cellát egy képlettel, és megtekintheti az összes cellát, amelytől függ, azaz a precedenseket.

Az eszköz a Képletek lap Képlet-auditálási részében érhető el.

Az eszköz használatához kövesse az alábbi lépéseket:

1. Kattintson a vizsgálni kívánt képletet tartalmazó cellára.

2. Lépjen az Excel szalag Képlet lapjára.

3. Kattintson a 'Precedens nyomon követése' gombra a 'Képletvizsgálat' csoportban.

4. Tekintse át az Excel által megjelenített nyomkövetési nyilakat.

Az alábbi képen látható kék nyilak azt mutatják, hogy a D6 cella az A2:B5 cellák tartományától függ.

Mindkét eszköz használatával megtalálhatja azokat az Excel körkörös hivatkozásokat, amelyek nem azonnal látszanak. A következő lépés a probléma megoldása a kiválasztott cellában. Ezután folytathatja ugyanazon probléma keresését más cellákban.

2 módszer a körkörös hivatkozások eltávolítására az Excelben

Két fő módszer létezik a körkörös hivatkozások feloldására:

• megtörve a körkörös hivatkozást

• alternatív képletek segítségével

Magyarázzuk el mindkét irányt.

1. A körlevél feltörése

A körkörös hivatkozás megszakítása az Excelben hatékony módja a probléma megoldásának. Ehhez azonosítania kell a körkörös hivatkozásban érintett cellákat, és ennek megfelelően módosítania kell képleteiket.

Ehhez kövesse a korábban leírt lépéseket a cellacím megkereséséhez.

Ezután meg kell vizsgálnia a képletet a Képletsávban. Keressen hivatkozást magára a cellára.

A körkörös hivatkozás első példájában a C1 cellában lévő összeadási képlet magára a cellára utalt:

=A1 + B1 + C1

Ez a végső kiegészítés eltávolításával javítható. Ha ez a probléma megoldódott, keressen további körleveleket, és foglalkozzon velük.

2. Használjon alternatív képleteket

A körkörös hivatkozások leggyakoribb okai a cellák összegzése vagy átlagolása. Az a hiba, hogy az eredménycellát belefoglaljuk a képletbe.

Előző példánkban a képlet összeadási operátorokat használt a cellák tartományának összegének kiszámításához. Az alternatív módszer a SUM Excel függvény használata.

A példa javításához cserélje ki a képletet a következőre:

=SZUM(A1:B1)

Hasonlóképpen átlagolhat egy tartományt az alábbi képlettel: =(A1+B1)/2. Az AVERAGE függvény azonban kisebb valószínűséggel vezet hibához.

=ÁTLAG(A1:B1)

5 tipp a körkörös hivatkozások megelőzésére

A körkörös hivatkozások elkerülése érdekében a Microsoft Excelben elengedhetetlen, hogy világosan megértse, hogyan hatnak egymásra a képletek az Excel-lapon.

Ha azonosítja a sejtek közötti kapcsolatokat, és tisztában van ezeknek a kapcsolatoknak a működésével, elkerülheti a körképletek létrehozását.

Íme az öt legjobb tippünk a körkörös hivatkozások megelőzésére.

1. Legyen óvatos a tartományok kiválasztásakor

Tapasztalataim szerint a körkörös hivatkozások egyik leggyakoribb oka az, amikor a felhasználók gyorsan választanak ki tartományokat a képleteikbe felvenni.

Ha függőlegesen és/vagy vízszintesen csúsztat a cellák csoportosításához, könnyen kijelölhet még egy cellát, amely a megfelelő tartomány mellett található. Ha ez a cella egyben az eredménycella is, akkor véletlenül körkörös hivatkozást hozott létre.

Ezt a hibát általában nagy adathalmazokkal való munka során követik el, például khi-négyzet tesztek futtatásakor. A megoldás az, hogy körültekintően válassza ki a tartományt.

2. Gondosan tervezze meg képleteit

Mielőtt beírná a képleteket, gondolja át a számítások mögött meghúzódó logikát, és azt, hogy ezek hogyan folyjanak át a cellákon.

A számításokat előre feltérképezheti diagramkészítő eszközök vagy tábla segítségével.

Ez segíthet azonosítani minden lehetséges hurkot, amely körkörös hivatkozásokhoz vezethet.

3. Használjon alternatív képleteket

Bizonyos funkciók, mint például az INDIRECT vagy az OFFSET , véletlenül körkörös hivatkozásokat hozhatnak létre, ha nem megfelelően használják őket.

Fontolja meg olyan alternatív funkciók használatát, amelyek hasonló funkcionalitást biztosítanak a körkörös hivatkozások kockázata nélkül.

4. Bontsa fel az összetett képleteket kisebb részekre!

Ahelyett, hogy egyetlen összetett képletet hozna létre több beágyazott függvénysel, fontolja meg a számítások felosztását kisebb, jobban kezelhető részekre.

Ez nemcsak megkönnyíti a munkafüzet megértését és karbantartását, hanem csökkenti a körkörös hivatkozás bevezetésének esélyét is.

5. Javítsa ki a problémákat menet közben

Ha nagy vagy bonyolult munkafüzetekkel dolgozik, elengedhetetlen a körkörös hivatkozások figyelése is. Ahelyett, hogy figyelmen kívül hagyná a problémákat, amíg nagy mennyiségű adat és képlet össze nem áll, jobb, ha minden hibát azonnal kijavít.

Dönthet úgy, hogy egy rövid ideig figyelmen kívül hagyja a körkörös hivatkozási figyelmeztető üzenetet. Használja azonban a Hibaellenőrzés funkciót, hogy túl sokáig utolérje a meglévő körkörös hivatkozásokat.

Ez megkönnyíti a javításukat, és megakadályozza a munkafüzetre gyakorolt ​​hatásukat.

Hogyan lehet engedélyezni az iteratív számítást, és miért baj?

Általában szeretné elkerülni a körkörös hivatkozásokat a munkalapon. Vannak azonban olyan esetek, amikor szükség van rájuk.

Például egyes műszaki vagy tudományos számításokhoz egy korábbi számítás eredményét kell használni. Ezt iteratív megoldásnak nevezik.

Minden ciklus egy adott küszöbérték vagy cél eléréséig finomítja a számítás eredményeit.

Ezekben az esetekben elengedhetetlen az Iteratív számítások engedélyezése az Excel beállításaiban, hogy lehetővé tegye a kívánt körkörös hivatkozásokat. Ez lehetővé teszi a szoftver számára, hogy meghatározott számú alkalommal megismételje a számításokat, konvergálva a megoldáshoz.

Kövesse az alábbi lépéseket egy körkörös hivatkozás létrehozásához az Excel-fájlban:

1. Kattintson a Fájl fülre.

2. Válassza az Opciók lehetőséget.

3. Válassza ki a Képletek kategóriát.

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.

5. Módosítsa a számot a maximális iteráció mezőben, vagy hagyja az alapértelmezett 100-at.

6. Használja a maximális változtatási mezőt az iterációk csökkentéséhez az igényeinek megfelelően.