Як використовувати логічні функції в Excel IF, AND, OR, XOR, NOT

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

Як використовувати функцію IF

Функція IF є головною логічною функцією в Excel і, отже, вона розуміється в першу чергу. Він буде з'являтися багато разів у цій статті.

Давайте поглянемо на структуру функції IF, а потім побачимо деякі приклади її використання.

Функція IF приймає 3 біти інформації:

= IF (logical_test, [value_if_true], [value_if_false])

• logical_test: Це умова для перевірки функції.
• value_if_true: Дія, яку слід виконати, якщо умова виконана, або є істинною.
• value_if_false: Дія, яку слід виконати, якщо умова не виконана, або є помилковим.

Оператори порівняння використовуються з логічними функціями

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

Тепер давайте подивимося на деякі приклади цього в дії.

IF Функція Приклад 1: Значення тексту

У цьому прикладі ми хочемо перевірити, що клітина дорівнює певній фразі. Функція IF не чутлива до регістру, тому не враховує великі та великі літери.

Наступна формула використовується у стовпці C для відображення "Ні", якщо стовпець B містить текст "Завершено" та "Так", якщо він містить щось інше.

= IF (B2 = "Завершено", "Ні", "Так")

Хоча функція IF не є чутливою до регістру, текст повинен бути точним.

IF Функція Приклад 2: Числові значення

Функція IF також чудово підходить для порівняння числових значень.

У наведеній нижче формулі ми перевіряємо, чи містить клітка B2 число більше або дорівнює 75. Якщо так, то ми показуємо слово «Pass», а якщо ні - слово «Fail».

= IF (B2> = 75, "Pass", "Fail")

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

У цьому прикладі ми хочемо надати 10% знижку, якщо клієнт витрачає певну суму грошей. Як приклад, ми використаємо 3000 фунтів стерлінгів.

= IF (B2> = 3000, B2 * 90%, B2)

Частина формули B2 * 90% - це спосіб, з якого ви можете відняти 10% від значення в комірці B2. Є багато способів зробити це.

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

IF Функція Приклад 3: Значення дати

У цьому третьому прикладі ми використовуємо функцію IF для відстеження списку термінів. Ми хочемо відобразити слово «Прострочена», якщо дата в колонці В знаходиться в минулому. Але якщо дата в майбутньому, розрахуйте кількість днів до дати завершення.

