Pri práci s údajmi a vzorcami v Exceli sa určite stretnete s chybami.
Na spracovanie chýb poskytuje Excel užitočnú funkciu – funkciu IFERROR.
Predtým, ako pochopíme mechanizmus používania funkcie IFERROR v Exceli, poďme najprv pochopiť rôzne typy chýb, s ktorými sa môžete stretnúť pri používaní vzorcov.
Obsah
Typy chýb v Exceli
Pochopenie chýb v Exceli vám lepšie pomôže určiť možné príčiny a najlepší spôsob, ako ich riešiť.
Tu sú typy chýb, ktoré môžete nájsť v Exceli.
Chyby #N/A
Toto sa nazýva chyba „hodnota nie je k dispozícii“.
Vidíte to, keď použijete vyhľadávací vzorec a hodnota sa nenájde (a preto nie je k dispozícii).
Nižšie je uvedený príklad, kde používam vzorec VLOOKUP na nájdenie ceny položky, ale vráti chybu, keď nemôže nájsť položku v poli tabuľky.
#DIV/0!omyl
Túto chybu môžete vidieť, keď je číslo delené 0.
Toto sa nazýva chyba delenia.V nižšie uvedenom príklade dáva #DIV/0!Chyba, pretože hodnota množstva (deliteľ vo vzorci) je 0.
#HODNOTA!omyl
Chyby hodnoty sa vyskytujú, keď vo vzorci použijete nesprávny typ údajov.
Napríklad v príklade nižšie, keď sa pokúsim pridať bunku s číslami a znakom A, zobrazí sa chyba hodnoty.
Stáva sa to preto, že môžete pridávať iba číselné hodnoty, ale skúšal som pridávať čísla pomocou textových znakov.
#REF! omyl
Toto sa nazýva chyba odkazu a táto chyba sa zobrazí, keď odkaz vo vzorci už nie je platný.To sa môže stať, keď vzorec odkazuje na odkaz na bunku a tento odkaz na bunku neexistuje (stane sa to, keď odstránite riadok/stĺpec alebo hárok, na ktorý sa odkazuje vo vzorci).
V nižšie uvedenom príklade, hoci pôvodný vzorec je =A2/B2, keď odstránim stĺpec B, všetky odkazy naň sa stanú #REF!Dalo to aj #REF!chyba v dôsledku vzorca.
Chyba #NAME?
Táto chyba môže byť spôsobená preklepom vo funkcii.
Ak napríklad omylom použijete VLOKUP namiesto VLOOKUP, zobrazí sa chyba názvu.
Chyby: #NUM
Ak sa v Exceli pokúsite vypočítať veľmi veľkú hodnotu, môžu sa vyskytnúť chyby typu Num.napríklad, = 187 ^ 549 vráti číselnú chybu.
Ďalšou situáciou, kedy sa môže vyskytnúť chyba NUM, je, keď zadáte vzorec s neplatným číselným argumentom.Napríklad, ak počítate druhú odmocninu, vráti číselnú chybu, ak je to číslo a ako argument zadáte záporné číslo.
Napríklad v prípade funkcie druhej odmocniny, ak zadáte záporné číslo ako argument, vráti číselnú chybu (zobrazenú nižšie).
Aj keď som tu uviedol len niekoľko príkladov, chyby v Exceli môžu mať mnoho ďalších príčin.Keď sa v Exceli zobrazí chyba, nemôžete ju tam nechať.Ak sa údaje ďalej používajú vo výpočtoch, musíte zabezpečiť, aby sa s chybami zaobchádzalo správnym spôsobom.
Funkcia Excel IFERROR je skvelý spôsob, ako zvládnuť všetky typy chýb v Exceli.
Funkcia Excel IFERROR - prehľad
Pomocou funkcie IFERROR môžete určiť, čo má vzorec vrátiť namiesto chyby.Ak vzorec nevráti žiadne chyby, vráti svoj vlastný výsledok.
Syntax funkcie IFERROR
=IFERROR(hodnota, hodnota_ak_chyba)
Vstupné parametre
- hodnota - Toto je parameter na kontrolu chýb.Vo väčšine prípadov je to buď vzorec alebo odkaz na bunku.
- hodnota_ak_chyba – Toto je hodnota vrátená pri výskyte chyby.Boli vyhodnotené nasledujúce typy chýb: #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? a #NULL!.
Doplňujúce Poznámky:
- Ak použijete "" ako parameter value_if_error, bunka nezobrazí nič, keď sa vyskytne chyba.
- Ak parameter value alebo value_if_error odkazuje na prázdnu bunku, funkcia Excel IFERROR ju považuje za prázdnu reťazcovú hodnotu.
- Ak je argumentom hodnota vzorec poľa, IFERROR vráti pole výsledkov pre každú položku v rozsahu určenom hodnotou.
Funkcia Excel IFERROR - príklad
Nižšie sú uvedené tri príklady použitia funkcie IFERROR v Exceli.
Príklad 1 - Vráti prázdne bunky namiesto chýb
Ak máte funkciu, ktorá môže vrátiť chybu, môžete ju zabaliť do funkcie IFERROR a zadať prázdne miesto ako hodnotu, ktorá sa má vrátiť, keď sa vyskytne chyba.
V nižšie uvedenom príklade je výsledok v D4 #DIV/0!Chyba pri delení 0.
V tomto prípade môžete použiť nasledujúci vzorec na vrátenie prázdneho miesta namiesto škaredej chyby DIV.
=IFERROR(A1/A2,"")
Táto funkcia IFERROR skontroluje, či výsledkom výpočtu nie je chyba.Ak áno, vráti iba medzery špecifikované vo vzorci.
Tu môžete tiež zadať akýkoľvek iný reťazec alebo vzorec, ktorý sa má zobraziť namiesto prázdneho.
Napríklad vzorec nižšie vráti text „Chyba“ namiesto prázdnej bunky.
=IFERROR(A1/A2,"Chyba")
Poznámka: Ak používate Excel 2003 alebo starší, funkciu IFERROR tam nenájdete.V tomto prípade musíte použiť funkciu IF v spojení s funkciou ISERROR.
Príklad 2 – Vráti „Nenájdené“, keď funkcia VLOOKUP nemôže nájsť hodnotu
Keď použijete funkciu Excel VLOOKUP, vráti sa chyba #N/A, ak sa hodnota vyhľadávania nedá nájsť v zadanom rozsahu.
Napríklad nižšie je súbor údajov mien študentov a ich skóre.Použil som funkciu VLOOKUP na získanie skóre troch študentov (v D2, D3 a D4).
Zatiaľ čo vzorec VLOOKUP v príklade vyššie našiel mená prvých dvoch študentov, nemohol nájsť Joshovo meno v zozname, takže vrátil chybu #N/A.
Tu môžeme použiť funkciu IFERROR na vrátenie prázdneho alebo nejakého zmysluplného textu namiesto chyby.
Nižšie je uvedený vzorec, ktorý namiesto chyby vráti „Nenájdené“.
=IFERROR(VLOOKUP(D2;$A$2:$B$12,2,0;XNUMX;XNUMX);Nenájdené“)
Všimnite si, že vo VLOOKUP môžete použiť aj IFNA namiesto IFERROR.Zatiaľ čo IFERROR spracováva všetky typy chybových hodnôt, IFNA sa vzťahuje iba na chyby #N/A, nie na iné chyby.
Príklad 3 - Návrat 0 pri chybe
Ak nešpecifikujete hodnotu, ktorú IFERROR vráti v prípade chyby, automaticky vráti 0.
Ak napríklad vydelím 100 číslom 0, ako je uvedené nižšie, vráti sa chyba.
Ak však použijem nižšie uvedenú funkciu IFERROR, vráti 0.Upozorňujeme, že za prvým parametrom stále musíte použiť čiarku.
Príklad 4 - Použitie Nested IFERROR s VLOOKUP
Niekedy sa pri používaní funkcie VLOOKUP možno budete musieť pozrieť do tabuľky fragmentácie poľa.Povedzme napríklad, že máte predajné transakcie v 2 samostatných hárkoch a chcete vyhľadať číslo položky a zobraziť jej hodnotu.
Vyžaduje si to použitie vnoreného IFERROR s funkciou VLOOKUP.
Predpokladajme, že máte množinu údajov, ktorá vyzerá takto:
V tomto prípade, aby ste našli Graceino skóre, musíte 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),"Not Found"))
Toto vnorenie vzorcov zaisťuje, že získate hodnoty z ktorejkoľvek tabuľky a spracujete všetky vrátené chyby.
Všimnite si, že ak sú hárky na rovnakom hárku, v reálnom živote to môže byť na inom hárku.
