Використання функції IF з функціями AND, OR та NOT в Excel

Applies To Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Інтернету Excel 2024 Excel 2024 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2016 Excel Web App Excel для Windows Phone 10

В Excel функція IF дозволяє виконати логічне порівняння між значенням та очікуваним значенням, перевіривши умову та повертаючи результат, якщо ця умова має значення True або False.

• =ЯКЩО(це істинно, то зробити це, інакше зробити щось ще)

Але що робити, якщо необхідно перевірити кілька умов, де, припустимо, всі умови повинні мати значення ІСТИНА або БРЕХНЯ (І), тільки одна умова повинна мати таке значення (АБО) або ви хочете переконатися, що дані НЕ чи відповідають умові? Ці три функції можна використовувати самостійно, але вони набагато частіше зустрічаються у поєднанні з функцією ЯКЩО.

Використовуйте функцію ЯКЩО разом із функціями І, АБО та НЕ, щоб оцінювати кілька умов.

• ЯКЩО(І()): ЯКЩО(І(лог_вираз1; [лог_вираз2]; …), значення_якщо_істина; [значення_якщо_брехня])))
• ЯКЩО(АБО()): ЯКЩО(АБО(лог_вираз1; [лог_вираз2]; …), значення_якщо_істина; [значення_якщо_брехня])))
• ЯКЩО(НЕ()): ЯКЩО(НЕ(лог_вираз1), значення_якщо_істина; [значення_якщо_брехня])))

