Izbjegavanje neispravnih formula

Izbjegavanje neispravnih formula

Ako Excel ne može riješiti formulu koju pokušavate stvoriti, može se pojaviti poruka o pogrešci kao što je ova:

Slika dijaloškog okvira „Problem s ovom formulom” u programu Excel

Nažalost, to znači da Excel ne razumije što pokušavate učiniti, stoga biste mogli zatvoriti i početi ispočetka.

Najprije kliknite U redu ili pritisnite ESC da biste zatvorili poruku o pogrešci.

Vratit ćete se u ćeliju s neispravnom formulom, koja će biti u načinu uređivanja, a Excel će istaknuti mjesto gdje se nalazi problem. Ako još uvijek ne znate što učiniti i želite početi ispočetka, ponovno pritisnite ESC ili kliknite gumb Odustani na traci formule pa ćete zatvoriti način uređivanja.

Slika gumba Odustani na traci s formulama

Ako niste sigurni što trebate učiniti ili kakva vam pomoć treba, potražite slična pitanja na forumu zajednice korisnika programa Excel ili objavite vlastito pitanje.

Veza na forum zajednice korisnika programa Excel

Ako želite ići dalje, sljedeći kontrolni popis sadrži korake za otklanjanje poteškoća pomoću kojih možete otkriti što nije u redu s formulama.

Excel prikazuje različite poruke sa znakom #, kao što su #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? i #NULL!, koje upućuju na to da nešto u formuli ne funkcionira kako treba. Pogrešku #VALUE!, primjerice, uzrokuje pogrešno oblikovanje ili nepodržane vrste podataka u argumentima. Može vam se prikazati i pogreška #REF! ako se formula poziva na ćelije koje su izbrisane ili zamijenjene drugim podacima. Upute za otklanjanje poteškoća razlikuju se za svaku pogrešku.

Napomena : #### nije pogreška povezana s formulom. Ona samo upućuje na to da stupac nije dovoljno širok da se prikaže sadržaj ćelije. Jednostavno povucite stupac da biste ga proširili ili odaberite Polazno > Oblikuj > Samoprilagodi širinu stupca.

Slika Polazno > Oblik > Širina samoprilagodbe stupca

Pročitajte neku od sljedećih tema, ovisno o tome koja vam se pogreška sa znakom # prikazuje:

Svaki put kada otvorite proračunsku tablicu koja sadrži formule koje se pozivaju na vrijednosti u drugim proračunskim tablicama, zatražit će se da ažurirate reference ili da ih ostavite neizmijenjene.

Dijaloški okvir Neispravne reference u programu Excel

Excel prikazuje gornji dijaloški okvir da bi provjerio upućuju li uvijek formule u trenutnoj proračunskoj tablici na najažurniju vrijednost, u slučaju da se referentna vrijednost promijenila. Možete odabrati ažuriranje referenci ili preskočiti taj korak ako ih ne želite ažurirati. Čak i ako odlučite ne ažurirati reference, u svakom trenutku možete ručno ažurirati veze u proračunskoj tablici.

U svakom trenutku možete onemogućiti prikaz dijaloškog okvira prilikom pokretanja. Da biste to učinili, odaberite Datoteka > Mogućnosti > Napredno > Općenito pa poništite okvir Pitaj za ažuriranje automatskih veza. U programu Excel 2007 kliknite gumb Office > Mogućnosti programa Excel. Gumb sustava Office 2007

Slika mogućnosti Zatraži ažuriranje automatske veze

Važno : Ako prvi put radite s neispravnim vezama u formulama, ako morate osvježiti znanje o rješavanju neispravnih veza ili ako ne znate je li potrebno ažurirati reference, pročitajte članak Kontrola vremena ažuriranja vanjskih referenci (veza).

