Izbjegavanje neispravnih formula

Izbjegavanje neispravnih formula

Napomena: Željeli bismo vam pružiti najnoviji sadržaj pomoći što je brže moguće i to na vašem jeziku. Ova je stranica strojno prevedena te može sadržavati gramatičke pogreške ili netočnosti. Naša je namjera da vam ovaj sadržaj bude koristan. Možete li nam pri dnu ove stranice javiti jesu li vam ove informacije bile korisne? Kao referencu možete pogledati i članak na engleskom jeziku.

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 upućuju na vrijednosti u drugim proračunske tablice, zatražit će se ažurirati reference ili ih kao ostavite-je.

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 slučaju, morat ćete omogućiti Uklanjanje ili dopuštanje kružne reference

Dodatne informacije o kružnim referencama potražite u članku Uklanjanje ili dopuštanje 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 kao što je SUM(A1:A10), Excel prikazuje tekstni niz SUM(A1:A10) umjesto rezultata formule. Ako upišete 11/2, Excel prikazuje s datumom, primjerice 2 – riječi studeni ili 11/02/2009, umjesto dijeljenje 11, 2.

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

Excel funkcije imaju argumente – vrijednosti, morate upisati funkcije za rad. Samo nekoliko funkcije (primjerice PI ili DANAS) koriste nema argumenata. Provjerite sintaksu formule koja se pojavljuje kada počnete upisivati u funkciji da biste provjerili funkciju ima obavezne argumente.

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

Osim toga, neke funkcije, kao što su SUM, zahtijevaju samo numerički argumenti dok ostalih funkcija, kao što su ZAMJENAzahtijevaju tekstne vrijednosti za barem jedno od svoje argumente. Ako koristite vrsta podataka, funkcija može vratiti neočekivane rezultate ili prikaz na #VALUE! pogreške.

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 brojeve u argumentima, prikazat će se izračun neočekivane rezultate, ali može se prikazati u #NUM! pogreške. Ako, na primjer, ako unesete formule =ABS(-2,134) da biste pronašli apsolutna vrijednost argumenta-2134, Excel prikazuje pogrešku #NUM! Pogreška jer Funkcija ABS prihvaća samo jedan od argumenata.

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 *

Međutim, ako koristite reference ćelija, Excel će vratiti #NAME?. .

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

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

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

Na primjer, da biste vratili vrijednost iz ćelije D3 u radni list pod nazivom podaci za tromjesečje u radnoj knjizi, upišite: = 'Podaci za tromjesečje'! D3. Bez navodnika oko naziv lista, prikazuje formulu u #NAME? pogreške.

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

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 vrijednost rezultira u #DIV/0! pogreške.

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 formula upućuje na ćelije koje su izbrisane ili zamijeniti drugim podacima i vraća je #REF! pogreške, odaberite ćeliju koja sadrži #REF! . Na traci formule odaberite #REF! možete i izbrisati. Nakon toga ponovno je unesite raspon za formulu.

  • Ako nema definiranog naziva i formula koja se odnosi na taj naziv vraća na #NAME? pogreške, definirajte novi naziv koji upućuje na raspon koji želite ili promijenite formulu tako da se izravno pozivaju 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 ne morate Book1.xlsx, vrijednosti u toj radnoj knjizi ostaju dostupna. Međutim, ako je uređivanje i spremanje formulu koja se odnosi na toj radnoj knjizi, Excel prikazuje dijaloški okvir Ažuriranje vrijednosti i traži da unesete ključ proizvoda. Kliknite Odustani, a zatim provjerite je li da ovaj podaci ne gube zamjenjujući formule koje upućuju na nedostaju radnu knjigu s rezultatima formule.

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

Na primjer, možda ćete morati kopirati vrijednost rezultata formule u ćeliji na drugom radnom listu. Ili, možda ćete morati izbrisati vrijednosti koje se koristi u formuli nakon nastalu vrijednost koju ste kopirali u drugu ćeliju na radnom listu. Obje akcije uzrokovati pogrešku ćelija nije valjana referenca (#REF!) Da biste prikazuju se u ćeliji odredište jer više ne može upućivati ćelije koje sadrže vrijednosti koje se koriste 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 , i zatim kliknite Zalijepi vrijednosti,, ili pritisnite Alt > E > S > V > Enter za Windows ili mogućnost > naredba > V > V > Enter na web Mac.

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.

Pogledajte i sljedeće

Pregled formula u programu Excel

Pronalaženje pogrešaka u formulama

Funkcije programa Excel (abecednim redoslijedom)

Funkcije programa Excel (po kategorijama)

Proširite svoje vještine korištenja sustava Office
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.

×