Pregled formula

Ako ste novi korisnik sustava Excel za web, ubrzo ćete saznati da je to više od rešetke u koju unosite brojeve u stupce ili retke. Da, možete koristiti Excel za web da biste pronašli ukupne zbrojeve za stupac ili redak brojeva, ali možete i izračunati otplatu hipotekom, riješiti matematičke ili inženjerske probleme ili pronaći scenarij u najboljem slučaju na temelju varijabilnih brojeva koje priključite.

Excel za web to učiniti pomoću formula u ćelijama. Formula izvodi izračune ili druge akcije na podacima na radnom listu. Formula uvijek počinje znakom jednakosti (=), nakon kojeg mogu slijediti brojevi, matematički operatori (kao što su znak plus ili minus) i funkcije koje mogu doista proširiti snagu formule.

Sljedećom formulom, primjerice, množi se 2 s 3, a zatim se umnožak zbraja s 5 da bi se dobio rezultat 11.

=2*3+5

U sljedećoj se formuli pomoću funkcije PMT izračunava otplata rate (1073,64 kn) kredita od 200 000 kn uz kamatu od pet posto (kad se 5% podijeli na dvanaest mjeseci, dobiva se mjesečna kamatna stopa) tijekom 30 godina (360 mjeseci):

=PMT(0,05/12;360;200000)

Evo još nekih primjera formula koje možete unijeti na radni list.

=A1+A2+A3 Zbraja vrijednosti u ćelijama A1, A2 i A3.
=SQRT(A1) Pomoću funkcije SQRT izračunava kvadratni korijen vrijednosti u ćeliji A1.
=TODAY() Vraća trenutni datum.
=UPPER("hello") Pretvara tekst "pozdrav" u "POZDRAV" pomoću funkcije radnog lista UPPER.
=IF(A1>0) Testira ćeliju A1 da bi se utvrdilo sadrži li vrijednost veću od 0.

Dijelovi formule

Formula može sadržavati i funkcije, reference, operatore i konstante.

1. Funkcije: funkcija PI() vraća vrijednost pi: 3,142...

2. Reference: A2 vraća vrijednost u ćeliji A2.

3. Konstante: brojevi ili tekstne vrijednosti uneseni izravno u formulu, na primjer 2.

4. Operatori: operator ^ (karet) služi za izračun potencije broja, a operator * (zvjezdica) za množenje brojeva.

Korištenje konstanti u formulama

Konstanta je vrijednost koja se ne izračunava, već je uvijek ista. Na primjer, datum 10/9/2008, broj 210 i tekst "Kvartalna zarada" su konstante. izraz ili vrijednost koja je rezultat izraza nije konstanta. Ako u formuli koristite konstante umjesto referenci na ćelije (na primjer =30+70+110), rezultat se mijenja samo ako izmijenite formulu.

Korištenje računskih operatora u formulama

Operatori određuju vrstu izračuna koji želite izvesti na elementima u formuli. Izračuni se izvršavaju zadanim redoslijedom (prema osnovnim matematičkim pravilima), ali se taj redoslijed može i promijeniti pomoću zagrada.

Vrste operatora

Postoje četiri različite vrste računskih operatora: aritmetički operatori, operatori usporedbe, operatori spajanja teksta i operatori referenci.

Aritmetički operatori

Za izvođenje osnovnih matematičkih operacija zbrajanja, oduzimanja, množenja i dijeljenja, kombiniranje brojeva te dobivanje brojčanih rezultata koristite sljedeće aritmetičke operatore.

Aritmetički operator	Značenje	Primjer
+ (znak plus)	zbrajanje	3+3
- (znak minus)	Oduzimanje Negacija	Od 3 do 1 –1
* (zvjezdica)	množenje	3*3
/ (kosa crta)	dijeljenje	3/3
% (znak postotka)	postotak	20%
^ (karet)	potencija	3^2

Operatori usporedbe

Dvije vrijednosti možete usporediti pomoću sljedećih operatora. Kada se dvije vrijednosti uspoređuju pomoću tih operatora, rezultat je logička vrijednost: TRUE ili FALSE.

