Cikličke veze u programu Microsoft Excel

Opšte je prihvaćeno da su cikličke veze u Excelu pogrešan izraz. Zaista, prilično često je to istina, ali još uvijek ne uvijek. Ponekad se primenjuju sasvim namerno. Otkrijmo što su cikličke veze, kako ih stvoriti, kako pronaći postojeće u dokumentu, kako raditi s njima ili kako ih izbrisati ako je potrebno.

Koristeći kružne reference

Prije svega, otkrijmo što je kružna veza. U stvari, to je izraz koji se pomoću formula u drugim ćelijama odnosi na sebe. Može biti i veza koja se nalazi u elementu lista na koji se i sam odnosi.

Treba napomenuti da moderne verzije Excela prema zadanim postavkama automatski blokiraju proces izvođenja cikličke operacije. To je zbog činjenice da su takvi izrazi pretjerano pogrešni, a petlja stvara stalni proces preračunavanja i izračunavanja, što stvara dodatno opterećenje za sustav.

Stvorite kružnu vezu

Sada da vidimo kako stvoriti jednostavan ciklički izraz. To će biti veza koja se nalazi u istoj ćeliji na koju se odnosi.

1. Odaberite stavku na listu A1 i u nju napišite sljedeći izraz:

   = A1

   Zatim kliknite na dugme Unesite na tastaturi.



2. Nakon toga pojavljuje se dijaloški okvir upozorenja za ciklični izraz. Kliknite dugme u njemu. "OK".



3. Tako smo dobili cikličku operaciju na listu u kojoj se ćelija odnosi na sebe.

Komplicirajmo zadatak malo i iz nekoliko ćelija stvorimo ciklički izraz.

1. U bilo kojem elementu lista napišite broj. Neka bude ćelija A1, i broj 5.



2. U drugu ćeliju (B1) napišite izraz:

   = C1



3. U sledećem elementu (C1) pišemo takvu formulu:

   = A1



4. Nakon toga vraćamo se u ćeliju A1u kome je postavljen broj 5. Pozivamo se na element u njemu. B1:

   = B1

   Kliknite na dugme Unesite.



5. Tako se petlja zatvorila i dobili smo klasičnu kružnu referencu. Nakon zatvaranja prozora upozorenja, program je cikličku vezu označio plavim strelicama na listu, koje se nazivaju strelicama u tragovima.

Sada prelazimo na stvaranje cikličkog izraza pomoću tablice primjera. Imamo tabelu prodaje hrane. Sastoji se od četiri stupca koji označavaju naziv proizvoda, broj prodanih proizvoda, cijenu i iznos prihoda od prodaje cijelog volumena. Tabela u posljednjem stupcu već sadrži formule. Prihod izračunavaju množenjem količine s cijenom.

1. Da biste formulu formulirali u prvom retku, odaberite element lista sa količinom prve stavke na računu (B2) Umjesto statičke vrijednosti (6) tamo unosimo formulu koja će razmotriti količinu robe dijeleći ukupni iznos (D2) po cijeni (C2):

   = D2 / C2

   Kliknite na dugme Unesite.



2. Dobili smo prvu kružnu vezu, čiji je odnos obično naznačen strelicom u tragovima. Ali kao što vidite, rezultat je pogrešan i jednak nuli, kao što je već spomenuto, Excel blokira izvršenje cikličkih operacija.



3. Kopirajte izraz u sve ostale ćelije u stupcu s brojem proizvoda. Da biste to učinili, postavite kursor u donji desni ugao elementa koji već sadrži formulu. Kursor se pretvara u križ, koji se obično naziva marker punjenja. Držite lijevu tipku miša i povucite ovaj križ na kraju tablice prema dolje.



4. Kao što vidite, izraz je kopiran u sve elemente stupca. Ali samo je jedna veza označena strelicom u tragovima. Zapamtite to za budućnost.

Potražite kružne veze

Kao što smo vidjeli gore, ne u svim slučajevima program označava odnos kružne reference s objektima, čak i ako se nalazi na listu. S obzirom na činjenicu da je velika većina cikličkih operacija štetna, treba ih ukloniti. Ali za to ih prvo moraju pronaći. Kako to učiniti ako izrazi nisu označeni linijom sa strelicama? Riješimo ovaj problem.

