„Chyby sú príležitosťou na zlepšenie“. V akejkoľvek úlohe alebo systéme sa vyskytnú chyby. Excel nie je výnimkou. Keď sa pokúšate niečo urobiť so vzorcom, stretnete sa s rôznymi druhmi chýb programu Excel. Tieto chyby môžu spôsobiť, že váš vzorec/informačný panel/správy bude absolútnym plytvaním. A ak neviete, prečo sa vyskytol konkrétny typ chyby, možno budete musieť stráviť hodiny, kým ich vyriešite.
Pri práci na programe Excel som sa stretol s mnohými chybami programu Excel. S trochou námahy a hľadaním na Googli som tieto chyby vyriešil. V tomto článku vysvetlím niektoré bežné a otravné chyby programu Excel, ktoré sa vyskytujú v programe Excel. Budeme diskutovať o tom, prečo sa tieto chyby vyskytujú a ako ich vyriešiť.
Čo sú chyby vzorcov programu Excel
Pri aplikácii vzorca, ktorého výsledkom sú chyby definované v Exceli (#NA, #VALUE, #NAME atď.), Sa nazývajú chyby vzorcov programu Excel. Tieto chyby zachytí excel a vytlačí ich na hárky. Príčiny týchto chýb môžu byť, nedostupné hodnoty, nesprávny typ argumentov, delenie 0 atď. Je ľahké ich chytiť a opraviť.
Logické chyby Excel nezachytáva a je najťažšie ich opraviť. Častým dôvodom týchto chýb je nekonzistentnosť údajov, nesprávne zadanie údajov, ľudské chyby atď. Môžu byť tiež opravené, ale vyžadujú si čas a úsilie. Pred vykonaním operácie s nimi je lepšie svoje údaje dokonale pripraviť.
Chytanie chýb vzorca Excel:
V Exceli existuje niekoľko vyhradených funkcií na zachytenie a spracovanie konkrétneho typu chýb (napríklad funkcia ISNA). Funkcia ISERROR a IFERROR sú dve funkcie, ktoré môžu zachytiť akýkoľvek druh chyby programu Excel (okrem logických).
Chyba Excel #NA
Ak nie je nájdená hodnota, v programe Excel sa vyskytne chyba #NA. Jednoducho to znamená NEDOSTUPNÉ. Pri funkcii VLOOKUP programu Excel sa často vyskytuje chyba #NA.
Na vyššie uvedenom obrázku dostávame chybu #NA, keď hľadáme „Divya“ v stĺpci A. Dôvodom je, že „Divya nie je v zozname.
Riešenie chyby #NA:
Ak ste si istí, že vyhľadávacia hodnota musí existovať vo vyhľadávacom zozname, prvá vec, ktorú by ste mali urobiť, je skontrolovať vyhľadávaciu hodnotu. Skontrolujte, či je hodnota vyhľadávania zadaná správne. Ak nie, opravte to.
Za druhé, čiastočnú zhodu môžete vykonať pomocou funkcie VLOOKUP alebo akejkoľvek funkcie vyhľadávania. Ak ste si istí, že sa niektorá časť textu musí zhodovať, použite túto.
Ak si nie ste istí, či táto hodnota existuje alebo nie, môže sa použiť na kontrolu, či hodnota v zozname existuje alebo nie. Na obrázku vyššie môžeme povedať, že Divya nie je v zozname.
Ak chcete namiesto chyby #NA zachytiť chybu #NA a vytlačiť alebo urobiť niečo iné, môžete použiť funkciu Excel ISNA. Funkcia ISNA vráti hodnotu TRUE, ak funkcia vráti chybu #NA. Vďaka tomu sa môžeme vyhnúť chybe #NA. ISNA funguje úžasne s funkciou VLOOKUP. Pozrite sa sem.
Chyba Excelu #HODNOTA
#HODNOTA sa vyskytuje, ak zadaný argument nie je podporovaného typu. Ak sa napríklad pokúsite pridať dva texty pomocou operátora aritmetiky plus (+), zobrazí sa chyba #HODNOTA. To isté sa stane, ak sa pokúsite získať rok neplatného formátu dátumu pomocou funkcie YEAR.
Ako opraviť chybu #HODNOTA?
Najprv potvrďte údajový typ, o ktorom hovoríte. Ak vaša funkcia vyžaduje číslo, uistite sa, že ho zadávate. Ak je číslo naformátované ako text, potom ho pomocou funkcie VALUE skonvertujte na číslo. Ak funkcia vyžaduje text (napríklad funkcia DATEVALUE) a odkazujete na typ čísla alebo dátumu, preveďte ich na text.
Ak očakávate, že môže dôjsť k chybe #HODNOTA a chcete ich chytiť, potom môžete použiť ISERR, ISERROR alebo IFERROR na odchyt a urobiť niečo iné.
Chyba Excel #REF
„Odkaz“ v #REF znamená referenciu. K tejto chybe dochádza, keď vzorec odkazuje na miesto, ktoré neexistuje. K tomu dôjde, keď odstránime bunky z rozsahov, na ktoré sa vzťahuje aj vzorec.
V súbore gif nižšie vzorec odkazuje na A2 a B2. Keď odstránim A2, vzorec sa zmení na chybu #REF.
Vyriešiť chybu programu Excel #REF:
Najlepšie je byť opatrný pred odstránením buniek v dátach. Zaistite, aby sa na túto bunku nevzťahoval žiadny vzorec.
Ak už máte chybu #REF, vysledujte ju a potom ju odstráňte zo vzorca.
Akonáhle sa napríklad zobrazí chyba #REF, váš vzorec bude vyzerať takto.
| = A2+#REF! |
Môžete iba odstrániť #REF! Zo vzorca získate vzorec bez chýb. Ak to chcete urobiť hromadne, použite funkciu nájsť a nahradiť. Stlačením CTRL+H otvorte položku Hľadať a nahradiť. Do vyhľadávacieho poľa napíšte #REF. Pole na výmenu nechajte prázdne. Kliknite na tlačidlo Nahradiť všetko.
Ak chcete znova nastaviť odkaz na novú bunku, urobte to ručne a nahraďte #REF! S tým platným odkazom.
Excel #NAME Chyba
#NÁZOV sa vyskytuje v programe Excel, ak nemôže identifikovať text vo vzorci. Ak napríklad napíšete nesprávne názov funkcie, program Excel zobrazí chybu #NAME. Ak vzorec odkazuje na názov, ktorý v hárku neexistuje, zobrazí sa chyba #NÁZOV.
Na vyššie uvedenom obrázku má bunka B2 vzorec = PRÁVOMOCI (A2,2). POWERS nie je v programe Excel platná funkcia, preto vracia chybu #NAME.
V bunke B3 máme = SUM (čísla). SUM je platnou funkciou programu Excel, ale pomenované rozsahy „čísel“ v hárku neexistujú. Preto excel vracia #NAME? Chyba.
Ako sa vyhnúť chybe #NAME v programe Excel?
Aby ste sa vyhli chybám #NAME v programe Excel, vždy správne napíšte názvy funkcií. Na zaistenie toho, že používate platnú funkciu, môžete použiť návrh programu Excel. Kedykoľvek píšeme znaky za znamienko rovnosti, Excel zobrazuje funkcie a pomenované rozsahy na hárku, začínajúc od týchto znakov. Ak chcete použiť túto funkciu, v zozname návrhov prejdite nadol na názov funkcie alebo názov rozsahu v zozname návrhov.
Excel #DIV/0! Chyba
Ako naznačuje názov, táto chyba sa vyskytuje, keď vzorec vyústi do delenia nulou. K tejto chybe môže dôjsť aj vtedy, keď z bunky, na ktorej závisí vzorec delenia, odstránite nejakú hodnotu.
| = IF (B2 = 0, A2, A2/B2) |
Chybu DIV/0 budeme mať iba vtedy, ak je deliteľ 0 alebo prázdny. Preto skontrolujeme deliteľ (B2), ak je nula, vytlačíme A2, inak rozdelíme A2 na B2. To bude fungovať aj pre prázdne bunky.
Chyba Excel #ČÍSLO
K tejto chybe dochádza vtedy, keď číslo nemožno zobraziť na obrazovke. Dôvodom môže byť to, že číslo je príliš malé alebo príliš veľké na to, aby sa dalo zobraziť. Ďalším dôvodom môže byť to, že výpočet nemožno vykonať s daným číslom.
Na vyššie uvedenom obrázku sa v bunke C2 pokúšame získať hodnotu -16309. Hodnota je taká malá, že sa nedá zobraziť.
V bunke C3 sa pokúšame získať odmocninu z hodnoty -16. Pretože neexistuje žiadna taká hodnota ako druhá odmocnina zápornej hodnoty (okrem imaginárnych čísel), preto Excel zobrazuje #ČÍSLO! Chyba.
Ako vyriešiť #ČÍSLO! Chyba?
Ak chcete vyriešiť chybu #ČÍSLO, prvá vec, ktorú môžete urobiť, je skontrolovať všetky hodnoty, ktoré uvádzate. Skontrolujte, či ide o platné čísla, s ktorými môže váš vzorec fungovať.
Na vyriešenie formátu čísla použite funkcie počítadla. Napríklad vo vyššie uvedenom príklade, ak chcete získať odmocninu z -16, ale nechcete meniť hodnotu v bunke, môžeme použiť funkciu ABS.
| = SQRT (ABS (A3)) |
Tým sa vráti 4. Ak chcete získať zápornú hodnotu, použite pred funkciou záporné znamienko. Potom samozrejme môžete samozrejme použiť funkciu spracovania chýb.
O riešení chyby #ČÍSLO máme vyhradený článok. Môžete to skontrolovať tu.
#NULL Chyba v programe Excel
Toto je zriedkavý typ chyby. Chyba #NULL spôsobená nesprávnym odkazovaním na bunku. Napríklad, ak chcete uviesť odkaz na rozsah A2: A5 vo funkcii SUMA, ale omylom napíšete A2 A5. To vygeneruje chybu #NULL. Ako vidíte na obrázku nižšie, vzorec vracia chybu #NULL.
Ak nahradíte medzeru stĺpcom (:), chyba #NULL zmizne a dostanete súčet A2: A5. Ak medzeru nahradíte čiarkou (,), dostanete súčet A2 a A5.
Ako vyriešiť chybu #NULL?
Ako vieme, chyba #NULL je spôsobená preklepom. Aby ste tomu zabránili, skúste namiesto ručného písania vybrať rozsahy pomocou kurzora.
Niekedy sa stane, že budete musieť zadať adresu rozsahu z klávesnice. Vždy dbajte na stĺpec spájajúcich symbolov (:) alebo čiarkou (,), ktorej sa treba vyhnúť.
Ak máte existujúcu chybu #NULL, skontrolujte odkazy. S najväčšou pravdepodobnosťou ste vynechali stĺpec (:) alebo čiarku (,) medzi dvoma odkazmi na bunky. Nahraďte ich príslušným symbolom a môžete ísť.
###### Chyba v programe Excel
Niekedy si myslíme, že táto chyba je spôsobená nedostatočným priestorom na zobrazenie hodnoty, ale nie je tomu tak. V tomto prípade stačí rozšíriť šírku bunky a zobraziť hodnotu v bunke. Nenazývam to chybou.
V skutočnosti táto chyba spôsobila, keď sa pokúšame ukázať zápornú hodnotu času (neexistuje nič také ako záporný čas). Ak sa napríklad pokúsime odpočítať 1 od 12:21:00, vráti sa #######. V programe Excel je prvý dátum 1. 1. 1900 00:00. Ak sa pokúsite odpočítať čas, ktorý nasleduje, Excel vám ukáže chybu #######. Čím viac šírku rozšírite, tým viac # získate. Podrobnejšie som to popísal v tomto článku.
Ako sa vyhnúť chybám ###### Excel?
Pred vykonaním aritmetických výpočtov v programe Excel s časovou hodnotou si tieto veci zapamätajte.
- Minimálna hodnota času je 1/1/1900 00:00. V Exceli nemôžete mať predtým platný dátum
- 1 sa rovná 24 hodinám (1 dňu) v programe Excel. Odčítajte hodiny, minúty a sekundy a prevádzajte ich na ekvivalentné hodnoty. Ak chcete napríklad odčítať 1 hodinu od 12:21, musíte z nej odpočítať 1/24.
Sledovanie chýb v programe Excel
Teraz vieme, aké sú bežné vzorce programu Excel a prečo sa vyskytujú. Diskutovali sme tiež o tom, aké možné riešenie môže byť pre každý typ chýb programu Excel.
Niekedy sa v správach Excelu zobrazujú chyby a my sme nevedeli, odkiaľ sa chyba v skutočnosti vyskytuje. Je ťažké vyriešiť tieto druhy chýb. Na sledovanie týchto chýb poskytuje Excel funkciu sledovania chýb na karte vzorcov.
Podrobne som diskutoval o sledovaní chýb v programe Excel.
Riešenie logických chýb vo vzorci
Logické chyby je ťažké nájsť a vyriešiť. Excel nemá žiadnu masáž, ak máte vo svojej funkcii logickú chybu. Všetko vyzerá dobre. Ale len vy viete, že tam niečo nie je v poriadku. Napríklad, keď získate percentuálny podiel na časti celku, delíte časť na celkovú (= (časť/celkom)*100). Mala by byť vždy rovnaká alebo nižšia ako 100%. Keď získate viac ako 100%, viete, že niečo nie je v poriadku.
Bola to jednoduchá logická chyba. Niekedy však vaše údaje pochádzajú z niekoľkých zdrojov, v takom prípade je ťažké vyriešiť logické problémy. Jednou z možností je vyhodnotiť vzorec.
Na karte vzorcov je k dispozícii možnosť vyhodnotiť vzorec. Vyberte vzorec a kliknite naň. Každý krok vášho výpočtu vám ukáže, čo vedie k vášmu konečnému výsledku. Tu môžete skontrolovať, kde sa problém vyskytol alebo kde sa výpočet pokazil.
Môžete tiež vyhľadať závislé položky a precedenty a zistiť, na ktorých odkazoch váš vzorec závisí a aké vzorce závisia od konkrétnej bunky.
Takže áno, chlapci, to boli niektoré z bežných typov chýb, s ktorými sa stretáva každý užívateľ programu Excel. Dozvedeli sme sa, prečo sa každý typ chyby vyskytuje a ako sa im môžeme vyhnúť. Dozvedeli sme sa aj o vyhradenej funkcii Excel pre spracovanie chýb. V tomto článku máme odkazy na súvisiace stránky, ktoré podrobne diskutujú o probléme. Môžete si ich pozrieť. Ak máte nejaký konkrétny typ chyby, ktorý vás obťažuje, uveďte to v sekcii komentárov nižšie. Riešenie získate láskavo.
Ako opraviť #ČÍSLO! Chyba
Vytvorte vlastné chybové pruhy v programe Excel 2016
Chyba #HODNOTA a ako ju opraviť v programe Excel
Ako vystopovať a opraviť chyby vzorcov v programe Excel
Populárne články:
Funkcia VLOOKUP v programe Excel
COUNTIF v Exceli 2016
Ako používať funkciu SUMIF v programe Excel