Operator usporedbe	Značenje	Primjer
= (znak jednako)	Jednako je	A1=B1
> (znak veće od)	veće od	A1>B1
< (znak manje od)	manje od	A1<B1
>= (znak veće od ili jednako)	veće od ili jednako	A1>=B1
<= (znak manje od ili jednako)	manje od ili jednako	A1<=B1
<> (znak nije jednako)	nije jednako	A1<>B1

Operator spajanja teksta

Pomoću znaka & pridružite (sjedinite) tekstne nizove da biste stvorili jednodijelni tekst.

Tekstni operator	Značenje	Primjer
& (ampersand)	Povezuje ili spaja dvije vrijednosti i daje jednu neprekidnu tekstnu vrijednost.	"Pazi"&"kuća" rezultira nizom "Pazikuća".

Operatori referenci

Rasponi ćelija za izračune kombiniraju se pomoću sljedećih operatora.

Operator referenci	Značenje	Primjer
: (dvotočka)	Operator raspona koji stvara jednu referencu na sve ćelije između dviju referenci, uključujući i te dvije reference.	B5:B15
, (zarez)	Operator unije koji više referenci spaja u jednu.	SUM(B5:B15;D5:D15)
(razmak)	Operator presjeka koji kao rezultat stvara jednu referencu na ćelije koje su zajedničke dvjema referencama.	B7:D7 C6:C8

Redoslijed kojim Excel za web izvršava operacije u formulama

U nekim slučajevima redoslijed izvođenja izračuna može utjecati na vrijednost koju će formula vratiti, pa je radi postizanja željenog rezultata važno znati kako se određuje i mijenja redoslijed.

Redoslijed izračuna

Formule izračunavaju vrijednosti određenim redoslijedom. Formula uvijek započinje znakom jednakosti (=). Excel za web znakove koji slijede nakon znaka jednakosti tumači kao formulu. Nakon znaka jednakosti slijede elementi koje treba izračunati (operandi), primjerice konstante ili reference na ćelije. Oni su razdvojeni operatorima izračuna. Excel za web izračunava formulu slijeva nadesno prema određenom redoslijedu za svaki operator u formuli.

Prioritet operatora

Ako kombinirate nekoliko operatora u jednoj formuli, Excel za web izvodi operacije redoslijedom prikazanim u sljedećoj tablici. Ako formula sadrži operatore s istim prioritetom – na primjer, ako formula sadrži operator množenja i dijeljenje – Excel za web izračunava operatore slijeva nadesno.

Operator	Opis
: (dvotočka) (jednostruki razmak) ; (točka sa zarezom)	operatori reference
–	negativni broj (kao u -1)
%	postotak
^	potencija
* i /	množenje i dijeljenje
+ i -	Zbrajanje i oduzimanje
&	povezivanje dva niza teksta (spajanje)
= < > <= >= <>	Usporedba

Korištenje zagrada

Da biste promijenili redoslijed procjene, u zagrade stavite onaj dio formule koji se treba izračunati prvi. Sljedeća formula, primjerice, stvara 11 jer Excel za web množenje izvodi prije zbrajanja. Formula množi 2 i 3, a zatim rezultatu pribraja 5.

=5+2*3

Nasuprot tome, ako koristite zagrade da biste promijenili sintaksu, Excel za web zbraje 5 i 2, a zatim rezultat množi s 3 da bi se proizašla 21.

=(5+2)*3

U sljedećem primjeru zagrade koje okružuju prvi dio formule prisiljavaju Excel za web da najprije izračuna B4+25, a zatim rezultat podijelite zbrojom vrijednosti u ćelijama D5, E5 i F5.

=(B4+25)/SUM(D5:F5)

Upotreba funkcija i ugniježđenih funkcija u formulama

Funkcije su unaprijed definirane formule koje izvode izračune pomoću određenih vrijednosti, koje se zovu argumenti, u točno zadanom redoslijedu ili strukturi. Funkcije se mogu koristiti za izvođenje jednostavnih ili kompleksnih izračuna.

Sintaksa funkcije

Slijedeći primjer funkcije ROUND koja zaokružuje broj u ćeliji A10 prikazuje sintaksu funkcije.

1. Struktura. Struktura funkcije počinje znakom jednakosti (=), nakon čega slijedi naziv funkcije, otvorena zagrada, argumenti funkcije razdvojeni zarezima i zatvorena zagrada.