Наведена нижче формула використовується у стовпці C. Ми перевіряємо, що дата завершення в комірці B2 менша за сьогоднішню дату (функція TODAY повертає поточну дату з годинника комп'ютера).

= IF (B2 
 
Що таке вкладені IF Формули?
 
Можливо, ви раніше чули про термін вкладених IF. Це означає, що ми можемо написати функцію IF в межах іншої функції IF. Можливо, ми захочемо зробити це, якщо виконати більше двох дій.
 
Одна функція IF здатна виконувати дві дії ( value_if_true і value_if_false ). Але якщо ми вставляємо (або гніздо) іншу функцію IF в value_if_false розділ, то ми можемо виконати іншу дію.
 
Візьмемо цей приклад, де ми хочемо показати слово “Відмінно”, якщо значення в комірці В2 більше або дорівнює 90, відображає “Добре”, якщо значення більше або дорівнює 75, і відображає “Погано”, якщо що-небудь інше.
 
= IF (B2> = 90, "Excellent", IF (B2> = 75, "Good", "Poor"))
 
 
Тепер ми розширили нашу формулу за межі того, що може зробити тільки одна функція ІФ. При необхідності ви можете вкласти більше функцій, якщо це можливо.
 
Зверніть увагу на дві закриваючі дужки на кінці формули-1 для кожної функції IF.
 
Існують альтернативні формули, які можуть бути чистішими, ніж цей вкладений підхід IF. Одна дуже корисна альтернатива - функція SWITCH в Excel.
 
Логічні функції AND та OR
 
Функції AND та OR використовуються, коли ви хочете виконати більш ніж одне порівняння у вашій формулі. Функція IF самостійно може обробляти тільки одну умову або порівняння.
 
Візьмемо приклад, де ми знижуємо вартість на 10% залежно від суми, яку клієнт витрачає і скільки років вони були клієнтом.
 
Самі по собі функції AND та OR повертають значення TRUE або FALSE.
 
Функція AND повертає TRUE, лише якщо виконується кожна умова, і в іншому випадку повертає FALSE. Функція OR повертає TRUE, якщо одна або всі умови виконуються, і повертає FALSE тільки, якщо не виконано жодних умов.
 
Ці функції можуть перевіряти до 255 умов, тому, звичайно, вони не обмежуються лише двома умовами, наведеними тут.
 
Нижче наведено структуру функцій AND і OR. Вони написані однаково. Просто замініть ім'я AND на OR. Саме їхня логіка відрізняється.
 
= AND (logical1, [logical2]…)
 
Подивимося приклад того, як вони оцінюють дві умови.
 
І приклад функції
 
Функція AND використовується нижче, щоб перевірити, чи споживач витрачає принаймні 3 000 фунтів стерлінгів і є клієнтом не менше трьох років.
 
= AND (B2> = 3000, C2> = 3)
 
 
Ви можете бачити, що воно повертає FALSE для Matt і Terry, тому що хоча обидва вони відповідають одному з критеріїв, вони повинні зустрічатися як з функцією AND.
 
АБО Функція Приклад
 
Функція OR використовується нижче, щоб перевірити, чи споживач витрачає принаймні 3 000 фунтів стерлінгів або є клієнтом протягом принаймні трьох років.
 
= OR (B2> = 3000, C2> = 3)
 
 
У цьому прикладі формула повертає TRUE для Matt і Terry. Тільки Джулі і Джилліан не в змозі виконувати обидві умови і повертають значення FALSE.
 
Використовуючи AND та OR з функцією IF
 
Оскільки функції AND та OR повертають значення TRUE або FALSE при використанні окремо, їх рідко використовують самі.
 
Замість цього ви зазвичай використовуєте їх з функцією IF або в межах функції Excel, наприклад, умовне форматування або перевірку даних, щоб виконати деяку ретроспективну дію, якщо формула оцінюється як TRUE.
 
У формулі нижче функція AND вкладена в логічний тест функції IF. Якщо функція AND повертає TRUE, то 10% дисконтується від суми у стовпці B; в іншому випадку знижка не надається, а значення в колонці B повторюється в колонці D.
 
= IF (AND (B2> = 3000, C2> = 3), B2 * 90%, B2)
 
 
Функція XOR
 
Окрім функції OR, існує також функція виключення АБО. Це називається функцією XOR. Функція XOR була введена з версією Excel 2013.
 
Ця функція може вимагати певних зусиль, щоб зрозуміти, тому показаний практичний приклад.
 
Структура функції XOR є такою ж, як функція OR.
 
= XOR (logical1, [logical2]…)
 
При оцінці лише двох умов функція XOR повертає:
 
 
TRUE, якщо будь-яка умова оцінюється як TRUE.
 
FALSE, якщо обидва умови є TRUE, або жоден з умов не є TRUE.
 
 
Це відрізняється від функції OR, оскільки це повертає TRUE, якщо обидві умови були TRUE.
 
Ця функція стає трохи більш заплутаною, коли додаються додаткові умови. Потім функція XOR повертає:
 
 
ІСТИНА, якщо незвичайний кількість умов повертає TRUE.
 
FALSE, якщо навіть кількість умов призводить до TRUE або якщо все Умови FALSE.
 
 
Давайте розглянемо простий приклад функції XOR.
 
У цьому прикладі продажі розділені на дві половини року. Якщо продавець продає 3 000 фунтів або більше в обох половинах, то їм призначається золотий стандарт. Це досягається з функцією AND з IF, як і раніше в статті.
 
Але якщо вони продають 3 000 фунтів або більше в будь-якій половині, то ми хочемо призначити їм статус срібла. Якщо вони не продають £ 3000 або більше в обох, то нічого.
 
Функція XOR ідеально підходить для цієї логіки. Наведена нижче формула вводиться в стовпець Е і показує функцію XOR з IF для відображення "Так" або "Ні", лише якщо виконується одна з умов.
 
= IF (XOR (B2> = 3000, C2> = 3000), "Так", "Ні")
 
 
Функція NOT
 
Остаточна логічна функція для обговорення в цій статті - це функція NOT, і ми залишили найпростіший для останнього. Хоча іноді може бути важко побачити спочатку використання «реального світу» функції.
 
Функція NOT змінює значення свого аргументу. Отже, якщо логічне значення TRUE, то воно повертає FALSE. І якщо логічне значення FALSE, воно поверне TRUE.
 
Це буде легше пояснити деякими прикладами.
 
Структурою функції НЕ є;
 
= НЕ (логічний)
 
НЕ Функціональний приклад 1
 
У цьому прикладі уявімо, що ми маємо головний офіс у Лондоні, а потім багато інших регіональних сайтів. Ми хочемо відобразити слово "Так", якщо сайт є чимось окрім Лондона, і "Ні", якщо це Лондон.
 
Функція NOT була вкладена в логічний тест функції IF нижче, щоб повернути результат TRUE.
 
= IF (NOT (B2 = "Лондон"), "Так", "Ні")
 
 
Це також може бути досягнуто за допомогою НЕ логічного оператора. Нижче наведено приклад.
 
= IF (B2 "Лондон", "Так", "Ні")
 
НЕ Функціональний приклад 2
 
Функція NOT корисна при роботі з інформаційними функціями в Excel. Це група функцій в Excel, яка щось перевіряє, і повертає TRUE, якщо перевірка є успішною, і FALSE, якщо це не.
 
Наприклад, функція ISTEXT перевірятиме, чи містить комірка текст і повертає TRUE, якщо воно має, і FALSE, якщо цього не відбувається. Функція NOT є корисною, оскільки вона може змінити результат цих функцій.
 
У наведеному нижче прикладі ми хочемо заплатити продавцю 5% від суми, яку вони продають. Але якщо вони нічого не розпродають, то в клітині йде слово «Ніхто», і це призведе до помилки у формулі.
 
Функція ISTEXT використовується для перевірки наявності тексту. Це повертає TRUE, якщо є текст, тому функція NOT змінює це значення на FALSE. І IF виконує свій розрахунок.
 
= IF (NOT (ISTEXT (B2)), B2 * 5%, 0)
 
 
Освоєння логічних функцій дасть вам велику перевагу як користувача Excel. Дуже корисним є тестування і порівняння значень у клітинах і виконання різних дій на основі цих результатів.
 
 
Ця стаття охоплює найкращі логічні функції, що використовуються сьогодні. Останні версії Excel бачили введення додаткових функцій, доданих до цієї бібліотеки, таких як функція XOR, згадана в цій статті. Тримати вас у курсі цих нових додатків буде тримати вас попереду натовпу.