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

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

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

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

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

• Правила виділення клітинок (Highlight Cells Rules).
• Правила для визначення перших і останніх елементів (Top/Bottom Rules).
• Гістограми (Data Bars).
• Кольорові шкали (Color Scales).
• Набори піктограм (Icon Sets).

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

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

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

• Більше (Greater Than).
• Менше (less Than).
• Між (Between).
• Дорівнює (Equal To).
• Текст, який містить (Text that Containts).
• Дата (A Date Occurring).
• Повторювані значення (Duplicate Values).

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

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

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

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

Встановлення правил форматування:

• виділити необхідний діапазон клітинок з електронної таблиці;
• перейти до вкладки Home (Основне), у розділі Style (Стилі) обрати команду Conditional Formatting (Умовне форматування);
• обираємо потрібний спосіб форматування – Highlight Cells Rules (Правила виділення клітинок);
• обираємо правило форматування – Text that Containts (Текст, який містить);
• у вікні, що відкривається задаємо умову форматування та формат, який буде застосовано, якщо умова виконається;
• формат клітинки можна обирати з вже предустановлених або обравши команду Castom Format (Настроюваний формат) встановити власний у вікні Format Cells (Формат Клітинок);
• для застосування умовного форматування натискаємо кнопку ОК.

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

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

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

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

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

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

Встановлення правил форматування:

• виділити необхідний діапазон клітинок з електронної таблиці;
• перейти до вкладки Home (Основне), у розділі Style (Стилі) обрати команду Conditional Formatting (Умовне форматування);
• обираємо потрібний спосіб форматування – Top/Bottom Rules (Правила визначення перших і останніх елементів);
• обираємо правило форматування – Top 10 items (10 перших елементів);
• у вікні, що відкривається задаємо умову форматування та формат, який буде застосовано, якщо умова виконається;

• формат клітинки можна обирати з вже переустановлених або обравши команду Castom Format (Настроюваний формат) встановити власний у вікні Format Cells (Формат Клітинок);
• для застосування умовного форматування натискаємо кнопку ОК.

Гістограми.

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

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

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

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

Встановлення правил форматування:

• виділити необхідний діапазон клітинок з електронної таблиці;
• перейти до вкладки Home (Основне), у розділі Style (Стилі) обрати команду Conditional Formatting (Умовне форматування);
• обирати потрібний спосіб форматування – Data Bars (Гістограми);
• обирати правило форматування – Gradient Fill (Градієнтна заливка) та відповідний колір.

Але, іноді потрібно встановити інші кольори для гістограми або взагалі змінити напрям її відображення. В цьому випадку треба скористатися налаштуванням додаткових правил (More Rules).

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

• виділити необхідний діапазон клітинок;
• перейти до меню умовного форматування;
• обрати тип форматування Data Bars (Гістограми) і натиснути на напис More Rules (Додаткові правила);
• у вікні New Formatting Rule (Нове правило форматування) обрати потрібний тип правила Format all cells based on their values(Форматувати всі клітинки на основі їх значень);
• у розділі Bar Appearanse (Оформлення смуги) обрати тип заливки – Fill, колір заливки – Color, обрати наявність меж – Border, та їх колір, а також встановити напрям відображення смуги – Bar Direction;
• для застосування всіх налаштувань натисніть кнопку ОК.

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

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

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

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

Встановлення правил форматування:

• виділити необхідний діапазон клітинок;
• перейти до вкладки Home (Основне), у розділі Style (Стилі) обрати команду Conditional Formatting (Умовне форматування);
• обирати потрібний спосіб форматування – Color Scales (Кольорові шкали);
• обирати стандартні кольори шкали або самостійно їх налаштувати звернувшись до More Rules (Додаткові правила);
• задати стиль форматування Format Style – 3-Color Scale;
• обрати для значень відповідні кольори;
• для застосування всіх налаштувань натиснути кнопку ОК.

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

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

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

Виділимо клітинки кольором відповідно до вимог:

• стовпець US – 3 Arrows Colored (3 стрілки);
• стовпець EUR – 3 Triangles (3 трикутника).

Встановлення правил форматування:

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

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

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

Для вирішення цієї задачі користувачу необхідно звернутися до менеджера правил умовного форматування Manage Rules (Керувати правилами), відкриється вікно Conditional Formatting Rules Manager (Диспетчер правил умовного форматування), в якому можна 

Практична робота

 Додаткові файли