Цей посібник демонструє, як використовувати функцію Excel IFERROR для виявлення помилок формули, замінюючи їх іншою формулою, порожнім значенням, 0 або користувацьким повідомленням.
Огляд функції IFERROR
Функція IFERROR перевіряє, чи формула призводить до помилки. Якщо FALSE, повертає вихідний результат формули. Якщо TRUE, поверніть інше вказане значення.
Синтаксис IFERROR
Щоб використовувати функцію робочого аркуша Excel IFERROR, виберіть клітинку та введіть:= ПОМИЛКА (
Зверніть увагу, як виглядають вхідні дані формули IFERROR:
Синтаксис і входи функції IFERROR:
= IFERROR (VALUE, value_if_error)
значення - Вираз. Приклад: 4/A1
value_if_error - Значення або розрахунок для виконання, якщо попереднє введення призвело до помилки. Приклад 0 або "" (пусто)
Що таке функція IFERROR?
Функція IFERROR підпадає під категорію логічних функцій у Microsoft Excel, яка включає ISNA, ISERROR та ISERR. Усі ці функції допомагають виявляти та обробляти помилки формул.
IFERROR дозволяє виконати розрахунок. Якщо розрахунок не призведе до помилки, то відобразиться результат розрахунку. Якщо розрахунок робить призводить до помилки, потім виконується інший розрахунок (або виводиться статичне значення, таке як 0, порожнє або якийсь текст).
Коли б ви використовували функцію IFERROR?
- При поділі чисел уникнути помилок, спричинених діленням на 0
- Під час здійснення пошуку для запобігання помилкам, якщо значення не знайдено.
- Якщо ви хочете виконати інший розрахунок, якщо перший призводить до помилки (наприклад, пошук значення у 2nd таблиці, якщо її немає в першій таблиці)
Помилки формул, які не обробляються, можуть спричинити помилки у вашій книзі, але видимі помилки також роблять вашу таблицю менш помітно привабливою.
Якщо помилка, то 0
Давайте розглянемо базовий приклад. Нижче ви ділите два числа. Якщо ви спробуєте поділити на нуль, ви отримаєте помилку:
Замість цього вставте розрахунок у функцію IFERROR, і якщо поділити на нуль, замість помилки виводиться 0:
= ПОМИЛКА (A2/B2,0)
Якщо помилка, то пусте
Замість того, щоб встановити помилки на 0, ви можете встановити для них значення «порожні» з подвійними лапками («»):
= ПОМИЛКА (A2/B2, "")
Ми розглянемо більше використання IFERROR з функцією VLOOKUP …
ПОМИЛКА з VLOOKUP
Функції пошуку, такі як VLOOKUP, генеруватимуть помилки, якщо значення пошуку не знайдено. Як показано вище, ви можете використовувати функцію IFERROR для заміни помилок пробілами (“”) або 0:
Якщо помилка, то зробіть щось інше
Функція IFERROR також може бути використана для виконання другого розрахунку, якщо перший розрахунок призводить до помилки:
= IFERROR (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), VLOOKUP (A2, LookupTable2! $ A $ 2: $ B $ 4,2, FALSE))
Тут, якщо дані не знаходяться в "LookupTable1", замість цього виконується VLOOKUP у "LookupTable2".
Більше прикладів формул IFERROR
Вкладений IFERROR - VLOOKUP Кілька аркушів
Ви можете розмістити IFERROR всередині іншого IFERROR, щоб виконати 3 окремі обчислення. Тут ми будемо використовувати два IFERROR для виконання VLOOKUP на 3 окремих аркушах:
= IFERROR (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), IFERROR (VLOOKUP (A2, LookupTable2! $ A $ 2: $ B $ 4,2, FALSE), VLOOKUP (A2, LookupTable3! $) A $ 2: $ B $ 4,2, НЕВІРНО)))
Index / Match & XLOOKUP
Звичайно, IFERROR також працюватиме з формулами Index / Match та XLOOKUP.
XLER КУПУВАННЯ IFERROR
Функція XLOOKUP - це розширена версія функції VLOOKUP.
ІНДЕКС ПОМИЛКИ / МАТЧ
Ви також можете шукати значення за допомогою функцій INDEX та MATCH у Excel.
IFERROR у масивах
Формули масиву в Excel використовуються для виконання кількох обчислень за допомогою однієї формули. Припустимо, є три стовпці Рік, Продажі та Середня ціна. Ви можете дізнатися загальну кількість за такою формулою у стовпці Е.
{= SUM ($ B $ 2: $ B $ 4/$ C $ 2: $ C $ 4)}
Формула працює добре, поки діапазон дільника не отримає порожню клітинку або нулі. В результаті ви знову побачите помилку #DIV/0!
Цього разу ви можете використовувати функцію IFERROR так:
{= SUM (IFERROR ($ B $ 2: $ B $ 4/$ C $ 2: $ C $ 4,0))}
Зверніть увагу, що функція IFERROR повинна бути вкладена всередині функції SUM, інакше IFERROR буде застосовуватися до загальної суми, а не до кожного окремого елемента в масиві.
IFNA проти IFERROR
Функція IFNA працює точно так само, як і функція IFERROR, за винятком того, що функція IFNA вловлюватиме лише #помилок. Це надзвичайно корисно під час роботи з функціями пошуку: звичайні помилки формул все одно будуть виявлені, але помилка не з'явиться, якщо значення пошуку не знайдено.
= IFNA (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), "Не знайдено")
Якщо ISERROR
Якщо ви все ще використовуєте Microsoft Excel 2003 або старішу версію, можна замінити IFERROR комбінацією IF та ISERROR. Ось короткий приклад:
= IF (ПОМИЛКА (A2/B2), 0, A2/B2)
IFERROR у Google Таблицях
Функція IFERROR працює точно так само в Google Таблицях, як і в Excel:
Приклади IFERROR у VBA
VBA не має вбудованої функції IFERROR Fucntion, але ви також можете отримати доступ до функції IFERROR Excel з VBA:
Dim n as long n = Application.WorksheetFunction.IfError (Value, value_if_error)
Додаток.Функція робочого аркуша надає доступ до багатьох (не всіх) функцій Excel у VBA.
Зазвичай IFERROR використовується при зчитуванні значень з комірок. Якщо клітинка містить помилку, VBA може видати повідомлення про помилку при спробі обробити значення комірки. Спробуйте це з прикладом коду нижче (де клітинка В2 містить помилку):
Sub IFERROR_VBA () Dim n As Long, m As Long 'IFERROR n = Application.WorksheetFunction.IfError (Діапазон ("b2"). Значення, 0)' No IFERROR m = Range ("b2"). Value End Sub
Код призначає комірку B2 змінній. Друге призначення змінної викликає помилку, оскільки значення комірки - #N/A, але перша працює нормально через функцію IFERROR.
Ви також можете використовувати VBA для створення формули, що містить функцію IFERROR:
Діапазон ("C2"). Формула R1C1 = "= ПОМИЛКА (RC [-2]/RC [-1], 0)"
Обробка помилок у VBA значно відрізняється, ніж у Excel. Як правило, для обробки помилок у VBA ви використовуєте обробку помилок VBA. Обробка помилок VBA виглядає так:
Sub TestWS () MsgBox DoesWSExist ("test") End Sub Function DoesWSExist (wsName As String) Як Boolean Dim ws As Worksheet On Error Resume Next Set ws = Sheets (wsName) 'Якщо помилка WS не існує Якщо Err.Number 0 Тоді DoesWSExist = False Else DoesWSExist = True End якщо увімкнено Помилка GoTo -1 End Функція
Зверніть увагу, що ми використовуємо Якщо Err.Number 0 Тоді щоб визначити, чи сталася помилка. Це типовий спосіб виявлення помилок у VBA. Однак функція IFERROR має певне застосування при взаємодії з осередками Excel.
Практичні вправи IFFROR + приклади
Двічі клацніть всередині комірки, щоб переглянути її формулу та відредагувати.
робити:
зараз видалити приклади електронних таблиць…
перевірити / подумати:
- зображення проти gif зверху
- звернути увагу на інтерактивні приклади на уроці?
- змінити етикетку / змінити стиль кодових блоків…
- TOC зверху? видалити їх із цих сторінок і вручну додати [TOC]?
- розмістити зверху посилання VBA “excel, googlesheets” і, можливо, позбутися TOC? "Функція IFERROR доступна в…"
- як щодо деяких інших зображень … наприклад, значка Excel або аркушів Google або VBA, щоб воно виглядало приємніше
- я думаю, подивіться проект електронної пошти, де використовується логотип Excel, і додайте це десь…. і зробіть його фантастичним заголовком … так само з g аркушами та vba!
- зменшити розмір шрифту на сайті!
- виправити CSS … TOC, область синтаксису … тощо!
додайте кнопку завантаження для завантаження електронної таблиці + запитайте електронного листа ПІСЛЯ завантаження…
або зробіть щось на зразок виробників шаблонів… де вони просять вас заповнити опитування
