Izbjegavanje neispravnih formula u programu Excel

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

Nažalost, to znači da Excel ne razumije što pokušavate učiniti, stoga morate ažurirati formulu ili provjerite koristite li funkciju ispravno.

Vratite 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 odaberite gumb Odustani na traci formule čime ćete izaći iz načina za uređivanje.

Ako želite ići dalje, sljedeći popis za provjeru sadrži korake za otklanjanje poteškoća pomoću kojih možete otkriti što nije u redu. Odaberite naslove kako biste saznali više.

Koristite li ispravne razdjelnike popisa u formuli?

Formule s više argumenata koriste razdjelnike popisa za razdvajanje argumenata. Koji se razdjelnik koristi ovisi o postavkama regionalne sheme operacijskog sustava i programa Excel. Najčešći razdjelnici popisa su zarez „,” i točka sa zarezom „;”.

Formula neće biti ispravna ako njezine funkcije koriste netočne graničnike.

Dodatne informacije potražite u sljedećim člancima: Pogreške formule kada razdjelnik popisa nije pravilno postavljen

Prikazuje li vam se pogreška sa simbolom ljestvi (#) u formulama programa Excel?

Excel prikazuje različite pogreške sa simbolom ljestvi (#), kao što su #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? i #NULL! da biste ukazali na to da nešto u formuli ne funkcionira kako treba i #NULL!, koje upućuju na to da nešto u formuli ne funkcionira kako treba. pogrešku uzrokuje pogrešno oblikovanje ili nepodržane vrste podataka u argumentima. Može vam se prikazati i #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.

Pregledajte bilo koju od sljedećih tema povezanih s pogreškom sa simbolom ljestvi koje vidite:

• Ispravljanje pogreške #NUM! pogreška
• Ispravljanje pogreške #VRIJEDNOST! pogreška
• Ispravljanje pogreške #N/A
• Ispravljanje pogreške #DIJ/0! pogreška
• Ispravljanje pogreške #REF! pogreška
• Ispravljanje pogreške #NAME?
• Ispravljanje pogreške #NULL! pogreška

Popravljanje neispravnih veza u formulama programa Excel

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 od vas da ažurirate reference ili da ih ostavite neizmijenjene.

Excel prikazuje gornji dijaloški okvir kako 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, otvorite Mogućnosti > datoteke>, Napredno>, Općenito i poništite okvir Pitaj za ažuriranje automatskih veza.

Formula u programu Excel umjesto vrijednosti prikazuje sintaksu

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

• Provjerite je li Excel postavljen tako da se u proračunskoj tablici prikazuju formule. Da biste to učinili, odaberite karticu Formule i u grupi Nadzor formula odaberite 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 naveden korak ne riješi problem, možda je ćelija oblikovana kao tekst. Desnom tipkom miša kliknite ćeliju pa odaberite Oblikuj ćelije > općenito (ili Ctrl + 1), a potom pritisnite F2 > Enter da biste promijenili oblik.

• Ako imate stupac s velikim rasponom ćelija koje su oblikovane kao tekst, odaberite raspon, primijenite željeni oblik broja i idite na Tekst > podataka do završetka stupca>. Na taj će se način oblikovanje primijeniti na sve odabrane ćelije.

  

Omogućivanje automatskog izračuna radne knjige ako formula ne izračunava rezultat u programu Excel

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. Slijedite ove korake kako biste provjerili postoji li automatski izračun.

1. Odaberite karticu Datoteka, zatim Mogućnosti, a zatim odaberite kategoriju Formule.

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

   

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

U formuli postoje kružne reference

Kružna se referenca pojavljuje kada se formula poziva na ćeliju u kojoj se nalazi. Problem možete riješiti premještanjem formule u drugu ćeliju ili promjenom sintakse 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 tim slučajevima morate omogućiti mogućnost Ukloni ili dopusti kružnu referencu.

Dodatne informacije o kružnim referencama potražite u članku Uklanjanje ili dopuštanje kružne reference.

Započinje li vaša funkcija sa znakom jednakosti (=)?

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. Osim toga, ako upišete 11/2, Excel prikazuje datum, primjerice 2. studenog ili 2. 11. 2009. umjesto da dijeli 11 s 2.

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

Jeste li uparili sve otvorene i zatvorene zagrade?

Kada koristite funkciju u formuli, svakoj je otvorenoj zagradi potrebna i zatvorena da bi funkcija funkcionirala kako treba. Provjerite jesu li sve zagrade dio odgovarajućeg para. Na primjer, formula =IF(B5<0),"Nije valjano",B5*1,05) neće funkcionirati jer postoje dvije zatvorene zagrade, a samo jedna lijeva. Ispravna formula izgledala bi ovako: =IF(B5<0,"Nije valjano",B5*1,05).

Jesu li u sintaksi prisutni svi obavezni argumenti?

Funkcije programa Excel sadrže argumente – vrijednosti koje morate navesti da bi funkcija funkcionirala. Samo nekoliko funkcija (kao što su PI ili TODAY) ne sadrži argumente. Provjerite sintaksu formule koja se prikazuje kada počnete upisivati u funkciju kako biste bili sigurni da funkcija sadrži potrebne argumente.

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

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, funkcije mogu vratiti neočekivane rezultate ili prikazati pogrešku#VALUE!.

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

Rukovanje neoblikovanim brojevima u formulama programa Excel

U formulu nemojte unositi brojeve oblikovane znakom dolara ($) ili, ako koristite američku regionalnu shemu, razdjelnikom tisućica (,) jer znak dolara upućuje na 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, dobit ćete neočekivane rezultate izračuna, ali bi vam se mogla prikazati i pogreška #NUM! . Ako, primjerice, unesete formulu =ABS(-2,134) kako biste pronašli apsolutnu vrijednost -2134, Excel će prikazati #NUM! pogrešku jer funkcija ABS prihvaća samo jedan argument i vidi -2 i 134 kao zasebne argumente.

Pripadaju li referencirane ćelije točnoj vrsti podataka?

Formula možda neće vratiti očekivane rezultate ako vrstu podataka u ćeliji nije moguće koristiti u izračunima. Ako, primjerice, u ćeliju oblikovanu kao tekst unesete jednostavnu formulu =2+3, 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 teksta u Općenito ovako:

1. Odaberite ćeliju.
2. Odaberite Polazno i odaberite strelicu kako biste proširili grupu Broj ili Oblik broja (ili pritisnite Ctrl + 1). Zatim odaberite 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.

Pokušavate li množiti bez znaka *?

Č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 (*).

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

Nedostaju li navodnici oko teksta u formulama?

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

Sadrži li formula više od 64 funkcije?

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

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

Jesu li nazivi listova obuhvaćeni jednostrukim navodnicima?

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, vratili vrijednost ćelije D3 na radnom listu naziva Quarterly Data u radnoj knjizi, upišite: ='Quarterly Data'!D3. Bez navodnika oko naziva lista formula prikazuje pogrešku #NAME?.

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

Ispravljanje putova vanjskih radnih knjiga u formulama programa Excel

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

Dijelite li numeričke vrijednosti s nulom?

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. Možete koristiti:

=IF(B1,A1/B1,0)

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

Poziva li se formula na izbrisane podatke?

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

Ako formule ne možete zamijeniti njihovim rezultatima, pregledajte ove podatke o pogreškama i rješenja:

• Ako se formula poziva na ćelije koje su izbrisane ili zamijenjene drugim podacima te ako vraća pogrešku #REF!, odaberite ćeliju koja sadrži #REF! pogreška. Na traci formule odaberite #REF! i izbrišite pogrešku. Zatim ponovo unesite raspon formule.
• 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, a više nemate Book1.xlsx, vrijednosti na koje se poziva u toj radnoj knjizi ostaju dostupne. No ako uredite i spremite formulu koja se poziva na tu radnu knjigu, Excel prikazuje dijaloški okvir Ažuriranje vrijednosti i traži da unesete naziv datoteke. Odaberite Odustani, a zatim provjerite jesu li podaci izgubljeni pri zamjeni formula koje se pozivaju na radnu knjigu koja ne postoji s rezultatima formula.

Jeste li kopirali i zalijepili ćelije povezane s formulom u proračunskoj tablici?

Ponekad, kada kopirate sadržaj ćelije, želite zalijepiti samo vrijednost, ne i formulu prikazanu u traci formule.

Primjerice, možda želite kopirati vrijednost formule u ćeliju na drugom radnom listu. Ili ž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 odaberite Kopiraj .
   
   Tipkovni prečac: pritisnite CTRL + C.

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

   Savjet

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

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

Ako je riječ o ugniježđenoj formuli, izračunajte formulu korak po korak

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. Odabir formula zaizračun formule>.
   

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

4. Ako je podcrtani dio formule referenca na drugu formulu, odaberite Uđi za prikaz druge formule u okviru Analiza. Odaberite Izađi za povratak 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 analizu formule neće vam nužno 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.

   Napomena

   • Neki dijelovi funkcija IF i CHOOSE neće se analizirati i u okviru za izračun mogla bi se pojaviti pogreška #N/D.
   • Prazna reference prikazuju se kao nula (0) u okviru Analiza.
   • Neke se funkcije ponovo izračunavaju svaki put kada se radni list promijeni. 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ć?

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

Dodatne informacije

Pregled formula u programu Excel

Pomoć & učenje za Excel