Izbjegavanje neispravnih formula u programu Excel

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 članku Pogreške u formulama kada razdjelnik popisa nije pravilno postavljen 

Excel prikazuje različite pogreške sa simbolom ljestvi (#) kao što su #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? i #NULL!, što upućuje 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.

Pregledom tema povezanih s pogreškom sa simbolom ljestvi vidjet ćete:

• 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 #NAZIV? pogreška

• Ispravljanje pogreške #NULL! pogreška

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 se provjerilo upućuju li formule u trenutačnoj 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, idite na Datoteka > Mogućnosti > Napredno > Općenito i poništite okvir Pitaj za ažuriranje automatskih veza.

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 radi prikaza formula, 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 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 željeni oblik broja i idite na Podaci > Tekst u stupac > Završi. Na taj će se način oblikovanje primijeniti na sve odabrane ćelije.

  

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 u radnoj knjizi provjerite je li odabrana mogućnost Automatski.

   

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

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, na primjer: =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. Na primjer, formula =IF(B5<0),"Not valid",B5*1.05) neće funkcionirati jer sadrži dvije zatvorene zagrade, a samo jednu otvorenu. Ispravna formula izgleda ovako: =IF(B5<0,"Nije valjano",B5*1,05).

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 funkcije kao što je SUM prihvaćaju samo brojčane argumente, dok je za druge kao što je 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)

U formulu nemojte unositi brojeve oblikovane znakom dolara ($) ili decimalne razdjelnike (,) jer znak dolara upućuje na apsolutne reference, a zarez 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 može se 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.

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

Č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ška.

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.

Na primjer, formula =IF(SQRT(PI())<2,"Less than two!","More than two!") sadrži tri razine funkcija; funkcija PI ugniježđena je unutar funkcije SQRT, koja je pak ugniježđena u funkciji 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 ć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.

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

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

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 se formule ne mogu 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 i ako vraća pogrešku #REF!, odaberite ćeliju s #REF! pogreška. Na traci formule odaberite #REF! i izbrišite pogrešku. Zatim ponovo unesite raspon formule.

• Ako nedostaje definirani naziv, a 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.

Ponekad, kada kopirate sadržaj ćelije, želite zalijepiti samo vrijednost, a ne i formulu koju prikazuje traka 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 radnje uzrokuju pogrešku reference na ćeliju koja nije valjana (#REF!) u odredišnoj ćeliji jer se na ćelije koje sadrže vrijednosti koje ste koristili u formuli više ne može pozivati.

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čje 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 > V > Enter za 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 > Analiza 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 Uđi nije dostupan drugi put kada se referenca pojavi u formuli – ili ako se formula poziva na ćeliju u 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.

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