Zaznavanje napak v formulah

V spodnji tabeli so povzete nekatere najpogostejše napake, ki jih lahko uporabnik naredi pri vnašanju formule. Tabela vsebuje tudi razlago, kako te napake popravite.

Preverite to:	Več informacij
Vsaka funkcija naj se začne z enačajem (=)	Če izpustite enačaj, je lahko vnos prikazan kot besedilo ali kot datum. Če na primer vnesete SUM(A1:A10),Excel prikaže besedilni niz SUM(A1:A10) in ne izvede izračuna. Če vnesete 11/2, Excelprikaže datum 2 . nov (ob predvidevanju, da je oblika celice Splošno), namesto da bi 11 delite z 2.
Ujemanje vseh oklepajev in zaklepajev	Prepričajte se, da so vsi oklepaji del ujemajočih se parov (odpiranje in zapiranje). Ko uporabite funkcijo v formuli, je pomembno, da je vsak oklepaj v pravilnem položaju, da funkcija deluje pravilno. Formula =IF(B5<0),"Not valid",B5*1.05) na primer ne bo delovala, ker sta dva zaključna oklepaja in le en odprt oklepaj, v vsakem pa mora biti le en. Formula bi morala biti videti tako: =IF(B5<0,"Ni veljavno",B5*1.05).
Uporaba dvopičja za označevanje obsega	Če se sklicujete na obseg celic, uporabite dvopičje (:) za ločevanje sklica na prvo celico v obsegu in sklica na zadnjo celico v obsegu. Na primer =SUM(A1:A5), ne =SUM(A1 A5), kar bi vrnilo #NULL! Napaka.
Vnos vseh zahtevanih argumentov	Nekatere funkcije zahtevajo argumente. Prepričajte se tudi, da niste vnesli preveč argumentov.
Vnos pravilne vrste argumentov	Nekatere funkcije, npr. Druge funkcije, kot je REPLACE, zahtevajo besedilno vrednost za vsaj enega od svojih argumentov. Če kot argument uporabite napačen podatkovni tip, Excel vrne nepričakovane rezultate ali prikaže napako.
Ugnezdenje največ 64 funkcij	Znotraj funkcije lahko vnesete ali ugnezdite največ 64 ravni funkcij.
Postavitev drugih imen listov v enojne narekovaje	Če se v formuli sklicujete na vrednosti ali celice v drugih delovnih listih ali delovnih zvezkih in je v imenu drugega delovnega zvezka ali delovnega lista presledek ali znak, ki ni črka, morate njegovo ime vstaviti med enojne narekovaje ( ' ), na primer ='Četrtletni podatki'!D3, or =‘123’!A1.
Če ime delovnega lista uporabljate v formuli, za njim vnesite klicaj (!).	Na primer, če želite, da se vrednost iz celice D3 delovnega lista, imenovanega »Četrtletni podatki«, vrne znotraj istega delovnega zvezka, uporabite to formulo: ='Četrtletni podatki'!D3.
Vključitev poti do zunanjih delovnih zvezkov	Preverite, ali vsak zunanji sklic vsebuje ime delovnega zvezka in pot do tega zvezka. Sklic na delovni zvezek vključuje ime delovnega zvezka in mora stati med oklepajema ([Ime delovnega zvezka.xlsx]). V sklicu mora biti tudi ime delovnega lista v delovnem zvezku. Če delovni zvezek, na katerega se želite sklicevati, ni odprt v Excel, lahko v formulo še vedno vključite sklic na delovni zvezek. Vnesete celotno pot do datoteke, na primer v tem primeru: =ROWS('C:\Moji dokumenti\[Q2 Operations.xlsx]Prodaja'! A1:A8). Ta formula vrne število vrstic v obsegu, ki vključuje celice od A1 do A8 v drugem delovnem zvezku (8). Opomba: Če so v celotno pot vključeni presledki, kot to velja v prejšnjem primeru, morate pot vnesti v enojne narekovaje (na začetku poti in za imenom delovnega lista, pred klicajem).
Vnos števil brez oblikovanja	Ko številke vnašate v formule, jih ne oblikujte. Če je na primer vrednost, ki jo želite vnesti, 1.000 $, vnesite v formulo 1000 . Če kot del številke vnesete vejico, jo Excel obravnava kot znak za ločilo. Če želite prikazati številke tako, da prikazujejo ločila za tisočice ali milijone ali simbole valut, oblikujte celice, ko vnesete števila. Če želite na primer vrednosti v celici A3 dodati 3100 in vnesete formulo =SUM(3,100,A3), Excel sešteje števili 3 in 100 in nato to vsoto prišteje vrednosti iz celice A3, namesto da bi vrednost 3100 prištela v A3, kar bi bilo =SUM(3100,A3). Če pa vnesete formulo =ABS(-2,134), Excel prikaže napako, ker funkcija ABS sprejme le en argument: =ABS(-2134).

Za preverjanje napak v formulah lahko izvajate določena pravila. Ta pravila ne morejo zagotoviti, da je delovni zvezek brez napak, lahko pa dovolj uspešno poiščejo pogoste napake. Vsako od teh pravil lahko posamično vklopite ali izklopite.

Napake lahko označite in popravite na dva načina: po eno napako hkrati (kot črkovalnik) ali pa takoj, ko se napake pojavijo na delovnem listu med vnašanjem podatkov.

Napako lahko odpravite z možnostmi, ki jih prikaže Excel, lahko pa napako prezrete tako, da izberete Prezri napako. Če prezrete napako v določeni celici, se napaka v tej celici ne prikaže pri nadaljnjih preverjanjih napak. Vendar pa lahko ponastavite vse predhodno prezrte napake, tako da se znova prikažejo.

1. Za Excel v sistemu Windows pojdite v razdelek > možnosti > formulah ali
   za Excel v računalniku Mac izberite meni Excel > Nastavitve > preverjanje napak.

2. V razdelku Preverjanje napak potrdite potrditveno polje Omogoči preverjanje napak v ozadju. Vsaka najdena napaka je označena s trikotnikom v zgornjem levem kotu celice.

   

3. Če želite spremeniti barvo trikotnika, ki označuje mesto napake, v polju Pokaži napake s to barvo izberite želeno barvo.

4. V razdelku Excelova pravila preverjanja napak potrdite ali počistite polja za katero koli od teh pravil:

   • Celice s formulami, ki privedejo do napake: Formula ne uporablja pričakovane sintakse, argumentov ali podatkovnih tipov. Vrednosti napak vključujejo #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, in #VALUE!. Vsaka od teh vrednosti napak ima različne vzroke in je odpravljena na različne načine.

     Opomba: Če vrednost napake vnesete neposredno v celico, je shranjena kot vrednost napake, vendar ni označena kot napaka. Če pa se formula v drugi celici sklicuje na to celico, formula vrne vrednost napake iz te celice.

   • Neskladna izračunana formula stolpca v tabelah: Izračunani stolpec lahko vključuje posamezne formule, ki se razlikujejo od glavne formule stolpca, kar ustvari izjemo. Izjeme izračunanega stolpca nastanejo, ko naredite nekaj od tega:

     • Vnesete podatke, ki niso formula, v celico izračunanega stolpca.

     • Vnesite formulo v izračunano celico stolpca, nato pa uporabite Ctrl +Z ali izberite Razveljavi v orodni vrstici za hitri dostop.

     • Vnesete novo formulo v izračunani stolpec, ki že vsebuje eno ali več izjem.

     • Kopirate podatke v izračunani stolpec, ki ne ustreza formuli izračunanega stolpca. Če je v kopiranih podatkih formula, boste s to formulo prepisali podatke v izračunanem stolpcu.

     • Premaknete ali izbrišete celico na drugem območju delovnega lista, na katerega se sklicuje ena od vrstic v izračunanem stolpcu.

   • Celice z leti, ki so predstavljene kot dvomestna števila: V celici je besedilni datum, ki se ga lahko narobe tolmači kot napačno stoletje, če ga uporabimo v formulah. Datum v formuli =YEAR("1/1/31") bi lahko na primer pomenil leto 1931 ali 2031. S tem pravilom preverite dvoumne besedilne datume.

   • Števila, ki so oblikovana kot besedilo ali je pred njimi opuščaj: V celici so števila, shranjena kot besedilo. To se po navadi zgodi, ko uvažate datume iz drugih virov. Števila, ki so shranjena kot besedilo, lahko povzročijo nepričakovane rezultate razvrščanja, zato je najbolje, da jih pretvorite v številke. Formula ‘=SUM(A1:A10) je obravnavana kot besedilo.

   • Formule, ki niso v skladu z drugimi formulami v območju: Formula ne ustreza vzorcu drugih bližnjih formul. V mnogih primerih se formule, ki so sosednje drugim formulam, razlikujejo le po uporabljenih sklicih. V tem primeru štirih sosednjih formul Excel prikaže napako ob formuli »=SUM(A10:C10)« v celici D4, ker se sosednje formule povišajo za eno vrstico, ena formula pa se poviša za 8 vrstic – Excel pričakuje formulo »=SUM(A4:C4).

     

     Če sklici, uporabljeni v formuli, niso skladni s sklici v sosednjih formulah, Excel prikaže napako.

   • Formule, ki izpuščajo celice v območju: Formula morda samodejno ne vključuje sklicev na podatke, ki ste jih vnesli med izvirni obseg podatkov in celico, v kateri je formula. To pravilo primerja sklic v formuli z dejanskim obsegom celic, ki je sosednji celici, v kateri je formula. Če so v sosednjih celicah dodatne vrednosti in niso prazne, Excel ob formuli prikaže napako.

     Excel na primer vstavi napako ob formulo =SUM(D2:D4), ko uporabite to pravilo, ker so celice D5, D6 in D7 sosednje celicam, na katere se sklicuje formula, in celici, v kateri je formula (D8), ter tistim celicam, v katerih so podatki, na katere bi se morali sklicevati v formuli.

     

   • Odklenjene celice, ki vsebujejo formule: Formula ni zaklenjena zaradi zaščite. Privzeto so vse celice na delovnem listu zaklenjene, tako da jih ni mogoče spreminjati, ko je delovni list zaščiten. To lahko pomaga preprečiti nenamerne napake, kot je nenamerno brisanje ali spreminjanje formul. Ta napaka pomeni, da je bila celica nastavljena za odklepanje, vendar list ni bil zaščiten. Prepričajte se, da celice ne želite zakleniti.

   • Formule, ki se sklicujejo na prazne celice: Formula s sklicem na prazno celico. Kot kaže primer, boste s tem dobili nepričakovane rezultate.

     Denimo, da želite izračunati povprečje števil v spodnjem stolpcu s celicami. Če je tretja celica prazna, ni vključena v izračun in rezultat je 22,75. Če je v tretji celici vrednost 0, bo rezultat 18,2.

     

   • Podatki, vneseni v tabelo, niso veljavni: V tabeli je prišlo do napake pri preverjanju veljavnosti podatkov. Preverite nastavitve preverjanja veljavnosti za celico, tako da kliknete zavihek Podatki > skupina Podatkovna orodja > Preverjanje veljavnosti podatkov.

1. Izberite delovni list, na katerem želite preveriti, ali je prišlo do napak.

2. Če je delovni list ročno izračunan, takoj pritisnite F9 za vnovični izračun.

   Če pogovorno okno Preverjanje napak ni prikazano, izberite Formule > nadzor formule in > preverjanje napak.

3. Če ste prej prezrli napake, lahko znova preverite te napake tako: pojdite v razdelek Možnosti > in>formule. Za Excel v računalniku Mac izberite meni Excel > Nastavitve > preverjanje napak.

   V razdelku Preverjanje napak izberite Ponastavi prezrte napake, >redu.

   

   Opomba: S ponastavitvijo prezrtih napak ponastavite vse napake na vseh listih v aktivnem delovnem zvezku.

   Namig: Morda si lahko pomagate tako, da premaknete pogovorno okno Preverjanje napak tik pod vnosno vrstico.

   

4. Izberite enega od gumbov za dejanja na desni strani pogovornega okna. Dejanja, ki so na voljo, se razlikujejo glede na vrsto napake.

5. Izberite Naprej.

1. Ob celici izberite Preverjanje in nato izberite želeno možnost. Ukazi, ki so na voljo, se razlikujejo glede na vrsto napake, pri čemer prvi vnos opisuje napako.

   Če izberete Prezri napako, bo napaka prezrta za vsako zaporedno preverjanje.

   

Če formula ne more pravilno ovrednotiti rezultata, Excel prikaže vrednost napake, na primer #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, in #VALUE!. Vsaka vrsta napake ima različne vzroke in različne rešitve.

V tej tabeli so povezave do člankov, v katerih so podrobno opisane te napake, in kratek opis za začetek dela.

Tema	Opis
Popravljanje napake #####	Excel prikaže to napako, če stolpec ni dovolj širok za prikaz vseh znakov v celici ali če celica vsebuje negativne vrednosti za datum ali čas. Na primer, formula, ki odšteje datum iz prihodnosti od datuma iz preteklosti, kot je =06/15/2008-07/01/2008, vrne rezultate z negativno vrednostjo datuma. Namig: Poskusite samodejno prilagoditi celico tako, da dvokliknete med glavami stolpcev. Če je ### prikazan, ker Excel ne more prikazati vseh znakov, to popravi.
Popravek napake #DIV/0! napaka	Excel prikaže to napako, ko je število deljeno ali z ničlo (0) ali s celico, ki ne vsebuje nobene vrednosti. Namig: Dodajte rutino za obravnavo napak, kot je prikazano v tem primeru: =IF(C2,B2/C2,0)
Popravljanje napake #N/V	Excel prikaže to napako, če vrednost ni na voljo za funkcijo ali formulo. Če uporabljate funkcijo VLOOKUP: ali ima element, ki ga želite poiskati, ujemanje v obsegu iskanja? Najpogosteje nima. Poskusite uporabiti IFERROR, da preprečite #N/A. V tem primeru lahko uporabite: =IFERROR(VLOOKUP(D2,$D$6:$E$8,2,TRUE),0)
Popravek napake #NAME? napaka	Ta napaka je prikazana, če Excel ne prepozna besedila v formuli. Na primer, ime obsega ali ime funkcije je lahko napačno črkovano. Opomba: Če uporabljate funkcijo, preverite, ali je ime funkcije pravilno črkovano. V tem primeru je funkcija SUM črkovana nepravilno. Odstranite »e« in Excel jo popravi.
Odpravljanje napak #NULL! napaka	Excel prikaže to napako, če določite presek dveh območij, ki se ne sekata (navzkrižno). Operator preseka je znak za presledek, ki ločuje sklice v formuli. Opomba: Preverite, ali so obsegi pravilno ločeni – območji C2:C3 in E4:E6 se ne sekata, zato vnos formule =SUM(C2:C3 E4:E6) vrne #NULL! napaka #REF!. Ko vnašate vejico med obsega C in E, popravite =SUM(C2:C3,E4:E6)
Popravljanje napake #ŠTEV! napaka	Excel prikaže to napako, če formula ali funkcija vsebuje neveljavne številske vrednosti. Ali uporabljate funkcijo, ki iterira, kot je IRR ali RATE? Če je odgovor pritrdilen, je do napake #NUM! najbrž prišlo, ker funkcija ne more najti rezultata. Postopke za odpravljanje napake boste našli v temi pomoči.
Popravljanje napake #REF! napaka	Excel prikaže to napako, če sklic na celico ni veljaven. Morda ste na primer izbrisali celice, na katere so se sklicele druge formule, ali pa ste prilepili celice, ki ste jih premaknili na vrh celic, na katere so se sklicele druge formule. Ali ste pomotoma izbrisali vrstico ali stolpec? V formuli =SUM(A2,B2,C2) smo izbrisali stolpec B in poglejte, kaj se je zgodilo. Uporabite ukaz Razveljavi (Ctrl + Z), da razveljavite brisanje, znova zgradite formulo ali pa uporabite sklic na nepretrgan obseg takole = SUM(A2:C2), ki se je samodejno posodobil, ko ste izbrisali stolpec B.
Popravek napake #VALUE! napaka	Excel lahko prikaže to napako, če vaša formula vključuje celice, ki vsebujejo različne vrste podatkov. Ali uporabljate matematične operatorje (+, -, *, /, ^) z različnimi vrstami podatkov? V tem primeru uporabite raje funkcijo. V tem primeru bi s funkcijo =SUM(F2:F5) odpravili težavo.

Če celice niso vidne na delovnem listu, si lahko te celice in njihove formule ogledate v orodni vrstici Sledno okno. Sledno okno omogoča priročno pregledovanje, nadzor ali potrjevanje izračunov formul in rezultatov na velikih delovnih listih. Če uporabljate sledno okno, vam ni treba vedno znova drseti ali se pomikati na različne dele delovnega lista.

To orodno vrstico je mogoče premakniti ali zasidrati, podobno kot vse druge orodne vrstice. Na primer, lahko jo zasidrate na dno okna. V orodni vrstici se hranijo podatki o teh lastnostih celice: 1) delovni zvezek, 2) list, 3) ime (če ima celica ustrezen imenski obseg), 4) naslov celice, 5) vrednost in 6) formula.

