Zaznavanje napak v formulah v Excelu

S formulami lahko včasih poleg nepredvidenih rezultatov dobimo tudi napačne vrednosti. V nadaljevanju so navedena nekatera orodja, s katerimi lahko poiščete in raziščete vzroke za te napake in določite rešitve.

Naučite se vnesti preprosto formulo

Formule so enačbe, ki izračunajo vrednosti na vašem delovnem listu. Formula se začne z enačajem (=). Na primer, ta formula prišteje številu 1 število 3.

=3+1

Formula lahko vsebuje tudi enega ali vse od teh elementov: funkcije, sklici, operatorji in konstante.

Deli formule

1. Funkcije: vključene v Excel, funkcije so zasnovane formule, ki izvajajo posebne izračune. S funkcijo PI() dobite na primer vrednost za število pi: 3,142...

2. Sklici: se sklicujejo na posamezne celice ali obsege celic. A2 vrne vrednost v celici A2.

3. Konstante: številke ali besedilne vrednosti, ki so vnesene neposredno v formulo, na primer 2.

4. Operatorji: Operator ^ (strešica) da število na potenco, operator * (zvezdica) pa je namenjen množenju. Z operatorjema + in – seštevate in odštevate vrednosti, z operatorjem / pa jih delite.

   Opomba

   Nekatere funkcije zahtevajo elemente, ki se imenujejo argumenti. Argumenti so vrednosti, ki jih nekatere funkcije uporabijo za opravljanje svojih izračunov. Po potrebi se argumenti postavijo v oklepaje funkcije (). Funkcija PI ne zahteva nobenih argumentov, zato je prazna. Nekatere funkcije zahtevajo enega ali več argumentov in lahko pustijo prostor za dodatne argumente. Argumente morate ločiti z vejico ali podpičjem (;), odvisno od nastavitev mesta.

Funkcija SUM na primer zahteva le en argument, a lahko sprejme skupno 255 argumentov.

=SUM(A1:A10) je primer enega argumenta.

=SUM(A1:A10, C1:C10) je primer več argumentov.

Odpravljanje pogostih napak pri vnosu formul

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 vneseno besedilo ali 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, Excel prikaže datum 2–november (ob predpostavki, da je oblika celice splošna), namesto da bi delil 11 z 2.
Ujemanje vseh oklepajev in zaklepajev	Prepričajte se, da so vsi oklepaji del ujemajočega se para (odpiranje in zapiranje). Ko uporabite funkcijo v formuli, je pomembno, da je vsak oklepaj v pravilnem položaju, da funkcija deluje pravilno. Na primer, formula =IF(B5<0),"Ni veljavno",B5*1,05) ne bo delovala, ker sta dva končna oklepaja in samo en odprt oklepaj, medtem ko bi moral biti vsak samo eden. 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 argumentov. Če kot argument uporabite napačno vrsto podatkov, lahko 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 formula sklicuje na vrednosti ali celice na drugih delovnih listih ali delovnih zvezkih, ime drugega delovnega zvezka ali delovnega lista pa vsebuje presledke ali neabecedne znake, morate njeno ime vključiti v enojne narekovaje ( ' ), na primer ='Četrtletni podatki'! D3 ali ='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 Excelu, lahko še vedno vključite sklic nanj v formulo. Navedete 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 celotna pot vsebuje presledke, kot v prejšnjem primeru, morate pot zapreti v enojne narekovaje (na začetku poti in za imenom delovnega lista, pred klicajem).
Vnos števil brez oblikovanja	Ne oblikujte števil, ko jih vnašate v formule. Če je na primer vrednost, ki jo želite vnesti, 1.000 $, v formulo vnesite 1000 . Če vejico vnesete kot del številke, jo Excel obravnava kot ločilo. Če želite, da so številke prikazane tako, da prikazujejo na tisoče ali milijone ločil ali simbole valut, oblikujte celice, ko vnesete številke. Če želite na primer dodati 3100 vrednosti v celici A3 in vnesete formulo =SUM(3.100;A3), Excel doda števili 3 in 100 ter nato doda to vsoto vrednosti iz celice A3, namesto da bi dodal 3100 v celico 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).

Odpravljanje pogostih težav v formulah

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, ali pa jo 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 prej prezrte napake, tako da se znova prikažejo.

Vklop ali izklop pravil preverjanja napak

1. V Excelu v sistemu Windows pojdite naFormulemožnosti>datoteke> ali
   za Excel v računalniku Mac izberite Excelov meni Preverjanje napak v nastavitvah >>.

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 povzročijo napako: 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 rešena na različne načine.

     Opomba

     Če vnesete vrednost napake neposredno v celico, bo shranjena kot vrednost napake, ne bo pa 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.
     • Vnesete formulo v izračunano celico stolpca in nato pritisnete kombinacijo tipk Ctrl + Z ali pa izberete »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 številnih primerih se formule, 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 vnese napako ob formulo =SUM(D2:D4), če je uporabljeno 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 morala formula sklicevati.

     

   • 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 ne želite celice 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, ne bo vključena v izračun in rezultat bo 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 za preverjanje veljavnosti za celico na zavihku »Podatki«, skupina »>>Podatkovna orodja«, »Preverjanje veljavnosti podatkov«.

