nadmašiti

Cikličke reference u Microsoft Excelu

Opće je prihvaćeno da su cikličke reference u Excelu pogrešan izraz. Doista, vrlo često to je tako, ali još uvijek nije uvijek. Ponekad se koriste sasvim svjesno. Da bismo saznali koje su kružne reference, kako ih izraditi, kako pronaći postojeće dokumente, kako ih raditi ili kako ih izbrisati, ako je potrebno.

Koristeći kružne reference

Prije svega, doznajemo što je ciklična referenca. Zapravo, to je izraz koji, pomoću formula u drugim stanicama, odnosi se na sebe. Također, može biti veza koja se nalazi u elementu lima, na koji se odnosi.

Valja napomenuti da su standardne verzije programa Excel automatski blokirale proces izvođenja cikličkog rada. To je zbog činjenice da su takvi izrazi izuzetno pogrešni, a petlje proizvodi stalan proces ponovnog izračuna i računanja, što stvara dodatno opterećenje na sustavu.

Izrada kružne referencije

Sada ćemo vidjeti kako stvoriti najjednostavniji ciklički izraz. To će biti veza smještena u istoj ćeliji kojoj se odnosi.

  1. Odaberite element liste A1 i upiši ga u sljedeći izraz:

    =A1

    Zatim kliknite gumb Enter na tipkovnici.

    2. Izrada jednostavne kružne reference u Microsoft Excelu

  2. Nakon toga pojavit će se dijaloški okvir za upozorenje o cikličkom izrazu. U njemu kliknite gumb "U redu" .

    3. Dijalog za obavijest o kružnom povezivanju u programu Microsoft Excel

  3. Tako smo dobili cikličku operaciju na listi u kojem se stanica odnosi na sebe.

Stanica se odnosi na Microsoft Excel

Neka pomalo komplicira zadatak i stvorimo ciklički izraz iz nekoliko ćelija.

  1. Napišite broj na bilo koji element lista. Neka bude stanica A1 i broj 5 .

    2. Broj 5 u ćeliji u programu Microsoft Excel

  2. U drugoj ćeliji ( B1 ) pišemo izraz:

    =C1

    3. Veza u ćeliji u programu Microsoft Excel

  3. Sljedećem elementu ( C1 ) pišemo sljedeću formulu:

    =A1

    4. Jedna stanica odnosi se na drugu u Microsoft Excelu

  4. Nakon toga vraćamo se u ćeliju A1 u kojoj je postavljen broj 5 . Ono se odnosi na element B1 :

    =B1

    Pritisnite tipku Enter .

    5. Postavljanje veze u ćeliji u programu Microsoft Excel

  5. Dakle, ciklus je zatvoren i dobivamo klasičnu cikličku referencu. Nakon zatvaranja prozora upozorenja vidimo da je program označio cikličku vezu plave strelice na listu, koje se nazivaju strelicama tragova.

Označavanje cikličkog povezivanja u programu Microsoft Excel

Sada nastavimo stvarati ciklični izraz pomoću tablice primjera. Imamo stol za prodaju hrane. Sastoji se od četiri stupca koji označavaju naziv proizvoda, broj prodanih proizvoda, cijenu i iznos prihoda od prodaje cjelokupnog volumena. U tablici u zadnjem stupcu već postoje formule. U njima, prihod se izračunava množenjem količine po cijeni.

Izračun prihoda u tablici u programu Microsoft Excel

  1. Da biste fiksirali formulu u prvom retku, odaberite element listova s ​​količinom prvog proizvoda ( B2 ). Umjesto statičke vrijednosti ( 6 ) upisujemo formulu koja će uzeti količinu robe dijeljenjem ukupnog iznosa ( D2 ) po cijeni ( C2 ):

    =D2/C2

    Kliknemo na gumb Enter .

    2. Umetanje kružne reference u tablicu u programu Microsoft Excel

  2. Imamo prvu cikličku vezu, odnos u kojem se obično označava strelicom traga. No, kao što možete vidjeti, rezultat je pogrešan i jednak nuli, jer je već rečeno, Excel blokira izvršavanje cikličkih operacija.

    3. Ciklička referenca u tablici u programu Microsoft Excel

  3. Kopirajte izraz na sve ostale ćelije u stupcu s brojem proizvoda. Da biste to učinili, postavite pokazivač u donji desni kut elementa koji već sadrži formulu. Kursor se pretvara u križ, koji se obično naziva oznaka za ispunjavanje. Pritisnite lijevu tipku miša i povucite ovaj križ do kraja tablice prema dolje.

    4. Popunjavanje u programu Microsoft Excel

  4. Kao što možete vidjeti, izraz je kopiran na sve elemente stupca. No, samo je jedan znak označen strelicom za praćenje. Napominjemo ovo za budućnost.

Cikličke veze kopirane u tablicu u programu Microsoft Excel

Pronalaženje kružnih referenci

