Умовне форматування – це виділення числових та текстових значень за допомогою кольору, гістограм та набору значків для легкого визначення тенденцій та закономірностей у даних. Це один з найкорисніших інструментів Excel, він автоматично змінює формат клітинки, якщо змінюється її значення.
Умовне форматування робить автоматичну зміну кольору тексту або заливки клітинки в залежності від її значення. Його можна застосувати до діапазону клітинок (виділеного або іменованого), а також до звіту зведеної таблиці.
Існуючі правила
Ефект вживання умовного форматування – це багатогодинна економія часу щодня. Для налаштування умовного формату слід скористатися відповідною командою на вкладці «Основне». При її натисканні відкривається меню з правилами.
Верхні 5 команд – це готові сценарії швидкого умовного форматування. Щоб ними скористатися, достатньо вибрати потрібний варіант і зробити мінімальні налаштування. Ці сценарії ми розглянемо нижче.
У нижній частині ще три команди, за допомогою яких відбувається ручне створення, видалення та керування правилами умовного форматування. Про них також поговоримо.
Усі сценарії розбиті на категорії:
- Правила виділення клітинок;
- Правила для перших і останніх значень;
- Гістограми;
- Кольорові шкали;
- Набори піктограм.
Правила виділення клітинок
Ці правила відповідають за перевірку числових значень у клітинках таблиці. Умову та діапазон вибирає сам користувач. Можливі й інші варіанти, наприклад, перевірка тексту.
Розглянемо кожне правило детальніше на конкретних прикладах. Щоб виконати умовне форматування необхідно:
- Виділити діапазон клітинок.
- Перейти за командою Основне – Стилі – Умовне форматування.
- Вибрати «Правила виділення клітинок».
Більше… Якщо значення клітинки, до якої застосовується правило виділення, більше за вказане значення, то в силу набуває заданий формат.
Порогове значення вказується в лівій частині вікна (зараз там 9), готовий формат вибирається зі списку, що випадає праворуч. Можна, звичайно, і самому поставити. Діалогові вікна для інших умов схожі, тому нижче наводяться лише ті, які можуть спричинити труднощі.
Менше… Принцип форматування клітинок той самий, тільки тепер виділятимуться клітинки, значення яких менші за заданий поріг. Ускладнимо завдання. Замість числа, введеного з клавіатури, будемо використовувати адресу клітинки. Для цього, наприклад, у клітинку F2 введемо число 7, і в правилі замість постійного числа вкажемо цю адресу.
Правило Між… чи Дорівнює… працює за аналогічним принципом. Тільки в цих випадках відповідно перевірятиметься діапазон чисел в клітинках або їх рівність.
До одного діапазону клітинок може бути застосовано декілька правил, які послідовно задані. Пріоритет має правило, яке перебуває вище у списку.
Текст, який містить… Умовне форматування даних перевіряє клітинки на збіг частини тексту. Якщо збігається хоча б частина тексту (слово, код, комбінація символів тощо), то інструмент виділяє вибраним форматуванням.
Розглянемо конкретний приклад, у якому потрібно виділити українські страви, але заливку вибрати не зі стандартного списку, а свою власну. Наприклад, синя заливка із напівжирним жовтим текстом.
Умовне форматування виконується таким чином:
- Виділяємо потрібні клітинки (на знімку з екрану нижче, це A2: A7);
- Заходимо Основне – Стилі – Умовне Форматування;
- Обираємо Правила виділення клітинок – Текст, який містить…
- Зліва вказуємо умову, праворуч у списку вибираємо «Настроюваний формат…»
- У вікні формату клітинок, на вкладі Шрифт вказуємо напівжирний, жовтий, а на вкладці заливки вибираємо синій колір. В результаті отримаємо необхідне форматування.
Зазначений приклад наочно показує, що форматувати клітинки за допомогою умовного форматування можна на власний смак. Розглянемо інші пункти розділу «Правила виділення клітинок».
Дата… Можливість форматувати періоди, що відстають від поточної дати, наприклад сьогодні, вчора, останні 7 днів, наступний місяць та ін. Умовне форматування в Excel для дати корисне при контролі платежів, відвантажень тощо.
Повторювані значення… виділяються клітинки з однаковим вмістом. Відмінний спосіб знайти дублікати (повтори). У налаштуваннях можна вибрати і зворотний варіант – виділити тільки унікальні значення.
Правила відбору перших та останніх значень
За допомогою цього правила виділяють найбільші чи найменші значення. Допомагають аналізувати дані, показуючи пріоритети та «слабкі місця».
Щоб застосувати це правило необхідно:
- Виділити потрібний діапазон клітинок.
- Виконати команду Основне – Стилі – Умовне форматування.
- Вибираємо «Правила для перших та останніх чисел».
- У запропонованому списку вибираємо правило.
10 перших елементів... Наприклад, нам потрібно виділити топ 10 клітинок. Кількість у діалоговому вікні вказується будь-яка (можна зробити топ 5, топ 3 та ін.).
Інші правила цього розділу працюють за аналогічним принципом. Тільки замість відбору перших чи останніх чисел будуть відбиратися частки цих чисел.
Більше середнього… Форматуються всі значення, які більші за середню арифметичну виділеного діапазону клітинок.
Гістограми
Цей інструмент дозволяє до кожної клітинки з числом додати стовпець лінійної гістограми, розмір якої визначається відносно максимального значення у виділеному діапазоні. Гістограма в умовному форматуванні може бути градієнтною або суцільною.
Гістограми добре допомагають візуалізувати невеликий набір даних без використання окремих діаграм. Примітно, що, починаючи з версії Excel 2010, гістограми працюють і з від’ємними числами. В результаті, після застосування цього правила таблиця буде виглядати як показано на знімку з екрана нижче.
Кольорові шкали
За своєю дією «Кольорові шкали» має деякі подібності з попереднім правилом, проте забезпечує інше оформлення клітинок. Шкали формуються з різних кольорів і за градієнтом можна швидко знайти мінімальне та максимальне значення в діапазоні. Також вони можуть бути побудовані для синоптичних карток або мати принцип «Світлофора».
Намагайтеся також, щоб кількість кольорів, що використовуються, була не більше трьох. Інакше увага розсіюється і представлення таблиці може стати ще гіршим.
Наприклад, найбільше значення – це червоне, найменше – зелене, а решта клітинок – це плавний перехід від одного кольору до іншого через проміжний білий.
Набори піктограм
Останній тип правил умовного форматування, досить цікавий щодо візуального оформлення, пов’язаний з додаванням значків для діапазону значень. Його принцип полягає в тому, що Excel, знаходить мінімальне та максимальне значення, і цей інтервал ділить на рівну кількість частин. Цих частин може бути 3, 4 або 5.
Набори піктограм, як і інші правила умовного форматування, можна змінювати на свій розсуд. Для цього у розділі «Набори піктограм» вибираємо «Додаткові правила…». У вікні налаштувань можна вказати форматування не лише на основі відсоткового співвідношення інтервалу чисел, а й за конкретними числами.
Наприклад, якщо на скріншоті, що розташований нижче, ціна акцій буде більше 80, то повинно з’явитись зелене коло, якщо більше 80, але менше 40 – жовте коло, і якщо менше 40 – червоний хрест.
Є критерії оцінок у вигляді шкал, кругових діаграм та зірочок. Все це дозволяє визначити динаміку зростання та дізнатися іншу корисну інформацію в таблиці.
Керування правилами
Може так трапитись, що правило потрібно буде змінити. Щоб внести зміни (задати своє форматування) або створити складніше правило, виділяємо діапазон клітинок, для яких потрібно змінити форматування та переходимо в керування правилами.
Відкриється діалогове вікно, де можна створити нове правило, змінити чи видалити існуюче правило. Часто умовне форматування використовують одразу декількох правил. Після натискання кнопки «Редагувати правило…» відкриється вікно, вигляд якого залежить від типу правила, що редагується.
Не важко помітити, що у відкритому вікні є майже всі правила, які вже розглядалися через стандартний інтерфейс умовного форматування на стрічці «Основне». Тому всі розглянуті приклади та правила можна створити через вікно керування правилами.
Щоб створити правило умовного форматування, необхідно на стрічці Основна, зайти в Стилі – Умовне форматування – Керування правилами та натиснути «Створити правило…». Цю ж команду також можна відразу знайти, якщо зайти в Стилі – Умовне форматування.
Використання формули для умовного форматування
Створення правил форматування на основі формул може мати безліч різних умов, які користувач може вигадувати на власний розсуд. Наприклад, для таблиці, зображеної на знімку з екрану нижче, клітинки у яких пройдений кілометраж менше 100, зробити фіолетовою заливкою та білим текстом. Значення норми зазначено у клітинці J3.
Щоб використовувати формулу для умовного форматування, потрібно:
- Виділити клітинки H5:H21.
- Перейти на стрічці Основне у Стилі – Умовне форматування та скористатись командою «Створити правило».
- Вибрати «Використовувати формулу для визначення клітинок для форматування».
- У полі «Форматувати значення, для яких ця формула має значення ІСТИНА:», ввести формулу: =$H5>$J$3;
- Вибрати потрібний формат і натиснути OK.
І насамкінець про видалення правил. Щоб видалити умовне форматування Ексель, необхідно застосувати команду: Умовне форматування – Очистити правила. У запропонованому списку вибрати бажану дію – Очистити правило у виділеному діапазоні або на всьому аркуші.
Відеоінструкція
Умовне форматування в Excel дуже зручна функція, за допомогою якої досягається бажаний вид діапазону клітинок із наочною демонстрацією вибраної умови. Якщо у вас залишилися питання, залиште їх у коментарях, а ми постараємось на них відповісти.
