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

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

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

В Excel існує декілька способів виділення клітинок за допомоги умовного форматування:

• Правила виділення клітинок.
• Правила відбору перших і останніх значень.
• Гістограми.
• Кольорові шкали.
• Набори піктограм.

Розглянемо їх усі на конкретних прикладах.

Правила виділення клітинок.

Застосовується, коли потрібно в електронній таблиці виділити клітинки (кольором заливки, межами, шрифтом, стилем, певним форматом даних) за обраним критерієм:

• Більше.
• Менше.
• Між.
• Дорівнює.
• Текст, який містить.
• Дата.
• Повторювані значення.

Клітинки можна форматувати на основі їх:

• значень: число, відсоток, формула;
• вмісту: текст, дати, пусті, не пусті, помилки, немає помилок.

Розглянемо застосування умовного форматування виділення клітинок на прикладі електронної таблиці «Зайнятість працівників». Нам необхідно оформити клітинки різними кольорами заливки відповідно до вимог:

• відрядження (вдр) – світло-зеленим;
• відпустка (від) – сірим;
• вихідні (в) – світло-синім;
• лікарняні (л/д) – жовтим;
• прогули (п) – світло-червоним.

Алгоритм налаштування правил умовного форматування:

• виділити необхідний діапазон клітинок електронної таблиці;
• перейти до вкладки Основне, у розділі Стилі обрати команду Умовне форматування;
• визначити спосіб форматування – Правила виділення клітинок;
• обрати правило форматування – Текст, який містить;
• у вікні, що відкривається, вказати умову форматування та формат, який буде застосовано, якщо умова виконається;
• формат клітинки можна вибрати з уже запропонованих або, через команду Настроюваний формат, та налаштувати власні параметри форматування у вікні Формат клітинок;
• для застосування умовного форматування натиснути кнопку ОК.

Правила для визначення перших і останніх елементів.

Застосовується, коли потрібно в електронній таблиці виділити клітинки (кольором заливки, межами, шрифтом, стилем, форматом даних) за обраним критерієм:

• 10 перших елементів;
• Перші 10%
• 10 останніх елементів;
• Останні 10%;
• Більше середнього;
• Менше середнього.

Тобто, відбувається форматування вказаного числа крайніх значень – N (N%) найбільших або найменших елементів, Великих або Менших від середнього арифметичного.

Розглянемо застосування умовного форматування виділення клітинок на прикладі електронної таблиці: «Температури за рік». Виділимо клітинки кольором відповідно до вимог:

• 5 перших значень – світло-червоний;
• 5 останніх значень – світло-блакитним

Алгоритм налаштування правил умовного форматування:

• виділити необхідний діапазон клітинок електронної таблиці;
• перейти до вкладки Основне, у розділі Стилі обрати команду Умовне форматування;
• визначити спосіб форматування – Правила визначення перших і останніх елементів;
• обрати правило форматування – 10 перших елементів;
• у вікні, що відкривається, вказати умову форматування та формат, який буде застосовано, якщо умова виконається;
• для застосування умовного форматування натиснути на кнопку ОК.

Гістограми.

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

Гістограми допомагають наглядно визначити значення в клітинці відносно інших клітинок. Довжина гістограми відповідає значенню в клітинці. Чим вона довша – тим більше значення. Гістограми оптимальні при визначенні основних показників, особливо в великих обсягах даних.

Розглянемо застосування умовного форматування виділення клітинок на прикладі електронної таблиці: «Розрахунок накопичень за вкладом». Виділимо клітинки кольором відповідно до вимог:

• до стовпця Усього застосувати умовне форматування – гістограма;
• колір гістограми – градієнтний, жовтий.

Алгоритм налаштування правил умовного форматування:

• виділити необхідний діапазон клітинок електронної таблиці;
• перейти до вкладки Основне, у розділі Стилі обрати команду Умовне форматування;
• визначити спосіб форматування – Гістограми;
• обрати правило форматування – Градієнтна заливка та відповідний колір.

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

Налаштування додаткових правил для гістограм:

• виділити необхідний діапазон клітинок;
• перейти до меню умовного форматування;
• обрати тип форматування – Гістограми, де клацнути на команду Додаткові правила;
• у вікні Нове правило форматування обрати тип правила – Форматувати всі клітинки на основі їх значень;
• у розділі Оформлення смуги налаштувати: тип заливки, колір заливки, наявність меж та їх колір, а також визначити напрям відображення смуги;
• для застосування всіх налаштувань натиснути кнопку ОК.

Кольорові шкали.

Кольорова шкала – це заливка обраного діапазону клітинок 2-ма або 3-ма кольорами, яка безпосередньо залежить від чисел, що знаходяться в ньому.

Розглянемо застосування умовного форматування виділення клітинок на прикладі електронної таблиці: «Успішність учнів по предметах». Виділимо клітинки кольором відповідно до вимог:

• мінімальні значення – світло-червоний;
• середні значення – білий;
• максимальні значення – світло-зелений.

Алгоритм налаштування правил умовного форматування:

• виділити необхідний діапазон клітинок;
• перейти до вкладки Основне, у розділі Стилі обрати команду Умовне форматування;
• обрати спосіб форматування – Кольорові шкали;
• обрати стандартні кольори шкали або самостійно їх налаштувати звернувшись до команди Додаткові правила;
• у вікні Нове правило форматування обрати тип правила – Форматувати всі клітинки на основі їх значень;
• розгорнути список Стиль формату та обрати параметр – Триколірна шкала;
• для значень визначити відповідні кольори;
• для застосування всіх налаштувань натиснути кнопку ОК.

Набори піктограм.

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

Розглянемо застосування умовного форматування на прикладі електронної таблиці: «Середня глобальна температура (1900-2024)».

Візуалізувати дані за допомоги піктограм:

• стовпець Різниця з попереднім періодом – 3 кольорові стрілки;

Алгоритм налаштування правил умовного форматування:

• виділити необхідний діапазон клітинок;
• перейти до вкладки Основне, у розділі Стилі обрати команду Умовне форматування;
• обирати потрібний спосіб форматування – Набори піктограм;
• обрати відповідний набір піктограм.

Редагування правил.

Якщо на аркуші Excel до деякого діапазону клітинок застосоване умовне форматування, але з годом виникла потреба, змінити параметри форматування, користувачу необхідно звернутися до менеджера правил умовного форматування, тобто розгорнути меню Умовного форматування та обрати команду – Керувати правилами. У вікні Диспетчер правил умовного форматування можна: створювати нові правила, редагувати правила, видаляти правила та змінювати їх порядок.

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