Dobar dan prijatelji!

U ovom članku ćemo govoriti o tome šta su greške formuleexcel sa kojima se susrećemo u radu sa elektronskim Excel tabele. Više sam nego siguran da su svi vidjeli greške, ali manje znaju kako da ih se ispravno riješe. Ipak, ovo znanje je važno, jer će vas od toga osigurati uobičajene greške ili vam pomoći da se brzo i bez panike riješite ili ispravite primljeno greške formuleexcel.

Možete puno pričati o greškama u Excelu, ali hajde da pogledamo one najčešće, reći ću vam zašto i kako se dešavaju, kao i kako ih popraviti. greške formuleexcel, za ispravan prikaz podataka.

Pa, šta se zapravo dešava greške formuleexcel:

1. Greška #####. Ovo je jedno od najčešćih i najjednostavnijih greške u Excel formulama. To znači samo jednu stvar, da širina kolone nije dovoljno široka da u potpunosti prikaže vaše podatke. Liječenje ovog problema je vrlo jednostavno, pomjerite kursor miša preko granice kolone i sa pritisnutim lijevom tipkom povećavajte ćeliju dok se podaci ne počnu prikazivati ​​ili dvostruki klik na granici kolone će omogućiti najširu ćeliju u koloni.
2. Greška #NAME?. Ovo greška (#NAME?) javlja se u Excel formulama samo kada uređivač ne može prepoznati tekst u formuli (na primjer, greška u nazivu funkcije zbog greške u kucanju = SUM (A1: A4). Da biste ovo ispravili greške formuleexcel, morate ga pažljivo pročitati i ispraviti grešku (A1:A4).
3. Greška #VALUE!. Ovo greška (#VRIJEDNOST!) možete doživjeti kada u formuli postoji argument čija vrsta nije prikladna za vaše proračune. Na primjer, u vašoj matematici ili formuli tekstualna vrijednost=A1+B1+C1, gdje je C1 tekst. Rješenje problema je jednostavno, koristite formulu koja zanemaruje ćelije koje sadrže tekst ili jednostavno uklonite datu vrijednost iz ćelije C1.
4. Greška #CASE/0. Kao što se može vidjeti iz greške koja se dogodila u formuli, jednostavno ste pomnožili svoj argument brojem 0, a to se ne može učiniti na osnovu matematička pravila. Da biste ispravili ovu grešku, morate promijeniti broj tako da ne bude jednak 0 ili promijeniti formulu, na primjer, logičku, čime ćete izbjeći grešku. =IF(A2=0;””;A1/A2)
5. Greška #LINK!. Ovo je jedna od najčešćih i zbunjujućih grešaka u Excel funkcijama. Kada vidite ovu grešku, to znači da se formula odnosi na ćeliju koja više ne postoji. Ovo je posebno problematično kada radite s velikim količinama podataka u i veliki iznos. Kada ovako uredite svoje tabele greške formuleexcel ne biste se trebali bojati, vrlo ih je lako popraviti, samo trebate vratiti sve na svoje mjesto ili, ako je potrebno, ručno prepisati formulu, isključujući iz nje pogrešan argument.

Nadam se da je ovaj članak o tome šta su greške formuleexcel i ispravljajući ih, postalo vam je korisno i naučili ste nešto novo i zanimljivo za sebe.

Vidimo se u novim člancima!

„Zašto je svijet tako uređen da ljudi koji znaju živjeti za svoje zadovoljstvo nikada nemaju novca, a oni koji imaju novca nemaju pojma šta znači „zapaliti život“?
D.B. Pokaži

Unosim formulu, ali umjesto rezultata vidim tekst #N/A ili #####. Šta to znači?

To znači da je napravljena greška u izvornim podacima, samoj formuli, formatiranju ili u drugom slučaju. Poruka o grešci sadrži indikaciju gdje je tačno napravljena. U nastavku ćemo pogledati poruke o greškama i njihove najčešće uzroke:

Greška #####

Najčešće, ova greška znači da kolona nije dovoljno široka da prikaže sadržaj ćelije. Da biste riješili problem, dovoljno je povećati širinu stupca.

Drugi razlog za grešku je taj što ćelija sadrži datum ili vrijeme sa negativnim vrijednostima.

Greška #N/A

