Dobar dan prijatelji!

U ovom članku ćemo govoriti o tome što su greške u formulamaExcel s kojima se susrećemo pri radu s elektroničkim Excel proračunske tablice... Više sam nego siguran da su svi vidjeli greške, ali manje znaju kako ih se ispravno riješiti. Ipak, ovo znanje je važno jer će vas od toga osigurati tipične greške ili će vam pomoći da se brzo i bez panike riješite ili popravite primljeno greške u formulamaExcel.

Puno se može pričati o pogreškama u Excelu, ali pogledajmo one najčešće, reći ću vam zašto i kako nastaju, kao i kako popraviti nastale greške u formulamaExcel za ispravan prikaz podataka.

Pa, zapravo što se događa greške u formulamaExcel:

1. Greška #####. Ovo je jedan od najčešćih i najjednostavnijih pogreške u Excel formulama... To znači samo jedno, da širina stupca nije dovoljno široka da u potpunosti prikaže vaše podatke. Liječenje ovog problema je vrlo jednostavno, pomaknite pokazivač miša preko granice stupca i držeći lijevu tipku povećavajte ćeliju dok se podaci ne počnu prikazivati ​​ili dvostruki klik na granici stupca omogućit će najširu ćeliju u stupcu.
2. Pogreška #NAME?... Ovaj pogreška (#NAME?) javlja se u Excel formulama samo kada uređivač ne može prepoznati tekst u formuli (na primjer, pogreška u nazivu funkcije zbog tipkarske pogreške = ZBIR (A1: A4). greške u formulamaExcel, morate ga pažljivo pročitati i ispraviti pogrešku (A1: A4).
3. Pogreška #VALUE!... Ovaj pogreška (#VRIJEDNOST!) možda imate slučaj kada formula sadrži argument čija vrsta nije prikladna za vaše izračune. Na primjer, u vašoj matematici ili formuli, tekstualna vrijednost= A1 + B1 + C1, gdje je C1 tekst. Liječenje problema je jednostavno, koristite formulu koja zanemaruje ćelije u kojima se nalazi tekst ili jednostavno uklonite zadanu vrijednost iz ćelije C1.
4. Pogreška # BUSINESS / 0... Kao što možete vidjeti iz pogreške u formuli, jednostavno ste pomnožili svoj argument s brojem 0, a to se ne može učiniti na temelju matematička pravila... Da biste ispravili ovu pogrešku, morate promijeniti broj tako da ne bude jednak 0 ili promijeniti formulu, na primjer, logičku, kako biste izbjegli pogrešku. = IF (A2 = 0; ""; A1 / A2)
5. Pogreška # LINK!... Ovo je jedna od najčešćih i zbunjujućih pogrešaka u Excelovim funkcijama. Kada vidite ovu pogrešku, to znači da se formula odnosi na ćeliju koja više ne postoji. To je posebno problematično kada radite s velikim količinama podataka u i velika količina... Kada ovako uredite svoje tablice greške u formulamaExcel ne biste se trebali bojati, vrlo ih je lako popraviti, samo trebate i sve će se vratiti na svoje mjesto, ili, ako je potrebno, ručno prepišite formulu, isključujući iz nje pogrešan argument.

Nadam se da je ovaj članak o tome što su greške u formulamaExcel 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 što znači "spaliti život"?
D.B. Pokazati

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

To znači da postoji pogreška u izvornim podacima, samoj formuli, formatiranju ili na neki drugi način. Poruka o pogrešci sadrži naznaku gdje se točno dogodila. U nastavku ćemo pogledati poruke o pogreškama i njihove najčešće uzroke:

Pogreška #####

Najčešće ova pogreška znači da stupac nije dovoljno širok za prikaz sadržaja ćelije. Da biste riješili problem, dovoljno je povećati širinu stupca.

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

Pogreška # N / A

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

• U funkciji nedostaje jedan ili više potrebnih argumenata.
• Funkcije VLOOKUP, HLOOKUP, LOOKUP ili SEARCH označavaju netočna vrijednost argumenta lookup_value.
• VLOOKUP, HLOOKUP ili MATCH koriste se za pretraživanje nerazvrstane tablice.
• U izvornoj tablici umjesto podataka koji nedostaju unesena je vrijednost # N / A ili ND ().