Dodajanje celic v sledno okno

1. Izberite celice, ki si jih želite ogledati.

   Če želite izbrati vse celice na delovnem listu s formulami, pojdite na Osnovno > Urejanje> izberite Najdi & Izberi (lahko pa uporabite Ctrl + G ali Control + G v računalniku Mac)> Pojdi na posebne >Formule.

   

2. Odprite razdelek Formule >nadzor formul in >Sledno okno.

3. Izberite Dodaj sledenje.

   

4. Potrdite, da ste izbrali vse celice, ki si jih želite ogledati, in izberite Dodaj.

   

5. Če želite spremeniti širino stolpca slednega okna, povlecite mejo na desni strani naslova stolpca.

6. Če želite prikazati celico, na katero se sklicuje vnos v orodni vrstici slednega okna, dvokliknite vnos.

   Opomba: Celice z zunanjimi sklici na druge delovne zvezke so prikazane v orodni vrstici slednega okna le, če so odprti drugi delovni zvezki.

Odstranjevanje celic iz slednega okna

1. Če orodna vrstica Sledno okno ni prikazana, pojdite v razdelek >nadzor formul in > Sledno okno.

2. Izberite celice, ki jih želite odstraniti.

   Če želite izbrati več celic, pritisnite CTRL in nato izberite celice.