Ako formula ne prikazuje vrijednost, učinite sljedeće:

  • Postavite Excel tako da se u proračunskoj tablici prikazuju formule. Da biste to učinili, kliknite karticu Formule pa u grupi Kontrola formule kliknite Prikaži formule.

    Savjet : To možete učiniti i pomoću tipkovnog prečaca Ctrl + q. Kada to učinite, stupci će se automatski proširiti da bi se prikazale formule, ali ne brinite, kada se vratite na normalni prikaz, veličina stupaca će se promijeniti.

  • Ako gore navedene upute ne riješe problem, možda je ćelija oblikovana kao tekst. Desnom tipkom miša kliknite ćeliju pa odaberite Oblikuj ćelije > Općenito (ili pritisnite tipke Ctrl + 1), a potom pritisnite F2 > Enter da biste promijenili oblik.

  • Ako imate veliki raspon ćelija u stupcu koje su oblikovane kao tekst, odaberite raspon, primijenite željeni oblik broja pa odaberite Podaci > Tekst u stupac > Završi. Na taj će se način oblikovanje primijeniti na sve odabrane ćelije.

    Slika dijaloškog okvira Podaci > Tekst u stupce

Kada formula ne izračunava rezultat, morate provjeriti je li omogućen automatski izračun u programu Excel. Ako je omogućen ručni izračun, formule neće izračunati rezultat. Da biste potražili mogućnost automatskog izračuna, učinite sljedeće:

  1. Kliknite karticu Datoteka, zatim Mogućnosti, a potom kategoriju Formule.

  2. U odjeljku Mogućnosti izračuna u odjeljku Izračun u radnoj knjizi provjerite je li odabrana mogućnost Automatski.

    Slika mogućnosti Automatski i ručni izračun

Dodatne informacije o izračunima potražite u članku Promjena ponovnog izračuna, iteracije i preciznosti formula.

Kružna se referenca pojavljuje kada se formula poziva na ćeliju u kojoj se nalazi. Problem možete riješiti tako da formulu premjestite u neku drugu ćeliju ili tako da promijenite sintaksu formule tako da ne uzrokuje pojavu kružnih referenci. No kružne su reference u nekim slučajevima potrebne jer omogućuju iteraciju funkcija – njihovo ponavljanje dok se ne ispuni određeni brojčani uvjet. U tom ćete slučaju morati omogućiti značajku izračuna s iteracijama.

Dodatne informacije o kružnim referencama potražite u članku Traženje i rješavanje kružne reference

Ako unos ne započinje znakom jednakosti, to nije formula i rezultat se neće izračunati, što je česta pogreška.

Kada upišete nešto slično ovome: SUM(A1:A10), Excel umjesto rezultata formule prikazuje tekstni niz SUM(A1:A10). Ako sada unesete 11/2, Excel ne dijeli 11 s 2, već prikazuje datum, npr. 2. stu ili 11/02/2009.

Da biste izbjegli te neočekivane rezultate, formulu uvijek započnite znakom jednakosti. Upišite, primjerice: =SUM(A1:A10) i =11/2

Kada u formuli koristite funkciju, za svaku lijevu zagradu mora postojati pripadajuća desna zagrada da bi funkcija funkcionirala kako treba, pa provjerite jesu li sve zagrade uparene. Formula =IF(B5<0),"Nije valjano",B5*1,05), primjerice, neće funkcionirati jer postoje dvije desne zagrade, a samo jedna lijeva. Ispravna formula izgleda ovako: =IF(B5<0,"Nije valjano",B5*1,05).

Funkcije programa Excel imaju argumente – vrijednosti koje morate unijeti da bi funkcije funkcionirale. Samo za nekoliko funkcija (npr. PI ili TODAY) ne unose se nikakvi argumenti. Da biste bili sigurni da funkcija sadrži sve potrebne argumente, provjerite sintaksu formule koja se prikazuje kada počnete pisati.

Funkcija UPPER, primjerice, kao argument prihvaća samo jedan tekstni niz ili referencu na ćeliju: =UPPER("zdravo") ili =UPPER(C2)

Napomena : Popis argumenata funkcije prikazivat će vam se na plutajućoj alatnoj traci s referencama za funkciju ispod formule koju upisujete.

Snimka zaslona s prikazanom alatnom trakom s referencama za funkciju
Alatna traka s referencama za funkciju

Neke pak funkcije, npr. SUM, prihvaćaju samo brojčane argumente, dok je za druge, npr. REPLACE, obavezno unijeti tekstnu vrijednost barem za jedan od argumenata. Ako koristite pogrešnu vrstu podataka, neke funkcije mogu davati neočekivane rezultate ili prikazivati pogrešku #VALUE!.