# DIV / 0 pogreška!

# DIV / 0 pogreška! Označava da se dijeljenje s nulom događa tijekom izračuna. Razlozi mogu biti sljedeći:

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

Pogreška # PRAZNO!

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

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

• Ako funkcija ne mora specificirati raspone koji se preklapaju (na primjer, prilikom zbrajanja), vjerojatno je kao razmak između raspona korišten razmak, a ne točka-zarez. Ili je umjesto dvotočke korišten razmak prilikom navođenja raspona.
• Ako se u funkciji moraju specificirati preklapajući rasponi, vjerojatno je da rasponi doista nemaju zajedničke stanice... Morate provjeriti navedene raspone u argumentima funkcije.

Pogreška #NUM!

Ova se pogreška javlja ako su u formuli ili funkciji navedene netočne numeričke vrijednosti. Na primjer:

• Funkcija koja uzima numerički argument navodi tekst ili referencu ćelije s nenumeričkom vrijednošću.
• Funkcija koja se ponavlja (izračun koji se ponavlja dok se ne ispuni zadani uvjet, na primjer, IRR ili BET) ne može izračunati rezultat (na primjer, dani uvjet nije ispunjen).
• Rezultat izračuna je broj koji je prevelik ili premalen da bi se mogao koristiti u Excelu. Podsjećamo vas na to maksimalan broj u Excelu ne može premašiti 10307 i biti manji od minus 10307.

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

Pogreška #VALUE!

Ova se pogreška javlja kada se koristi pogrešan tip argumenta ili operanda. Na primjer, korisnik bi mogao pogriješiti u sljedećem:

• Umjesto broja ili logičke vrijednosti (TRUE ili FALSE), u formulu je upisan tekst.
• Nakon unosa ili uređivanja formule polja, pritisnite ENTER umjesto CTRL + SHIFT + ENTER.
• Referenca, formula ili funkcija navedena je kao niz.
• Raspon je specificiran kao uvjet funkcije koji zahtijeva jednu vrijednost.

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

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

Ako formula sadrži nekoliko funkcija, onda u kontekstni izbornik Pojavljuje se Prikaži korake izračuna. U prozoru koji se otvori možete vidjeti koji izračun dovodi do pogreške.

Imam pogrešku #NAME? i ne mogu pronaći razlog za to.

• Provjerite pravopis naziva funkcije (naslov). Da biste to učinili, možete pozvati Čarobnjaka za funkcije (grupa Knjižnica funkcija na kartici Formule) i odabrati željenu funkciju s predloženog popisa.
• Tekst koji nije priložen formuli unesen je u formulu. dvostruki navodnici... Recimo da je rezultat formule trebao biti tekst s brojem - na primjer, Ukupno 200 r. Ako tekst nije stavljen u dvostruke navodnike kada u formulu unesete tekst Ukupno, Excel interpretira podatke kao naziv, što generira pogrešku.

Napomena: Ime je riječ, izraz ili drugi skup znakova definiranih od strane korisnika koji označava ćeliju, raspon ćelija, formulu ili konstantu.

