Dirbdami su „Excel“ formulėmis, kartais galite pamatyti šį įspėjamąjį raginimą.
Šis raginimas nurodo, kad jūsų darbalapyje yra apskritimo nuoroda ir tai gali lemti arba neteisingai apskaičiuoti pagal formules. Ji taip pat prašo išspręsti šią apskritosios nuorodos problemą ir ją surūšiuoti.
Šioje pamokoje aptarsiu viską, ką reikia žinoti apie apskritą nuorodą, taip pat kaip rasti ir pašalinti apskritas nuorodas „Excel“.
Taigi pradėkime!
Kas yra apskritoji nuoroda „Excel“?
Paprastais žodžiais tariant, apskritoji nuoroda įvyksta, kai ląstelėje yra formulė, kuri pati skaičiavimui naudoja langelį (kuriame jis buvo įvestas).
Leiskite man pabandyti tai paaiškinti paprastu pavyzdžiu.
Tarkime, kad duomenų rinkinį turite langelyje A1: A5 ir naudojate toliau pateiktą formulę langelyje A6:
= SUMA (A1: A6)
Taip gausite apskritą nuorodinį įspėjimą.
Taip yra todėl, kad norite apibendrinti langelio A1: A6 reikšmes, o rezultatas turėtų būti A6 langelyje.
Tai sukuria kilpą, nes „Excel“ tik prideda naują vertę langelyje A6, kuris nuolat keičiasi (taigi, apskrito nuorodos kilpa).
Kaip rasti apskritas nuorodas „Excel“?
Nors apskrito nuorodos įspėjimo raginimas yra pakankamai malonus, kad pasakytų, kad jis yra jūsų darbalapyje, jis nenurodo, kur tai vyksta ir kokios ląstelių nuorodos tai sukelia.
Taigi, jei bandote surasti ir tvarkyti apskritas nuorodas darbalapyje, turite žinoti būdą, kaip jas rasti.
Toliau pateikiami žingsniai, kaip rasti apskritą nuorodą „Excel“:
- Suaktyvinkite darbalapį, kuriame yra apskritimo nuoroda
- Spustelėkite skirtuką Formulės
- Grupėje Formulės redagavimas spustelėkite išskleidžiamąją piktogramą Klaidų tikrinimas (maža rodyklė, nukreipta žemyn)
- Užveskite pelės žymeklį ant parinkties Apskritosios nuorodos. Tai parodys langelį, kurio darbalapyje yra apskritimo nuoroda
- Spustelėkite langelio adresą (kuris rodomas) ir jis nuves jus į tą darbalapio langelį.
Išsprendę problemą, galite vėl atlikti tuos pačius aukščiau nurodytus veiksmus ir bus rodoma daugiau langelių nuorodų, turinčių apskritą nuorodą. Jei jo nėra, nematysite jokios langelio nuorodos,
Kitas greitas ir paprastas būdas rasti apskritą nuorodą yra pažvelgti į būsenos juostą. Kairėje jo dalyje bus rodomas tekstas Apskritoji nuoroda kartu su langelio adresu.
Dirbdami su apskritomis nuorodomis turite žinoti keletą dalykų:
- Jei iteracinis skaičiavimas yra įjungtas (aptariamas vėliau šiame vadove), būsenos juostoje nebus rodomas apskrito nuorodos langelio adresas
- Jei apskritos nuorodos nėra aktyviame lape (bet kituose tos pačios darbaknygės lapuose), ji parodys tik apskritimo nuorodą, o ne langelio adresą
- Jei vieną kartą gausite apskrito nuorodos įspėjimo raginimą ir jo atsisakysite, kitą kartą jis nebus rodomas.
- Jei atidarysite darbaknygę, kurioje yra apskritimo nuoroda, ji parodys raginimą, kai tik atsidarys darbaknygė.
Kaip pašalinti apskritą nuorodą „Excel“?
Nustačius, kad jūsų lape yra apskritų nuorodų, laikas jas pašalinti (nebent norite, kad jos būtų dėl kokios nors priežasties).
Deja, tai nėra taip paprasta, kaip paspausti ištrynimo klavišą. Kadangi tai priklauso nuo formulių ir kiekviena formulė yra skirtinga, turite tai analizuoti kiekvienu konkrečiu atveju.
Jei tai tik klausimas, kad klaida sukelia klaidą ląstelės nuorodoje, galite tiesiog ištaisyti koreguodami nuorodą.
Bet kartais tai nėra taip paprasta.
Apskritimo nuoroda taip pat gali būti sukurta remiantis keliomis ląstelėmis, kurios tiekiamos viena į kitą daugeliu lygių.
Leiskite parodyti jums pavyzdį.
Žemiau C6 langelyje yra apskritoji nuoroda, tačiau tai ne tik paprastas nuorodos į save atvejis. Tai daugiapakopis, kai skaičiavimuose naudojamos ląstelės taip pat nurodo viena kitą.
- A6 langelyje esančios formulės yra = SUM (A1: A5)+C6
- Formulė yra ląstelė C1 yra = A6*0,1
- Ląstelės C6 formulė yra = A6+C1
Anksčiau pateiktame pavyzdyje C6 langelio rezultatas priklauso nuo A6 ir C1 langelio reikšmių, kurios savo ruožtu priklauso nuo C6 langelio (taip sukuriant apskrito atskaitos klaidą)
Ir vėl aš pasirinkau tikrai paprastą pavyzdį tik demonstraciniais tikslais. Tiesą sakant, tai gali būti gana sunku išsiaiškinti ir galbūt toli tame pačiame darbalapyje arba net išsibarstę po kelis darbalapius.
Tokiu atveju yra vienas būdas nustatyti ląsteles, sukeliančias apskritą nuorodą, ir tada jas gydyti.
Tai naudojant parinktį „Trace Precedents“.
Toliau pateikiami žingsniai, kaip naudoti pėdsakų precedentus, norint rasti ląsteles, kurios maitina ląstelę, turinčią apskritą nuorodą:
- Pasirinkite langelį, kuriame yra apskritimo nuoroda
- Spustelėkite skirtuką Formulės
- Spustelėkite „Preceents Trace“
Pirmiau minėti veiksmai parodys mėlynas rodykles, kurios nurodys, kokios ląstelės tiekiamos į pasirinktos ląstelės formulę. Tokiu būdu galite patikrinti formules ir langelius ir atsikratyti apskritos nuorodos.
Jei dirbate su sudėtingais finansiniais modeliais, gali būti, kad šie precedentai taip pat yra kelių lygių.
Tai gerai veikia, jei turite visas formules, nurodančias to paties darbalapio langelius. Jei tai yra keliuose darbalapiuose, šis metodas nėra veiksmingas.
Kaip įjungti/išjungti pakartotinius skaičiavimus „Excel“
Kai langelyje turite apskritą nuorodą, pirmiausia gausite įspėjamąjį raginimą, kaip parodyta žemiau, ir jei uždarysite šį dialogo langą, rezultatas bus 0 langelyje.
Taip yra todėl, kad kai yra apskritoji nuoroda, tai yra begalinė kilpa, o „Excel“ nenori į tai įsitraukti. Taigi jis grąžina 0.
Tačiau kai kuriais atvejais galbūt norėsite, kad apskritoji nuoroda būtų aktyvi ir atliktų keletą pakartojimų. Tokiu atveju vietoj begalinės kilpos galite nuspręsti, kiek kartų ciklas turi būti vykdomas.
Tai vadinama iteracinis skaičiavimas „Excel“.
Toliau pateikiami veiksmai, leidžiantys ir konfigūruoti iteracinius skaičiavimus „Excel“:
- Spustelėkite skirtuką Failas
- Spustelėkite Parinktys. Bus atidarytas dialogo langas „Excel“ parinktys
- Kairėje srityje pasirinkite Formulė
- Skiltyje Skaičiavimo parinktys pažymėkite langelį „Įgalinti kartotinį skaičiavimą“. Čia galite nurodyti maksimalias iteracijas ir didžiausią pakeitimo vertę
Viskas! Pirmiau minėti veiksmai įgalintų iteracinį skaičiavimą „Excel“.
Taip pat norėčiau greitai paaiškinti du iteracinio skaičiavimo variantus:
- Maksimalios iteracijos: Tai yra maksimalus „Excel“ skaičiavimų skaičius prieš pateikiant galutinį rezultatą. Taigi, jei nurodysite tai kaip 100, „Excel“ paleis ciklą 100 kartų prieš pateikdama galutinį rezultatą.
- Maksimalus pokytis: Tai yra didžiausias pokytis, kuris nepasiekiamas tarp iteracijų, skaičiavimas būtų sustabdytas. Pagal numatytuosius nustatymus vertė yra .001. Kuo mažesnė ši vertė, tuo tikslesnis bus rezultatas.
Atminkite, kad kuo daugiau kartų kartojasi, tuo daugiau laiko ir išteklių reikia „Excel“ tai padaryti. Jei išlaikysite maksimalias iteracijas, „Excel“ gali sulėtėti arba sugesti.
Pastaba: kai įjungti iteraciniai skaičiavimai, „Excel“ nerodys apskrito nuorodos įspėjimo raginimo ir dabar bus rodoma būsenos juostoje.Sąmoningai naudojant apskritas nuorodas
Daugeliu atvejų apskritos nuorodos buvimas jūsų darbalapyje būtų klaida. Štai kodėl „Excel“ parodo raginimą, kuriame sakoma: „Pabandykite pašalinti arba pakeisti šias nuorodas arba perkelti formules į skirtingus langelius“.
Tačiau gali būti tam tikrų konkrečių atvejų, kai jums reikia apskrito nuorodos, kad galėtumėte gauti norimą rezultatą.
Vieną tokį konkretų atvejį jau rašiau apie tai, kad „Excel“ ląstelės langelyje yra laiko žymė.
Pvz., Tarkime, kad norite sukurti formulę, kad kiekvienas įrašas būtų padarytas A stulpelio langelyje, laiko stulpelis rodomas B stulpelyje (kaip parodyta žemiau):
Nors galite lengvai įterpti laiko žymę naudodami šią formulę:
= IF (A2 "", IF (B2 "", B2, DABAR ()), "")
Aukščiau pateiktos formulės problema yra ta, kad ji atnaujintų visas laiko žymes, kai tik bus atlikti bet kokie darbalapio pakeitimai arba jei darbalapis bus atidarytas iš naujo (nes formulė DABAR yra nepastovi)
Norėdami išspręsti šią problemą, galite naudoti apskrito atskaitos metodą. Naudokite tą pačią formulę, tačiau įgalinkite iteracinį skaičiavimą.
Yra ir kitų atvejų, kai norima turėti galimybę naudoti apskritą nuorodą (vieną pavyzdį rasite čia).
Pastaba: nors kai kuriais atvejais galite naudoti apskritą nuorodą, manau, kad geriausia jos nenaudoti. Apskritos nuorodos taip pat gali pakenkti jūsų darbaknygės veikimui ir sulėtinti. Retais atvejais, kai jums to reikia, aš visada norėčiau naudoti VBA kodus, kad galėčiau atlikti darbą.Tikiuosi, kad ši pamoka jums buvo naudinga!
Kitos „Excel“ pamokos, kurios jums gali būti naudingos:
- #REF! „Excel“ klaida; Kaip ištaisyti nuorodos klaidą!
- „Excel VBA“ klaidų tvarkymas
- Naudokite IFERROR su VLOOKUP, kad pašalintumėte #klaidų
- Kaip nurodyti kitą „Excel“ lapą ar darbaknygę (su pavyzdžiais)
- Absoliučios, santykinės ir mišrios ląstelių nuorodos „Excel“