1. Dakle, ako pokrenete Excel datoteku, otvara se informativni prozor u kojem se navodi da ona sadrži kružnu vezu, tada je poželjno da je pronađete. Da biste to učinili, pomaknite se na karticu Formule. Kliknite vrpcu na trokutu koja se nalazi desno od gumba "Provjeri greške"koja se nalazi u bloku alata Zavisnosti formule. Otvara se izbornik u kojem biste trebali pomicati pokazivač iznad stavke "Kružne veze". Nakon toga se u sljedećem izborniku otvara popis adresa elemenata lista u kojima je program otkrio ciklične izraze.



2. Kada kliknete na određenu adresu, odabire se odgovarajuća ćelija na listu.

Postoji još jedan način da saznate gdje je kružna veza. Poruka o ovom problemu i adresa elementa koji sadrži ovaj izraz nalazi se na lijevoj strani trake statusa, koja se nalazi na dnu prozora Excela. Istina, za razliku od prethodne verzije, statusna traka neće prikazivati ​​adrese svih elemenata koji sadrže kružne veze, ako ih ima mnogo, već se samo jedan pojavljuje prije ostalih.

Uz to, ako se nalazite u knjizi koja sadrži ciklički izraz, ne na listu na kojem se nalazi, već na drugoj strani, u tom će se slučaju u statusnoj traci prikazati samo poruka o prisutnosti pogreške bez adrese.

Lekcija: Kako pronaći kružne veze u Excelu

Popravite cikličke veze

Kao što je gore spomenuto, u ogromnoj većini slučajeva ciklične operacije su zlo koje bi trebalo odložiti. Stoga je logično da je nakon cikličke veze potrebno ispraviti da bi se formula dovela u normalnu formu.

Da bi se fiksirala ciklička zavisnost, potrebno je pratiti celu međusobnu povezanost ćelija. Čak i ako je provjera ukazala na određenu ćeliju, greška možda nije u samoj sebi, već u drugom elementu lanca ovisnosti.

1. U našem slučaju uprkos činjenici da je program ispravno ukazao na jednu od ćelija u petlji (D6), prava greška leži u drugoj ćeliji. Odaberite element D6da biste saznali iz kojih ćelija izvlači vrijednost. Izraz gledamo u traci formule. Kao što vidite, vrijednost ovog elementa lista formira se množenjem sadržaja ćelija B6 i C6.



2. Idi u ćeliju C6. Odaberite ga i pogledajte liniju formula. Kao što vidite, ovo je uobičajena statička vrijednost (1000), što nije proizvod izračuna formule. Stoga s pouzdanjem možemo reći da navedeni element ne sadrži grešku koja uzrokuje stvaranje cikličkih operacija.



3. Idite do sljedeće ćelije (B6) Nakon isticanja u traci formule, vidimo da sadrži izračunati izraz (= D6 / C6), koji povlači podatke iz drugih elemenata tabele, posebno iz ćelije D6. Znači ćelija D6 odnosi se na podatke predmeta B6 i obrnuto, što uzrokuje petlje.

   

   Ovde smo izračunali odnos prilično brzo, ali u stvarnosti postoje slučajevi kada je u proces izračuna uključeno puno ćelija, a ne tri elementa, kao što to imamo mi. Tada potraga može trajati prilično vremena, jer ćete morati proučiti svaki element cikličnosti.

4. Sada moramo razumjeti u kojoj ćeliji (B6 ili D6) sadrži grešku. Iako, formalno to nije ni greška, već jednostavno pretjerana upotreba veza, što dovodi do petlje. Tijekom procesa odlučivanja koja ćelija treba uređivati, mora se primijeniti logika. Ne postoji jasan algoritam postupaka. U svakoj će slučaju ova logika biti drugačija.

   Na primjer, ako bi se u našoj tablici ukupni iznos trebao izračunati množenjem količine stvarno prodane robe s njegovom cijenom, tada možemo reći da je veza koja izračunava iznos ukupnog iznosa prodaje očito suvišna. Stoga ga brišemo i zamjenjujemo statičkom vrijednošću.



