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 može razumjeti što pokušavate učiniti, pa možda želite početi ispočetka.

Počnite odabirom 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 i dalje ne znate što učiniti od tamo i želite početi ispočetka, možete ponovno pritisnuti Esc ili na traku formule odabrati gumb Odustani , što će vas izvući iz načina za uređivanje.

Slika gumba Odustani na traci s formulama

Ako želite nastaviti dalje, sljedeći kontrolni popis omogućuje korake za otklanjanje poteškoća koje će vam pomoći da otkrijete što je pošlo po zlu.

Napomena: Ako koristiteOffice za web, možda nećete vidjeti iste pogreške ili se rješenja možda neće primijeniti.

Excel baca razne pogreške od Pound (#) kao što su #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? i #NULL!, da bi se ukazivalo na to da nešto u formuli ne funkcionira ispravno. i #NULL!, koje upućuju na to da nešto u formuli ne funkcionira kako treba. Pogreška je uzrokovana pogrešnim oblikovanjem ili nepodržanim vrstama podataka u argumentima. Ili ćete vidjeti #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

Pogledajte neku od sljedećih tema koje odgovaraju pogrešci funte koja se prikazuje:

Svaki put kada otvorite proračunsku tablicu koja sadrži formule koje se odnose na vrijednosti u drugim proračunskim tablicama, od vas će se zatražiti da ažurirate reference ili ih ostavite kao što je.

Dijaloški okvir Neispravne reference u programu Excel

Excel prikazuje gore navedeni dijaloški okvir da biste bili sigurni da formule u tekućoj proračunskoj tablici uvijek upućuju na najažuriranu vrijednost u slučaju promjene vrijednosti referenca. 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ćnost datoteka > > napredni > općenito, a zatim poništite okvir traži ažuriranje automatskih veza .

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

Važno: Ako vam je ovo prvi put da radite s prekinutih veza u formulama, ako vam je potreban podsjetnik na rješavanje prekinutih veza ili ako ne znate želite li ažurirati reference, pročitajte članak kontrola prilikom ažuriranja vanjskih referenci (veza).

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

  • Provjerite je li Excel postavljen tako da prikazuje formule u proračunskoj tablici. Da biste to učinili, odaberite karticu formule , a zatim u grupi Nadzor formula odaberite Prikaz formula.

    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 se, kada se vratite na uobičajeni prikaz, stupci će se promijeniti.

  • Ako korak iznad i dalje ne riješi problem, moguće je 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 stupac s velikim rasponom ćelija koje su oblikovane kao tekst, odaberite raspon, primijenite oblik broja po želji, a zatim prijeđite na podatke > 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čuna, morat ćete provjeriti je li u programu Excel omogućen automatski izračun. Formule se neće izračunati ako je omogućen ručni izračun. Slijedite ove korake da biste provjerili radi li se o automatskom izračunu.

  1. Odaberite karticu datoteka , odaberite mogućnosti, a zatim odaberite 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 formule, iteracije ili preciznosti.

Kružna referenca pojavljuje se kada se formula odnosi na ćeliju u kojoj se nalazi. Popravak je premještanje formule u drugu ćeliju ili promjena sintakse formule u neku koja izbjegava kružne reference. 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 takvim slučajevima morat ćete omogućiti Uklanjanje ili dopuštanje kružne referenca.

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

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 će prikazati datum, primjerice 2-Nov ili 11/02/2009, umjesto da se dijeli od 11 prema 2.

Da biste izbjegli te neočekivane rezultate, formulu uvijek započnite znakom jednakosti. Na primjer, upišite: =Sum (a1: A10) i = 11/2.

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. Primjerice, formula = if (b5<0), "nije valjano", B5 * 1,05) neće funkcionirati jer postoje dvije zatvorene zagrade, ali samo jedna otvorena zagrada. Ispravna formula izgleda ovako: =IF(B5<0,"Nije valjano",B5*1,05).

Funkcije programa Excel imaju argumente – vrijednosti koje morate pružiti da bi funkcija funkcionirala. Samo nekoliko funkcija (kao što je pi ili danas) ne raspravlja se. Provjerite sintaksu formule koja će se prikazati dok počnete tipkati u funkciji da biste provjerili ima li funkcija obavezne argumente.

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

Napomena: Prikazat će se argumenti funkcije navedeni na alatnoj traci za referencu na plutajućoj funkciji ispod formule dok je upisujete.

Snimka zaslona s prikazanom alatnom trakom s referencama za funkciju

Neke funkcije, kao što je Sum, zahtijevaju samo numeričke argumente, dok druge funkcije, kao što je zamjena, zahtijevaju tekstnu vrijednost za najmanje jedan argument. Ako koristite pogrešnu vrstu podataka, funkcije mogu vratiti neočekivane rezultate ili prikazati #VALUE! pogreška.

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

Ne unosite brojeve oblikovane uz novčanice od dolar ($) ili decimalni razdjelnici (,) u formulama jer znakovi dolara ukazuju na apsolutne reference i zareze koji su razdjelnici argumenta. Umjesto $1,000, u formulu unesite 1000.

Ako u argumentima koristite oblikovane brojeve, prikazat će vam se neočekivani rezultati izračuna, no možda ćete vidjeti i #NUM! error. Ako, primjerice, unesete formulu = ABS (-2.134) da biste pronašli apsolutnu vrijednost-2134, Excel prikazuje #NUM! pogreška, budući da funkcija ABS prihvaća samo jedan argument, a on vidi-2 i 134 kao zasebne argumente.

