Üks levinumaid veahoiatusi, mida kasutajad Excelis kohtavad, on „ringiviite“. Tuhandetel kasutajatel on sama probleem ja see ilmneb siis, kui valem viitab otse või kaudselt tagasi oma lahtrile, põhjustades lõputu arvutusahela.
Näiteks on teil lahtrites B1 ja B2 kaks väärtust. Kui valem =B1+B2 sisestada B2-sse, loob see ringviite; B2 valem arvutab end korduvalt ümber, sest iga kord, kui see arvutab, on B2 väärtus muutunud.
Enamik ringviiteid on tahtmatud vead; Excel hoiatab teid nende eest. Siiski on ette nähtud ka ringviited, mida kasutatakse iteratiivsete arvutuste tegemiseks. Tahtmatud ringikujulised viited teie töölehel võivad põhjustada valemi arvutamise valesti.
Seetõttu selgitame selles artiklis kõike, mida peate teadma ringviite kohta ning kuidas Excelis ringviiteid leida, parandada, eemaldada ja kasutada.
Kuidas Excelis ringviiteid leida ja käsitleda
Exceliga töötades kohtame mõnikord ringikujulisi viitevigu, mis tekivad siis, kui sisestate valemi, mis sisaldab lahtrit, kus teie valem asub. Põhimõtteliselt juhtub see siis, kui teie valem proovib ennast arvutada.
Näiteks on teil lahtris A1:A4 arvude veerg ja te kasutate lahtris A5 funktsiooni SUM (=SUM(A1:A5)). Lahter A5 viitab otseselt tema enda lahtrile, mis on vale. Seetõttu saate järgmise ringikujulise viitehoiatuse:
Kui olete saanud ülaltoodud hoiatusteate, võite tõrke kohta lisateabe saamiseks klõpsata nupul „Abi” või sulgeda tõrketeate aken, klõpsates nuppu „OK” või „X”, ja saada tulemuseks „0”.
Mõnikord võivad ümmargused viiteahelad põhjustada arvutuse kokkujooksmist või aeglustada töölehe jõudlust. Ringviide võib põhjustada ka mitmeid muid probleeme, mis ei ilmne kohe. Seetõttu on kõige parem neid vältida.
Otsesed ja kaudsed ringkirjad
Ringkirjalised viited võib jagada kahte tüüpi: otsesed ringviited ja kaudsed ringviited.
Otsene viide
Otsene ringikujuline viide on üsna lihtne. Kui valem viitab otse oma lahtrile, ilmub otsene ringviite hoiatusteade.
Allolevas näites viitab lahtris A2 olev valem otseselt tema enda lahtrile (A2).
Kui hoiatusteade ilmub, võite klõpsata "OK", kuid tulemuseks on ainult "0".
Kaudne ringkiri
Kaudne ringikujuline viide Excelis ilmneb siis, kui väärtus valemis viitab tagasi oma lahtrile, kuid mitte otse. Teisisõnu, ringikujulise viite saab moodustada kahe üksteisele viitava lahtri abil.
Selgitame selle lihtsa näitega.
Nüüd algab väärtus A1-st, mille väärtus on 20.
Järgmisena viitab lahter C3 lahtrile A1.
Seejärel viitab lahter A5 lahtrile C3.
Nüüd asendage väärtus 20 lahtris A1 allpool näidatud valemiga. Iga teine lahter sõltub rakust A1. Kui kasutate A1-s mis tahes muu eelmise valemilahtri viidet, põhjustab see ringviitehoiatuse. Kuna A1 valem viitab lahtrile A5, mis viitab lahtrile C3, ja lahter C3 viitab tagasi lahtrile A1, seega ümmargune viide.
Kui klõpsate nupul „OK”, on lahtris A1 väärtus 0 ja Excel loob lingitud rea, mis näitab jälgimise pretsedente ja jälgimissõltujaid, nagu allpool näidatud. Seda funktsiooni saame kasutada ringikujuliste viidete hõlpsaks leidmiseks ja parandamiseks/eemaldamiseks.
Kuidas lubada / keelata ringikujulisi viiteid Excelis
Vaikimisi on iteratiivsed arvutused Excelis välja lülitatud (keelatud). Iteratiivsed arvutused on korduvad arvutused, kuni see vastab konkreetsele tingimusele. Kui see on keelatud, kuvab Excel ringikujulise viitesõnumi ja tagastab tulemuseks 0.
Kuid mõnikord on tsükli arvutamiseks vaja ringikujulisi viiteid. Ringviite kasutamiseks peate lubama oma Excelis iteratiivsed arvutused ja see võimaldab teil arvutusi teha. Nüüd näitame teile, kuidas saate iteratiivseid arvutusi lubada või keelata.
Rakendustes Excel 2010, Excel 2013, Excel 2016, Excel 2019 ja Microsoft 365 avage Exceli vasakus ülanurgas vahekaart „Fail” ja seejärel klõpsake vasakpoolsel paanil nuppu „Valikud”.
Minge Exceli suvandite aknas vahekaardile "Valem" ja märkige jaotises "Arvutusvalikud" märkeruut "Luba iteratiivne arvutus". Seejärel klõpsake muudatuste salvestamiseks nuppu "OK".
See võimaldab iteratiivset arvutamist ja seega ringikujulist viidet.
Selle saavutamiseks Exceli varasemates versioonides toimige järgmiselt.
- Programmis Excel 2007 klõpsake nuppu Office > Exceli suvandid > Valemid > iteratsiooniala.
- Excel 2003 ja varasemates versioonides peate avama menüü Menüü > Tööriistad > Valikud > vahekaart Arvutus.
Maksimaalsed iteratsioonid ja maksimaalsed muudatused
Kui olete iteratiivsed arvutused lubanud, saate iteratiivseid arvutusi juhtida, määrates jaotises Luba iteratiivne arvutus kaks võimalust, nagu on näidatud alloleval ekraanipildil.
- Maksimaalsed iteratsioonid – See arv määrab, mitu korda peab valem enne lõpptulemuse esitamist ümber arvutama. Vaikeväärtus on 100. Kui muudate selle väärtuseks 50, kordab Excel arvutusi 50 korda enne lõpliku tulemuse andmist. Pidage meeles, et mida suurem on iteratsioonide arv, seda rohkem ressursse ja aega kulub arvutamiseks.
- Maksimaalne muutus – See määrab arvutustulemuste vahelise maksimaalse muutuse. See väärtus määrab tulemuse täpsuse. Mida väiksem on see arv, seda täpsem on tulemus ja seda kauem võtab töölehe arvutamine aega.
Kui iteratiivsete arvutuste suvand on lubatud, ei saa te hoiatust alati, kui teie töölehel on ümmargune viide. Lubage interaktiivne arvutus ainult siis, kui see on hädavajalik.
Leidke Excelis ringviite
Oletame, et teil on suur andmestik ja saate ümmarguse viitehoiatuse, peate selle parandamiseks siiski välja selgitama, kus (mis lahtris) viga ilmnes. Ringikujuliste viidete leidmiseks Excelis toimige järgmiselt.
Veakontrolli tööriista kasutamine
Esmalt avage tööleht, kus ümmargune viide on toimunud. Minge vahekaardile "Valem", klõpsake tööriista "Veakontroll" kõrval olevat noolt. Seejärel hõljutage kursorit valiku „Circular References” kohal. Excel kuvab teile loendi kõigist lahtritest, mis on seotud ringviitega, nagu allpool näidatud.
Klõpsake loendis soovitud lahtri aadressi ja see suunab teid probleemi lahendamiseks selle lahtri aadressile.
Olekuriba kasutamine
Ringikujulise viite leiate ka olekuribalt. Exceli olekuribal kuvatakse uusim lahtri aadress koos ringikujulise viitega, näiteks „Circular References: B6” (vt allolevat ekraanipilti).
Ringviite käsitlemisel peaksite teadma teatud asju:
- Olekuribal ei kuvata ümmarguse viitelahtri aadressi, kui suvand Iteratiivne arvutamine on lubatud, seega peate selle keelama, enne kui hakkate ringikujulisi viiteid töövihikust otsima.
- Kui aktiivsel lehel ringviiteid ei leita, kuvatakse olekuribal ainult „Circular References” ilma lahtri aadressita.
- Saate ümmarguse viiteviipa ainult üks kord ja pärast „OK” klõpsamist ei kuvata seda järgmisel korral enam.
- Kui teie töövihikul on ümmargused viited, kuvatakse see viip iga kord, kui selle avate, kuni olete ümmarguse viite lahendanud või kuni iteratiivse arvutuse sisse lülitate.
Eemaldage Excelis ringikujuline viide
Circular viidete leidmine on lihtne, kuid selle parandamine pole nii lihtne. Kahjuks pole Excelis ühtegi valikut, mis võimaldaks teil kõik ringikujulised viited korraga eemaldada.
Ringviidete parandamiseks tuleb iga ringviite eraldi üles leida ja proovida seda muuta, ringvalem üldse eemaldada või mõne muuga asendada.
Mõnikord piisab lihtsate valemite puhul valemi parameetrite ümber seadistamisest, et see ei viitaks iseendale. Näiteks muutke valem jaotises B6 väärtuseks =SUM(B1:B5)*A5 (muutke B6 väärtuseks B5).
See tagastab arvutuse tulemuseks "756".
Juhtudel, kui Exceli ringviidet on raske leida, saate selle allikani jälitamiseks ja ükshaaval lahendamiseks kasutada funktsioone Trace Precedents ja Trace Dependents. Nool näitab, milliseid rakke aktiivne rakk mõjutab.
On kaks jälgimismeetodit, mis aitavad teil ringviiteid kustutada, näidates valemite ja lahtrite vahelisi seoseid.
Jälgimismeetoditele juurdepääsemiseks minge vahekaardile „Valemid“ ja seejärel klõpsake rühmas Valemi auditeerimine „Jälgimispretsedendid“ või „Jälgimissõltuvad“.
Trace Pretsedents
Kui valite selle suvandi, jälgib see aktiivse lahtri väärtust mõjutavaid lahtreid tagasi. See joonistab sinise joone, mis näitab, millised lahtrid mõjutavad praegust lahtrit. Otseteeklahv jälgimise pretsedentide kasutamiseks on Alt + T U T.
Allolevas näites näitab sinine nool lahtreid, mis mõjutavad B6 väärtust, on B1:B6 ja A5. Nagu allpool näete, on lahter B6 samuti valemi osa, mis muudab selle ümmarguse viitena ja paneb valemi tulemuseks tagastama "0".
Seda saab hõlpsasti parandada, asendades B6 B5-ga SUM-i argumendis: =SUM(B1:B5).
Jälgige ülalpeetavaid
Sõltuvate jälgimise funktsioon jälgib valitud lahtrist sõltuvaid lahtreid. See funktsioon tõmbab sinise joone, mis näitab, milliseid lahtreid valitud lahter mõjutab. See tähendab, et see kuvab, millised lahtrid sisaldavad aktiivsele lahtrile viitavaid valemeid. Ülalpeetavate kasutamise kiirklahv on Alt + T U D.
Järgmises näites mõjutab lahtrit D3 B4. Tulemuste saavutamiseks sõltub selle väärtus B4-st. Seega tõmbab jäljest sõltuv sinise joone B4-st D3-le, mis näitab, et D3 sõltub B4-st.
Tsirkulaarsete viidete tahtlik kasutamine Excelis
Ringviidete tahtlik kasutamine ei ole soovitatav, kuid harvadel juhtudel on vaja ringviiteid, et saada soovitud väljund.
Selgitame seda näite abil.
Alustuseks lubage oma Exceli töövihikus "Iteratiivne arvutamine". Kui olete iteratiivse arvutamise lubanud, võite hakata kasutama ringikujulisi viiteid.
Oletame, et ostate maja ja soovite anda oma agendile 2% vahendustasu maja kogumaksumusest. Lahtris B6 arvutatakse kogukulu ja B4-sse vahendustasu protsent (agenditasu). Vahendustasu arvestatakse kogumaksumusest ja kogukulu sisaldab vahendustasu. Kuna lahtrid B4 ja B6 sõltuvad üksteisest, loob see ringikujulise viite.
Sisestage lahtrisse B6 kogukulu arvutamiseks valem:
=SUM(B1:B4)
Kuna kogukulu sisaldab agenditasu, lisasime ülaltoodud valemisse B4.
2% agenditasu arvutamiseks sisestage see valem jaotisesse B4:
=B6*2%Nüüd sõltub lahtris B4 olev valem B6 väärtusest, et arvutada 2% kogutasust, ja lahtris B6 olev valem sõltub kogukulu (kaasa arvatud agenditasu) arvutamiseks B4-st, sellest ka ringkiri.
Kui iteratiivne arvutus on lubatud, ei anna Excel teile hoiatust ega tulemuseks 0. Selle asemel arvutatakse lahtrite B6 ja B4 tulemus nii, nagu ülal näidatud.
Iteratiivsete arvutuste valik on tavaliselt vaikimisi keelatud. Kui te seda sisse ei lülitanud ja kui sisestate valemi jaotisesse B4, loob ringviite. Excel väljastab hoiatuse ja kui klõpsate "OK", kuvatakse jälgimisnool.
see on kõik. See oli kõik, mida peate teadma Exceli ringviite kohta.