Умовне форматування в 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 (Диспетчер правил умовного форматування), в якому можна створювати нові правила, редагувати правила, видаляти правила та змінювати їх порядок.

додаткові
матеріали