Računanje u tablici - formule i funkcije

1. Uvod

Excelova  moć leži upravo u mogućnosti uporabe formula i funkcija. One uspostavljaju vezu između podataka.

Formula je matematički izraz na ćelijama, a funkcija je matematički izraz unaprijed ugrađen u Excel.

2. Formule

Formulama se obavljaju operacije zbrajanja, oduzimanja,množenja,dijeljenja ili uspoređivanja
podataka unesenih u radni list. S pomoću njih se obavljaju operacije s brojevima ili nizovima znakova.
Elementi formule mogu biti:

• brojevne vrijednosti
• adrese ćelija
• operatori
• funkcije, imena ćelija
• tekst.

Formule povezuju podatke pomoću operatora. Operatori kazuju Excelu koju vrstu
operacije treba provesti nad podatcima.

Unos formule u ćeliju potpuno je isti kao i unos bilo kojeg drugog podatka. Da bi se formula razlikovala od podatka, na početku svake formule piše se znak jednakosti.

Ako promijenimo vrijednosti u ćelijama koje su sadržane u nekim formulama, Excel će automatski preračunati formule i izmijeniti vrijednosti u ćelijama koje sadržavaju te formule.

Prije upisivanja formule upisujemo znak jednakosti, a nakon toga možemo tipkovnicom unijeti vrijednosti, adrese ćelija i matematičke operatore.

Napomena: Osim tipkovnicom, adresa ćelije može se upisati i klikom mišem na ćeliju.

2.1. Vrste operatora i redoslijed izvođenja operacija

Excel razlikuje tri skupine operatora:

• aritmetički operatori,
• operatori za tekst
• operatori uspoređivanja

Način zapisivanja operatora, operaciju koju predstavlja i primjer pogledajte u sljedećoj tablici.

Ako formula sadržava nekoliko operatora istog prioriteta, redoslijed je izvršavanja operacija slijeva nadesno. Upotrebom zagrada možete utjecati na redoslijed izvršavanja operacija. 

Ako su operatori različitog prioriteta, redoslijed izvršavanja operacija ovisi o prioritetu operatora.

Redoslijed prioriteta operatora je:

1. negativni broj, postotak
2. potenciranje
3. množenje i dijeljenje
4. zbrajanje i oduzimanje
5. spajanje niza znakova
6. uspoređivanje.

2.2. Relativne adrese

Formula se može brisati, uređivati, premještati i kopirati. Kopiranje formule iznimno je korisno i važno!
Formulu upisanu u neku ćeliju kopiramo ako u susjedne ćelije treba upisati „slične” matematičke izraze, izraze kojima su operatori isti, ali se po jasnoj logici mijenjaju adrese ćelije. Kopiranje adresa isprobali smo u prethodnoj vježbi, ali ono nije dalo željene rezultate.

Pokušajmo najprije s jednostavnijim primjerom:

U MS Excelu napravite tablicu:

Upišite tražene formule u ćelije D6, D7, E6 i E7

Na kartici Formule uključite prikaz formula (prikažite formule) da dobijete prikaz kao na slici:

Uočavate li sličnost među formulama u redovima 6 i 7?

Pokušajte sada formule iz ćelija D7 i E7 kopirati (povlačenjem) u preostale redove tablice. Što uočavate?

Isključite prikaz formula i provjerite izračunate vrijednosti.

Što smo naučili iz prethodnog primjera?

Formula u Excelu se može kopirati. Kopiranjem formule automatski se mijenjaju i adrese formule - izračunavaju se ovisno o pomaku:

• pri kopiranju formule prema dolje (ili gore) mijenja se broj retka, dok naziv stupca ostaje jednak
• pri kopiranju formule nadesno ili ulijevo mijenja se ime stupca, a kopiranjem će ostati isti broj retka

Ovakav način zapisivanja adresa - u obliku koji se mijenja kopiranjem formule zovemo relativna adresa. 

Zapamtimo!

Osnovno je obilježje relativnih adresa da se one mijenjaju postupkom kopiranja relativno prema novoj lokaciji

2.3. Apsolutne adrese

Već smo u vježbi s izračunom valuta primijetili kako nam kopiranje formula ne daje dobre rezultate.

Podsjetimo se, da bi izračunali koliko kuna ćemo dobiti promijenimo li 100 jedinica neke valute potrebno je pomnožiti tečaj brojem 100 i dobiveni rezultat podijeliti jedinicom za devize. Upišemo li u ćeliju C16 formulu: =100*F4/E4 tu ćemo formulu uspješno moći kopirati prema dolje (ali ne i u desno). Međutim, želimo li da Excel "pročita" vrijednost deviza iz zaglavlja tablice naša formula izgleda kao  na slici:

Sada ju ne možemo tako jednostavno kopirati. Zbog čega? Proučimo adrese kopiranih formula:

Što se događa?

U retku 17 srednji se tečaj ne množi sa 100 jedinica, već s vrijednošću zapisanom u ćeliji C15. Jer, kopiranjem formule, njezina se adresa preračunava relativno u odnosu na pomak. Zbog toga mi ne dobijemo točan rezultat.

Potrebno je pronaći način na koji bi kopirana formula mijenjala adrese svih ćelija osim adrrese ćelije C15.

Excel ima rješenje i za to, a naziva se apsolutno adresiranje ćelija.

Kada označimo adresu kao apsolutnu, njezina se adrese prilikom kopiranja neće mijenjati.

Oznaka za apsolutnu adresu je znak $ ispred naziva stupca i broja retka. U našem slučaju $C$15. 

Pokušajmo na ovaj način zapisati formulu u ćeliji C16 i zatim ju kopirati u preostale ćelije stupca C.

Jesu li sada rezultati u redu?

2.4. Mješovite adrese

Zadatak:

Kopirajte sada formule iz stupca C u stupce D i E. Što se događa? Zbog čega?

Pogledajmo sliku:

Što u formuli koja se nalazi u ćeliji C16 nije u redu?

1. Iznos valute je 100 (zbog apsolutne adrese $C$15)
2. Srednji tečaj formula traži u ćeliji G4, a ne F4, jer je adresa relativna i kopiranjem formule u desno i adrese u formuli su se "pomakle" u desno.
3. Iz istog razloga dio u formuli u kojem dijelimo s jedinicom za devize pomaknut je u stupac F.

Rezultat svega navedenog su netočne vrijednosti u stupcu D, te poruka o nedozvoljenom dijeljenju s nulom u stupcu E.

Što sada?

U ovakvim slučajevima odgovaralo bi nam kada bi dio formule mogao biti relativan, a dio apsolutan .

Pogledajmo formulu:

=iznos valute*srednji tečaj/jedinica za devize.

Pogledajmo argument po argument:

iznos valute - zbog kopiranja prema dolje, adresa retka treba biti "fiksna" (apsolutna), dok kopiranje u desno mora omogućiti promjenu stupca - adresa stupca treba biti relativna.

srednji tečaj - prilikom kopiranja formule prema dolje, potrebno je omogućiti promjenu retka (jer je u svakom retku nova valuta), ali vrijednost stupca se ne smije mijenjati jer je srednji tečaj upisan u stupcu F.

jedinica za valutu - jednako kao i srednju tečaj , treba omogućiti promjenu adrese prilikom kopiranja prema dolje, ali "zabraniti" kod kopiranja u desno.

U ovakvim slučajevima koristimo mješovite adrese: adrese čiji je jedan dio relativan (onaj koji se mora mijenjati), dok je drugi apsolutan.

Tako ćemo iznos valute zapisati kao C$15, srednji tečaj: $F4, a jedinicu za valutu kao $E4.

Formula koju je potrebno zapisati u ćeliju C16 tako glasi:

=C$15*$F4/$E4

Upišimo sada formulu i provjerimo!

3. Funkcije

Funkcije su posebni alati ugrađeni u Excel koji složene izračune obavljaju jednostavno i brzo. Rezultati dobiveni uporabom nekih funkcija mogu se dobiti i kao rezultat matematičke formule, ali uporaba funkcija puno je jednostavnija.
Program Microsoft Excel sadržava mnoštvo funkcija (više od 300) za različita područja primjene. Radi lakšeg snalaženja u odabiru, funkcije su podijeljene u kategorije, ovisno o području primjene:

• financijske funkcije
• logičke funkcije
• tekstne funkcije
• funkcije datuma i vremena
• funkcije adresa i traženja
• matematičke i trigonometrijske funkcije
• statističke
• funkcije baze podataka
• informacijske funkcije
• posljednje upotrebljavane
• sve funkcije.

Napomena: Za svaku od funkcija i skupina funkcija moguće je dobiti objašnjenje i pomoć pritiskom na tipku F1 kada se pokazivač miša postavi iznad naziva funkcije ili skupine funkcija.

3.1. Video lekcija - Prikupljanje, obrada i prikaz podataka MS Excel - 3. dio

Pogledajte video- lekciju

Paralelno s profesoricom radite zadatak iz videolekcije.

3.2. Unos funkcije

Funkcije se mogu upisati:

• primjenom alata Umetni funkciju s trake formula
• naredbenim gumbom Umetni funkciju  s kartice Formule
• odabirom funkcije iz biblioteka funkcija s kartice Formule
• izravnim upisom imena i argumenata pridržavajući se strogih pravila sintakse.

Oblik funkcije može biti:

=FUNKCIJA() ili
=FUNKCIJA(argument1;argument2;…argumentN).

