Pri práci s údajmi a vzorcami v programe Excel sa určite stretnete s chybami.
Na zvládnutie chýb poskytol Excel užitočnú funkciu - funkciu IFERROR.
Predtým, ako sa dostaneme k mechanike používania funkcie IFERROR v Exceli, si najskôr prejdeme rôzne druhy chýb, s ktorými sa môžete stretnúť pri práci so vzorcami.
Typy chýb v programe Excel
Poznanie chýb v programe Excel vám pomôže lepšie identifikovať možný dôvod a najlepší spôsob, ako ich odstrániť.
Nasledujú typy chýb, s ktorými sa môžete v programe Excel stretnúť.
#N/A Chyba
Toto sa nazýva chyba „Hodnota nie je k dispozícii“.
Uvidíte to, keď použijete vzorec vyhľadávania a nemôže nájsť hodnotu (preto nie je k dispozícii).
Nasleduje príklad, kde používam vzorec VLOOKUP na nájdenie ceny položky, ale vráti chybu, keď nemôže nájsť túto položku v poli tabuľky.
#DIV/0! Chyba
K tejto chybe pravdepodobne dôjde, keď je číslo delené 0.
Toto sa nazýva chyba rozdelenia. V nižšie uvedenom príklade dáva #DIV/0! chyba, pretože hodnota množstva (deliteľ vo vzorci) je 0.
#HODNOTA! Chyba
K chybe hodnoty dôjde, keď vo vzorci použijete nesprávny typ údajov.
Napríklad v nasledujúcom príklade, keď sa pokúsim pridať bunky, ktoré majú čísla a znak A, zobrazí sa chyba hodnoty.
Stáva sa to, pretože môžete pridať iba číselné hodnoty, ale namiesto toho som sa pokúsil pridať číslo s textovým znakom.
#REF! Chyba
Toto sa nazýva referenčná chyba a uvidíte to, keď referencia vo vzorci už nie je platná. To môže byť prípad, keď vzorec odkazuje na odkaz na bunku a tento odkaz na bunku neexistuje (stane sa to vtedy, keď odstránite riadok/stĺpec alebo pracovný hárok, na ktoré sa vo vzorci odkazovalo).
V nižšie uvedenom príklade bol pôvodný vzorec = A2/B2 a keď som odstránil stĺpček B, všetky odkazy naň boli #REF! a tiež dalo #REF! chyba v dôsledku vzorca.
#NAME ERROR
Táto chyba je pravdepodobne dôsledkom nesprávne napísanej funkcie.
Ak napríklad namiesto VLOOKUP omylom použijete VLOKUP, zobrazí sa chyba názvu.
#ČÍSLA CHYBA
Ak sa pokúsite vypočítať veľmi veľkú hodnotu v programe Excel, môže dôjsť k chybe. Napríklad = 187^549 vráti číselnú chybu.
Ďalšou situáciou, kde môžete získať chybu NUM, je zadanie neplatného argumentu čísla do vzorca. Ak napríklad vypočítate odmocninu a ako argument zadáte číslo záporné, vráti chybu s číslom.
Napríklad v prípade funkcie Square Root, ak ako argument zadáte záporné číslo, vráti číselnú chybu (ako je uvedené nižšie).
Aj keď som tu ukázal len niekoľko príkladov, môže existovať mnoho ďalších dôvodov, ktoré môžu viesť k chybám v programe Excel. Keď sa v programe Excel zobrazia chyby, nemôžete ich tu nechať. Ak sa údaje ďalej používajú vo výpočtoch, musíte sa uistiť, že s chybami sa zaobchádza správnym spôsobom.
Funkcia Excel IFERROR je úžasný spôsob, ako zvládnuť všetky typy chýb v programe Excel.
Funkcia Excel IFERROR - prehľad
Pomocou funkcie IFERROR môžete určiť, čo má vzorec vrátiť namiesto chyby. Ak vzorec nevráti chybu, vráti sa jeho vlastný výsledok.
Syntax funkcie IFERROR
= IFERROR (hodnota, hodnota_ak_ chyba)
Vstupné argumenty
- hodnota - toto je argument, pri ktorom sa kontroluje chyba. Vo väčšine prípadov ide buď o vzorec, alebo o odkaz na bunku.
- value_if_error - je to hodnota, ktorá sa vráti v prípade chyby. Hodnotené boli nasledujúce typy chýb: #N/A, #REF !, #DIV/0 !, #VALUE !, #NUM !, #NAME ?, a #NULL !.
Doplňujúce Poznámky:
- Ak ako argument value_if_error použijete „“, bunka v prípade chyby nič nezobrazí.
- Ak argument hodnota alebo hodnota_if_error odkazuje na prázdnu bunku, funkcia Excel IFERROR ju považuje za hodnotu prázdneho reťazca.
- Ak je argumentom hodnota vzorec poľa, IFERROR vráti pole výsledkov pre každú položku v rozsahu uvedenom v hodnote.
Funkcia Excel IFERROR - príklady
Tu sú tri príklady použitia funkcie IFERROR v programe Excel.
Príklad 1 - Namiesto chyby vráťte prázdnu bunku
Ak máte funkcie, ktoré môžu vrátiť chybu, môžete to zabaliť do funkcie IFERROR a ako hodnotu, ktorú chcete vrátiť v prípade chyby, zadajte prázdne miesto.
V nižšie uvedenom príklade je výsledkom v D4 #DIV/0! chyba, pretože deliteľ je 0.
V takom prípade môžete namiesto škaredej chyby DIV vrátiť nasledujúci vzorec.
= IFERROR (A1/A2, ””)
Táto funkcia IFERROR skontroluje, či výpočet nevedie k chybe. Ak áno, jednoducho vráti prázdne miesto, ako je uvedené vo vzorci.
Tu môžete tiež zadať akýkoľvek iný reťazec alebo vzorec, ktorý sa má zobraziť namiesto prázdneho.
Nasledujúci vzorec napríklad namiesto prázdnej bunky vráti text „Chyba“.
= IFERROR (A1/A2, „chyba“)
Poznámka: Ak používate Excel 2003 alebo staršiu verziu, funkciu IFERROR v ňom nenájdete. V takýchto prípadoch musíte použiť kombináciu funkcie IF a funkcie ISERROR.
Príklad 2 - Keď VLOOKUP nemôže nájsť hodnotu, vráťte „Nenašlo sa“
Keď použijete funkciu Excel VLOOKUP a nemôže nájsť vyhľadanú hodnotu v určenom rozsahu, vráti chybu #N/A.
Nasleduje napríklad súbor údajov o názvoch študentov a ich známkach. Na načítanie známok troch študentov (v D2, D3 a D4) som použil funkciu VLOOKUP.
Aj keď vzorec VLOOKUP vo vyššie uvedenom príklade nájde mená prvých dvoch študentov, nemôže nájsť Joshovo meno v zozname, a preto vracia chybu #N/A.
Tu môžeme použiť funkciu IFERROR na vrátenie prázdneho alebo nejakého zmysluplného textu namiesto chyby.
Nasleduje vzorec, ktorý namiesto chyby vráti hodnotu „Nenašlo sa“.
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 12,2,0), „Nenašlo sa“)
Pri VLOOKUP môžete tiež použiť IFNA namiesto IFERROR. Aj keď by IFERROR spracovával všetky druhy chybových hodnôt, IFNA by fungoval iba na chybách #N/A a nefungoval by s inými chybami.
Príklad 3 - V prípade chyby vráťte 0
Ak v prípade chyby nezadáte hodnotu, ktorú má IFERROR vrátiť, automaticky vráti hodnotu 0.
Ak napríklad vydelím 100 hodnotou 0, ako je uvedené nižšie, vráti sa chyba.
Ak však použijem nižšie uvedenú funkciu IFERROR, vráti namiesto toho hodnotu 0. Všimnite si toho, že po prvom argumente stále musíte použiť čiarku.
Príklad 4 - Použitie vnoreného IFERRORU s VLOOKUP
Niekedy pri použití funkcie VLOOKUP bude možno potrebné pozrieť sa na fragmentovanú tabuľku polí. Predpokladajme napríklad, že máte záznamy o predajných transakciách v 2 samostatných pracovných listoch a chcete vyhľadať číslo položky a zistiť jej hodnotu.
Na to je potrebné použiť vnorený IFERROR s VLOOKUP.
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie:
V tomto prípade musíte na nájdenie skóre pre Grace použiť nasledujúci vnorený vzorec IFERROR:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), „Nenašlo sa“))
Tento druh vnorenia vzorcov zaisťuje, že získate hodnotu z jednej z tabuliek a spracujú sa všetky vrátené chyby.
Všimnite si toho, že v prípade, že sú tabuľky na rovnakom pracovnom hárku, v skutočnom príklade to bude pravdepodobne na rôznych pracovných hárkoch.
Excel IFERROR Funkcia - VIDEO
- Excel A funkcia.
- Excel ALEBO funkcia.
- Excel NIE Funkcia.
- Funkcia IF IF.
- Excel IFS funkcia.
- Funkcia Excel FALSE.
- Funkcia Excel TRUE.
