Pronalaženje pogrešaka u formulama

U sljedećoj tablici prikazane su neke od najčešćih pogrešaka koje korisnik može načiniti pri unosu formule te se objašnjava kako ih ispraviti:

Obavezno učinite sljedeće	Dodatne informacije
Svaku funkciju započnite znakom jednakosti (=)	Ako izostavite znak jednakosti, ono što upišete može se prikazati kao tekst ili kao datum. Ako, primjerice, upišete SUM(A1:A10),Excel će tekstni niz SUM(A1:A10) i neće izvršiti izračun. Ako upišete 11/2, Excel prikazuje datum od 2 . studenog (pod pretpostavkom da je oblik ćelije Općenito) umjesto dijeljenja 11 s 2.
Uparite sve otvorene i zatvorene zagrade	Provjerite ima li svaka zagrada svoj par. Kada koristite funkciju u formuli, važno je da svaka zagrada bude na odgovarajućem položaju da bi funkcija ispravno funkcionirala. Na primjer, formula =IF(B5<0),"Nije valjano",B5*1,05) neće funkcionirati jer postoje dvije zatvorene i samo jedna otvorena zagrada (trebala bi postojati samo jedna otvorena i jedna zatvorena zagrada). Formula bi trebala izgledati ovako: =IF(B5<0,"Nije valjano",B5*1,05).
Pri navođenju raspona koristite dvotočku	Prilikom referiranja na raspon ćelija koristite dvotočku (:) za odvajanje reference na prvu ćeliju raspona od reference na posljednju ćeliju raspona. Npr. =SUM(A1:A5), a ne =SUM(A1 A5), što bi vratilo pogrešku #NULA!.
Unesite sve potrebne argumente	Neke funkcije imaju obavezne argumente. Provjerite i niste li unijeli previše argumenata.
Unesite odgovarajuću vrstu argumenata	Neke funkcije, kao što je SUM, zahtijevaju brojčane argumente. Druge funkcije, kao što je REPLACE, zahtijevaju tekstnu vrijednost za barem jedan od svojih argumenata. Ako kao argument koristite pogrešnu vrstu podataka, Excel vratiti neočekivane rezultate ili prikazati pogrešku.
Nemojte ugnijezditi više od 64 funkcija	Unutar funkcije možete unijeti ili ugnijezditi najviše 64 razine funkcija.
Obuhvatite druge nazive listova pomoću jednostrukog navodnika	Ako se formula odnosi na vrijednosti ili ćelije na nekim drugim radnim listovima ili radnim knjigama, a naziv te druge radne knjige ili radnog lista sadrži razmake ili znak koji nije slovo abecede, taj naziv morate unijeti pomoću jednostrukih navodnika ( ' ), ='Tromjesečni podaci'!D3 ili =‘123’!A1.
Kada se pozivate na naziv radnog lista, stavite uskličnik (!) nakon njegova naziva	Da biste, primjerice, vratili vrijednost iz ćelije D3 u radnom listu koji se naziva Podaci za tromjesečje u istoj radnoj knjizi, koristite ovu formulu: ='Podaci za tromjesečje'!D3.
Uvrstite put do vanjskih radnih knjiga	Provjerite sadrži li svaka vanjska referenca naziv radne knjige i put do nje. Referenca na radnu knjigu sadrži ime radne knjige i mora biti u uglatim zagradama ([NazivRadnogLista.xlsx]). Referenca mora sadržavati i naziv radnog lista u radnoj knjizi. Ako radna knjiga na koju se želite pozvati nije otvorena u programu Excel, referencu na nju još uvijek možete uvrstiti u formulu. Unesite potpuni put do datoteke kao u sljedećem primjeru:=ROWS('C:\Moji dokumenti\[T2 Operacije.xlsx]Prodaja'!A1:A8). Ova formula vraća broj redova u rasponu koji sadrži ćelije od A1 do A8 u drugoj radnoj knjizi (8). Napomena: Ako potpuni put sadrži razmake kao u prethodnom primjeru, put morate upisati u jednostrukim navodnicima (na početku puta i nakon naziva radnog lista prije uskličnika).
Brojeve unosite bez oblikovanja	Nemojte oblikovati brojeve kada ih unosite u formule. Na primjer, ako je vrijednost koju želite unijeti 1.000,00 kn, u formulu unesite 1000. Ako unesete zarez kao dio broja, Excel sa zarezom postupa kao s razdjelnikom. Ako želite da se brojke prikazuju tako da prikazuju razdjelnike tisućica ili milijuna ili simbole valuta, oblikujte ćelije nakon unosa brojki. Ako, primjerice, u ćeliju A3 želite dodati 3100, a zatim unesete formulu =SUM(3,100;A3),Excel zbrajati brojeve 3 i 100, a zatim taj zbroj dodati u vrijednost iz A3, a ne dodati 3100 u A3, što bi bilo =SUM(3100;A3). Ili, ako unesete formulu =ABS(-2,134), Excel prikazuje pogrešku jer funkcija ABS prihvaća samo jedan argument: =ABS(-2134).

Možete implementirati određena pravila da biste potražili pogreške u formulama. Ta pravila ne jamče da radni list ne sadrži pogreške, no mogu pojednostavniti pronalaženje najčešćih pogreški. Bilo koje od tih pravila možete zasebno uključiti i isključiti.

Pogreške se mogu označavati i provjeravati na dva načina: traženje po jedne pogreške (kao prilikom provjere pravopisa) ili otkrivanje pogrešaka istodobno s njihovom pojavom na radnom listu dok unosite podatke.

Pogrešku možete riješiti pomoću mogućnosti koje Excel prikazuje ili zanemariti pogrešku tako da odaberete Zanemari pogrešku. Ako zanemarite pogrešku u određenoj ćeliji, pogreška iz te ćelije neće se pojavljivati u budućim provjerama. No sve prethodno zanemarene pogreške možete vratiti tako da se ponovno prikažu.

1. Za Excel u sustavu Windows idite na Mogućnosti > datoteka > formule ili
   za Excel na Macu odaberite izbornik Excel > Preference > provjera pogrešaka.

2. U odjeljku Provjera pogrešaka potvrdite Omogući provjeru pogrešaka u pozadini. Sve pronađene pogreške označene su trokutom u gornjem lijevom kutu ćelije.

   

3. Da biste promijenili boju trokuta koji označava mjesto na kojem se nalazi pogreška, u okviru Obilježi pogreške ovom bojom odaberite željenu boju.

4. U odjeljku Pravila za provjeru u programu Excel po potrebi potvrdite ili poništite potvrdne okvire uz sljedeća pravila:

   • Ćelije koje sadrže formule koje rezultiraju pogreškom: formula ne koristi očekivanu sintaksu, argumente ni vrste podataka. Vrijednosti pogreške obuhvaćaju #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, i #VALUE!. Svaka od tih vrijednosti pogreške ima različite uzroke i razriješena je na različite načine.

     Napomena: Ako vrijednost pogreške unesete izravno u ćeliju, ona se pohranjuje kao ta vrijednost pogreške, ali nije označena kao pogreška. No ako u formuli u nekoj drugoj ćeliji postoji referenca na tu ćeliju, formula daje vrijednost pogreške iz te ćelije.

   • Nedosljedno izračunate formule stupca u tablicama:zračunati stupac može sadržavati pojedinačne formule koje se razlikuju od formule glavnog stupca, što stvara iznimku. Iznimke izračunatog stupca stvorit će se kada učinite nešto od sljedećeg:

     • u ćeliju izračunatog stupca upišete podatke koji nisu formula

     • U ćeliju izračunatog stupca upišite formulu, a zatim pritisnite Ctrl + Z ili odaberite Poništi na alatnoj traci za brzi pristup.

     • upišete novu formulu u izračunati stupac koji već sadrži neke iznimke

     • u izračunati stupac kopirate podatke koji nisu u skladu s formulom izračunatog stupca Ako kopirani podaci sadržavaju formulu, ta će formula prebrisati podatke u izračunatom stupcu.

     • premjestite ili izbrišete ćeliju u drugom području radnog lista na koje upućuje jedan ili više redaka u izračunatom stupcu

   • Ćelije koje sadrže godine prikazane kao dvije znamenke: ćelija sadrži tekstni datum koji se može pogrešno protumačiti kao pogrešno stoljeće kada se koristi u formulama. Na primjer, datum u formuli =YEAR("1/1/31") može biti 1931 ili 2031. Ovo pravilo koristite za provjeru dvosmislenih datuma teksta.

   • Brojevi koji su oblikovani kao tekst ili kojima prethodi apostrof: ćelija sadrži brojeve pohranjene u obliku teksta. To se obično događa kada se podaci uvezu iz drugih izvora. Brojevi pohranjeni kao tekst mogu uzrokovati neočekivane rezultate sortiranja, pa ih je najbolje pretvoriti u brojeve. ‘=SUM(A1:A10) vidi se kao tekst.

   • Formule koje nisu usklađene s drugim formulama unutar područja: formula ne odgovara uzorku ostalih formula pokraj nje. U mnogim slučajevima formule susjedne drugim formulama razlikuju se samo u korištenim referencama. U sljedećem primjeru četiriju susjednih formula Excel prikazuje pogrešku pokraj formule =SUM(A10:C10) u ćeliji D4 jer susjedne formule povećavaju vrijednost za jedan redak, a ta formula povećava vrijednost za 8 redaka – Excel očekuje formulu =SUM(A4:C4).

     

     Ako reference korištene u formuli nisu dosljedne onima u susjednim formulama, Excel prikazuje pogrešku.

   • Formule koje izostavljaju ćelije unutar područja: formula možda neće automatski obuhvatiti reference na podatke koje umetnete između izvornog raspona podataka i ćelije koja sadrži formulu. Ovim se pravilom uspoređuju reference u formuli sa stvarnim rasponom ćelija susjednim ćeliji koja sadrži formulu. Ako susjedne ćelije sadrže dodatne vrijednosti i nisu prazne, Excel prikazuje pogrešku pokraj formule.

     Excel, primjerice, umeće pogrešku pokraj formule =SUM(D2:D4) kada se primjenjuje to pravilo jer su ćelije D5, D6 i D7 susjedne ćelijama na koje se poziva formula i ćelija koja sadrži formulu (D8), a te ćelije sadrže podatke na koje se formula trebala pozivati.

     

   • Nezaključane ćelije koje sadrže formule: Formula nije zaključana radi zaštite. Sve su ćelije na radnom listu po zadanome zaključane, pa ih nije moguće promijeniti dok je radni list zaštićen. To može pridonijeti sprječavanju slučajnih pogrešaka kao što su slučajno brisanje ili mijenjanje formula. Ta pogreška znači da je ćelija postavljena tako da bude otključana, ali list nije zaštićen. Provjerite ne želite li da se ćelija zaključa.

   • Formule koje upućuju na prazne ćelije: formula sadrži referencu na praznu ćeliju. To može dovesti do neželjenih rezultata, kao što je prikazano u sljedećem primjeru.

     Pretpostavimo da želite izračunati prosjek brojeva u sljedećem stupcu ćelija. Ako je treća ćelija prazna, ona nije obuhvaćena izračunom, a rezultat je 22,75. Ako treća ćelija sadrži nulu, rezultat je 18,2.

     

   • Podaci uneseni u tablicu nisu valjani: u tablici postoji pogreška provjere valjanosti. Provjerite postavku provjere valjanosti ćelije tako da otvorite karticu Podaci > Alati za podatke > provjera valjanosti podataka.

1. Odaberite radni list na kojem želite potražiti pogreške.

2. Ako se izračuni na radnom listu izvode ručno, pritiskom na tipku F9 pokrenite ponovni izračun.

   Ako se ne prikazuje dijaloški okvir Provjera pogrešaka, odaberite Formule > nadzor formula > provjeru pogrešaka.

3. Ako ste prethodno zanemarili pogreške, te pogreške možete ponovno provjeriti na sljedeći način: idite na Mogućnosti >datoteka > formule. U programu Excel na Macu odaberite izbornik Programa Excel > Preference > provjera pogrešaka.

   U odjeljku Provjera pogrešaka odaberite Vrati zanemarene pogreške >redu.

   

   Napomena: Vraćanje zanemarenih pogrešaka na izvorne vrijednosti primjenjuje se na sve pogreške na svim radnim listovima u aktivnoj radnoj knjizi.

   Savjet: Mogli bi vam pomoći ako dijaloški okvir Provjera pogrešaka smjestite neposredno ispod trake formule.

   

4. Odaberite jedan od akcijskih gumba na desnoj strani dijaloškog okvira. Dostupne akcije različite su za svaku vrstu pogreške.

5. Odaberite Dalje.

1. Pokraj ćelije odaberite Provjera pogrešaka , a zatim odaberite željenu mogućnost. Dostupne naredbe ovise o vrsti pogreške, a prva stavka opisuje pogrešku.

   Ako odaberete Zanemari pogrešku, pogreška će se označiti kao zanemarena za svaku uzastopnu provjeru.

   

Ako formula ne može ispravno procijeniti rezultat, Excel prikazuje vrijednost pogreške, kao što su #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, i #VALUE!. Svaka vrsta pogreške ima različite uzroke i različita rješenja.

Sljedeća tablica sadrži veze na članke u kojima su detaljno opisane te pogreške, a uz njih kratak opis da biste ih lakše otklonili.

Tema	Opis
Ispravljanje pogreške ####	Excel ovu pogrešku prikazuje ako stupac nije dovoljno širok da bi se prikazali svi znakovi u ćeliji ili ako ćelija sadrži negativne vrijednosti datuma i vremena. Formula koja oduzima dan u budućnosti od dana u prošlosti, kao što je na primjer =06/15/2008-07/01/2008, vraća negativnu vrijednost datuma. Savjet: Probajte samoprilagoditi ćeliju tako da dvokliknete između zaglavlja stupaca. Ako se prikazuje ### jer Excel ne može prikazati sve znakove koje je ispravila.
Ispravljanje pogreške #DIJ/0! pogreška	Excel ovu pogrešku prikazuje kada se broj dijeli s nulom (0) ili s ćelijom koja ne sadrži vrijednost. Savjet: Dodajte ručicu pogreške kao u sljedećem primjeru, što je =IF(C2,B2/C2,0)
Ispravljanje pogreške #N/A	Excel ovu pogrešku prikazuje kada funkciji ili formuli nije dostupna vrijednost. Ako koristite funkciju kao što je VLOOKUP, ima li to što pokušavate pronaći podudarni rezultat u rasponu pretraživanja? Najčešće nema. Pokušajte koristiti IFERROR da biste riješili pogrešku #N/D. U tom slučaju možete koristiti: =IFERROR(VLOOKUP(D2,$D$6:$E$8,2,TRUE),0)
Ispravljanje pogreške #NAZIV? pogreška	Ova se pogreška prikazuje kada Excel ne prepoznaje tekst u formuli. Primjerice, naziv raspona ili naziv funkcije mogu biti pogrešno napisani. Napomena: Ako koristite neku funkciju, provjerite je li naziv funkcije pravilno napisan. U ovom slučaju SUM je nepravilno napisan. Uklonite "e" i Excel će to ispraviti.
Ispravljanje pogreške #NULL! pogreška	Excel ovu pogrešku prikazuje kada navedete presjek dvaju područja koja se ne sijeku. Operator presjeka znak je razmaka koji odvaja reference u formuli. Napomena: Provjerite jesu li rasponi ispravno razdvojeni – područja C2:C3 i E4:E6 ne sijeku se, pa unosom formule =SUM(C2:C3 E4:E6) vraća #NULL! pogreška. Postavljanjem zareza između raspona C i E ispravlja se =SUM(C2:C3;E4:E6)
Ispravljanje pogreške #NUM! pogreška	Excel ovu pogrešku prikazuje kada formula ili funkcija sadrže numeričke podatke koji nisu valjani. Koristite li funkciju koja iterira, kao što su IRR ili RATE? Ako je tako, #NUM! je vjerojatno zato što funkcija ne može pronaći rezultat. Upute za rješavanje potražite u temi pomoći.
Ispravljanje pogreške #REF! pogreška	Excel ovu pogrešku prikazuje kada referenca ćelije nije valjana. Možda ste, primjerice, izbrisali ćelije na koje su se odnosile druge formule ili ste zalijepili ćelije koje ste premjestili na vrh ćelija na koje su se odnosile druge formule. Jeste li slučajno izbrisali redak ili stupac? U ovoj formuli, =SUM(A2,B2,C2), slučajno smo izbrisali stupac B i pogledajte što se dogodilo. Pomoću naredbe Poništi (Ctrl + Z) poništite odabir, ponovno napišite formulu ili koristite kontinuirani raspon referenci kao što je ovaj: =SUM(A2:C2), koji bi se automatski ažurirao nakon brisanja stupca B.
Ispravljanje pogreške #VRIJEDNOST! pogreška	Excel može prikazati ovu pogrešku ako formula sadrži ćelije koje sadrže različite vrste podataka. Koristite li matematičke operatore (+, -, *, /, ^) s različitim vrstama podataka? U tom slučaju pokušajte koristiti funkciju. U ovom bi slučaju =SUM(F2:F5) ispravio problem.

Kada ćelije nisu vidljive na radnom listu, te ćelije i njihove formule možete pogledati na alatnoj traci nadzornog prozora. Nadzorni prozor je praktičan za provjeru, nadziranje ili potvrđivanje izračuna i rezultata formule na velikim radnim listovima. Uz nadzorni prozor ne morate se neprestano pomicati ili prelaziti na različite dijelove radnog lista.

Ta se alatna traka može premjestiti ili usidriti kao svaka druga alatna traka. Možete je, primjerice, usidriti na dno prozora. Alatna traka prati sljedeća svojstva ćelija: 1) radna knjiga, 2) list, 3) naziv (ako ćelija ima odgovarajući imenovani raspon), 4) adresa ćelije, 5) vrijednost i 6) formula.

