Kako se izogniti nedelujočim formulam v Excelu

Če Excel ne more razrešiti formule, ki jo poskušate ustvariti, se lahko prikaže sporočilo o napaki, kot je to:

Žal to pomeni, da Excel ne more razumeti, kaj poskušate narediti, zato boste morali posodobiti formulo ali se prepričati, da pravilno uporabljate funkcijo.

Vrnite se v celico s prekinjeno formulo, ki bo v načinu urejanja, in Excel bo označil mesto, kjer ima težavo. Če še vedno ne veste, kaj storiti od tam in želite začeti znova, lahko znova pritisnete ESC ali izberete gumb Prekliči v vnosni vrstici, ki vas bo premaknil iz načina urejanja.

Če se želite premakniti naprej, naslednji kontrolni seznam vsebuje korake za odpravljanje težav, ki vam bodo pomagali ugotoviti, kaj je morda šlo narobe. Če želite izvedeti več, izberite naslove.

Ali v formuli uporabljate pravilna ločila v seznamu?

Formule z več kot enim argumentom uporabljajo ločila v seznamu za ločevanje argumentov. Uporabljeno ločilo se lahko razlikuje glede na območne nastavitve operacijskega sistema in Excelove nastavitve. Najpogostejša ločila v seznamu so vejica »,« in podpičje ;«.

Formula ne bo delovala, če katera od njenih funkcij uporablja nepravilna ločila.

Če želite več informacij, glejte: Napake v formulah, ko ločilo seznama ni pravilno nastavljeno

