Ringikujulise viite leidmine Excelis (kiire ja lihtne)

Exceli valemitega töötades võite mõnikord näha järgmist hoiatusteadet.

See viip ütleb teile, et teie töölehel on ümmargune viide ja see võib viia valede arvutusteni. Samuti palub see teil selle ümmarguse viite probleemiga tegeleda ja sorteerida.

Selles õpetuses käsitlen kõike, mida peate teadma ümmarguse viite kohta ja ka kuidas Excelis ringviiteid leida ja eemaldada.

Nii et alustame!

Mis on ringviide Excelis?

Lihtsamalt öeldes juhtub ümmargune viide, kui teil on lahtris valem - mis iseenesest kasutab arvutamiseks lahtrit (kuhu see on sisestatud).

Proovin seda lihtsa näitega selgitada.

Oletame, et teil on andmestik lahtris A1: A5 ja kasutate lahtris A6 järgmist valemit:

= SUM (A1: A6)

See annab teile ümmarguse viitehoiatuse.

Seda seetõttu, et soovite liita väärtused lahtris A1: A6 ja tulemus peaks olema lahtris A6.

See loob tsükli, kuna Excel lisab lihtsalt lahtrisse A6 uue väärtuse, mis muutub pidevalt (seega ringikujuline võrdlusahel).

Kuidas leida Excelis ümmargusi viiteid?

Kuigi ümmarguse viite hoiatusviisiga saab piisavalt öelda, et see on teie töölehel olemas, ei ütle see teile, kus see toimub ja millised lahtriviited seda põhjustavad.

Nii et kui proovite töölehelt ringikujulisi viiteid leida ja nendega hakkama saada, peate teadma viisi nende leidmiseks.

Ümmarguse viite leidmiseks Excelis toimige järgmiselt.

1. Aktiveerige ümmarguse viitega tööleht
2. Klõpsake vahekaarti Valemid
3. Klõpsake rühmas Valemide redigeerimine rippmenüüd Vea kontrollimine (väike allapoole suunatud nool paremal)
4. Hõljutage kursorit suvandi Ringviited kohal. See näitab teile lahtrit, millel on töölehel ümmargune viide
5. Klõpsake lahtri aadressil (see kuvatakse) ja see viib teid töölehe lahtrisse.

Kui olete probleemiga tegelenud, saate uuesti järgida samu ülaltoodud samme ja see näitab rohkem lahtriviiteid, millel on ümmargune viide. Kui seda pole, ei näe te ühtegi lahtri viidet,

Veel üks kiire ja lihtne viis ümmarguse viite leidmiseks on olekuriba. Selle vasakus osas kuvatakse teile tekst Ümmargune viide koos lahtri aadressiga.

Ümmarguste viidetega töötamisel peate teadma mõnda asja.

1. Kui iteratiivne arvutus on lubatud (seda õpetust käsitletakse hiljem), ei näita olekuriba ümmarguse viite lahtri aadressi
2. Kui ümmargune viide pole aktiivsel lehel (kuid sama töövihiku teistel lehtedel), näitab see ainult ringviidet, mitte lahtri aadressi
3. Juhul, kui saate ümmarguse viite hoiatusviiba üks kord ja loobute sellest, ei kuvata seda järgmisel korral uuesti.
4. Kui avate ümmarguse viitega töövihiku, kuvatakse see kohe, kui töövihik avaneb.

Kuidas eemaldada ümmargune viide Excelist?

Kui olete tuvastanud, et teie lehel on ümmargused viited, on aeg need eemaldada (kui te ei soovi, et need mingil põhjusel seal oleksid).

Kahjuks pole see nii lihtne kui kustutusklahvi vajutamine. Kuna need sõltuvad valemitest ja iga valem on erinev, peate seda analüüsima iga juhtumi puhul eraldi.

Juhul, kui küsimus on selles, et lahtri viide põhjustab probleemi ekslikult, saate seda lihtsalt parandada, viite kohandades.

Kuid mõnikord pole see nii lihtne.

Ümmarguse viite võib põhjustada ka mitu rakku, mis mitmel tasandil üksteisesse söödetakse.

Näitan teile näidet.

Allpool on lahtris C6 ümmargune viide, kuid see pole lihtsalt eneseviitamise juhtum. See on mitmetasandiline, kus ka arvutustes kasutatavad lahtrid viitavad üksteisele.

• Lahtri A6 valemid on = SUM (A1: A5)+C6
• Valem on lahter C1 on = A6*0,1
• Lahtri C6 valem on = A6+C1

Ülaltoodud näites sõltub lahtri C6 tulemus lahtri A6 ja C1 väärtustest, mis omakorda sõltuvad lahtrist C6 (põhjustades seega ringviite vea)

Ja jällegi olen valinud tõeliselt lihtsa näite just demo eesmärgil. Tegelikkuses võib neid olla üsna raske välja mõelda ja võib -olla samal töölehel kaugel või isegi hajutatud mitmele töölehele.

Sellisel juhul on ringikujulisi viiteid põhjustavate rakkude tuvastamiseks ja seejärel ravimiseks üks võimalus.

Seda kasutades suvandit Trace Precedents.

Allpool on toodud sammud, kuidas kasutada jälgede pretsedente, et leida rakke, mis ümmarguse viitega rakku toidavad.