Ova greška se javlja ako funkcija ili formula sadrži referencu na vrijednost (ili samu vrijednost) koja ne postoji. Drugim riječima, obavezno polje nije popunjeno u funkciji ili formuli, ili postoji referenca na praznu ćeliju, dok vrijednost mora biti prisutna u ćeliji. Najčešći uzroci:

• Funkciji nedostaje jedan ili više potrebnih argumenata.
• Funkcije VLOOKUP, HLOOKUP, VIEW ili MATCH označavaju netačna vrijednost argument vrijednosti lookup_value.
• Funkcije VLOOKUP, HLOOKUP ili MATCH se koriste za pretraživanje nesortirane tablice.
• U izvornu tabelu unesena je vrijednost #N/A ili NA() umjesto podataka koji nedostaju.

Greška #DIV/0!

Greška #DIV/0! Označava da se deljenje sa nulom dešava tokom izračunavanja. Razlozi mogu biti sljedeći:

• Formula eksplicitno specificira dijeljenje nulom (na primjer, =10/0).
• U formuli ili funkciji, djelitelj je referenca na praznu ćeliju ili na ćeliju koja sadrži nulu.

Greška #BLANK!

Ova greška se javlja kada su dva raspona koji se preklapaju navedena u uvjetima funkcije, ali se oni zapravo ne sijeku.

Napomena: U nekim zadacima korisnik treba da izvrši radnje samo s onim ćelijama koje su istovremeno prisutne u dva raspona. Da biste to učinili, opsezi ćelija u funkciji nisu odvojeni točkom i zarezom, već razmakom (koji se naziva operator presjeka).

• Ako funkcija ne bi trebala specificirati preklapajuće opsege (na primjer, prilikom zbrajanja), vjerovatno je da je kao separator raspona korišten razmak, a ne tačka i zarez. Ili je korišten razmak prilikom specificiranja raspona umjesto dvotočke.
• Ako se preklapajući rasponi moraju specificirati u funkciji, vjerovatno je da rasponi zapravo nemaju zajedničke ćelije. Morate provjeriti date raspone u argumentima funkcije.

Greška #NUMBER!

Ova greška se javlja kada su netačne numeričke vrijednosti navedene u formuli ili funkciji. Na primjer:

• U funkciji koja uzima numerički argument, to je ili tekst ili referenca na ćeliju s nenumeričkom vrijednošću.
• Funkcija koja se ponavlja (izračun koji se ponavlja dok se ne ispuni specificirani uvjet, kao što je IRR ili RATE) ne može procijeniti rezultat (kao što je specificirani uvjet nije ispunjen).
• Rezultat izračuna je broj koji je ili prevelik ili premali da bi ga Excel mogao koristiti. Podsjećamo vas na to maksimalan broj u Excelu ne može premašiti 10307 i biti manje od minus 10307.

Ovakva poruka nam govori da je referenca ćelije nevažeća. Najčešće, to može ukazivati ​​na to da su ćelije na koje upućuje formula obrisane ili da su druge vrijednosti kopirane u te ćelije.

Greška #VALUE!

Ova greška se javlja kada se koristi pogrešan tip argumenta ili operanda. Na primjer, korisnik može napraviti sljedeće greške:

• Tekst se unosi u formulu umjesto broja ili logičke vrijednosti (TRUE ili FALSE).
• Nakon što otkucate ili uredite formulu niza, pritisnite ENTER umjesto CTRL+SHIFT+ENTER.
• Referenca, formula ili funkcija je specificirana kao niz.
• Raspon je specificiran kao uvjet funkcije koji zahtijeva jednu vrijednost.

Razumijem šta greška znači. Ali moja formula je vrlo velika/radi s puno podataka. Kako mogu pronaći mjesto gdje je napravljena greška?

Excel 2007 može vas odmah ukazati na grešku ili vam može pomoći da prođete kroz svoje proračune korak po korak kako biste pronašli grešku. Da biste to učinili, kliknite na ikonu koja se pojavila pored ćelije s porukom o grešci. Ako se formula sastoji od samo jedne funkcije ili je uzrok greške prilično jednostavan, kontekstni izbornik će sadržavati stavku Izvor greške. Nakon što kliknete na ovu stavku, Excel će vam automatski pokazati ćeliju s netačnim podacima:

Ako formula sadrži nekoliko funkcija, onda in kontekstni meni pojavljuje se opcija Prikaži korake izračunavanja. U prozoru koji se otvori možete vidjeti koji proračun uzrokuje grešku.