Kao i formule, funkcije počinju znakom jednakosti nakon kojega dolazi naziv funkcije. Iza imena u okrugle se zagrade upisuju argumenti – vrijednosti nad kojima se izvršava funkcija.

3.3. Argumenti funkcije

Argumenti funkcije mogu biti: broj, adresa, raspon adresa, tekst, logička vrijednost, konstanta, formula ili neka druga funkcija.

Primjeri funkcija:

=SUM(A4:A7;B6)
=AVERAGE(A1:A10)
=MAX(B1;C2:C8;D5)
=SQRT(225)
=INT(23,241)

Napomena: Pri upisu argumenata u funkciju znak ; upotrebljavamo kao operator razdvajanja, a znak : operator je raspona.

Pogledajmo na primjerima:

1. =SUM(A1;A3) zbraja sadržaj ćelija A1 i A3.
2. =SUM(A1:A3) zbraja sadržaj ćelija A1, A2 i A3.
3. =SUM(A1;C3) zbraja sadržaj ćelija A1 i C3.
4. =SUM(A1:C3) zbraja sadržaj raspona ćelija od A1 do C3 (9 ćelija).
5. =SUM(A1;C1:C3) zbraja sadržaj ćelija A1, C1, C2 i C3, jednako kao da piše =SUM(A1;C1;C2;C3).

3.4. Statističke funkcije

Najčešće korištene statističke funkcije su:

• AVERAGE (broj1; broj2;...) – daje srednju (aritmetičku sredinu) vrijednost zadanog raspona
• MIN (broj1; broj2;...) – daje najmanju vrijednost u zadanom rasponu
• MAX (broj1; broj2;...) – daje najveću vrijednost u zadanom rasponu
• COUNT (vrijednost1; vrijednost2;...) – vraća broj ćelija u koje je upisana brojčana vrijednost
• COUNTA (vrijednost1; vrijednost2;...) – vraća broj ćelija odabranog raspona koje nisu prazne
• COUNTBLANK (raspon) – vraća broj praznih ćelija unesenog raspona
• COUNTIF (raspon; kriteriji) – prebrojava ćelije unutar raspona koje ispunjavaju zadani kriterij.

3.5. Neke matematičke funkcije

Matematičke funkcije koje ćete često upotrebljavati u svom radu su:

• SUM (broj1; broj2;...) – zbraja sve brojeve koje ste naveli kao argumente
• SUMIF (raspon; kriteriji;[raspon_zbroja]) – zbraja sve brojeve iz zadanog raspona ako zadovoljavaju zadani uvjet
• PRODUCT (broj1; broj2;...) – množi sve brojeve navedene kao argumente
• ROUND (broj; broj znamenaka) – zaokružuje broj na zadani broj znamenaka
• TRUNC (broj) – odbacuje decimalni dio broja
• INT (broj) - zaokružuje broj na najbliži manji cijeli broj
• SQRT (broj) – računa drugi korijen broja
• POWER (broj; eksponent) – funkcija za potenciranje (broj eksponent).

3.6. Vježba 2

Preuzmite datoteku matematicke.xls na svoje računalo. Spremite ju u mapu Excel koja se nalazi u vašoj mapi MyDocuments.

Na radnom listu matematičke funkcije:

• U ćelije B5 do I5 upišite tražene matematičke funkcije (ABS, INT, ROUND, TRUNC, SQRT, POWER, PRODUCT). Argument svih funkcija je broj u ćeliji A5.
• Kopirajte formule iz reda 5 u redove 6 do 12 (osim formule u stupcu I, nju kopirajte samo u red 6)
• U ćeliju B13 upište funkciju kojom ćete zbrojiti sve brojeve iz raspona B5:B12.
• Funkciju kopirajte u ćelije C13 do H13 (osim u ćeliju F13)
• U ćeliju B14 upišite funkciju kojom ćete zbrojiti sve brojeve veće od 50 u iz raspona B5:B12.
• Funkciju iz ćelije B14 kopirajte u ćelije C14 do H14 (osim F14)
• Spremite promjene. 

3.7. Vježba 3

U radnu knjigu matematicke.xls umetnite novi radni list naziva tablica potencija.

Radni list uredite kao na slici:

Na radnom listu tablica potencija:

• U ćeliju B3 upišite funkciju koja će vratiti vrijednost 2⁰.
• Funkciju uredite tako da nakon kopiranja prema dolje i lijevo točno izračunava vrijednosti brojⁿ za sve ćelije koje imaju obrub.
• Kopirajte sada funkciju u raspon ćelija: B4:B27, zatim u ćelije C3:C11 i na kraju u ćelije D3:D9.

Spremite promjene u datoteci.

3.8. Logičke funkcije

Logičke funkcije vraćaju vrijednosti istina ili laž (true/false).

U ovu skupinu pripadaju i nama poznate funkcije: AND, OR i NOT.

Zadatak:

