Виправлення помилки #VALUE! у формулі з функцією IF

IF – одна з найуніверсальніших і найпопулярніших функцій Excel. Її можна використовувати кілька разів в одній формулі, а також поєднувати з іншими функціями. Проте через можливість будувати складні інструкції IF дуже легко отримати помилку #VALUE!. Зазвичай її можна уникнути додаванням до формули спеціальних функцій для обробки помилок, як-от ISERROR, ISERR або IFERROR.

Проблема: аргумент посилається на значення помилки

Якщо посилання на значення помилки містить посилання на клітинку, функція IF відображає #VALUE! помилку #REF!.

Вирішення. Ви можете використовувати будь-яку формулу обробки помилок, як-от ISERROR, ISERR або IFERROR разом із функцією IF. У наведених нижче статтях пояснюється, як використовувати функції IF, ISERROR та ISERR або IFERROR у формулі, коли аргумент посилається на значення помилок.

Примітки.:

Функція IFERROR, яка вперше з’явилася в Excel 2007, значно популярніша за ISERROR та ISERR, адже за її використання формула не містить надлишкових елементів. Функції ISERROR та ISERR передбачають подвійне обчислення формули: спочатку потрібно перевірити, чи не повертатиметься помилка, а потім – обчислити результат. Функція IFERROR потребує лише одного обчислення.
Конструкція =IFERROR(формула;0) значно краща за =IF(ISERROR(формула;0;формула)).

Проблема: хибний синтаксис

Якщо синтаксис функції неправильно побудовано, вона може повернути #VALUE! помилку #REF!.

Вирішення. Переконайтеся, що синтаксис будується належним чином. Ось приклад правильно побудованої формули, яка вкладає функцію IF в іншу функцію IF для обчислення відрахувань на основі рівня доходу.

=IF(E2<31500;E2*15%;IF(E2<72500;E2*25%;E2*28%))

Приклад правильної інструкції IF

Цю формулу можна прочитати так: ЯКЩО (значення в клітинці A5 менше за 31 500, помножити його на 15%. ЯКЩО це не так, перевірити, чи значення менше за 72 500. ЯКЩО це так, помножити його на 25%, в іншому разі – на 28%).

Щоб додати функцію IFERROR до наявної формули, просто вставте всю формулу в дужки функції IFERROR:

=IFERROR(IF(E2<31500;E2*15%;IF(E2<72500;E2*25%;E2*28%));0)

Це просто свідчить про те, що якщо будь-яка частина вихідної формули повертає помилку, а потім відображає 0, в іншому разі повертається результат інструкції IF. Деякі користувачі пишуть свої формули в комплекті з обробкою помилок, щоб почати, однак це не варто, оскільки обробник помилок придушить будь-які потенційні помилки, тому ви не обов'язково знатимете, чи працює формула належним чином. Якщо потрібно додати обробку помилок, радимо додати її, переконавшись, що формула працює належним чином.

Примітка.: Значення обчислення у формулах не містять роздільників. Якщо додати їх, функція IF спробує використати їх як аргументи, і в Excel станеться помилка. Проте відсоткові множники слід записувати із символом %. Для Excel це означає, що ці числа потрібно представити у вигляді відсотків. В іншому разі знадобиться ввести їх у вигляді їхніх фактичних відсоткових значень, наприклад "E2*0,25".

Повідомлення Excel, яке з’являється в разі додавання коми до значення