Логічні операції в Excel

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

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

Оператори порівняння

Кожен вираз містить оператори порівняння. Вони такі:

1. = – значення 1 дорівнює значенню 2.
2. > – значення 1 більше за значення 2.
3. < – ачение 1 еньше ачения 2.
4. >= значення 1 або ідентичне значенню 2 або більше.
5.  <= ачення 1 ще ачення 2 ідентично йому.
6. <> значення 1 або більше значення 2 або менше.

Як наслідок, Excel повертає один із двох можливих результатів: true (1) або false (2).

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

Справжня функція

Для використання цієї функції не потрібно вказувати ніяких аргументів, і вона завжди повертає «Істину» (що відповідає цифрі 1 подвійної системи розрахунку).

Приклад формули − =ІСТИНА().

Помилкова функція

Функція повністю схожа на попередню, тільки повертається нею результат «False». Найпростіша формула, де можна використовувати цю функцію, така =ЛОЖЬ().

І функція

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

Логічні посилання на комірки також використовуються як параметри функції. Максимальна кількість аргументів, які можна використовувати – 255. Але обов’язковою вимогою є наявність хоча б одного з них у дужках.

Синтаксис цієї функції:

=AND(Boolean1; [Boolean2];…)

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

Функція «або».

Перевіряє кілька значень на відповідність певним критеріям. Якщо будь-яке з них збігається, функція повертає справжнє значення (1). Максимальна кількість аргументів у цій ситуації також становить 255, і обов’язково вказувати один параметр функції.

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

Синтаксис формули такий:

=АБО(Логічний вираз 1; [Логічний вираз 2];…)

Так само, як і в попередньому, і в наступних випадках, кожен аргумент повинен бути відокремлений від іншого крапкою з комою. Якщо ми звернемося до прикладу вище, то кожен параметр повертає там «Істина», тому, якщо необхідно використовувати функцію «АБО» під час доступу до цього діапазону, тоді формула повертатиме «Істина», доки один із параметрів не відповідатиме певному критерій.

Функція «Ні».

Він повертає ті значення, які протилежні початково встановленому. Тобто при передачі значення «True» як параметр функції буде повернено «False». Якщо збігів не знайдено, то «Істина».

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

При використанні функції «Або» в поєднанні з функцією «Ні» таблиця матиме такий вигляд.

Синтаксис цієї функції дуже простий: =НЕ(приймаемое логічне значення).

If

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

Якщо говорити конкретно про цю функцію, то її синтаксис буде дещо складніше. 

=IF(Логічний_вираз,[Значення_якщо_істина],[Значення_якщо_хибне])

Давайте детальніше розглянемо приклад, показаний на скріншоті вище. Тут першим параметром є функція ІСТИНА, що перевіряється програмою. За результатами такої перевірки повертається другий аргумент. Третій опускається. 

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

Наприклад, є кілька кредитних карток, номери яких починаються з перших чотирьох цифр, які характеризують платіжну систему, яка обслуговує картку. Тобто є два варіанти – Visa і Mastercard. Щоб перевірити тип картки, потрібно використовувати цю формулу з двома вкладеними IF.

=IF(LEFT(A2)=”4″, “Visa”,IF(LEFT(A1111)=”2″,”Master Card”,”картка не визначена”))

Якщо ви не знаєте, що означає функція ЛЕВСІМВ, потім він записує в комірку частину рядка тексту ліворуч. Користувач у другому аргументі цієї функції вказує кількість символів, які Excel має вибрати зліва. Він використовується, щоб перевірити, чи перші чотири цифри номера кредитної картки починаються з 1111. Якщо результат істинний, повертається «Visa». Якщо умова хибна, то використовується функція IF.

Подібним чином можна досягти гідної вкладеності та перевірити вміст комірки чи діапазону на відповідність декільком умовам.

функція ERROR

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

Ця формула має такий синтаксис:

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

Як можна використовувати функцію?

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

Як булеві функції можна використовувати на практиці

Завдання 1

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

Для початку необхідно створити таку таблицю.

Для досягнення цієї мети необхідно використовувати функцію IF. У нашому прикладі ця формула буде виглядати так:

 =ЕСЛИ(C2>=8;B2/2;B2)

Логічний вираз, що міститься в першому аргументі функції, складається за допомогою операторів > і =. Простими словами, спочатку критерій такий: якщо значення комірки більше або дорівнює 8, виконується формула, надана в другому аргументі. Термінологічно кажучи, якщо перша умова вірна, то виконується другий аргумент. Якщо false – третій.

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

У цьому випадку вам потрібно ввести наступний рядок у поле введення формули.

=ЕСЛИ(И(C2>=8);B2/2;ЕСЛИ(И(C2>=5);B2/1,5;B2))

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

Завдання 2

Припустимо, після того, як товар був уцінений, він став коштувати менше 300 рублів, тоді його необхідно списати. Те ж саме потрібно зробити, якщо він пролежав без продажу 10 місяців. У даній ситуації прийнятний будь-який із цих варіантів, тому логічно використовувати функцію OR и IF. У результаті виходить наступний рядок.

=ЕСЛИ(ИЛИ(D2<300;C2>=10);»списан»;»»)

Якщо при написанні умови використовувався логічний оператор OR, то його необхідно розшифрувати наступним чином. Якщо клітинка C2 містить число 10 або більше, або якщо клітинка D2 містить значення менше 300, тоді у відповідній клітинці має бути повернуто значення «списане».

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

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

Завдання 3

Припустимо, є кілька учнів, які здають кілька іспитів перед вступом до гімназії. Прохідним балом є 12. А для вступу обов’язково має бути мінімум 4 бали з математики. У результаті Excel має створити звіт про отримання. 

Спочатку потрібно побудувати наступну таблицю.

Наше завдання звірити суму всіх оцінок з прохідним балом, а також переконатися, що оцінка з математики нижче 4. А в графі з результатом потрібно вказати «прийнято» або «ні».

Нам потрібно ввести наступну формулу.

=ЕСЛИ(И(B3>=4;СУММ(B3:D3)>=$B$1);»принят»;»нет»)

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

Таким чином, використовуючи функцію IF можна вирішувати багато різних задач, тому вона є однією з найпоширеніших.

Завдання 4

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

Для цього можна скористатися тією ж таблицею, яка була наведена вище.

Щоб вирішити цю задачу, потрібно скористатися такою формулою.

=ЯКЩО(D2

У виразі, поданому в першому аргументі, ми використали функцію СЕРЕДНЯA, що визначає середнє арифметичне певного набору даних. У нашому випадку це діапазон D2:D7.

Завдання 5

У цьому випадку, скажімо, нам потрібно визначити середні продажі. Для цього потрібно створити таку таблицю.

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

Цю задачу можна вирішити за допомогою лише однієї функції.

=СРЗНАЧЕСЛИ($B$2:$B$7;B9;$C$2:$C$7)

Перший аргумент - це діапазон значень, які потрібно перевірити. Друга задає умову, в нашому випадку це комірка B9. Але в якості третього аргументу використовується діапазон, який буде використовуватися для обчислення середнього арифметичного.

функція БЕЗСЕРДЕЧИЙ дозволяє порівняти значення клітинки B9 з тими значеннями, які розташовані в діапазоні B2:B7, в якому перераховані номери магазинів. Якщо дані збігаються, то формула обчислює середнє арифметичне діапазону C2:C7.

Висновки

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

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

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