Koristeći sustav pomoći proučite i dodajte u prezntaciju sintaksu ovih funkcija.

Najčešće korištena logička funkcija je funkcija IF:

• IF(uvjet; vrijednost_za_TRUE; vrijednost_za_FALSE)

3.9. Vježba 4

Preuzmite na svoje računalo datoteku vjezba5.xls. Spremite datoteku u mapu Excel.

Otvorite radni list logičke funkcije:

• U ćeliju E5 upišite formulu kojom ćete izračunati postotak riješenosti testa učenika pod rednim brojem 1. Ukupan broj bodova upisan je u ćeliji C2.
• Ako podatak nije prikazan u obliku postotka, uredite prikaz broja
• Ako niste prilikom unosa, uredite formulu tako da ju možete kopirati u raspon ćelija E6 do E20.
• Kopirajte formulu u ćelije E6 do E20.
• Koristeći funkciju IF i zadan kriterij ocjenjivanja izračunajte ocjenu za učenika pod rednim brojem 1. 
• Kopirajte funkciju tako da izračuna ocjenu za preostale učenike
• U ćeliju L6 upišite funkciju kojom ćete izračunati broj učenika s ocjenom 1.
• Kopirajte funkciju u ćelije L7 do L10. Vodite računa o ispravmosti adresa!
• U ćeliji L11 izračunajte ukupan broj učenika koji su pisali test.
• U ćeliju L12 upišite funkciju kojom ćete izračunati prosječnu ocjenu testa.
• Promijenite podatak u ćeliji C2 u 37.

Spremite promjene u datoteci vjezba4.

3.10. Tehničke funkcije

U skupini tehničkih (inženjering) funkcija nalaze se i funkcije za pretvorbe brojeva iz jednog sustava u drugi:

• BIN2DEC, BIN2OCT, BIN2HEX - pretvaraju zadani binarni broj u dekadski, oktalni i heksadekadski
• DEC2BIN, DEC2OCT, DEC2HEX - pretvaraju zadani dekadski broj u binaarni, oktalni i heksadekadski
• OCT2DEC, OCT2BIN, OCT2HEX - pretvaraju zadani oktalni broj u dekadski, binarni i heksadekadski
• HEX2DEC, HEX2BIN, HEX2OCT - pretvaraju zadani heksadekadski broj u dekadski, binarni i oktalni.

Osim skupine funkcija za rad u različitim brojevnim sustavima, ovdje se nalaze i funkcije za rad s kompleksnim brojevima:

• COMPLEX (realni dio, imaginarni dio, sufiks) - pretvara realne i imaginarne koeficijente u kompleksni broj oblika x + yi (sufiks se može izostaviti, podrazumijevani sufiks je "i")
• IMSUM (i_broj1;i_broj2;...) - zbraja kompleksne brojeve oblika x + yi
• IMSUB(i_broj1;i_broj2;...) - računa razliku kompleksnih brojeva
• IMPRODUCT(i_broj1;i_broj2;...) - računa umnožak kompleksnih brojeva
• IMDIV(i_broj1;i_broj2;...) - kvocijent kompleksnih brojeva

Još jedna korisna funkcija iz ove skupine je CONVERT(broj;from_unit;to_unit), funkcija koja pretvara broj iz jednog mjernog sustava u drugi.

Na primjer: =CONVERT(12;"C";"K") preračunava 12° u Kelvine.

3.11. Vježba 6

U datoteci vjezba5.xls otvorite radni list tehničke funkcije.

• Koristeći tehničke funkcije za pretvorbe brojeva popunite tablicu Pretvaranje brojeva iz sustava u sustav. Kao argumente koristite brojeve koji se nalaze u ćelijama s plavom ispunom.
• Koristeći funkciju COMPLEX, u ćeliji C13 kreirajte kompleksni broj kojem je realan dio sadržaj ćelije A13, a imaginarni dio sadržaj ćelije B13.
• Kopirajte formulu iz ćelije C13 u ćelije C14 i C15.
• Koristeći funkciju za zbrajanje kompleksnih brojeva, u ćeliji C16 prikažite zbroj kompleksnih brojeva iz raspona C13:C15.
• Koristeći funkciju za množenje kompleksnih brojeva, u ćeliji C17 prikažite umnožak kompleksnih brojeva iz raspona C13:C15.
• U ćeliji C18 oduzmite kompleksni broj iz ćelije C13 od broja iz ćelije C14.
• U ćeliji C19 izračunajte rezultat dijeljenje broja iz ćelije C14 brojem iz ćelije C13.

Spremite promjene u datoteci.

3.12. Zadatak 3

U prezentaciju dodajte informacije o osnovnim matematičkim, statističkim, logičkim i tehničkim funkcijama.

Za svaku funkciju upišite njezinu sintaksu i dodajte najmanje jedan primjer.