3. Izberite Izbriši sledenje.

   

Včasih je težko razumeti, kako ugnezdena formula izračuna končni rezultat, saj obstaja več vmesnih izračunov in logičnih preskusov. Toda v pogovornem oknu Ovrednoti formulo lahko vidite različne dele ugnezdene formule, ovrednotene po vrstnem redu izračuna formule. Na primer, formulo =IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0) je lažje razumeti, če vidite te vmesne rezultate:

V pogovornem oknu »Ovrednotenje formule«	Opis
=IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0)	Na začetku je prikazana ugnezdena formula. Funkciji AVERAGE in SUM sta ugnezdeni v funkcijo IF. Obseg celic D2:D5 vsebuje vrednosti 55, 35, 45 in 25, zato znaša rezultat funkcije AVERAGE(D2:D5) 40.
=IF(40>50,SUM(E2:E5),0)	Obseg celic D2:D5 vsebuje vrednosti 55, 35, 45 in 25, zato znaša rezultat funkcije AVERAGE(D2:D5) 40.
=IF(False,SUM(E2:E5),0)	Ker število 40 ni večje od števila 50, je izraz v prvem argumentu funkcije IF (argument za logični_preskus) enak »False«. Funkcija IF vrne vrednost tretjega argumenta (argumenta value_if_false). Funkcija SUM ni ovrednotena, ker je drugi argument za funkcijo IF (argument value_if_true) in je vrnjena le, če je izraz »True«.

1. Izberite celico, ki jo želite ovrednotiti. Mogoče je vrednotenje le ene celice hkrati.

2. Pojdite v razdelek Formule > nadzor formule in >Ovrednoti formulo.

3. IzberiteOvrednoti, če želite pregledati vrednost podčrtanega sklica. Rezultat vrednotenja je prikazan v poševni pisavi.

   Če je podčrtani del formule sklic na drugo formulo, izberite Korak v , da prikažete drugo formulo v polju Vrednotenje . Izberite Izstopi, da se vrnete na prejšnjo celico in formulo.

   Gumb Vstopi ni na voljo, če se sklic drugič prikaže v formuli ali če se formula sklicuje na celico v drugem delovnem zvezku.

4. Nadaljujte z izbiranjem možnosti Ovrednoti, dokler ni vsak del formule ovrednoten.

5. Če si želite znova ogledati vrednotenje, izberite Ponovni zagon.

6. Če želite končati vrednotenje, izberite Zapri.