Pogreške formule u Excelu i rješenja

"Pogreške su prilika za poboljšanje". U bilo kojem zadatku ili sustavu dolazi do pogrešaka. Excel nije iznimka. Dok pokušavate učiniti nešto s formulom, naići ćete na razne vrste pogrešaka programa Excel. Ove pogreške mogu učiniti vašu formulu/nadzornu ploču/izvješća apsolutnim rasipanjem. A ako ne znate zašto je došlo do određene vrste pogreške, možda ćete morati potrošiti sate na njihovo rješavanje.

Tijekom rada na Excelu naišao sam na mnoge Excelove pogreške. Uz malo muke i google pretraživanja, zaobišao sam te pogreške. U ovom članku ću objasniti neke uobičajene i dosadne Excel pogreške koje se pojavljuju u Excelu. Razgovarat ćemo zašto se pojavljuju te pogreške i kako ih riješiti.

Što su pogreške formule Excel
Primjena formule koja rezultira pogreškama definiranim u Excelu (#NA, #VALUE, #NAME itd.) Naziva se pogreškama formule excela. Ove pogreške bilježi Excel i ispisuje na listovima. Razlozi za ove pogreške mogu biti, nedostupne vrijednosti, netočne vrste argumenata, podjela s 0 itd. Lako ih je uhvatiti i popraviti.

Excel ne hvata logičke pogreške i najteže ih je ispraviti. Nedosljednost u podacima, pogrešan unos podataka, ljudske pogreške itd. Uobičajeni su razlozi ovih grešaka. I oni se mogu popraviti, ali oduzimaju vrijeme i trud. Bolje je savršeno pripremiti svoje podatke prije nego što ih operirate.
Hvatanje grešaka u formuli programa Excel:

U Excelu postoje neke namjenske funkcije za hvatanje i rješavanje određene vrste pogrešaka (poput ISNA funkcije). No, funkcija ISERROR i funkcija IFERROR dvije su funkcije koje mogu uhvatiti bilo koju vrstu pogreške programa Excel (osim logičke).

Excel #NA pogreška
#NA pogreška javlja se u excelu kada vrijednost nije pronađena. To jednostavno znači NIJE DOSTUPNO. #NA pogreška često se javlja kod Excel VLOOKUP funkcije.

Na gornjoj slici dobivamo #NA grešku kada tražimo "Divya" u stupcu A. To je zato što "Divya nije na popisu.

Rješavanje greške #NA

Ako ste sigurni da vrijednost pretraživanja mora postojati na popisu za pretraživanje, prvo što trebate učiniti je provjeriti vrijednost pretraživanja. Provjerite jesu li vrijednosti za pretraživanje ispravno napisane. Ako ne, ispravite to.

Drugo, možete napraviti djelomično podudaranje s VLOOKUP -om ili bilo kojom funkcijom pretraživanja. Koristite ovo ako ste sigurni da se neki dijelovi teksta moraju podudarati.
Ako niste sigurni postoji li ta vrijednost ili ne, tada se može koristiti za provjeru postoji li vrijednost na popisu ili ne. Na gornjoj slici možemo reći da Divya nije na popisu.

Ako želite uhvatiti #NA pogrešku i ispisati ili učiniti nešto drugo umjesto ispisa #NA error, možete upotrijebiti Excel ISNA funkciju. ISNA funkcija vraća TRUE ako funkcija vrati #NA pogrešku. Pomoću ovoga možemo izbjeći #NA pogrešku. ISNA nevjerojatno radi s funkcijom VLOOKUP. Ovdje provjerite.

Pogreška programa Excel #VALUE
#VALUE se javlja kada dostavljeni argument nije podržanog tipa. Na primjer, ako pokušate dodati dva teksta koristeći aritmetički plus operator (+), dobit ćete pogrešku #VALUE. Ista će se stvar dogoditi ako pokušate dobiti godinu dana nevažećeg formata datuma pomoću funkcije GODINA.

Kako ispraviti grešku #VALUE?

Prvo potvrdite vrstu podataka na koju se pozivate. Ako vaša funkcija zahtijeva broj, svakako se pozovite na broj. Ako je broj oblikovan kao tekst, upotrijebite funkciju VALUE za pretvaranje u broj. Ako funkcija zahtijeva tekst (poput funkcije DATEVALUE), a pozivate se na broj ili vrstu datuma, pretvorite ih u tekst.

Ako očekujete da može doći do pogreške #VALUE i želite ih uhvatiti, tada možete upotrijebiti ISERR, ISERROR ili IFERROR da uhvatite i učinite nešto drugo.

Excel #REF pogreška
"Ref" u #REF označava referencu. Do ove pogreške dolazi kada se formula odnosi na mjesto koje ne postoji. To se događa kada izbrišemo ćelije iz raspona na koje se odnosi i formula.

Na donjem gif -u formula zbroja odnosi se na A2 i B2. Kad izbrišem A2, formula se pretvara u pogrešku #REF.

Riješite grešku Excel #REF:
Najbolje je biti oprezan prije brisanja ćelija u podacima. Pazite da se nijedna formula ne odnosi na tu ćeliju.

Ako već imate grešku #REF, tada unesite trag, a zatim ga izbrišite iz formule.

Na primjer, kada dobijete #REF pogrešku, vaša će formula izgledati ovako.

= A2+#REF!

Možete samo ukloniti #REF! Iz formule dobiti formulu bez grešaka. Ako to želite učiniti skupno, upotrijebite značajku pronađi i zamijeni. Pritisnite CTRL+H da biste otvorili opciju Pronađi i zamijeni. U okvir za traženje upišite #REF. Ostavite kutiju za zamjenu praznom. Pritisnite gumb Zamijeni sve.

Ako želite prilagoditi referencu na novu ćeliju, učinite to ručno i zamijenite #REF! Uz tu valjanu referencu.

Pogreška programa Excel #NAME
#NAME se pojavljuje u Excelu kada ne može identificirati tekst u formuli. Na primjer, ako ste pogrešno napisali naziv funkcije, Excel će prikazati pogrešku #NAME. Ako se formula odnosi na naziv koji ne postoji na listu, prikazat će grešku #NAME.

Na gornjoj slici ćelija B2 ima formulu = MOĆI (A2,2). POWERS nije važeća funkcija u Excelu, stoga vraća grešku #NAME.

U ćeliji B3 imamo = SUM (brojeve). SUM je valjana funkcija programa Excel, ali imenovani raspon "brojevi" ne postoji na listu. Stoga Excel vraća #NAME? Pogreška.

Kako izbjeći grešku #NAME u Excelu?

Da biste izbjegli pogrešku #NAME u Excelu, uvijek ispravno pišite nazive funkcija. Možete upotrijebiti prijedloge programa Excel kako biste bili sigurni da koristite valjanu funkciju. Kad god upisujemo znakove iza znaka jednakosti, Excel prikazuje funkcije i imenovane raspone na listu, počevši od tog znaka/znakova. Pomaknite se prema dolje do naziva funkcije ili naziva raspona na popisu prijedloga. Pritisnite karticu za korištenje te funkcije.

Excel #DIV/0! Pogreška
Kao što naziv govori, do ove pogreške dolazi kada formula rezultira dijeljenjem s nulom. Ova se pogreška može pojaviti i kada izbrišete neku vrijednost iz ćelije o kojoj ovisi formula podjele.

Kako riješiti #DIV/0! Pogreška.

To se može lako riješiti ako budete oprezni s podacima i provjerite hoće li formula rezultirati #DIV/0! pogreška. Evo primjera formule za to.

= IF (B2 = 0, A2, A2/B2)

Pogrešku DIV/0 imat ćemo samo ako je djelitelj 0 ili prazan. Stoga provjeravamo djelitelj (B2), ako je nula, ispišite A2, a zatim podijelite A2 s B2. Ovo će raditi i za prazne ćelije.

Pogreška u Excelu #NUM

Ova se pogreška javlja kada se broj ne može prikazati na ekranu. Razlog može biti to što je broj premalen ili prevelik da bi se mogao prikazati. Drugi razlog može biti to što se izračun ne može izvršiti s zadanim brojem.

= SQRT (ABS (A3))

Vratit će se 4. Ako želite dobiti negativnu vrijednost, upotrijebite predznak prije funkcije. Naravno, naravno, možete koristiti funkciju obrade pogrešaka.
O rješavanju greške #NUM imamo poseban članak. Ovdje možete provjeriti.

#NULL Pogreška u Excelu
Ovo je rijetka vrsta greške. #NULL pogreška uzrokovana pogrešnim upućivanjem na ćeliju. Na primjer, ako želite dati referencu raspona A2: A5 u funkciji SUM, ali greškom upisujete A2 A5. Ovo će generirati pogrešku #NULL. Kao što možete vidjeti na donjoj slici, formula vraća pogrešku #NULL.
Ako razmak zamijenite stupcem (:), pogreška #NULL će nestati i dobit ćete zbroj A2: A5. Ako razmak zamijenite zarezima (,), dobit ćete zbroj A2 i A5.

Kako riješiti grešku #NULL?

Kao što znamo da je pogreška #NULL uzrokovana pogreškom pri upisu. Da biste to izbjegli, pokušajte odabrati raspone pomoću kursora umjesto ručnog upisivanja.

Bit će trenutaka kada ćete morati unijeti adresu raspona s tipkovnice. Da biste to izbjegli, uvijek vodite računa o stupcu simbola povezivanja (:) ili zarezu (,)).