Ako želite brzo potražiti sintaksu određene funkcije, pogledajte popis funkcija programa Excel (po kategorijama)

U formule nemojte unositi brojeve oblikovane znakom dolara ($) ili, ako koristite američku regionalnu shemu, razdjelnikom tisućica (,) jer znak dolara označava apsolutne reference, a zarez u toj regionalnoj shemi služi kao razdjelnik argumenata. Umjesto $1,000, u formulu unesite 1000.

Ako koristite oblikovane brojke u argumentima, dobit ćete neočekivane izračune, ali bi vam se mogla prikazati i pogreška #NUM!. Primjerice, ako unesete formulu =ABS(-2,134) da biste dobili apsolutnu vrijednost -2134, Excel će prikazati pogrešku #NUM! jer funkcija ABS prihvaća samo jedan argument.

Napomena : Rezultat formule možete oblikovati razdjelnikom tisućica i simbolom valute nakon što unesete formulu pomoću neoblikovanih brojeva (konstanti). Konstante obično nije dobro unositi u formule jer ih je teško pronaći ako ih je potrebno naknadno ažurirati, a i lako ih je pogrešno napisati. Konstante je mnogo bolje unijeti u ćelije, gdje ih je lako pronaći i gdje se jednostavno referenciraju.

Formula možda neće vratiti očekivane rezultate ako vrstu podataka u ćeliji nije moguće koristiti u izračunima. Na primjer, ako unesete jednostavnu formulu = 2 + 3 u ćeliju oblikovanu kao tekst, Excel ne može izračunati unesene podatke. U ćeliji će se prikazati samo = 2 + 3. Da biste riješili taj problem, promijenite vrstu podataka iz Tekst u Općenito ovako:

  1. Odaberite ćeliju.

  2. Kliknite Polazno > strelicu pokraj mogućnosti Oblik broja (ili pritisnite tipke Ctrl + 1), a zatim Općenito.

  3. Pritisnite tipku F2 da biste ćeliju postavili u način rada za uređivanje pa Enter da biste prihvatili formulu.

Datum koji unesete u ćeliju čija je vrsta podataka Broj može se umjesto kao datum prikazivati kao brojčana datumska vrijednost. Da biste prikazali taj broj kao datum, u galeriji Oblik broja odaberite oblik Datum.

Često se u formuli kao operator množenja koristi slovo x, ali Excel za množenje prihvaća samo zvjezdicu (*). Ako u formuli koristite konstante, Excel prikazuje poruku o pogrešci i formulu može ispraviti tako da slovo x zamijenite zvjezdicom (*).

Okvir s porukom u kojem se traži da za množenje umjesto znaka x koristite znak *
Poruka o pogrešci zbog korištenja slova x s konstantama umjesto znaka * za množenje

No ako koristite reference ćelija, Excel će vratiti pogrešku #NAME?.

Pogreška #NAME? prilikom korištenja znaka x s referencama ćelija umjesto znaka * za množenje
Pogreška #NAME? prilikom korištenja slova x s referencama ćelija umjesto znaka *

Ako stvorite formulu koja obuhvaća tekst, umetnite tekst u navodnike.

Primjerice, formula ="Danas je " & TEXT(TODAY(),"dddd, mmmm dd") kombinira tekst "Danas je " s rezultatima funkcija TEXT i TODAY te daje rezultat kao što je Danas je ponedjeljak, 30. svibnja.

Prije drugog navodnika u segmentu "Danas je " u formuli umetnut je razmak koji želite dobiti između segmenata „Danas je” i „ponedjeljak, 30. svibnja”. Bez navodnika oko teksta formula bi prikazivala pogrešku #NAZIV?.

Unutar funkcije možete kombinirati (ili ugnijezditi) najviše 64 razine funkcija.

Primjerice, formula =IF(SQRT(PI())<2,"Manje od dva!","Više od dva!") ima tri razine funkcija: funkcija PI ugniježđena je unutar funkcije SQRT, koja je pak ugniježđena unutar funkcije IF.

