Логічні функції в Excel. Все про застосування логічних функцій в Excel

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

Вони призначені для перевірки відповідності значень заданим критеріям. Якщо такий збіг є, то в клітинку, де він записаний, вноситься значення «ІСТИНА», у разі розбіжності – «ЛОЖЬ». Сьогодні ми більш детально розглянемо такі питання, як структура логічних функцій, сфера їх використання.

Список логічних функцій у Excel

Існує величезна кількість логічних функцій, але найчастіше використовуються наступні:

1. ІСТИНА
2. БРЕХНЯ
3. IF
4. ПОМИЛКА
5. OR
6. И
7. НЕ
8. ЕОШИБКА
9. ІСБЛАНК

Усі вони можуть бути використані для створення складних структур і визначення критеріїв будь-якого порядку. Майже всі ці функції передбачають передачу їм певних параметрів. Єдиним винятком є ​​TRUE і FALSE, які повертаються самі. Як параметри часто використовуються числа, текст, посилання на комірки, діапазони тощо. Давайте розглянемо всі перераховані вище оператори.

Оператори TRUE і FALSE

Спільним для обох цих функцій є те, що вони повертають лише одне значення. Сферою їх використання є використання як компонента інших функцій. Як можна зрозуміти з назви операторів, функцій ІСТИНА и БРЕХНЯ повертаються значення ІСТИНА и БРЕХНЯ відповідно.

НЕ оператор

Ця функція використовується з одним аргументом і записує протилежне значення в клітинку. Якщо передати цей оператор ІСТИНА, то воно повернеться БРЕХНЯ і, відповідно, вірним є протилежне твердження. Тому результат обробки даних цим оператором повністю залежить від того, які параметри йому передати.

Синтаксис цього оператора такий: =НІ (істинне чи хибне).

Оператори І та АБО

Ці два оператори необхідні для того, щоб передати відношення умов виразу один до одного. функція И використовується, щоб вказати, що два критерії повинні відповідати одному номеру або тексту одночасно. Ця функція повертає значення ІСТИНА лише за умови, що всі критерії дають це значення одночасно. Якщо хоча б один критерій не виконується, вся послідовність повертає значення БРЕХНЯ.

Спосіб створення оператора І дуже простий: =І(аргумент1; аргумент2; …). Максимальна кількість аргументів, які може використовувати ця функція, становить 255. Синтаксис оператора OR схожі, але механіка роботи дещо інша. Якщо одна зі списку функцій дає результат ІСТИНА, то це число буде повернено як цілу логічну послідовність.

Оператори IF і ISERROR

Ці дві функції мають дуже важливе призначення – вони безпосередньо встановлюють критерій, на відповідність якому необхідно перевірити певний вираз. Для глибшого розуміння того, як працює оператор ПОМИЛКА, спочатку потрібно описати функцію IF. Його загальна структура трохи складніше попередніх: =ЯКЩО(логічний_вираз, значення_якщо_істина, значення_якщо_хибність).

Завдання цього оператора - створювати найскладніші конструкції. Він перевіряє відповідність критеріям. Якщо так, то оператор повернеться ІСТИНА, якщо ні - БРЕХНЯ. Але оператор часто використовується в поєднанні з іншими. Наприклад, якщо він використовується як аргумент функції НЕ, то, відповідно, загальна сума автоматично заміниться на протилежну. Тобто, якщо є відповідність критерію, буде повернено значення БРЕХНЯ. Це головна перевага логічних функцій: їх можна комбінувати в найхимерніших формах.

Далі схема ускладнюється. Якщо за цим критерієм ми отримаємо результат «ІСТИНА», то можна вказати текст, число, яке буде відображатися, або функцію, яка буде обчислюватися. Так само ви можете встановити результат, який відображатиметься, якщо результат було повернуто після обробки даних. БРЕХНЯ.

Структура оператора ПОМИЛКА досить схожі, але все ж дещо різні. Містить два необхідні аргументи:

1. Значення. Перевіряється сам вираз. Якщо це виявляється істинним, то це значення повертається.
2. Значення, якщо помилка. Це текст, число або функція, які будуть відображені або виконані, якщо результатом перевірки першого аргументу буде FALSE.

Синтаксис: =ЯКЩОПОМИЛКА(значення;значення_якщо_помилка).

Оператори ISERROW і ISEMPLAND

Перша функція містить лише одне значення та має такий синтаксис: =ISERROR(значення). Завдання цього оператора — перевірити, наскільки добре заповнені комірки (одна чи весь діапазон). Якщо виявляється, що доповнення було неправильним, він повертає справжній результат. Якщо все добре – брехня. Можна застосувати безпосередньо як критерій для іншої функції.

Excel може перевіряти посилання на такі типи помилок:

• #ІМ'Я?;
• #Н/З;
• #DEL/0!;
• #НОМЕР!;
• #ТАК;
• #ПУСТИЙ!;
• #ПОСИЛАННЯ!.

функція ІСБЛАНК Загалом, це неймовірно просто. Він містить лише один параметр, який є коміркою/діапазоном, який потрібно перевірити. Якщо є клітинка, яка не містить ні тексту, ні цифр, ні недрукованих символів, то повертається результат ІСТИНА. Відповідно, якщо дані є у всіх клітинках діапазону, то користувач отримує результат БРЕХНЯ.