Dodavanje ćelija u nadzorni prozor

1. Odaberite ćelije koje želite nadzirati.

   Da biste odabrali sve ćelije na radnom listu s formulama, idite na Polazno > Uređivanje> odaberite Pronađi & Odaberi (ili možete koristiti Ctrl + G ili Control + G na Macu)> Idi na posebno >Formule.

   

2. Idite na Formule > nadzor formula >odaberite Nadzorni prozor.

3. Odaberite Dodaj nadzor.

   

4. Provjerite jeste li odabrali sve ćelije koje želite gledati, a zatim odaberite Dodaj.

   

5. Da biste promijenili širinu stupca nadzornog prozora, vucite granicu na desnoj strani zaglavlja stupca.

6. Da biste prikazali ćeliju na koju se stavka na alatnoj traci nadzornog prozora poziva, dvokliknite stavku.

   Napomena: Ćelije koje sadrže vanjske reference na druge radne knjige prikazuju se na alatnoj traci nadzornog prozora samo kada su druge radne knjige otvorene.

Uklanjanje ćelija iz nadzornog prozora

1. Ako alatna traka Nadzornog prozora nije prikazana, idite na Formule > nadzor formula > odaberite Nadzorni prozor.

2. Odaberite ćelije koje želite ukloniti.

   Da biste odabrali više ćelija, pritisnite CTRL, a zatim odaberite ćelije.