лог_вираз (обов'язково)

Умова, яку потрібно перевірити.

значення_якщо_істина (обов'язково)

Значення, яке має повертатися, якщо лог_вираз має значення ІСТИНА.

значення_якщо_брехня (Необов'язково)

Значення, яке має повертатися, якщо лог_вираз має значення брехня.

Загальні відомості про використання цих функцій див.у наступних статтях: І, АБО, НІ. При поєднанні з оператором ЯКЩО вони розшифровуються наступним чином:

• І: =ЯКЩО(І(умова; інша умова); значення, якщо ІСТИНА; значення, якщо БРЕХНЯ)
• АБО: = ЯКЩО (АБО (умова; інша умова); значення, якщо ІСТИНА; значення, якщо БРЕХНЯ)
• НЕ: =ЯКЩО(НЕ(умова); значення, якщо ІСТИНА; значення, якщо БРЕХНЯ)

Приклади

Нижче наведено приклади деяких поширених вкладених операторів IF(AND()), IF(OR()) та IF(NOT()) в Excel. Функції І та АБО підтримують до 255 окремих умов, але рекомендується використовувати лише кілька умов, оскільки формули з великим ступенем вкладеності складно створювати, тестувати та змінювати. Функція НЕ може мати лише одну умову.

Нижче наведено формули з розшифровкою їхньої логіки.

Якщо A2 (25) більше нуля і B2 (75) менше 100, повертається значення ІСТИНА, інакше повертається значення БРЕХНЯ. У цьому випадку обидві умови мають значення ІСТИНА, тому функція повертає значення ІСТИНА.

Якщо A3 ("синій") = "червоний" і B3 ("зелений") дорівнює "зелений", повертається значення ІСТИНА, інакше повертається значення БРЕХНЯ. У цьому випадку тільки одна умова має значення ІСТИНА, тому повертається значення БРЕХНЯ.

Якщо A4 (25) більше нуля або B4 (75) менше 50, повертається значення ІСТИНА, інакше повертається значення БРЕХНЯ. У цьому випадку тільки перша умова має значення ІСТИНА, але оскільки АБО потрібно, щоб тільки один аргумент був істинним, формула повертає значення ІСТИНА.

Якщо значення A5 ("синій") дорівнює "червоний" або значення B5 ("зелений") дорівнює "зелений", повертається значення ІСТИНА, інакше повертається значення БРЕХНЯ.У цьому випадку другий аргумент має значення ІСТИНА, тому формула повертає значення ІСТИНА.

Якщо A6 (25) НЕ більше 50, повертається значення ІСТИНА, інакше повертається значення БРЕХНЯ. У цьому випадку значення не більше 50, тому формула повертає значення ІСТИНА.

Якщо значення A7 ("синій") НЕ дорівнює "червоний", повертається значення ІСТИНА, інакше повертається значення БРЕХНЯ.

Зверніть увагу, що у всіх прикладах є дужка, що закриває, після умов. Аргументи ІСТИНА і БРЕХНЯ відносяться до зовнішнього оператора ЯКЩО. Крім того, ви можете використовувати текстові чи числові значення замість значень ІСТИНА та БРЕХНЯ, які повертаються в прикладах.

Ось кілька прикладів використання операторів І, АБО та НЕ для оцінки дат.

Нижче наведено формули з розшифровкою їхньої логіки.

Якщо A2 більше за B2, повертається значення ІСТИНА, в іншому випадку повертається значення БРЕХНЯ. У цьому випадку 12.03.14 більше 01.01.14, тому формула повертає значення ІСТИНА.

Якщо A3 більше B2 І менше C2, повертається значення ІСТИНА, інакше повертається значення БРЕХНЯ. У цьому випадку обидва аргументи є істинними, тому формула повертає значення ІСТИНА.

Якщо A4 більше B2 АБО менше B2+60, повертається значення ІСТИНА, інакше повертається значення БРЕХНЯ. У цьому випадку перший аргумент дорівнює ІСТИНА, а другий - БРЕХНЯ. Так як для оператора АБО потрібно, щоб один із аргументів був істинним, формула повертає значення ІСТИНА. Якщо ви використовуєте майстер обчислення формул на вкладці Формули, ви побачите, як Excel обчислює формулу.

Якщо A5 не більше B2, повертається значення ІСТИНА, інакше повертається значення БРЕХНЯ.У цьому випадку A5 більша за B2, тому формула повертає значення БРЕХНЯ.

Використання І, АБО та NOT з умовним форматуванням в Excel

Excel можна також використовувати and, OR і NOT, щоб задати умови умовного форматування за допомогою параметра формули. При цьому можна опустити функцію ЯКЩО.

В Excel на вкладці Головна клацніть Умовне форматування > Нове правило. Потім виберіть параметр Використовувати формулу для визначення осередків, що форматуються., введіть формулу та застосуйте формат.

Ось як виглядатимуть формули для прикладів із датами:

Якщо A2 більше B2, відформатувати комірку, інакше не виконувати жодних дій.

Якщо A3 більше B2 І менше C2, відформатувати комірку, інакше не виконувати жодних дій.

Якщо A4 більше B2 АБО менше B2 + 60, відформатувати комірку, інакше не виконувати жодних дій.

Якщо A5 НЕ більше B2, відформатувати комірку, інакше не виконувати жодних дій. У цьому випадку A5 більша за B2, тому формула повертає значення БРЕХНЯ. Якщо змінити формулу на =НЕ(B2>A5), вона поверне значення ІСТИНА, а комірка буде відформатовано.

Примітка: Поширеною помилкою є введення формули до умовного форматування без знака рівності (=). У цьому випадку ви побачите, що в діалоговому вікні Умовне форматування до формули будуть додані знак рівності та лапки : ="OR(A4>B2,A4<>Тому вам потрібно видалити лапки, перш ніж формула відповість належним чином.

Додаткові відомості

Див. також

Ви завжди можете поставити запитання експерту в Excel Tech Community або отримати підтримку у спільнотах.

Функція ЯКЩО (IF): одна та кілька умов, приклади, часті помилки, корисні поради

Загальна інформація про ЯКЩО (IF)
Функція ЯКЩО - це одна з найпопулярніших в Excel функцій. В англомовному Excel, а також Google Sheets, LibreOffice, OpenOffice, ця функція називається IF. ЯКЩО (IF) відноситься до логічних функцій.
Рівень складності за шкалою BRP ADVICE – 2 із 7 . Кожна вкладена ЯКЩО (IF) збільшує складність формули вдвічі.
ЯКЩО (IF) дозволяє побудувати дерево рішень, тобто при виконанні умови виконувати одну дію, а при невиконанні – іншу. При цьому умова має бути питанням, що має варіанти відповіді «так/ні» або «вірно/невірно» (у термінах Excel, Google Sheets, LibreOffice, OpenOffice це «ІСТИНА/БРЕХНЯ» («TRUE/FALSE»).
Щоб розібратися з функцією ЯКЩО (IF), спочатку треба розібратися з тим, що таке логічні функції.
Що таке логічні функції У Excel, Google Sheets, LibreOffice, OpenOffice та інших табличних документах робота логічних функцій ґрунтується на існуванні логічних параметрів. Логічних параметрів два: перший – ІСТИНА (TRUE), другий – БРЕХНЯ (FALSE).
За підсумками використання цих логічних параметрів можна побудувати дерево рішень. У найпростішому варіанті цього дерева буде поставлене питання, відповіддю на яке може бути ІСТИНА (TRUE) або БРЕХНЯ (FALSE), і дано вказівку, що робити в кожному з цих двох випадків. Схематично таке дерево рішень зображено на малюнку нижче.
Малюнок. Найпростіше дерево рішень
Логічні функції дозволяють або побудувати таке дерево рішень, або ставити питання та отримувати логічний параметр. До перших відносяться, наприклад, ЯКЩО (IF), ЯСЛИПОМИЛКА (IFERROR).До других – ЧИСЛО (ISNUMBER), І (AND), АБО (OR).
Excel, Google Sheets, LibreOffice, OpenOffice та більшість інших програмних продуктів дозволяє використовувати логічні параметри ІСТИНА (TRUE) та БРЕХНЯ (FALSE) при виконанні математичних операцій. Найчастіше, ІСТИНА (TRUE) набуває значення 1, БРЕХНЯ (FALSE) набуває значення 0. Хоча іноді ІСТИНА (TRUE) і БРЕХНЯ (FALSE) набувають інших значень, наприклад, при програмуванні в VBA ІСТИНА (TRUE) – це -1, а не 1.
До речі, логічні параметри ще називають булевими на честь англійського математика та логіка Джорджа Буля.
Функція ЯКЩО (IF)
Отже, функція ЯКЩО (IF) дозволяє побудувати дерево рішень. У цього дерева рішень є одне питання на вході та два варіанти дій. Питання обов'язково має два варіанти відповіді: так / ні, вірно / неправильно або в термінах логічних параметрів ІСТИНА (TRUE) / БРЕХНЯ (FALSE).
Питання та два варіанти дій – це і є три аргументи функції ЯКЩО (IF).
Перший аргумент функції ЯКЩО (IF) – логічне питання. У Excel він називається "лог_вираз". Excel, Google Sheets, LibreOffice, OpenOffice автоматично знаходять відповідь на це питання, і ця відповідь має прийняти значення ІСТИНА (TRUE) / БРЕХНЯ (FALSE). Що ж може дати таку відповідь? Найпростіші варіанти – це класичні рівності та нерівності. Наприклад, вираз 12=12 поверне логічний параметр ІСТИНА (TRUE), а нерівність 12>40 поверне логічний параметр БРЕХНЯ (FALSE).
У логічному питанні можна використовувати рівності (ліва і права частина порівнюються за допомогою знака «=»), нерівності (більше – «>», менше – «=», менше або одно «<=»), а також просто не рівно – « <>».
Більш складні логічні питання можна поставити за допомогою вкладених функцій. В результаті обчислення таких вкладених функцій повинен вийти цей логічний параметр ІСТИНА (TRUE) або БРЕХНЯ (FALSE). До таких функцій відносяться, наприклад, ЧИСЛО (ISNUMBER), ЕТЕКСТ (ISTEXT), ЕНД (ISNA), І (AND), АБО (OR), у складних випадках – ще одна ЯКЩО (IF).
Другий і третій аргумент - це функція ЯКЩО (IF) повинна зробити, коли відповідь на питання ІСТИНА (TRUE), а коли БРЕХНЯ (FALSE). Функція ЯКЩО (IF) обчислює або тільки другий аргумент (якщо ІСТИНА (TRUE)), або тільки третій аргумент (якщо БРЕХНЯ (FALSE)).
Розглянемо приклади застосування функції ЯКЩО (IF) з однією або декількома умовами.
Застосування ЯКЩО (IF) з однією умовою Файл-приклад №1 ви можете завантажити за цим посиланням.
Припустимо, в компанії встановлений план з продажу: кожен менеджер повинен продати щонайменше ніж на 1 мільйон рублів на місяць. Оклад менеджера з продажу складає 20 тисяч карбованців. За виконання плану менеджер отримує оклад і премію 5% від фактичного обсягу продажів. При невиконанні плану продажу – лише оклад.
Наприкінці кожного місяця формується таблиця, що містить інформацію про продаж кожного менеджера. Ця таблиця може бути, наприклад, як у малюнку нижче.
Малюнок. Продажі у розрізі менеджерів з продажу за звітний місяць
За допомогою функції ЯКЩО (IF) цю таблицю можна швидко перетворити з простого набору даних про продаж за місяць на звіт, який показуватиме, хто план виконав, хто ні, і яка буде зарплата кожного менеджера. Такий звіт може виглядати як на малюнку нижче.

Малюнок. Звіт за результатами роботи менеджерів з продажу
Для того, щоб автоматично заповнювати стовпець «Виконання плану» та «Зарплата за місяць, руб.» (стовпці E та F відповідно), можна використовувати функцію ЯКЩО (IF).
Приклад 1.1 – підстановка тексту за допомогою ЯКЩО (IF) Файл-приклад №1 ви можете завантажити за цим посиланням.

У стовпці «Виконання плану» в осередку E4 використовуємо таку формулу: =ЯКЩО(D4>=1000000;"Молодець!";"План не виконаний:(") або для англомовного Excel, Google Sheets, LibreOffice, OpenOffice: =IF( D4>=1000000;"Молодець!";"План не виконаний:(") . До речі, в деяких версіях Excel замість ";" повинна використовуватися ",".
Після цього осередок можна буде скопіювати вниз до кінця стовпця, і програма в кожному рядку напише хто молодець, а хто не виконав план.
Що означають всі аргументи ЯКЩО (IF)? 1. Лог_вираз: D4>=1000000. , OpenOffice підставляють замість D4 значення з цієї комірки і перевіряють, чи вірно вказане нерівність. У результаті перевірки у формулі виходить проміжний результат, він використовується для вибору потрібної гілки у дереві рішень.
2. Значення_якщо_істина. На наших схемах це ліва гілка дерева рішень. поточному прикладі необхідно просто написати текст «Молодець!». написано у лапках, тому що будь-який текст усередині формули має бути написаний у лапках.Винятком є ​​лише назви функцій та іменованих діапазонів. В інших випадках завжди ставте текст у лапки.
3. Значення_якщо_брехня. На наших схемах це права гілка дерева рішень. У поточному прикладі значення аргументу – "План не виконаний: (".) Цей аргумент показує, що повинна зробити функція ЯКЩО (IF), коли в результаті обчислення першого аргументу виходить брехня (FALSE). У поточному прикладі необхідно просто написати текст "План не виконаний :(». Тут ми також вказали текст у лапках, тому що, якщо не укладати текст усередині формули в лапки, виникне помилка #ІМ'Я? (#NAME?).
Що саме робить функція ЯКЩО (IF) у цьому прикладі? По-перше, функція ЯКЩО (IF) відповідає логічне питання (обчислює перший аргумент). По-друге, йде до відповідної гілки дерева рішень. По Александрову П.Ф. виходить так: 1. D4> = 1000000, отже перевіряємо 1000329> = 1000000, вираз вірно, отже логічний параметр - це ІСТИНА (TRUE).
2. Ідемо в аргумент Значення_якщо_істина. Потрібно просто підставити текст «Молодець!». Вказуємо текст у комірці. Кінець розрахунків. Аргумент Значение_если_брехня у разі функція ЯКЩО (IF) ігнорує.
Схематично розрахунки виглядають як на малюнку нижче.
Малюнок. Як працює функція ЯКЩО (IF), коли логічний вираз повертає ІСТИНА (TRUE)
По Ільїну М.А. виходить так: 1. D5> = 1000000, отже перевіряємо 848880> = 1000000, вираз не вірно, значить логічний параметр - БРЕХНЯ (FALSE).
2. Ідемо в аргумент Значення_якщо_брехня. Потрібно просто підставити текст «План не виконаний:(». Вказуємо текст у комірці. Кінець розрахунків.Аргумент Значение_если_истина у разі функція ЯКЩО (IF) ігнорує.
Схематично розрахунки виглядають як на малюнку нижче.
Малюнок. Як працює функція ЯКЩО (IF), коли логічний вираз повертає брехню (FALSE)
А ось цей текст - це посилання на завантаження прикладу в Excel 2010-2013. Бажаєте вирішити приклад онлайн? Залишіть заявку, ми вже працюємо над цим. Як завжди, наші вправи працюють в Excel 2007-2013, а розширений функціонал можна використовувати в Excel 2010 та 2013. З його допомогою можна почати будь-яку вправу з початку лише однією кнопкою "Почати заново" на вкладці BRP ADVICE, що з'являється в Excel при відкритті наших вправ. Тільки не забудьте увімкнути макроси.
Приклад 1.2 – обчислення різних формул за допомогою ЯКЩО (IF) Файл-приклад №1 ви можете завантажити за цим посиланням.
У стовпці «Зарплата за місяць, руб.» у осередку F4 використовуємо ось таку формулу: =ЯКЩО(D4>=1000000;20000+D4*5/100;20000) чи англомовного Excel, Google Sheets, LibreOffice, OpenOffice: = IF(D4>=1000000;20000+D 5/100; 20000). Не забувайте, у деяких версіях Excel, замість ";" має використовуватися ",".
Після цього осередок можна буде скопіювати до кінця стовпця, і програма в кожному рядку напише зарплату кожного з менеджерів.
Що саме робить функція ЯКЩО (IF) у цьому прикладі? Функція ЯКЩО (IF) відповідає на логічне питання (обчислює перший аргумент) і переходить до відповідної гілки дерева рішень. По Александрову П.Ф. виходить так: 1. D4> = 1000000, отже перевіряємо 1000329> = 1000000, вираз вірно, отже логічний параметр - це ІСТИНА (TRUE).

2. Ідемо в аргумент Значення_якщо_істина.Потрібно обчислити 20000+D4*5/100 (тобто оклад 20 тисяч і та премія 5% від продажів). Отримуємо 70016, вказуємо це значення в комірці. Кінець розрахунків. Аргумент Значение_если_брехня у разі функція ЯКЩО (IF) ігнорує.
По Ільїну М.А. виходить так: 1. D5> = 1000000, отже перевіряємо 848880> = 1000000, вираз не вірно, значить логічний параметр - БРЕХНЯ (FALSE).

2. Ідемо в аргумент Значення_якщо_брехня. Потрібно просто поставити 20000. Вказуємо число в комірці. Кінець розрахунків. Аргумент Значение_если_истина у разі функція ЯКЩО (IF) ігнорує.
А ось цей текст - це посилання на завантаження прикладу в Excel 2010-2013. Бажаєте вирішити приклад онлайн? Залишіть заявку, ми вже працюємо над цим. Як завжди, наші вправи працюють в Excel 2007-2013, а розширений функціонал можна використовувати в Excel 2010 та 2013. З його допомогою можна почати будь-яку вправу з початку лише однією кнопкою "Почати заново" на вкладці BRP ADVICE, що з'являється в Excel при відкритті наших вправ. Тільки не забудьте увімкнути макроси.
Застосування ЯКЩО (IF) з кількома умовами Приклад 2 – різні умови у логічному виразі Файл-приклад №2 ви можете завантажити за цим посиланням.
У минулому прикладі і менеджери, і старші менеджери мали однаковий план продажів на місяць. Ускладнимо завдання: встановимо підвищений план старшим менеджерам – 1 мільйон 200 тисяч на місяць. Звіт тоді виглядатиме, як на малюнку нижче.

Малюнок. Звіт за результатами роботи менеджерів та старших менеджерів
У цьому випадку в стовпці «Виконання плану» в осередку E4 використовуємо таку формулу: =ЯКЩО(ЯКЩО(C4="Старший менеджер";D4>=1200000;D4>=1000000);"Молодець!";"План не виконаний: (") або для англомовного Excel, Google Sheets, LibreOffice, OpenOffice: =IF(IF(C4="Старший менеджер";D4>=1200000;D4>=1000000);"Молодець!";"План не виконаний:(") . Не забудьте, в деяких версіях Excel, замість ";" повинна використовуватися ",".
Що саме робить функція ЯКЩО (IF) у цьому прикладі? По Александрову П.Ф. виходить так: 1. Функція ЯКЩО (IF) починає розрахунок з логічного виразу і бачить там вкладену функцію ЯКЩО (IF). Excel, Google Sheets, LibreOffice, OpenOffice спочатку рахує вкладену функцію.
2. Вкладена функція ЯКЩО (IF) перевіряє логічний вираз: посада менеджера старший менеджер чи ні. Александров П.Ф. – це старший менеджер. Тому логічний вираз у вкладеній ЯКЩО (IF) повертає Брехня (FALSE).
3. Вкладена функція ЯКЩО (IF) переходить до аргументу Значення_якщо_брехня і порівнює фактичні продажі з планом менеджера з продажу. 1 000 329 більше 1 000 000, тому вкладена ЯКЩО (IF) повертає логічний параметр ІСТИНА (TRUE).
4. Результат обчислення вкладеної функції ЯКЩО (IF) передається в основну функцію. Основна функція ЯКЩО (IF) бачить логічний параметр ІСТИНА (TRUE) і переходить до свого (а не до вкладеного) аргументу Значення_якщо_істина. Цей аргумент – просто текст «Молодець!».
Формула з кількома умовами, тобто з вкладеними функціями ЯКЩО (IF), повертає в комірку текст «Молодець!».
Перегляньте побудоване дерево рішень на схемі нижче.
Малюнок. Дерево рішень для функції ЯКЩО (IF) з кількома умовами
По Ільїну М.А. виходить так: 1.Функція ЕСЛИ (IF) починає розрахунок з логічного висловлювання і бачить там вкладену функцію ЕСЛИ (IF). Excel, Google Sheets, LibreOffice, OpenOffice спочатку рахує вкладену функцію.
2. Вкладена функція ЯКЩО (IF) перевіряє логічний вираз: посада менеджера старший менеджер чи ні. Ільїн М.А. – це старший менеджер. Тому логічний вираз у вкладеній ЯКЩО (IF) повертає Брехня (FALSE).
3. Вкладена функція ЯКЩО (IF) переходить до аргументу Значення_якщо_брехня і порівнює фактичні продажі з планом менеджера з продажу. 848 880 менше 1 000 000, тому вкладена ЯКЩО (IF) повертає логічний параметр БРЕХНЯ (FALSE).
4. Результат обчислення вкладеної функції ЯКЩО (IF) передається в основну функцію. Основна функція ЯКЩО (IF) бачить логічний параметр Брехня (FALSE) і переходить до свого (а не до вкладеного) аргументу Значення_якщо_брехня. Цей аргумент - просто текст "План не виконаний: (". Формула з кількома умовами, тобто з вкладеними функціями ЯКЩО (IF), повертає в комірку текст "План не виконаний: (").
Перегляньте побудоване дерево рішень на схемі нижче.
Малюнок. Дерево рішень для функції ЯКЩО (IF) з кількома умовами
По Незенцеву А.А. виходить так: 1. Функція ЯКЩО (IF) починає розрахунок з логічного виразу і бачить там вкладену функцію ЯКЩО (IF). Excel, Google Sheets, LibreOffice, OpenOffice спочатку рахує вкладену функцію.
2. Вкладена функція ЯКЩО (IF) перевіряє логічний вираз: посада менеджера старший менеджер чи ні. Незенецев А.А. - Це старший менеджер. Тому логічний вираз у вкладеній ЯКЩО (IF) повертає ІСТИНА (TRUE).
3. Вкладена функція ЯКЩО (IF) переходить до аргументу Значення_якщо_істина і порівнює фактичні продажі з планом старшого менеджера з продажу.1 204 346 більше 1 200 000, тому вкладена ЯКЩО (IF) повертає логічний параметр ІСТИНА (TRUE).
4. Результат обчислення вкладеної функції ЯКЩО (IF) передається в основну функцію. Основна функція ЯКЩО (IF) бачить логічний параметр ІСТИНА (TRUE) і переходить до свого (а не до вкладеного) аргументу Значення_якщо_істина. Цей аргумент – просто текст «Молодець!». Формула з кількома умовами, тобто з вкладеними функціями ЯКЩО (IF), повертає в комірку текст «Молодець!».
Перегляньте побудоване дерево рішень на схемі нижче.
Малюнок. Дерево рішень для функції ЯКЩО (IF) з кількома умовами
За Соколовою Н.І. виходить так: 1. Функція ЯКЩО (IF) починає розрахунок з логічного виразу і бачить там вкладену функцію ЯКЩО (IF). Excel, Google Sheets, LibreOffice, OpenOffice спочатку рахує вкладену функцію.
2. Вкладена функція ЯКЩО (IF) перевіряє логічний вираз: посада менеджера старший менеджер чи ні. Соколова Н.І. - Це старший менеджер. Тому логічний вираз у вкладеній ЯКЩО (IF) повертає ІСТИНА (TRUE).
3. Вкладена функція ЯКЩО (IF) переходить до аргументу Значення_якщо_істина і порівнює фактичні продажі з планом старшого менеджера з продажу. 1046625 менше 1200000, тому вкладена ЯКЩО (IF) повертає логічний параметр БРЕХНЯ (FALSE).
4. Результат обчислення вкладеної функції ЯКЩО (IF) передається в основну функцію. Основна функція ЯКЩО (IF) бачить логічний параметр Брехня (FALSE) і переходить до свого (а не до вкладеного) аргументу Значення_якщо_брехня. Цей аргумент - просто текст "План не виконаний: (". Формула з кількома умовами, тобто з вкладеними функціями ЯКЩО (IF), повертає в комірку текст "План не виконаний: (").
Перегляньте побудоване дерево рішень на схемі нижче.
Малюнок.Дерево рішень для функції ЯКЩО (IF) з кількома умовами
Формула для розрахунку заробітної плати у прикладі 3 У стовпці «Зарплата за місяць, руб.» в осередку F4 використовуємо ось таку формулу: =ЯКЩО(ЯКЩО(C4="Старший менеджер";D4>=1200000;D4>=1000000);20000+D4*5/100;20000) або для англомовного Excel, Google Sheets, LibreOffice , OpenOffice: =IF(IF(C4="Старший") менеджер"; D4> = 1200000; D4> = 1000000); 20000 + D4 * 5/100; 20000). Не забудьте в деяких версіях Excel замість ";" має використовуватися ",".
У цьому випадку функція ЯКЩО (IF) працює так само, як і в осередку E4.
Приклад 4 – різні умови і в логічному виразі, і в гілках дерева рішень Файл-приклад №3 ви можете завантажити за цим посиланням.
Отже, у нас є менеджери, старші менеджери. У старших менеджерів план вищий, ніж у звичайних менеджерів. Для того, щоб така модель працювала, часто необхідно додаткове стимулювання для старших менеджерів. Наприклад, премія старшого менеджера збільшується до 6%. Тобто у нас одразу кілька умов: 1. Премія виплачується лише якщо виконано план. 2. Якщо посада старший менеджер, план – 1 мільйон 200 тисяч, інакше – 1 мільйон. 3. Якщо посада старший менеджер, премія – 6%, інакше – 5%.
У результаті виходить звіт, як у малюнку нижче.
Малюнок. Звіт за результатами роботи менеджерів та старших менеджерів
Як вирішити таку задачу за допомогою функції ЯКЩО (IF)?
У комірці F4 можна написати таку формулу: =ЯКЩО(ЯКЩО(C4="Старший менеджер";D4>=1200000;D4>=1000000); 20000+D4*ЯКЩО(C4="Старший менеджер";6;5)/100 ; 20000) або для англомовного Excel, Google Sheets, LibreOffice, OpenOffice: =IF( IF(C4="Старший менеджер";D4>=1200000;D4>=1000000); 20000+D4*IF(C4="Старший менеджер";6;5)/100; 20000) Не забувайте, у деяких версіях Excel, замість ";" має використовуватись ",".
Щоб формулу було простіше прочитати і зрозуміти, ми розбили її на кілька рядків так: є основна функція ЯКЩО (IF), кожен аргумент цієї функції вказаний в окремому рядку.
На малюнку нижче схематично зображено побудоване дерево розв'язків.
Рисунок. Приклад дерева рішень з кількома умовами і в логічному виразі, і в інших аргументах функції (ЯКІ)
Часті помилки при роботі з функцією ЯКЩО (IF) 1. Для функції ЯКЩО (IF) завжди повинен бути вказаний перший аргумент – логічне вираження та другий аргумент – значення якщо істина. Третій аргумент необов'язковий. формулами, через це в деяких випадках замість потрібного результату в комірці з'являється логічний параметр Брехня (FALSE).
2. Складність формули дуже швидко зростає при використанні вкладених Якщо (IF) Через це дуже часто користувачі забувають закрити дужки вкладених обчислень, не ставлять роздільник аргументів («;» або «,»). не вдається записати, або вважається неправильно.
3. У складних формулах з ЯКЩО (IF) дуже важко відстежувати правильність розрахунків: кожна вкладена функція ЯКЩО (IF) додає у ваше дерево рішень одне питання і мінімум дві гілки.У середньому людина в умі тримає до 7 об'єктів, виходить, що при трьох вкладених ЯКЩО (IF) в умі потрібно тримати 3 питання та 6 гілок дерева рішень. Контрольованість та надійність формули стрімко знижується. Як уникнути цих помилок під час роботи з функцією ЯКЩО (IF)? Мінімізуйте використання ЯКЩО (IF) з іншими функціями і особливо з вкладеними ЯКЩО (IF). Краще робіть проміжні розрахунки у сусідніх осередках.
Порада: робота зі складними формулами Часто нам доводиться працювати зі складними формулами, формулами яких в одну функцію вкладені інші. Як не помилитись при створенні такої формули? Дієте за наступним алгоритмом: 1. Визначте кінцеву мету ваших розрахунків: який результат ви повинні отримати у результаті. 2. Визначте функцію, яка дозволяє це зробити. 3. Починайте створення формули з цієї функції, вкажіть її та переходьте до роботи з аргументами. 4. Якщо аргумент простий (число, посилання на комірку), поставте його та переходьте до наступного аргументу. 5. Якщо вам необхідно виконати проміжні обчислення, то визначаєте кінцеву мету цих обчислень, функцію тощо. Зазвичай завдання проміжних обчислень – це отримати аргумент для основної функції. Пам'ятайте про це, тому що іноді потрібно отримати аргумент певного типу (саме текст, саме число, саме логічний параметр чи щось інше). 6. Завжди стежте за дужками: як тільки закінчили опис функції, закривайте дужки.
І пам'ятайте, якщо формула надто складна, краще зробити проміжний розрахунок у сусідньому осередку.
Чим доповнити та замінити функцію ЯКЩО (IF) Замість констант у формулі можна використовувати іменовані діапазони.Розв'язання задачі з кількома умовами можна спростити за допомогою використання вкладених функцій І (AND), АБО (OR). Функція ЯКЩО (IF) іноді може бути замінена на функцію ВПР (VLOOKUP), ГПР (HLOOKUP), ПЕРЕГЛЯД(LOOKUP), ЄЛИСПОМИЛКА (IFERROR), СУМІСЛІ (SUMIF) або РАХУНКИ (COUNTIF).
Швидкі посилання
Файл-приклад №1 "Застосування функції ЯКЩО (IF) з однією умовою" можна завантажити за цим посиланням .
Файл-приклад №2 "Застосування функції ЯКЩО (IF) з кількома умовами" можна завантажити за цим посиланням .
Файл-приклад №3 "Застосування функції ЯКЩО (IF) з кількома умовами у різних аргументах" ви можете завантажити за цим посиланням.

Залишились питання? Пишіть нам у форму зворотного зв'язку і записуйтесь на інтенсивність Excel або курс функцій Excel .

Сподобалася стаття? Дізнайтесь більше раніше за інших: заходьте на нашу сторінку у ВКонтакті та підписуйтесь на новини.

Бажаємо вам успішної роботи!
Ваш Віктор Рибцев та команда Навчального центру BRP ADVICE.