Otisni knjiguOtisni knjigu

Računanje u tablici - formule i funkcije

Sjedište: CARNET - Arhiva 2021 Loomen
E-kolegij: Pripreme za ispit iz informatike na državnoj maturi
Knjiga: Računanje u tablici - formule i funkcije
Otisnuo/la: Gost (anonimni korisnik)
Datum: nedjelja, 24. studenoga 2024., 12:58

Sadržaj

1. Formule i funkcije

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.


1.1. Formule

Formulama se obavljaju operacije zbrajanja, oduzimanja,množenja,dijeljenja ili uspoređivanja podataka unesenih u radni list. 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.


1.2. Vrste operatora i redoslijed izvođenja operacija

Excel razlikuje tri skupine operatora: aritmetički operatori,operatori za tekst ioperatori uspoređivanja.

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

Operator

Objašnjenje

Primjer

aritmetički operatori

+

zbrajanje

=A1+A2+B6+D12

-

oduzimanje

=B6-C3

*

množenje

=A4*B7*6

/

dijeljenje

=B4/A2

%

postotak

=10%*200

^

potencija

=3^2

operator za tekst

&

povezuje dva niza znakova u jedan (spaja tekst iz dvije ćelije)

=C2&C3

operatori usporedbe (vrijednost u ćeliji bit će TRUE ili FALSE)

=

jednakost

=5=D17

> 

veće od

=7>3

< 

manje od

=H6<C9

>=

veće od ili jednako

=Prihod>=Trošak

<=

manje od ili jednako

=B4<=65

<> 

različito

=8<>12,5

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.
Zadatak:
Bez uporabe Excela izračunajte vrijednost formule: =14+54*7/(25/5+2). Rezultat provjerite u Excelu.

1.3. Relativne, apsolutne i mješovite adrese

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. 

Osnovno je obilježje relativnih adresa da se one  postupkom kopiranja mijenjaju.

U slučajevima u kojima se adresa ćelije ne smije mijenjati kopiranjem formule koristimo apsolutne adrese ćelija.

Apsolutne adrese se prilikom kopiranja ne mijenjaju.

Oznaka za apsolutnu adresu je znak $ ispred naziva stupca i broja retka (npr. $C$15).

Mješovite adrese su adrese čiji je jedan dio relativan (onaj koji se mora mijenjati), dok je drugi apsolutan (npr. C$15).


1.4. Zadatci s provedenih ispita

  1. (Ljetni rok, 2010, zadatak 6) U programu za proračunske tablice stvoren je prikazani dio tablice.
    Koja će vrijednost pisati u ćeliji B3 ako u nju kopiramo formulu iz ćelije A3?


    A. 5
    B. 6
    C. 7
    D. 8

  2. (Jesenski rok, 2010, zadatak 6) U programu za proračunske tablice stvoren je prikazani dio tablice.
    Koja će vrijednost pisati u ćeliji D1 ako u nju kopiramo formulu iz ćelije C1?



    A. 8
    B. 10
    C. 12
    D. 14

  3. (Ljetni rok, 2011, zadatak 5) U programu za proračunske tablice stvoren je prikazani dio tablice.



    Koja će vrijednost pisati u ćeliji E2 ako u nju kopiramo formulu iz ćelije B2?

    A. 16
    B. 20
    C. 24
    D. 30

  4. (Jesenski rok, 2011, zadatak 5) U programu za proračunske tablice stvoren je prikazani dio tablice.



    Koja će vrijednost pisati u ćeliji E2, ako u nju kopiramo formulu iz ćelije B2?

    A. 16
    B. 20
    C. 24
    D. 30

  5. (Ljetni rok, 2012, zadatak 22) U programu za proračunske tablice stvoren je prikazani dio tablice.
    Koja će vrijednost pisati u ćeliji A3 ako se, nakon što su označene ćelije A1 i A2, na ćeliju A3 povuče hvataljka ispune u donjem desnom kutu ćelije A2?


  6. (Jesenski rok, 2012, zadatak 22) U programu za proračunske tablice stvoren je prikazani dio tablice.
    Koja će vrijednost pisati u ćeliji C1 ako se, nakon što su označene ćelije A1 i B1, na ćeliju C1 povuče hvataljka ispune u donjem desnom kutu ćelije B1?



1.5. Rješenja

  1. D 
  2. C
  3. C
  4. A
  5. 6
  6. 15

2. 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.

2.1. Unos funkcije

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.

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).

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).


2.2. 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.

2.3. Neke matematičke funkcije

Matematičke funkcije koje često koristimo 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).

2.4. 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.

Najčešće korištena logička funkcija je funkcija IF:
  • IF(uvjet; vrijednost_za_TRUE; vrijednost_za_FALSE)