Ali vidite napako funta (#) v Excelovih formulah?

Excel vrže različne napake v funtu (#), kot so #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? in #NULL! , da označite, da nekaj v formuli ne deluje pravilno. in #NULL!, s katerimi označi, da v vaši formuli nekaj ne deluje pravilno. do napake pride zaradi nepravilnega oblikovanja ali nepodprtih podatkovnih tipov v argumentih. Ali pa boste videli #REF! Ali pa boste videli napako #REF!, če se formula sklicuje na celice, ki so bile izbrisane ali zamenjane z drugimi podatki. Navodila za odpravljanje napak bodo drugačna za vsako napako.

Oglejte si katero koli od teh tem, ki ustrezajo napaki funta, ki jo vidite:

• Popravljanje napake #ŠTEV! napaka
• Popravek napake #VALUE! napaka
• Popravljanje napake #N/A
• Popravek napake #DIV/0! napaka
• Popravljanje napake #REF! napaka
• Popravljanje napake #IME?
• Odpravljanje napak #NULL! napaka

Popravljanje prekinjenih povezav v Excelovih formulah

Vsakič, ko odprete preglednico s formulami, ki se sklicujejo na vrednosti v drugih preglednicah, boste pozvani, da posodobite sklice ali pa jih pustite nespremenjene.

Excel prikaže zgornje pogovorno okno, da zagotovi, da formule v trenutni preglednici vedno kažejo na najbolj posodobljeno vrednost, če se je referenčna vrednost spremenila. Sklice lahko posodobite, lahko pa ta korak preskočite, če vrednosti ne želite posodobiti. Tudi če sklicev ne posodobite, lahko povezave na preglednici kadar koli posodobite ročno.

Po želji lahko tudi onemogočite prikazovanje pogovornega okna pri zagonu. Če želite to narediti, pojdite na Dodatne > možnosti > datoteke > Splošno in počistite polje Vprašaj za posodobitev samodejnih povezav.

Formula prikazuje sintakso namesto vrednosti v Excelu

Če formula ne prikaže vrednosti, upoštevajte ta navodila:

• Prepričajte se, da je Excel nastavljen tako, da prikazuje formule v preglednici. To naredite tako, da izberete zavihek Formule in v skupini Nadzor formul izberitePokaži formule.

  Namig

  Lahko tudi uporabite bližnjico na tipkovnici Ctrl + ` (tipka nad tabulatorko). Ko to naredite, se bodo stolpci samodejno razširili, da bodo prikazane formule, vendar ne skrbite, ko preklopite nazaj na običajni pogled, se bo velikost stolpcev spremenila.

• Če z zgornjim korakom še vedno ne odpravite težave, je celica morda oblikovana kot besedilo. Z desno miškino tipko kliknite celico in izberite Oblikuj celice > splošno (ali Ctrl + 1), nato pritisnite F2 > Enter , da spremenite obliko.

• Če imate stolpec z velikim obsegom celic, ki so oblikovane kot besedilo, lahko izberete obseg, uporabite obliko zapisa števila po svoji izbiri in pojdite na Besedilo podatkov > za zaključek stolpca>. Tako boste obliko zapisa uporabili za vse izbrane celice.

  

Omogočite samodejni izračun delovnega zvezka, če formula ne izračunava v Excelu

Ko formula ne izračuna, boste morali preveriti, ali je v Excelu omogočen samodejni izračun. Formule ne bodo izračunane, če je omogočen ročni izračun. Sledite tem korakom, da preverite Samodejni izračun.

1. Izberite zavihek Datoteka, izberite Možnostiin nato izberite kategorijo Formule.

2. V razdelku Možnosti izračuna v razdelku Izračun delovnega zvezka preverite, ali je izbrana možnost Samodejno .

   

Če želite več informacij o izračunih, glejte Spreminjanje preračunavanja, ponovitve ali natančnosti formule.

V formuli je en krožni sklic ali več krožnih sklicev

Do krožnega sklica pride, ko se formula sklicuje na celico, v kateri je. Rešitev je, da premaknete formulo v drugo celico ali spremenite sintakso formule tako, da se ta izogne krožnim sklicem. Vendar pa boste v nekaterih primerih morda potrebovali krožne sklice, saj lahko te poskrbijo za iteracijo funkcije, dokler ni izpolnjen določen pogoj. V takšnih primerih boste morali omogočiti možnost Odstrani ali dovoliti krožni sklic.

Če želite več informacij o krožnih sklicih, glejte Odstranjevanje ali omogočanje krožnega sklica.

Ali se vaša funkcija začne z znakom za »je enako« (=)?

Če se vnos ne začne z enačajem, to ni formula in se ne izračuna – pogosta napaka.

Ko vnesete nekaj takšnega kotSUM(A1:A10), Excel prikaže besedilni niz SUM(A1:A10) namesto rezultata formule. Če pa vnesete 11/2, Excel prikaže datum, na primer 2. november ali 11. 2 . 2009, namesto da deli 11 z 2.

Vnos vedno začnite z enačajem in se izognite takšnim nepričakovanim rezultatom. Vnesite na primer: =SUM(A1:A10) in =11/2.

Ali se začetni in zaključni oklepaj ujemata?

Ko v formuli uporabite funkcijo, je pomembno, mora vsak oklepaj imeti ustrezni zaklepaj, da bi funkcija delovala pravilno. Prepričajte se, ali vsi oklepaji nastopajo v parih. Formula =IF(B5<0),»Ni veljavno«,B5*1.05) na primer ne bo delovala, ker sta dva končna oklepaja, vendar le en začetni oklepaj. Pravilna formula bi izgledala takole: =IF(B5<0;"Ni veljavno";B5*1,05).

Ali so v sintaksi vsi zahtevani argumenti?

Excelove funkcije imajo argumente – vrednosti, ki jih morate zagotoviti, da funkcija deluje. Le nekatere funkcije (denimo PI ali TODAY) ne potrebujejo argumentov. Preverite sintakso formule, ki se prikaže, ko začnete vnašati funkcijo, in se prepričajte, da ima funkcija zahtevane argumente.

Funkcija UPPER na primer za argument sprejme le en niz besedila ali le en sklic na celico: =UPPER("živjo") ali =UPPER(C2)

Poleg tega nekatere funkcije, kot je SUM, zahtevajo le številske argumente, medtem ko druge funkcije, kot je REPLACE, zahtevajo besedilno vrednost za vsaj enega od svojih argumentov. Če uporabite napačno vrsto podatkov, lahko funkcije vrnejo nepričakovane rezultate ali prikažejo napako #VALUE!.

Če morate hitro poiskati sintakso določene funkcije, glejte seznam Excelovih funkcij (po kategoriji).

Ravnanje z neoblikovanimi števili v Excelovih formulah

V formule ne vnašajte števil, oblikovanih z znaki dolarja ($) ali decimalnimi ločili (,), ker znaki dolarja označujejo absolutne reference, vejice pa ločila argumentov. Namesto, da v formulo vnesete $1,000, vnesite 1000.

Če v argumentih uporabljate oblikovana števila, boste videli nepričakovane rezultate izračuna, vendar se lahko prikaže tudi napaka #NUM! . Če na primer vnesete formulo =ABS(-2,134), da poiščete absolutno vrednost -2134, Excel prikaže #NUM! napako, ker funkcija ABS sprejme le en argument in vidi -2 in 134 kot ločena argumenta.

Ali so sklicevane celice pravilne vrste podatkov?

Formula morda ne bo vrnila pričakovanih rezultatov, če podatkovnega tipa celice ni mogoče uporabiti v izračunih. Če na primer v celico, ki je oblikovana kot besedilo, vnesete preprosto formulo =2+3, Excel ne more izračunati podatkov, ki ste jih vnesli. Vse, kar vidite v celici, je =2+3. Če želite odpraviti to težavo, spremenite podatkovni tip celice iz Besedilo v Splošno , kot sledi:

1. Izberite celico.
2. Izberite Domov in izberite puščico, da razširite Število ali Skupino oblike zapisa števila(ali pritisnite Ctrl + 1). Nato izberite Splošno.
3. Pritisnite tipko F2, da celico preklopite v način urejanja, in nato pritisnite ENTER, da potrdite formulo.

Datum, ki ga vnesete v celico, ki uporablja podatkovni tip Število , je morda prikazan kot številska vrednost datuma in ne kot datum. Če želite to število prikazati kot datum, v galeriji Oblika zapisa števil izberite Datum.

Ali poskušate množiti brez simbola »*«?

V formulah je za množenje pogosto uporabljena simbol x, čeprav Excel namesto tega uporabi zvezdico (*). Če v formuli za množenje uporabite znak konstante, Excel prikaže sporočilo o napaki in lahko namesto vas popravi formulo tako, da zamenja znak x z zvezdico (*).

Če pa uporabite sklice na celice, bo Excel vrnil #NAME? napaka.

Ali besedilo v formulah ni obdano z narekovaji?

Če ustvarite formulo z besedilom, to besedilo dajte med narekovaje.

Formula ="Danes je " & TEXT(TODAY(),"dddd, mmmm dd") združi besedilo »Danes je« z rezultati funkcij TEXT in TODAY ter v celici prikaže rezultat na primer Danes je ponedeljek, 30. maj.

V formuli je na koncu besedne zveze »Danes je « presledek, kar pomeni, da bo vnesen tudi presledek med besedami »Danes je« in »ponedeljek, 30. maj«. Če besedila ne daste v oklepaje, lahko formula prikaže napako #NAME?.

Ali je v formulah več kot 64 funkcij?

V formuli lahko kombinirate (ali ugnezdite) do 64 ravni funkcij.

Na primer, formula =IF(SQRT(PI())<2,"Manj kot dva!","Več kot dva!") ima 3 ravni funkcij; funkcija PI je ugnezdena znotraj funkcije SQRT, ki je ugnezdena znotraj funkcije IF.

Ali so imena listov obdana z enojnimi narekovaji?

Ko sklic na vrednosti ali celice vnesete na drugem delovnem listu, v imenu tega lista pa je znak, ki ni črka ali številka (npr. presledek), vnesite ime med enojne narekovaje (').

Če na primer želite, da se vrednost iz celice D3 na delovnem listu z imenom »Četrtletni podatki« , vrne v delovnem zvezku, vnesite: ='Četrtletni podatki'!D3. Brez narekovajev okoli imena lista formula prikaže napako #NAME?.

Izberete lahko tudi vrednosti ali celice na drugem listu, da se nanje sklicujete v formuli. Excel nato samodejno doda narekovaje na začetek in konec imen listov.

Popravljanje poti zunanjega delovnega zvezka v Excelovih formulah

Ko vtipkate sklic na vrednosti ali celice v drugem delovnem zvezku, vključite ime delovnega zvezka med oglatimi oklepaji ([]), ki jim sledi ime delovnega lista, kjer so vrednosti ali celice.

Če se želite na primer sklicevati na celice od A1 do A8 na listu »Prodaja« v delovnem zvezku »Operacije Q2«, ki je odprt v Excelu, vnesite: =[Q2 Operations.xlsx]Sales! A1: A8. Brez oglatih oklepajev formula prikaže napako #REF!.

Če delovni zvezek ni odprt v Excelu, vnesite celotno pot do datoteke.

Na primer =ROWS('C:\Moji dokumenti\[Operacije v 2. četrtletju.xlsx]Prodaja'!A1:A8).

Ali številske vrednosti delite z ničlo?

Če celico delite s celico z vrednostjo nič (0) ali s celico brez vrednosti, bo namesto rezultata prikazana napaka #DIV/0.

Tej napaki se izognete tako, da jo naslovite neposredno in preverite, ali obstaja imenovalec. Uporabite lahko:

=IF(B1,A1/B1,0)

Kar pomeni IF(B1 obstaja, potem deli A1 z B1, drugače vrni 0).

Ali se formula sklicuje na izbrisane podatke?

Preden kar koli izbrišete, vedno preverite, ali se kakšna formula sklicuje na podatke v celicah, obsegih, določenih imenih, na delovnih listih ali v delovnih zvezkih. Preden odstranite sklicevane podatke, lahko nato nadomestite te formule z ustreznimi rezultati.

Če formul ne morete zamenjati z njihovimi rezultati, si oglejte te informacije o napakah in možnih rešitvah:

• Če se formula nanaša na celice, ki so bile izbrisane ali nadomeščene z drugimi podatki, in če vrne napako #REF!, izberite celico z možnostjo #REF! napaka #REF!. V vnosni vrstici izberite #REF! in jo izbrišite. Nato znova vnesite obseg za formulo.
• Če manjka določeno ime in formula, ki se sklicuje na to določeno ime, vrne napako #IME?, določite novo ime, ki se sklicuje na želeni obseg, ali spremenite formulo, da se sklicuje neposredno na obseg celic (na primer, A2:D8).
• Če delovni list manjka in formula, ki se sklicuje nanj, vrne #REF! Napaka, tega na žalost ni mogoče odpraviti – delovnega lista, ki je bil izbrisan, ni mogoče obnoviti.
• Če manjka delovni zvezek, formula, ki se sklicuje nanj, ostane nespremenjena, vse dokler ne posodobite formule.
  Če imate na primer formulo =[Book1.xlsx]Sheet1'!A1, nimate pa več Zvezka1.xlsx, bodo sklicevane vrednosti iz tega delovnega zvezka še vedno na voljo. Če pa uredite in shranite formulo, ki se sklicuje na ta delovni zvezek, Excel prikaže pogovorno okno Posodobi vrednosti in vas pozove k vnosu imena datoteke. Izberite Prekliči, nato pa se prepričajte, da se ti podatki ne izgubijo, tako da zamenjate formule, ki se sklicujejo na manjkajoči delovni zvezek, z rezultati formule.

Ali ste kopirali celice, povezane s formulo, in jih prilepili na preglednico?

Včasih, ko kopirate vsebino celice, želite prilepiti le vrednost in ne osnovne formule, ki je prikazana v vnosni vrstici.

Morda boste na primer želeli kopirati dobljeno vrednost formule v celico na drugem delovnem listu. Morda pa želite izbrisati vrednosti, ki ste jih uporabili v formuli, potem ko ste kopirali dobljene vrednosti v drugo celico na delovnem listu. Obe dejanji povzročita, da se v ciljni celici prikaže napaka z neveljavnim sklicem na celico (#REF!), ker se ni več mogoče sklicevati na celice, ki vsebujejo vrednosti, ki ste jih uporabili v formuli.

Tej napaki se lahko izognete tako, da dobljene vrednosti ali formule v ciljne celice prilepite brez formule.

1. Na delovnem listu izberite celice, ki vsebujejo nastale vrednosti formule, ki jih želite kopirati.

2. Na zavihku Osnovno v skupini Odložišče izberite Kopiraj .
   
   Bližnjica na tipkovnici: pritisnite CTRL + C.

3. Izberite zgornjo levo celico območja lepljenja.

   Namig

   Če želite premakniti ali kopirati izbor na drug delovni list ali delovni zvezek, izberite drug zavihek delovnega lista ali preklopite na drug delovni zvezek in nato izberite zgornjo levo celico območja lepljenja.

4. Na zavihku Osnovno v skupini Odložišče izberite Prilepi in nato izberite Prilepi vrednosti ali pritisnite Alt > E > S > V > Enter za Windows ali Option > Command > V > V > Enter v računalniku Mac.

Če imate ugnezdeno formulo, po korakih ocenite formulo

Če želite razumeti, kako zapletena ali vgnezdena formula izračuna končni rezultat, lahko to formulo ovrednotite.

1. Izberite formulo, ki jo želite ovrednotiti.

2. Izberite Formule Ovrednoti>formulo.
   

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

4. Če je podčrtani del formule sklic na drugo formulo, izberiteVstopi, da prikažete drugo formulo v polju Ocenjevanje. Izberite Izstopi, da se vrnete na prejšnjo celico in formulo.
   Gumb »Vstopi « ni na voljo, ko se sklic drugič prikaže v formuli ali če se formula sklicuje na celico v drugem delovnem zvezku.

5. Nadaljujte, dokler ne ovrednotite vseh delov formule.
   Orodje za ocenjevanje formule vam morda ne pove vedno, zakaj je formula poškodovana, vendar vam lahko pomaga ugotoviti, kje napaka je. To je lahko izredno priročno orodje v velikih formulah, kjer bi drugače le težko poiskali težavo.

   Opomba

   • Nekateri deli funkcij IF in CHOOSE ne bodo ocenjeni in v polju za vrednotenje se lahko prikaže napaka #N/A.
   • Prazni sklici so v polju Vrednotenje prikazani kot ničelne vrednosti (0).
   • Nekatere funkcije so znova izračunane vsakič, ko se delovni list spremeni. Te funkcije, vključno s funkcijami RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW, TODAY, in RANDBETWEEN, lahko povzročijo, da pogovorno okno Ovrednoti formulo prikaže rezultate, ki se razlikujejo od dejanskih rezultatov v celici na delovnem listu.

Potrebujete dodatno pomoč?

Kadar koli lahko zastavite vprašanje strokovnjaku v skupnosti tehničnih strokovnjakov za Excel ali pa pridobite podporo v skupnostih.

Glejte tudi

Pregled formul v Excelu

Pomoč & učenje za Excel