Kuinka löytää pyöreitä viittauksia Excelissä

Kun työskentelet -sovelluksen kanssa, saatat toisinaan kohdata niin sanotun pyöreän viittauksen. Tällöin solun kaava viittaa takaisin itseensä aiheuttaen loputtoman silmukan laskentaprosessissa.

Voit etsiä pyöreitä viittauksia Excelissä käyttämällä "Kaavat"-välilehden "Virheiden tarkistus" -työkalua tunnistaaksesi solut, joilla on pyöreä riippuvuus.

Tässä artikkelissa kerrotaan, kuinka voit etsiä pyöreitä viittauksia Excelissä ja korjata ne. Opit myös estämään ne, kun käsittelet suuria tai monimutkaisia ​​laskentataulukoita.

Sisällysluettelo

• Mitä ovat kiertoviitteet
• Circular Reference -virheilmoitusikkuna
• Kuinka löytää kiertoviittauksia Excelissä
• Kuinka löytää pyöreitä viittauksia Excelin tilarivillä
• Kuinka löytää piilotettuja pyöreitä viittauksia Excelissä
  • Trace Dependent -työkalun käyttäminen
  • Trace Precedents -työkalu
• 2 tapaa poistaa pyöreät viittaukset Excelissä
  • 2. Käytä vaihtoehtoisia kaavoja
• 5 vinkkiä pyöreän viittauksen estämiseen
  • 1. Ole varovainen valitessasi alueita
  • 2. Suunnittele kaavat huolellisesti
  • 3. Käytä vaihtoehtoisia kaavoja
  • 4. Pilko monimutkaiset kaavat pienempiin osiin
  • 5. Korjaa ongelmat edetessäsi
• Kuinka ottaa iteratiivinen laskenta käyttöön ja miksi vaivautua?

Mitä ovat kiertoviitteet

Ympyräviittauksia esiintyy Excelissä, kun solun kaavalaskenta riippuu solun omasta arvosta. Tämä luo silmukan ja johtaa vääriin tai arvaamattomiin tuloksiin.

Tämä on helpompi ymmärtää yksinkertaisella esimerkillä.

Oletetaan, että sinulla on kaksi numeroa soluissa A1 ja B1. Haluat käyttää solussa A3 kaavaa, joka laskee kaksi numeroa yhteen. Solun A3 kaavan pitäisi näyttää tältä:

=A1 + B1

Jos kuitenkin teet virheen ja sisällytät C1:n laskelmiin, sinulla on nyt pyöreä viite:

=A1 + B1 + C1

Tämä voi olla ilmeinen ongelma, joka on vältettävä, kun käsitellään kahta solua. Kuitenkin, kun työskentelet suurten solualueiden kanssa, virheet ovat todennäköisempiä.

Onneksi Excel on suunniteltu käsittelemään tällaisia ​​tilanteita näyttämällä varoitusviestin, kun ympyräviittaus havaitaan.

Joissakin tapauksissa saatat kuitenkin kohdata pyöreän viittauksen ilman hälytystä. Siksi on tärkeää tietää, kuinka nämä ongelmat voidaan löytää ja ratkaista manuaalisesti.

Circular Reference -virheilmoitusikkuna

Oletusarvoisesti Excel tarkkailee pyöreitä viittauksia, kun syötät kaavoja soluihin. Kun painat enteriä solussa, jossa on ongelma, ponnahdusikkunassa näkyy pyöreä viitevirheilmoitus.

Tämä on selittävä teksti:

On olemassa yksi tai useampi pyöreä viittaus, joissa kaava viittaa omaan soluunsa joko suoraan tai epäsuorasti. Tämä saattaa aiheuttaa sen, että he laskevat väärin. Yritä poistaa tai muuttaa nämä viittaukset tai siirtää kaavat eri soluihin.

Kun näet tämän valintaikkunan, napsauta "OK" ja korjaa ongelma tämän artikkelin menetelmillä.

