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

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

Exit Знаки логічних умов Логічні умови використовують знаки: > (більше), < (менше), = (дорівнює), >= (не менше), <= (не більше), <> (не дорівнює). Логічні умови використовують знаки: > (більше), < (менше), = (дорівнює), >= (не менше), <= (не більше), <> (не дорівнює).

Exit Множина значень логічних умов Логічна умова, яка виконується, має значення 1 або ИСТИНА (істина). Логічна умова, яка не виконується, має значення 0 або ЛОЖЬ (хибність). Логічна умова, яка виконується, має значення 1 або ИСТИНА (істина). Логічна умова, яка не виконується, має значення 0 або ЛОЖЬ (хибність). Праворуч розглянуто два приклади використання простих логічних умов. Комірки A1 та B1 містять однакові значення, а отже формула =(A1=B1) має значення "ИСТИНА". Комірки A2 та B2 містять різні значення, а отже формула =(A2=B2) має значення "ЛОЖЬ"

Exit Логічна функція ЕСЛИ ЕСЛИ (логічна умова; Дія 1; Дія 2) Дія 1 - виконується при значенні умови ИСТИНА. Дія 2 - виконується при значенні умови ЛОЖЬ. Наприклад: нехай прибутковий податок нараховується залежно від суми заробітку: 10% при заробітку до 150 грн. і 20% при заробітку більше 150 грн. У цьому випадку допоможе функція ЕСЛИ, яку треба записати так (нехай сума заробітку записана у комірці С7): ЕСЛИ(С7<150;10%*С7;20%*С7) ЕСЛИ (логічна умова; Дія 1; Дія 2) Дія 1 - виконується при значенні умови ИСТИНА. Дія 2 - виконується при значенні умови ЛОЖЬ. Наприклад: нехай прибутковий податок нараховується залежно від суми заробітку: 10% при заробітку до 150 грн. і 20% при заробітку більше 150 грн. У цьому випадку допоможе функція ЕСЛИ, яку треба записати так (нехай сума заробітку записана у комірці С7): ЕСЛИ(С7<150;10%*С7;20%*С7) С7 ЕСЛИ(С7<150;10%*С7;20%*С7) ні так Дозволяє вибрати одну дію із двох

Exit Логічна функція И И(умова1;умова2;...) - має значення ИСТИНА, якщо ВСІ умови виконуються, і ЛОЖЬ, якщо одна з умов не виконується. И(умова1;умова2;...) - має значення ИСТИНА, якщо ВСІ умови виконуються, і ЛОЖЬ, якщо одна з умов не виконується. Якщо взяти до уваги, ИСТИНА=1, а ЛОЖЬ=0, то: И(1,1,1,1,1) матиме значення 1 И(1,1,0,1,1) матиме значення 0 И(0,0,0,0,0) матиме значення 0 і так далі. Якщо взяти до уваги, ИСТИНА=1, а ЛОЖЬ=0, то: И(1,1,1,1,1) матиме значення 1 И(1,1,0,1,1) матиме значення 0 И(0,0,0,0,0) матиме значення 0 і так далі.

Exit Логічна функція ИЛИ ИЛИ(умова1;умова2;...) - має значення ИСТИНА, якщо хоча б одна з умов виконується, і ЛОЖЬ, якщо ні одна з умов не виконується. ИЛИ(умова1;умова2;...) - має значення ИСТИНА, якщо хоча б одна з умов виконується, і ЛОЖЬ, якщо ні одна з умов не виконується. Якщо взяти до уваги, ИСТИНА=1, а ЛОЖЬ=0, то: ИЛИ(1,1,1,1,1) матиме значення 1 ИЛИ(1,1,0,1,1) матиме значення 1 ИЛИ(0,0,0,0,0) матиме значення 0 і так далі. Якщо взяти до уваги, ИСТИНА=1, а ЛОЖЬ=0, то: ИЛИ(1,1,1,1,1) матиме значення 1 ИЛИ(1,1,0,1,1) матиме значення 1 ИЛИ(0,0,0,0,0) матиме значення 0 і так далі.

Exit Приклад використання логічних функцій Підсумки вступних іспитів Наприклад, склали наведену електронну таблицю, у яку записали оцінки, одержані абітурієнтами на чотирьох вступних іспитах. Необхідно підрахувати суму балів, після чого заповнити стовпчик "Результат" за умовою вступу: сума балів більше 15. Для комірки F2 використали формулу: =СУММ(В2:Е2) Підсумки вступних іспитів Наприклад, склали наведену електронну таблицю, у яку записали оцінки, одержані абітурієнтами на чотирьох вступних іспитах. Необхідно підрахувати суму балів, після чого заповнити стовпчик "Результат" за умовою вступу: сума балів більше 15. Для комірки F2 використали формулу: =СУММ(В2:Е2) Формула =ЕСЛИ(F2>15; "Поступив"; "Не поступив") поміщає у комірку G2 слово "Поступив" якщо умова F2>15 виконується і слово "Не поступив", якщо ця умова не виконується. Аналогічні формули містять комірки діапазону G3:G6

