Yksi yleisimmistä virhevaroituksista, joita käyttäjät kohtaavat Excelissä, on "Circular Reference". Tuhansilla käyttäjillä on sama ongelma, ja se ilmenee, kun kaava viittaa suoraan tai epäsuorasti takaisin omaan soluunsa, mikä aiheuttaa loputtoman laskentasilmukan.
Esimerkiksi soluissa B1 ja B2 on kaksi arvoa. Kun kaava =B1+B2 syötetään B2:een, se luo ympyränmuotoisen viittauksen; B2:n kaava laskee itsensä toistuvasti uudelleen, koska joka kerta kun se laskee, B2-arvo on muuttunut.
Useimmat pyöreät viittaukset ovat tahattomia virheitä; Excel varoittaa sinua näistä. Kuitenkin on myös tarkoitettu ympyräviitteitä, joita käytetään iteratiivisten laskelmien tekemiseen. Tahatut pyöreät viittaukset laskentataulukkoon voivat saada kaavan laskemaan väärin.
Siksi tässä artikkelissa selitämme kaiken, mitä sinun on tiedettävä pyöreistä viittauksista sekä kuinka löytää, korjata, poistaa ja käyttää pyöreitä viittauksia Excelissä.
Pyöreän viittauksen löytäminen ja käsitteleminen Excelissä
Kun työskentelemme Excelin kanssa, kohtaamme joskus pyöreitä viitevirheitä, jotka tapahtuvat, kun syötät kaavan, joka sisältää solun, jossa kaavasi sijaitsee. Periaatteessa se tapahtuu, kun kaava yrittää laskea itsensä.
Esimerkiksi solussa A1:A4 on numerosarake ja käytät SUMMA-funktiota (=SUMMA(A1:A5)) solussa A5. Solu A5 viittaa suoraan sen omaan soluun, mikä on väärin. Tästä syystä saat seuraavan pyöreän viitevaroituksen:
Kun olet saanut yllä olevan varoitusviestin, voit napsauttaa 'Ohje'-painiketta saadaksesi lisätietoja virheestä tai sulkea virheilmoitusikkunan napsauttamalla joko 'OK' tai 'X'-painiketta ja saat tulokseksi '0'.
Joskus pyöreät viitesilmukat voivat aiheuttaa laskutoimituksen kaatumisen tai hidastaa laskentataulukon suorituskykyä. Pyöreä viittaus voi johtaa myös useisiin muihin ongelmiin, jotka eivät tule heti ilmi. Näitä on siis parasta välttää.
Suorat ja epäsuorat kiertoviitteet
Kiertoviittaukset voidaan luokitella kahteen tyyppiin: suorat kiertokirjeviitteet ja epäsuorat kiertoviittaukset.
Suora viite
Suora pyöreä viittaus on melko yksinkertainen. Suoran pyöreän viittauksen varoitusviesti tulee näkyviin, kun kaava viittaa suoraan takaisin omaan soluunsa.
Alla olevassa esimerkissä solun A2 kaava viittaa suoraan sen omaan soluun (A2).
Kun varoitusviesti tulee näkyviin, voit napsauttaa "OK", mutta se johtaa vain "0".
Epäsuora kiertokirje
Epäsuora pyöreä viittaus Excelissä tapahtuu, kun kaavan arvo viittaa takaisin omaan soluunsa, mutta ei suoraan. Toisin sanoen ympyräviittaus voidaan muodostaa kahdella solulla, jotka viittaavat toisiinsa.
Selitetäänpä tällä yksinkertaisella esimerkillä.
Nyt arvo alkaa arvosta A1, jonka arvo on 20.
Seuraavaksi solu C3 viittaa soluun A1.
Sitten solu A5 viittaa soluun C3.
Korvaa nyt solun A1 arvo 20 alla esitetyllä kaavalla. Jokainen toinen solu on riippuvainen solusta A1. Kun käytät viittausta johonkin muuhun aikaisempaan kaavasoluun A1:ssä, se aiheuttaa pyöreän viittausvaroituksen. Koska A1:n kaava viittaa soluun A5, joka viittaa C3:een, ja solu C3 viittaa takaisin A1:een, tästä syystä pyöreä viittaus.
Kun napsautat 'OK', soluun A1 tulee arvo 0 ja Excel luo linkitetyn rivin, joka näyttää jäljitysprecedentit ja jäljitysriippuvat alla olevan kuvan mukaisesti. Tämän ominaisuuden avulla voimme helposti löytää ja korjata/poistaa pyöreitä viittauksia.
Kuinka ottaa pyöreät viittaukset käyttöön / poistaa ne käytöstä Excelissä
Iteratiiviset laskelmat ovat oletusarvoisesti pois päältä (pois käytöstä) Excelissä. Iteratiiviset laskelmat ovat toistuvia laskelmia, kunnes ne täyttävät tietyn ehdon. Kun se on poistettu käytöstä, Excel näyttää Circular Reference -sanoman ja palauttaa tuloksena 0:n.
Joskus silmukan laskemiseen tarvitaan kuitenkin pyöreitä viittauksia. Jotta voit käyttää pyöreää viittausta, sinun on otettava iteratiiviset laskelmat käyttöön Excelissä, jolloin voit suorittaa laskelmat. Nyt näytämme sinulle, kuinka voit ottaa iteratiiviset laskelmat käyttöön tai poistaa ne käytöstä.
Siirry Excel 2010:ssä, Excel 2013:ssa, Excel 2016:ssa, Excel 2019:ssä ja Microsoft 365:ssä Excelin vasemmassa yläkulmassa olevaan Tiedosto-välilehteen ja napsauta sitten vasemmasta ruudusta Asetukset.
Siirry Excelin asetukset -ikkunassa Kaava-välilehteen ja valitse "Ota iteratiivinen laskenta käyttöön" -valintaruutu Laskentavaihtoehdot-osiossa. Napsauta sitten "OK" tallentaaksesi muutokset.
Tämä mahdollistaa iteratiivisen laskennan ja siten pyöreän viittauksen.
Voit saavuttaa tämän aiemmissa Excel-versioissa seuraavasti:
- Napsauta Excel 2007:ssä Office-painiketta > Excel-asetukset > Kaavat > Iterointialue.
- Excel 2003:ssa ja aiemmissa versioissa sinun on siirryttävä kohtaan Valikko > Työkalut > Asetukset > Laskenta-välilehti.
Suurin iteraatio ja enimmäismuutosparametrit
Kun otat iteratiiviset laskelmat käyttöön, voit hallita iteratiivisia laskelmia määrittämällä kaksi vaihtoehtoa Ota iteratiiviset laskelmat käyttöön -osiossa alla olevan kuvakaappauksen mukaisesti.
- Maksimi iteraatiot – Tämä numero määrittää, kuinka monta kertaa kaavan tulee laskea uudelleen ennen lopullisen tuloksen antamista. Oletusarvo on 100. Jos muutat sen arvoon 50, Excel toistaa laskelmat 50 kertaa ennen kuin antaa lopullisen tuloksen. Muista, että mitä suurempi iteraatioiden määrä on, sitä enemmän resursseja ja aikaa kuluu laskemiseen.
- Suurin muutos – Se määrittää suurimman muutoksen laskentatulosten välillä. Tämä arvo määrittää tuloksen tarkkuuden. Mitä pienempi luku, sitä tarkempi tulos olisi ja sitä kauemmin laskentataulukon laskeminen kestää.
Jos iteratiivisten laskelmien vaihtoehto on käytössä, et saa varoitusta aina, kun laskentataulukossasi on pyöreä viittaus. Ota interaktiivinen laskenta käyttöön vain, kun se on ehdottoman välttämätöntä.
Etsi pyöreä viite Excelistä
Oletetaan, että sinulla on suuri tietojoukko ja saat pyöreän viittausvaroituksen, sinun on silti selvitettävä, missä (missä solussa) virhe on tapahtunut, jotta se voidaan korjata. Voit etsiä pyöreitä viittauksia Excelissä seuraavasti:
Virheentarkistustyökalun käyttäminen
Avaa ensin laskentataulukko, jossa pyöreä viittaus on tapahtunut. Siirry Kaava-välilehdelle ja napsauta Virheentarkistus-työkalun vieressä olevaa nuolta. Vie sitten hiiren osoitin "Circular References" -vaihtoehdon päälle, jolloin Excel näyttää luettelon kaikista soluista, jotka liittyvät pyöreään viittaukseen alla olevan kuvan mukaisesti.
Napsauta mitä tahansa solun osoitetta, jonka haluat luettelossa, ja se vie sinut kyseiseen soluun ongelman ratkaisemiseksi.
Tilapalkin käyttö
Löydät myös pyöreän viittauksen tilapalkista. Excelin tilarivillä se näyttää uusimman solun osoitteen pyöreällä viitteellä, kuten "Circular References: B6" (katso alla kuvakaappaus).
On tiettyjä asioita, jotka sinun tulee tietää käsitellessäsi kiertoviittausta:
- Tilarivillä ei näy pyöreän viittaussolun osoitetta, kun iteratiivinen laskenta -vaihtoehto on käytössä, joten sinun on poistettava se käytöstä, ennen kuin alat etsiä ympyräviittauksia työkirjasta.
- Jos kiertoviittausta ei löydy aktiivisesta arkista, tilarivillä näkyy vain "Circular References" ilman soluosoitetta.
- Saat pyöreän viitekehotteen vain kerran, ja kun napsautat "OK", se ei näytä kehotetta uudelleen seuraavan kerran.
- Jos työkirjassasi on pyöreitä viittauksia, se näyttää kehotteen joka kerta, kun avaat sen, kunnes olet ratkaissut pyöreän viittauksen tai kunnes otat iteratiivisen laskennan käyttöön.
Poista pyöreä viittaus Excelissä
Circular-viitteiden löytäminen on helppoa, mutta sen korjaaminen ei ole niin yksinkertaista. Valitettavasti Excelissä ei ole vaihtoehtoa, jonka avulla voit poistaa kaikki pyöreät viittaukset kerralla.
Korjataksesi ympyräviittauksia, sinun on löydettävä jokainen ympyräviittaus yksitellen ja yritettävä muokata sitä, poistaa ympyräkaava kokonaan tai korvata se toisella.
Joskus yksinkertaisissa kaavoissa sinun tarvitsee vain säätää kaavan parametrit uudelleen, jotta se ei viittaa itseensä. Muuta esimerkiksi B6:n kaava muotoon =SUMMA(B1:B5)*A5 (muuta B6 muotoon B5).
Se palauttaa laskennan tulokseksi "756".
Tapauksissa, joissa Excel-kiertoviittausta on vaikea löytää, voit jäljittää sen takaisin lähteeseen ja ratkaista sen yksitellen Trace Precedents- ja Trace Dependents -ominaisuuksilla. Nuoli näyttää, mihin soluihin aktiivinen solu vaikuttaa.
On olemassa kaksi jäljitysmenetelmää, joiden avulla voit poistaa pyöreät viittaukset näyttämällä kaavojen ja solujen väliset suhteet.
Pääset jäljitysmenetelmiin siirtymällä "Kaavat"-välilehteen ja napsauttamalla sitten joko "Jäljitä ennakkotiedot" tai "Jäljitä riippuvat" kaavan tarkastus -ryhmästä.
Trace Precedents
Kun valitset tämän vaihtoehdon, se jäljittää takaisin solut, jotka vaikuttavat aktiivisen solun arvoon. Se piirtää sinisen viivan, joka osoittaa, mitkä solut vaikuttavat nykyiseen soluun. Pikanäppäin jäljityksen ennakkotapausten käyttämiseen on Alt + T U T.
Alla olevassa esimerkissä sininen nuoli näyttää solut, jotka vaikuttavat B6-arvoon, ovat B1:B6 ja A5. Kuten alla näkyy, solu B6 on myös osa kaavaa, mikä tekee siitä pyöreän viittauksen ja saa kaavan palauttamaan tuloksena '0'.
Tämä voidaan helposti korjata korvaamalla B6:lla B5 SUM-argumentissa: =SUM(B1:B5).
Jäljitä huollettavia
Jäljitysriippuvaisten ominaisuus jäljittää solut, jotka ovat riippuvaisia valitusta solusta. Tämä ominaisuus piirtää sinisen viivan, joka osoittaa, mihin soluihin valittu solu vaikuttaa. Toisin sanoen se näyttää, mitkä solut sisältävät kaavoja, jotka viittaavat aktiiviseen soluun. Pikanäppäin huollettavien käyttöön on Alt + T U D.
Seuraavassa esimerkissä B4 vaikuttaa soluun D3. Se on riippuvainen B4:stä, jotta sen arvo tuottaa tuloksia. Tästä syystä jäljestä riippuvainen piirtää sinisen viivan B4:stä D3:een, mikä osoittaa, että D3 on riippuvainen B4:stä.
Pyöreäviittausten tarkoituksellinen käyttö Excelissä
Pyöreän viittauksen tahallista käyttöä ei suositella, mutta joissakin harvoissa tapauksissa tarvitset pyöreän viittauksen saadaksesi haluamasi tulosteen.
Selvitetään se esimerkin avulla.
Aloita ottamalla "Iteratiivinen laskenta" käyttöön Excel-työkirjassasi. Kun olet ottanut iteratiivisen laskennan käyttöön, voit alkaa käyttää pyöreitä viittauksia hyödyksesi.
Oletetaan, että olet ostamassa taloa ja haluat antaa edustajallesi 2 %:n välityspalkkion talon kokonaiskustannuksista. Kokonaiskustannus lasketaan solussa B6 ja välitysprosentti (agenttipalkkio) lasketaan B4:ssä. Palkkio lasketaan kokonaiskustannuksista ja kokonaishinta sisältää välityspalkkion. Koska solut B4 ja B6 ovat riippuvaisia toisistaan, se luo pyöreän viittauksen.
Syötä kaava kokonaiskustannusten laskemiseksi soluun B6:
=SUMMA(B1:B4)
Koska kokonaiskustannukset sisältävät agenttipalkkion, sisällytimme B4:n yllä olevaan kaavaan.
Laskeaksesi agenttipalkkion 2%, lisää tämä kaava kohtaan B4:
=B6*2 %Nyt solun B4 kaava riippuu B6:n arvosta laskeakseen 2 % kokonaispalkkiosta, ja B6:n kaava riippuu B4:stä kokonaiskustannusten (mukaan lukien agenttipalkkion) laskemiseksi, tästä syystä kiertokirje.
Jos iteratiivinen laskenta on käytössä, Excel ei anna varoitusta tai nollaa tuloksessa. Sen sijaan solujen B6 ja B4 tulos lasketaan edellä esitetyllä tavalla.
Iteratiivisten laskelmien vaihtoehto on yleensä oletusarvoisesti poissa käytöstä. Jos et laittanut sitä päälle ja kun syötät kaavan kohtaan B4, joka luo pyöreän viittauksen. Excel antaa varoituksen ja kun napsautat "OK", jäljitysnuoli tulee näkyviin.
Se siitä. Tämä oli kaikki mitä sinun tarvitsee tietää pyöreistä viittauksista Excelissä.