Vikaa ei kuitenkaan tarvitse korjata heti. Voit jatkaa työskentelyä laskentataulukon kanssa, jotta voit ratkaista ongelman myöhemmin.

Siinä tapauksessa sinun on tiedettävä, kuinka löytää pyöreät viittaukset Excelistä sen jälkeen, kun olet hylännyt varoitusruudun.

Kuinka löytää kiertoviittauksia Excelissä

Excelissä on sisäänrakennettu virheentarkistustyökalu, joka löytää monenlaisia ​​virheitä, mukaan lukien pyöreät viittaukset. Voit käyttää työkalua Kaavat-välilehdellä.

Tämä kuva näyttää sijainnin Formula Auditing -ryhmässä.

Nyt tiedät kuinka löytää työkalu, tässä on yksityiskohtaiset vaiheet sen käyttöön:

1. Siirry Kaavat-välilehteen.

1. Napsauta kaavan tarkastus -ryhmän avattavaa Virheentarkistus-valikkoa.

2. Valitse avattavasta luettelosta "Circular References".

3. Napsauta linkkiä siirtääksesi kohdistimen ensimmäiseen soluun, jossa on ongelma.

Alla olevasta kuvasta näkyy, että työkalu on löytänyt ympyrävirheen solusta G1. Näytetty solu on hyperlinkki.

Kun napsautat linkkiä, osoitin siirtyy soluun. Tämä on erittäin hyödyllistä suuren tietomäärän kanssa.

Kun olet ratkaissut ensimmäisen pyöreän viittauksen, voit palata Virheentarkistus-työkaluun löytääksesi seuraavan taulukosta.

Korjaa ongelma tämän artikkelin myöhemmissä osissa olevien ohjeiden avulla ja siirry seuraavaan ongelmaan työkalun avulla.

Jos kiertoongelmia ei enää ole, "Circular References" -valikkokohta avattavassa valikossa näkyy harmaana. Sinulla ei ole enää työtä tehtävänä.

Kuinka löytää pyöreitä viittauksia Excelin tilarivillä

Toinen tapa havaita pyöreä viittaus on katsoa Excel-laskentataulukon alareunassa olevaa tilapalkkia.

Kun laskentataulukossa on yksi tai useampi ongelma, tilarivillä näkyy yhden ongelmasoluviittauksen sijainti.

Jos esimerkiksi solussa C1 on pyöreä viittaus eikä laskentataulukossa ole muita ongelmia, tilarivillä lukee: "Circular References: C1".

Tämä kuva näyttää, missä näet näytön.

Kuinka löytää piilotettuja pyöreitä viittauksia Excelissä

Piilotetut pyöreät viittaukset ovat ympyräviittauksia, joita sisäänrakennetut virheominaisuudet eivät heti havaitse. Toisin sanoen Excel ei näytä vakiovaroitusta.

Ne tapahtuvat, kun solu viittaa toiseen soluun epäsuorasti välisolujen sarjan kautta ja lopulta viittaa takaisin alkuperäiseen soluun.

Excelissä on kaksi työkalua, joiden avulla voit jäljittää suhteita ja tutkia näitä ongelmia:

1. Trace Dependents -työkalu

2. Trace Precedents -työkalu

Trace Dependent -työkalun käyttäminen

Kokeneet Excel-käyttäjät hyödyntävät Trace Dependents -työkalua tutkiakseen näitä ongelmia ja korjatakseen pyöreitä viittauksia. Valitsemalla yhden tai useamman solun ja käyttämällä tätä työkalua, Excel näyttää sinulle riippuvat solut.

Työkalu on saatavilla Kaavat-välilehden Kaavan tarkastus -osiossa.

Käytä työkalua seuraavasti:

1. Napsauta solua tai soluja, joiden huollettavia haluat jäljittää.

2. Siirry Excel-nauhan Kaavat-välilehteen.

3. Napsauta 'Jäljitä riippuvat' -painiketta 'Formula Auditing' -ryhmässä.

4. Tarkista riippuvuuksia osoittava jäljitysnuoli tai -nuolet.