3. Odaberite Izbriši nadzor.

   

Ponekad je teško shvatiti kako ugniježđena formula izračunava konačni rezultat jer postoji nekoliko prijelaznih izračuna i logičkih testova. No ako koristite dijaloški okvir Analiza formule, možete vidjeti različite dijelove ugniježđene formule koji se analiziraju redoslijedom kojim se formula izračunava. Primjerice, formulu =IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0) lakše je razumjeti kada možete vidjeti sljedeće posredne rezultate:

U dijaloškom okviru Analiza formule	Opis
=IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0)	Na početku se prikazuje ugniježđena formula. Funkcije AVERAGE i SUM ugniježđene su unutar funkcije IF. Raspon ćelija D2:D5 sadrži vrijednosti 55, 35, 45 i 25, pa rezultat funkcije AVERAGE(D2:D5) iznosi 40.
=IF(40>50,SUM(E2:E5),0)	Raspon ćelija D2:D5 sadrži vrijednosti 55, 35, 45 i 25, pa rezultat funkcije AVERAGE(D2:D5) iznosi 40.
=IF(False,SUM(E2:E5),0)	Budući da 40 nije veće od 50, izraz u prvom argumentu funkcije IF (argument logički_test) ima vrijednost FALSE. Funkcija IF vraća vrijednost trećeg argumenta (argumenta vrijednost_ako_je_false). Funkcija SUM ne analizira se jer je riječ o drugom argumentu funkcije IF (argument vrijednost_ako_je_true), koji se vraća samo kada je vrijednost izraza TRUE.

1. Odaberite ćeliju koju želite analizirati. Analizirati je moguće samo jednu po jednu tablicu.

2. Idite na Formule > nadzor formula >analizirajte formulu.

3. Odaberite Analiziraj radi pregleda vrijednosti podcrtane reference. Rezultat izračuna se prikazuje u kurzivu.

   Ako je podcrtani dio formule referenca na neku drugu formulu, odaberite Korak u da biste prikazali drugu formulu u okviru Analiza. Odaberite Izađi za povratak na prethodnu ćeliju i formulu.

   Gumb Korak prema unutra nije dostupan za referencu drugi put kada se referenca pojavi u formuli ili ako se formula poziva na ćeliju u nekoj drugoj radnoj knjizi.

4. Nastavite odabirom mogućnosti Procijeni dok se ne procijeni svaki dio formule.

5. Da biste ponovno vidjeli procjenu, odaberite Ponovno pokreni.

6. Da biste završili procjenu, odaberite Zatvori.