Ako imate pogrešku #NULL, provjerite reference. Najvjerojatnije ste propustili stupac (:) ili zarez (,) između dvije reference ćelije. Zamijenite ih odgovarajućim simbolom i spremni ste.
###### Greška u Excelu
Ponekad mislimo da je ova pogreška uzrokovana nedostatkom prostora za prikaz vrijednosti, ali to nije slučaj. U tom slučaju možete samo proširiti širinu ćelije da biste vidjeli vrijednost u ćeliji. Neću to nazvati greškom.
Zapravo, ova pogreška nastala je kada pokušamo prikazati negativnu vrijednost vremena (ne postoji negativno vrijeme). Na primjer, ako pokušamo oduzeti 1 od 12:21:00, vratit će se ######. U Excelu je prvi datum 1/1/1900 00:00. Ako pokušate oduzeti vrijeme prije toga, Excel će vam pokazati grešku ######. Što više širite širinu, više ćete dobiti #. Ja sam to razradio u ovom članku.

Kako izbjeći grešku u programu Excel ######?
Prije nego izvršite aritmetičke izračune u excelu s vrijednošću vremena imajte na umu ove stvari.

Minimalna vrijednost vremena je 1/1/1900 00:00. Prije ovoga u Excelu ne možete imati valjani datum
1 je jednako 24 sata (1 dan) u Excelu. Dok oduzimate sate, minute i sekunde, pretvorite ih u ekvivalentne vrijednosti. Na primjer, da biste oduzeli 1 sat od 12:21 popodne, morate od toga oduzeti 1/24.