Imam grešku #NAME? i ne mogu da nađem razlog za to.

• Provjerite pravopis imena (ime) funkcije. Da biste to učinili, možete pozvati čarobnjaka za funkcije (grupa Biblioteka funkcija na kartici Formule) i odabrati željenu funkciju sa predložene liste.
• U formulu je unesen tekst koji nije priložen dvostruki navodnici. Recimo da je rezultat formule trebao biti tekst sa brojem - na primjer, Ukupno 200 rubalja. Kada u formulu unesete tekst Ukupno, ako tekst nije stavljen u dvostruke navodnike, Excel takve podatke tumači kao ime, što uzrokuje grešku.

Napomena: Ime je korisnički definirana riječ, fraza ili drugi skup znakova koji predstavlja ćeliju, raspon ćelija, formulu ili konstantu.

• Formula ili funkcija koristi ime koje nije definirano. Proveri to dato ime postoji. Na kartici Formule, u grupi Imenovane ćelije, izaberite Upravitelj imena i pogledajte da li je dato ime na listi. Ako naziv ne postoji, možete ga dodati odabirom naredbe Kreiraj ime.
• Došlo je do greške u pisanju imena. Da biste provjerili da li su imena u traci formule ispravna, pritisnite F3, kliknite na željeno ime, a zatim kliknite na OK.
• Formula ili funkcija koristi referencu na drugi radni list koji nije zatvoren u jednostrukim navodnicima. Ako naziv drugog radnog lista ili radne knjige sadrži znakove koji nisu abecedni ili razmak, tada ime mora biti ograđeno apostrofom (").

imam ogromna baza podaci o kupcima. Svakog dana u bazu podataka se dodaje kolona koja pokazuje iznos prihoda od svakog kupca. Ako kupac nije ništa kupio, #N/A se stavlja u ćeliju. Kako mogu sabrati kolonu ako postoje takvi podaci?

Najlakši način za rješavanje ovakvog problema je promjena principa popunjavanja baze podataka i nepopunjavanja ćelija za kupce koji nisu kupovali.

Ali ako se glavna tabela ne može promijeniti (od različitih razloga), možete pokušati riješiti ovaj problem pomoću funkcije BDSUMM.

Recimo da originalna tabela izgleda ovako:

Koristeći BSUM funkciju, možemo odabrati za sumiranje one ćelije koje ne sadrže vrijednost #N/A. BSUM funkcija ima sljedeću sintaksu:

BDSUMM(baza podataka, polje, uslovi), gdje

• database_database je raspon ćelija na koje treba djelovati.
• polje je kolona iz koje će se sabrati vrijednosti.
• uslovi su skup uslova koji moraju biti tačni za vrednost ćelije da bi se ta vrednost zbrojila.

To jest, u funkciji trebamo postaviti zbir prvog stupca naše tablice, pod uvjetom da vrijednost ćelije ove kolone nije jednaka #N/A. Da biste to uradili, prvo kreirajte uslov - na bilo kom pogodnom mestu na radnom listu napišite uslov #N/A i imenujte ovu ćeliju kao i kolonu baze podataka koja nam je potrebna.

BDSUMM(C2:C18;1;E2:E3) gdje

• C2:C18 - naš sto,
• E2:E3 je naš uslov.

Imajte na umu da opsezi baze podataka i uslovi moraju sadržavati zaglavlja kolona! U ovom slučaju, naslov uvjeta mora odgovarati naslovu kolone na kojoj se izvode proračuni!

Naš rezultat je prikazan ispod tabele:

Ako naša baza podataka sadrži greške različite vrste, dovoljno ih je dodati uslovima (i, shodno tome, proširiti raspon uslova u formuli) da biste dobili željeni rezultat.

Ova metoda se može koristiti i za funkcije COUNT, COUNT, DMAX, DMIN, DBPRODUCT i slično.

Nakon unosa ili ispravke formule, kao i prilikom promjene bilo koje vrijednosti funkcije, dešava se da se pojavi greška formule, a ne tražena vrijednost. Ukupno, uređivač tabela prepoznaje sedam glavnih tipova takvih netačnih proračuna. Kako izgledaju greške u Excelu i kako ih popraviti, analizirat ćemo u nastavku.

U nastavku ćemo predstaviti opis formula prikazanih na slici sa detaljne informacije za svaku grešku.

1. #POSAO!- “podijeli sa 0”, najčešće se javlja kada pokušavate podijeliti sa nulom. Odnosno, formula ugrađena u ćeliju, obavljajući funkciju podjele, naiđe na ćeliju u kojoj se nula vrijednost ili je "Empty". Da biste riješili problem, provjerite sve ćelije uključene u izračun i ispravite sve nevažeće vrijednosti. Druga akcija koja vodi do #DIV/O! - ovo je unos pogrešnih vrijednosti u neke funkcije, kao što je \u003d AVERAGE () ako je izračunavanje u rasponu vrijednosti 0. Isti rezultat će izazvati prazne ćelije kojima se pristupa formulom koja zahtijeva specifične podatke za izračunavanje.
2. #N / A- "nema podataka". Ovako Excel označava vrijednosti koje su nerazumljive za formulu (funkciju). Unošenje pogrešnih brojeva u funkciju će definitivno pokrenuti ovu grešku. Kada se pojavi, uvjerite se da su sve ćelije za unos ispravno popunjene, a posebno one na kojima svijetli isti natpis. Često se viđa prilikom upotrebe
3. #NAME? - "neispravan naziv", indikator netačnog naziva formule ili nekog njenog dijela. Problem nestaje ako provjerite i ispravite sva imena i nazive koji prate algoritam proračuna.
4. #PRAZNO!– „postoji prazna vrijednost u rasponu“, signal da su područja koja se ne preklapaju upisana negdje u proračunu ili je postavljena praznina između navedenih raspona. Prilično retka greška. Unos greške može izgledati ovako:

ZBIR (G10:G12 I8:J8)

Excel ne prepoznaje takve komande.
5. #BROJ!- grešku uzrokuje formula koja sadrži broj koji ne odgovara granicama naznačenog raspona.
6. #VEZA!- upozorava da su ćelije povezane sa ovom formulom nestale. Provjerite jesu li ćelije navedene u formuli najvjerovatnije obrisane.
7. #VRIJEDNOST!– Neispravno odabran tip argumenta da bi funkcija radila.

8. Bonus, greška ##### - širina ćelije nije dovoljna za prikaz cijelog broja

Osim toga, Excel izdaje upozorenje o nevažećoj formuli. Program će vam pokušati reći kako točno urediti interpunkciju (na primjer, zagrade). Ako predložena opcija ispunjava vaše zahtjeve, kliknite na "Da". Ako opis zahtijeva ručno podešavanje. Zatim odaberite "Ne" i sami preuredite zagrade.

Greške u Excelu. Korištenje funkcije ISERROR() za Excel 2003

Dobro pomaže da se eliminišu greške u Excel funkciji. Radi tako što pronalazi greške u ćelijama, ako pronađe grešku u formuli, vraća TRUE i obrnuto. U kombinaciji sa =IF(), omogućit će zamjenu vrijednosti ako se pronađe greška.

Radna formula: =IF(GREŠKA(izraz),greška,izraz).

IF(ISGREŠKA(A1/A2),"";A1/A2)

Objašnjenje: ako se pronađe greška tijekom izvršavanja A1/A2, prazan ("") će biti vraćen. Ako je sve prošlo kako treba (tj. ISGREŠKA (A1/A2) = FALSE), tada se izračunava A1/A2.

Greške u Excelu.Korištenje IFERROR() za Excel 2007 i novije verzije

Jedan od razloga zašto sam brzo prešao na Excel 2007 bio je IFERROR() (najviše glavni razlog- ovo )

Funkcija iferror sadrži mogućnosti obje funkcije - ISERROR() i IF(), ali je dostupna u novijim verzijama Excel-a, što je vrlo zgodno

Alat se aktivira na sljedeći način: =IFERROR(vrijednost; vrijednost u slučaju greške). Umjesto "vrijednosti" stavlja se računski izraz / referenca ćelije, a umjesto "vrijednost na grešci", šta treba vratiti ako dođe do nepreciznosti, na primjer, ako se tokom izračunavanja A1 / A2, #POSAO! tada će formula izgledati ovako:

ISGREŠKA(A1/A2;””)

» preuzmite ovdje

Liste i rasponi (5)
Makroi (VBA procedure) (63)
razno (39)
Greške i greške u Excelu (3)

Kako prikazati 0 umjesto greške u ćeliji sa formulom

Postoje situacije kada radna sveska Na listovima se kreiraju mnoge formule koje obavljaju različite zadatke. U isto vrijeme, formule su stvorene davno, možda čak i na vama. I formule vraćaju greške. Na primjer #DIV/0! (#DIV/0!) . Ova greška se javlja ako se podjela nulom dogodi unutar formule: = A1 / B1 , gdje je B1 nula ili prazan. Ali mogu postojati i druge greške (#N/A, #VALUE!, itd.). Možete izmijeniti formulu dodavanjem provjere greške:

=IF(ISERR(A1 / B1),0, A1 / B1)
argumenti:
=IF(EOSH(1 arg), 2 arg, 1 arg)
Ove formule će raditi u bilo kojoj verziji Excel-a. Istina, EOS funkcija neće obraditi grešku #N/A (#N/A). Za obradu #N/A na isti način, trebate koristiti funkciju ISERROR:
=IF(ISGREŠKA(A1 / B1), 0, A1 / B1)
=IF(ISGREŠKA(A1 / B1),0, A1 / B1)
Međutim, kasnije ću u tekstu koristiti EOSH (jer je kraći), a osim toga nije uvijek potrebno "ne vidjeti" #N/A greške.
Ali za verzije Excel-a 2007 i novije, može se primijeniti nešto optimiziranija funkcija ESLIOSHIBKA (AKO GREŠKA):
=IFGREŠKA(A1 / B1;0)
=IFGREŠKA(A1 / B1 ,0)
argumenti:
=IFERROR(1 arg; 2 arg)

1 argument: izraz za procjenu
2 argument: vrijednost ili izraz koji se vraća u ćeliju u slučaju greške u prvom argumentu.

Zašto je ESLIERROW bolji i zašto ga nazivam optimiziranijim? Analizirajmo prvu formulu detaljnije:
=IF(ISH(A1 / B1), 0, A1 / B1)
Ako računamo korak po korak, vidjet ćemo da je izraz A1 / B1 (tj. podjela) prvo izračunat. A ako je njegov rezultat greška, tada će EOSH vratiti TRUE (TRUE) , što će biti proslijeđeno IF (IF) . A onda će funkcija IF(IF) vratiti vrijednost iz drugog argumenta 0.
Ali ako rezultat nije pogrešan i ISERR vraća FALSE, tada će funkcija ponovo procijeniti prethodno izračunati izraz: A1 / B1
Uz gornju formulu, to ne igra posebnu ulogu. Ali ako se formula kao što je VLOOKUP koristi sa pogledom na nekoliko hiljada redova, tada dvostruko izračunavanje može značajno povećati vrijeme potrebno za ponovno izračunavanje formula.
Funkcija IFERROR procjenjuje izraz jednom, pamti njegov rezultat, a ako je pogrešan, vraća ono što je napisano kao drugi argument. Ako nema greške, onda vraća pohranjeni rezultat evaluacije izraza iz prvog argumenta. One. stvarni proračun se dešava jednom, što praktično neće uticati na brzinu ukupnog ponovnog izračunavanja formula.
Stoga, ako imate Excel 2007 i noviju verziju i datoteka se neće koristiti u više rane verzije- onda ima smisla koristiti upravo ESLIOSHIBKA (IFERROR).

Zašto uopće ispravljati formule s greškama? Obično se radi za estetskiji prikaz podataka u izvještajima, posebno ako se izvještaji zatim šalju menadžmentu.

Dakle, na listu postoje takve formule čije se greške moraju obraditi. Ako postoje jedna ili dvije takve formule za korekciju (da, čak 10-15), onda gotovo da nema problema za ručno zamijeniti. Ali ako postoji nekoliko desetina ili čak stotina takvih formula, problem postaje gotovo univerzalan po obimu :-). Međutim, proces se može pojednostaviti pisanjem relativno jednostavan kod Visual basic za primjenu.
Za sve verzije Excela:

