Vienas iš dažniausiai pasitaikančių klaidų įspėjimų, su kuriais susiduria „Excel“, yra „Circular Reference“. Tūkstančiai vartotojų turi tą pačią problemą, ir ji įvyksta, kai formulė tiesiogiai arba netiesiogiai nurodo savo langelį, sukeldama nesibaigiančią skaičiavimų kilpą.
Pavyzdžiui, langeliuose B1 ir B2 yra dvi reikšmės. Kai formulė =B1+B2 įvedama į B2, sukuriama žiedinė nuoroda; B2 formulė pakartotinai perskaičiuoja save, nes kiekvieną kartą skaičiuojant keičiasi B2 reikšmė.
Dauguma žiedinių nuorodų yra netyčinės klaidos; „Excel“ jus apie tai įspės. Tačiau yra ir numatytų žiedinių nuorodų, kurios naudojamos kartotiniams skaičiavimams atlikti. Dėl nenumatytų žiedinių nuorodų darbalapyje formulė gali būti skaičiuojama neteisingai.
Todėl šiame straipsnyje paaiškinsime viską, ką reikia žinoti apie žiedines nuorodas, taip pat kaip rasti, taisyti, pašalinti ir naudoti žiedines nuorodas programoje „Excel“.
Kaip rasti ir tvarkyti žiedinę nuorodą programoje „Excel“.
Dirbdami su „Excel“ kartais susiduriame su žiedinėmis nuorodų klaidomis, kurios nutinka įvedus formulę, kurioje yra langelis, kuriame yra jūsų formulė. Iš esmės tai atsitinka, kai jūsų formulė bando apskaičiuoti save.
Pavyzdžiui, langelyje A1:A4 yra skaičių stulpelis, o A5 langelyje naudojate funkciją SUM (=SUM(A1:A5)). Ląstelė A5 tiesiogiai nurodo savo ląstelę, o tai neteisinga. Taigi gausite šį apskrito nuorodos įspėjimą:
Kai gausite aukščiau pateiktą įspėjimo pranešimą, galite spustelėti mygtuką „Pagalba“, kad sužinotumėte daugiau apie klaidą, arba uždaryti klaidos pranešimo langą spustelėdami mygtuką „Gerai“ arba „X“ ir gauti „0“ kaip rezultatą.
Kartais dėl žiedinių nuorodų kilpų skaičiavimas gali sugesti arba sulėtinti darbalapio našumą. Apykaitinė nuoroda taip pat gali sukelti daugybę kitų problemų, kurios nebus akivaizdžios iš karto. Taigi, geriausia jų vengti.
Tiesioginės ir netiesioginės aplinkinės nuorodos
Circular nuorodas galima suskirstyti į du tipus: tiesiogines žiedines nuorodas ir netiesiogines žiedines nuorodas.
Tiesioginė nuoroda
Tiesioginė žiedinė nuoroda yra gana paprasta. Tiesioginės žiedinės nuorodos įspėjimo pranešimas pasirodo, kai formulė tiesiogiai nurodo atgal į savo langelį.
Toliau pateiktame pavyzdyje formulė langelyje A2 tiesiogiai nurodo savo langelį (A2).
Kai pasirodys įspėjimo pranešimas, galite spustelėti „Gerai“, bet bus tik „0“.
Netiesioginė žiedinė nuoroda
Netiesioginė žiedinė nuoroda programoje „Excel“ atsiranda, kai formulės reikšmė nurodo atgal į savo langelį, bet ne tiesiogiai. Kitaip tariant, apskrita nuoroda gali būti sudaryta iš dviejų langelių, nurodančių viena kitą.
Paaiškinkime šiuo paprastu pavyzdžiu.
Dabar vertė prasideda nuo A1, kurios vertė yra 20.
Toliau langelis C3 nurodo langelį A1.
Tada ląstelė A5 reiškia ląstelę C3.
Dabar pakeiskite reikšmę 20 langelyje A1 formule, kaip parodyta toliau. Kiekviena kita ląstelė priklauso nuo ląstelės A1. Kai A1 naudosite bet kurio kito ankstesnio formulės langelio nuorodą, bus įspėjamasis apskrito nuorodos įspėjimas. Kadangi A1 formulė nurodo langelį A5, kuris nurodo C3, o langelis C3 nurodo atgal į A1, taigi apskrita nuoroda.
Spustelėjus „Gerai“, langelyje A1 bus 0 reikšmė, o „Excel“ sukuria susietą eilutę, rodančią sekimo precedentus ir priklausomybes, kaip parodyta toliau. Šią funkciją galime naudoti norėdami lengvai rasti ir pataisyti/pašalinti žiedines nuorodas.
Kaip įjungti / išjungti žiedines nuorodas programoje „Excel“.
Pagal numatytuosius nustatymus kartotiniai skaičiavimai programoje Excel yra išjungti (išjungti). Iteraciniai skaičiavimai yra pasikartojantys skaičiavimai, kol jie atitinka tam tikrą sąlygą. Kai jis išjungtas, „Excel“ rodo žiedinės nuorodos pranešimą ir pateikia 0.
Tačiau kartais apskaičiuojant kilpą reikia žiedinių nuorodų. Norėdami naudoti žiedinę nuorodą, „Excel“ turite įjungti pasikartojančius skaičiavimus ir galėsite atlikti skaičiavimus. Dabar parodysime, kaip galite įjungti arba išjungti iteracinius skaičiavimus.
Programose „Excel 2010“, „Excel 2013“, „Excel 2016“, „Excel 2019“ ir „Microsoft 365“ eikite į skirtuką „Failas“, esantį viršutiniame kairiajame „Excel“ kampe, tada kairiojoje srityje spustelėkite „Parinktys“.
„Excel“ parinkčių lange eikite į skirtuką „Formulė“ ir pažymėkite žymės langelį „Įgalinti kartotinį skaičiavimą“, esantį skiltyje „Skaičiavimo parinktys“. Tada spustelėkite „Gerai“, kad išsaugotumėte pakeitimus.
Tai leis iteracinį skaičiavimą ir taip sudarys žiedinę nuorodą.
Norėdami tai pasiekti ankstesnėse „Excel“ versijose, atlikite šiuos veiksmus:
- Programoje Excel 2007 spustelėkite Office mygtuką > Excel parinktys > Formulės > Iteracijos sritis.
- „Excel 2003“ ir ankstesnėse versijose turite eiti į Meniu > Įrankiai > Parinktys > skirtuką Skaičiavimas.
Maksimalios iteracijos ir didžiausio pakeitimo parametrai
Įjungę kartotinį skaičiavimą, galėsite valdyti kartotinį skaičiavimą, nurodydami dvi parinktis, galimas skyriuje Įgalinti kartotinį skaičiavimą, kaip parodyta toliau esančioje ekrano kopijoje.
- Maksimalios iteracijos – Šis skaičius nurodo, kiek kartų formulė turi perskaičiuoti prieš pateikdama galutinį rezultatą. Numatytoji reikšmė yra 100. Jei pakeisite ją į „50“, „Excel“ pakartos skaičiavimus 50 kartų prieš pateikdama galutinį rezultatą. Atminkite, kad kuo didesnis iteracijų skaičius, tuo daugiau išteklių ir laiko reikia apskaičiuoti.
- Maksimalus pokytis – Jis nustato didžiausią skaičiavimo rezultatų pokytį. Ši vertė lemia rezultato tikslumą. Kuo mažesnis skaičius, tuo tikslesnis rezultatas būtų gaunamas ir tuo ilgiau užtrunka skaičiuoti darbalapį.
Jei įjungta kartotinių skaičiavimų parinktis, negausite jokio įspėjimo, kai darbalapyje bus žiedinė nuoroda. Įjunkite interaktyvų skaičiavimą tik tada, kai tai absoliučiai būtina.
Raskite žiedinę nuorodą programoje „Excel“.
Tarkime, kad turite didelį duomenų rinkinį ir gavote apskrito nuorodos įspėjimą, vis tiek turėsite išsiaiškinti, kur (kuriame langelyje) įvyko klaida, kad ją ištaisytumėte. Norėdami rasti žiedines nuorodas programoje „Excel“, atlikite šiuos veiksmus:
Klaidų tikrinimo įrankio naudojimas
Pirmiausia atidarykite darbalapį, kuriame įvyko žiedinė nuoroda. Eikite į skirtuką „Formulė“, spustelėkite rodyklę šalia įrankio „Klaidų tikrinimas“. Tada tiesiog užveskite pelės žymeklį ant parinkties „Circular References“, „Excel“ parodys visų langelių, susijusių su žiedine nuoroda, sąrašą, kaip parodyta toliau.
Sąraše spustelėkite bet kurio langelio adresą ir jis nuves jus į tą langelio adresą, kad išspręstumėte problemą.
Būsenos juostos naudojimas
Apvalią nuorodą taip pat galite rasti būsenos juostoje. „Excel“ būsenos juostoje bus rodomas naujausias langelio adresas su žiedine nuoroda, pvz., „Circular References: B6“ (žr. toliau pateiktą ekrano kopiją).
Yra tam tikrų dalykų, kuriuos turėtumėte žinoti tvarkydami žiedinę nuorodą:
- Būsenos juostoje nebus rodomas apskrito nuorodos langelio adresas, kai įjungta Iteratyvinio skaičiavimo parinktis, todėl prieš pradėdami žiūrėti į darbaknygę ir ieškoti žiedinių nuorodų, turite ją išjungti.
- Jei aktyviajame lape žiedinės nuorodos nerasta, būsenos juostoje rodomos tik „Circular References“ be langelio adreso.
- Apskritą nuorodą gausite tik vieną kartą ir spustelėję „Gerai“, kitą kartą raginimas nebebus rodomas.
- Jei darbaknygėje yra žiedinių nuorodų, joje bus rodomas raginimas kiekvieną kartą ją atidarius, kol išspręsite žiedinę nuorodą arba įjungsite kartotinį skaičiavimą.
Pašalinkite žiedinę nuorodą programoje „Excel“.
Circular nuorodas rasti lengva, tačiau tai nėra taip paprasta. Deja, „Excel“ nėra parinkties, kuri leistų vienu metu pašalinti visas žiedines nuorodas.
Norėdami pataisyti žiedines nuorodas, turite rasti kiekvieną žiedinę nuorodą atskirai ir pabandyti ją modifikuoti, visiškai pašalinti apskrito formulę arba pakeisti kita.
Kartais paprastose formulėse tereikia iš naujo sureguliuoti formulės parametrus, kad ji nesikreiptų į save. Pavyzdžiui, pakeiskite formulę B6 į =SUM(B1:B5)*A5 (pakeiskite B6 į B5).
Jis grąžins skaičiavimo rezultatą kaip „756“.
Tais atvejais, kai sunku rasti „Excel“ žiedinę nuorodą, galite naudoti „Sekti precedentus“ ir „Sekti priklausomybes“ funkcijas, kad atsektumėte ją iki šaltinio ir išspręstumėte po vieną. Rodyklė rodo, kurias ląsteles paveikė aktyvi ląstelė.
Yra du sekimo metodai, kurie gali padėti ištrinti žiedines nuorodas, rodydami ryšius tarp formulių ir langelių.
Norėdami pasiekti sekimo metodus, eikite į skirtuką „Formulės“, tada spustelėkite „Sekti precedentus“ arba „Sekti priklausomybes“ formulių audito grupėje.
Atsekti precedentus
Kai pasirenkate šią parinktį, ji seka langelius, kurie turi įtakos aktyvaus langelio vertei. Jis nubrėžia mėlyną liniją, rodančią, kurios ląstelės veikia esamą langelį. Spartusis klavišas, norint naudoti sekimo precedentus, yra Alt + T U T.
Toliau pateiktame pavyzdyje mėlyna rodyklė rodo langelius, turinčius įtakos B6 reikšmei, yra B1:B6 ir A5. Kaip matote toliau, langelis B6 taip pat yra formulės dalis, todėl ji yra apskrita nuoroda ir dėl to formulė grąžina „0“.
Tai galima lengvai išspręsti SUM argumente pakeitus B6 į B5: =SUM(B1:B5).
Atsekti priklausomus asmenis
Priklausomų elementų sekimo funkcija seka langelius, kurie priklauso nuo pasirinkto langelio. Ši funkcija nubrėžia mėlyną liniją, rodančią, kurias ląsteles paveikė pasirinktas langelis. Tai reiškia, kad jis rodo, kuriuose langeliuose yra formulės, nurodančios aktyvų langelį. Spartusis klavišas naudoti išlaikytinius yra Alt + T U D.
Toliau pateiktame pavyzdyje ląstelę D3 paveikia B4. Tai priklauso nuo B4, kad jo vertė duos rezultatus. Taigi, priklausomas nuo pėdsako nubrėžia mėlyną liniją nuo B4 iki D3, nurodant, kad D3 priklauso nuo B4.
Sąmoningas žiedinių nuorodų naudojimas programoje „Excel“.
Sąmoningai naudoti žiedines nuorodas nerekomenduojama, tačiau retais atvejais gali prireikti žiedinės nuorodos, kad gautumėte norimą išvestį.
Paaiškinkime tai naudodami pavyzdį.
Norėdami pradėti, „Excel“ darbaknygėje įgalinkite „Iteratyvų skaičiavimą“. Įjungę kartotinį skaičiavimą, galite pradėti naudoti žiedines nuorodas savo naudai.
Tarkime, kad perkate namą ir norite savo agentui skirti 2% komisinį mokestį nuo visų namo išlaidų. Bendra kaina bus skaičiuojama langelyje B6, o komisinis procentas (agento mokestis) – B4. Komisinis atlyginimas skaičiuojamas nuo visų išlaidų ir į bendrą kainą įeina komisiniai. Kadangi langeliai B4 ir B6 priklauso vienas nuo kito, sukuriama apskrita nuoroda.
Įveskite formulę bendrai kainai apskaičiuoti langelyje B6:
=SUMA(B1:B4)
Kadangi į bendrą kainą įeina agento mokestis, į aukščiau pateiktą formulę įtraukėme B4.
Norėdami apskaičiuoti 2% agento mokestį, įrašykite šią formulę į B4:
=B6*2 %Dabar formulė langelyje B4 priklauso nuo B6 vertės, kad būtų galima apskaičiuoti 2 % viso mokesčio, o formulė B6 priklauso nuo B4, kad būtų galima apskaičiuoti visas išlaidas (įskaitant agento mokestį), taigi, apskrito nuoroda.
Jei iteracinis skaičiavimas įjungtas, „Excel“ nepateiks įspėjimo ar rezultato 0. Vietoj to, langelių B6 ir B4 rezultatas bus apskaičiuojamas, kaip parodyta aukščiau.
Iteratyvių skaičiavimų parinktis paprastai yra išjungta pagal numatytuosius nustatymus. Jei jo neįjungėte ir įvedę formulę į B4, bus sukurta apskrita nuoroda. „Excel“ paskelbs įspėjimą, o kai spustelėsite „Gerai“, bus rodoma sekimo rodyklė.
Viskas. Tai buvo viskas, ką turėjote žinoti apie žiedines nuorodas programoje „Excel“.