Praćenje pogrešaka u Excelu
Sada znamo koje su uobičajene excel formule i zašto se pojavljuju. Također smo razgovarali o mogućem rješenju za svaku vrstu grešaka programa Excel.
Ponekad u izvješćima programa Excel dobivamo pogreške i nismo mogli znati odakle greška zapravo nastaje. Teško je riješiti takve greške. Za praćenje ovih pogrešaka Excel nudi funkciju praćenja pogrešaka na kartici formula.

Detaljno sam raspravljao o praćenju grešaka u excelu.

Rješavanje logičkih pogrešaka u formuli

Logičke pogreške teško je pronaći i riješiti. Excel ne prikazuje masažu ako imate logičku pogrešku u svojoj funkciji. Sve izgleda u redu. Ali samo vi znate da tu nešto nije u redu. Na primjer, dok dobivate postotak dijela cjeline, dio biste podijelili s ukupnim brojem (= (dio/ukupno)*100). Uvijek bi trebao biti jednak ili manji od 100%. Kad dobijete više od 100%, znate da nešto nije u redu.

Ovo je bila jednostavna logička pogreška. No ponekad vaši podaci dolaze iz nekoliko izvora, u tom slučaju postaje teško riješiti logičke probleme. Jedan od načina je da procijenite svoju formulu.

Na kartici formule dostupna je mogućnost procjene formule. Odaberite formulu i kliknite na nju. Pokazat će vam se svaki korak vašeg izračuna koji vodi do konačnog rezultata. Ovdje možete provjeriti gdje je došlo do problema ili gdje je izračun pogriješio.

Također možete pratiti svoje ovisne osobe i presedane da biste vidjeli o kojim referencama ovisi vaša formula, a koje o pertikularnoj ćeliji.

Pa da, dečki, ovo su bile neke uobičajene vrste pogrešaka s kojima se suočava svaki Excel korisnik. Naučili smo zašto dolazi do svake vrste pogreške i kako ih možemo izbjeći. Naučili smo i o namjenskoj obradi grešaka u excel funkciji. U ovom članku imamo veze na povezane stranice koje detaljno raspravljaju o problemu. Možete ih provjeriti. Ako imate neku vrstu greške koja vas živcira, navedite je u donjem odjeljku komentara. Rješenje ćete dobiti na odgovarajući način.