Pronalaženje pogrešaka formule u programu Excel

Osim što vraćaju neželjene rezultate, formule katkad mogu vratiti i pogrešne vrijednosti. U nastavku su navedeni neki alati pomoću kojih možete pronaći i istražiti uzroke tih pogrešaka i utvrditi rješenja.

Unos jednostavne formule

Formule su jednadžbe kojima se izvršavaju izračuni na vrijednostima na radnom listu Formula započinje znakom jednakosti (=). Sljedeća formula, primjerice, zbraja 3 i 1.

=3+1

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

Dijelovi formule

1. Funkcije: funkcije, koje su sastavni dio programa Excel, strojne su formule koje izvode specifične izračune. Primjerice, funkcija PI() vraća vrijednost broja pi: 3,142...

2. Reference: pogledajte pojedinačne ćelije ili raspone ćelija. A2 vraća vrijednost u ćeliji A2.

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

4. Operatori: Operator ^ (karet) diže broj na potenciju, a * (zvjezdica) ga množi. Pomoću operatora + i – zbrajajte i oduzimajte, a pomoću operatora / dijelite.

   Napomena

   Za neke funkcije potrebni su argumenti. Argumenti su vrijednosti pomoću kojih određene funkcije izvode izračune. Kada su potrebni, argumenti se stavljaju unutar zagrada u funkciji (). Za funkciju PI nisu potrebni nikakvi argumenti, pa je zato prazna. Za neke funkcije potrebni su argumenti ili više njih, a može postojati i prostor za dodatne argumente. Argumente razdvajate zarezom ili točkom sa zarezom (;) ovisno o postavkama mjesta.

Funkcija SUM, primjerice, zahtijeva samo jedan argument, ali može prihvatiti ukupno 255 argumenata.

=SUM(A1:A10) primjer je jednog argumenta.

=SUM(A1:A10, C1:C10) primjer je više argumenata.