2. Naziv funkcije. Da bi vam se prikazao popis dostupnih funkcija, kliknite ćeliju, a zatim pritisnite SHIFT+F3.

3. Argumenti. Argumenti mogu biti brojevi, tekst, logičke vrijednosti kao što su TRUE ili FALSE, polja, vrijednosti pogrešaka kao što su #N/A ili reference na ćelije. Argument koji odredite mora dati valjanu vrijednost za taj argument. Argumenti mogu biti i konstante, formule ili druge funkcije.

4. Opis elementa za argument. Opis elementa sa sintaksom i argumentima pojavit će se za vrijeme upisivanja funkcija. Ako upišete, primjerice,=ROUND(, pojavit će se opis elementa. Opisi elementa prikazuju se samo za ugrađene funkcije.

Unos funkcija

Kada stvorite formulu koja sadrži funkciju, pomoću dijaloškog okvira Umetanje funkcije možete unositi funkcije radnog lista. Prilikom unosa funkcije u formulu u dijaloškom okviru Umetanje funkcije prikazuje se naziv funkcije, svi njezini argumenti, opis funkcije i svakog argumenta, trenutni rezultat funkcije i trenutni rezultat cijele formule.

Da biste pojednostavnili stvaranje i uređivanje formula i minimizirali pogreške sintakse i upisa, koristite mogućnost samodovršavanja formule. Kada upišete = (znak jednakosti) i početna slova ili okidač prikaza, Excel za web ispod ćelije prikazuje dinamični padajući popis valjanih funkcija, argumenata i naziva koji odgovaraju slovima ili okidačima. Tada stavku s padajućeg popisa možete umetnuti u formulu.

Ugnježđivanje funkcija

U nekim je slučajevima funkciju potrebno koristiti kao jedan od argumenata neke druge funkcije. U sljedećoj se formuli, primjerice, koristi ugniježđena funkcija AVERAGE, a rezultat se uspoređuje s vrijednošću 50.

Ugniježđene funkcije

1. Funkcije AVERAGE i SUM ugniježđene su unutar funkcije IF.

Valjani povrati Kada se ugniježđena funkcija koristi kao argument, ugniježđena funkcija mora vratiti istu vrstu vrijednosti koju argument koristi. Ako, primjerice, argument vraća vrijednost TRUE ili FALSE, ugniježđena funkcija mora vratiti vrijednost TRUE ili FALSE. Ako se funkcija ne prikaže, Excel za web prikazuje #VALUE! vrijednost nenumeričke prirode, PHI vraća vrijednost pogreške #VALUE!.

Ograničenja razine gniježđenja Formula može sadržavati najviše sedam razina ugniježđenih funkcija. Kada se neka funkcija (nazovimo je funkcija B) koristi kao argument druge funkcije (nazovimo je funkcija A), funkcija B ponaša se kao funkcija druge razine. Na primjer, funkcija AVERAGE i funkcija SUM obje su funkcije druge razine ako se koriste kao argumenti funkcije IF. Funkcija koja je ugniježđena unutar ugniježđene funkcije AVERAGE tada je funkcija treće razine i tako dalje.

Korištenje referenci u formulama

Referenca identificira ćeliju ili raspon ćelija na radnom listu i govori Excel za web gdje tražiti vrijednosti ili podatke koje želite koristiti u formuli. Pomoću referenci možete u jednoj formuli koristiti podatke sadržane u raznim dijelovima radnog lista ili pak u nekoliko formula koristiti vrijednost iz jedne ćelije. Možete se pozivati i na ćelije na drugim listovima iste radne knjige i u drugim radnim knjigama. Reference na ćelije u drugim radnim knjigama zovu se veze ili vanjske reference.

Stil reference A1

Zadani stil reference Prema zadanim postavkama Excel za web stil reference A1, koji se odnosi na stupce sa slovima (od A do XFD, za ukupno 16 384 stupaca) i odnosi se na retke s brojevima (od 1 do 1 048 576). Ta slova i brojevi zovu se zaglavlja redaka i stupaca. Da biste unijeli referencu na neku ćeliju, unesite slovo stupca i nakon njega broj retka. Tako B2 upućuje na ćeliju na presjeku stupca B i retka 2.

Da biste se pozvali na	Koristite značajku
ćeliju u stupcu A i retku 10	A10
raspon ćelija u stupcu A i recima 10 do 20	A10:A20
raspon ćelija u retku 15 i stupcima B do E	B15:E15
sve ćelije u retku 5	5:5
sve ćelije u recima od 5 do 10	5:10
sve ćelije u stupcu H	H:H
sve ćelije u stupcima od H do J	H:J
raspon ćelija u stupcima od A do E i recima od 10 do 20	A10:E20

Pozivanje na drugi radni list U sljedećem primjeru funkcija radnog lista AVERAGE izračunava prosječnu vrijednost raspona B1:B10 na radnom listu nazvanom Marketing u istoj radnoj knjizi.

1. Upućuje na radni list pod nazivom Marketing

2. Upućuje na raspon između ćelija B1 i B10, uključujući te ćelije

3. Odjeljuje referencu radnog lista od reference raspona ćelija

Razlike između apsolutnih, relativnih i mješovitih referenci

Relativne reference Relativna referenca na ćeliju u formuli, kao što je A1, temelji se na relativnom položaju ćelije koja sadrži formulu i ćelije na koju se referenca poziva. Ako se položaj ćelije koja sadrži formulu promijeni, mijenja se i referenca. Ako kopirate ili ispunite formulu po recima ili niz stupce, referenca se prilagođava automatski. Nove formule po zadanom koriste relativne reference. Na primjer, ako kopirate ili ispunite relativnu referencu iz ćelije B2 u ćeliju B3, ona se automatski prilagođava sa =A1 na =A2.

Kopirana formula s relativnom referencom

Apsolutne reference Apsolutna referenca na ćeliju u nekoj formuli, npr $A$1, uvijek se poziva na ćeliju na točno određenom mjestu. Ako se promijeni položaj ćelije koja sadrži formulu, apsolutna referenca ostaje ista. Ako kopirate formulu po recima ili niz stupce, apsolutna se referenca ne prilagođava. Po zadanom, nove formule koriste relativne reference, pa ćete ih po potrebi morati pretvoriti u apsolutne reference. Ako, primjerice, kopirate apsolutnu referencu iz ćelije B2 u ćeliju B3, ona u obje ćelije ostaje ista: =$A$1.

Kopirana formula s apsolutnom referencom

Mješovite reference Mješovita referenca ima ili apsolutni stupac i relativni redak ili apsolutni redak i relativni stupac. Apsolutna referenca stupca ima oblik $A1, $B1 i tako dalje. Apsolutna referenca retka ima oblik A$1, B$1 i tako dalje. Ako se položaj ćelije koja sadrži formulu promijeni, mijenja se i relativna referenca, a apsolutna se referenca ne mijenja. Ako kopirate formulu ili je ispunite po recima ili niz stupce, relativna referenca se automatski prilagođava, a apsolutna se referenca ne prilagođava. Ako, primjerice, kopirate ili ispunite mješovitu referencu iz ćelije A2 u ćeliju B3, ona se mijenja iz =A$1 u =B$.

Kopirana formula s mješovitom referencom

Trodimenzionalni stil reference

Praktične reference na više radnih listova Ako želite analizirati podatke u istoj ćeliji ili rasponu ćelija na više radnih listova u jednoj radnoj knjizi, koristite 3D referencu. 3D referenca obuhvaća referencu ćelije ili raspona kojoj prethodi raspon naziva radnih listova. Excel za web koristi sve radne listove pohranjene između početnih i završnih naziva reference. Na primjer, =SUM(List2:List13!B5) zbraja sve vrijednosti sadržane u ćeliji B5 na svim radnim listovima između listova 2 i 13, uključujući ta dva lista.

Pomoću 3D referenci možete se referencirati na ćelije na drugim listovima, definirati nazive i stvarati formule koristeći sljedeće funkcije: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA i VARPA.
3D reference ne mogu se koristiti u formulama polja.
3D reference ne mogu se koristiti s operator presjeka (jednostruki razmak) ili u formulama koje koriste implicitno sjecište.

Što se događa tijekom premještanja, kopiranja, umetanja ili brisanja radnih listova Sljedeći primjeri objašnjavaju što se događa kad premještate, kopirate, umećete ili brišete radne listove obuhvaćene 3D referencama. U primjerima se koristi formula =SUM(List2:List6!A2:A5) za zbrajanje ćelija od A2 do A5 na radnim listovima od 2 do 6.

Umetanje ili kopiranje Ako umetnete ili kopirate list između listova List2 i List6 (krajnjih točaka u ovom primjeru), Excel za web obuhvaća sve vrijednosti u ćelijama od A2 do A5 iz dodanih listova u izračunima.
Izbriši Ako izbrišete listove između listova List2 i List6, Excel za web njihove vrijednosti iz izračuna.
Premjesti Ako listove premjestite između listova List2 i List6 na mjesto izvan raspona referenciranog lista, Excel za web njihove vrijednosti iz izračuna.
Premještanje krajnje točke Ako premjestite List2 ili List6 na drugo mjesto u istoj radnoj knjizi, Excel za web prilagođava izračun tako da odgovara novom rasponu listova između njih.
Brisanje krajnje točke Ako izbrišete List2 ili List6, Excel za web prilagođava izračun tako da odgovara rasponu listova između njih.

Stil reference R1C1

Možete koristiti i stil reference u kojem su i reci i stupci na radnom listu numerirani. Stil reference R1C1 koristan je pri izračunavanju položaja redaka i stupaca u makronaredbama. U stilu R1C1 Excel za web mjesto ćelije s "R" nakon čega slijedi broj retka i "C" nakon čega slijedi broj stupca.

Referenca	Značenje
R[-2]C	relativna referenca na ćeliju dva retka iznad u istom stupcu
R[2]C[2]	relativna referenca na ćeliju dva retka ispod i dva stupca desno
R2C2	apsolutna referenca na ćeliju u drugom retku i drugom stupcu
R[-1]	relativna referenca na cijeli redak iznad aktivne ćelije
R	apsolutna referenca na trenutni redak

Kada snimite makronaredbu, Excel za web neke naredbe pomoću stila reference R1C1. Ako, primjerice, snimite naredbu, npr. kliknete gumb Automatski zbroj da biste umetnuli formulu koja dodaje raspon ćelija, Excel za web će snimiti formulu pomoću stila R1C1, a ne stila A1, referenci.

Korištenje naziva u formulama

Možete stvoriti definirane nazive koji predstavljaju ćelije, raspone ćelija, formule, konstante ili Excel za web tablice. Naziv je smislena kratica koja olakšava razumijevanje svrhe naizgled teško razumljivih referenci ćelija, konstanti, formula ili tablica. Informacije koje slijede prikazuju uobičajene primjere naziva i načina na koji njihovo korištenje poboljšava jasnoću i pojednostavnjuje razumijevanje formula.

Vrsta primjera	Primjer u kojem se umjesto naziva koriste rasponi	Primjer u kojem se koriste nazivi
Referenca	=SUM(A16:A20)	=SUM(Prodaja)
Konstanta	=PRODUCT(A12;9,5%)	=PRODUCT(Cijena;PoreznaStopaKC)
Formula	=TEXT(VLOOKUP(MAX(A16;A20);A16:B20;2;FALSE);"m/dd/yyyy")	=TEXT(VLOOKUP(MAX(Prodaja);PodaciOProdaji;2;FALSE);"m/dd/yyyy")
Tablica	A22:B25	=PRODUCT(Cijena;Tablica1[@Porezna stopa])

Vrste naziva

Postoji nekoliko vrsta naziva koje možete stvoriti i koristiti.

Definirani naziv Naziv koji predstavlja ćeliju, raspon ćelija, formulu ili vrijednost konstante. Možete stvoriti vlastiti definirani naziv. Osim Excel za web ponekad stvara definirani naziv za vas, npr. kada postavite područje ispisa.

Naziv tablice Naziv tablice Excel za web, što je zbirka podataka o određenom predmetu pohranjenom u zapisima (recima) i poljima (stupcima). Excel za web stvara zadani naziv Excel za web tablice "Tablica1", "Tablica2" i tako dalje, svaki put kada umetnete tablicu programa Excel za web, ali te nazive možete promijeniti da bi bili smisleniji.

Stvaranje i unos naziva

Naziv stvarate pomoću mogućnosti Stvori naziv iz odabira. Nazive možete praktično stvarati iz oznaka postojećeg retka i stupca odabirom ćelija na radnom listu.

Napomena: Nazivi po zadanom koriste apsolutne reference ćelija.

Naziv možete unijeti na sljedeće načine:

Tipkanje Upisivanje naziva, primjerice, kao argument formule.
Korištenjem samodovršetka formule Koristite padajući popis samodovršetka formule na kojem se automatski navode valjani nazivi.

Korištenje formula polja i konstanti polja

Excel za web ne podržava stvaranje formula polja. Možete pogledati rezultate formula polja stvorenih u aplikaciji Excel za stolna računala, ali ne možete ih uređivati ni ponovno izračunavati. Ako imate aplikaciju Excel za stolna računala, kliknite Otvori u programu Excel da biste radili s poljima.

U primjeru sljedeće formule polja izračunava se ukupni iznos niza cijena dionica i udjela, bez korištenja retka ćelija za izračun i prikaz pojedinačnih iznosa za svaku dionicu.

Unesete li formulu ={SUM(B2:D2*B3:D3)} kao formula polja, ona će pomnožiti Dionice i Cijenu za svaku dionice, a zatim zbrojiti rezultate tih izračuna.

Izračun višestrukih rezultata Neke funkcije radnog lista vraćaju polja vrijednosti ili kao argument zahtijevaju niz vrijednosti. Da biste izračunali više rezultata pomoću formule polja, morate unijeti niz u raspon ćelija koji ima isti broj redaka i stupaca kao i argumenti niza.

Ako, primjerice, postoji niz od tri iznosa prodaje (u stupcu B) za niz od tri mjeseca (u stupcu A), funkcija TREND određuje pravocrtne vrijednosti za iznose prodaje. Radi prikaza svih rezultata formule ona se unosi u tri ćelije u stupcu C (C1:C3).

Unesete li formulu =TREND(B1:B3,A1:A3) kao formulu polja, ona će dati tri zasebna rezultata (22.196, 17.079 i 11.962) koja se temelje na tri iznosa prodaje i tri mjeseca.

Korištenje konstanti polja

U običnu formulu možete unijeti referencu na ćeliju koja sadrži neku vrijednost ili na samu vrijednost, što se naziva i konstanta. Na sličan način, u formulu polja možete unijeti referencu na polje ili unijeti samo polje vrijednosti sadržanih u ćelijama, što se naziva i konstanta polja. Formule polja prihvaćaju konstante na isti način kao što to čine i ostale formule, ali konstante polja moraju se unijeti u određenom obliku

Konstante polja mogu sadržavati brojeve, tekst, logičke vrijednosti kao što su TRUE ili FALSE ili vrijednosti pogrešaka kao što su #N/A. Različite vrste vrijednosti mogu biti u istoj konstanti polja – na primjer, {1,3,4; TRUE, FALSE, TRUE}. Brojevi u konstantama polja mogu biti u cjelobrojnom, decimalnom ili znanstvenom obliku. Tekst se mora nalaziti u dvostrukim navodnicima , npr. "Utorak".

Konstante polja ne smiju sadržavati reference ćelija, stupaca ili redaka nejednakih duljina, formule ili posebne znakove $ (znak dolara), zagrade ili % (znak postotka).

Prilikom oblikovanja konstanti polja svakako učinite sljedeće:

Stavite ih u vitičaste zagrade ({ }).
Vrijednosti u različitim stupcima odijelite zarezima (,). Da biste, primjerice, prikazali vrijednosti 10, 20, 30 i 40, unesite {10,20,30,40}. Ta se konstanta polja naziva polje 1-sa-4 i ekvivalentna je referenci 1-redak-sa-4-stupca.
Vrijednosti u različitim recima odijelite točkom sa zarezom (;). Da biste, primjerice, prikazali vrijednosti 10, 20, 30 i 40 u jednom retku, a 50, 60, 70 i 80 u retku neposredno ispod njega, unesite konstantu polja 2-sa-4: {10,20,30,40;50,60,70,80}.