Microsoft Excel - це потужне та універсальне додаток для електронних таблиць, яке чудово підходить для відстеження та управління усім, від корпоративного інвентарю, до бюджетів малого бізнесу, до особистої пристосованості. Однією з переваг Excel є те, що ви можете заздалегідь налаштувати формули, які автоматично оновлюються під час введення нових даних. Деякі формули, на жаль, математично неможливі без необхідних даних, що призводить до помилок у вашій таблиці, таких як # DIV / 0 !, # VALUE !, #REF !, та #NAME ?. Ці помилки, не обов'язково шкідливі, відображатимуться у вашій електронній таблиці до виправлення або до введення необхідних даних, що може зробити загальну таблицю менш привабливою та складнішою для розуміння. На щастя, принаймні у випадку відсутніх даних ви можете приховати помилки Excel за допомогою допомоги IF та ISERROR. Ось як це зробити.
Ми використовуємо невелику електронну таблицю відстеження втрат ваги як приклад таблиці таблиці, яка б створювала помилку розрахунку (відсоток втрати ваги) під час очікування нових даних (наступні зважування).
Наш приклад електронної таблиці чекає на введення в стовпці " Вага ", а потім автоматично оновлює всі інші стовпці на основі нових даних. Проблема полягає в тому, що стовпець "Відсоткова втрата " покладається на значення " Зміни", яке не оновлювалось тижнями, в яких ще не було введено ваги, в результаті чого # DIV / 0! помилка, яка вказує на те, що формула намагається розділити на нуль. Ми можемо вирішити цю помилку трьома способами:
- Ми можемо вилучити формулу з тижнів, протягом яких не було введено жодної ваги, а потім вручну додавати її назад в кожний тиждень. Це буде працювати в нашому прикладі, оскільки електронна таблиця порівняно невелика, але не була б ідеальною для великих та складніших електронних таблиць.
- Ми можемо обчислити відсотки втрачених за допомогою іншої формули, яка не ділиться на нуль. Знову ж таки, це можливо в нашому прикладі, але це не завжди може залежати від електронної таблиці та набору даних.
- Ми можемо використовувати функцію ISERROR, яка в поєднанні з оператором IF дозволяє нам визначити альтернативне значення або обчислення, якщо початковий результат повертає помилку. Це рішення, яке ми вам сьогодні покажемо.
Функція ІСЕРРОР
Сама по собі ISERROR тестує призначену комірку або формулу і повертає "true", якщо результат обчислення або значення комірки є помилкою, а "false" - якщо це не так. Ви можете використовувати ISERROR просто, ввівши обчислення або клітинку в круглі дужки, виконуючи функцію. Наприклад:
ІЗЕРОР ((B5-B4) / C5)
Якщо обчислення (B5-B4) / C5 повертає помилку, ISERROR поверне "true", коли він поєднується з умовною формулою. Хоча це може бути використано різними способами, його, мабуть, найкорисніша роль відіграє в парі з функцією IF.
Функція IF
Функція IF використовується, якщо розміщувати три тести чи значення в дужках, розділених комами: IF (значення, яке підлягає тестуванню, значення, якщо справжнє, значення, якщо помилкове). Наприклад:
ЯКЩО (B5> 100, 0, B5)
У наведеному вище прикладі, якщо значення в комірці B5 перевищує 100 (це означає, що тест є істинним), нуль відображатиметься як значення комірки. Але якщо B5 менше або дорівнює 100 (це означає, що тест помилковий), буде показано фактичне значення B5.
ЯКЩО І ІСЕРРОР поєднуються
Спосіб поєднання функцій IF та ISERROR - це використання ISERROR як тесту для оператора IF. Звернімося до нашої таблиці з втратою ваги як до прикладу. Причина того, що клітина E6 повертає # DIV / 0! помилка полягає в тому, що її формула намагається розділити загальну вагу, втрачену вагою попереднього тижня, яка ще не доступна протягом усіх тижнів і яка фактично діє як спроба розділити на нуль.
Але якщо ми використовуємо комбінацію IF і ISERROR, ми можемо сказати Excel ігнорувати помилки і просто ввести 0% (або будь-яке значення, яке ми хочемо), або просто завершити розрахунок, якщо помилок немає. У нашому прикладі це можна зробити за допомогою наступної формули:
ЯКЩО (ІСЕРРОР (D6 / B5), 0, (D6 / D5))
За допомогою цієї функції ви можете скопіювати її до будь-яких інших комірок, і будь-які помилки будуть замінені нулями. Однак, коли ви вводите нові дані в майбутньому, зачіпаючі комірки автоматично оновлюються до своїх правильних значень, оскільки стан помилок більше не буде істинним.
Майте на увазі, що намагаючись приховати помилки Excel, ви можете використовувати майже будь-яке значення або формулу для всіх трьох змінних у операторі IF. це не повинно бути нульовим чи цілим числом, як у нашому прикладі. Альтернативи включають посилання на абсолютно окрему формулу або вставлення порожнього пробілу, використовуючи два лапки ("") як "справжнє" значення. Для ілюстрації, наступна формула відображатиме порожній пробіл у випадку помилки замість нуля:
ЯКЩО (ІСЕРРОР (D6 / B5), "", (D6 / D5))
Пам'ятайте лише, що висловлювання IF можуть швидко стати довгими і складними, особливо якщо вони з'єднані з ISERROR, і в таких ситуаціях легко замінити круглі дужки або кома. Останні версії формул коду кольорів Excel, коли ви вводите їх, допоможуть вам відслідковувати значення комірок і круглі дужки.
