Електронні таблиці Excel

7.8. Використання логічних функцій

Логічні функції

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

Exit

Знаки логічних умов

Логічні умови використовують знаки:

>

(більше),

<

(менше),

=

(дорівнює),

>=

(не менше),

<=

(не більше),

<>

(не дорівнює).

Exit

Множина значень логічних умов

Логічна

умова, яка виконується

, має значення

1

або

ИСТИНА

(істина).

Логічна

умова, яка не виконується

, має значення

0

або

ЛОЖЬ

(хибність).

Праворуч розглянуто два приклади використання простих логічних умов. Комірки

A1

та

B1

містять однакові значення, а отже формула

=(A1=B1)

має значення "

ИСТИНА

". Комірки

A2

та

B2

містять різні значення, а отже формула

=(A2=B2)

має значення "

ЛОЖЬ

"

Exit

Exit

Логічна функція ЕСЛИ

Дозволяє вибрати одну дію із двох

ЕСЛИ (логічна умова; Дія 1; Дія 2) Дія 1

виконується при значенні умови

ИСТИНА

.

Дія 2

виконується при значенні умови

ЛОЖЬ

. Наприклад: нехай прибутковий податок нараховується залежно від суми заробітку: 10% при заробітку до 150 грн. і 20% при заробітку більше 150 грн. У цьому випадку допоможе функція

ЕСЛИ

, яку треба записати так (нехай сума заробітку записана у комірці

С7

):

ЕСЛИ(С7<150;10%*С7;20%*С7) С7

так

ЕСЛИ( С7<150 ; 10%*С7 ; 20%*С7 )

ні

Логічна функція И

И(умова1;умова2;...)

має значення

ИСТИНА

, якщо ВСІ умови виконуються, і

ЛОЖЬ

, якщо одна з умов не виконується.

Якщо взяти до уваги,

ИСТИНА=1

, а

ЛОЖЬ=0

, то:

И(1,1,1,1,1)

матиме значення 1

И(1,1,0,1,1) И(0,0,0,0,0)

і так далі. матиме значення 0 матиме значення 0

Exit

Логічна функція ИЛИ

ИЛИ(умова1;умова2;...)

має значення

ИСТИНА

, якщо хоча б одна з умов виконується, і

ЛОЖЬ

, якщо ні одна з умов не виконується.

Якщо взяти до уваги,

ИСТИНА=1

, а

ЛОЖЬ=0

, то:

ИЛИ(1,1,1,1,1)

матиме значення 1

ИЛИ(1,1,0,1,1) ИЛИ(0,0,0,0,0)

і так далі. матиме значення 1 матиме значення 0

Exit

Exit

Приклад використання логічних функцій

Підсумки вступних іспитів

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

Необхідно підрахувати суму балів, після чого заповнити стовпчик "Результат" за умовою вступу: сума балів більше 15. Для комірки

F2

використали формулу:

=СУММ(В2:Е2)

Формула

=ЕСЛИ(F2>15; "Поступив"; "Не поступив")

слово "

Поступив

" якщо умова

F2>15

поміщає у комірку виконується і слово "

Не поступив G2

", якщо ця умова не виконується. Аналогічні формули містять комірки діапазону

G3:G6

Exit

Оптимізація формули

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

Необхідно оптимізувати певні формули таблиці.

Пропонується для комірок

F2:F6

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

F2

підійде така формула:

=ЕСЛИ(ИЛИ(В2=2;С2=2;D2=2;Е2=2);0;СУММ(В2:Е2))

Аналогічні формули матимуть комірки діапазону

F3:F6

Exit

Розрахунок прибуткового податку

Нехай розрахунок прибуткового податку проводиться за таких спрощених умов, при мінімальному заробітку 185 грн.:  заробіток менший або рівний за 185 грн. - податок 0 %;  заробіток більший за 185 грн. - податок 13 % від суми, що перевищує 185 грн.

Формула для нарахування прибуткового податку (наприклад, для комірки

C2

) може бути такою:

=ЕСЛИ(B2>185;(B2-185)*13%;0)

Нехай заробіток працівника становить 500 грн. Прибутковий податок з цієї суми становитиме (500-185)*13% = 40,95 грн.

Складні формули

Розрахунок премії за вислугу років

За безперервну роботу на одному підприємстві або установі працівнику може нараховуватися премія за вислугу років. Умови виплати премії за вислугу років можуть бути різні.

Наприклад, такі: стаж роботи (комірка

В2

) до 8 років включно - 0 % від нарахованого (комірка

С2

), від 8 до 15 років включно - 20%, більше 15 років - 50%.

Формула для нарахування премії за вислугу років може бути така:

=ЕСЛИ(В2<=8;0 ; ЕСЛИ(В2>15;С2*50%;С2*20%) ) Запам'ятайте: параметром функції може бути інша функція.

Exit

Питання для самоконтролю (Тест ТЕМА-7-8):

1 За якою формулою знайти максимальне число в комірках А2:АЗЗ?

2 Як знайти корінь квадратний від суми змісту комірок від Е2 до Е8?

3 Як обчислити середнє арифметичне змісту комірок від Е2 до Е8?

4 Що дає використання логічних функцій?

5 Якими знаками записуються логічні умови? 6 Коли і як використовується логічна функція ЕСЛИ? 7 При якому значенні умови виконується перша дія функції ЕСЛИ?

8 Що таке логічне значення ИСТИНА і логічне значення Неправда?

9 Як позначаються логічні значення істина і хибність?

10 Коли і як використовується логічна функція И?

11 Яке значення буде мати функція И(1;1;1;1;1)?

12 Яке значення буде мати функція И(1;1;1;0;1)?

13 Коли і як використовується логічна функція ИЛИ? 14 Яке значення буде мати функція ИЛИ(1;0;0;1;1)?

15 Яке значення буде мати функція ИЛИ(0;0;0;0;0)?

Exit