Умовне форматування в excel, exceltip
Умовне форматування дозволяє застосовувати форматування осередків вибірково або автоматично на підставі їх значень. Наприклад, ви можете задати умовне форматування таким чином, щоб всі осередки з негативними значеннями зафарбовувати в червоний колір. Коли ви вводите або міняєте значення в осередку, Excel перевіряє його і порівнює з умовами, заданими в правилах. Якщо значення негативне, фон комірки закраситься, інакше залишиться без змін.
Умовне форматування - це простий спосіб визначити осередки з помилковими записами або значеннями певного типу. Ви можете використовувати формат (наприклад, червона заливка), щоб легко ідентифікувати певні осередки.
Види умовного форматування
Коли ви натискаєте на кнопку Умовне форматування, яка знаходиться в групі Стилі вкладки Головна, ви побачите меню, що випадає з наступними опціями:
Правила виділення осередків відкриває додаткове меню з різними параметрами для визначення правил форматування осередків, що містять конкретні значення або знаходиться в певному діапазоні.
Правила відбору перших і останніх значень відкриває опції, що дозволяють задавати формат осередкам на підставі входження їх в топ перших або останніх елементів.
Гістограма відкриває палітру гістограм різних кольорів, які ви можете задати для обраних осередків, для візуалізації значень, що містяться в цих осередках.
Кольорові шкали дозволяє задавати дво- і трехцветовие шкали для кольору фону комірки на основі її значення щодо інших осередків в діапазоні
Створити правило відкриває діалогове вікно Створення правила форматування, яке дозволяє створити для користувача умовне форматування для обраних осередків.
Видалити правила відкриває додаткове меню, де ви можете видалити правила умовного форматування як для обраних осередків, так і на всьому аркуші.
Управління правилами відкриває діалогове вікно Диспетчер правил умовного форматування, яке дозволяє редагувати і видаляти певні правила, а також задавати пріоритет, пересуваючи вниз і вгору за списком правил.
Графічне умовне форматування
Ймовірно, саме круте (і звичайно, просте) умовне форматування, яке можна застосувати до діапазону комірок - це форматування з застосуванням графічних елементів - Гістограми, Кольорові шкали і Набори значків.
На малюнку зображено застосування двох різних правил для форматування для діапазону від 6 до 1 і навпаки. У першому випадку застосовувалися Кольорові шкали, де ми бачимо, як змінюється формат при зміні значення від 6 до 1, у другому - 3 кольорові стрілки.
Визначення конкретних значень в діапазоні осередків
Варіанти умовного форматування Правила виділення осередків і Правила відбору перших і останніх значень дозволяють задавати певний формат осередкам при досягненні заданих умов.
Правила, які ви створили для виконання цих умов, можуть варіюватися в широких межах. Ви можете встановлювати правила, згідно з яким осередки будуть форматироваться, якщо вона містить певний текст або значення, може бути більше / менше або бути в межах певних значень. Також ви можете застосовувати правила форматування для впізнання осередків входять в топ 10 найбільших / найменших значень.
Приклади умовного форматування
Давайте розглянемо приклад, де ми будемо створювати правило зміни формату комірки на червону заливку з темно-червоним шрифтом за умови, якщо значення клітинки містить слово Ні.
Виділяємо діапазон комірок, до якого ми хочемо застосувати умовне форматування. Переходимо по вкладці Основне в групу Стилі, клацаємо кнопку Умовне форматування -> Правила виділення осередків -> Текст містить.
У діалоговому вікні Текст, який містить в лівому текстовому полі необхідно задати фрагмент тексту, який буде умовою для застосування формату до осередку, зазначеного в правому випадаючому списку. У нашому випадку - це Світло-червона заливка і темно-червоний текст.
Клацаємо ОК, щоб наше правило вступило в силу.
Кілька умовного форматування для одного діапазону
Скажімо, ви хочете застосувати три різних умовних форматування до одного і того ж діапазону комірок: перший тип формату, коли осередок містить цільове значення, другий - коли більше мети і третій - коли менше. Нижче описані кроки за завданням формату Бажаючи заливка з темно-жовтим текстом для осередків що містять значення 95, Зелена заливка з темно-зеленим текстом для осередків зі значеннями більше 95 і Світло-червона заливка і темно-червоним текстом для осередків менше 95.
Виділяємо діапазон комірок, до якого ми хочемо застосувати три різних правила умовного форматування. Почнемо зі створення правила для осередків, що містять значення рівне 95. Переходимо по вкладці Основне в групу Стилі, клацаємо кнопку Умовне форматування -> Правила виділення осередків -> Так само. Excel відкриє діалогове вікно Так само, де в лівому текстовому полі необхідно вказати умову 95, а в правому випадаючому списку вибрати формат для цього умови Бажаючи заливка з темно-жовтим текстом.
Далі задаємо умовне форматування для значень більше 95. З меню Умовне форматування -> Правила виділення осередків вибираємо Більше, в діалоговому вікні Більше вказуємо значення, вище якого осередок буде зафарбовувати в зелений колір, і сам формат.
Аналогічну операцію проробляємо для осередків зі значеннями менше 95. На цей раз зі списку правил необхідно вибрати Менше і задати формат з червоною заливкою.
У міру того, як ви будете визначати всі три правила для діапазону, осередки будуть зафарбовувати в певний колір. У підсумку ми отримаємо, приблизно таку картину. Зверніть увагу, що якщо осередок буде порожній, вона теж буде зафарбовувати в червоний колір, припускаючи, що значення осередку менше 95.
Формули в умовному форматуванні
Хоча Excel пропонує велику кількість готових правил, ви можете задати своє власне правило на основі формули. Наприклад, у вас є таблиця з даними про продажі по днях і ви хочете, щоб осередки з вихідними днями (субота і неділя) зафарбовувати.
Виділяємо таблицю з даними, до якої ми хочемо застосувати умовне форматування. Переходимо по вкладці Основне в групу Стилі, клацаємо кнопку Умовне форматування -> Створити правило. У діалоговому вікні Створення правила форматування в поле Виберіть тип правила вибираємо Використовувати формулу для визначення форматується осередків.
В поле Змініть опис правила задаємо умови і формат для нашого правила. У нашому випадку, умовою буде формула = АБО (ДЕНЬНЕД ($ A2; 2) = 6; ДЕНЬНЕД ($ A2; 2) = 7). Як формат я вибрав темно червону заливку.
Післямова
У даній статті ми розглянули один з найпопулярніших інструментів Excel - умовне форматування, яке дозволяє задавати різний вигляд для осередку в залежності від його вмісту. Умовне форматування є корисним інструментом для візуалізації числових даних. У ряді випадків умовне форматування є реальною альтернативою створення діаграми.