Excel näyttää nuolet aktiivisen solun arvosta kaikkiin siitä riippuvaisiin soluihin. Alla olevan kuvan sininen nuoli osoittaa, että solu D6 on riippuvainen solusta B2.

Trace Precedents -työkalu

Ennakkotapaukset ovat huollettavien vastakohta. Trace Precedents -työkalulla voit valita solun kaavalla ja nähdä kaikki solut, joista se riippuu eli ennakkotapaukset.

Työkalu on saatavilla Kaavat-välilehden Kaavan tarkastus -osiossa.

Käytä työkalua seuraavasti:

1. Napsauta solua, joka sisältää tutkittavan kaavan.

2. Siirry Excel-nauhan Kaava-välilehteen.

3. Napsauta 'Trace Precedents' -painiketta 'Formula Auditing' -ryhmässä.

4. Tarkista Excelin näyttämät jäljitysnuolet.

Alla olevan kuvan siniset nuolet osoittavat, että solu D6 on riippuvainen solualueesta A2:B5.

Molempien työkalujen avulla voit löytää Excel-kiertoviittauksia, jotka eivät heti näy. Seuraava vaihe on korjata valitun solun ongelma. Voit sitten jatkaa saman ongelman etsimistä muissa soluissa.

2 tapaa poistaa pyöreät viittaukset Excelissä

Pyöreäviittausten ratkaisemiseen on kaksi päätapaa:

• pyöreän viittauksen rikkominen

• käyttämällä vaihtoehtoisia kaavoja

Selitetään molemmat tavat.

1. Pyöreän viittauksen rikkominen

Pyöreän viittauksen rikkominen Excelissä on tehokas tapa ratkaista ongelma. Tämä edellyttää, että tunnistat kiertoviittaukseen liittyvät solut ja muokkaat niiden kaavoja vastaavasti.

Voit tehdä tämän noudattamalla aiemmin kuvattuja vaiheita löytääksesi solun osoitteen.

Sinun tulee sitten tutkia kaavaa kaavapalkissa. Etsi viittaus itse soluun.

Ensimmäisessä esimerkissämme pyöreästä viittauksesta solun C1 summauskaava sisälsi viittauksen itse soluun:

=A1 + B1 + C1

Tämä voidaan korjata poistamalla viimeinen lisäys. Kun tämä ongelma on ratkaistu, voit etsiä muita kiertoviitteitä ja käsitellä niitä.

2. Käytä vaihtoehtoisia kaavoja

Jotkut yleisimmistä syistä ympyräviittauksiin ovat solujen summaus tai keskiarvo. Virhe on sisällyttää tulossolu kaavaan.

Edellisessä esimerkissämme kaava käytti summausoperaattoreita solualueen summan laskemiseen. Vaihtoehtoinen tapa on käyttää SUM Excel -funktiota.

Voit korjata esimerkin korvaamalla kaavan seuraavalla:

=SUMMA(A1:B1)

Vastaavasti voit laskea alueen keskiarvon kaavalla kuten =(A1+B1)/2. AVERAGE-funktio ei kuitenkaan todennäköisesti johda virheeseen.

=KESKIARVO(A1:B1)

5 vinkkiä pyöreän viittauksen estämiseen

Pyöreän viittauksen estämiseksi Microsoft Excelissä on tärkeää, että ymmärrät selkeästi, kuinka kaavasi ovat vuorovaikutuksessa toistensa kanssa Excel-taulukossasi.

Tunnistamalla solujen väliset suhteet ja olemalla tietoinen siitä, kuinka nämä yhteydet toimivat yhdessä, voit välttää kehäkaavojen luomisen.

Tässä on viisi parasta vinkkiämme, jotka auttavat sinua estämään pyöreät viittaukset.

1. Ole varovainen valitessasi alueita

Kokemukseni mukaan yksi yleisimmistä syistä kiertoviittauksiin on se, että käyttäjät valitsevat nopeasti kaavoihinsa sisällytettävät alueet.