1. Valige lahter, millel on ümmargune viide
2. Klõpsake vahekaarti Valemid
3. Klõpsake suvandil Trace Precedents

Ülaltoodud sammud näitaksid siniseid nooli, mis näitavad teile, millised rakud sisestavad valitud lahtrisse valemit. Nii saate vaadata valemeid ja lahtreid ning vabaneda ümmargusest viitest.

Kui töötate keeruliste finantsmudelitega, võib olla võimalik, et ka need pretsedendid ulatuvad mitmel tasandil.

See toimib hästi, kui teil on kõik töölehe lahtritele viitavad valemid. Kui see on mitmes töölehes, pole see meetod efektiivne.

Iteratiivsete arvutuste lubamine/keelamine Excelis

Kui teil on lahtris ümmargune viide, saate kõigepealt hoiatusviiba, nagu allpool näidatud, ja kui selle dialoogiboksi sulgete, annab see lahtris tulemuseks 0.

Selle põhjuseks on asjaolu, et ümmarguse viite olemasolul on see lõputu silmus ja Excel ei taha sellest kinni pidada. Nii et see tagastab 0.

Kuid mõnel juhul võite tegelikult soovida, et ümmargune viide oleks aktiivne ja teeks paar kordust. Sellisel juhul saate lõpmatu ahela asemel ja saate otsustada, mitu korda tsükkel tuleks käivitada.

Seda nimetatakse iteratiivne arvutus Excelis.

Siin on sammud iteratiivsete arvutuste lubamiseks ja konfigureerimiseks Excelis:

1. Klõpsake vahekaarti Fail
2. Klõpsake suvandil Valikud. See avab dialoogiboksi Exceli suvandid
3. Valige vasakpoolsel paanil valem
4. Märkige jaotises Arvutusvalikud ruut „Luba iteratiivne arvutus”. Siin saate määrata maksimaalsed iteratsioonid ja maksimaalse muutuse väärtuse

See on kõik! Ülaltoodud sammud võimaldaksid Excelis iteratiivset arvutamist.

Lubage mul selgitada ka iteratiivse arvutuse kahte võimalust:

• Maksimaalsed iteratsioonid: See on maksimaalne arv kordi, mida soovite Excelil enne lõpptulemuse andmist arvutada. Nii et kui määrate selle väärtuseks 100, käivitab Excel enne lõpptulemuse andmist tsüklit 100 korda.
• Maksimaalne muutus: See on maksimaalne muutus, mida juhul, kui seda iteratsioonide vahel ei saavutata, arvutamine peatatakse. Vaikimisi on väärtus .001. Mida madalam see väärtus, seda täpsem oleks tulemus.

Pidage meeles, et mida rohkem kordusi käia, seda rohkem aega ja ressursse kulub Excelil selle tegemiseks. Kui hoiate maksimaalseid iteratsioone kõrgel, võib see põhjustada teie Exceli aeglustumise või krahhi.

Märkus. Kui iteratiivsed arvutused on lubatud, ei näita Excel teile ümmarguse viite hoiatust ja kuvab selle nüüd ka olekuribal.

Ümmarguste viidete teadlik kasutamine

Enamikul juhtudel on ümmarguse viite olemasolu teie töölehel viga. Seetõttu kuvab Excel teile viiba, mis ütleb: "Proovige need viited eemaldada või muuta või teisaldada valemid erinevatesse lahtritesse."

Kuid võib juhtuda, et soovitud tulemuse saamiseks on vaja ümmargust viidet.

Ühest sellisest konkreetsest juhtumist olen juba kirjutanud selle kohta, et saan ajatemplit Exceli lahtri lahtrisse.

Oletame näiteks, et soovite luua valemi nii, et iga kirje tehakse veeru A lahtrisse, ajatempel kuvatakse veerus B (nagu allpool näidatud):

Kuigi saate hõlpsasti sisestada ajatempli, kasutades järgmist valemit:

= IF (A2 "", IF (B2 "", B2, NOW ()), "")

Ülaltoodud valemiga on probleem see, et see ajakohastab kõiki ajatempleid kohe, kui töölehel tehakse muudatusi või kui tööleht avatakse uuesti (kuna KOHE valem on muutlik)

Selle probleemi lahendamiseks võite kasutada ümmarguse viite meetodit. Kasutage sama valemit, kuid lubage iteratiivne arvutus.

On ka teisi juhtumeid, kus soovitakse kasutada ümmargust viidet (ühe näite leiate siit).

Märkus. Ehkki mõnedel juhtudel võite kasutada ümmargust viidet, leian, et on parem selle kasutamist vältida. Ümmargused viited võivad samuti teie töövihiku toimivust kahjustada ja aeglustada. Harvadel juhtudel, kui seda vajate, eelistan alati töö tegemiseks kasutada VBA -koode.

Loodan, et sellest õpetusest oli teile kasu!

Teised Exceli õpetused, mis võivad teile kasulikud olla:

• #REF! Viga Excelis; Kuidas parandada viiteviga!
• Exceli VBA vigade käsitlemine
• Kasutage IFERROR -i koos VLOOKUP -iga, et vabaneda #puuduvatest vigadest
• Kuidas viidata teisele lehele või töövihikule Excelis (näidetega)
• Absoluutsed, suhtelised ja segarakulised viited Excelis