Napomena: Rezultat formule možete oblikovati razdjelnikom tisućica i simbolom valute nakon što unesete formulu pomoću neoblikovanih brojeva (konstanti). Općenito nije dobra ideja stavljati konstante u formule jer ih je teško pronaći ako je potrebno ažurirati kasnije, a oni su više skloni tome da se neispravno upisuju. Mnogo je bolje da konstante postavite u ćelije, gdje se nalaze na otvorenom i jednostavno na koje se referencira.

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 to ispravili, promijenite vrstu podataka Tekst u Općenito na sljedeći način:

  1. Odaberite ćeliju.

  2. Odaberite Polazno , a zatim odaberite strelicu da biste proširili grupu ili oblikbroja ( 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.

Č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 *

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

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 3 razine funkcija; funkcija PI ugniježđena je unutar funkcije Sqrt, što je pak ugniježđeno 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, vratili vrijednost iz ćelije D3 na radnom listu s nazivom tromjesečni podaci u radnoj knjizi, upišite: = ' Tromjesečni podaci '! D3. Ako nema navodnika oko naziva lista, formula prikazuje #NAME? pogreška.

Možete i odabrati vrijednosti ili ćelije na drugom listu da biste ih upućivale u formulu. 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 najlakši je način otvaranja druge radne knjige, a zatim iz izvorne radne knjige upišite = i koristite Alt + Tab da biste se pomakli na drugu radnu knjigu. Odaberite bilo koju ćeliju na listu koji želite, a zatim Zatvori izvornu 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 s drugom ćelijom koja ima nulu (0) ili vrijednost bez rezultata u #DIV/0! pogreška.

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

Uvijek provjerite imate li formule koje se odnose na podatke u ćelijama, rasponima, definiranim nazivima, radnim listovima ili radnim knjigama prije brisanja bilo čega. 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, a ako vraća #REF! pogreškaOdaberite ćeliju s #REF! pogreška. Na traku formule odaberite #REF! i izbrišite ga. Zatim ponovno unesite raspon formule.

  • Ako nema određenog naziva, a formula koja se odnosi na taj naziv vraća #NAME? pogreška, Definirajte novi naziv koji se odnosi na željeni raspon ili promijenite formulu da bi se izravno upućivati na raspon ćelija (na primjer, 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.

    Ako je, primjerice, formula = [Book1.xlsx] A1 i više nemate Book1.xlsx, vrijednosti koje su na njih referencirane u toj radnoj knjizi ostaju dostupne. No ako uređujete i spremate formulu koja se odnosi na tu radnu knjigu, Excel prikazuje dijaloški okvir ažuriranje vrijednosti i traži od vas da unesete naziv datoteke. Odaberite Odustani, a zatim provjerite nije li taj podatak izgubljen tako da zamijenite formule koje se odnose na radnu knjigu koja nema pomoću rezultata formule.

Ponekad, kada kopirate sadržaj ćelije, želite zalijepiti samo vrijednost, a ne pozadinsku formulu koja se prikazuje u traka formule.

Primjerice, možda želite kopirati vrijednost formule u ćeliju na drugom radnom listu. Ili možda želite izbrisati vrijednosti koje ste koristili u formuli nakon kopiranja nastale vrijednosti u drugu ćeliju na radnom listu. Obje ove akcije uzrokuju pogrešku u referenci ćelije koja nije valjana (#REF!) da bi se prikazala u odredišnoj ćeliji, jer ćelije koje sadrže vrijednosti koje ste koristili u formuli više se ne mogu referencirati.

Tu pogrešku možete izbjeći lijepljenjem rezultata formula, bez formule, u odredišnim ćelijama.

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

  2. Na kartici Polazno u grupi međuspremnik odaberite Kopiraj Slika gumba .

    slika trake programa excel

    Tipkovni prečac: pritisnite CTRL + C.

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

    Savjet: Da biste premjestili ili kopirali odabrano na drugi radni list ili radnu knjigu, odaberite neku 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 Slika gumba , a zatim odaberite zalijepi vrijednostiili pritisnite Alt > E > S > V > enter za Windows ili mogućnost > naredbe > v > v > unesite 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. Odaberite formule > Vrednovanje formule.

    Grupa Nadzor formula na kartici Formule

  3. Odaberite Vrednovanje da biste ispitali vrijednost podcrtane reference. Rezultat izračuna se prikazuje u kurzivu.

    Dijaloški okvir Analiza formule

  4. Ako je podcrtano dio formule referenca na drugu formulu, odaberite korak u da biste prikazali drugu formulu u okviru procjena . Odaberite izađi da biste se vratili na prethodnu ćeliju i formulu.

    Gumb zakorači nije dostupan drugi put kada se referenca pojavi u formuli – ili ako se formula odnosi na ćeliju u drugoj radnoj knjizi.

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

    Alat za procjenu formule neće vam nužno reći zašto je formula slomljena, ali može pomoći ukazati na to gdje. 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.

    • Neke se funkcije ponovno izračunavaju prilikom svakog promjene radnog lista. 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 zajednici za odgovore.

Dodatne informacije

Pregled formula u programu Excel

Pronalaženje pogrešaka u formulama

Funkcije programa Excel (abecednim redoslijedom)

Funkcije programa Excel (po kategorijama)

Potrebna vam je dodatna pomoć?

Proširite svoje vještine korištenja sustava Office
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

×