Пам’ятна таблиця “Логічні функції в Excel”

Щоб підсумувати все вищеописане, давайте наведемо невелику таблицю, що містить інформацію про всі часто використовувані логічні функції.

Логічні функції та приклади розв’язування задач

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

Завдання 1. Припустимо, у нас залишилася частина товару після певного часу продажу. Її необхідно переоцінити за такими правилами: якщо її не вдалося продати за 8 місяців, розділіть її ціну на 2 рази. Спочатку створимо діапазон, який описує вихідні дані. Виглядає це так.

Для того, щоб описане завдання було успішно вирішено, необхідно скористатися наступною функцією.

Ви можете побачити це в рядку формул на скріншоті. Тепер внесемо деякі уточнення. Логічний вираз, який зображено на скріншоті (тобто C2>=8), означає, що товар має бути на складі до 8 місяців включно. Використовуючи арифметичні оператори >=, ми визначаємо правило «більше або дорівнює». Після того, як ми написали цю умову, функція поверне одне з двох значень: «ІСТИНА» або «ХИБЬ». Якщо формула відповідає критерію, то значення після переоцінки записується в комірку (ну або передається як аргумент в іншу функцію, все залежить від параметрів, заданих користувачем), ділиться на два (для цього ми розділили ціна на момент надходження на склад на дві) . Якщо після цього буде виявлено, що товар був на складі менше 8 місяців, повертається те саме значення, яке міститься в клітинці.

А тепер давайте ускладнимо завдання. Застосовуємо умову: шкала знижок має бути прогресивною. Простіше кажучи, якщо товар лежить більше 5 місяців, але менше 8, ціну потрібно ділити в півтора рази. Якщо більше 8, два. Щоб ця формула відповідала значенню, воно має бути таким. Подивіться на знімок екрана в рядку формул, щоб побачити це.

Важливо! В якості аргументів допустимо використовувати не тільки числові, але і текстові значення. Тому допустимо встановлювати критерії самого різного порядку. Наприклад, зробити знижку на товар, отриманий у січні, і не робити її, якщо він надійшов у квітні.

Завдання 2. Застосуємо цей критерій до товару, який є в наявності. Припустимо, якщо після проведеної вище уцінки його вартість стала менше 300 рублів, або якщо він не продається більше 10 місяців, то його просто знімають з продажу. Формула наступна.

Давайте розберемо це. Як критерій ми використали функцію OR. Необхідно забезпечити таку вилку. Якщо комірка D2 містить число 10, то у відповідному рядку стовпця E буде автоматично відображено значення «списано». Те ж саме стосується іншої умови. Якщо жоден із них не виконується, то просто повертається порожня клітинка.

Завдання 3. Скажімо, у нас є вибірка студентів, які намагаються вступити до середньої школи. Для цього їм потрібно скласти іспити з кількох предметів, зображених на скріншоті нижче. Для вступу до цього навчального закладу їм необхідно набрати 12 балів. При цьому важливою умовою є те, що бал з математики має бути не нижче 4 балів. Завдання полягає в тому, щоб автоматизувати обробку цих даних, а також скласти звіт про те, хто студенти вступили, а хто ні. Для цього зробимо таку таблицю.

Отже, наше завдання — змусити програму підрахувати, скільки балів буде в сумі, подивитися прохідний результат і провести порівняння. Після цих операцій функція повинна помістити результат у комірку, до якої він поміщається. Можливі два варіанти: «прийнято» або «ні». Щоб реалізувати це завдання, введіть аналогічну формулу (просто вставте свої значення): =ЕСЛИ(И(B3>=4;СУММ(B3:D3)>=$B$1);»принят»;»нет»).

З булевою функцією И ми можемо переконатися, що дві умови виконуються одночасно. У цьому випадку ми використали функцію SUM підрахувати загальний бал. В якості першої умови (в першому аргументі функції AND) ми вказали формулу B3>=4. У цій графі зазначається оцінка з математики, яка має бути не нижче 4 балів.

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

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

Завдання 4. Перед нами стоїть завдання визначити загальну вартість товару після уцінки. Вимога – вартість товару має бути вищою або середньою. Якщо ця умова не виконується, товар підлягає списанню. У цьому прикладі ми побачимо, як працює купа арифметичних і статистичних функцій.

Скористаємося вже намальованою таблицею. Для того, щоб розв’язати цю задачу, необхідно поставити правило як умову, згідно з якою комірка D2 повинна бути менше середнього арифметичного всього асортименту товару. Якщо правило підтверджується, то в комірці, де записана ця формула, встановлюється значення «списано». Якщо критерій не виконується, то встановлюється порожнє значення. Для повернення середнього арифметичного існує функція СЕРЕДНЯ.

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

Наше завдання — визначити середнє для всіх значень, яке відповідає певним характеристикам. Для цього використовуємо спеціальну функцію, якої не було в списку вище. Він дозволяє поєднати дві функції СЕРЕДНЯ и ЯКЩО І вона подзвонила БЕЗСЕРДЕЧИЙ. Містить три аргументи:

1. Діапазон для перевірки.
2. Умова для перевірки.
3. Усереднення діапазону.

В результаті виходить наступна формула (на скріншоті).

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