Popravljanje pogostih napak v formuli eno za drugo

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 se pogovorno okno za preverjanje napak ne prikaže, izberite »Preverjanje napak med formulami>Preverjanje napak prinadzoru> formule«.

3. Če ste prej prezrli katero koli napako, lahko znova preverite napake tako, da naredite to: pojdite v razdelek»Možnosti>datoteke>« – formule. Za Excel v računalnikih Mac izberite »Preverjanje napak v nastavitvah>« v Excelovem meniju>.
   V razdelku Preverjanje napak izberite »Ponastavi prezrte napake>«.

   

   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 interaktivnih gumbov na desni strani pogovornega okna. Dejanja, ki so na voljo, se razlikujejo glede na vrsto napake.

5. Izberite Next (Naprej).

Popravljanje posameznih pogostih napak v formuli

1. Zraven celice izberite « in 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 pri vseh naslednjih pregledih.

   

Popravljanje napake # error value

Če formula ne more pravilno ovrednotiti rezultata, Excel prikaže napako z vrednostjo, 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 se prikaže ###, ker Excel ne more prikazati vseh znakov, to popravite.
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, ki je =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)
Popravljanje napake #IME?	Ta napaka je prikazana, če Excel ne prepozna besedila v formuli. Na primer, morda je ime obsega ali ime funkcije nepravilno č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 bo popravil.
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!. Če vstavite vejico med obsega C in E, se =SUM(C2:C3,E4:E6)#NULL!
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! napaka je verjetno zato, 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, ko sklic na celico ni veljaven. Morda ste na primer izbrisali celice, na katere so se sklicevale druge formule, ali pa ste prilepili celice, ki ste jih premaknili, na celice, na katere so se sklicevale 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 Razveljavi (Ctrl+Z), da razveljavite brisanje, znova zgradite formulo ali uporabite neprekinjen sklic na obseg, kot je ta: =SUM(A2:C2), ki bi se samodejno posodobil, ko bi bil stolpec B izbrisan.
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 =SUM(F2:F5) odpravil težavo.

Ogled formule in rezultata z orodno vrstico »Sledno okno«

Če celice niso vidne na delovnem listu, si lahko ogledate te celice in njihove formule v orodni vrstici Watch Window. 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 lahko premaknete ali zasidrate kot katero koli drugo orodno vrstico. 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 naUrejanje>domov>, izberite Poišči & Izberite (ali pa uporabite Ctrl+G ali Control+G v računalniku Mac)> Pojdi na posebne>formule.

   

2. Pojdite na Formule>, Nadzor> formul izberite Okno za ogled.

3. Izberite Dodaj uro.

   

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 Watch Window ni prikazana, pojdite na Nadzor>>formul formul in izberite Watch Window.

2. Izberite celice, ki jih želite odstraniti.
   Če želite izbrati več celic, pritisnite tipko CTRL in nato izberite celice.

3. Izberite Izbriši uro.

   

Postopno ocenjevanje ugnezdene formule

Včasih je težko razumeti, kako ugnezdena formula izračuna končni rezultat, saj obstaja več vmesnih izračunov in logičnih preskusov. Če pa uporabite formulo »Ovrednoti « v Excelu za Windows, si lahko ogledate različne dele ugnezdene formule, ki so ovrednoteni v vrstnem redu, v katerem je izračunana formula. Formulo =IF(AVERAGE(D2:D5)>50;SUM(E2:E5);0) je na primer lažje razumeti, če si ogledate te vmesne rezultate:

V pogovornem oknu »Ovrednotenje formule«	Opis
=IF(POVPREČJE(D2:D5)>50;VSOTA(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.
=ČE(40>; 50;VSOTA(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. V Excelu za Windows izberite celico, ki jo želite ovrednotiti. Mogoče je vrednotenje le ene celice hkrati.
2. Pojdite na FormuleNadzor >formule>Ovrednotite 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 Step In , da prikažete drugo formulo v polju Ocenjevanje . 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 Oceni, dokler ne bo ovrednoten vsak del formule.
5. Če si želite znova ogledati oceno, izberite Vnovični zagon.
6. Če želite končati ocenjevanje, izberite Zapri.

Potrebujete dodatno pomoč?

Kadar koli se lahko obrnete na strokovnjaka v Excelovi tehnični skupnosti ali pridobite podporo v skupnostih.

Glejte tudi

Prikazovanje razmerij med formulami in celicami

Kako se izogniti nedelujočim formulam