Az egyik leggyakoribb hibafigyelmeztetés, amellyel a felhasználók az Excelben találkoznak, a „Körlevél”. Felhasználók ezrei küzdenek ugyanezzel a problémával, és ez akkor fordul elő, amikor egy képlet közvetlenül vagy közvetve hivatkozik a saját cellájára, ami végtelen számítási ciklust okoz.
Például két érték van a B1 és B2 cellában. Ha a =B1+B2 képletet beírjuk a B2-be, akkor körkörös hivatkozást hoz létre; a B2-ben szereplő képlet többször újraszámolja magát, mert minden számításkor a B2-érték megváltozott.
A legtöbb körlevél nem szándékos hiba; Az Excel figyelmeztetni fogja ezekre. Vannak azonban tervezett körkörös hivatkozások is, amelyeket iteratív számításokhoz használnak. A munkalapon található nem szándékos körkörös hivatkozások a képlet helytelen kiszámítását okozhatják.
Ezért ebben a cikkben elmagyarázunk mindent, amit tudnia kell a körkörös hivatkozásokról, valamint arról, hogyan kereshet, javíthat, távolíthat el és használhat körkörös hivatkozásokat az Excelben.
A körkörös hivatkozások megtalálása és kezelése az Excelben
Az Excel használata során néha körkörös hivatkozási hibákkal találkozunk, amelyek akkor fordulnak elő, amikor olyan képletet ad meg, amely tartalmazza azt a cellát, amelyben a képlet található. Alapvetően ez akkor történik, amikor a képlet önmagát próbálja kiszámítani.
Például az A1:A4 cellában van egy számoszlop, és az A5 cellában a SZUM függvényt (=SZUM(A1:A5)) használja. Az A5 cella közvetlenül a saját cellájára utal, ami téves. Ezért a következő körkörös hivatkozási figyelmeztetést kapja:
Miután megkapta a fenti figyelmeztető üzenetet, kattintson a „Súgó” gombra, hogy többet megtudjon a hibáról, vagy zárja be a hibaüzenet ablakot az „OK” vagy az „X” gombra kattintva, és a „0” eredményt kapja.
Néha a körkörös referenciahurkok a számítás összeomlását vagy lelassíthatják a munkalap teljesítményét. A körkörös hivatkozás számos egyéb problémához is vezethet, amelyek nem lesznek azonnal nyilvánvalóak. Ezért a legjobb ezeket elkerülni.
Közvetlen és közvetett körlevélreferenciák
A körkörös hivatkozások két típusba sorolhatók: közvetlen körkörös hivatkozások és közvetett körkörös hivatkozások.
Közvetlen hivatkozás
A közvetlen körkörös hivatkozás nagyon egyszerű. A közvetlen körkörös hivatkozás figyelmeztető üzenete akkor jelenik meg, ha a képlet közvetlenül a saját cellájára hivatkozik.
Az alábbi példában az A2 cellában lévő képlet közvetlenül a saját cellájára utal (A2).
Amint megjelenik a figyelmeztető üzenet, kattintson az „OK” gombra, de csak „0” lesz.
Közvetett körlevél
Közvetett körkörös hivatkozás az Excelben akkor fordul elő, ha egy képletben lévő érték visszautal a saját cellájára, de nem közvetlenül. Más szavakkal, a körkörös hivatkozást két egymásra hivatkozó cella alkothatja.
Magyarázzuk meg ezzel az egyszerű példával.
Most az érték A1-től kezdődik, amelynek értéke 20.
Ezután a C3 cella az A1 cellára vonatkozik.
Ezután az A5 cella a C3 cellára vonatkozik.
Most cserélje ki az A1 cellában lévő 20 értéket az alábbi képlettel. Minden más cella az A1 cellától függ. Ha egy másik korábbi képletcellára hivatkozik az A1-ben, az körkörös hivatkozási figyelmeztetést fog okozni. Mivel az A1-ben lévő képlet az A5 cellára vonatkozik, amely a C3-ra, a C3-as cella pedig az A1-re utal, ezért a körkörös hivatkozás.
Ha rákattint az „OK” gombra, akkor az A1 cellában 0 lesz az érték, és az Excel egy csatolt sort hoz létre, amely a nyomkövetési előzményeket és a nyomkövetési függőket mutatja az alábbiak szerint. Ezzel a funkcióval könnyedén megtalálhatjuk és javíthatjuk/eltávolíthatjuk a körkörös hivatkozásokat.
A körkörös hivatkozások engedélyezése / letiltása az Excelben
Alapértelmezés szerint az iteratív számítások ki vannak kapcsolva (letiltva) az Excelben. Az iteratív számítások ismétlődő számítások mindaddig, amíg nem teljesítik egy adott feltételt. Ha le van tiltva, az Excel egy Körkörös hivatkozási üzenetet jelenít meg, és ennek eredményeként 0-t ad vissza.
Néha azonban körkörös hivatkozásokra van szükség a hurok kiszámításához. A körkörös hivatkozás használatához engedélyeznie kell az iteratív számításokat az Excelben, és ez lehetővé teszi a számítások elvégzését. Most pedig megmutatjuk, hogyan engedélyezheti vagy tilthatja le az iteratív számításokat.
Az Excel 2010, Excel 2013, Excel 2016, Excel 2019 és Microsoft 365 alkalmazásban lépjen a „Fájl” fülre az Excel bal felső sarkában, majd kattintson a „Beállítások” lehetőségre a bal oldali ablaktáblában.
Az Excel beállításai ablakban lépjen a „Képlet” fülre, és jelölje be az „Iteratív számítás engedélyezése” jelölőnégyzetet a „Számítási beállítások” részben. Ezután kattintson az „OK” gombra a módosítások mentéséhez.
Ez lehetővé teszi az iteratív számítást és ezáltal a körkörös hivatkozást.
Ennek eléréséhez az Excel korábbi verzióiban kövesse az alábbi lépéseket:
- Az Excel 2007 programban kattintson az Office > Excel-beállítások > Képletek > Iterációs terület elemre.
- Az Excel 2003 és korábbi verzióiban a Menü > Eszközök > Beállítások > Számítás fülre kell lépnie.
Maximális iterációk és maximális változtatási paraméterek
Miután engedélyezte az iteratív számításokat, az alábbi képernyőképen látható módon az Iteratív számítások engedélyezése szakaszban elérhető két lehetőség megadásával vezérelheti az iteratív számításokat.
- Maximális iterációk – Ez a szám megadja, hogy a képletnek hányszor kell újraszámolnia a végeredmény megadása előtt. Az alapértelmezett érték 100. Ha 50-re módosítja, az Excel 50-szer megismétli a számításokat, mielőtt megadja a végeredményt. Ne feledje, hogy minél több az iterációk száma, annál több erőforrást és időt vesz igénybe a számítás.
- Maximális változás – Meghatározza a számítási eredmények közötti maximális változást. Ez az érték határozza meg az eredmény pontosságát. Minél kisebb a szám, annál pontosabb lesz az eredmény, és annál tovább tart a munkalap kiszámítása.
Ha az iteratív számítások opció engedélyezve van, nem kap semmilyen figyelmeztetést, ha körkörös hivatkozás található a munkalapon. Csak akkor engedélyezze az interaktív számítást, ha az feltétlenül szükséges.
Keresse meg a Körhivatkozást az Excelben
Tegyük fel, hogy nagy adatkészlettel rendelkezik, és megkapta a körkörös hivatkozási figyelmeztetést, akkor is meg kell találnia, hogy hol (melyik cellában) fordult elő a hiba a hiba kijavításához. Ha körkörös hivatkozásokat szeretne keresni az Excelben, kövesse az alábbi lépéseket:
Hibaellenőrző eszköz használata
Először nyissa meg azt a munkalapot, ahol a körkörös hivatkozás történt. Lépjen a „Képlet” fülre, kattintson a „Hibaellenőrzés” eszköz melletti nyílra. Ezután csak vigye a kurzort a „Körhivatkozások” opció fölé, az Excel megmutatja a körkörös hivatkozásban érintett összes cella listáját, az alábbiak szerint.
Kattintson a listában a kívánt cellacímre, és az adott cellacímre irányítja a probléma megoldásához.
Az állapotsor használata
A körkörös hivatkozást az állapotsorban is megtalálhatja. Az Excel állapotsorán körkörös hivatkozással, például „Körhivatkozások: B6” (lásd az alábbi képernyőképet) jeleníti meg a legújabb cellacímet.
Vannak bizonyos dolgok, amelyeket tudnia kell a körlevél kezelésekor:
- Az állapotsoron nem jelenik meg a körkörös hivatkozási cella címe, ha az Iteratív számítás lehetőség be van kapcsolva, ezért le kell tiltania, mielőtt a munkafüzetben körkörös hivatkozásokat keresne.
- Ha az aktív lapon nem található körkörös hivatkozás, az állapotsoron csak a „Körhivatkozások” jelennek meg cellacím nélkül.
- Csak egyszer kap körkörös hivatkozási promptot, és miután az „OK” gombra kattint, a következő alkalommal nem jelenik meg újra.
- Ha a munkafüzet körkörös hivatkozásokat tartalmaz, akkor minden megnyitáskor megjelenik a prompt, amíg meg nem oldja a körkörös hivatkozást, vagy amíg be nem kapcsolja az iteratív számítást.
Távolítson el egy körkörös hivatkozást az Excelben
Könnyű hivatkozásokat találni, de a javítás nem olyan egyszerű. Sajnos az Excelben nincs olyan lehetőség, amely lehetővé tenné az összes körkörös hivatkozás egyidejű eltávolítását.
A körhivatkozások kijavításához minden egyes körhivatkozást külön kell megkeresni, és meg kell próbálni módosítani, teljesen eltávolítani a körképletet, vagy ki kell cserélni egy másikra.
Néha az egyszerű képleteknél mindössze annyit kell tennie, hogy újra beállítja a képlet paramétereit, hogy az ne hivatkozzon önmagára. Például módosítsa a képletet a B6-ban =SZUM(B1:B5)*A5-re (B6 módosítása B5-re).
A számítás eredményét „756”-ként adja vissza.
Azokban az esetekben, amikor egy Excel körkörös hivatkozást nehéz megtalálni, a Precedens nyomon követése és a Függőségek nyomon követése szolgáltatások segítségével visszakeresheti a forrásig, és egyenként megoldhatja. A nyíl mutatja, hogy mely cellákat érinti az aktív cella.
Két nyomkövetési módszer segíthet a körkörös hivatkozások törlésében a képletek és cellák közötti kapcsolatok megjelenítésével.
A nyomkövetési módszerek eléréséhez lépjen a „Képletek” fülre, majd kattintson a „Trace Precedents” (Nyomkövetési előzmények) vagy a „Trace Dependents” (Nyomkövetési előzmények) lehetőségre a Képlet-auditálás csoportban.
Trace Precedents
Ha ezt a lehetőséget választja, akkor visszaköveti az aktív cella értékét befolyásoló cellákat. Kék vonalat húz, jelezve, hogy mely cellák befolyásolják az aktuális cellát. A nyomkövetési precedensek használatához szükséges gyorsbillentyű a következő Alt + T U T.
Az alábbi példában a kék nyíl mutatja a B6 értéket befolyásoló cellákat: B1:B6 és A5. Amint alább látható, a B6 cella szintén a képlet része, ami körkörös hivatkozássá teszi, és a képlet „0”-t ad vissza.
Ez könnyen javítható, ha a B6-ot B5-re cseréli a SUM argumentumában: =SZUM(B1:B5).
Trace Dependents
A függő nyomkövetési funkció nyomon követi a kiválasztott cellától függő cellákat. Ez a szolgáltatás kék vonalat húz, jelezve, hogy a kiválasztott cella mely cellákat érinti. Ez azt jelenti, hogy megjeleníti, hogy mely cellák tartalmaznak olyan képleteket, amelyek az aktív cellára hivatkoznak. Az eltartottak használatának gyorsbillentyűje a Alt + T U D.
A következő példában a D3 cellát a B4 befolyásolja. A B4-től függ, hogy értéke hozzon eredményt. Ezért a nyomkövetéstől függő kék vonalat húz B4-től D3-ig, jelezve, hogy D3 függ a B4-től.
A körkörös hivatkozások szándékos használata az Excelben
A körkörös hivatkozások szándékos használata nem javasolt, de előfordulhatnak olyan ritka esetek, amikor szükség van egy körkörös hivatkozásra, hogy megkapja a kívánt kimenetet.
Magyarázzuk meg ezt egy példa segítségével.
Kezdésként engedélyezze az „Iteratív számítás” funkciót az Excel-munkafüzetben. Miután engedélyezte az iteratív számítást, elkezdheti használni a körkörös hivatkozásokat.
Tételezzük fel, hogy házat vásárol, és 2%-os jutalékot szeretne adni a ház teljes költségére az ügynökének. A teljes költséget a B6-os cellában, a jutalék százalékát (ügynöki díj) pedig a B4-ben számítja ki. A jutalék a teljes költségből kerül kiszámításra, és a teljes költség tartalmazza a jutalékot. Mivel a B4 és B6 cellák egymástól függenek, körkörös hivatkozást hoz létre.
Írja be a képletet a teljes költség kiszámításához a B6 cellában:
=SZUM(B1:B4)
Mivel a teljes költség az ügynöki díjat is tartalmazza, ezért a fenti képletben B4-et vettünk fel.
A 2%-os ügynöki díj kiszámításához írja be ezt a képletet a B4-be:
=B6*2%Most a B4 cellában lévő képlet a B6 értékétől függ a teljes díj 2%-ának kiszámításához, a B6 képlet pedig a B4-től függ a teljes költség kiszámításához (beleértve az ügynöki díjat is), ezért a körkörös hivatkozás.
Ha az iteratív számítás engedélyezve van, az Excel nem ad figyelmeztetést vagy 0-t az eredményben. Ehelyett a B6 és B4 cellák eredménye a fent látható módon kerül kiszámításra.
Az iteratív számítások beállítása alapértelmezés szerint általában le van tiltva. Ha nem kapcsolta be, és amikor beírja a képletet a B4-be, amely körkörös hivatkozást hoz létre. Az Excel figyelmeztetést küld, és amikor az „OK” gombra kattint, megjelenik a nyomkövető nyíl.
Ez az. Ez volt minden, amit tudnia kellett az Excel körkörös hivatkozásairól.