• Formula ili funkcija koristi naziv koji nije definiran. Provjerite to dato ime postoji. Na kartici Formule, u grupi Imenovane ćelije, odaberite Upravitelj imena i provjerite nalazi li se dano ime na popisu. Ako ovaj naziv nije prisutan, možete ga dodati odabirom Create Name.
• Došlo je do greške u pisanju imena. Da biste provjerili jesu li nazivi ispravni u traci formule, pritisnite F3, kliknite naziv koji želite, a zatim kliknite U redu.
• Formula ili funkcija koristi referencu na drugi list koji nije zatvoren u jednostrukim navodnicima. Ako naziv drugog lista ili knjige sadrži znakove koji nisu abecedni ili razmak, tada se taj naziv mora staviti u apostrofe (").

imam ogromna baza podaci o kupcima – kupci. Svaki dan se u bazu podataka dodaje stupac koji prikazuje iznos prihoda od svakog kupca. Ako kupac nije ništa kupio, u ćeliju se stavlja # N/A. Kako da zbrojim stupac ako postoje takvi podaci?

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

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

Recimo da originalna tablica izgleda ovako:

Pomoću funkcije BDSUMM možemo odabrati za zbrajanje one ćelije koje ne sadrže vrijednost # N / A. Funkcija BDSUMM ima sljedeću sintaksu:

BDSUMM (baza_podataka, polje, uvjeti), gdje

• baza podataka je raspon ćelija na koje će se djelovati.
• polje je stupac iz kojeg će se zbrati vrijednosti.
• uvjeti su skup uvjeta koji moraju biti ispunjeni za vrijednost ćelije da bi se ta vrijednost zbrojila.

To jest, u funkciji moramo postaviti zbroj prvog stupca naše tablice, pod uvjetom da vrijednost ćelije ovog stupca nije jednaka # N / A. Da biste to učinili, prvo stvorite uvjet - na bilo kojem prikladnom mjestu na radnom listu napišite uvjet # N / A i označite ovu ćeliju na isti način kao i stupac baze podataka koji nam je potreban.

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

• C2: C18 je naš stol,
• E2: E3 je naš uvjet.

Imajte na umu da rasponi baze podataka i uvjeti moraju sadržavati zaglavlja stupaca! U tom slučaju zaglavlje uvjeta mora odgovarati zaglavlju stupca na kojem se izvode izračuni!

Naš rezultat je naveden ispod tablice:

Ako naša baza podataka sadrži pogreške različiti tipovi, dovoljno ih je dodati uvjetima (i, sukladno tome, proširiti raspon uvjeta u formuli), kako biste dobili željeni rezultat.

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

Nakon unosa ili ispravljanja formule, kao i kod promjene bilo koje vrijednosti funkcije, događa se da se pojavi greška formule, a ne tražena vrijednost. Ukupno, uređivač proračunskih tablica prepoznaje sedam glavnih vrsta takvih netočnih izračuna. U nastavku ćemo analizirati kako izgledaju pogreške u Excelu i kako ih popraviti.

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

1. #SLUČAJ!- "podjela s 0", najčešće se javlja pri pokušaju dijeljenja s nulom. Odnosno, formula ugrađena u ćeliju, obavljajući funkciju podjele, nailazi na ćeliju u kojoj s nultu vrijednost ili je "Prazno". Da biste riješili problem, provjerite sve ćelije uključene u izračune i ispravite sve nevažeće vrijednosti. Druga radnja koja vodi do # DIV / O! - ovo je unos netočnih vrijednosti u neke funkcije, kao što je = AVERAGE (), ako je izračun 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 formula (funkcija) ne može razumjeti. Unošenjem neprikladnih brojeva u funkciju, sigurno ćete izbaciti ovu grešku. Kada se pojavi, provjerite jesu li sve ćelije za unos ispravno popunjene, a posebno u onima na kojima svijetli isti natpis. Uobičajeno kada se koristi
3. #IME? - "Neispravan naziv", pokazatelj netočnog naziva formule ili nekog njenog dijela. Problem nestaje ako provjerite i ispravite sva imena i nazive koji prate algoritam izračuna.
4. # PRAZNO!- “prazna vrijednost u rasponu”, signal da su negdje u izračunu upisana područja koja se ne sijeku ili je između navedenih raspona napravljen razmak. Prilično rijetka greška. Pogrešan unos može izgledati ovako:

ZBIR (G10: G12 I8: J8)

Excel ne prepoznaje takve naredbe.
5. #BROJ!- pogrešku uzrokuje formula koja sadrži broj koji ne odgovara navedenom rasponu.
6. #VEZA!- upozorava da su stanice povezane s ovom formulom nestale. Provjerite jesu li ćelije navedene u formuli izbrisane.
7. #VRIJEDNOST!- tip argumenta za funkciju nije ispravno odabran.

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

Osim toga, Excel izdaje upozorenje o netočnoj formuli. Program će vam pokušati reći kako točno rasporediti interpunkcijske znakove (na primjer, zagrade). Ako predložena opcija zadovoljava vaše zahtjeve, kliknite "Da". Ako upit zahtijeva ručno podešavanje. Zatim odaberite "Ne" i sami preuredite zagrade.

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

Funkcija pomaže dobro eliminirati pogreške u Excelu. Radi tako što pronalazi pogreške u ćelijama, ako pronađe pogrešku u formuli, vraća TRUE i obrnuto. U kombinaciji sa = IF (), zamijenit će vrijednost ako se pronađe greška.

Radna formula: = IF (GREŠKA (izraz); pogreška; izraz).

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

Objašnjenje: ako se pronađe greška tijekom izvršavanja A1 / A2, vratit će se prazan (""). Ako je sve prošlo ispravno (tj. ISERROR (A1 / A2) = FALSE), tada se izračunava A1 / A2.

Pogreške u Excelu.Primjena 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- to)