Sub IfIsErrNull() Const sToReturnVal As String = "0" , vbInformation, "www.site" Exit Sub End If For Every rc In rr Ako rc.HasFormula Onda s = rc.Formula s = Mid(s, 2) ss = " =" & "IF(ISERR(" & s & ")," & sToReturnVal & "," & s & ")" Ako je ostalo(i, 9)<>"IF(ISERR(" Onda Ako rc.HasArray Onda rc.FormulaArray = ss Else rc.Formula = ss Kraj Ako Ako Err.Number Onda ss = rc.Adresa rc.Odaberi Izlaz Za Kraj Ako Kraj Ako Kraj Ako Sljedeće rc Ako Err .Broj Zatim MsgBox "Formule obrađene"

Sub IfIsErrNull() Const sToReturnVal As String = "0" "ako je potrebno vratiti prazno umjesto nule "Const sToReturnVal As String = """""" Dim rr As Range, rc As Range Dim s Kao string, ss As String on Error Nastavi sljedeći set rr = Intersect(Selection, ActiveSheet.UsedRange) Ako rr nije ništa onda MsgBox "Odabrani opseg ne sadrži podatke", vbInformation, "www..HasFormula Tada s = rc.Formula s = Mid(s, 2 ) ss = " =" & "IF(ISERR(" & s & ")," & sToReturnVal & "," & s & ")" Ako je ostalo(i, 9)<>"IF(ISERR(" Onda Ako rc.HasArray Onda rc.FormulaArray = ss Else rc.Formula = ss Kraj Ako Ako Err.Number Onda ss = rc.Adresa rc.Odaberi Izlaz Za Kraj Ako Kraj Ako Kraj Ako Sljedeće rc Ako Err .Number Then MsgBox "Nije moguće pretvoriti formulu u ćeliju: " & ss & vbNewLine & _ Err.Description, vbInformation, "www..site" Kraj ako Kraj pod

Za verzije 2007 i novije

Sub IfErrorNull() Const sToReturnVal As String = "0" "ako je potrebno, vrati prazno umjesto nule "Const sToReturnVal As String = """""" Dim rr As Range, rc As Range Dim s Kao String , ss Kao String Pri grešci Nastavi sljedeći Set rr = Intersect(Selection, ActiveSheet.UsedRange) Ako rr nije ništa onda MsgBox "Odabrani raspon ne sadrži podatke", vbInformation, "www.site" Exit Sub End If Za svaki rc In rr Ako rc.HasFormula Onda s = rc.Formula s = Mid(s, 2) ss = "=" & "IFERROR(" & s & ", " & sToReturnVal & ")" Ako je preostalo(e, 8)<>"IFERROR(" Onda Ako rc.HasArray Onda rc.FormulaArray = ss Inače rc.Formula = ss Kraj Ako Ako Err.Number Onda ss = rc.Adresa rc.Odaberi Izlaz Za Kraj Ako Kraj Ako Kraj ako Sljedeće rc Ako Err.Number Zatim MsgBox "Nije moguće pretvoriti formulu u ćeliju: "& ss & vbNewLine & _ Err.Description, vbInformation, "www.site" Else MsgBox "Formule obrađene", vbInformation, "www.site" Kraj ako Kraj pod

Sub IfErrorNull() Const sToReturnVal As String = "0" "ako je potrebno vratiti prazno umjesto nule "Const sToReturnVal As String = """""" Dim rr As Range, rc As Range Dim s Kao string, ss As String on Error Nastavi sljedeći set rr = Intersect(Selection, ActiveSheet.UsedRange) Ako rr nije ništa onda MsgBox "Odabrani opseg ne sadrži podatke", vbInformation, "www..HasFormula Tada s = rc.Formula s = Mid(s, 2 ) ss = " =" & "IFGREŠKA(" & s & "," & sToReturnVal & ")" Ako je ostalo(e, 8)<>"IFERROR(" Onda Ako rc.HasArray Onda rc.FormulaArray = ss Inače rc.Formula = ss Kraj Ako Ako Err.Number Onda ss = rc.Adresa rc.Odaberi Izlaz Za Kraj Ako Kraj Ako Kraj ako Sljedeće rc Ako Err.Number Zatim MsgBox "Nije moguće pretvoriti formulu u ćeliju: " & ss & vbNewLine & _ Err.Description, vbInformation, "www..site" Kraj ako Kraj pod

Kako radi
Ako niste upoznati sa makroima, onda je bolje prvo pročitati kako ih kreirati i pozvati: Šta je makro i gdje ga pronaći? , jer može se desiti da sve uradite kako treba, ali zaboravite da omogućite makroe i ništa neće raditi.

Kopirajte gornji kod, idite na VBA editor ( alt+F11), kreirajte standardni modul ( Insert -modul) i samo zalijepite ovaj kod u njega. Idi na onu koju želiš Excel radna sveska i izaberite sve ćelije čije formule treba konvertovati na način da u slučaju greške vrate nulu. Pritisnite alt+F8, odaberite kod IfIsErrNull(ili IfErrorNull, u zavisnosti od toga koji ste kopirali) i pritisnite Trči.
Funkcija rukovanja greškama bit će dodana svim formulama u odabranim ćelijama. Dati kodovi takođe uzimaju u obzir:
-ako je formula već koristila funkciju IFERROR ili IF(EOSH), onda se takva formula ne obrađuje;
-kod će ispravno obraditi i funkcije niza;
- možete odabrati ćelije koje nisu susjedne (putem Ctrl).
Šta je nedostatak: složene i dugačke formule niza mogu uzrokovati grešku koda zbog posebnosti ovih formula i njihove obrade iz VBA. U tom slučaju, kod će pisati o nemogućnosti nastavka rada i istaknuti problematičnu ćeliju. Stoga vam toplo preporučujem da izvršite zamjenu na kopijama datoteka.
Ako vrijednost greške treba zamijeniti praznom umjesto nulom, onda string

"Const sToReturnVal As String = """"""

Ukloni apostrof ( " )

Također je moguće dati kod pozovite pritiskom na dugme (Kako kreirati dugme za pozivanje makroa na listu) ili ga postaviti u dodatak (Kako kreirati sopstveni dodatak?), tako da ga možete pozvati iz bilo koje datoteke.

I mali dodatak: pokušajte promišljeno primijeniti kod. Ne ometa uvijek vraćanje greške. Na primjer, kada korištenje VLOOKUP-a ponekad je korisno vidjeti koje vrijednosti nisu pronađene.
Također želim napomenuti da je potrebno primijeniti na stvarno funkcionalne formule. Jer ako formula vraća #NAME!(#NAME!), onda to znači da je neki argument pogrešno napisan u formuli i to je greška u pisanju formule, a ne greška u rezultatu proračuna. Bolje je analizirati takve formule i pronaći grešku kako bi se izbjegle logičke greške u proračunima na listu.

Da li je članak pomogao? Podijelite link sa svojim prijateljima! Video lekcije

("Donja traka":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"desno","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1" ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"desno","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffect5delay0 textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:apsolute; top:0px; lijevo:0px; širina:100%; visina:100% ; boja pozadine: #333333; neprozirnost: 0,6; filter: a lpha(opacity=60);","titlecss":"display:block; pozicija:relativna; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; pozicija:relativna; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; boja:#fff; margin-top:8px;","buttoncss":"display:block; pozicija:relativna; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

Za pogrešne proračune, formule prikazuju nekoliko vrsta grešaka umjesto vrijednosti. Hajde da ih pogledamo praktični primjeri tokom rada formula koje su dale pogrešne rezultate proračuna.

Greške formule Excel prikazane u ćelijama

IN ovu lekciju Bit će opisane vrijednosti greške formule koje ćelije mogu sadržavati. Znajući značenje svakog koda (na primjer: #VRIJEDNOST!, #DIV/0!, #BROJ!, #N/A!, #NAME!, #PRAZNO!, #REFERENCA!) možete lako shvatiti kako pronaći grešku u formuli i eliminisati je.

Kako ukloniti #DIV/0 u Excelu

Kao što se vidi prilikom dijeljenja po ćeliji sa prazna vrijednost program to doživljava kao deljenje sa 0. Kao rezultat, daje vrednost: #DIV/0! Ovo se može provjeriti uz pomoć savjeta.

U drugima aritmetički proračuni(pomnoži, zbroj, oduzmi) prazna ćelija je također nula.



Rezultat pogrešnog izračuna je #BROJ!

Pogrešan broj: #NUMBER! je greška nemogućnosti izvršenja proračuna u formuli.

Nekoliko praktičnih primjera:

Greška: #NUMBER! nastaje kada numerička vrijednost prevelika ili premala. Isti način data greška može se dogoditi kada pokušavate uzeti korijen negativnog broja. Na primjer, =ROOT(-25).

U ćeliji A1 - također veliki broj(10^1000). Excel ne može raditi s ovim veliki brojevi.

Ćelija A2 ima isti problem sa velikim brojevima. Čini se da 1000 mali broj, ali vraćanje faktorijala rezultira numeričkom vrijednošću koja je prevelika da bi je Excel mogao rukovati.

U ćeliji A3 - Kvadratni korijen ne može biti od negativnog broja, ali prikazani program dati rezultat ista greška.

Kako ukloniti ND u Excelu

Vrijednost nije dostupna: #N/A! - znači da vrijednost nije dostupna za formulu:

Napisana formula u B1: =MATCH("Maxim", A1:A4) traži sadržaj teksta "Maxim" u rasponu ćelija A1:A4. Sadržaj pronađen u drugoj ćeliji A2. Prema tome, funkcija vraća rezultat 2. Druga formula traži tekstualni sadržaj "Andrey", tada opseg A1:A4 ne sadrži takve vrijednosti. Stoga funkcija vraća grešku #N/A (bez podataka).

Greška #NAME! u Excelu

Biti klasifikovan kao greška u pisanju funkcija. Nevažeći naziv: #NAME! - znači da Excel nije prepoznao tekst napisan u formuli (ime funkcije = SUM () mu je nepoznato, napisano je s greškom). Ovo je rezultat sintaksičke greške prilikom pisanja naziva funkcije. Na primjer:

Greška #BLANK! u Excelu

Prazan set: #EMPTY! su greške skupa operatora preseka. U Excelu postoji takva stvar kao što je presjek skupova. Prijavljuje se za brzi prijem podaci iz velikih tabela na zahtev presečne tačke vertikalnog i horizontalnog opsega ćelija. Ako se opsezi ne sijeku, program prikazuje pogrešnu vrijednost - #EMPTY! Operator skupa ukrštanja je jedan prostor. Odvaja vertikalne i horizontalne opsege navedene u argumentima funkcije.

U ovom slučaju, presjek raspona je ćelija C3 i funkcija prikazuje njenu vrijednost.

Dati argumenti u funkciji: =SUM(B4:D4 B2:B3) – ne formiraju raskrsnicu. Dakle, funkcija daje vrijednost s greškom - #EMPTY!

#VEZA! - Greška reference ćelije u Excelu

IN ovaj primjer došlo je do greške kada je formula pogrešno kopirana. Imamo 3 raspona ćelija: A1:A3, B1:B4, C1:C2.

Ispod prvog raspona, u ćeliju A4, unesite formulu za sumiranje: =SUM(A1:A3). A zatim kopirajte istu formulu pod drugi raspon, u ćeliju B5. Formula, kao i ranije, zbraja samo 3 ćelije B2:B4, zaobilazeći vrijednost prve B1.

Kada je ista formula kopirana pod trećim opsegom, funkcija je vratila grešku #REF! u ćeliji C3. Budući da iznad ćelije C3 mogu biti samo 2 ćelije, a ne 3 (kao što zahtijeva originalna formula).

Bilješka. IN ovaj slučaj najpogodnije je pritisnuti kombinaciju prečaca ispod svakog raspona prije nego što počnete da ulazite ALT tasteri+=. Zatim ubacite funkciju sumiranja i automatski odredite broj ćelija za zbrajanje.

Kako popraviti VALUE u Excelu

#VRIJEDNOST! - greška u značenju. Ako pokušamo da dodamo broj i riječ u Excelu, dobit ćemo #VRIJEDNOST! Zanimljiva je činjenica da ako pokušamo da dodamo dvije ćelije u kojima je vrijednost prve broj, a druge tekst pomoću funkcije =SUM(), onda neće doći do greške, a tekst će poprimiti vrijednost 0 prilikom izračunavanja. Na primjer:

Rešetke u Excel ćeliji

Red traka umjesto vrijednosti ćelije ###### - ova vrijednost nije greška. To je samo informacija da je širina kolone preuska da bi se smjestila ispravan prikaz sadržaja ćelije. Samo treba da proširite kolonu. Na primjer, uradi dvostruki klik lijevo dugme miša na ivici zaglavlja kolona ove ćelije.

Dakle, rešetke (######) umjesto vrijednosti ćelija mogu se vidjeti sa negativnim datumom. Na primjer, pokušavamo oduzeti od stari datum novi datum. I kao rezultat izračunavanja, format ćelija je "Datum" (a ne "Općenito").

Pogrešan format ćelije također može prikazati seriju heš oznaka (######) umjesto vrijednosti.