Ciklične reference v programu Microsoft Excel

Šteje se, da so ciklične reference v Excelu napačni izraz. Dejansko je pogosto to tako, vendar še vedno ne vedno. Včasih se uporabljajo zelo zavestno. Ugotovimo, kakšne so krožne reference, kako jih ustvariti, kako najti obstoječe v dokumentu, kako delati z njimi ali kako jih zbrisati, če je potrebno.

Uporaba krožnih referenc

Najprej pa ugotovimo, kakšna je ciklična referenca. Pravzaprav je izraz, ki se s pomočjo formul v drugih celicah sklicuje na samega sebe. Prav tako je lahko povezava, ki se nahaja v elementu stanja, na katero se nanaša.

Upoštevati je treba, da moderne različice Excela samodejno blokirajo proces izvajanja cikličnega delovanja. To je posledica dejstva, da so takšni izrazi v veliki meri napačni, in zanke proizvaja stalen proces ponovnega izračunavanja in izračunavanja, kar ustvarja dodatno obremenitev sistema.

Ustvarjanje krožne reference

Zdaj pa si oglejmo, kako ustvariti najpreprostejši ciklični izraz. To bo povezava, ki se nahaja v isti celici, na katero se nanaša.

1. Izberite element lista A1 in vnesite naslednji izraz:

   =A1

   Nato kliknite gumb Enter na tipkovnici.

2. Po tem se prikaže pogovorno okno za opozorilo o cikličnem izrazu. V njem kliknite gumb »V redu« .
3. Tako smo na listu, v katerem se celica nanaša na samega sebe, dobila ciklično operacijo.

Let's nekoliko zapletite nalogo in ustvarite ciklični izraz iz več celic.

1. Napišite številko kateregakoli elementa lista. Naj bo celica A1 in številka 5 .
2. V drugi celici ( B1 ) napišemo izraz:

   =C1

3. Naslednjemu elementu ( C1 ) napišemo naslednjo formulo:

   =A1

4. Po tem se vrnemo v celico A1 , v kateri je nastavljena številka 5 . V točki B1 se sklicujemo na to:

   =B1

   Pritisnite gumb Enter .

5. Tako je cikel zaprt in pridobili smo klasično ciklično referenco. Ko je opozorilno okno zaprto, vidimo, da je program označil ciklično povezavo z modrimi puščicami na listu, ki se imenujejo puščice sledi.

Zdaj pa nadaljujemo z ustvarjanjem cikličnega izraza z uporabo tabele z meritvami. Imamo mizo za prodajo hrane. Sestavljen je iz štirih stolpcev, ki navajajo ime izdelka, število prodanih proizvodov, ceno in znesek prihodkov od prodaje celotnega obsega. V tabeli v zadnjem stolpcu že obstajajo formule. Pri njih se prihodek izračuna tako, da količino pomnoži s ceno.

1. Če želite popraviti formulo v prvi vrstici, izberite element lista s količino prvega izdelka ( B2 ). Namesto statične vrednosti ( 6 ) vnesemo formulo, ki bo zajemala količino blaga tako, da bi skupni znesek ( D2 ) delili s ceno ( C2 ):

   =D2/C2

   Kliknemo na gumb Enter .

2. Imamo prvo ciklično povezavo, katerega razmerje navadno označuje puščica sledi. Toda, kot si lahko ogledate, je rezultat napačen in enak nič, ker je bilo že rečeno, Excel blokira izvajanje cikličnih operacij.
3. Kopirajte izraz v vse ostale celice v stolpcu s številom izdelkov. Če želite to narediti, postavite kazalko v spodnji desni kot elementa, ki že vsebuje formulo. Kazalec se pretvori v križ, ki se ponavadi imenuje oznaka za polnjenje. Pritegnite levi gumb miške in povlecite ta križ na konec tabele navzdol.
4. Kot lahko vidite, je bil izraz kopiran v vse elemente stolpca. Ampak samo ena povezava je označena s puščico sledi. Zapomnimo jo za prihodnost.

Iskanje krožnih referenc

Kot smo videli zgoraj, program ne označuje razmerja krožnega sklicevanja na predmete ves čas, tudi če obstaja na listu. Glede na to, da je velika večina cikličnih operacij škodljivih, jih je treba odstraniti. Toda za to morajo najprej najti. Kako je to mogoče storiti, če izrazi niso označeni s puščično črto? Obravnavajmo to nalogo.

