Funkcije u excelu
Već smo rekli da formule mogu sadržavati i funkcije. Postoji više vrsta funkcija. Jedna vrsta funkcija predstavlja samo skraćeni način zapisivanja nekih drugih izraza. Na primjer, sum (D3:D8) predstavlja skraćeni zapis za izraz D3+D4+D5+D6+D7+D8, dok sum(B5:E5) predstavlja skraćeni zapis za izraz B5+C5+D5+E5. Iza imena funkcije (sum u navedenim primjerima), uvijek u paru zagrada slijede informacije koje govore na šta se konkretno funkcija odnosi (D3: D8 odnosno B5 :E5 u navedenim primjerima). Ove informacije zovu se argumenti funkcija. Još jedan primjer slične funkcije je funkcija average, koja služi za računanje prosječne vrijednosti. Na primjer, average(D3:D8) je skraćeni zapis za izraz (D3+D4+D5+D6+D7+D8) /6, odnosno sum(D3:D8)/6. Naročita korist od funkcije average je činjenica da ne moramo brojati podatke, kao i da ne moramo prepravljati formulu svaki put kada dodamo odnosno obrišemo neki podatak. Druga vrsta funkcija služi za obavljanje nekih radnji koje se inače ne bi mogle izvesti bez njih. Na primjer, funkcija sqrt računa kvadratni korijen svog argumenta, što se ne može uraditi bez nje. Funkcije mogu biti sastavni dio složenijih izraza koji čine formulu, tako da je formula =1+average(D3:D8)/(sqrt(A2-1)+1) sasvim legalna formula.
Različite funkcije prihvataju različite vrste argumenata. Većina funkcija kao argumente prihvataju izraze. Pojedine funkcije (poput sum i average) prihvataju specijalne vrste argumenata nazvane opsezi (engl. range) i liste (engl. list). Opsezi predstavljaju imena dvije ćelije razdvojene znakom dvotačka (:) i predstavljaju sve ćelije u pravougaonom području čiji uglovi leže na naznačenim ćelijama. Na primjer, opseg B2:B6 predstavlja ćelije B2, B3, B4, B5 i B6, opseg C2:F2 predstavlja ćelije C2, D2, E2 i F2, dok opseg B2:C5 predstavlja ćelije B2, B3, B4, B5, C2, C3, C4 i C5. Liste predstavljaju skupine izraza i opsega koji su međusobno razdvojeni znakom tačka-zarez (;). Na primjer, A2:A5;5*B4+l;C3:C6;-2 je primjer liste. Sam opseg ili izraz također se smatraju specijalnim slučajem listi.
Excel posjeduje izuzetno veliki broj funkcija (oko 250 u verziji Microsoft Excel 97), od kojih mnoge obavljaju veoma složene matematičke, statističke ili inžinjerske proračune. Srećom, već je poznavanje svega desetak funkcija dovoljno za izradu veoma moćnih proračunskih tablica. U funkcije koje svakako treba poznavati spadaju funkcije opisane u sljedećoj tabeli:
|
SUM(lista) |
Daje kao rezultat sumu svih elemenata u listi lista. Na primjer, SUM(A3,B5;l) je isto što i A3+B5+1. Lista obično sadrži barem jedan opseg ćelija. Na primjer, SUM(C2:C6) je skraćeni zapis za izraz C2+C3+C4+C5+C6, dok je SUM(A1,B2:C4,1) skraćeni zapis za izraz A1+B2+B3+B4+C2+C3+C4+1. |
||
|
COUNT(lista) |
Daje kao rezultat broj ćelija koje sadrže brojeve u listi lista, koja se obično sastoji od jednog ili više opsega ćelija. Na primjer, COUNT(C2:C6) daje kao rezultat broj ćelija u opsegu od C2 do C6 koje sadrže brojeve. |
||
|
COUNTA(lista) |
Djeluje slično poput funkcije COUNT, ali broji sve ćelije koje nisu prazne, tj. koje sadrže barem neki podatak. |
||
|
COUNTBLANK(lista) |
Djeluje slično poput funkcije COUNT, ali broji sve ćelije koje su prazne, tj. koje ne sadrže niti jedan podatak. |
||
|
AVERAGE(lista) |
Daje kao rezultat aritmetičku sredinu svih elemenata u listi lista (tj. sumu svih elemenata podijeljenu sa brojem elemenata). AVERAGE(lista) ne predstavlja ništa drugo nego skraćeni zapis za izraz SUM(lista)/COUNT(lista). |
||
|
MAX(lista) |
Daje kao rezultat najveći od svih brojčanih elemenata u listi lista. Na primjer, MAX(C3:G3) daje kao rezultat najveći od svih brojeva upisanih u ćelije u opsegu od C3 do G3. |
||
|
MIN(lista) |
Daje kao rezultat najmanji od svih brojčanih elemenata u listi lista. |
||
|
SQRT(izraz) |
Daje kao rezultat kvadratni korijen vrijednosti izraza izraz. |
||
|
ABS(izraz) |
Daje kao rezultat apsolutnu vrijednost (modul) izraza izraz. |
||
|
ROUND(izraz;brdec) |
Daje kao rezultat vrijednost izraza izraz zaokruženu na broj decimala određen izrazom brdec. Na primjer, ROUND(A3;2) daje kao rezultat vrijednost ćelije A3 zaokruženu na dvije decimale. Ukoliko je vrijednost izraza brdec nula, izraz izraz zaokružuje se na najbliži cijeli broj. |
||
|
COUNTIF(opseg;kriterij) |
Daje kao rezultat broj ćelija u opsegu opseg koje ispunjavaju kriterij kriterij. Kriterij može biti proizvoljan broj ili izraz. U tom slučaju broje se ćelije čija je vrijednost jednaka vrijednosti navedenog broja ili izraza. Na, primjer, COUNTIF(B2:B10;5) daje kao rezultat broj ćelija u opsegu od B2 do B10 čiji je sadržaj 5. Ukoliko želimo da brojimo ćelije koje sadrže neki tekst, kao kriterij zadajemo željeni tekst uokviren znacima navoda, npr. COUNTIF(C4:F4;"Senad"). Kao kriteriji se mogu upotrijebiti i konstrukcije poput ">broj" i " |
||
|
IF(uvjet;ako_da;ako_ne) |
Testira uvjet uvjet, i ako je uvjet tačan, daje kao rezultat vrijednost izraza ako_da, a u suprotnom, daje kao rezultat vrijednost izraza ako_ne. Uvjet je zapravo izraz, koji pored uobičajenih operacija sadrži i barem jednu od relacija manje (<), veće (>), jednako (=), manje ili jednako (<=), veće ili jednako (>=) ili različito (<>). Uvjet mora biti tačan ili netačan. Na primjer, B3>A5+2 je uvjet koji je tačan ukoliko je sadržaj ćelije B3 veći od sadržaja ćelije A5 uvećanog za 2, a u suprot nom je netačan. Izrazi ako_da i ako_ne mogu predstavljati i neki tekst uokviren znakovima navoda. |
||
Neke od ovih funkcija djeluju na prvi pogled dosta komplicirano. Stoga ćemo njihovu upotrebu objasniti na konkretnim primjerima. Pretpostavimo, na primjer, da su u ćelije od C3 do G3 upisane ocjene nekog učenika (pri čemu prostor za neke od ocjena može da bude i prazan). Formula
=max(C3:G3) izračunat će najbolju ocjenu tog učenika. Formula
=countif(C3:G3;-1) izračunat će koliko učenik ima slabih ocjena, formula
=countif(C3:G3;">l") izračunat će koliko učenik ima prolaznih ocjena, dok će formula
=count(C3:G3)-countif(C3:G3;3) izračunati koliko učenik ima ocjena koje nisu trojke.
U slučaju daje u ćeliji H3 upisan broj slabih ocjena nekog učenika, a u ćeliju I3 srednja vrijednost svih ocjena (uključujući i slabe ocjene), tada će formula =if(H3=0;I3;l) dati kao rezultat prosjek ocjena učenika ili jedinicu, zavisno od toga da li učenik ima slabih ocjena ili ne. Ukoliko je ova formula upisana u ćeliju J3, tada će formula =round(J3;0) dati kao rezultat uspjeh učenika (tj. prosjek ocjena zaokružen na najbliži cijeli broj, ili jedinicu). Formula =IF(H3=0;"Prošao";"Pao") upisaće u pripadnu ćeliju tekst "Prošao" ili "Pao" ovisno od toga da li učenik ima slabih ocjena ili ne.
Nema nikakvog razloga da se kao argument neke od funkcija ne upotrijebi izraz koji također sadrži neku funkciju. Na primjer, ukoliko ponovo pretpostavimo da ćelije od C3 do G3 sadrže ocjene nekog učenika, moguće je odmah napisati jedinstvenu formulu koja će odrediti uspjeh učenika. Takva formula glasi:
=IF(COUNTIF(C3:G3;1)=0;ROUND(AVERAGE(C3:G3);0);1)
Ova formula na prvi pogled djeluje potpuno nerazumljivo. Međutim, ukoliko se ona pažljivo raščlani, vidjećemo da u njoj nema ništa posebno neobično. Izraz se sastoji od funkcije IF, u kojoj se kao uvjet javlja izraz COUNTIF(C3:G3,l)=0, koji ponovo sadrži funkciju COUNTIF(C3:G3;l) koja broji jedinice u opsegu ćelija od C3:G3 (tj. broji slabe ocjene). Stoga je uvjet COUNTIF(C3:G3;1)=0 tačan ako je broj jedinica jednak nuli, tj. ukoliko učenik nema slabih ocjena. Ukoliko je uvjet ispunjen, funkcija if kao rezultat daje vrijednost izraza ROUND(AVERAGE(C3:G3);0), a ukoliko uvjet nije ispunjen, funkcija IF kao rezultat daje jedinicu (treći argument funkcije IF), tako da je uspjeh jednak jedinici ukoliko je makar jedna ocjena jednaka jedinici. Ostaje još da detaljnije razmotrimo izraz ROUND(AVERAGE(C3:G3);0) čija se vrijednost dobija kao krajnji rezultat ukoliko učenik nema jedinica. Funkcija ROUND zaokružuje svoj prvi argument AVERAGE(C3:G3) na najbliži cijeli broj, jer je njen drugi argument 0. Drugim riječima, ova funkcija zaokružuje srednju ocjenu učenika na najbliži cijeli broj, a upravo tako se računa uspjeh učenika.
Na ovom mjestu moramo istaći jednu bitnu činjenicu. Naime, ukoliko su regionalne postavke računara podešene tako da se za razdvajanje decimalnog od cijelog dijela broja koristi tačka umjesto zareza, tada se za razdvajanje argumenata u funkcijama i za razdvajanje stavki u listama koristi obični zarez, a ne tačka-zarez! U tom slučaju bismo glomaznu formulu iz prethodnog primjera trebali zapisati ovako:
=IF(COUNTIF(C3:G3,1)=0;ROUND(AVERAGE(C3:G3),0),1)
Stoga, ukoliko imate problema sa prihvatanjem formula koje sadrže znak tačka-zarez, probajte ga zamijeniti zarezom, i vjerovatno će sve biti u redu.
Možda sve ovo što smo do sada rekli o formulama i funkcijama djeluje pomalo komplicirano, ali tek kada u potpunosti shvatite sve o čemu se ovdje govori, možete reći da ste savladali tehniku kreiranja proračunskih tablica. Inače, kada savladate gore opisane funkcije, steći ćete dovoljno znanja da možete u potpunosti kreirati proračunsku tablicu za vođenje osnovne statistike o uspjehu učenika nekog razreda koju smo opisivali u prvom dijelu udžbenika. Najbolje je da jednu takvu tablicu napravite kao praktičnu vježbu, eventualno uz pomoć nastavnika.
Zadatak: Koje vrijednosti imaju formule:
1. Napiši formulu =(C1+C2+C3+C4+C5+C6)/6 na kraći način?
2. Napiši formulu =SUM(A1:B3) bez korištenja funkcije.
Excel posjeduje još jednu interesantnu mogućnost za unos funkcija, koja je posebno korisna ukoliko nismo tačno sigurni kako se funkcija piše. Pokretanjem naredbe Function iz menija Insert ili alatke Paste Function sa "Standard" trake sa alatima, otvara se dijalog (prikazan na slici) koji omogućava izbor željene funkcije iz spiska. Sve funkcije su, radi lakšeg snalaženja, razvrstane u nekoliko kategorija. Kategorija Most Recently Used sadrži popis posljednjih nekoliko korištenih funkcija. Ukoliko želimo popis svih funkcija, trebamo izabrati kategoriju All. Nakon što smo izabrali željenu funkciju, pojavljuje se novi dijalog, čiji tačan izgled ovisi od izabrane funkcije (na slici sa strane prikazan je dijalog za funkciju IF). Ovaj dijalog sadrži polja za unos, u koje treba upisati argumente funkcije. Ukoliko željeni argument predstavlja ime ćelije ili opseg ćelija, umjesto unosa pomoću tastature moguće je mišem kliknuti na željenu ćeliju ili označiti željeni opseg. Ukoliko želimo da označimo argument mišem, a smeta nam prisustvo dijaloga na ekranu, klikom na dugme sa nacrtanom crvenom strelicom u krajnjem desnom dijelu odgovarajućeg polja za unos (vidljivo na prikazanoj slici) moguće je privremeno minimizirati ovaj dijalog na veličinu trake, i ponovo ga klikom na isto dugme vratiti na prvobitnu veličinu. Ukoliko se kao argument funkcije javlja ponovo neka druga funkcija, nju je moguće izabrati iz padajuće liste koja se pojavljuje na mjestu polja sa imenom ćelije u traci funkcija. Ovakav način unosa funkcija zahtijeva dosta prakse, ali bitno ubrzava rad nakon što se korisnik malo izvježba (u krajnjem slučaju, ukoliko vam je teško ovako unositi funkcije, uvijek ih možete ukucavati ručno). Dodatna prednost ovakvog načina unošenja leži u činjenici da se ne morate brinuti da li se za razdvajanje argumenata koristi zarez ili tačka-zarez (Excel o tome vodi brigu sam).
Kao što je već rečeno, Excel omogućava analize tipa "šta je potrebno uraditi da bi se postigao zadani cilj". Na primjer, ukoliko nas zanima na koju vrijednost bi se trebao povećati prihod firme "Kristalinvest" u toku aprila (ćelija F3) da bi se prosječni polugodišnji prihod (ćelija I6) povećao sa 1020 na 1050, u Tools meniju treba izabrati naredbu Goal Seek, a zatim u polja za unos nazvana Sef cell, To value i By changing cell upisati redom I6, 1050 i F3. Excel će obaviti analizu, i zaključiti da se aprilski prihod firme "Kristalinvest" mora povećati sa 130 na 220. Analiza pomoću naredbe Goal Seek može se koristiti samo za ispitivanje utjecaja jedne ćelije na neku drugu ćeliju. Za detaljnije analize treba koristiti naredbu Solver iz Tools menija (koja je također i znatno složenija za upotrebu).