Exit Оптимізація формули Давайте змінемо значення оцінок за 4-й екзамен абітурієнтів Дьогтяренко і Закритигуба на 2. Тоді виявиться, що Закритигуба поступить, маючи більше 15 балів, не зважаючи на те, що склав екзамен на 2 бали, що суперечить вимогам. Необхідно оптимізувати певні формули таблиці. Пропонується для комірок F2:F6 змінити формули так, щоб при виявленні хоча б однієї оцінки за екзамен 2 бали, сумарна оцінка обнулялася. Для нашого випадку для комірки F2 підійде така формула: =ЕСЛИ(ИЛИ(В2=2;С2=2;D2=2;Е2=2);0;СУММ(В2:Е2)) Аналогічні формули матимуть комірки діапазону F3:F6 Давайте змінемо значення оцінок за 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 грн. Прибутковий податок з цієї суми становитиме ( )*13% = 40,95 грн. Нехай розрахунок прибуткового податку проводиться за таких спрощених умов, при мінімальному заробітку 185 грн.: заробіток менший або рівний за 185 грн. - податок 0 %; заробіток більший за 185 грн. - податок 13 % від суми, що перевищує 185 грн. Формула для нарахування прибуткового податку (наприклад, для комірки C2) може бути такою: =ЕСЛИ(B2>185;(B2-185)*13%;0) Нехай заробіток працівника становить 500 грн. Прибутковий податок з цієї суми становитиме ( )*13% = 40,95 грн.

Exit Складні формули Розрахунок премії за вислугу років За безперервну роботу на одному підприємстві або установі працівнику може нараховуватися премія за вислугу років. Умови виплати премії за вислугу років можуть бути різні. Наприклад, такі: стаж роботи (комірка В2) до 8 років включно - 0 % від нарахованого (комірка С2), від 8 до 15 років включно - 20%, більше 15 років - 50%. Формула для нарахування премії за вислугу років може бути така: =ЕСЛИ(В2 15;С2*50%;С2*20%)) Розрахунок премії за вислугу років За безперервну роботу на одному підприємстві або установі працівнику може нараховуватися премія за вислугу років. Умови виплати премії за вислугу років можуть бути різні. Наприклад, такі: стаж роботи (комірка В2) до 8 років включно - 0 % від нарахованого (комірка С2), від 8 до 15 років включно - 20%, більше 15 років - 50%. Формула для нарахування премії за вислугу років може бути така: =ЕСЛИ(В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)? 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 Вправа 7-8. "Логічні функції в електронній таблиці" 1) Підготувати комп'ютер до роботи. Завантажити Excel. Створити наведену електронну таблицю, у яку записати оцінки, одержані абітурієнтами на вступних іспитах. 2) Доповнити список ще 5 особами з різними оцінками. 3) Підрахувати для всіх абітурієнтів суму балів за формулою, щоб записувала 0 балів при оцінці 2 з одного з предметів. 4) За допомогою логічної формули заповнити стовпчик "Результат" за умовою вступу: середній бал не менше 18 5) Міняти значення оцінок у стовпчиках В, С, D і Е, спостерігати результати. Зберегти таблицю з іменем Вправа 7-6 у власній папці. Закрити програму Excel. 6) Відкрити власну папку. Зберегти файл Вправа 7-6 на дискету. Вилучити файл Вправа 7-6 із власної папки Відновити його з дискети. Закрити всі вікна. 1) Підготувати комп'ютер до роботи. Завантажити Excel. Створити наведену електронну таблицю, у яку записати оцінки, одержані абітурієнтами на вступних іспитах. 2) Доповнити список ще 5 особами з різними оцінками. 3) Підрахувати для всіх абітурієнтів суму балів за формулою, щоб записувала 0 балів при оцінці 2 з одного з предметів. 4) За допомогою логічної формули заповнити стовпчик "Результат" за умовою вступу: середній бал не менше 18 5) Міняти значення оцінок у стовпчиках В, С, D і Е, спостерігати результати. Зберегти таблицю з іменем Вправа 7-6 у власній папці. Закрити програму Excel. 6) Відкрити власну папку. Зберегти файл Вправа 7-6 на дискету. Вилучити файл Вправа 7-6 із власної папки Відновити його з дискети. Закрити всі вікна.