Ispravljanje uobičajenih pogrešaka prilikom unosa formula

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 upišete, primjerice, SUM(A1:A10), Excel će prikazati tekstni niz SUM(A1:A10) i neće izvršiti izračun. Ako upišete 11/2, Excel prikazuje datum 2. studenog (pod pretpostavkom da je oblik ćelije Općenito) umjesto da dijeli 11 s 2.
Uparite sve otvorene i zatvorene zagrade	Provjerite ima li svaka zagrada svoj par. Kada u formuli koristite funkciju, važno je da svaka zagrada bude na odgovarajućem položaju da bi funkcija pravilno 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	Za neke funkcije, npr . SUM, potrebni su numerički argumenti. U ostalim funkcijama, npr. u funkciji REPLACE, barem jedan argument mora biti tekstna vrijednost. Ako kao argument koristite pogrešnu vrstu podataka, Excel može 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 ( ' ), npr . ='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, želite pribrojiti 3100 vrijednosti u ćeliji A3, a unijeli ste formulu =SUM(3,100,A3), Excel zbraja brojke 3 i 100 te zatim pribraja taj zbroj vrijednosti iz A3, umjesto da zbroji 3100 i vrijednosti iz ćelije A3, što će biti =SUM(3100,A3). Ili, ako unesete formulu =ABS(-2,134), Excel prikazuje pogrešku jer funkcija ABS prihvaća samo jedan argument: =ABS(-2134).

Rješavanje uobičajenih problema u formulama

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 je moguće ispraviti pomoću mogućnosti koje se prikazuju ili zanemariti ako 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.

Uključivanje i isključivanje pravila kojima se traže pogreške

1. Ako koristite Excel u sustavu Windows, odaberiteMogućnosti>datoteke>,Formule ili
   za Excel na Macu odaberite izbornik > Excel Preference > provjere 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: u formuli se ne koristi očekivana sintaksa, argumenti ili vrste podataka. Vrijednosti pogrešaka obuhvaćaju #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! i #VALUE!. Svaka vrijednost pogreške ima drugačiji uzrok i različit način rješavanja.

     Napomena

     Ako pogrešnu vrijednost unesete izravno u ćeliju, ona neće biti 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šete 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 predstavljene dvoznamenkastim brojem: ćelija sadrži datum u tekstnom obliku koji se prilikom korištenja u formulama može interpretirati kao da pripada pogrešnom stoljeću. Datum u formuli =YEAR("1/1/31"), primjerice, može biti u godini 1931. ili 2031. Ovo pravilo koristite za provjeru nejasnih datuma u tekstnom obliku.

   • 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 u obliku teksta mogu uzrokovati neočekivane rezultate sortiranja, pa ih nalaze 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 se slučajevima susjedne formule razlikuju samo po 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 usklađene s onima u susjednim formulama, Excel će prikazati 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.
     Kada se to pravilo primjenjuje, Excel, primjerice, umeće pogrešku pokraj formule =SUM(D2:D4) jer su ćelije D5, D6 i D7 susjedne ćelijama na koje se poziva formula i ćeliji 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 želite li da ćelija bude zaključana.

   • 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 se ne uvrštava u izračun i 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 vezana uz provjeru valjanosti. Provjerite postavku provjere valjanosti za ćeliju tako da na kartici >Podaci grupa Alati> za podatke Provjera valjanosti podataka.

Ispravljanje uobičajenih pogrešaka u formulama jedne po jedne

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 prikaže dijaloški okvir Provjera pogrešaka, odaberite Formule>Nadzor> formulaProvjera pogrešaka.

3. Ako ste prethodno zanemarili neke pogreške, možete ih ponovno potražiti na sljedeći način: otvoriteFormula mogućnosti>datoteke>. Za Excel na Macu odaberite izbornik > Excel Preference > provjere pogrešaka.
   U odjeljku Provjera pogrešaka odaberite Vrati zanemarene pogreške> na izvorno Uredu.

   

   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.

Pojedinačno ispravljanje uobičajenih pogrešaka u formulama

1. Uz ćeliju odaberite ikonu Provjera , 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 obilježiti kao zanemarena prilikom svake sljedeće provjere.

   

Ispravak pogrešne vrijednosti

Ako formula ne može pravilno izračunati 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 prikaže ### jer Excel ne može prikazati sve znakove, time se ispravlja pogreška.
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škama 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 #NAME?	Ova se pogreška prikazuje kada Excel ne prepoznaje tekst u formuli. Naziv raspona ili naziv funkcije, na primjer, možda nije pravilno napisan. 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 ga 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 vam rasponi pravilno odijeljeni – područja C2:C3 i E4:E6 ne sijeku se pa unos formule =SUM(C2:C3 E4:E6) vraća #NULL! pogreška. Stavljanjem zareza između raspona C i E ispravlja se pogreška =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 se iterira, primjerice IRR ili RATE? Ako je tako, #NUM! vjerojatno zato što funkcija ne može pronaći nijedan rezultat. Upute za rješenje problema potražite u temi pomoći.
Ispravljanje pogreške #REF! pogreška	Excel ovu pogrešku prikazuje kada referenca ćelije nije valjana. Ćelije na koje se pozivaju druge formule možda ste, primjerice, izbrisali ili zalijepili preko drugih ćelija na koje su se pozivale 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 drugim vrstama podataka? Ako je tako, pokušajte koristiti funkciju. U ovom slučaju problem bi riješio =SUM(F2:F5).

Nadzor formule i njezinog rezultata pomoću nadzornog prozora

Kada ćelije nisu vidljive na radnom listu, njih i njihove formule možete nadzirati 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 Početno>uređivanje> Odaberite Traži & Odabir (ili možete upotrijebiti Ctrl+G ili Control + G na Macu)> Idi na posebne>formule.

   

2. Idite na Nadzorformula Odabir nadzornog prozoraFormule>>.

3. Odaberite Dodaj nadzor.

   

4. Provjerite jeste li odabrali sve ćelije koje želite nadzirati pa 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 se alatna traka nadzornog prozora ne prikazuje, idite na Nadzor formula>Nadzor> formula i 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.

   

Izračun ugniježđene formule korak po korak

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 u programu Excel za Windows koristite analizu 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 shvatiti kada pogledate sljedeće prijelazne 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. U programu Excel za Windows odaberite ćeliju koju želite analizirati. Analizirati je moguće samo jednu po jednu tablicu.
2. Idite na Formulas>>Nadzor formulaAnaliza formule.
3. Odaberite Analiziraj radi pregleda vrijednosti podcrtane reference. Rezultat izračuna se prikazuje u kurzivu.
   Ako je podcrtani dio formule referenca na drugu formulu, odaberite Korak prema unutra za prikaz druge formule 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 odabirati Izračunaj dok se ne analizira svaki dio formule.
5. Da biste ponovno vidjeli procjenu, odaberite Ponovno pokreni.
6. Da biste prekinuli analizu, odaberite Zatvori.

Je li vam potrebna dodatna pomoć?

Uvijek možete postaviti pitanje stručnjaku u tehničkoj zajednici za Excel ili zatražiti podršku u zajednicama.

Dodatne informacije

Prikaz odnosa između formula i ćelija

Izbjegavanje neispravnih formula