Kada upišete reference na vrijednosti ili ćelije na drugom radnom listu, a naziv tog lista sadrži znak koji nije slovo abecede (npr. razmak), stavite ga u jednostruke navodnike (').

Da biste, primjerice dobili vrijednost iz ćelije D3 na radnom listu koji se zove Podaci za tromjesečje u vašoj radnoj knjizi, upišite: ='Podaci za tromjesečje'!D3. Bez navodnika oko naziva lista, formula prikazuje pogrešku #NAME?.

Možete i kliknuti vrijednosti ili ćelije na drugom listu da biste se pozvali na njih u formuli. Excel zatim automatski dodaje navodnike oko naziva listova.

Kada upišete reference na vrijednosti ili ćelije u drugoj radnoj knjizi, navedite naziv radne knjige u uglatim zagradama ([]) nakon čega slijedi naziv radnog lista koji sadrži vrijednosti ili ćelije.

Na primjer, da biste se pozvali na ćelije A1 do A8 na listu Prodaja u radnoj knjizi Q2 operacije koja je otvorena u programu Excel, upišite: = [Q2 Operations.xlsx]Sales! A1:a8. Bez uglatih zagrada, formula prikazuje pogrešku #REF!.

Ako radna knjiga nije otvorena u programu Excel, unesite cijeli put do datoteke.

Na primjer, = ROWS ('C:\My Documents\ [Q2 Operations.xlsx]Sales'! A1:a8).

Napomena : Ako puni put sadrži razmake, obuhvatite ga jednostrukim navodnicima (na početku puta i nakon naziva lista prije uskličnika).

Savjet : Put do druge radne knjige najjednostavnije ćete dodati tako da otvorite drugu radnu knjigu, u izvornoj radnoj knjizi upišete =, pritisnete tipke Alt+Tab da biste prešli na drugu radnu knjigu, a zatim odaberete bilo koju ćeliju na željenom listu. Nakon toga zatvorite izvorišnu radnu knjigu. Formula će se automatski ažurirati tako da prikazuje cijeli put do datoteke i naziv lista uz potrebnu sintaksu. Možete i kopirati i zalijepiti put i koristiti ga gdje god vam je potreban.

Dijeljenje ćelije drugom ćelijom koja ima vrijednost nula (0) ili nema nikakvu vrijednost rezultira pogreškom#DIV/0!.

Tu pogrešku možete izravno otkloniti i provjeriti postoji li nazivnik.

=IF(B1,A1/B1,0)

Odnosno AKO(postoji B1, podijeli A1 s B1, a u suprotnom vrati 0).

Uvijek provjerite imate li sve formule koje se odnose na podatke u ćelijama, rasponima, definiranim nazivima, radnim listovima ili radnim knjigama, prije no što bilo što izbrišete. Zatim možete zamijeniti te formule njihovim rezultatima prije no što uklonite referencirane podatke.

Ako se formule ne mogu zamijeniti njihovim rezultatima, pregledajte ove podatke o pogreškama i rješenja:

  • Ako se formula odnosi na ćelije koje su izbrisane ili zamijenjene drugim podacima i vraća pogrešku #REF!, odaberite ćeliju s pogreškom #REF!. U traci formule odaberite #REF! i izbrišite to. Nakon toga ponovno unesite raspon za formulu.

  • Ako nema definiranog naziva i formula koja se poziva na taj naziv vraća pogrešku #NAME?, definirajte novi naziv koji se poziva na raspon koji želite ili promijenite formulu tako da se poziva izravno na raspon ćelija (primjerice, A2:D8).

  • Ako radni list ne postoji, a formula koja se odnosi na njega vraća pogrešku #REF!, ne postoji, nažalost, rješenje za taj problem. Izbrisani radni list ne može se vratiti.

  • Ako nema radne knjige, formula koja upućuje na njega ostaje netaknuta dok ne ažurirate formulu.

    Na primjer, ako je formula = [Book1.xlsx]Sheet1'! A1 i više nemate knjigu Book1.xlsx, vrijednosti koje se pozivaju u toj radnoj knjizi ostaju dostupne. No ako ćete uređivati i spremati formulu koja upućuje na radnu knjigu, Excel prikazuje dijaloški okvir Ažuriranje vrijednosti i traži da unesete naziv datoteke. Kliknite Odustani, a zatim provjerite jesu li neki od tih podataka izgubljeni pri zamjeni formula koje se pozivaju na radnu knjigu koja ne postoji s rezultatima formula.

Ponekad, kada kopirate sadržaj ćelije, želite zalijepiti samo vrijednost, ne i formulu u podlozi koju prikazuje traka formule.

Ako, na primjer, želite kopirati vrijednost formule u ćeliju na drugom radnom listu. Ili ako pak želite izbrisati vrijednosti koje ste koristili u formuli nakon kopiranja vrijednosti u drugu ćeliju na radnom listu. Obje te akcije uzrokuju pogrešku (#REF!) koja se pojavljuje u odredišnoj ćeliji i ukazuje na to da referenca na ćeliju nije valjana jer više nije moguće pozivanje na ćelije koje sadrže vrijednosti korištene u formuli.

Tu pogrešku možete izbjeći lijepljenjem vrijednosti formula bez formule u odredišne ćelije.

  1. Na radnom listu odaberite ćelije koje sadrže vrijednosti formule koje želite kopirati.

  2. Na kartici Polazno u grupi Međuspremnik kliknite Kopiraj Gumb Popis mapa u navigacijskom oknu .

    slika trake programa excel

    Tipkovni prečac: pritisnite CTRL + C.

  3. Odaberite gornju lijevu ćeliju područje lijepljenja.

    Savjet : Da biste premjestili ili kopirali odabir u drugi radni list ili radnu knjigu, kliknite drugu karticu radnog lista ili se prebacite u drugu radnu knjigu, a potom odaberite gornju lijevu ćeliju područja lijepljenja.

  4. Na kartici Polazno u grupi Međuspremnik kliknite Zalijepi Slika gumba , a zatim kliknite Zalijepi vrijednosti ili pritisnite Alt > E > S > V > Enter u sustavu Windows ili Option > Command > V > V > Enter na Macu.

Da biste proučili kako složene ili ugniježđene formule izračunavaju konačni rezultat, možete analizirati tu formulu.

  1. Odaberite formulu koju želite analizirat.

  2. Kliknite Formule > Analiza formule.

    Grupa Nadzor formula na kartici Formule

  3. Kliknite Analizirajte da pogledate vrijednost podcrtane reference. Rezultat izračuna se prikazuje u kurzivu.

    Dijaloški okvir Analiza formule

  4. Ako je podcrtani dio formule referenca na neku drugu formulu, kliknite Korak prema unutra da bi se pokazala druga formula u okviru Analiza. Kliknite Korak prema van da biste se vratili na prethodnu ćeliju i formulu.

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

  5. Nastavite sve dok se ne analizira svaki dio formule.

    Alat za provjeru formula neće vam uvijek reći zašto je formula neispravna, ali pokazat će vam gdje je neispravna. Alat je vrlo praktičan u velikim formulama kada je inače teško pronaći problem.

    Napomene : 

    • Neki dijelovi funkcija IF i CHOOSE neće se analizirati i mogla bi se pojaviti pogreška #N/A u okviru Analiza.

    • Prazna reference prikazuju se kao nula (0) u okviru Analiza.

    • Funkcije koje se izračunavaju prilikom svake promjene na radnom listu. Te funkcije, uključujući funkcije RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW, TODAY i RANDBETWEEN mogu uzrokovati da se u dijaloškom okviru Analiza formule prikazuju rezultati koji se razlikuju od stvarnih rezultata u ćeliji radnog lista.

Je li vam potrebna dodatna pomoć?

Postavite pitanje stručnjaku u tehničkoj zajednici za Excel, zatražite podršku u zajednici za odgovore ili predložite novu značajku ili poboljšanje na forumu za Excel User Voice.

Dodatni sadržaji

Pregled formula u programu Excel

Otkrivanje pogrešaka u formulama

Funkcije programa Excel (abecednim redom)

Funkcije programa Excel(po kategorijama)

Proširite svoje vještine
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

Hvala vam na povratnim informacijama!

Hvala vam na povratnim informacijama! Čini se da bi vam pomoglo kad bismo vas povezali s nekim od naših agenata podrške za Office.

×