1. Torej, če odprete okno z informacijami, ko zaženete datoteko Excel, da vsebuje krožno povezavo, jo najdeš. Če želite to narediti, pojdite na kartico »Formule «. Kliknite na trak na trikotniku, ki se nahaja desno od gumba "Preveri napake" , ki se nahaja v orodni vrstici "Formula Dependencies" . Odpre se meni, s katerim premaknete kazalec na element »Ciklične povezave« . Po tem v naslednjem meniju se odpre seznam naslovov elementov stanja, v katerih je program našel ciklične izraze.
2. Ko kliknete določen naslov, je izbrana ustrezna celica na listu.

Obstaja še en način, kako ugotoviti, kje je krožna povezava. Sporočilo o tej težavi in ​​naslov elementa, ki vsebuje tak izraz, je na levi strani vrstice stanja, ki se nahaja na dnu okna Excel. Vendar pa za razliko od prejšnje različice v vrstici stanja ne bodo prikazani naslovi vseh elementov, ki vsebujejo krožne povezave, če jih je veliko, toda samo eden izmed njih, ki se je pojavil pred ostalimi.

Poleg tega, če ste v knjigi s cikličnim izrazom, ne na listi, kjer se nahaja, po drugi strani pa v tem primeru se v vrstici stanja prikaže le sporočilo o napaki brez naslova.

Lekcija: Kako najti krožne reference v Excelu

Popravljanje cikličnih povezav

Kot je bilo že omenjeno, je v večini primerov ciklično delovanje zlo, iz katerega se moramo znebiti. Zato je naravno, da je po odkritju ciklične komunikacije potrebno popraviti, da bi formula v normalni obliki.

Da bi popravili ciklično odvisnost, je potrebno slediti celotnemu medsebojnemu povezovanju celic. Tudi če je preverjanje določilo določeno celico, napaka morda ni samo po sebi, ampak v drugem elementu verige odvisnosti.

1. V našem primeru je kljub dejstvu, da je program pravilno pokazal eno od celic zanke ( D6 ), resnična napaka leži v drugi celici. Izberite element D6, da ugotovite, iz katerih celic je potegnil vrednost. Poglejte izraz v vrstici s formulo. Kot lahko vidite, se vrednost v tem elementu stanja oblikuje tako, da pomnoži vsebino celic B6 in C6 .
2. Pojdite v celico C6 . Izberite to in poglejte vrstico formule. Kot vidite, je to običajna statična vrednost ( 1000 ), ki ni proizvod za izračun formule. Zato lahko z gotovostjo rečemo, da navedeni element ne vsebuje napake, ki bi povzročila nastanek cikličnih operacij.
3. Pojdi na naslednjo celico ( B6 ). Po izboru v vrstici s formulo vidimo, da vsebuje izračunan izraz ( = D6 / C6 ), ki potegne podatke iz drugih elementov tabele, še posebej iz celice D6 . Tako se celica D6 nanaša na podatke elementa B6 in obratno, kar povzroči fiksacijo.

   Tukaj smo relativno izračunali razmerje, v resnici pa obstajajo primeri, ko v procesu izračuna obstaja veliko celic, ki pa ne trije elementi, kot jih imamo. Potem lahko iskanje traja zelo dolgo, ker morate preučevati vsak element cikličnosti.

4. Zdaj moramo razumeti, katera celica ( B6 ali D6 ) vsebuje napako. Čeprav, formalno, to ni niti napaka, temveč preprosto pretirana uporaba referenc, kar vodi v zanko. Med postopkom odločanja, katero celico želite urediti, morate uporabiti logiko. Ni jasnega algoritma delovanja. V vsakem primeru bo ta logika lastna.

   Na primer, če je v naši tabeli skupni znesek izračunan z množenjem količine dejansko prodanega blaga po njegovi ceni, potem lahko rečemo, da je povezava, ki izračuna znesek od skupnega zneska prodaje, očitno odveč. Zato jo izbrišemo in jo nadomestimo s statično vrednostjo.

