Prevence chyb vzorců v aplikaci Excel pomocí IFERROR nebo IFNA. Jsou lepší než staré ISERROR ISERR a ISNA. Více informací zde.
Chyby vzorce mohou být běžné. Pokud máte datovou sadu se stovkami záznamů, musí se tu a tam vynořit dělení na nulu nebo # N / A.
V minulosti vyžadovalo předcházení chybám herkulovské úsilí. Vědomě kývněte hlavou, pokud jste někdy vyrazili =IF(ISNA(VLOOKUP(A2,Table,2,0),"Not Found",VLOOKUP(A2,Table,2,0)). Kromě toho, že je psaní opravdu dlouhé, vyžaduje toto řešení, aby aplikace Excel provedla dvakrát tolik VLOOKUPů. Nejprve provedete VLOOKUP, abyste zjistili, zda VLOOKUP způsobí chybu. Pak znovu provedete stejný VLOOKUP, abyste získali výsledek bez chyby.
Excel 2010 představil výrazně vylepšený = IFERROR (vzorec, hodnota v případě chyby). Vím, že IFERROR zní jako staré staré funkce ISERROR, ISERR, ISNA, ale je to úplně jiné.
To je brilantní funkce: =IFERROR(VLOOKUP(A2,Table,2,0),"Not Found"). Pokud máte 1 000 VLOOKUPŮ a pouze 5 návratových # N / A, pak 995, které fungovaly, vyžaduje pouze jeden VLOOKUP. Je to jen 5, které vrátily # N / A, které potřebují přejít k druhému argumentu IFERROR.
Kupodivu Excel 2013 přidal funkci IFNA (). Je to jako IFERROR, ale vyhledává pouze chyby # N / A. Lze si představit podivnou situaci, kdy je nalezena hodnota ve vyhledávací tabulce, ale výslednou odpovědí je dělení 0. Pokud jste chtěli z nějakého důvodu zachovat chybu dělení nulou, udělá to IFNA ().
Osoba, která sestavila vyhledávací tabulku, měla samozřejmě použít IFERROR, aby na prvním místě zabránila dělení nulou. Na obrázku níže je „nm“ kód bývalého manažera pro „není smysluplné“.
Díky Justinovi Fishmanovi, Stephenovi Gilmerovi a Excelu Joe.
Sledovat video
- Za starých časů byste použili
=IF(ISNA(Formula),0,Formula) - Počínaje Excelem 2010,
=IFERROR(Formula,0) - IFERROR ale zachází s chybami DIV / 0, to samé bylo # N / A! chyby
- Počínaje Excelem 2013 použijte
=IFNA(Formula,0)k detekci pouze # chyb N / A - Díky Justinovi Fishmanovi, Stephenovi Gilmerovi a Excelu Joe.
Přepis videa
Naučte se Excel z podcastu, epizoda 2042 - IFERROR a IFNA!
Budu podcastovat všechny své tipy z této knihy, kliknutím na „i“ v pravém horním rohu zobrazíte celý seznam skladeb!
Dobře, vraťme se do starých časů, Excel 2007 nebo dříve, pokud jste potřebovali zabránit # N / A! z vzorce VLOOKUP, jako je tento, jsme dělali tuto šílenou věc. Naberte všechny znaky VLOOKUPu, až na =, Ctrl + C a vložte jej do schránky, = IF (ISNA (, stisknutím klávesy End se dostanete na konec, pokud je to # N / A !, pak jsme řekněte „Nenalezeno“, čárka, jinak provedeme VLOOKUP! Správně, vložím to tam a podívám se, jak dlouhý je tento vzorec, Ctrl + Enter, přidej a) přímo tam, Ctrl + Enter, v pořádku, to je milé. Dobře, ale problém je, že i když řeší problém # N / A! Každý vzorec dělá VOOKUP dvakrát. Nechce, abychom řekli: „Hele, je to chyba? Oh, ne, není to chyba. Pojďme to udělat znovu! “Všechny tyto SVYHLEDÁNÍ tedy trvá dvakrát déle. V aplikaci Excel 2010dávají nám úžasný a úžasný vzorec IFERROR!
Nyní vím, že to zní jako to, co jsme měli dříve, ISERROR nebo ISERR, ale toto je IFERROR. A jak to funguje, vezměte jakýkoli vzorec, který by mohl vrátit chybu, a pak řeknete = IFERROR, je tu vzorec, čárka, co dělat, pokud se jedná o chybu, tedy „Nenalezeno“. Dobře, krásná věc na tom je, řekněme, že jste měli udělat tisíc VLOOKUPů a 980 z nich funguje. U modelu 980 to prostě dělá VLOOKUP, není to chyba, vrací odpověď, nikdy neprovede druhý VLOOKUP, to je krása IFERROR. Excel IFERROR přišel v aplikaci Excel 2010, ale samozřejmě je tu opravdu hloupý problém, že samotná tabulka vrací chybu. Správně, někdo měl 0 množství, výnosy / množství, a tak dostáváme # DIV / 0! chyba tam, a tato chyba bude také detekována jako chyba IFERROR. V pořádku,a bude to vypadat, že není nalezen, je nalezen, je to jen to, že kdokoli postavil tento stůl, nedělal dobrou práci při vytváření tohoto stolu.
Dobře, volba č. 1, Excel 2013 nebo novější, přepněte na funkci, kterou přidali v roce 2013 a která nevyhledává všechny chyby, hledá pouze # N / A! Ctrl + Enter a nyní pro ExcelIsFun dostaneme Not Found, ale vrací # DIV / 0! chyba. Opravdu to, co bychom měli dělat, je zde v tomto vzorci, měli bychom tento vzorec zpracovávat, abychom na prvním místě zabránili tomuto dělení nulou. Takže = IFERROR, toto funguje pro všechny druhy věcí, stiskněte klávesu End, abyste se dostali na konec, čárku a pak co dělat? Vždy bych sem dal průměrnou cenu nulu.
Jednou jsem měl manažerku, Susannu (?): „To není matematicky správné, musíte dát„ nm “, protože to nemá smysl.“ Přesně takhle je bláznivé, jak dlouho mě můj manažer zbláznil jejich bláznivým požadavkem, sso, pojďme to zkopírovat, Paste Special Formulas, alt = "" ES F. Nyní se nám zobrazí „nesmyslná“ chyba, „ Not Found “našel IFERROR, a„ not sense “je ve skutečnosti výsledkem VLOOKUP. Dobře, takže pár různých způsobů, jak se vydat, je pravděpodobně bezpečné použít IFNA, pokud máte Excel 2013 a všichni, se kterými sdílíte svůj sešit, mají Excel 2013. Tato kniha a spousta dalších jsou v této knize, kape s pikantními tipy, 200 stran, snadno čitelné, plnobarevné úžasné, úžasné knihy. Podívejte se na to, klikněte na „I“ v pravém horním rohu,knihu si můžete koupit.
Dobře, rekapitulace epizody: Za starých časů jsme používali dlouhý formát = IF (ISNA (vzorec, 0, jinak vzorec, vzorec byl vypočítán dvakrát, což je pro VLOOKUP hrozné. Počínaje Excelem 2010, = IFERROR , stačí dát vzorec jednou, čárka, co dělat, když se jedná o chybu. IFERROR však zachází s chybami # DIV / 0! stejně jako s chybami # N / A! takže od aplikace Excel 2013 funguje funkce IFNA stejně jako IFERROR, ale detekuje pouze chyby # N / A!
Mimochodem, tento tip navrhli čtenáři Justin Fishman, Stephen Gilmer a Excel od Joe. Děkujeme jim za to, že to navrhli, a díky vám, že jste se zastavili, uvidíme se příště pro další netcast od!
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2042.xlsm