Funkcija if error sadrži mogućnosti i ISERROR () i IF (), ali je dostupna u novijim verzijama Excela, što je vrlo zgodno

Alat se aktivira na sljedeći način: = IFERROR (vrijednost; vrijednost u slučaju pogreške). Umjesto "vrijednosti" stavlja se izračunati izraz / referenca ćelije, a umjesto "vrijednost u slučaju pogreške" - što treba vratiti kada se pojavi netočnost, na primjer, ako tijekom izračunavanja A1 / A2 #SLUČAJ! tada će formula izgledati ovako:

ESLIOSHIBKA (A1 / A2; "")

»Preuzmite ovdje

Popisi i rasponi (5)
Makronaredbe (VBA procedure) (63)
Razno (39)
Greške i greške u Excelu (3)

Kako prikazati 0 umjesto pogreške u ćeliji s formulom

Postoje situacije kada u radna bilježnica na listovima se stvaraju mnoge formule koje obavljaju razne zadatke. Pritom su formule nastajale nekada davno, možda čak i na vama. I formule vraćaju pogreške. Na primjer # DIV / 0! (# DIV / 0!). Ova se pogreška događa ako se dijeljenje s nulom dogodi unutar formule: = A1 / B1, gdje B1 sadrži nulu ili prazno. Ali mogu postojati i druge pogreške (# N / A, #VALUE! Itd.). Možete promijeniti formulu dodavanjem provjere pogreške:

= IF (ISERR (A1 / B1), 0, A1 / B1)
argumenti:
= IF (EOSH (1 argument), 2 argument, 1 argument)
Ove formule će raditi u bilo kojoj verziji Excela. Istina, EOS funkcija neće rješavati pogrešku # N / A (# N / A). Da biste također obrađivali # N / A, morate koristiti funkciju ISERROR:
= IF (ISGREŠKA (A1 / B1), 0, A1 / B1)
= IF (ISGREŠKA (A1 / B1), 0, A1 / B1)
No, dalje u tekstu koristit ću EOS (jer je kraći), a osim toga nije uvijek potrebno "ne vidjeti" grešku # N/A.
Ali za Excel verzije 2007. i novije, može se primijeniti nešto optimiziranija funkcija AKO POGREŠKA:
= AKO GREŠKA (A1 / B1,0)
= AKO GREŠKA (A1 / B1, 0)
argumenti:
= IFERROR (1 argument, 2 argument)

1 argument: izraz za procjenu
Argument 2: vrijednost ili izraz koji se vraća u ćeliju ako dođe do pogreške u prvom argumentu.

Zašto je IFERROR bolji, a ja ga nazivam optimiziranijim? Pogledajmo pobliže prvu formulu:
= IF (EOSH (A1 / B1); 0; A1 / B1)
Ako računamo korak po korak, vidjet ćemo da se najprije izračuna izraz A1 / B1 (tj. dijeljenje). A ako je njegov rezultat pogreška, tada će EOSH vratiti TRUE, što će biti proslijeđeno u IF. A onda će funkcija IF vratiti vrijednost iz drugog argumenta 0.
Ali ako rezultat nije pogrešan i ISERR vraća FALSE, tada će funkcija ponovno izračunati prethodno izračunati izraz: A1 / B1
Uz gornju formulu, to ne igra posebnu ulogu. Ali ako se formula poput VLOOKUP-a koristi s pogledom na nekoliko tisuća redaka, tada izračun dva puta može značajno povećati vrijeme ponovnog izračuna formula.
Funkcija IFERROR procjenjuje izraz jednom, pamti njegov rezultat, a ako je pogrešan, vraća ono što je zapisano kao drugi argument. Ako nema greške, onda vraća pohranjeni rezultat evaluacije izraza iz prvog argumenta. Oni. izračun se zapravo događa jednom, što praktički neće utjecati na brzinu općeg ponovnog izračuna formula.
Stoga, ako imate Excel 2007 i noviji i datoteka se više neće koristiti ranim verzijama- onda ima smisla koristiti IFERROR.

