Зміст:
Використання функції IF із функціями AND, OR та NOT
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Вебпрограма Excel Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2013 Excel Web App Excel 2013 для розробників Excel для Windows Phone 10 Інші…Менше
Функція IF дає змогу порівняти значення з очікуваним результатом, використовуючи логічну операцію. Для цього вона перевіряє умову та повертає результат, якщо перевірка дала значення True або False.
Проте іноді потрібно перевірити кілька умов, наприклад, щоб визначити, чи мають усі вони (AND) або одна з них (OR) значення True або False або якщо потрібно переконатися, що умова не відповідає критеріям (NOT). Ці 3 функції можна використовувати окремо, але найчастіше вони застосовуються в поєднанні з функцією IF.
Функція IF у поєднанні з функціями AND, OR й NOT дає можливість перевірити, чи дорівнює результат перевірки відразу кількох умов значенням True або False.
- IF(AND()) – IF(AND(лог_значення1; [лог_значення2]; . ); значення_якщо_істина; [значення_якщо_хибність]))
- IF(OR()) – IF(OR(лог_значення1; [лог_значення2]; . ); значення_якщо_істина; [значення_якщо_хибність]))
- IF(NOT()) – IF(NOT(лог_значення1); значення_якщо_істина; [значення_якщо_хибність]))
лог_вираз (обов’язковий аргумент)
Умова, яку потрібно перевірити.
значення_якщо_істина (обов’язковий аргумент)
Значення, яке повертається, якщо результат умови “лог_вираз” має значення TRUE.
значення_якщо_хибність (необов’язковий аргумент)
Значення, яке повертається, якщо результат умови “лог_вираз” має значення FALSE.
Нижче описано принцип дії окремо кожної з функцій (AND, OR й NOT). Якщо поєднати їх із оператором IF, вони матимуть такий зміст:
- AND. =ЯКЩО(І(істинний вираз, ще один істинний вираз); значення, якщо істина; значення, якщо хибність)
- OR. =ЯКЩО(АБО(істинний вираз, ще один істинний вираз); значення, якщо істина; значення, якщо хибність)
- NOT. =ЯКЩО(НЕ(істинний вираз); значення, якщо істина; значення, якщо хибність)
Приклади
Нижче наведено приклади поширених вкладених виразів IF(AND()), IF(OR()) та IF(NOT()). Функції AND і OR підтримують до 255 окремих умов функцій, але таку велику кількість використовувати недоцільно, тому що складні вкладені формули дуже важко створювати, перевіряти та змінювати. Функція NOT приймає лише одну умову.
Нижче наведено опис формул відповідно до їхньої логіки.
ЯКЩО A2 (25) більше 0, А B2 (75) менше 100, повертається значення TRUE, інакше повертається значення FALSE. У цьому випадку обидві умови істинні, тому повертається значення TRUE.
ЯКЩО A3 (“Синій”) дорівнює “Червоний” І B3 (“Зелений”) дорівнює “Зелений”, повертається значення TRUE, в іншому разі повертається значення FALSE. У цьому випадку лише друга умова істинна, тому повертається значення FALSE.
ЯКЩО A4 (25) більше 0, АБО B4 (75) менше 50, повертається значення TRUE, інакше повертається значення FALSE. У цьому випадку лише перша умова має значення TRUE, але оскільки функція OR вимагає істинності лише одного аргументу, формула повертає значення TRUE.
ЯКЩО A5 (“Синій”) дорівнює “Червоний” АБО B5 (“Зелений”) дорівнює “Зелений”, повертається значення TRUE, в іншому разі повертається значення FALSE. У цьому випадку вираз у другому аргументі істинний, тому формула повертає логічне значення TRUE.
ЯКЩО A6 (25) не більше 50, повертається значення TRUE, інакше повертається значення FALSE. У цьому випадку 25 не перевищує 50, тому формула повертає значення TRUE.
ЯКЩО A7 (“Синій”) не дорівнює “Червоний”, повертається значення TRUE, в іншому разі повертається значення FALSE.
Зверніть увагу, що в усіх прикладах дужки закриваються після кожної відповідної умови. Решта аргументів TRUE та FALSE стосуються зовнішнього оператора IF. Крім того, у цих прикладах логічні значення TRUE та FALSE можна замінити текстовими або числовими.
Ось кілька прикладів того, як можна обчислювати дати за допомогою логічних операторів AND, OR та NOT.
Нижче наведено опис формул відповідно до їхньої логіки.
ЯКЩО A2 більше B2, повертається значення TRUE, інакше повертається значення FALSE. 12.03.14 більше 01.01.14, тому формула повертає значення TRUE.
ЯКЩО A3 більше B2 І A3 менше C2, повертається значення TRUE, інакше повертається значення FALSE. У цьому випадку обидва аргументи істинні, тому формула повертає значення TRUE.
ЯКЩО A4 більше B2 АБО A4 менше B2 + 60, повертається значення TRUE, інакше повертається значення FALSE. У цьому випадку перший аргумент істинний, а другий – хибний. Оскільки для функції OR потрібен лише один із аргументів, вона повертає значення TRUE. Якщо ви використовуєте майстер обчислення формул на вкладці Формула, ви побачите, як Excel обчислює формулу.
ЯКЩО A5 не більше B2, повертається значення TRUE, інакше повертається значення FALSE. У цьому випадку A5 більше B2, тому формула повертає значення FALSE.
Використання операторів AND, OR та NOT з умовним форматуванням
Вибравши відповідний параметр для формул, можна також задати умови для умовного форматування за допомогою операторів AND, OR та NOT. У такому випадку можна пропустити функцію IF і використовувати функції AND, OR та NOT окремо.
На вкладці Основне послідовно виберіть елементи Умовне форматування > Створити правило. Потім клацніть параметр Використовувати формулу для визначення клітинок для форматування та введіть формулу й застосуйте форматування за власним вибором.
Ось якими будуть формули, якщо скористатися датами з прикладу вище.
Якщо A2 більше B2, клітинки форматуються. В іншому разі нічого не відбувається.
Якщо A3 більше B2 ТА A3 менше C2, клітинки форматуються. В іншому разі нічого не відбувається.
Якщо A4 більше B2 АБО A4 менше B2 плюс 60 (днів), клітинки форматуються. В іншому разі нічого не відбувається.
Якщо A5 НЕ більше B2, відформатуйте клітинку, інакше нічого не робити. У цьому випадку A5 більше B2, тому результат поверне значення FALSE. Якщо змінити формулу на =NOT(B2>A5), вона поверне значення TRUE, і клітинку буде відформатовано.
Примітка.: Поширена помилка – ввести формулу в умовне форматування без знака рівності (=). Якщо це зробити, ви побачите, що в діалоговому вікні Умовне форматування до формули буде додано знак рівності та лапки – =”OR(A4>B2;A4
Потрібна додаткова довідка?
Додаткові відомості
Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільнотах.
Використання операторів обчислення у формулах Excel
Оператори визначають тип обчислень, які потрібно виконати з елементами формули. Excel керується загальними математичними правилами для обчислень, тобто дужок, експонентів, множення та ділення, додавання та віднімання, або акроніма PEMDAS (Будь ласка, вибачте, моя дорога тітка Саллі). За допомогою дужок можна змінити порядок обчислення.
Типи операторів. Існує чотири різні типи операторів обчислення: арифметичне, порівняння, об’єднання тексту та посилання.
- Арифметичні оператори Для виконання базових математичних операцій, таких як додавання, віднімання, множення або ділення, об’єднання чисел і створення числових результатів, використовуйте такі арифметичні оператори.
| Арифметичний оператор | Значення | Приклад |
|---|---|---|
| + (знак плюс) | Додавання | =3+3 |
| – (знак мінус) | Віднімання Заперечення | =3–3 =-3 |
| * (зірочка) | Множення | =3*3 |
| / (ліва скісна риска) | Ділення | =3/3 |
| % (знак відсотка) | Частка | 30% |
| ^ (”кришка”) | Піднесення до степеня | =3^3 |
| Оператор порівняння | Значення | Приклад |
|---|---|---|
| = (знак рівності) | Дорівнює | =A1=B1 |
| > (знак ”більше”) | Більше | =A1>B1 |
| < (знак ''менше'') | Менше | =A1 |
| >= (знак ”більше або дорівнює”) | Більше або дорівнює | =A1>=B1 |
| Менше або дорівнює | =A1 | |
| <> (знак ”не дорівнює”) | Не дорівнює | =A1<>B1 |
| Текстовий оператор | Значення | Приклад |
|---|---|---|
| & (амперсанд) | Об’єднує два значення та створює одне безперервне текстове значення | =”Північ”&”вітер” призводить до “Northwind”. Якщо A1 містить “Прізвище”, а B1 – “Ім’я”, =A1&”, “&B1 – “Прізвище, ім’я”. |
| Оператор посилання | Значення | Приклад |
|---|---|---|
| : (двокрапка) | Оператор діапазону. Створює посилання на всі клітинки, які розташовано між двома посиланнями (включно з ними). | B5:B15 |
| , (кома) | Оператор об’єднання. Об’єднує кілька посилань в одне. | =SUM(B5:B15;D5:D15) |
| (пробіл) | Оператор перетину. Створює одне посилання на клітинки, спільні у двох посиланнях. | B7:D7 C6:C8 |
Знак «не дорівнює» в Microsoft Excel
Якщо такі знаки порівняння, як «більше» (>) і «менше» ( <)досить легко знаходяться на клавіатурі комп’ютера, то з написанням елемента «не дорівнює» (≠) виникають проблеми, так як його символ на ній відсутня. Це питання стосується всіх програмних продуктів, але особливо він актуальний для додатка Microsoft Excel, так як в ньому проводяться різні математичні та логічні обчислення, для яких цей знак необхідний. Давайте дізнаємося, як поставити це символ в Ексель.
Написання знака «не дорівнює»
Перш за все, потрібно сказати, що в Ексель існує два знака «не дорівнює»: «<>» і «≠». Перший з них використовується для обчислень, а другий виключно для графічного відображення.
Символ «<>»
Елемент «<>» застосовується в логічних формулах Ексель, коли потрібно показати нерівність аргументів. Втім, його можна застосовувати і для візуального позначення, так як він все більше входить в ужиток.
Напевно, вже багато хто зрозумів, що для того, щоб набрати символ «<>», потрібно відразу набрати на клавіатурі знак «менше» ( <),а потім елемент «більше» (>). У підсумку виходить ось такий напис: «<>».
Існує ще один варіант набору даного елемента. Але, при наявності попереднього, він, безумовно, здасться незручним. Сенс його використовувати є лише в тому випадку, якщо з якої-небудь причини клавіатура виявилася відключена.
- Виділяємо осередок, куди повинен бути вписаний знак. Переходимо у вкладку «Вставка». На стрічці в блоці інструментів «Символи» тиснемо на кнопку з найменуванням «Символ».
Таким чином, наша задача повністю виконана.
Символ «≠»
Знак «≠» використовується виключно в візуальних цілях. Для формул та інших обчислень в Excel його застосовувати не можна, так як програма не розпізнає його, як оператора математичних дій.
На відміну від символу «<>» набрати знак «≠» можна тільки за допомогою кнопки на стрічці.
- Кількома по осередку, в яку планується вставити елемент. Переходимо у вкладку «Вставка». Тиснемо на вже знайому нам кнопку «Символ».
Як бачимо, елемент «≠» в поле осередку вставлений успішно.
Ми з’ясували, що в Ексель існує два види символів «не дорівнює». Один з них складається з знаків «менше» і «більше», і використовується для обчислень. Другий (≠) – самодостатній елемент, але його використання обмежується тільки візуальним позначенням нерівності.