Умовне форматування

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

Умовне форматування тісно пов’язане з функцією if.

Функцiя IF повертає у комірку одну величину, якщо задана користувачем умова справджується, й іншу, якщо умова не справджується. Функція має 3 аргументи:

• логічний вираз, який задає умову — logical test в описі синтаксису;
• величину, що виводять у випадку справдження умови — value if true в описі синтаксису;
• величину, що виводять у випадку несправдження умови — value if false в описі синтаксису.

Синтаксис (правильна структура виклику) функції IF має такий вигляд:

IF(logical test; [value if true]; [value if false])

Дослівно: ЯКЩО(логічний вираз; [величина, якщо істина]; [величина, якщо хибність])

Квадратні дужки формального запису функції вказують на можливість відсутності параметрів. Але розділові знаки (;) потрібно записати обов’язково.

Другий і третій аргументи функції IF можуть містити функції, у тому числі й функцію IF. Наприклад,

IF(В2<=40;"їхати потрібно швидше!";IF(В2>=90;"їдь повільше!";"Молодець!"))

Логічні функцiї AND, OR, NOT (англійською: або, і, заперечення/не) використовують для запису складніших логічних виразів, ніж рівність або нерівність. Наприклад, для запису системи чи сукупності співвідношень:

-  систему 40 ≤ В2 ≤ 90 записують AND(B2>=40; B2<=90);

-  сукупність В2 < 40 або 90 < В2 записують OR(B2<40;90<B2).

Функції AND і OR можуть мати довільну кількість аргументів, NOT — лише один.

Завдання 1. Розглянемо життєвий приклад. Дано таблицю з такими даними:

Обчислити вартість товарів з урахуванням того, що магазин надає знижку 10%, якщо кількість куплених товарів одного найменування перевищує 5 одиниць.

Порада. Доцільно застосувати функцію ІF для рядка 3 таким чином:

ІF(С3>5;B3*C3*0,9; B3*C3)

Інакше кажучи, якщо кількість С3 перевищує 5, тоді сплачують не повну вартість (B3*C3), а лише 90% від неї, тобто (B3*C3*0,9).

Опис виконання. У попередньо підготовленій таблиці встановіть курсор миші в комірку D3 і зверніться до Помічника з функцій. У вікні діалогу Function Wizard виберіть категорію Логічні функції, функцію ІF і введіть у поля аргументів потрібні параметри.

Натиснути кнопку Гаразд. Скористатися маркером автозаповнення для поширення результату на інші рядки. Виконайте контрольне обчислення:

Завдання 2. Завантажити таблицю такого вигляду:

За допомогою функції ІF заповнити стовпчик ВИСНОВОК залежно від величини швидкості, вказаної у клітинці ліворуч:

• швидкість більша за 100 км/год — Надто швидко;
• швидкість менша за 40 км/год — Дуже повільно;
• швидкість між 40 і 100 км/год (включно) — Все правильно.

Опис виконання. У попередньо підготовленій таблиці встановити курсор миші в комірку D2. Викликати Помічник з функцій. У вікні діалогу Function Wizard вибрати категорію Логічні функції, функцію ІF і заповнити поля:

  

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

Натиснути кнопку Гаразд. Скористатися маркером автозаповнення для поширення результату на інші рядки:

Завдання 3. Завантажити таблицю такого вигляду:

За допомогою функції ІF заповнити стовпчик Висновок залежно від швидкості і категорії шляху, вказаної у тому самому рядку:

• швидкість до 21 км/год у житловій та пішохідній зоні;
• швидкість до 61 км/год в населеному пункті;
• швидкість до 130 км/год на решті шляхів (включно!) — молодець, інакше — сплати штраф.

Опис виконання. У попередньо підготовленій таблиці встановити курсор миші в комірку D2. Викликати Помічник з функцій. У вікні діалогу Function Wizard вибрати категорію категорію Логічні функції, функцію ІF і самостійно заповнити поля. Натиснути кнопку Гаразд. Скористатися маркером автозаповнення для поширення результату на інші рядки. Отримати таблицю такого вигляду:

Завдання 4. Завантажити таблицю такого вигляду:

Використовуючи Умовне форматування, змінити тло комірок діапазону бали таким чином:

• помаранчевим, якщо бали вищі за 9;
• жовтим інтенсивним, якщо оцінки від 7 до 9;
• жовтим найменшої інтенсивності, якщо оцінки від 4 до 6;
• блакитним найменшої інтенсивності, якщо оцінки нижчі за 4.

Опис виконання. Виокремити B3:E14 — діапазон комірок, який містить бали. Скористатися вказівкою меню Формат / Conditional Formatting / Conditional Formatting (Умовне форматування). У вікні діалогу Conditional Formatting for B3:E14 4 рази натисніть кнопку Add для створення 4 закладок Condition з номером.

У вікні діалогу Conditional Formatting for B3:E14 на всіх 4 закладках Condition праворуч від напису Apply Style з випадного переліку Apply Style виберіть New Style,

у вікні діалогу Стиль комірки на закладці Тло виберіть колір тла й натисніть Гаразд.

У вікні діалогу Conditional Formatting for B3:E14 натиснути Гаразд. Перевірити, що таблиця набуде такого вигляду:

Завдання 5. В таблиці, отриманій в результаті виконання останнього завдання, виділити:

• прізвища відмінників з інформатики:
• тло — синього кольору;
• шрифт — блакитного;
• прізвища учнів, що мають оцінки з інформатики, нижчі за 4:
• тло — жовтого кольору;
• шрифт — червоного.

Опис виконання. Виокремити А3:А14 — діапазон комірок, який містить прізвища. Скористатися вказівкою меню Формат / Conditional Formatting / Conditional Formatting. У вікні діалогу Conditional Formatting for А3:А14 натиснути двічі кнопку Add для створення закладок Condition. На одній закладці вкажіть умову: «всі оцінки не менші за 10», на іншій — «оцінки менші за 4».

На кожній закладці вкажіть такі параметри:

• праворуч від напису Apply Style з випадного переліку Apply Style виберіть New Style;
• у вікні діалогу Стиль комірки на закладці Тло виберіть колір тла, на закладці Ефекти шрифта — колір шрифта й натисніть кнопку Гаразд.

У вікні діалогу Conditional Formatting for А3:А14 натисніть кнопку Гаразд. Перевірте, чи буде отримано таблицю у такому вигляді: