Kun työskentelet Excel -kaavojen kanssa, saatat joskus nähdä seuraavan varoituksen.
Tämä kehote kertoo, että laskentataulukossasi on pyöreä viittaus ja tämä voi johtaa kaavojen laskemiseen. Se pyytää sinua myös käsittelemään tätä pyöreää viittausongelmaa ja lajittelemaan sen.
Tässä opetusohjelmassa käsitän kaiken, mitä sinun tarvitsee tietää pyöreästä viittauksesta, sekä kuinka löytää ja poistaa pyöreitä viittauksia Excelissä.
Aloitetaan siis!
Mikä on pyöreä viittaus Excelissä?
Yksinkertaisesti sanottuna pyöreä viittaus tapahtuu, kun lopulta sinulla on kaava solussa - joka itsessään käyttää solua (johon se on syötetty) laskemiseen.
Yritän selittää tämän yksinkertaisella esimerkillä.
Oletetaan, että sinulla on tietojoukko solussa A1: A5 ja käytät alla olevaa kaavaa solussa A6:
= SUMMA (A1: A6)
Tämä antaa sinulle pyöreän viittausvaroituksen.
Tämä johtuu siitä, että haluat laskea yhteen solun A1: A6 arvot ja tuloksen pitäisi olla solussa A6.
Tämä luo silmukan, kun Excel vain lisää uutta arvoa soluun A6, joka muuttuu jatkuvasti (siis pyöreä viitesilmukka).
Kuinka löytää pyöreät viitteet Excelistä?
Vaikka pyöreän viittauksen varoituskehote voi kertoa sinulle, että se on laskentataulukossasi, se ei kerro, missä se tapahtuu ja mitkä soluviitteet aiheuttavat sen.
Joten jos yrität löytää ja käsitellä pyöreitä viittauksia laskentataulukosta, sinun on tiedettävä tapa löytää ne jollakin tavalla.
Alla on ohjeet pyöreän viitteen löytämiseen Excelistä:
- Aktivoi laskentataulukko, jossa on pyöreä viittaus
- Napsauta Kaavat -välilehteä
- Napsauta Kaavan muokkaus -ryhmässä avattavaa Virheentarkistus-kuvaketta (pieni alaspäin osoittava nuoli oikealla)
- Vie hiiri Ympyräviittaukset -vaihtoehdon päälle. Se näyttää solun, jonka laskentataulukossa on pyöreä viittaus
- Napsauta soluosoitetta (joka näkyy) ja se vie sinut laskentataulukon soluun.
Kun olet ratkaissut ongelman, voit jälleen suorittaa samat vaiheet yllä, ja se näyttää lisää soluviittauksia, joissa on pyöreä viittaus. Jos niitä ei ole, et näe soluviittausta,
Toinen nopea ja helppo tapa löytää pyöreä viittaus on tarkastella tilariviä. Sen vasemmassa osassa näkyy teksti Ympyräviite ja solun osoite.
On olemassa muutamia asioita, jotka sinun on tiedettävä, kun käytät pyöreitä viitteitä:
- Jos iteratiivinen laskenta on käytössä (käsitellään myöhemmin tässä opetusohjelmassa), tilarivillä ei näy pyöreää viitesolun osoitetta
- Jos pyöreä viittaus ei ole aktiivisessa taulukossa (mutta muissa saman työkirjan arkeissa), se näyttää vain ympyräviittauksen eikä solun osoitetta
- Jos saat pyöreän viittauksen varoituskehotteen kerran ja hylkäät sen, se ei näytä kehotetta uudelleen seuraavalla kerralla.
- Jos avaat työkirjan, jossa on pyöreä viittaus, se näyttää kehotteen heti, kun työkirja avautuu.
Kuinka poistaa pyöreä viittaus Excelistä?
Kun olet havainnut, että taulukossasi on pyöreitä viittauksia, on aika poistaa ne (ellet halua niiden olevan siellä jostain syystä).
Valitettavasti se ei ole niin helppoa kuin painaa poistonäppäintä. Koska nämä ovat riippuvaisia kaavoista ja jokainen kaava on erilainen, sinun on analysoitava tämä tapauskohtaisesti.
Jos kyse on vain siitä, että soluviittaus aiheuttaa ongelman virheellisesti, voit yksinkertaisesti korjata säätämällä viitettä.
Mutta joskus se ei ole niin yksinkertaista.
Pyöreä viittaus voi johtua myös useista soluista, jotka syötetään toisiinsa monilla tasoilla.
Annan sinulle esimerkin.
Alla on pyöreä viittaus solussa C6, mutta se ei ole vain yksinkertainen tapa viitata itseensä. Se on monitasoinen, jossa sen laskelmissa käyttämät solut viittaavat myös toisiinsa.
- Solun A6 kaavat ovat = SUMMA (A1: A5)+C6
- Kaava on solu C1 on = A6*0,1
- Kaava solussa C6 on = A6+C1
Yllä olevassa esimerkissä solun C6 tulos riippuu solun A6 ja C1 arvoista, jotka puolestaan ovat riippuvaisia solusta C6 (aiheuttaen siten pyöreän viitevirheen)
Ja jälleen, olen valinnut todella yksinkertaisen esimerkin vain demotarkoituksiin. Todellisuudessa nämä voivat olla melko vaikeita selvittää ja ehkä kaukana samasta laskentataulukosta tai jopa hajallaan useille laskentataulukoille.
Tällaisessa tapauksessa on yksi tapa tunnistaa ympyräviittausta aiheuttavat solut ja käsitellä niitä sitten.
Se käyttää Trace Precedents -vaihtoehtoa.
Alla on vaiheet, joilla löydetään ennakkotapauksia solujen löytämiseksi, jotka syöttävät solua, jolla on pyöreä viittaus:
- Valitse solu, jossa on pyöreä viittaus
- Napsauta Kaavat -välilehteä
- Napsauta Trace Precedents
Yllä olevat vaiheet näyttävät siniset nuolet, jotka kertovat, mitä soluja syötetään valitun solun kaavaan. Tällä tavalla voit tarkastella kaavoja ja soluja ja päästä eroon pyöreästä viittauksesta.
Jos työskentelet monimutkaisten rahoitusmallien kanssa, saattaa olla mahdollista, että nämä ennakkotapaukset ovat myös monitasoisia.
Tämä toimii hyvin, jos sinulla on kaikki saman laskentataulukon soluihin viittaavat kaavat. Jos se on useissa laskentataulukoissa, tämä menetelmä ei ole tehokas.
Iteratiivisten laskelmien ottaminen käyttöön/poistaminen käytöstä Excelissä
Kun solussa on pyöreä viittaus, saat ensin varoituksen alla olevan kuvan mukaisesti, ja jos suljet tämän valintaikkunan, tuloksena solussa on 0.
Tämä johtuu siitä, että kun on pyöreä viittaus, se on loputon silmukka ja Excel ei halua tarttua siihen. Palauttaa siis 0.
Mutta joissakin tapauksissa saatat todella haluta pyöreän viittauksen olevan aktiivinen ja tehdä pari iteraatiota. Tällaisessa tapauksessa äärettömän silmukan sijasta voit päättää, kuinka monta kertaa silmukka suoritetaan.
Tätä kutsutaan iteratiivinen laskenta Excelissä.
Seuraavat vaiheet ottavat käyttöön ja määrittävät iteratiiviset laskelmat Excelissä:
- Napsauta Tiedosto -välilehteä
- Napsauta Asetukset. Tämä avaa Excelin asetukset -valintaikkunan
- Valitse vasemmasta ruudusta Kaava
- Valitse Laskutusvaihtoehdot -osiossa Ota käyttöön iteratiivinen laskenta -ruutu. Tässä voit määrittää suurimmat iteroinnit ja suurimman muutosarvon
Se siitä! Yllä olevat vaiheet mahdollistavat iteratiivisen laskennan Excelissä.
Selitän myös nopeasti kaksi vaihtoehtoa iteratiivisessa laskennassa:
- Suurimmat iteraatiot: Tämä on enimmäismäärä, jonka haluat Excelin laskevan ennen lopullisen tuloksen antamista. Joten jos määrität tämän arvoksi 100, Excel suorittaa silmukan 100 kertaa ennen lopullisen tuloksen antamista.
- Suurin muutos: Tämä on suurin muutos, jota ellei saavuteta iteraatioiden välillä, laskenta keskeytetään. Oletusarvo on .001. Mitä pienempi tämä arvo, sitä tarkempi tulos olisi.
Muista, että mitä useamman kerran iteraatiot suoritetaan, sitä enemmän aikaa ja resursseja tarvitaan Excelin suorittamiseen. Jos pidät suurimmat iteroinnit korkeina, Excel voi hidastua tai kaatua.
Huomautus: Kun iteratiiviset laskelmat ovat käytössä, Excel ei näytä ympyräviittausvaroitusta ja näyttää sen nyt myös tilarivillä.Käytä tarkoituksella pyöreitä viittauksia
Useimmissa tapauksissa pyöreä viittaus laskentataulukossasi olisi virhe. Tästä syystä Excel näyttää kehotteen, joka sanoo: "Kokeile poistaa tai muuttaa näitä viittauksia tai siirtää kaavoja eri soluihin."
Mutta voi olla joitakin erityistapauksia, joissa tarvitset pyöreän viitteen, jotta voit saada halutun tuloksen.
Yksi tällainen tapaus, jonka olen jo kirjoittanut siitä, että saan aikaleiman solun soluun Excelissä.
Oletetaan esimerkiksi, että haluat luoda kaavan siten, että jokainen merkintä tehdään sarakkeen A soluun, aikaleima näkyy sarakkeessa B (kuten alla):
Voit lisätä aikaleiman helposti alla olevan kaavan avulla:
= JOS (A2 "", JOS (B2 "", B2, NYT ()), "")
Yllä olevan kaavan ongelma on, että se päivittää kaikki aikaleimat heti, kun laskentataulukkoon tehdään muutoksia tai jos laskentataulukko avataan uudelleen (koska NYT -kaava on epävakaa)
Voit kiertää tämän ongelman käyttämällä pyöreää viitemenetelmää. Käytä samaa kaavaa, mutta ota iteratiivinen laskenta käyttöön.
On myös joitain muita tapauksia, joissa pyritään käyttämään pyöreää viitettä (löydät yhden esimerkin täältä).
Huomautus: Vaikka joissakin tapauksissa voit käyttää pyöreää viitettä, minusta on parasta välttää sen käyttöä. Pyöreät viitteet voivat myös heikentää työkirjasi suorituskykyä ja hidastaa sitä. Harvoissa tapauksissa, joissa tarvitset sitä, käytän aina mieluummin VBA -koodeja työn tekemiseen.Toivottavasti pidit tätä opetusohjelmaa hyödyllisenä!
Muut Excel -opetusohjelmat, joista saatat olla hyötyä:
- #VIITE! Virhe Excelissä; Kuinka korjata viitevirhe!
- Excel VBA -virheiden käsittely
- Käytä IFERRORia VLOOKUPin kanssa päästäksesi eroon #N/A -virheistä
- Kuinka viitata toiseen taulukkoon tai työkirjaan Excelissä (esimerkkien avulla)
- Absoluuttiset, suhteelliset ja sekoitetut soluviitteet Excelissä
