Умова якщо в екселі. Як у excel за допомогою вкладених функцій якщо() розрахувати бонус із продажу. Запобігання розподілу на нуль

Функція ЯКЩО()є одним із найпотужніших інструментів у світі функцій. Якщо ви зможете освоїти її в роботі, ви перейдете на новий рівень створення формул.

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

Наприклад, розберемо таку формулу: =ЯКЩО(A1>1000;"багато") . Логічне вираження А1>1000 використовується для перевірки. Допустимо, ви додасте цю формулу в комірку В1. Якщо логічний вираз є істинним (воно є таким, коли число в A1 більше 1000), функція повертає значення «багато», і саме це ви побачите в комірці В1 (якщо А1 менше 1000, ви побачите значення БРЕХНЯ).

Інше просте застосування функції – перевірка для виведення попереджень. Наприклад, якщо у вас є книга з результатами збільшення продажів різних товарів у відсотках. Корисно буде відзначити певним чином товари зі зменшеними продажами. Базова формула для такої позначки є наступним: =ЯКЩО(осередок

Тут комірка- адреса осередку для перевірки, а позначка- певного роду текст для виведення екран для вказівки на негативне значення. Ось приклад: =ЯКЩО(В1

Трохи більш покращена версія позначки, в якій вона залежатиме від величини негативного значення, виглядає так: ПОВТОР("

Обробка помилкового результату

Як ви можете побачити на рис. 4.15, якщо у функції ЯКЩО()логічний вираз приймає негативне значення, функція повертає Брехня як результат. Це не є критичним недоліком, проте робить робочі листи як би «важчими», ніж якби в результаті повертався, наприклад, порожній рядок.

Для того щоб зробити це, вам необхідно використовувати розширений синтаксис функції: ЯКЩО (лог_вираз; значення_якщо_істина; значення_якщо_брехня). Перші два аргументи ми з вами вже знаємо, а останній аргумент значення_якщо_брехнязадає значення для повернення функції ЯКЩО(), коли результат обчислення лог_вираження є хибним.

Наприклад, розглянемо таку формулу: =ЯКЩО(A1>1000;"багато";"мало") . Якщо цього разу комірка A1 містить число, менше або 1000, формула виведе рядок «мало». Для прикладу з відміткою негативних обсягів продажу (див. рис. 4.15) необхідно використовувати таку формулу: =ЯКЩО(B1

Як ви можете судити з рис. 4.16, робочий лист тепер виглядає набагато краще.

Запобігання розподілу на нуль

Як ми вже зазначали раніше, функція повертає помилку #СПРАВ/0!якщо формула намагається поділити якесь значення на 0. Для усунення навіть можливості здійснення такої помилки ви можете скористатися функцією ЯКЩО()для перевірки розподілу.

Наприклад, найпростіше обчислення відсотка валового прибутку за формулою (Доходи – Витрати)/Доходи. Для впевненості в тому, що значення Доходи не дорівнює нулю, використовуйте наступну формулу (передбачається, що замість слів Доходи та Витрати будуть підставлені необхідні значення): =ЯКЩО(Доходи 0; (Доходи - Витрати)/Доходи; "Доходи рівні 0!" ). Якщо логічний вираз Доходи 0вірно, отже, відбудеться обчислення за такою формулою. В іншому випадку функція поверне попереджувальне повідомлення замість помилки.

Важливими завданнями навчального курсу у школі є розвиток в учнів основних розумових операцій (аналіз, синтез, порівняння, узагальнення), нормалізація взаємозв'язку їхньої діяльності з мовленням, формування прийомів розумової роботи: аналіз вихідних даних, планування діяльності, здійснення поетапного та підсумкового самоконтролю, для чого використовується поурочне планування процесу навчання. Велике значення надається вмінню розповідати про виконану роботу з правильним вживанням відповідної термінології та встановленням логічних зв'язків у матеріалі, що викладається.

Функція ЯКЩО відноситься до логічних функцій і є однією з найчастіше використовуваних у роботі.За допомогою цього оператора можна виконувати різні завдання, коли необхідно порівняти якісь дані та видати результат. Функція ЯКЩО в Excel дає можливість використовувати в роботі розгалужені алгоритми, будувати дерево рішень і т.д.

Відео щодо використання функції ЯКЩО в Excel

Приклади використання оператора ЯКЩО

Функція ЯКЩО виглядає так:

ЯКЩО (вираз; істина; брехня).

А тепер трохи докладніше:

Наприклад, можна запровадити у полі C1 цифру 8, а полі D1 написати так: =ЕСЛИ(C1<10; 1; 2). То есть программа будет сравнивать значение в поле C1 с цифрой 10, и когда оно окажется меньше 10 (C1<10), то в поле D1 будет написана единица. В противном случае программа выдаст число 2.

Наступний приклад: є список студентів, а також їх оцінки, які вони отримали за іспитом: 5, 4, 3 та 2. Умова завдання полягає в тому, що для кожного студента потрібно прописати текстовий коментар «здав» або «не здав». Тобто, якщо студент отримав оцінку 3 і вище, то він склав іспит, інакше — не склав. Для розв'язання цього завдання можна написати так: =ЯКІ(C1<3; «не сдал»; «сдал»). Excel будет сравнивать оценку каждого студента, и когда она будет меньше 3 (т.е. двойка), то в соответствующем поле будет написан комментарий «не сдал». Если же оценка будет 3 или выше, то в ячейке будет отмечено, что этот студент сдал экзамен. За таким нескладним принципом працює логічна функція ЯКЩО в Екселі.Варто зазначити, що текстові коментарі обов'язково потрібно брати у лапки.

При використанні функції Якщо в Excel можна застосовувати такі оператори порівняння:

більше (>);
менше (<);
більше чи одно (>=);
менше або дорівнює (<=);
одно (=);
не дорівнює (< >).

Приклади функцій І, АБО

Також при використанні в Екселі функції ЯКЩО є можливість з'єднати її з простими операторами порівняння (на зразок І, АБО). Наприклад, можна написати таку умову: =ЯКЩО(І(A1<=5; A1>3); "проходить", "не проходить"). Виходить така умова: якщо оцінка студента менша або дорівнює 5 і більше 3, то буде показано коментар «проходить», інакше — «не проходить» (проходять лише ті студенти, у яких в оцінках стоять четвірки та п'ятірки).

Більш складний приклад використання функції ЯКЩО - з використанням умов І або АБО

Наступна умова з прикладом оператора АБО: = ЯКЩО (АБО (A1 = 5; A1 = 10); 100; 0). Виходить, що якщо число в комірці А1 дорівнює 5 або 10, то програма видасть 100, інакше - 0.

Крім того, можна застосовувати ці прості оператори для вирішення більш складних завдань. Наприклад, у базі даних необхідно відібрати боржників, які мають понад 10 тисяч рублів і вже не платили понад 6 місяців. Якщо програма знайде таку людину, то нехай видасть критерій «проблемний позичальник».

Допустимо, в осередку A1 знаходиться інформація про термін заборгованості (у місяцях), а в полі B1 – сума заборгованості. Тоді формула виглядатиме так: =ЯКЩО(І(A1>=6; B1>10000); «проблемний позичальник»; «»). Якщо буде знайдено людину, яка підходить під зазначену умову, то програма напише напроти його прізвища коментар «проблемний позичальник», інакше осередок залишиться порожнім.

Якщо один із параметрів вважається критичним, тоді можна скласти формулу так: =ЯКЩО(АБО(А1>=6; B1>10000); «критична ситуація»; «»). Якщо програма знайде збіги хоча б за одним параметром (або термін, або сума заборгованості), користувач побачить повідомлення про те, що ситуація критична. Різниця з попередньою формулою в тому, що в першому випадку повідомлення «проблемний позичальник» видавалося лише тоді, коли виконувались обидві умови.

Інші приклади використання оператора ЯКЩО

Найчастіше у Екселе виникає така помилка, як «СПРАВ/0», тобто. розподіл на 0. Як правило, вона з'являється в тих випадках, коли копіюється формула «A/B», а число B у деяких осередках дорівнює нулю. Цього можна уникнути, якщо використовувати оператор ЯКЩО. І тому необхідно написати так: =ЯКЩО(B1=0; 0; A1/B1). Виходить, що якщо в осередку B1 буде нуль, то Excel одразу ж видасть нуль, інакше програма поділить A1 на B1 і видасть результат.

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

до 1000 - 0%;
від 1001 до 3000 - 3%;
від 3001 до 5000 - 5%;
понад 5001 - 7%.

Наприклад, в Excel є умовна база даних клієнтів та інформація про те, скільки вони витратили на покупки. Завдання у тому, щоб розрахувати їм знижку. Для цього можна написати так: =ЯКІ(A1>=5001; B1*0,93; ЯКЩО(А1>=3001; B1*0,95;..). Суть зрозуміла: перевіряється загальна сума покупок, і коли вона, до Наприклад, більше 5001 рублів, то множиться на 93% вартості товару (осередок B1 * 0,93), коли більше 3001 рублів, то множиться на 95% вартості товару і т. д. легко можна використовувати і на практиці: рівень обсягу продажів рівень знижок встановлюється на ваш розсуд.

Таким чином, застосовувати функцію ЯКЩО можна практично в будь-якій ситуації, функціональність це дозволяє. Головне — правильно скласти формулу, щоб результат не виявився хибним.

Поширене питання щодо Excel «Як записувати кілька умов в одній формулі?». Особливо часто застосовується дві і більше умов використання функції ЯКЩО. Зробити кілька умов у формулі ЯКЩО досить просто, головне знати основні принципи. Їх і обговорюємо нижче.

На мою думку, показово розглянути приклад із вирішенням системи умов. Такі завдання часто задають в інститутах, на парах Excel.

Наприклад, є ось така, досить нагромаджена формула:

Розберемо на прикладі, як перенести її до Excel

Зрозуміло, що ця формула складатиметься з трьох частин, як мінімум:

SIN(B1)^2 =COS(B1) =EXP(1/B1)

Але як записати кілька цих функцій одну, ще й за умовою? Щоб розібратися, докладно подивимося на функцію ЯКЩО.

Її склад наступний:

ЯКЩО (Умова; якщо умова = ТАК (ІСТИНА); якщо умова = НІ (БРЕХНЯ))

Тобто. якщо ми запишемо просту формулу, що ми отримаємо у результаті в осередку B2?

Правильно — відобразитися 100. Якщо ж А1 буде стояти будь-яке інше значення крім 1, то B2 відобразиться б 0.

Повернімося до нашої системи умов. Тепер нам треба розуміти як записати одразу дві умови до першої точки з комою. У нас у B1 порожньо, а значить = 0, і тільки при виконанні обох умов А1 = 1 і B1 = 0 (знак *) значення формули дорівнюватиме 100.

Особливо розберемо між дужками

Оператор І він * означає, що має виконуватися обидві умови одночасно, А1=1 і B1=0.

Якщо між дужками поставити + (або), достатньо буде однієї з умов. Наприклад, якщо А1=1, то вже буде відображатися 100.

Ми готові до написання формули, це робитимемо частинами

Запишемо першу умову

ЯКЩО((B1>-2)*(B1<9);SIN(B1)^2);

Якщо умова виконується, то виконується перша формула із синусом
Якщо ні, друга умова

ЯКЩО((B1>-2)*(B1<9);SIN(B1)^2;ЯКЩО((B1>=9)*(B1<=19);COS(B1)

У всіх інших випадках буде виконуватися формула =EXP(1/B1)
Разом виходить:

ЯКЩО((B1>-2)*(B1<9);SIN(B1)^2;ЕСЛИ((B1>=9) * (B1<=19);COS(B1);EXP(1/B1)))

Запис кількох формул в одній

Якщо в осередку B1 буде текст, формула видасть помилку. Тому я часто застосовую формулу.

Уявімо, що вся наша формула з попереднього пункту це один умовний аргумент А

Тоді = ПОЛИПОМИЛКА (А; »»)

Або для нашого прикладу

ЯСЛИПОМИЛКА(ЯКЩО((B1>-2)*(B1<9);SIN(B1)^2;ЕСЛИ((B1>=9) * (B1<=19);COS(B1);EXP(1/B1)));"")

Приклад можна завантажити

Загальна інформація про ЯКЩО (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)

У стовпці «Виконання плану» в осередку E4 використовуємо таку формулу:

ЯКЩО(D4>=1000000;"Молодець!";"План не виконаний:(")

IF(D4>=1000000;"Молодець!";"План не виконаний:(") .

До речі, в деяких версіях Excel замість ";" має використовуватися ",".

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

Що означають усі аргументи ЯКЩО (IF)?

1. Лог_вираз: D4> = 1000000. У нашому прикладі логічне питання – це порівняння фактичного результату та плану продажів. D4 - це посилання на комірку з фактичним продажем цього менеджера. Excel, Google Sheets, LibreOffice, OpenOffice підставляють замість D4 значення з цієї комірки і перевіряють, чи вірна вказана нерівність. В результаті перевірки у формулі виходить проміжний результат, він використовується для вибору потрібної гілки дерева рішень.

2. Значення_якщо_істина. На наших схемах це ліва гілка дерева рішень. У поточному прикладі значення аргументу - "Молодець!". Цей аргумент показує, що має зробити функція ЯКЩО (IF), коли в результаті обчислення першого аргументу виходить ІСТИНА (TRUE). У цьому прикладі необхідно просто написати текст «Молодець!».

До речі, "Молодець!" у нас написано в лапках, тому що будь-який текст усередині формули має бути написаний у лапках. Винятком є лише назви функцій та іменованих діапазонів. В інших випадках завжди ставте текст у лапки.

3. Значення_якщо_брехня. На наших схемах це права гілка дерева рішень. У поточному прикладі значення аргументу - "План не виконаний:(".) Цей аргумент показує, що має зробити функція ЯКЩО (IF), коли в результаті обчислення першого аргументу виходить брехня (FALSE). У поточному прикладі необхідно просто написати текст "План не виконаний :(».

Тут ми також вказали текст у лапках, тому що, якщо не укладати текст усередині формули в лапки, виникне помилка #ІМ'Я? (#NAME?). Виняток - лише назви функцій та іменованих діапазонів.

По-перше, функція ЯКЩО (IF) відповідає логічне питання (обчислює перший аргумент). По-друге, йде до відповідної гілки дерева рішень. По Александрову П.Ф. виходить так:

1. D4>=1000000, отже перевіряємо 1000329>

2. Ідемо в аргумент Значення_якщо_істина. Потрібно просто підставити текст «Молодець!». Вказуємо текст у комірці. Кінець розрахунків.

Малюнок. Як працює функція ЯКЩО (IF), коли логічний вираз повертає ІСТИНА (TRUE)

1. D5>=1000000, отже перевіряємо 848880>

2. Ідемо в аргумент Значення_якщо_брехня. Потрібно просто підставити текст «План не виконаний:(». Вказуємо текст у комірці. Кінець розрахунків.

Схематично розрахунки виглядають як на малюнку нижче.

Малюнок. Як працює функція ЯКЩО (IF), коли логічний вираз повертає брехню (FALSE)

Приклад 1.2 - обчислення різних формул за допомогою ЯКЩО (IF)

Файл-приклад №1 ви можете завантажити.

ЯКЩО(D4>=1000000;20000+D4*5/100;20000)

або для англомовного Excel, Google Sheets, LibreOffice, OpenOffice:

IF (D4> = 1000000; 20000 + D4 * 5/100; 20000).

Після цього осередок можна буде скопіювати до кінця стовпця, і програма в кожному рядку напише зарплату кожного з менеджерів.

Що саме робить функція ЯКЩО (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 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);"Молодець!";"План не виконаний:(") .

Що саме робить функція ЯКЩО (IF) у цьому прикладі?

По Александрову П.Ф. виходить так:

2. Вкладена функція ЯКЩО (IF) перевіряє логічний вираз: посада менеджера старший менеджер чи ні. Александров П.Ф. - це старший менеджер. Тому логічний вираз у вкладеній ЯКЩО (IF) повертає Брехня (FALSE).

3. Вкладена функція ЯКЩО (IF) переходить до аргументу Значення_якщо_брехня і порівнює фактичні продажі з планом менеджера з продажу. 1 000 329 більше 1 000 000, тому вкладена ЯКЩО (IF) повертає логічний параметр ІСТИНА (TRUE).

По Ільїну М.А. виходить так:

1. Функція ЕСЛИ (IF) починає розрахунок з логічного висловлювання і бачить там вкладену функцію ЕСЛИ (IF). Excel, Google Sheets, LibreOffice, OpenOffice спочатку рахує вкладену функцію.

2. Вкладена функція ЯКЩО (IF) перевіряє логічний вираз: посада менеджера старший менеджер чи ні. Ільїн М.А. - це старший менеджер. Тому логічний вираз у вкладеній ЯКЩО (IF) повертає Брехня (FALSE).

3. Вкладена функція ЯКЩО (IF) переходить до аргументу Значення_якщо_брехня і порівнює фактичні продажі з планом менеджера з продажу. 848 880 менше 1 000 000, тому вкладена ЯКЩО (IF) повертає логічний параметр БРЕХНЯ (FALSE).

Перегляньте побудоване дерево рішень на схемі нижче.

Малюнок. Дерево рішень для функції ЯКЩО (IF) з кількома умовами

По Незенцеву А.А. виходить так:

2. Вкладена функція ЯКЩО (IF) перевіряє логічний вираз: посада менеджера старший менеджер чи ні. Незенецев А.А. - Це старший менеджер. Тому логічний вираз у вкладеній ЯКЩО (IF) повертає ІСТИНА (TRUE).

3. Вкладена функція ЯКЩО (IF) переходить до аргументу Значення_якщо_істина і порівнює фактичні продажі з планом старшого менеджера з продажу. 1 204 346 більше 1 200 000, тому вкладена ЯКЩО (IF) повертає логічний параметр ІСТИНА (TRUE).

4. Результат обчислення вкладеної функції ЯКЩО (IF) передається в основну функцію. Основна функція ЯКЩО (IF) бачить логічний параметр ІСТИНА (TRUE) і переходить до свого (а не до вкладеного) аргументу Значення_якщо_істина. Цей аргумент – просто текст «Молодець!».

Формула з кількома умовами, тобто з вкладеними функціями ЯКЩО (IF), повертає в комірку текст «Молодець!».

Перегляньте побудоване дерево рішень на схемі нижче.

Малюнок. Дерево рішень для функції ЯКЩО (IF) з кількома умовами

За Соколовою Н.І. виходить так:

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;

або для англомовного Excel, Google Sheets, LibreOffice, OpenOffice:

IF(C4="Старший менеджер";D4>=1200000;D4>=1000000);

20000+D4*IF(C4="Старший менеджер";6;5)/100;

Не забувайте, у деяких версіях Excel, замість ";" має використовуватися ",".

На малюнку нижче схематично зображено побудоване дерево розв'язків.

Малюнок. Приклад дерева рішень з декількома умовами і в логічному виразі, і в інших аргументах функції якщо (IF)

Часті помилки при роботі з функцією ЯКЩО (IF)

1. Для функції ЯКЩО (IF) завжди повинен бути вказаний перший аргумент - логічний вираз і другий аргумент - значення, якщо істина. Третій аргумент необов'язковий. Користувачі часто забувають вказати третій аргумент особливо при роботі зі складними формулами, через це в деяких випадках замість потрібного результату в комірці з'являється логічний параметр БРЕХНЯ (FALSE).

2. Складність формули дуже швидко зростає при використанні вкладених ЯКЩО (IF). Через це часто користувачі забувають закрити дужки вкладених обчислень, не ставлять роздільник аргументів («;» або «,»). Залежно від помилки формулу в комірку або вдається записати, або вважається неправильно.

3. У складних формулах з ЯКЩО (IF) дуже важко відстежувати правильність розрахунків: кожна вкладена функція ЯКЩО (IF) додає у ваше дерево рішень одне питання і мінімум дві гілки. У середньому людина в умі тримає до 7 об'єктів, виходить, що при трьох вкладених ЯКЩО (IF) в умі потрібно тримати 3 питання та 6 гілок дерева рішень. Контрольованість та надійність формули стрімко знижується.

Як уникнути цих помилок під час роботи з функцією ЯКЩО (IF)? Мінімізуйте використання ЯКЩО (IF) з іншими функціями і особливо з вкладеними ЯКЩО (IF). Краще робіть проміжні розрахунки у сусідніх осередках.

Порада: робота зі складними формулами

Часто нам доводиться працювати зі складними формулами, формулами яких у одну функцію вкладено інші. Як не помилитись при створенні такої формули? Дієте за наступним алгоритмом:

1. Визначте кінцеву мету ваших розрахунків: який результат ви повинні отримати у результаті.

2. Визначте функцію, яка дозволяє це зробити.

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

5. Якщо вам необхідно виконати проміжні обчислення, то визначаєте кінцеву мету цих обчислень, функцію тощо. Зазвичай завдання проміжних обчислень - це отримати аргумент для основної функції. Пам'ятайте про це, оскільки іноді потрібно отримати аргумент певного типу (саме текст, саме число, саме логічний параметр чи щось інше).

6. Завжди стежте за дужками: як тільки закінчили опис функції, закривайте дужки.

І пам'ятайте, якщо формула надто складна, краще зробити проміжний розрахунок у сусідньому осередку.

Чим доповнити та замінити функцію ЯКЩО (IF)

Замість констант у формулі можна використовувати іменовані діапазони.

Розв'язання задачі з кількома умовами можна спростити за допомогою використання вкладених функцій І (AND), АБО (OR).

Функція ЯКЩО (IF) іноді може бути замінена на функцію ВПР (VLOOKUP) , ГПР (HLOOKUP), ПЕРЕГЛЯД (LOOKUP), ЄЛИПОМИЛКА (IFERROR), СУМІСЛІ (SUMIF) або РАХУНКИ (COUNTIF).

Файл-приклад №1 "Застосування функції ЯКЩО (IF) з однією умовою" ви можете завантажити .

Файл-приклад №2 "Застосування функції ЯКЩО (IF) з кількома умовами".

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

Функція ЯКЩОодна з найпопулярніших і найчастіше використовуваних функцій Excel. Використовуючи її спільно з операторами порівняння та іншими логічними функціями Excel, можна вирішувати складні завдання. У цьому уроці спробуємо розібрати її дію на простих прикладах, а також навчимося використовувати відразу кілька функцій ЯКЩОв одній формулі.

Коротко про синтаксис

Функція ЯКЩОмає лише три аргументи:

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

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

Про те, як задавати умови в Excel, читайте статті: Як встановити просту логічну умову в Excel і Використовуємо логічні функції Excel для завдання складних умов.

Приклад 1

Звернемося до прикладу, наведеному нижче. У цьому прикладі функція ЯКЩОнасамперед перевірять умову A1>25. Якщо це так, то формула поверне текстовий рядок “більше 25”, у будь-якому іншому випадку – “менше або 25”.

Приклад 2

Функція ЯКЩОє дуже гнучкою і її можна застосовувати у різних ситуаціях. Розглянемо ще один приклад. У таблиці нижче наведено результати переатестації співробітників фірми:

У стовпець C нам необхідно виставити результат іспиту, який має містити лише два варіанти: Здавабо Не здав. Ті, хто набрав понад 45 балів – склали іспит, решта немає.

Функція ЯКЩО і кілька умов

Функції ЯКЩОможна вкладати один одного, якщо необхідно розширити варіанти прийняття рішень в Excel. Наприклад, для розглянутого раніше випадку переатестації співробітників потрібно проставити не результат, а оцінку з ряду: Відмінно, Добре і Погано. Оцінка Відмінноставиться за кількості балів більше 60, оцінка добрепри більш ніж 45 і оцінка Поганов інших випадках.

Як бачите, замість другого та третього аргументів функції ЯКЩОможна підставляти нові функції ЯКЩО, тим самим розширюючи кількість умов, які формула може обробити. Таким чином, Ви можете створити потрібну кількість вкладень. Правда є очевидний недолік такої конструкції, після 3-5 вкладень формула стане нечитаною і громіздкою, і з нею буде неможливо працювати.

В Excel існують благородніші інструменти для обробки великої кількості умов, наприклад, функція ВВРабо ПЕРЕГЛЯД.

Отже, у цьому уроці ми розглянули логічну функцію ЯКЩОу всій її красі та прикладах, а також розібрали простий приклад з використанням відразу кількох функцій ЯКЩОв одній формулі. Сподіваюся, що ця інформація була для Вас корисною. Успіхів Вам і великих успіхів у вивченні Microsoft Excel!