Предотвратяване на грешки във формули в Excel с помощта на IFERROR или IFNA. Те са по-добри от старите ISERROR ISERR и ISNA. Научете повече тук.
Грешките във формулата могат да бъдат често срещани. Ако имате набор от данни със стотици записи, от време на време непременно ще изскачат деление на нула или # N / A.
В миналото предотвратяването на грешки изискваше херкулесови усилия. Кимайте съзнателно, ако някога сте нокаутирали =IF(ISNA(VLOOKUP(A2,Table,2,0),"Not Found",VLOOKUP(A2,Table,2,0)). Освен че е наистина дълго за въвеждане, това решение изисква Excel да направи двойно повече VLOOKUP. Първо, правите VLOOKUP, за да видите дали VLOOKUP ще генерира грешка. След това правите отново същия VLOOKUP, за да получите резултата без грешка.
Excel 2010 представи значително подобрения = IFERROR (формула, стойност при грешка). Знам, че IFERROR звучи като старите стари функции ISERROR, ISERR, ISNA, но е съвсем различно.
Това е блестящ функция: =IFERROR(VLOOKUP(A2,Table,2,0),"Not Found"). Ако имате 1000 VLOOKUP и само 5 връщате # N / A, тогава 995, който е работил, изисква само един VLOOKUP. Само 5, които са върнали # N / A, трябва да преминат към втория аргумент на IFERROR.
Странно, Excel 2013 добави функцията IFNA (). Това е точно като IFERROR, но търси само # N / A грешки. Може да си представим странна ситуация, при която стойността в справочната таблица е намерена, но полученият отговор е разделение на 0. Ако искате да запазите грешка разделяне на нула по някаква причина, тогава IFNA () ще направи това.
Разбира се, човекът, който е създал справочната таблица, е трябвало да използва IFERROR, за да предотврати разделянето на нула на първо място. На фигурата по-долу „nm“ е бивш код на мениджър за „не е смислен“.
Благодарение на Джъстин Фишман, Стивън Гилмър и Excel от Джо.
Гледам видео
- В старите дни бихте използвали
=IF(ISNA(Formula),0,Formula) - Започвайки от Excel 2010,
=IFERROR(Formula,0) - Но IFERROR третира DIV / 0 грешки, същото е # N / A! грешки
- От Excel 2013 използвайте, за
=IFNA(Formula,0)да откривате само # N / A грешки - Благодарение на Джъстин Фишман, Стивън Гилмър и Excel от Джо.
Видео транскрипция
Научете Excel от подкаст, епизод 2042 - IFERROR и IFNA!
Ще подкастирам всичките си съвети от тази книга, щракнете върху „i“ в горния десен ъгъл, за да стигнете до целия плейлист!
Добре, нека се върнем към старите дни, Excel 2007 или преди, ако е трябвало да предотвратите # N / A! от формула на VLOOKUP като тази, ние правехме това лудо нещо. Вземете всички символи на VLOOKUP, смесете с изключение на =, Ctrl + C, за да го поставите в клипборда, = IF (ISNA (, натиснете клавиша End, за да стигнете до края, ако е # N / A!, Тогава ние кажете “Not Found”, запетая, в противен случай правим VLOOKUP! Точно така, поставям го там и вижте колко е дълга тази формула, Ctrl + Enter, добавете а) точно там, Ctrl + Enter, добре вижте, това е сладко. Добре, но проблемът е, че докато решава проблема с # N / A! Всяка една формула прави VOOKUP два пъти. Не иска да кажем „Хей, това грешка ли е? О, не, не е грешка. Нека да го направим отново! ”Така отнема два пъти повече време, за да се направят всички тези VLOOKUPs.те ни дават страхотната, страхотна формула на IFERROR!
Сега знам, че това звучи като това, което сме имали преди, ISERROR или ISERR, но това е IFERROR. И по начина, по който работи, вземете всяка формула, която може да върне грешка, и тогава казвате = IFERROR, има формулата, запетая, какво да направите, ако е грешка, така че „Не е намерено“. Добре сега, красивото нещо в това е, да предположим, че сте имали хиляди VLOOKUP да направите и 980 от тях работят. За 980 той просто прави VLOOKUP, това не е грешка, той връща отговора, никога не продължава да прави втория VLOOKUP, това е красотата на IFERROR. IFERROR на Excel се появи в Excel 2010, но разбира се, един наистина глупав проблем тук е, че самата таблица връща грешка. Да, някой е имал 0 количество, приходи / количество и така получаваме # DIV / 0! грешка там и тази грешка също ще бъде открита като грешка от IFERROR. Добре,и ще изглежда, че не е намерен, той е намерен, просто този, който е построил тази маса, не е свършил добра работа по изграждането на тази маса.
Добре, избор №1, Excel 2013 или по-нов, преминете към функцията, която те добавиха през 2013 г., която не търси всички грешки, а търси само # N / A! Ctrl + Enter и сега ще получим Not Found for ExcelIsFun, но връща # DIV / 0! грешка. Наистина обаче това, което трябва да правим, е тук в тази формула, трябва да боравим с тази формула, за да предотвратим това разделяне на нула на първо място. И така = IFERROR, това работи за всякакви неща, натиснете клавиша End, за да стигнете до края, запетая и след това какво да правите? Винаги бих поставил нула тук като средна цена.
Веднъж имах мениджър, Сузана (?), „Това не е математически правилно, трябва да въведете„ nm “, за да няма смисъл.“ Точно така, лудост е колко дълго моят мениджър просто ме побърква с безумната им молба, sso, нека копираме това, Поставете специални формули, alt = "" ES F. Сега получаваме грешка „без значение“ тук, „ Не е намерено ”се намира от IFERROR и„ не е смислено ”всъщност е резултат от VLOOKUP. Добре, така че няколко различни начина да отидете, вероятно най-сигурното нещо, което трябва да направите тук, е да използвате IFNA, при условие че имате Excel 2013 и всеки, с когото споделяте работната си книга, има Excel 2013. Тази книга, плюс цял куп други са в тази книга, капе с пикантни съвети, 200 страници, лесна за четене, страхотна пълноцветна, страхотна книга. Проверете го, кликнете върху „I“ в горния десен ъгъл,можете да си купите книгата.
Добре, резюме на епизода: Навремето бихме използвали дълъг формат = IF (ISNA (формулата, 0, иначе формулата, формулата беше изчислена два пъти, което е ужасно за VLOOKUPs. От Excel 2010, = IFERROR , просто сложете формулата веднъж, запетая, какво да правите, ако е грешка. IFERROR обаче третира грешки # DIV / 0! по същия начин като грешки # N / A!, така че стартирайки Excel 2013 функцията IFNA работи точно като IFERROR, но ще открие само грешките # N / A!
Този съвет между другото, предложен от читателите Джъстин Фишман, Стивън Гилмър и Excel от Джо. Благодарим им, че предложиха това, и благодарим на вас, че се отбихте, ще се видим следващия път за поредното излъчване от!
Свали файл
Изтеглете примерния файл тук: Podcast2042.xlsm
