Napake v formuli Excel

Več formul napišete, več napak boste naleteli :)

Čeprav so frustrirajoče, so napake v formuli koristne, saj vam jasno povedo, da je nekaj narobe. To je veliko bolje kot ne vedeti. Običajno izvirajo najbolj katastrofalne napake v Excelu normalno videti formule, ki se tiho vračajo nepravilno rezultatov.

Ko naletite na napako formule, ne paničite. Ostanite mirni in metodično preiskujte, dokler ne odkrijete vzroka. Vprašajte se: 'Kaj mi sporoča ta napaka?' Poskusite s poskusi in napakami. Z več izkušnjami se boste lahko izognili številnim napakam in hitreje odpravili napake, ki se pojavijo.

# DIV / 0! , #NAME? , #N/A , #ON ONE , #VREDNOST! , #REF! , #NIČ , #### , #GAME! , #CALC!

Odpravljanje napak

Spodaj je osnovni postopek za odpravljanje napak. Ne pozabite, da se napake v formuli pogosto 'uvrstijo' skozi delovni list, ko ena napaka sproži drugo. Ko odkrijete in odpravite osrednjo težavo, se stvari pogosto hitro združijo.

1. Poiščite napake. Uporabite Pojdi na Posebno> Formula, kot je opisano spodaj.

2. Poiščite napako nazaj do njenega vira. Če je to težko, poskusite funkcija napake pri sledenju .

3. Ugotovite, kaj povzroča napako. Po potrebi formulo razdelite na dele.

kako pretvoriti formulo v vrednost

4. Odpravite napako pri viru.

Video: Primeri napak v formuli Excel

Video: Za odpravljanje napak formule uporabite F9

Odkrivanje vseh napak

Vse napake naenkrat najdete s Pojdi na posebno. Uporabi bližnjica na tipkovnici Control + G, nato kliknite gumb »Posebno«. Excel bo prikazal pogovorno okno s številnimi možnostmi, ki so prikazane spodaj. Če želite izbrati samo napake, izberite Formule + Napake in kliknite »V redu«:

Napake pri ujemanju

Napake pri ujemanju so način, kako 'ujeti' napake, da se sploh ne pojavijo. To je smiselno, če veste, da so verjetne določene napake in želite preprečiti pojavljanje sporočil o napakah. Obstajata dva osnovna pristopa:

2. Napako ulovite z NAPAKA ali NAPAKA . S tem pristopom opazujete napako in ponudite alternativo, ko odkrijete napako. Na tej strani je prikazan primer VLOOKUP .

3. Preprečite izračun, dokler niso na voljo zahtevane vrednosti. V tem primeru namesto da bi opazovali napako, poskusite preprečiti nastanek napake tako, da najprej preverite vrednosti. Na tej strani je prikazanih več primerov .

Kode napak v Excelu

Pri delu z Excelovimi formulami boste verjetno naleteli na 9 kod napak. Ta razdelek prikazuje primere vsake napake formule z informacijami in povezavami, kako odpraviti napako.

# DIV / 0! napaka

Kot že ime pove, #DIV/0! napaka se pojavi, ko formula poskuša deliti z ničlo ali z vrednostjo, ki je enaka nič. Morda boste videli #DIV/0! napaka, ko podatki še niso popolni. Celica na delovnem listu je na primer prazna, ker podatki niso bili vneseni ali še niso na voljo. Morda boste videli tudi napako deljenja z nič z POVPREČNO in POVPREČNO funkcije, če se merilo ne ujema z nobeno celico v obsegu.

Na spodnjem delovnem listu je na primer napaka DIV prikazana v celici D4, ker je C4 prazen. Prazne celice Excel oceni kot nič, B4 pa ni mogoče deliti z ničlo:

V mnogih primerih se praznim celicam ali manjkajočim vrednostim ni mogoče izogniti. Uporabite lahko Funkcija IFERRROR ujeti #DIV/0! in prikažete prijaznejše sporočilo, če želite.

Več: Kako popraviti #DIV/0! napaka

#NAME? napaka

Ime? Napaka pomeni, da Excel ne prepozna ničesar. To je lahko napačno napisano ime funkcije, a imenovani obseg ki ne obstaja ali pa je sklic na celico vnesen napačno. Na primer, na spodnjem zaslonu je VLOOKUP funkcija v F3 je napačno napisano 'VLOKUP'. VLOKUP ni veljavno ime, zato formula vrne #NAME ?.

Če želite popraviti #NAME? napako, morate najti težavo, nato popraviti črkovanje ali skladnjo. Za več podrobnosti in primerov, poglej to stran .

Video: Kako uporabiti F9 za odpravljanje napak v formuli

#N/A napaka

Napaka #N/A se prikaže, ko česa ni mogoče najti. Pove, da nekaj manjka ali je napačno napisano. To je lahko koda izdelka, ki še ni na voljo, napačno črkovano ime zaposlenega, barva, ki ne obstaja itd. Pogosto #N/A napake povzročijo dodatni presledki, napačne črke ali nepopolna iskalna tabela. Napake #N/A, na katere najpogosteje vpliva napaka, so klasične funkcije iskanja, vključno z VLOOKUP, HLOOKUP, LOOKUP in MATCH.

Na primer, na spodnjem zaslonu formula v F3 vrne #N/A, ker 'Bacon' ni v iskalni tabeli:

Če se vrednost v E3 spremeni v „kava“, „jajca“ itd., Bo VLOOKUP deloval normalno in povrnil stroške artikla.

