Запобігання помилкам формул в 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!, Тоді ми скажіть “Не знайдено”, кома, інакше ми робимо ПЕРЕГЛЯД! Правильно, я вставляю його туди, і подивіться, наскільки довга ця формула, Ctrl + Enter, додайте а) тут же, Ctrl + Enter, гаразд, чудово. Добре, але проблема в тому, що вона вирішує проблему # N / A! Кожна окрема формула робить VOOKUP двічі. Вона не хоче, щоб ми говорили: "Гей, це помилка? О, ні, це не так помилка. Давайте зробимо це ще раз! »Тому для виконання всіх цих VLOOKUP потрібно вдвічі більше часу. У Excel 2010вони дають нам чудову, приголомшливу формулу 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 сторінок, легко читається, повнокольорова чудова, чудова книга. Перевірте це, натисніть "Я" у верхньому правому куті,Ви можете придбати книгу.
Добре, підсумок епізоду: Раніше ми використовували довгий формат = IF (ISNA (формула, 0, інакше формула, формула була розрахована двічі, що жахливо для VLOOKUP. Починаючи з Excel 2010, = IFERROR . але він виявить лише помилки # N / A!
Цю пораду, до речі, запропонували читачі Джастін Фішман, Стівен Гілмер та Excel від Джо. Дякуємо їм за те, що вони запропонували це, і дякуємо вам, що завітали, і ми побачимо вас наступного разу для чергової трансляції від
Завантажити файл
Завантажте зразок файлу тут: Podcast2042.xlsm
