Формула якщо в excel приклади кілька умов

Привіт, друзі! Чи часто вам доводиться робити вибір? Наприклад, захотіли купити новий телефон, а повної суми грошей у вас немає. Взяти кредит або збирати? Так сьогодні розберемо як робити вибір в електронних таблицях Excel. Це дозволяє зробити умовна функція ЯКЩО ().

Умовна функція ЯКЩО ()

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

Операції відносини в логічних виразах

просте умова

Що ж робить функція ЯКЩО ()? Подивіться на схему. Тут наведено простий приклад роботи функції при визначенні знака числа а.

Блок-схема “Просте умова”. Визначення негативних і невід’ємних чисел

Умова а>= 0 визначає два можливих варіанти: невід’ємне число (нуль або позитивне) і негативне. Нижче схеми наведена запис формули в Excel. Після умови через крапку з комою перераховуються варіанти дій. У разі істинності умови, в осередку відобразиться текст “невід’ємне”, інакше – “негативний”. Тобто запис, відповідна гілки схеми «Так», а слідом – «Ні».

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

Якщо результатом повинні бути дані, отримані в результаті обчислень, то дивимося наступний приклад. Виконаємо збільшення невід’ємного числа на 10, а негативне залишимо без змін.

Блок-схема “Просте умова”. розрахунок даних

На схемі видно, що при виконанні умови число збільшується на десять, і в формулі Excel записується розрахункове вираз А1 + 10 (Виділено зеленим кольором). В іншому випадку число не змінюється, і тут розрахункове вираз складається тільки з позначення самого числа А1 (Виділено червоним кольором).

Це була коротка вступна частина для початківців, які тільки почали навчатися Excel. А тепер давайте розглянемо більш серйозний приклад з використанням умовної функції.

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

Рішення:

Рішення даного завдання видно на малюнку нижче. Але внесемо все-таки ясність в цю ілюстрацію. Основні вихідні дані для вирішення цього завдання знаходяться в стовпцях А і В. В осередку А5 вказано прикордонне значення доходу при якому змінюється ставка оподаткування. Відповідні ставки вказані в осередках В5 і В6. Дохід фірм вказаний в діапазоні осередків В9: В14. Формула розрахунку податку записується в комірку С9: = ЕСЛИ (B9>A $ 5; B9 * B $ 6; B9 * B $ 5). Цю формулу потрібно скопіювати в нижні комірки (виділено жовтим кольором).

У розрахунковій формулі адреси осередків записані у вигляді A $ 5, B $ 6, B $ 5. Знак долара робить фіксованою частина адреси, перед якою він встановлений, при копіюванні формули. Тут встановлено заборону на зміну номера рядка в адресі комірки.

складений умова

Складений умова складається з простих, пов’язаних логічними операціями І () і АБО ().

І () – логічна операція, що вимагає одночасного виконання всіх умов, пов’язаних нею. АБО () – логічна операція, що вимагає відповідності одній з умов, пов’язаних нею.

Логічна операція І ()

Наприклад: Розглянемо електронну таблицю “Відомість здачі вступних іспитів”. Для зарахування абітурієнта до ВНЗ, йому необхідно подолати прохідний бал, і з математики відмітка повинна бути вище 70 балів. Подивіться уважно на малюнок нижче.

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

Чому так сталося? Подивимося уважно на умова в нашій формулі = ЕСЛИ (И (E6>= D2; B6>70); “зарахований”; «не зараховано”). логічна операція І () вимагає виконання всіх умов, але у нас виконується тільки одне. друга умова B6>70 не виконана, тому складене умова приймає значення «брехня». І на екран виводиться повідомлення «Не зарахований» (згадуємо схему – гілка «ні»).

завдання: У торговій фірмі перед Новим Роком влаштована святковий розпродаж. Розрахувати суму продажів з урахуванням знижки, яка призначається в період розпродажу.

1. Розрахуйте загальну вартість продажів. Разом = Вартість * К
ількість.

2. Визначте знижку (у відсотках), використовуючи функцію ЯКЩО (). Якщо дата продажу потрапляє в період святкового розпродажу, то призначається знижка, інакше знижка дорівнює нулю. При завданні умов використовуйте логічну функцію І ().

3. Визначте суму продажу з урахуванням знижки. Сума продажу з урахуванням знижки = Разом * (1 Знижка%).

Рішення:

Для проведення розрахунків необхідно вписати наступні формули:

  • В осередку Е7: = B7 * C7
  • В осередку F7: = ЕСЛИ (И (D7>= D $ 4; D7<= E $ 4); B $ 4; 0)
  • В осередку G7: = E7 * (1-F7)

і скопіювати за відповідними стовпцями до 16 рядка включно.

Логічна операція АБО ()

Подивимося приклад з логічною операцією АБО () в тій же таблиці. Тільки трохи змінимо умову задачі. Для зарахування абітурієнта досить мати з математики більше 60 балів або загальну суму балів не менше прохідного. Малюнок нижче.

Абітурієнт Сидоров зарахований, хоча не набрав прохідний бал. ось формула = ЕСЛИ (ИЛИ (B7>60; E7>D2;); “зарахований”; «не зараховано”). Тут використана операція АБО (), тому досить виконання хоча б однієї умови. Що і сталося, перша умова B7>60 істинно. Воно привело до висновку повідомлення про зарахування абітурієнта.

завдання: У торговій фірмі встановлені дні розпродажів – останні числа місяця. Розрахувати суму продажів з урахуванням знижки, яка призначається в дні розпродажу.

1. Розрахуйте загальну вартість продажів. Разом = Вартість * Кількість.

2. Визначте знижку (у відсотках), використовуючи функцію ЯКЩО (). Якщо дата продажу збігається з датами розпродажів, то призначається знижка, інакше знижка дорівнює нулю. При завданні умов використовуйте логічну функцію АБО ().

3. Визначте суму продажу з урахуванням знижки. Сума продажу з урахуванням знижки = Разом * (1 Знижка%)

Рішення:

Для проведення розрахунків необхідно вписати наступні формули:

  • В осередку Е7: = B7 * C7
  • В осередку F7: = ЕСЛИ (ИЛИ (D7 = D $ 4; D7 = E $ 4; D7 = F $ 4); B $ 4; 0)
  • В осередку G7: = E7 * (1-F7)

і скопіювати за відповідними стовпцями до 15 рядка включно.

вкладені умови

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

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

завдання: З метою зменшення плинності кадрів адміністрація вирішила виплачувати надбавку за безперервний стаж роботи на своєму підприємстві. Розрахуйте надбавку за безперервний стаж роботи відповідно до таблиці Надбавка.

1. Визначте стаж роботи співробітників. Стаж = 2018- Рік прийому на роботу.

2. Використовуючи вкладені функції Якщо, розрахуйте надбавку для співробітників. Надбавка (руб.) = Надбавка (%) * Оклад

Рішення:

Для проведення розрахунків необхідно вписати наступні формули:

  • В осередку D9: = 2018 B9
  • В осередку E9: = ЕСЛИ (D9>= B $ 6; C9 * C $ 6; ЯКЩО (D9>= B $ 5; C9 * C $ 5; 0))

і скопіювати за відповідними стовпцями до 19 рядка включно.

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

Додаткова інформація:

PS: Дивовижні факти

Ссылка на основную публикацию