5. Izvajamo podobno operacijo pri vseh drugih cikličnih izrazih, če obstajajo na listu. Po popolni odstranitvi vseh krožnih referenc iz knjige se sporočilo o prisotnosti te težave izgine iz vrstice stanja.

   Poleg tega, če so bili ciklični izrazi popolnoma izbrisani, lahko ugotovite z orodjem za preverjanje napak. Pojdite na zavihek »Formule « in kliknite na trikotnik, ki ga že poznamo, na desni strani gumba »Preveri napake« v orodni vrstici »Formula Dependencies« . Če element "Ciklične povezave" v meniju ni aktiven, smo iz dokumenta odstranili vse take predmete. V nasprotnem primeru boste morali postopke brisanja uporabiti za elemente, ki so na seznamu enako kot prej.

Omogočanje cikličnih operacij

V prejšnjem delu lekcij smo v glavnem govorili o tem, kako ravnati s cikličnimi referencami ali kako jih najti. Toda prej je bil pogovor o dejstvu, da je v nekaterih primerih uporabnik lahko uporaben in zavestno uporabljen. Ta metoda se na primer pogosto uporablja za iterativne izračune pri gradnji ekonomskih modelov. Toda težave so v tem, da ne glede na to, ali zavestno ali nezavedno uporabljate ciklični izraz, Excel po privzetku še vedno blokira delovanje na njih, da ne povzroči pretirane preobremenitve sistema. V tem primeru je vprašanje prisilnega onemogočanja takšne ključavnice aktualno. Poglejmo, kako to storiti.

1. Najprej se premaknemo na kartico »Datoteka « programa Excel.
2. Nato kliknite na "Možnosti" na levi strani okna, ki se odpre.
3. Začne se okno možnosti Excel. Pojdimo na jeziček "Formule" .
4. V oknu se odpre, da boste lahko izvajali ciklične operacije. Premaknemo se v desni blok tega okna, kjer se nahajajo nastavitve programa Excel. Delali bomo s konfiguracijskim blokom "Možnosti izračunavanja" , ki se nahaja na samem vrhu.

   Če želite dovoliti uporabo cikličnih izrazov, morate potrditi polje poleg možnosti Omogoči iterativne izračune . Poleg tega lahko v istem bloku nastavite največje število ponovitev in relativno napako. Privzeto so njihove vrednosti enake 100 in 0,001. V večini primerov teh parametrov ni treba spreminjati, čeprav lahko po potrebi spremenite navedena polja ali če želite. Toda tu je treba upoštevati, da lahko preveč ponovitev povzroči resen obremenitev programa in sistema kot celote, še posebej, če delate z datoteko, ki vsebuje veliko cikličnih izrazov.

   Torej, preverite možnost »Omogoči iterativne izračune« in nato kliknite gumb »V redu«, ki se nahaja na dnu okna možnosti Excel, da bodo nove nastavitve začele veljati.

5. Po tem bomo samodejno prešli na listo trenutne knjige. Kot lahko vidite, v celicah, v katerih se nahajajo ciklične formule, so zdaj vrednosti pravilno izračunane. Program ne blokira izračunov v njih.

Vendar je treba še vedno opozoriti, da se vključitve cikličnih operacij ne sme zlorabljati. To funkcijo uporabite samo, če je uporabnik povsem prepričan o svoji potrebi. Nerazumno vključevanje cikličnih operacij ne more povzročiti le prekomerne delovne obremenitve sistema in počasi izračunati med delom z dokumentom, lahko pa uporabnik nenamerno vnese napačen ciklični izraz, ki ga program takoj zamrzne.

Kot vidimo, v veliki večini primerov ciklične reference predstavljajo pojav, ki ga je treba boriti. Če želite to narediti, najprej najdejte najbolj ciklično razmerje, potem izračunajte celico, v kateri je napaka vsebovana, in nazadnje jo odstranite z ustreznimi prilagoditvami. Toda v nekaterih primerih so ciklični postopki lahko uporabni pri izračunih in jih uporabnik zavestno uporabi. Toda tudi takrat jih je treba uporabljati previdno, pravilno nastaviti Excel in poznati obseg dodajanja takšnih povezav, kar lahko pri uporabi v razsutem stanju upočasni sistem.

• ← Oblike vnosa podatkov v programu Microsoft Excel
• Obnovitev manjkajočih listov v programu Microsoft Excel →