Obavljanje proračuna u radnim listovima
Osnovnu snagu programima za tabelarna izračunavanja daju formule. Formule definiraju način na koji sadržaj neke od ćelija zavisi od sadržaja drugih ćelija. Formule u programu Microsoft Excel obavezno počinju znakom "=" iza kojeg mora slijediti izraz. Izrazi predstavljaju pravila koja govore kako se računa sadržaj ćelije u koju je formula upisana. Izrazi mogu sadržavati brojeve, imena ćelija (bilo generička, bilo korisnički definirana imena), oznake osnovnih matematičkih operacija poput sabiranja (+), oduzimanja (-), množenja (*) i dijeljenja (/), zatim zagrade za definiranje prioriteta operacija (s obzirom da množenje i dijeljenje imaju viši prioritet u odnosu na sabiranje i oduzimanje), kao i funkcije, sa kojima ćemo se uskoro upoznati. Bitno je naglasiti da se u izrazima uvijek koriste isključivo male zagrade, bez obzira na njihov broj i mjesto u izrazu. Pretpostavimo, na primjer, da smo u ćeliju C7 unijeli formulu koja glasi =1+ (B2*3+Bl-5)/2. Ova formula sadrži izraz 1+ (B2*3+Bl-5)/2 koji govori da sadržaj ćelije C7 treba da bude jednak zbiru broja 1 i polovine vrijednosti izraza B2*3+Bl-5 (tj. polovine vrijednosti proizvoda sadržaja ćelije B2 sa brojem 3, uvećanog za sadržaj ćelije B1 i umanjenog za 5).
Prilikom unosa formula u ćelije treba voditi računa o nekoliko činjenica. Prvo, formula ne smije sadržavati nikakve razmake, inače neće biti shvaćena kao formula, ili će Excel odbiti da je prihvati (korisnici često prave grešku tako što nesvjesno upišu razmak ispred znaka jednakosti). Drugo, prilikom unosa formule direktno u ćeliju, svaki klik mišem na neku od ćelija ne djeluje na uobičajeni način, već dopisuje u formulu ime trenutno obilježene ćelije. Isto vrijedi i za upotrebu tipki sa strelicama. Ova osobina može ponekad biti jako korisna, međutim početnike u većini slučajeva samo zbunjuje. Stoga, ukoliko želimo uređivati formulu unutar ćelije pomoću tipki sa strelicama, moramo se koristiti isključivo poljem za uređivanje formula u traci formula (ovo polje aktiviramo klikom miša unutar njega). Da bismo započeli unos formule u polje za uređivanje formula, dovoljno je kliknuti mišem na dugme označeno znakom "=" u traci formula. Također, unos formule uvijek treba završiti isključivo pritiskom na tipku ENTER ili klikom na dugme sa zelenom kvačicom.
Zadaci:
1. Izračunaj u Excel-u y = ax2 + bx + c, za sljedeće vrijednosti promjenjivih a=2; b=4; c=-3 i x=3.
2. Izračunaj u Excel-u a = (F -μ m g) / m, za sljedeće vrijednosti promjenjivih F=10;μ=0,1;m=5 i g=9,81.
Rješenje zadatka 1 Rješenje zadatka 2
Rad sa formulama objasnićemo na jednom konkretnom primjeru. Pretpostavimo da je potrebno da vodimo evidenciju o ostvarenom prihodu (u hiljadama KM) po mjesecima u toku polugodišnjeg perioda (Januar — Juni) za tri preduzeća: "Kristalinvest", "Drvopromet" i "Mašinotehna". Pored toga, potrebni su nam podaci o ukupnom prihodu za sva tri preduzeća u toku polugodišnjeg perioda, zatim ukupni prihod svih preduzeća (total), kao i prosječni ostvareni prihodi po mjesecima. Pretpostavimo da smo unijeli podatke o mjesečnim prihodima u radnu stranicu, i oblikovali tabelu kao na sljedećoj slici:
Naravno, ukupne prihode, total, kao i prosječne prihode, možemo bez problema izračunati i ručno. Međutim, zašto ne pustiti Excel da obavi ovaj posao za nas? Mada postupak koji je neophodno obaviti za tu svrhu na prvi pogled izgleda malo složeniji, korist od takvog postupka je višestruka. Stoga, krenimo na posao. Ukupni prihod za "Kristalinvest", koji treba da se upiše u ćeliju I3, računa se kao zbir prihoda ovog preduzeća za svih 6 mjeseci, koji su upisani u ćelije C3, D3, E3, F3, G3 i H3. Upišimo stoga u ćeliju I3 formulu =C3+D3+E3+F3+G3+H3 (kasnije ćemo vidjeti kako se ova formula može zapisati kraće). Ukoliko smo formulu unijeli kako treba, nakon pritiska na tipku ENTER formula nestaje sa ekrana, i umjesto nje pojavljuje se odgovarajući rezultat (koji u navedenom primjeru iznosi 980). Pri tome, formulu i dalje možemo da vidimo u polju za uređivanje formula kad god je odgovarajuća ćelija aktivna. Također, dvostruki klik na ćeliju prikazuje upisanu formulu, i omogućava njeno naknadno uređivanje. Ukoliko se nije pojavio rezultat, a sigurni ste da ste formulu pravilno unijeli, pokrenite naredbu Options iz menija Tools, a zatim provjerite da li je uključena opcija Formulas na kartici View (ukoliko jeste, isključite je).
Na sličan način možemo unijeti formule koje će računati ukupan prihod za ostala dva preduzeća. Tako, u ćeliju 14 treba unijeti formulu =C4+D4+E4+F4+G4+H4, dok u ćeliju 15 treba unijeti formulu =C5+D5+E5+F5+G5+H5 (kasnije ćemo vidjeti kako se ovaj postupak može ubrzati, ali za početak malo vježbanja ručnog unošenja formula neće škoditi). Total je, naravno, jednak zbiru sadržaja ćelija I3, I4 i I5, tako da u ćeliju I8 treba upisati formulu =I3 + I4 + I5 (napomenimo da formulu uvijek treba započeti u novoj ćeliji, a ne nipošto kao produžetak teksta u nekoj od ćelija koje sadrže tekst). Ostaje još da izračunamo prosječne prihode po mjesecima. Prosjek ćemo dobiti ukoliko saberemo prihode za sva tri preduzeća i podijelimo zbir sa ukupnim brojem preduzeća (tj. sa 3). Stoga, formule koje treba upisati u ćelije C6, D6, E6, F6, G6 i H6 treba redom da glase =(C3+C4+C5)/3, =(D3+D4+D5)/3, =(E3+E4+E5)/3, =(F3+F4+F5)/3, =(G3+G4+G5)/3 i =(H3+H4+H5)/3. U ćeliju I6, koja bi trebala da sadrži prosječni prihod za čitav polugodišnji period, možemo upisati formulu =C6+D6+E6+F6+G6+H6, formulu =(I3 + I4 + I5)/3, ili formulu =I8/3 (sve tri formule daju isti rezultat — razmislite zašto). Napomenimo da nije potrebno širiti kolone da bismo upisali dugačku formulu, jer će prikaz formule ionako biti zamijenjen prikazom odgovarajućeg rezultata.
Kako će prosječne vrijednosti često biti brojevi koji nisu cijeli, pomoću alatki Increase Decimal i Decrease Decimalna "Formatting" traci sa alatima možemo oblikovati ćelije C6, D6, E6, F6, G6, H6 i 16 da prikazuju sadržaj uvijek na dvije decimale. Nakon što smo obavili cjelokupni opisani postupak, radna stranica bi trebala da izgleda ovako:
Sve ovo izgleda lijepo, međutim do istog rezultata smo mogli doći brže uz pomoć običnog džepnog kalkulatora. Da bismo vidjeli pravu svrhu formula, pretpostavimo da iz nekog razloga treba da promijenimo vrijednost nekog od mjesečnih prihoda. Promijenimo, recimo, iznos prihoda preduzeća "Kristalinvest" za mjesec april (ćelija F3) sa 130 na 180 i pogledajmo šta se dešava. Istog trenutka kada izvršite ovu promjenu, primijetićete da se automatski promijenio i sadržaj nekih drugih ćelija. Sadržaj ćelije F6 promijenio se sa 136,67 na 153,33, sadržaj ćelije I3 promijenio se sa 980 na 1030, sadržaj ćelije I6 promijenio se sa 1020,00 na 1036,67, dok se sadržaj ćelije I8 promijenio sa 3060 na 3110. Ukoliko se ove promjene nisu dogodile, pokrenite naredbu Options iz menija Tools, i provjerite da li je aktivno radio dugme Automatic na kartici Calculation (ukoliko nije, aktivirajte ga). Šta se zapravo dešava? Ćelije I3 i F6 zavise od sadržaja ćelije F3 preko formula upisanih u njih, ćelija I8 zavisi od ćelije I3, dok ćelija I6 zavisi od ćelija F6, I3 ili I8 (zavisno koju ste formulu koristili), tako da je promjena sadržaja ćelije F3 pokrenula "lančanu reakciju" koja je dovela do izmjene sadržaja svih ovih ćelija. Da smo ručno računali i upisivali vrijednosti, nakon svake promjene morali bismo vršiti ponovna preračunavanja, pri čemu bismo prethodno morali provjeriti šta od čega zavisi (uz veliku vjerovatnoću da ćemo nešto previdjeti). Ovako smo se jednom pomučili da napravimo "pametnu" tabelu, koju kasnije možemo koristiti, i po potrebi samo mijenjati neovisne podatke. Ova automatizacija naročito dolazi do izražaja kada radimo sa veoma velikim tabelama sa brojnim međuzavisnostima, što je u poslovnoj praksi izuzetno čest slučaj.
U slučaju da iz bilo kojeg razloga formula ne može da izračuna vrijednost (npr. ukoliko upišemo formulu =ai+bi a ćelije A1 ili B1 ne sadrže brojeve nego tekst, ili ukoliko upišemo formulu =ai/bi a ćelija B1 sadrži nulu), umjesto rezultata pojaviće se informacija o grešci, koja uvijek počinje znakom "#", npr. #value! ili #div/0 ! (podsjetimo se da pojava velikog broja znakova "#" u ćeliji ukazuje da je širina odgovarajuće kolone neadekvatna za prikaz odgovarajućeg podatka i rezultata). Također, trebamo paziti da ne napravimo niz formula koje se "vrte u krug" (što se može desiti ukoliko, na primjer, u ćelije A1, B1 i C1 upišemo redom formule =Bl + l, =C1+l i =Al+l). Ovakve greške su prilično česte i nazivaju se kružne reference (engl. circular references). Desi li se da slučajno napravimo kružnu referencu, Excel će nas na to odmah upozoriti. Kružna referenca može nastati i u slučaju da u neku ćeliju upišemo formulu u kojoj se spominje upravo ta ćelija (npr. ukoliko u ćeliju D5 upišemo formulu =D4+D5+D6). Ovakve formule Excel jednostavno neće prihvatiti. Napomenimo još i to da se u mnogim formulama prazne ćelije tretiraju kao da imaju sadržaj jednak nuli.
Unošenje velikog broja formula koje su međusobno slične može biti jako zamorno. Pogledajmo, na primjer, ćelije I3, I4, I5 i I6, u koje smo upisali veoma slične formule =C3+D3+E3+F3+G3+H3, =C4+D4+E4+F4+G4+H4, =C5+D5+E5+F5+G5+H5 i =C6+D6+E6+F6+G6+H6. Zamislimo Šta bismo morali uraditi da smo, umjesto tri, imali npr. 300 preduzeća! Srećom, Excel omogućava da se isti posao obavi mnogo lakše. Dovoljno je unijeti formulu samo u ćeliju I3, a zatim sadržaj te ćelije kopirati u ćelije I4, I5 i I6, pomoću naredbi Copy i Paste, ili još bolje, pomoću crnog znaka plus (+) koji se javlja kada pokazivač miša dovedemo u donji desni ugao ćelije (na način koji smo već objasnili kada smo govorili o samopopunjavanju). Excel će tada, umjesto bukvalnog kopiranja identičnih formula, obaviti automatsko prilagođavanje formula ćelijama u koje se formula kopira. Tako će se, na primjer, formula = (C3+C4+C5)/3, prilikom kopiranja iz ćelije C6 u ćeliju D6, automatski pretvoriti u formulu = (D3+D4+D5) /3. Ova osobina naziva se inteligentno kopiranje ili kopiranje sa samoprilagođavanjem.
Postoje situacije u kojima inteligentno kopiranje može da smeta. Pretpostavimo da želimo da u ovu tablicu dodamo i kolonu "J" u koju ćemo upisati iznose poreza koji treba platiti (u ćelije J3, J4 i J5), i pretpostavimo da je procentualni iznos poreske stope upisan u ćeliju C8. Tada formule za računanje poreza koje treba upisati u ćelije J3, J4 i J5 redom glase =I3*C8/100, =I4*C8/100 i =I5*C8/100. Međutim, ukoliko upišemo samo prvu formulu u ćeliju J3, i izvršimo "inteligentno" kopiranje u ćelije J4 i J5, primijetićemo da je Excel pretvorio prvu formulu redom u formule =I4*C9/100 i =I5*C10/100, što nije korektno! Naime, Excel nije smio da mijenja adresu ćelije C8. Excel, međutim, nije mogao ovo da zna. Da bismo riješili ovaj problem, u ćeliju J3 treba upisati formulu =I3*$C$8/100, nakon čega će inteligentno kopiranje raditi kako treba. Oznaka dolar ($) ispred oznake reda ili kolone govori Excelu da oznaku tog reda odnosno kolone ne treba mijenjati prilikom kopiranja. Adrese ćelija poput "C8", koje se mijenjaju prilikom kopiranja formula u druge ćelije, nazivaju se relativne adrese, dok se adrese ćelija poput "$C$8", koje ostaju nepromijenjene ma gdje se kopirale, nazivaju apsolutne adrese. Postoje i mješovite adrese, poput "$C8" i "C$8", ali se one koriste znatno rjeđe.