Zašto uopće ispravljati formule s pogreškama? Obično se to radi zbog estetskijeg prikaza podataka u izvješćima, posebno ako se izvješća potom šalju upravi.

Dakle, na listu postoje takve formule, čije pogreške treba obraditi. Ako postoje jedna ili dvije slične formule za korekciju (da, čak 10-15), tada gotovo da nema problema za ručno zamijeniti. Ali ako postoji nekoliko desetaka ili čak stotina takvih formula, problem postaje gotovo univerzalan :-). Međutim, proces se može pojednostaviti pisanjem relativno jednostavan kod Visual basic za Prijavu.
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 Tada s = rc.Formula s = Mid (s, 2) ss = " = "&" IF (ISERR ("& s &"), "& sToReturnVal &", "& s &") "Ako je lijevo (s, 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 niz, ss As String On Error Nastavi sljedeći set rr = Intersect (Odabir, ActiveSheet.UsedRange) Ako rr nije ništa onda MsgBox "Odabrani raspon ne sadrži podatke", vbInformation, "www..HasFormula Tada s = rc.Formula s = Mid (s, 2) ss =" = "&" IF (ISERR ("& s &"), "& sToReturnVal &", "& s &") "Ako lijevo (s, 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 transformirati formulu u ćeliji:" & ss & vbNewLine & _ Err.Description, vbInformation, "www..site" End If End Sub

Za verzije 2007 i novije

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 niz, ss Kao string na Error Nastavi sljedeći set rr = Intersect (Odabir, 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 Tada s = rc.Formula s = Mid (s, 2) ss = "=" & "IFERROR (" & s & ", "& sToReturnVal &") "Ako ostane (s, 8)<>"IFERROR (" Tada Ako rc.HasArray Onda rc.FormulaArray = ss Else rc.Formula = ss Kraj Ako Ako Err.Number Zatim ss = rc.Adresa rc.Odaberi Izlaz za Kraj Ako Kraj ako Kraj ako Sljedeći rc Ako Err.Number Zatim MsgBox "Nije moguće pretvoriti formulu u ćeliju:"& ss & vbNewLine & _ Err.Description, vbInformation, "www.site" Drugo 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 (Odabir, ActiveSheet.UsedRange) Ako rr nije ništa onda MsgBox "Odabrani raspon ne sadrži podatke", vbInformation, "www..HasFormula Tada s = rc.Formula s = Mid (s, 2) ss =" = "&" IFERROR ("& s &", "& sToReturnVal &") "Ako je lijevo (s, 8)<>"IFERROR (" Tada Ako rc.HasArray Onda rc.FormulaArray = ss Else rc.Formula = ss Kraj Ako Ako Err.Number Zatim ss = rc.Adresa rc.Odaberi Izlaz za Kraj Ako Kraj ako Kraj ako Sljedeći rc Ako Err.Number Zatim MsgBox "Ne mogu transformirati formulu u ćeliji:" & ss & vbNewLine & _ Err.Description, vbInformation, "www..site" End If End Sub

Kako radi
Ako niste upoznati s makronaredbama, prvo je bolje pročitati kako ih kreirati i nazvati: Što je makronaredba i gdje je mogu pronaći? od može se dogoditi da sve napravite kako treba, ali zaboravite uključiti makronaredbe i ništa neće raditi.

Kopirajte gornji kod, idite na VBA editor ( Alt+F11), kreirajte standardni modul ( Umetnuti -Modul) i samo zalijepite ovaj kod u njega. Idi desno Excel radna knjiga i odaberite sve ćelije u kojima formule treba transformirati tako da u slučaju pogreške vrate nulu. Pritisnite Alt+F8, odaberite kod IfIsErrNull(ili IfErrorNull, ovisno o tome koji ste kopirali) i pritisnite Izvršiti.
Funkcija rukovanja pogreškama bit će dodana svim formulama u odabranim ćelijama. Dani kodovi također uzimaju u obzir:
- ako je formula već primijenila funkciju IFERROR ili IF (EOSH), tada se takva formula ne obrađuje;
- kod će ispravno obraditi i funkcije niza;
- možete odabrati nesusjedne ćelije (putem Ctrl).
Koji je nedostatak: složene i dugačke formule niza mogu uzrokovati pogrešku koda, zbog posebnosti ovih formula i njihove obrade iz VBA. U tom slučaju, kod će pisati o nemogućnosti nastavka rada i istaknut će problemsku ćeliju. Stoga toplo preporučam da napravite zamjenu na kopijama datoteka.
Ako vrijednost pogreške treba zamijeniti praznom, a ne nulom, tada vam je potrebna linija

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

Ukloni apostrof ( " )

Također možete dati kod pozovite ga pritiskom na gumb (Kako stvoriti gumb za pozivanje makronaredbe na listu) ili ga smjestite u dodatak (Kako stvoriti vlastiti dodatak?) tako da ga možete pozvati iz bilo koje datoteke.

I mali dodatak: pokušajte promišljeno primijeniti kod. Ne smeta uvijek vraćanju pogreške. Na primjer, za pomoću VLOOKUP-a ponekad je korisno vidjeti koje vrijednosti nisu pronađene.
Također želim napomenuti da ga treba primijeniti na formule koje stvarno rade. Jer ako formula vraća #NAME! (# NAME!), 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 izračuna. Bolje je analizirati takve formule i pronaći pogrešku kako bi se izbjegle logičke pogreške izračuna na listu.

Je li č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 ":" lijevo ","di textefce " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "desno", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffect60" , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "desno", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffects1": 600, "textefcs1" :" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; položaj: apsolutni; vrh: 0px; lijevo: 0px; širina: 100%; visina: 100% ; pozadina -boja: # 333333; neprozirnost: 0,6; filter: a lpha (prozirnost = 60); "," titlecss ":" prikaz: blok; položaj: relativan; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; boja: #fff; "," descriptioncss ":" prikaz: blok; položaj: relativan; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; boja: #fff; margin-top: 8px; "," buttoncss ":" display: block; položaj: relativan; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" veličina fonta: 12px; "," descriptioncssresponsive ":" display: none! važno; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40))

U slučaju pogrešnih izračuna, formule umjesto vrijednosti prikazuju nekoliko vrsta pogrešaka. Razmotrite ih na praktični primjeri u procesu rada formula koje su dale pogrešne rezultate proračuna.

Pogreške Excel formule prikazane u ćelijama

V ovu lekcijuće opisati vrijednosti pogreške formula koje ćelije mogu sadržavati. Znajući značenje svakog koda (na primjer: #VALUE !, # DIV / 0 !, #BROJ !, # N / A !, #NAME !, #NOT !, #REF!), lako možete shvatiti kako pronaći grešku u formuli i eliminirati je.

Kako ukloniti # DIV / 0 u Excelu

Kao što se vidi pri dijeljenju stanicom s prazna vrijednost program to doživljava kao podjelu s 0. Kao rezultat, ispisuje vrijednost: # DIV / 0! To možete provjeriti uz pomoć savjeta.

U drugima aritmetički proračuni(množenje, zbrajanje, oduzimanje) prazna ćelija također je nula.



Rezultat pogrešnog izračuna je #NUM!

Nevažeći broj: # NUMBER! Greška je nemogućnosti izvođenja izračuna u formuli.

Neki praktični primjeri:

Pogreška: #NUM! nastaje kada brojčana vrijednost prevelika ili premala. Isti način data greška može se pojaviti pri pokušaju dobivanja korijena iz negativnog broja. Na primjer, = ROOT (-25).

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

Ćelija A2 ima isti problem s velikim brojevima. Naizgled 1000 mali broj, ali vraćanje njegovog faktorijala rezultira prevelikom brojčanom vrijednošću koju Excel ne može podnijeti.

U ćeliji A3 - Korijen ne može biti s negativnim brojem, ali prikazan 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: = SEARCH (“Maxim”; A1: A4) traži tekstualni sadržaj “Maxim” u rasponu ćelija A1: A4. Sadržaj je pronađen u drugoj ćeliji A2. Stoga funkcija vraća rezultat 2. Druga formula traži tekstualni sadržaj "Andrey", tada raspon A1: A4 ne sadrži takve vrijednosti. Stoga funkcija vraća pogrešku # N / A (bez podataka).

Pogreška #NAME! u Excelu

Spadaju u kategoriju pogrešaka u pisanju funkcija. Nevažeći naziv: #NAME! - znači da Excel nije prepoznao tekst napisan u formuli (naziv funkcije = SUM () mu je nepoznat, napisan je s greškom). Ovo je rezultat sintaktičke pogreške tijekom pisanja naziva funkcije. Na primjer:

Pogreška # PRAZNO! u Excelu

Prazan skup: # PRAZNO! - to su pogreške operatora presjeka skupova. U Excelu postoji takva stvar kao što je presjek skupova. Odnosi se na brzi prijem podaci iz velikih tablica na zahtjev točke presjeka vertikalnog i horizontalnog raspona ćelija. Ako se rasponi ne preklapaju, program prikazuje pogrešnu vrijednost - # NULL! Operator skupa raskrižja je jedan prostor. Oni odvajaju okomite i horizontalne raspone navedene u argumentima funkcije.

U ovom slučaju, sjecište raspona je ćelija C3 i funkcija prikazuje njezinu vrijednost.

Zadani argumenti u funkciji: = SUM (B4: D4 B2: B3) - ne tvore sjecište. Stoga funkcija daje vrijednost s greškom - # NULL!

#VEZA! - pogreška referenci Excel ćelija

V ovaj primjer greška se dogodila kada je formula pogrešno kopirana. Imamo 3 raspona ćelija: A1: A3, B1: B4, C1: C2.

Pod prvim rasponom u ćeliji A4 unesite formulu za zbrajanje: = ZBIR (A1: A3). I zatim kopirajte istu formulu pod drugim rasponom, u ćeliju B5. Formula, kao i prije, zbraja samo 3 ćelije B2: B4, zaobilazeći vrijednost prvog B1.

Kada je ista formula kopirana pod trećim rasponom, funkcija je vratila pogrešku #REFERENCA! u ćeliju C3. Budući da iznad ćelije C3 mogu biti samo 2 ćelije, a ne 3 (kao što zahtijeva izvorna formula).

Bilješka. V u ovom slučaju najzgodnije je pritisnuti kombinaciju vrućeg ALT tipke+ =. Tada će se umetnuti funkcija zbrajanja i automatski će odrediti broj ćelija za zbrajanje.

Kako popraviti VALUE u Excelu

#VRIJEDNOST! - greška u vrijednosti. Ako pokušamo dodati broj i riječ u Excelu, rezultat će biti #VRIJEDNOST! Zanimljiva je činjenica da ako pokušamo dodati dvije ćelije, u kojima je vrijednost prve broj, a druge tekst pomoću funkcije = SUM (), tada se neće dogoditi pogreška, a tekst će uzeti vrijednost 0 tijekom izračuna. Na primjer:

Rešetke u Excel ćeliji

Red hash umjesto vrijednosti ćelije ###### - ova vrijednost nije pogreška. To je samo informacija da je širina stupca preuska da bi primila ispravno prikazan sadržaj ćelije. Samo trebate proširiti stupac. Na primjer, učiniti dvostruki klik lijevom tipkom miša na rubu naslova stupaca ove ćelije.

Dakle, hash (######) umjesto vrijednosti ćelija može se vidjeti kada je datum negativan. Na primjer, pokušavamo oduzeti od stari datum novi datum... I kao rezultat izračuna, format ćelija je "Datum" (ne "Općenito").

Netočan format ćelije također može prikazati niz hash znakova (######) umjesto vrijednosti.