Kao što smo vidjeli gore, ne uvijek puta program označava odnos kružne reference na objekte, čak i ako postoji na listi. S obzirom na činjenicu da je velika većina cikličnih operacija štetna, trebalo bi ih ukloniti. Ali za to se najprije moraju naći. Kako se to može učiniti ako izrazi nisu označeni strelicom? Bavimo se ovim zadatkom.

  1. Dakle, ako otvorite prozor s informacijama kada pokrenete Excel datoteku koja sadrži kružnu vezu, trebali biste je pronaći. Zbog toga prelazimo na karticu "Formule" . Kliknite na vrpcu na trokutu, koji se nalazi desno od gumba "Provjera pogrešaka" koji se nalazi u okviru alata "Formula Dependencies" . Otvara se izbornik za pomicanje pokazivača na stavku "Cyclic links" . Nakon toga, sljedeći izbornik otvara popis adresa elemenata listova u kojima je program pronašao cikličke izraze.

    2. Traženje kružnih referenci u Microsoft Excelu

  2. Kada kliknete na određenu adresu, odabrana je odgovarajuća ćelija na listu.

Idite na ćeliju s kružnom referencom u programu Microsoft Excel

Postoji još jedan način da saznate gdje se nalazi kružna veza. Poruka o ovom problemu i adresa elementa koji sadrži takav izraz nalazi se na lijevoj strani trake statusa koja se nalazi na dnu prozora programa Excel. Međutim, za razliku od prethodne varijante, na traci stanja prikazat će se adrese svih elemenata koji sadrže kružne veze, ako ih ima puno, ali samo jedan od njih, koji se pojavio pred ostalima.

Poruka o kružnoj vezi u traci stanja u programu Microsoft Excel

Osim toga, ako ste u knjizi koja sadrži ciklični izraz, a ne na listici gdje se nalazi, ali s druge strane, u ovom će se slučaju na traci stanja prikazati samo poruka o pogrešci bez adrese.

Ciklička veza na drugi list u Microsoft Excelu

Pouka: Kako pronaći kružne reference u programu Excel

Ispravljanje cikličkih veza

Kao što je gore već spomenuto, u velikoj većini slučajeva, ciklička operacija je zlo s kojega se treba riješiti. Stoga je prirodno da se nakon otkrivanja cikličke komunikacije, potrebno je ispraviti kako bi formula postala uobičajeni oblik.

Da bi se ispravila ciklička ovisnost, potrebno je pratiti cijelo međusobno povezivanje stanica. Čak i ako je provjera specificirala određenu ćeliju, pogreška možda nije sama po sebi, već u drugom elementu lanca ovisnosti.

  1. U našem slučaju, unatoč činjenici da je program točno ukazao na jednu od ćelija petlje ( D6 ), stvarna pogreška leži u drugoj ćeliji. Odaberite element D6 da biste saznali koje ćelije iz nje izvlači vrijednost. Pogledajte izraz u traci s formulama. Kao što možete vidjeti, vrijednost u ovom elementu ploče formirana je množenjem sadržaja ćelija B6 i C6 .

    2. Izraz u programu u programu Microsoft Excel

  2. Idite na ćeliju C6 . Odaberite ga i pogledajte redak formata. Kao što možete vidjeti, to je uobičajena statička vrijednost ( 1000 ), koja nije proizvod izračuna formule. Dakle, sa sigurnošću možemo reći da navedeni element ne sadrži pogrešku koja uzrokuje stvaranje cikličnih operacija.

    3. Statička vrijednost u Microsoft Excelu

  3. Idite na sljedeću ćeliju ( B6 ). Nakon odabira u traci formule, vidimo da sadrži izračunati izraz ( = D6 / C6 ), koji povlači podatke iz drugih elemenata tablice, posebno iz ćelije D6 . Dakle, stanica D6 odnosi se na podatke elementa B6 i obratno, što uzrokuje fiksaciju.

    Ciklička referenca u ćeliji tablice u Microsoft Excelu

    Ovdje smo vrlo brzo izračunali odnos, ali u stvarnosti postoje slučajevi kada u procesu obračuna postoji mnogo stanica, a ne tri elementa, kao i mi. Zatim pretraživanje može potrajati dosta dugo, jer morate proučavati svaki element cikličnosti.

  4. Sada moramo razumjeti koja stanica ( B6 ili D6 ) sadrži pogrešku. Iako, formalno, to nije ni pogreška, već samo prekomjerna upotreba referenci, što dovodi do petlje. Tijekom procesa odlučivanja koje ćelije za uređivanje morate primijeniti logiku. Nema jasnog algoritma djelovanja. U svakom slučaju, ta će logika biti sama.

    Na primjer, ako se u našoj tablici ukupni iznos treba izračunati množenjem količine stvarno prodane robe prema njegovoj cijeni, možemo reći da je veza koja izračunava iznos od ukupnog iznosa prodaje jasno suvišna. Stoga ga izbrišemo i zamijenite statičkom vrijednosti.

    5. Referenca je zamijenjena vrijednostima u programu Microsoft Excel

  5. Izvršavamo sličnu operaciju na svim ostalim cikličkim izrazima, ako postoje na listi. Nakon apsolutno sve kružne reference uklonjene su iz knjige, poruka o prisutnosti ovog problema trebala bi nestati sa trake statusa.

    Osim toga, ako su ciklički izrazi potpuno izbrisani, možete saznati pomoću alata za provjeru pogrešaka. Idite na karticu "Formulas" i kliknite na trokut koji već znamo, desno od gumba "Provjeri pogreške" na alatnoj traci "Formula Dependencies" . Ako stavka "Cikličke veze" nije aktivna na izborniku, uklonili smo sve takve predmete iz dokumenta. U suprotnom ćete morati primijeniti postupak brisanja stavkama na popisu na isti način kao i prije.

