Які бувають помилки в Excel + функція ЕСЛИПОМИЛКА для їх фільтрації

Якщо ви постійно працюєте або плануєте працювати в Excel, вам необхідно знати як фільтрувати помилки. І звичайно ж, у Excel є і така функція.

Тож почнемо!

Які помилки бувають?

Давайте розглянемо кожен тип помилки в Excel і чому вони виникають.

Помилка #Н/Д (#N/A)

Ця помилка виникає тоді, коли Excel не може підвантажити значення. Наприклад, коли його в осередку немає.

На малюнку нижче, я намагаюся порахувати значення, якого в табличці не існує (порожній осередок):

Помилка #ДІЛ/0! (#DIV/0!)

Власне, із назви функції все зрозуміло. Помилка виникає, коли ви намагаєтеся поділити щось на 0.

Помилка #ЗНАЧ! (#VALUE!)

Ця помилка пов'язана із типом даних. Наприклад, коли ви намагаєтеся додати до літерного значення. Ці дані не сумісні для операції додавання.

Приклад нижче:

Помилка #ПОСИЛКА! (#REF!)

З цією помилкою я стикаюся найчастіше. Наприклад, у будь-якій формулі ви послалися на комірку, що містить дані. Але з якихось причин пересунули стовпці або видалили рядки, загалом варіантів багато. Тоді ви побачите цю помилку.

Я видалив стовпець із нашої таблички і вийшло це:

Помилка #ІМ'Я? (#NAME?)

Ця помилка є помилкою імені (зрозуміло з назви), імені функції, найчастіше. Наприклад, ви хочете викликати функцію ВПР, а викликаєте ВП.

Приклад на малюнку нижче:

Помилка #ЧИСЛО! (#NUM!)

Ця помилка може виникнути в тому випадку, якщо ви робите розрахунки великих чисел. Excel має обмеження, тому він поверне вам цю помилку.

А ще ця помилка може виникати, коли ви намагаєтеся розрахувати, що неможливо розрахувати. Наприклад, обчислити корінь із негативного числа.

Ми розглянули лише найпростіші приклади, але я вставив їх у цю статтю для того, щоб ви зрозуміли тип помилок. Звичайно, у вас можуть бути інші ситуації, при яких ви отримуєте вищезгадані помилки. То що ж із ними робити?

Функція ЕСЛИПОМИЛКА хороший метод фільтрації цих помилок.

Функція ЕСЛИПОМИЛКА

Отже, як відомо з назви, функція обробляє помилку і повертає зазначене значення, якщо результаті виконання будь-якої функції виникла помилка.

Синтаксис

=ЯКЛИПОМИЛКА(функція; значення_якщо_помилка)

Вхідні аргументи

• функція – це частина, яка у разі виникнення помилки буде оброблена;

• значення_якщо_помилка — значення, яке буде результатом виконання функції ОСЛИПОМИЛКА за наявності помилки.

Важлива інформація:

• Ви можете вказати «» у другому аргументі функції, тоді, якщо помилка, результатом виконання функції буде порожнє місце.

• Якщо другий аргумент функції – значення масиву, Excel поверне результат функції з кожним значенням масиву.

Варіанти використання

Повернемо порожню комірку замість помилки

Отже, ми розглянемо приклад із поділом на 0.

На малюнку нижче, ми намагаємося ділити число 10 на 0, тому виникає помилка #ДЕЛ/0!.

Спробуємо обробити її!

Використовуємо функцію:

=ЕСЛИПОМИЛКА(A1/A2;””)

Так як в результаті виконання розподілу, ми отримуємо помилку, то функція ЕСЛИПОМИЛКА поверне нам порожнє місце, як ми вказали в другому аргументі функції.

У разі помилки, ви можете вивести будь-що. На ваш вибір.

Давайте зробимо те саме, але щоб повернулося не порожнє місце, а слово «Помилка».

=ЯКИЙ ПОМИЛКА(A1/A2; “Помилка”)

Фільтрування помилки #Н/Д

Наприклад, ви викликаєте функцію ВПР, а в аргументі вказали недоступний осередок. Тобто Excel не може «Підвантажити» значення цього осередку, через це виникає помилка.

На прикладі нижче ви можете побачити, як з'явилася наша помилка:

Функція ВПР неспроможна знайти ім'я останнього студента у списку і через це з'являється помилка #Н/Д.

Давайте фільтруємо її!

Отже, ця функція, в результаті виконання, віддасть нам “Не знайдено” при виникненні помилки.

=ЯКЛИПОМИЛКА(ВПР(D2;$A$2:$B$12;2;0); “Не знайдено”)

Також можна використовувати функцію ЕСНД для обробки помилок типу #Н/Д, але вона працює тільки для цього типу помилок.

Повертаємо 0 у разі виникнення помилки

Мало хто знає, але якщо ми просто не вкажемо другий аргумент нашої функції, то при виникненні помилки в результаті виконання функції ми отримаємо 0.

Наприклад, та ж помилка #ДІЛ/0!:

Використовуємо нашу функцію ЕСЛИПОМИЛКА, але навмисно не будемо вказувати другий аргумент функції. Подивимося, що буде:

Використовуємо ЄЛИСПОМИЛКА спільно з ВПР

При роботі з масивами часто необхідно обробляти помилки, які виникають в результаті виконання функцій відразу на двох листах.

Щоб зробити це, ми можемо використовувати функцію ЕСЛИПОМИЛКА спільно з ВПР.

Допустимо, ми маємо таку табличку:

Давайте знайдемо оцінку для Грейс, для цього використовуємо:

=ЯСЛИПОМИЛКА(ВПР(G3;$A$2:$B$5;2;0);ЯСЛИПОМИЛКА(ВПР(G3;$D$2:$E$5;2;0);”Не знайдено”))

Якщо ви використовуєте таку формулу, всі помилки будуть відфільтровані так, як ви вказали. Навіть якщо ці помилки виникають через виконання функцій на різних аркушах.