5. Izvodimo sličnu operaciju i na svim ostalim cikličkim izrazima, ako ih ima na listu. Nakon što su apsolutno sve kružne reference uklonjene iz knjige, poruka o prisutnosti ovog problema treba nestati iz statusne trake.

   Osim toga, jesu li ciklični izrazi potpuno uklonjeni, možete saznati pomoću alata za provjeru pogreške. Idite na karticu Formule i kliknite na već poznati trokut nama s desne strane gumba "Provjeri greške" u grupi alata Zavisnosti formule. Ako se u meniju koji se otvori otvorite "Kružne veze" neće biti aktivan, to znači da smo izbrisali sve takve objekte iz dokumenta. U suprotnom, bit će potrebno primijeniti postupak brisanja na elemente koji se nalaze na popisu na isti način kao što je prethodno razmatrano.

Dozvola za povratnu petlju

U prethodnom dijelu lekcije uglavnom smo razgovarali o tome kako postupati s kružnim vezama ili kako ih pronaći. Ali, ranije je razgovor bio i o činjenici da u nekim slučajevima, naprotiv, korisnik može biti koristan i svjesno ih koristiti. Na primjer, prilično često se ova metoda koristi za iterativne proračune u konstrukciji ekonomskih modela. No, problem je u tome što će, bez obzira da li svjesno ili nesvjesno upotrebljavate kružni izraz, Excel prema zadanim postavkama i dalje blokirati rad na njima, kako ne bi došlo do pretjeranog preopterećenja sustava. U ovom slučaju, pitanje prisilnog onesposobljavanja takve brave postaje relevantno. Da vidimo kako to učiniti.

1. Prije svega, prijeđite na karticu Datoteka Excel aplikacije.



2. Zatim kliknite na stavku "Opcije"koja se nalazi na lijevoj strani prozora koji se otvara.



3. Pokreće se prozor opcija programa Excel. Moramo ići na karticu Formule.



4. U prozoru koji se otvori moguće je dozvoliti izvršenje cikličkih operacija. Idemo u desni blok ovog prozora, gdje se nalaze i same Excel postavke. Radit ćemo s blokom postavki Parametri izračunakoji se nalazi na samom vrhu.

   Da biste omogućili upotrebu cikličkih izraza, potvrdite okvir pokraj parametra Omogući Iterativno računanje. Uz to se u isti blok može postaviti i granični broj ponavljanja i relativna greška. Prema zadanim postavkama njihove vrijednosti su 100, odnosno 0,001. U većini slučajeva ove parametre nije potrebno mijenjati, mada po potrebi ili po želji možete vršiti promjene u ovim poljima. Ali ovdje trebate uzeti u obzir da previše ponavljanja može dovesti do ozbiljnog opterećenja programa i sustava u cjelini, posebno ako radite s datotekom koja sadrži mnogo cikličnih izraza.

   Dakle, potvrdite okvir pored parametra Omogući Iterativno računanje, a zatim kako bi nova podešavanja stupila na snagu, kliknite na gumb "OK"koji se nalazi na dnu prozora opcija programa Excel.



5. Nakon toga automatski idemo na list trenutne knjige. Kao što vidite, u ćelijama u kojima se nalaze cikličke formule sada se vrijednosti ispravno izračunavaju. Program u njima ne blokira proračune.

Ipak, vrijedno je napomenuti da se sa uključivanjem cikličnih operacija ne treba zloupotrijebiti. Ovu značajku koristite samo kad je korisnik u potpunosti siguran u njezinu neophodnost. Nerazumno uključivanje cikličkih operacija ne može samo dovesti do prekomjernog opterećenja sustava i usporiti proračune pri radu s dokumentom, već korisnik može nenamjerno uvesti pogrešan ciklički izraz, koji bi program po principu odmah blokirao program.

Kao što vidimo, u velikoj većini slučajeva kružne reference fenomen su koji treba riješiti. Za to je prvo potrebno detektirati sam ciklički odnos, zatim izračunati ćeliju u kojoj se nalazi greška i, na kraju, ukloniti je odgovarajućim prilagođavanjem. U nekim slučajevima, ciklične operacije mogu biti korisne u proračunima i korisnik ih obavlja svjesno. Ali čak i tada, njihovoj upotrebi treba pristupiti s oprezom, pravilno postavljanjem Excela i poznavanjem mjere u dodavanju takvih veza, koje, ako se koriste skupno, mogu usporiti sustav.