У статті йдеться про функцію табличного редактора Excel «Якщо». Вона пов'язана з логічними можливостями програми. Цю функцію можна віднести до найпоширенішої, яка найчастіше використовується під час роботи.
Ключові можливості
У програмі Excel формула «Якщо» дозволяє різного роду завдання, які вимагають порівняти певні значення та отримати результат. Таке рішення дає можливість використовувати розгалужені алгоритми, створювати дерево рішень.
Приклади застосування
Функція має вигляд: =ЯКЩО (завдання; істина; брехня). Першою складовою є логічний вираз. Воно здатне виступати як фрази чи числа. Наприклад, «10» чи «без ПДВ». Такий параметр слід заповнити. Істина являє собою значення, що відображається як результат, якщо вираз вірний. Брехня являє собою дані, які будуть видані, коли завдання неправильне.
Рівність параметрів двох осередків
Щоб краще зрозуміти можливості функції "Якщо" в Excel, необхідно навести приклади. Тому далі варто розпочати їх розгляд. Потрібно ввести в клітину C1 значення 8. Після цього в полі на адресу D1 необхідно вписати формулу виду: =ЯКЩО(C1<10; 1; 2). В результате программа самостоятельно начинает сравнение параметров из клетки C1 со значением 10. Когда оно достигнет десяти, в поле по адресу D1 можно увидеть единица. В противном случае редактор отобразит 2.
Можна розглянути ще один приклад. Існує кілька студентів та їх позначки, які зароблено за іспит. Дані такі: 5, 4, і навіть 3 і 2. За умовою завдання передбачається створення кожному з учня текстового коментаря «здав» чи «не сдал». Таким чином, якщо студент отримав відмітку три та вище, вважається, що він склав іспит. Якщо його оцінка нижче 3, студент не впорався із заланням. Для вирішення такого завдання слід написати таку формулу: =ЯКЩО(C1<3; «не справился»; «сдал»).
Програма починає порівнювати результати кожного студента. Якщо показник менший за три, у відповідній клітині з'явиться напис «не впорався». Коли позначка 3 і вище, у потрібній графі можна побачити коментар про те, що студент не склав іспит. Варто зазначити, що текстові коментарі слід завжди писати в лапках. Під час використання цієї функції можна застосовувати такі оператори порівняння:< >, =, >, <, >=, <=.
Приклади з використанням умов «АБО», «І»
Необхідно продовжити розгляд логічних можливостей цієї програми. Існує можливість поєднати функцію "Якщо" з операторами порівняння. Це такі параметри:
«АБО»;
"І".
Необхідно вказати важливу умову в Excel: коли оцінка студента дорівнює або менше 5, але більше 3. При цьому слід відобразити коментар: проходить чи ні. Таким чином, проходять лише ті студенти, які заробили п'ятірки та четвірки. Щоб записати це завдання в табличному редакторі слід застосувати спеціальну формулу. Вона має такий вигляд: =ЯКІ(І(A1)<=5; A1>3); "проходить", "ні").
Якщо розглянути складніший приклад, знадобиться використовувати «АБО» чи «І». Таким чином, можна ознайомитися із застосуванням формули в Excel, якщо існує декілька умов у завданні. Наприклад: =ЯКЩО(АБО(A1=5; A1=10); 100; 0). В даному випадку можна зробити висновок, коли значення в клітці А1 дорівнює 5 або 10, програма відображає результат 100. В іншому випадку він становить 0. Існує можливість застосувати ці оператори, щоб знайти рішення складнішим завданням.
Наприклад, в базі потрібно обчислити боржників, яким потрібно заплатити понад 10 000 рублів. Можна задати умову, що позика не погашалася понад півроку, тобто шести місяців. Завдяки функції табличного редактора Excel «Якщо» дозволяється в автоматичному режимі отримати у відповідних імен позначку «проблемний клієнт». Допустимо, в клітині A1 розміщені дані, які вказують на термін заборгованості (місяці). Поле B1 містить суму.
В даному випадку формула представлена в наступному вигляді: =ЯКІ(І(A1>=6; B1>10000); «проблемний клієнт»; «»). Таким чином, якщо виявлено людину, яка відповідає зазначеним умовам, програма відображає напроти її імені відповідний коментар. Для інших учасників списку ця клітка залишиться порожньою.
Також можна ознайомитись із прикладом, коли ситуація є критичною. Необхідно запровадити відповідний коментар. Як результат, формула вийде наступною: =ЯКЩО(АБО(A1>=6; B1>10000); «критична ситуація»; «»). Однак, якщо програма визначає збіги за одним із параметрів як мінімум, відображається відповідна примітка.
Складні завдання
Функція Excel "Якщо" застосовується для того, щоб уникнути вбудованих помилок при розподілі на нуль. Крім того, вона використовується і в деяких інших ситуаціях. Перший випадок позначається як «ДІЛ/0». Його можна зустріти досить часто. Зазвичай це відбувається тоді, коли формула A/B вимагає копіювання. У цей же час показник B в окремих осередках становить 0. Щоб уникнути подібної ситуації, варто використовувати можливості оператора, що розглядається. Таким чином, необхідна формула має такий вигляд: =ЯКЩО(B1=0; 0; A1/B1). Отже, якщо клітина B1 заповнена значенням "нуль", редактор відобразить "0". В іншому випадку програма поділить показник 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 відсотка ціни товару. Якщо перевищено позначку 3001 одиниць, відбувається аналогічна дія, проте вже враховується 95%.
Такі висловлювання, як «більше чи одно», «менше чи одно» і «не одно», можуть бути дуже корисні при аналізі даних.
Ці оператори використовують порівняння двох значень, і результатом порівняння буде ІСТИНАабо Брехня.
Зверніть увагу, що логічні оператори порівнюють значення незалежно від того, було введено безпосередньо або було створено за допомогою формули. Ці оператори порівнюють саме значення. Чи не формули.
Рівно (=) і не дорівнює ()
Ці оператори прості: вони кажуть вам, чи є два значення рівними чи не рівними один одному:
Ці оператори так само і не однаково працюють з текстом:
Більше (>) і менше () – якщо перше значення більше другого, результатом буде ІСТИНА Оператор менше (=) і менше або дорівнює (=) – якщо перше значення більше другого або дорівнює йому, результатом буде ІСТИНА Оператор менше або дорівнює (
Назва форуму Теми Повідомлення Оновлення Тут можна отримати відповіді на запитання щодо Microsoft Excel 58627 479886 13 Лис 2018 01:32:48 44519 357825 29 Січ 2017 17:28:40 Найкращі обрані теми 10 11 Якщо ви - щасливий володар Mac 😉 220 1066 11 Лис 2018 04:30:45 Розділ для розміщення платнихпитань, проектів та завдань та пошуку виконавців для них. 2149 13707 12 Лис 2018 23:19:09 Якщо Ви завантажили або придбали надбудову PLEX для Microsoft Excel і у Вас є запитання чи побажання - Вам сюди. 317 1617 12 Лис 2018 14:23:59 824 11982 12 Лис 2018 14:08:08 Обговорення функціоналу, правил тощо. 270 3481 30 Жов 2018 15:01:36 Зараз на форумі (гостей: 426, користувачів: 5, з них прихованих: 1) , Сьогодні відзначають день народження (35), (32), (31) Всього зареєстрованих користувачів: 83968 участь в обговоренні: 32196 Всього тем: 106940
Якщо такі знаки порівняння, як «більше» (>) і «менше» (
Функція ЯКЩО Функції І, АБО, НЕ Вкладені функції ЯКЩО Функції ІСТИНА і БРЕХНЯ Функція ЕПУСТО Логічні вирази використовуються для запису умов, у яких порівнюються числа, функції, формули, текстові чи логічні значення. Будь-який логічний вираз має містити, принаймні, один оператор порівняння, який визначає відношення між елементами логічного виразу. Нижче наведено список операторів порівняння Excel = Рівно > Більше >= Більше або одно Не дорівнює Результатом логічного виразу є логічне значення ІСТИНА (1) або логічне значення БРЕХНЯ (0). Функція ЯКЩО
Функція ЯКЩО (IF) має наступний синтаксис:
=ЯКЩО(логічне_вираження;значення_якщо_істина;значення_якщо_брехня)
Наступна формула повертає значення 10, якщо значення в осередку А1 більше 3, а в іншому випадку - 20:
ЯКЩО (А1> 3; 10; 20)
Як аргументи функції ЯКЩО можна використовувати інші функції. У функції ЯКЩО можна використовувати текстові аргументи. Наприклад:
ЯКЩО (А1> = 4; "Залік здав"; "Залік не здав")
Можна використовувати текстові аргументи у функції ЯКЩО, щоб у разі невиконання умови вона повертала порожній рядок замість 0.
Наприклад:
ЯКЩО (СУМ (А1: А3) = 30; А10; »»)
Аргумент логічний_вираз функції ЯКЩО може містити текстове значення. Наприклад:
ЯКЩО (А1 = "Динамо"; 10; 290)
Ця формула повертає значення 10, якщо осередок А1 містить рядок «Динамо», і 290, якщо в ньому є будь-яке інше значення. Збіг між порівнюваними текстовими значеннями має бути точним, але не враховуючи регістру.
Функції І, АБО, НЕ
Функції І (AND), АБО (OR), НЕ (NOT) - дозволяють створювати складні логічні вирази. Ці функції працюють у поєднанні із простими операторами порівняння. Функції І та АБО можуть мати до 30 логічних аргументів і мають синтаксис:
=І(логічне_значення1;логічне_значення2…)
=АБО(логічне_значення1;логічне_значення2…)
Функція НЕ має лише один аргумент та наступний синтаксис:
=НЕ(логічне_значення)
Аргументи функцій І, АБО, НЕ можуть бути логічними виразами, масивами або посиланнями на комірки, що містять логічні значення.
Наведемо приклад. Нехай Excel повертає текст «Пройшов», якщо учень має середній бал більше 4 (осередок А2), та пропуск занять менше 3 (осередок А3). Формула набуде вигляду:
=ЯКІ(І(А2>4;А3
Не дивлячись те що, що функція АБО має самі аргументи, як і І, результати виходять зовсім різними. Так, якщо в попередній формулі замінити функцію І на АБО, то учень проходитиме, якщо виконується хоча б одна з умов (середній бал більше 4 або пропуски занять менше 3). Таким чином, функція АБО повертає логічне значення ІСТИНА, якщо хоча б один із логічних виразів істинно, а функція І повертає логічне значення ІСТИНА, тільки якщо всі логічні вирази істинні.
Функція НЕ змінює значення свого аргументу протилежне логічне значення і зазвичай використовують у поєднані із іншими функціями. Ця функція повертає логічне значення ІСТИНА, якщо аргумент має значення Брехня, і логічне значення Брехня, якщо аргумент має значення ІСТИНА.
Вкладені функції ЯКЩО
Іноді буває дуже важко вирішити логічне завдання лише за допомогою операторів порівняння та функцій І, АБО, НЕ. У цих випадках можна використовувати вкладені функції ЯКЩО. Наприклад, у наступній формулі використовуються три функції ЯКЩО:
= ЯКЩО (А1 = 100; "Завжди"; ЯКЩО (І (А1> = 80; А1 = 60; А1)
Якщо значення в комірці А1 є цілим числом, формула читається так: «Якщо значення в комірці А1 дорівнює 100, повернути рядок «Завжди». В іншому випадку, якщо значення в комірці А1 знаходиться між 80 і 100, повернути "Зазвичай". В іншому випадку, якщо значення в клітинці А1 знаходиться між 60 і 80, повернути рядок Іноді. І якщо жодна з цих умов не виконується, повернути рядок «Ніколи». Усього допускається до 7 рівнів вкладення функцій ЯКЩО.
Функції ІСТИНА і БРЕХНЯ
Функції ІСТИНА (TRUE) і БРЕХНЯ (FALSE) надають альтернативний спосіб запису логічних значень ІСТИНА та БРЕХНЯ. Ці функції не мають аргументів і виглядають так:
=ІСТИНА()
=БРЕХНЯ()
Наприклад, осередок А1 містить логічний вираз. Тоді наступна функція повернути значення «Проходьте», якщо вираз у комірці А1 має значення ІСТИНА:
ЯКЩО(А1=ІСТИНА();"Проходьте";"Стоп")
А якщо ні, то формула поверне «Стоп».
Функція ЄПУСТО
Якщо потрібно визначити, чи осередок порожній, можна використовувати функцію ЕПУСТО (ISBLANK), яка має наступний синтаксис:
= ЄПУСТО (значення)
На початок сторінки
На початок сторінки
Одна з найпопулярніших функцій у програмі Excel – ЯКЩО. Це логічне порівняннязначення та одержуваного результату. Якщо говорити простіше, то функція може звучати так:
ЯКЩО умова істинна, то виконуємо це, а інакше робимо щось ще
Синтаксисцієї функції досить простий:
ЯКЩО (логічне_умова; значення_у_випадку_ІСТИНА; значення у випадку_БРЕХНЯ)
Розберемо докладніше:
- Логічне_умова– значення, яке перевірятимемо
- Значення_у_випадку_ІСТИНА- Дія, коли вимога виконується
- Значення у випадку_БРЕХНЯ- дія, коли Логічне_умова не виконується
Як використовувати функцію
Приклад №1
На скріншотах нижче показаний найпростіший варіант використання. Ми спочатку перевіряємозадана умова А1>25. ЯКЩО це вимога виконується, тоді виводимо в комірку « більше 25», інакше « менше або дорівнює 25». 
Приклад №2
Тепер складніше завдання. Нижче ми розглянемо приклад переатестації працівників підприємства. Спочатку таблиця виглядає так: 
Нам необхідноу стовпці С вивести результати переатестації, які можуть набувати бінарного значення: ЗДАВабо НЕ ЗДАВ. Критерії у нас будуть такими: хто набрав понад 45 балів, той вважається здавшиміспит, ну а решта немає.
Для виконання завдання необхідно:
Використовуємо декілька умов
При необхідності, функцію ЯКЩОможна, можливо вкластиодин в одного. Отже, ми розширимо варіанти рішень.
Наприклад, візьмемо переатестацію співробітників, яку розглянули раніше. Змінимо критерії результату та виставимо кожному оцінку: Погано, Добре та Відмінно. Відмінноставитимемо, коли бали перевищать 60. ОцінкуДобре можна буде отримати, набравши від 45 до 60 балів. Ну і в інших випадках ставимо Погано.
Як видно з прикладу, замість другого та третього значення функції можна підставляти умову. У такий спосіб додаємо необхідну кількість вкладень. Однак варто відзначити, що після додавання 3-5 вкладень працювати з формулою практично неможливо, т.к. вона буде дуже громіздкою.
Інші варіанти використання функції
Знаходимо суму стовпців чи осередків
Наприклад, є необхідність підсумувати кожен другий осередок стовпця. У цьому випадку допоможе використання оператора СУМІСЛІ. Він дозволить працювати лише з тими стовпцями, які підпадають під наші вимоги. Ми розглядатимемо випадок, коли необхідно підсумовувати елементи в непарних рядках. 
Для вирішення задачі нам знадобиться вставитидодатковий стовпець і пронумерувати рядки 1 та 0. Саме ці дані ми будемо використовувати у нашій формулі. У прикладі видно, яку формулу використовували.
Синтаксис: СУМІСЛИ(діапазон_осередків; використовується_умова; [діапазон_підсумовування])
- Діапазон- Обов'язковий аргумент. Тут визначається діапазон, який оцінюється на відповідність умові.
- Використана_умова- Тут вказуємо, які саме осередки необхідно підсумовувати.
- Діапазон_підсумовування– вказуємо, який саме діапазон використовуватиме підсумовування.
Кількість повторень елементів на аркуші
У цьому випадку ми можемо порахувати скільки разів заданий елемент зустрічається на аркуші. Для цього використовуємо функцію РАХУНКИ. Вона вважає кількість осередків, які збігаютьсяіз заданим значенням. 
У цьому прикладі ми вважаємо, скільки клієнтів у кожному місті. У формулі ми задаємо першим діапазон, а другим значення, які слід шукати.
Використовуємо РАХУНКИ та СУМІСЛИ при підрахунку даних
У прикладі ми спробуємо визначити усереднений обсяг доходувід клієнтів у кожному місті. Для цього поділимо сумарний прибуток міста на кількість клієнтів. 
Пошук кількох входжень елемента у списку
Якщо у нас є список даних, що постійно оновлюється, в якому в міру зростання можуть з'являтися дублікати, то пошук входжень у цьому списку може виявитися скрутним. Для вирішення цього завдання найпростіше скористатися функцією ВВРі РАХУНКИ.
Для початку, додамододатковий стовпець, який додає порядковий номер входження до імені клієнта. У результаті перша згадка елемента видаватиме Ім'я1, наступне Ім'я2 і т.д. 
Використовуємо пошукпо клієнту Крістіна Агілера. Використовуючи формулу ВПР («Христина Агілера3», діапазон_пошуку, 2, брехня), ми отримаємо третій номер цього клієнта. У формулі, останнім значенням ставимо брехню, оскільки список не сортований, і нам необхідний точний збіг елементів.
Як скоротити кількість вкладень ЯКЩО
Версії до EXCEL 2003 включно підтримували до 7 рівніввкладеності ЯКЩО. Починаючи з версії Excel 2007, це обмеження прибрали. Але варто зауважити, що нижче за 3-4 рівні вкладеності мало хто опускається.
Для того, щоб зменшити кількість використання вкладень ЯКЩО, можна використовувати функцію ВИБІР. Вона працює зі значеннями або діями із заданого списку за номером індексу.
Синтаксис: ВИБІР (номер_індексу; значення_індексу1; значення_індексу2; [значення_індексу3];…)
Наприклад, функція ВИБІР (1; «Третій»; «Другий»; «Перший»), поверне нам слово « Третій», якщо ми її додамо в комірку.
Варто зазначити, що є певні обмеження. Зокрема, як індекс можуть виступати тільки числа.
В Excel існує багато різних функцій, робота яких побудована на перевірці логічних умов. Наприклад, це функції ЯКЩО, РАХУНКИ, СУМІСЛІ і т.д. Також логічні умови можна задавати у звичайних формулах, якщо необхідно отримати ствердну відповідь: Такабо Ні. Наприклад, задаючи прості логічні умови, Ви можете відповісти на такі запитання:
- 5 більше 8?
- Вміст комірки A5 менший за 8?
- А може одно 8?
Оператори порівняння в Excel
У Excel є низка стандартних операторів, які використовуються завдання простих логічних умов. Усі шість можливих операторів порівняння наведено в таблиці нижче:
Як задати умову в Excel
Оператори порівняння дозволяють задавати умови, які повертають логічні значення ІСТИНА або БРЕХНЯ. Приклади використання логічних умов наведені нижче:
=A1=B1– Ця умова поверне ІСТИНА, якщо значення в осередках A1 і B1 рівні, або БРЕХНЯ в іншому випадку. Задаючи таку умову, можна порівнювати текстові рядки без урахування регістру. Наприклад, порівнюючи "СІЧЕНЬ" і "січень" формула поверне ІСТИНА.
=A1>B1– Наступна формула поверне ІСТИНА, якщо значення осередку А1 більше, ніж у B1. В іншому випадку формула поверне брехню. Такі порівняння можна задавати і під час роботи з текстом.
Наприклад, якщо в клітинці A1 зберігається значення “Апельсин”, а в B1 – “Кавун”, то формула поверне БРЕХНЯ, оскільки в алфавітному порядку “Кавун” знаходиться нижче, ніж “Апельсин”. Що нижче, то більше.
=A1<=B1 – Формула поверне ІСТИНА, якщо значення осередку A1 менше або дорівнює значенню в осередку B1. Інакше результатом буде брехня.
=A1<>B1– Формула поверне ІСТИНА, якщо значення осередків A1 та B1 не рівні. В іншому випадку - БРЕХНЯ.
У Excel існують логічні функції ІСТИНА()і БРЕХНЯ(), які немає аргументів. Ці функції існують в основному для сумісності з іншими електронними таблицями. Ви можете вводити значення ІСТИНА і БРЕХНЯ прямо в комірки або формули, не використовуючи форму запису функції, Excel все чудово зрозуміє.
Якщо Ви впевнені, що вже досить добре освоїли цю тему, можете звернутися до статті Використовуємо логічні функції Excel для завдання складних умов, щоб навчитися задавати умови, використовуючи різні логічні функції, наприклад І()або АБО().
Перевірка істинності умов та логічних порівнянь між виразами, які є спільними для багатьох завдань. Для створення умовних формул можна використовувати функції " і", "," та " Якщо ".
Наприклад, функція Якщовикористовує наведені нижче аргументи.
Формула, яка використовує функцію якщо
Лог_вираз: умова, яку потрібно перевірити.
Значення_якщо_істина: значення, що повертається, якщо умова істинна.
Значення_якщо_брехня: значення, що повертається, якщо умова має значення false.
Для отримання додаткових відомостей про створення формул можна знайти у розділі Створення та видалення формул .
В цій статті
Створення умовної формули, що призводить до логічного значення (істина чи брехня)
Для виконання цього завдання використовуйте функції та оператори and, orі Not
приклад
Копіювання прикладу
Важливо:
Формулив групі Залежність формулнатисніть кнопку Показувати формули.
|
|
Для отримання додаткових відомостей про використання цих функцій див. статті, функції та функції, а не функції .
Створення умовної формули, яка призводить до іншого обчислення чи значень, відмінних від істина чи брехня
Для виконання цього завдання використовуйте функції та оператори Якщо, та і абоЯк показано в наступному прикладі.
приклад
Щоб цей приклад було простіше зрозуміти, скопіюйте його на порожній лист.
Копіювання прикладу
Виділіть приклад, наведений у цій статті.
Важливо:Не виділяйте заголовки рядків чи стовпців.
Виділення прикладу у довідці
Натисніть клавіші CTRL+C.
В Excel створіть порожню книгу чи аркуш.
Виділіть на аркуші комірку A1 і натисніть клавіші CTRL+V.
Важливо:Щоб приклад правильно працював, його потрібно вставити в комірку A1.
Щоб переключитись між переглядом результатів та переглядом формул, що повертають ці результати, натисніть клавіші CTRL+` (знак наголосу) або на вкладці Формулив групі Залежність формулнатисніть кнопку Показувати формули.
Скопіювавши приклад на порожній аркуш, ви можете налаштувати його так, як вам потрібно.
|
|
Додаткові відомості про використання цих функцій можна знайти у статтях функція та функція.
