Під час роботи з даними та формулами в Excel ви обов’язково зіткнетеся з помилками.
Для вирішення помилок Excel надав корисну функцію - функцію IFERROR.
Перш ніж ми перейдемо до механіки використання функції IFERROR в Excel, давайте спочатку розглянемо різні види помилок, з якими можна зіткнутися під час роботи з формулами.
Типи помилок в Excel
Знання помилок у програмі Excel допоможе вам визначити можливі причини та найкращий спосіб їх усунення.
Нижче наведені типи помилок, які ви можете знайти в Excel.
#Помилка N/A
Це називається помилкою "Значення недоступне".
Ви побачите це, коли будете використовувати формулу пошуку, і вона не зможе знайти значення (отже, недоступно).
Нижче наведено приклад, коли я використовую формулу VLOOKUP для визначення ціни товару, але він повертає помилку, коли не може знайти цей елемент у масиві таблиць.
#DIV/0! Помилка
Ви, ймовірно, побачите цю помилку, якщо число поділити на 0.
Це називається помилкою поділу. У наведеному нижче прикладі він дає #DIV/0! помилка, оскільки величина величини (дільник у формулі) дорівнює 0.
#ЦІННІСТЬ! Помилка
Помилка значення виникає, коли у формулі використовується неправильний тип даних.
Наприклад, у наведеному нижче прикладі, коли я намагаюся додати клітинки з числами та символом А, це дає помилку значення.
Це відбувається, оскільки ви можете додавати лише числові значення, але замість цього я спробував додати число з текстовим символом.
#REF! Помилка
Це називається помилкою посилання, і ви побачите це, коли посилання у формулі більше не буде дійсним. Це може бути випадок, коли формула посилається на посилання на клітинку, а посилання на клітинку не існує (це відбувається, коли ви видаляєте рядок/стовпець або аркуш, на які посилається формула).
У наведеному нижче прикладі, хоча початкова формула була = A2/B2, коли я видалив стовпець B, усі посилання на неї стали #REF! і це також дало #REF! помилка в результаті формули.
ПОМИЛКА #NAME
Ця помилка, ймовірно, є результатом помилки в написанні функції.
Наприклад, якщо замість VLOOKUP ви помилково використовуєте VLOKUP, це видасть помилку імені.
#NUM ПОМИЛКА
Число помилок може виникнути, якщо ви спробуєте обчислити дуже велике значення в Excel. Наприклад, = 187^549 поверне числову помилку.
Інша ситуація, коли ви можете отримати помилку NUM-це коли ви надаєте формулі недопустимий аргумент числа. Наприклад, якщо ви обчислюєте квадратний корінь, якщо число і в якості аргументу ви надаєте від’ємне число, воно поверне помилку числа.
Наприклад, у випадку функції квадратного кореня, якщо ви дасте негативне число як аргумент, вона поверне числову помилку (як показано нижче).
Хоча я показав тут лише пару прикладів, може бути багато інших причин, які можуть призвести до помилок у Excel. Коли ви отримуєте помилки в Excel, ви не можете просто залишити їх там. Якщо дані далі використовуються у розрахунках, вам потрібно переконатися, що помилки обробляються належним чином.
Функція Excel IFERROR - це чудовий спосіб впоратися з усіма типами помилок у Excel.
Функція Excel IFERROR - огляд
За допомогою функції IFERROR можна замість помилки вказати, що потрібно повернути формулі. Якщо формула не повертає помилку, повертається її власний результат.
Синтаксис функції IFERROR
= IFERROR (значення, значення_якщо_помилка)
Аргументи введення
- значення - це аргумент, який перевіряється на наявність помилки. У більшості випадків це або формула, або посилання на клітинку.
- value_if_error - це значення, яке повертається, якщо є помилка. Оцінюються такі типи помилок: #N/A, #REF !, #DIV/0 !, #VALUE !, #NUM !, #NAME ?, і #NULL !.
Додаткові нотатки:
- Якщо ви використовуєте “” як аргумент value_if_error, клітинка нічого не відображає у разі помилки.
- Якщо аргумент value або value_if_error посилається на порожню клітинку, функція Excel IFERROR розглядається як порожнє значення рядка.
- Якщо аргументом значення є формула масиву, IFERROR поверне масив результатів для кожного елемента в діапазоні, зазначеному у значенні.
Функція Excel IFERROR - Приклади
Ось три приклади використання функції IFERROR в Excel.
Приклад 1 - Повернути порожню клітинку замість помилки
Якщо у вас є функції, які можуть повернути помилку, ви можете перенести її у функцію IFERROR і вказати порожнє значення як значення для повернення у разі помилки.
У наведеному нижче прикладі результатом у D4 є #DIV/0! помилка, оскільки дільник дорівнює 0.
У цьому випадку ви можете використати таку формулу, щоб повернути порожнє місце замість потворної помилки DIV.
= ПОМИЛКА (A1/A2, "")
Ця функція IFERROR перевірятиме, чи призводить розрахунок до помилки. Якщо це так, він просто повертає пробіл, як зазначено у формулі.
Тут ви також можете вказати будь -який інший рядок або формулу для відображення замість пробілу.
Наприклад, наведена нижче формула повертає текст "Помилка" замість порожньої клітинки.
= IFERROR (A1/A2, "Помилка")
Примітка: Якщо ви використовуєте Excel 2003 або попередню версію, ви не знайдете в ньому функцію IFERROR. У таких випадках вам потрібно використовувати комбінацію функції IF та функції ISERROR.
Приклад 2 - Поверніть "Не знайдено", коли VLOOKUP не може знайти значення
Коли ви використовуєте функцію Excel VLOOKUP, і вона не може знайти значення пошуку у зазначеному діапазоні, це поверне помилку #N/A.
Наприклад, нижче наведено набір даних про імена студентів та їх оцінки. Я використовував функцію VLOOKUP для отримання оцінок трьох студентів (у D2, D3 та D4).
Хоча формула VLOOKUP у наведеному вище прикладі знаходить імена перших двох учнів, вона не може знайти ім’я Джоша у списку, і тому повертає помилку #N/A.
Тут ми можемо використовувати функцію IFERROR, щоб замість помилки повернути пустий або якийсь значущий текст.
Нижче наведена формула, яка замість помилки поверне "Не знайдено".
= ПОМИЛКА (VLOOKUP (D2, $ A $ 2: $ B $ 12,2,0), "Не знайдено")
Зауважте, що ви також можете використовувати IFNA замість IFERROR з VLOOKUP. Хоча IFERROR буде обробляти всі види значень помилок, IFNA працюватиме лише з помилками #N/A і не працюватиме з іншими помилками.
Приклад 3 - Поверніть 0 у разі помилки
Якщо ви не вкажете значення, яке має повертати IFERROR у разі помилки, воно автоматично поверне 0.
Наприклад, якщо я поділю 100 на 0, як показано нижче, це поверне помилку.
Однак, якщо я використовую нижченаведену функцію IFERROR, вона замість цього поверне 0. Зверніть увагу, що після першого аргументу вам все одно потрібно використовувати кому.
Приклад 4 - Використання вкладеного IFERROR з VLOOKUP
Іноді під час використання VLOOKUP може знадобитися переглянути фрагментовану таблицю масивів. Наприклад, припустимо, що у вас є записи транзакцій продажів у 2 окремих робочих аркушах, і ви хочете знайти номер товару та побачити його вартість.
Для цього потрібно використовувати вкладену IFERROR з VLOOKUP.
Припустимо, у вас є набір даних, як показано нижче:
У цьому випадку, щоб знайти оцінку для Grace, вам потрібно скористатися наведеною нижче формулою IFERROR:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), "Not Found"))
Такий тип вкладеності формул гарантує, що ви отримаєте значення з будь -якої з таблиць, і будь -яка повернена помилка обробляється.
Зауважте, що, якщо таблиці знаходяться на одному аркуші, однак у реальному прикладі це, ймовірно, буде на різних аркушах.
Функція Excel IFERROR - ВІДЕО
- Функція Excel І.
- Функція Excel АБО.
- Функція Excel НЕ.
- Функція Excel IF.
- Функція Excel IFS.
- Функція Excel FALSE.
- Функція Excel TRUE.