Najboljši način za preprečevanje #N/A napak je, da preverite, ali so iskalne vrednosti in iskalne tabele pravilne in popolne. Po potrebi lahko napako #N/A ujamete z IFERROR in prikažete prijaznejše sporočilo ali pa sploh ne prikažete ničesar. '

Več informacij: Kako popraviti napako #N/A

#NUM! napaka

Številka #! napaka se pojavi, če je število preveliko ali majhno, ali kadar izračun ni mogoč. Če na primer poskusite izračunati kvadratni koren negativnega števila, se prikaže napaka #NUM:

Na zaslonu nad funkcijo SQRT, ki se uporablja za izračun kvadratnih korenskih števil v stolpcu B. Formula v C5 vrne #NUM! napaka, ker je vrednost v B5 negativna in ni mogoče izračunati kvadratnega korena negativnega števila.

Morda boste naleteli tudi na napako #NUM, če obrnete začetni in končni datum znotraj datoteke Funkcija DATEDIF .

Na splošno popravljanje številke #NUM! napaka je stvar prilagajanja vhodov, ki so potrebni za ponovni izračun.

Več informacij: Kako popraviti številko #NUM! napaka .

#VREDNOST! napaka

Vrednost! napaka se pojavi, ko vrednost ni pričakovana ali veljavna vrsta (tj. datum, čas, številka, besedilo itd.) To se lahko zgodi, če celica ostane prazna, ko je besedilna vrednost dana funkciji, ki pričakuje številsko vrednost, ali ko datume ovrednoti Excel kot besedilo.

Na spodnjem zaslonu na primer celica C3 vsebuje besedilo 'NA', formula v F2 pa vrne #VALUE! napaka.

Spodaj je MESEC funkcija ne morem izvleči mesečne vrednosti iz 'jabolka', saj 'jabolko' ni datum:

excel, kako narediti stolpec vedno viden

Opomba: morda boste videli tudi #VREDNOST! napaka, če ustvarite datoteko formula matrike in pozabite vnesti formulo s tipkama Control + Shift + Enter.

Če želite popraviti #VALUE! napake, morate izslediti težavno vrednost in vnesti pravo vrsto vrednosti. Za več podrobnosti in primerov, poglej to stran .

#REF! napaka

#REF! napaka je ena najpogostejših napak, ki jih boste videli v Excelovih formulah. Pojavi se, ko sklic postane neveljaven. V mnogih primerih je to zato, ker so bili listi, vrstice ali stolpci odstranjeni ali pa je bila formula z relativnimi referencami kopirana na novo mesto, kjer sklici niso veljavni.

Na spodnjem zaslonu je bila na primer formula v C8 kopirana v E4. Na tem novem mestu, ker je obseg C3: C7 relativno, postane neveljaven in formula vrne #REF !:

#REF! Napake je lahko nekoliko težavno popraviti, ker je prvotna referenca celice izginila za vedno. Če izbrišete vrstico ali stolpec in vidite #REF! Napake, dejanje morate takoj razveljaviti in najprej prilagoditi formule.

Več podrobnosti o #REF! napake.

#NIČ! napaka

#NULL! Napaka je v Excelu precej redka in je običajno posledica tipkarske napake, pri kateri je namesto vejice (,) ali dvopičja (:) med dvema sklicema na celice uporabljen presledek. Na primer, na zaslonu pod formulo v F3 vrne napako #NULL:

Tehnično je to zato, ker je presledek 'presek obsega' operater in #NULL! napaka poroča, da se obsega (C3 in C7) ne križata. V večini primerov lahko napako NULL popravite tako, da presledek po potrebi zamenjate z vejico ali dvopičjem.

Več: Kako popraviti #NULL! napaka

#### napaka

Čeprav tehnično ni napaka, boste morda videli tudi formulo, ki namesto običajnega rezultata prikaže niz razpršenih znakov (###). Na spodnjem zaslonu na primer formula v C3 datumu v stolpcu B doda 5 dni:

V tem primeru se pojavijo znaki razpršitve ali funta (###), ker so datumi v stolpcu C oblikovani v dolgi obliki in se ne prilegajo stolpcu. Če želite odpraviti to napako, samo razširite stolpec.

Opomba: Excel ne bo prikazal negativnih datumov. Če formula vrne negativno vrednost datuma, bo Excel prikazal #####.

Več: Kako popraviti napake #####

#GAME! napaka

Napaka #SPILL se pojavi, ko formula izpiše a obseg iger ki teče v celico, ki že vsebuje podatke. Na primer, na spodnjem zaslonu je UNIQUE funkcija je konfiguriran za ekstrahiranje seznama edinstvenih imen v razponu razlitja, ki se začne z D3. Ker D5 vsebuje 'jabolko', se operacija ustavi in ​​formula vrne #SPILL !.

Ko se 'jabolko' izbriše iz D5, bo formula delovala normalno in vrnila 'Joe', 'Sam' in 'Mary'.

Podrobnosti: Kako popraviti napake #SPILL

Video: Razlitje in območje razlitja

#CALC! napaka

Napaka #CALC se pojavi, ko formula naleti na napako izračuna z nizom. Na spodnjem zaslonu je na primer nastavljena funkcija FILTER za filtriranje izvornih podatkov v B5: D11. Vendar formula zahteva vse podatke v skupini 'jabolko', ki ne obstaja:

Če je formula prilagojena za filtriranje v skupini 'A', bo formula delovala normalno:

 
= FILTER (B5:D11,B5:B11='a')