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.

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. 

   V Excel 2007 izberite gumb Microsoft Office, da >Excelove >formule.

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.
Popravite #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.
Popravek 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:

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. Izberite Ovrednoti, da pregledate 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 Izhod iz koraka , 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.