2.5. Zadatci s provedenih ispita

  1. (2010, ljetni rok, zadatak 7) U programu za proračunske tablice stvoren je prikazani dio tablice.
    Koja će vrijednost pisati u ćeliji A5 nakon što korisnik pritisne tipku [Enter] na tipkovnici?



    A. 1
    B. 2,5
    C. 3
    D. 4

  2. (2010, jesenski rok, zadatak 7) U programu za proračunske tablice stvoren je prikazani dio tablice.
    Koja će vrijednost pisati u ćeliji A5 nakon što korisnik pritisne tipku [Enter] na tipkovnici?



    A. 2
    B. 3
    C. 4
    D. 10

  3. (2011., ljetni rok, zadatak 6) U programu za proračunske tablice stvoren je prikazani dio tablice.


    Koja će vrijednost pisati u ćeliji C3 nakon što korisnik pritisne tipku [Enter] na tipkovnici?

    A. 5
    B. 6
    C. 8
    D. 9

  4. (2011., jesenski rok, zadatak 6) U programu za proračunske tablice stvoren je prikazani dio tablice.


    Koja će vrijednost pisati u ćeliji C3 nakon što korisnik pritisne tipku [Enter] na tipkovnici?
    A. 5
    B. 6
    C. 8
    D. 9

  5. (2012., ljetni rok, zadatak 5) U programu za proračunske tablice stvoren je prikazani dio tablice.
    Kojom će se od navedenih formula utvrditi broj osoba u prikazanom području koje su postigle više od 20 bodova?


    A. =IF("bodovi>20";B2:B11)
    B. =COUNTA("bodovi>20";B2:B11)
    C. =COUNTIF(B2:B11;">20")
    D. =COUNT(B2:B11;">20")

  6. (2012., ljetni rok, zadatak 6) U programu za proračunske tablice stvoren je prikazani dio tablice.
    Koja će vrijednost pisati u ćeliji I2 nakon pritiska na tipku [Enter]?


    A. 50
    B. 40
    C. 30
    D. 20

  7. (2012., jesenski rok, zadatak 5) U programu za proračunske tablice stvoren je prikazani dio tablice.


    Što treba upisati u ćeliju B8 ako na temelju prikazanih podataka želimo izračunati koliki su ukupni troškovi za knjige, bilježnice, pribor i olovke na početku školske godine?

    A. =SUM(B2:B4;B7)
    B. =SUM(B2;B4;B7)
    C. =SUM(B2;B4:B7)
    D. =SUM(B2:B4:B7)

  8. (2012., jesenski rok, zadatak 6) U programu za proračunske tablice stvoren je prikazani dio tablice.
    Pritom je odabrana mogućnost prikazivanja formule. Koja će vrijednost pisati u ćeliji H2 ako isključimo prikaz formule?


    A. 18
    B. 16
    C. 6
    D. 3

  9. (2013., ljetni rok, zadatak 5) U programu za proračunske tablice MS Excel na adresi B2 upisana je vrijednost 2,8.
    Kako glasi formula kojom se ta vrijednost zaokružuje na najbliži cijeli broj?
    A. =CIRCLE(B2;0)
    B. =ROUND(B2;1)
    C. =CIRCLE(B2;1)
    D. =ROUND(B2;0)

  10. (2013., ljetni rok, zadatak 22) U programu za proračunske tablice MS Excel, stvoren je prikazani dio tablice.
    Koji će biti rezultat formule =COUNT(A1:E3) za tablicu prikazanu na slici?



  11. (2013., jesenski rok, zadatak 6) U programu za proračunske tablice MS Excel stvoren je prikazani dio tablice.
    Koja od navedenih formula može zamijeniti formulu u ćeliji D5?

    A. =AVERAGE(A1:C4)
    B. =SUM(A1:C4)
    C. =COUNTIF(A1:C4)
    D. =ROUND(A1:C4)

  12. (2013., jesenski rok, zadatak 22) Napišite formulu kojom ćete u programu za proračunske tablice MS Excel u ćeliju C1 upisati veći od brojeva iz ćelija A1 i B1.

  13. (2014., ljetni rok, zadatak 5) Kojom se formulom u programu za proračunske tablice MS Excel mogu prebrojiti dvojke u rasponu ćelija od C14 do D20?
    A. =COUNT(C14:D20;2)
    B. =COUNTIF(C14:D20;2)
    C. =COUNTA(C14:D20;2)
    D. =COUNT(C14:D20;IF=2)

  14. (2014., ljetni rok, zadatak 22) Koja će vrijednost pisati u ćeliji A2 u programu za proračunske tablice MS Excel nakon što pritisnemo tipku [Enter]?


  15. (2014., jesenski rok, zadatak 5) Dvadeset učenika bralo je jagode. Svaki od njih ubrao je određenu masu jagoda, a zatim su podatci uneseni u tablicu programa za proračunske tablice MS Excel. U prvi stupac tablice (stupac A) upisana su imena učenika, a u drugi stupac tablice (stupac B) za svakoga učenika iz stupca A upisana je masa jagoda koju je taj učenik ubrao. Podatci su upisani redom u retke tako da su popunili prvih 20 redaka tablice. U ćeliji B21 upisana je formula =SUM(B1:B20). Koju formulu treba upisati u ćeliji C1 tako da ona izračunava udio jagoda koje je prikupio učenik čiji se podatci nalaze u prvome retku? Formula treba biti takva da prilikom kopiranja povlačenjem (na ćelije od C2 do C20) daje točan rezultat za svakoga učenika.
    A. =B1/B21
    B. =B1/$B21
    C. =B1/B$21
    D. =$B$1/B$21

  16. (2014., jesenski rok, zadatak 6) Kojom formulom u programu za proračunske tablice MS Excel možemo prebrojati petice u rasponu ćelija od B2 do B100?
    A. =COUNTIF(B2:B100;5)
    B. =COUNT(B2:B100;5)
    C. =SUMIF(B2:B100;5)
    D. =SUM(B2:B100;5)

  17. (2014., jesenski rok, zadatak 22) Koja će vrijednost pisati u ćeliji A5 u programu za proračunske tablice MS Excel nakon što pritisnemo tipku [Enter]?


  18. (2015., ljetni rok, zadatak 5) Kojom se od navedenih formula u programu za proračunske tablice MS Excel izračunava zbroj vrijednosti iz raspona ćelija od A1 do B5 i od C1 do D5?
    A. =SUM(A1;B5;C1;D5)
    B. =SUM(A1:B5:C1:D5)
    C. =SUM(A1;B5:C1;D5)
    D. =SUM(A1:B5;C1:D5)

  19. (2015., ljetni rok, zadatak 6) U programu za proračunske tablice MS Excel upisane su školske ocjene iz pisane provjere znanja u rasponu ćelija od C2 do C30. Kojom se od navedenih formula može prebrojiti koliko je učenika pozitivno riješilo pisanu provjeru znanja?
    A. =COUNTIF(C2:C30;ꞌ2,3,4,5ꞌ)
    B. =COUNTIF(C2:C30;ꞌ>1ꞌ)
    C. =COUNTIF(C2:C30;ꞌ2 OR 3 OR 4 OR 5ꞌ)
    D. =COUNTIF(C2:C30;ꞌ0ꞌ)

  20. (2015., ljetni rok, zadatak 22) Kojom se formulom prebrojava koliko ima brojeva u rasponu ćelija od A3 do C8 u programu za proračunske tablice MS Excel?

  21. (2015., jesenski rok, zadatak 5) Kojom se od navedenih formula u programu za proračunske tablice MS Excel prebrojava koliko ima popunjenih ćelija u rasponu od A1 do B5?
    A. = COUNT(A1:B5)
    B. = COUNTA(A1:B5)
    C. = SUMIF(A1:B5)
    D. = SUM(A1:B5)

  22. (2015., jesenski rok, zadatak 6) U programu za proračunske tablice MS Excel stvoren je prikazani dio tablice. U ćeliji C2 upisana je formula kojom se izračunava opći uspjeh učenika izražen cjelobrojnom vrijednošću. Kako treba glasiti ta formula ako je želimo primijeniti za izračunavanje općega uspjeha svakoga pojedinog učenika postupkom kopiranja formule povlačenjem?
    (Napomena: Ako učenik ima jednu ili više jedinica, uspjeh mu je nedovoljan.)


    A. =IF(B2>0;ROUND(A2;0);1)
    B. =IF(B2<1;ROUND(A2;0);B2)
    C. =IF(B2=0;ROUND(A2;0);1)
    D. =IF(B2<1;1;ROUND(A2;0))

  23. (2015., jesenski rok, zadatak 22) U programu za proračunske tablice MS Excel stvoren je prikazani dio tablice.
    Kako će glasiti formula u ćeliji C3 koja se dobije kopiranjem formule povlačenjem iz ćelije B2 u ćeliju C2 pa u ćeliju C3?


  24. (2016., ljetni rok, zadatak 5) U programu za proračunske tablice MS Excel u ćelijama A1 do A15 upisane su
    zaključne ocjene za nekoga učenika iz 15 predmeta. Kojom se od navedenih formula izračunava srednja ocjena toga učenika?

    A. =ROUND(A1:A15)
    B. =ROUND(A1;A15)
    C. =AVERAGE(A1:A15)
    D. =AVERAGE(A1;A15)

  25. (2016., ljetni rok, zadatak 6) Kojom će se formulom u programu za proračunske tablice MS Excel prebrojiti pozitivne vrijednosti u rasponu ćelija od A1 do D100?
    A. =COUNT(A1:D100;>0)
    B. =COUNTIF(A1:D100;">0")
    C. =COUNTIF(A1;D100:">0")
    D. =COUNTA(A1:D100;">0")

2.6. Rješenja

  1. A
  2. B
  3. A
  4. B
  5. C
  6. A
  7. A
  8. D
  9. D
  10. 12
  11. A
  12. = MAX(A1;B1)
  13. B
  14. 2.5
  15. C
  16. A
  17. 1.5
  18. D
  19. B
  20. =COUNT(A3:C8)
  21. B
  22. C
  23. =$B4+D$4
  24. C
  25. B