Cikličke reference u knjizi nisu u Microsoft Excelu

Omogućavanje cikličkih operacija

U prethodnom dijelu lekcije uglavnom smo govorili o tome kako se nositi s cikličkim referencama ili kako ih pronaći. No, ranije je razgovor bio i činjenica da u nekim slučajevima oni, naprotiv, mogu biti korisni i svjesno upotrebljeni od strane korisnika. Na primjer, ova se metoda često koristi za iterativne izračune u izgradnji ekonomskih modela. No, nevolja je ta, bez obzira na to da li svjesno ili nesvjesno upotrebljavate cikličke izraze, Excel će prema zadanim postavkama i dalje blokirati operaciju na njima kako ne bi uzrokovala prekomjerno preopterećenje sustava. U ovom slučaju, pitanje prisilnog onesposobljavanja takve brave postaje aktualno. Pogledajmo kako to učiniti.

Blokirajte cikličke veze u programu Microsoft Excel

  1. Prije svega, premjestit ćemo se na karticu "Datoteka" programa Excel.

    2. Premještanje na karticu Datoteka u programu Microsoft Excel

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

    3. Idite na prozor opcija u programu Microsoft Excel

  3. Počinje se prozor opcije programa Excel. Moramo ići na karticu "Formulas" .

    4. Idite na karticu Formule u programu Microsoft Excel

  4. U prozoru se otvara da ćete moći obavljati cikličke operacije. Prelazimo u desni blok ovog prozora, gdje se nalaze same postavke programa Excel. Radit ćemo s konfiguracijskim blokom "Izgledi za izračunavanje" koji se nalazi na samom vrhu.

    Da biste dopustili upotrebu cikličkih izraza, morate potvrditi okvir opcije Omogući izračune Iterativnih izraza. Osim toga, u istom bloku možete postaviti maksimalni broj iteracija i relativnu pogrešku. Prema zadanim postavkama, njihove su vrijednosti jednako 100 i 0.001. U većini slučajeva ne morate mijenjati ove parametre, iako možete po potrebi napraviti promjene na navedenim poljima. Ali ovdje je potrebno uzeti u obzir da previše iteracija može dovesti do ozbiljnog opterećenja programa i sustava u cjelini, pogotovo ako radite s datotekom koja sadrži mnoge cikličke izraze.

    Zato provjerite opciju "Omogući iterativne izračune" , a zatim kliknite gumb "U redu" koji se nalazi pri dnu prozora opcija programa Excel kako bi nove postavke postale učinkovite.

    5. Omogućavanje Iterativnih izračuna u programu Microsoft Excel

  5. Nakon toga, automatski idemo na listu trenutne knjige. Kao što možete vidjeti, u stanicama u kojima se nalaze cikličke formule, sada su vrijednosti ispravno izračunate. Program ne blokira izračune u njima.

Ćelije s cikličkim formulama prikazuju ispravne vrijednosti u programu Microsoft Excel

Ipak, valja istaknuti da uključivanje cikličnih operacija ne bi trebalo biti zlostavljano. Koristite ovu značajku samo ako je korisnik potpuno siguran u svoju potrebu. Nerazumno uključivanje cikličkih operacija ne samo da može dovesti do pretjeranog opterećenja sustava i usporiti izračun dok se radi s dokumentom, ali korisnik može nenamjerno unijeti pogrešan ciklički izraz koji će program automatski blokirati.

Kao što vidimo, u ogromnoj većini slučajeva, cikličke reference su fenomen koji treba riješiti. Da biste to učinili, prije svega trebate pronaći najkoksičniji odnos, a zatim izračunati ćeliju u kojoj se nalazi ta pogreška, i konačno ga ukloniti tako da podesite odgovarajuće prilagodbe. No, u nekim slučajevima, ciklička operacija može biti korisna u izračunima i svjesno sastavljena od strane korisnika. Ali čak i tada, vrijedno ih je koristiti s oprezom, pravilno postavljanjem programa Excel i poznavanjem opsega dodavanja takvih veza koje, kada se koriste u rasutom stanju, mogu usporiti sustav.