Kun pyyhkäisemällä pysty- ja/tai vaakasuunnassa ryhmittelet soluja, on helppo valita yksi solu lisää, joka on oikean alueen vieressä. Jos tämä solu on myös tulossolu, olet vahingossa luonut pyöreän viittauksen.

Tämä virhe tehdään tyypillisesti työskennellessäsi suurten tietojoukkojen kanssa, kuten khin-neliötestejä suoritettaessa. Ratkaisu on olla varovainen alueen valinnassa.

2. Suunnittele kaavat huolellisesti

Ennen kuin syötät kaavoja, mieti laskujesi taustalla olevaa logiikkaa ja sitä, kuinka niiden tulisi virrata solujen välillä.

Voit kartoittaa laskelmat etukäteen käyttämällä kaaviotyökaluja tai taulua.

Tämä voi auttaa sinua tunnistamaan mahdolliset silmukat, jotka voivat johtaa pyöreisiin viittauksiin.

3. Käytä vaihtoehtoisia kaavoja

Tietyt toiminnot, kuten EPÄSUORA tai OFFSET , voivat vahingossa luoda pyöreitä viittauksia, jos niitä käytetään väärin.

Harkitse vaihtoehtoisten toimintojen käyttöä, jotka tarjoavat samanlaisia ​​toimintoja ilman pyöreän viittauksen riskiä.

4. Pilko monimutkaiset kaavat pienempiin osiin

Sen sijaan, että luot yhden monimutkaisen kaavan, jossa on useita sisäkkäisiä funktioita, harkitse laskelmien jakamista pienempiin, paremmin hallittaviin osiin.

Tämä ei ainoastaan ​​tee työkirjastasi helpommin ymmärrettävää ja ylläpidettävää, vaan myös vähentää mahdollisuuksia ottaa käyttöön pyöreä viittaus.

5. Korjaa ongelmat edetessäsi

Kun työskentelet suurten tai monimutkaisten työkirjojen kanssa, on myös tärkeää seurata pyöreitä viittauksia. Sen sijaan, että jättäisit huomioimatta ongelmat, ennen kuin suuri joukko tietoja ja kaavoja on koottu, on parempi korjata jokainen virhe heti kun se ilmenee.

Voit jättää huomioimatta pyöreän viittausvaroitusviestin hetkeksi. Sinun tulisi kuitenkin käyttää Error Checking -ominaisuutta saadaksesi umpeen olemassa olevat kiertoviittaukset ennen liian kauan.

Tämä helpottaa niiden korjaamista ja estää niiden vaikutukset työkirjaan.

Kuinka ottaa iteratiivinen laskenta käyttöön ja miksi vaivautua?

Yleensä haluat välttää pyöreitä viittauksia laskentataulukossasi. On kuitenkin olemassa tilanteita, joissa ne ovat välttämättömiä.

Esimerkiksi joissakin teknisissä tai tieteellisissä laskelmissa on käytettävä aikaisemman laskelman tulosta. Tämä tunnetaan iteratiivisena ratkaisuna.

Jokainen silmukka tarkentaa laskennan tuloksia, kunnes tietty kynnys tai tavoite saavutetaan.

Näissä tapauksissa on tärkeää ottaa Iteratiiviset laskelmat käyttöön Excelin asetuksissa, jotta aiotut ympyräviittaukset sallitaan. Näin ohjelmisto voi toistaa laskelmat tietyn määrän kertoja konvergoimalla ratkaisuun.

Tee pyöreä viittaus toimimaan Excel-tiedostossasi seuraavasti:

1. Napsauta Tiedosto-välilehteä.

2. Valitse Asetukset.

3. Valitse Kaavat-luokka.

4. Valitse Laskentavaihtoehdot-osiossa valintaruutu Ota iteratiivinen laskenta käyttöön.

5. Muuta iteraatioiden enimmäismäärää tai jätä oletusarvoksi 100.

6. Käytä enimmäismuutosruutua vähentääksesi iteraatioita tarpeidesi mukaan.