Функція якщо в excel з декількома умовами. Приклади використання вкладених функцій якщо

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

Приклад завдання з використання декількох функцією ЯКЩО: У нас є звіт з продажу продавців. План продажів вважається за кількістю проданих одиниць товару і становить від 18 до 20 шт. Кожен співробітник отримує оплату 2000 рублів. За кожну додатково продану одиницю товару понад план співробітник отримує 25 рублів, а за невиконання плану - штрафується по 50 рублів за одиницю (наприклад, продав 16 шт, недобравши 2 шт до мінімального плану 18 шт, отже він отримує 2000 рублів мінус 2 * 50 рублів тобто 1900 рублів). Нам необхідно розрахувати оплату для кожного співробітника.

Для зручності винесемо всі умови в окрему таблицю, щоб при необхідності ми могли швидко їх міняти якщо, наприклад, зміниться оплата або план

Нам потрібно заповнити стовпець «Оплата». У нас кілька умов, тому одним якщо обійтися не вийде. Ось як ми розглядатиме ці умови

• якщо продажів більше 20, то вважаємо оплату за перевиконання, інакше перевіряємо
• якщо продажів менше 18, то вважаємо оплату за невиконання, інакше було від 18 до 20 продажів і сума виплати становить 2000 рублів

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

Отже, вставте курсор в осередок C3 і натисніть на значок вибору функцій, перейдіть в категорію «Логічні» і виберіть функцію ЯКЩО і натисніть «ОК» (див. На малюнок)

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

лог_вираз - це те що ми перевірятиме. Помістіть курсор в цьому полі. У нашому випадку як ви пам`ятаєте ми спочатку перевіряємо продажу більше 20 (тобто більше плану) чи ні. Продажі Олексія у нас в осередку B3 - клікніть мишкою на B3 і цей вислів з`явиться в цьому полі (можна просто прописати B3, але зручніше вибирати). Далі нам необхідно перевірити ці продажі Олексія з планом. Напишемо B3gt; і виберемо осередок з планом G2, У нас повинно вийти вираз B3gt; G2

Відео: Перевірка умов і логічні функції в Excel

Для того, щоб в подальшому застосувати цю формулу для інших співробітників, простягнувши її вниз, нам необхідно закріпити посилання в осередку G2, щоб вона стала абсолютною (тобто не змінювалася, коли ми буде протягувати формулу). Для цього необхідно прописати знаки долара перед буквою і цифрою або тільки перед цифрою, так як ми не буде протягувати формулу вліво або вправо. Найзручніше зафіксувати клітинку просто виділивши у формулі потрібну комірку і натиснувши клавішу F4

Має вийти ось так B3gt; $ G $ 2 або так B3gt; G $ 2

Зверніть увагу, Excel автоматично обчислює вираз і відображає його справа. У нашому випадку Excel обчислив, що вираз - ІСТИНА, тобто дійсно Олексій зробив продажів в кількості 35 шт, що більше верхньої межі плану 20 шт. Зверніть увагу, що якщо б у Олексія було б недовиконання плану, то формулу ми б все одно писали точно так же - гіпотетично розмірковуючи, як якщо б у Олексія був би виконаний план.

Поїхали далі, переходимо до другого аргументу функції.

Значеніе_еслі_істіна. Переведіть курс в це поле. Тут нам необхідно розрахувати оплату співробітнику, якщо він виконав план. Стандартна оплата 2000 (F4) плюс так як ми розглядаємо ситуацію коли план перевиконано, потрібно додати переробку. Для цього віднімемо від усього продажів план і отримаємо кількість продажів понад норму (B3-G2) і помножимо їх на оплату за перевиконання 25 рублів (F5). В результаті отримуємо наступний розрахунок 2000+ (35-20) * 25 тобто F4 + (B3-G2) * F5

Відео: Функція ЯКЩО Microsoft Excel, частина 1

так само не забуваємо, що дані з таблиці умов нам потрібно закріпити, щоб вони зсувалися при протягуванні формули. Для цього виділяємо потрібні значення і натискаємо F4

Отримуємо підсумковий розрахунок: $ F $ 4 + (B3- $ G $ 2) * $ F $ 5

Переходимо до наступного поля.

Значеніе_еслі_ложь. Отже, вище ми розрахували оплату співробітнику, якщо план продажів виконаний (тобто якщо наше вираз в першому полі вірно - повертає істину). Якщо немає, то залишилося два варіанти: або співробітник точно виконав план продажів (від 18 до 20 шт), або недовиконав. Знову дві умови, потрібно вкладена ЯКЩО. Буде легше, якщо ми спочатку перевіримо умова невиконання плану, а залишився варіант буде виконання плану.

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

Після цього у вас знову з`явиться нове діалогове вікно функції ЯКЩО. Не треба панікувати попередня інформацію не стерлася, а просто згорнулася. Ви можете це бачити в рядку формул. Це зроблено для зручності.

заповнюємо поле лог_вираз, як ви пам`ятаєте ми перевіряємо умову про невиконання плану співробітником. Тобто прописуємо (вибираємо) B3

далі поле Значеніе_еслі_істіна. Тобто якщо дійсно B3

Отримуємо наступну формулу: $ F $ 4 - ($ F $ 2-B3) * $ F $ 6

Відео: Урок 7. Функція ЯКЩО

Відмінно. Якщо план перевиконано, то ми порахували оплату, якщо не виконано, ми перевіряємо співробітника на невиконання плану. Якщо він не виконав план, то ми вважаємо йому оплату. Якщо ж він і не перевиконав план і не недовиконав план, то значив від зробив рівно план від 18 до 20 шт.

Тому в наступному полі ми розраховуємо оплату співробітнику, коли він виконав план, тобто він отримує 2000 рублів (комірка F4). Переходимо в поле Значеніе_еслі_ложь і прописуємо (вибираємо) F4. Не забуваємо закріпити посилання - $ F $ 4

Ось так буде виглядати вкладене ЯКЩО

Натискаємо «Ок» - формула готова. Ось так виглядає повна формула:

= ЕСЛИ (B3gt; $ G $ 2- $ F $ 4 + (B3- $ G $ 2) * $ F $ 5-ЯКЩО (B3lt; $ F $ 2- $ F $ 4 + ($ F $ 2-B3) * $ F $ 6 $ F $ 4))

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