Умовне форматування в Excel – детально з прикладами

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

Як виконати умовне форматування в Excel

Ви можете знайти «Умовне форматування» на першій вкладці стрічки, перейшовши до розділу «Стилі». 

Далі потрібно знайти оком значок стрілки трохи правіше, навести на нього курсор. Потім відкриються налаштування, де можна гнучко налаштувати всі необхідні параметри. 

Далі потрібно вибрати відповідний оператор для порівняння потрібної змінної з числом. Існує чотири оператори порівняння: більше, менше, рівно та між. Вони вказані в меню правил. 

Далі ми вказали ряд чисел у діапазоні A1:A11.

Давайте відформатуємо відповідний набір даних. Потім відкриваємо меню налаштувань «Умовне форматування» і задаємо необхідні критерії відбору даних. У нашому випадку, наприклад, ми виберемо критерій «Більше». 

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

Далі завершіть налаштування кнопкою OK.

Для чого потрібне умовне форматування?

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

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

Візьмемо приклад.

У цій таблиці показано запаси, які є на складі. Має колони. як продукт, середній обсяг продажів (вимірюється в одиницях на тиждень), запас і скільки тижнів залишилося цього продукту.

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

Припустимо, що критерієм для визначення причини паніки є інвентаризація менше 3 тижнів. Це означає, що нам потрібно підготувати замовлення. Якщо запас товару менше двох тижнів, то це говорить про необхідність термінового оформлення замовлення. Якщо таблиця містить величезну кількість позицій, то вручну перевірити, скільки тижнів залишилося, досить проблематично. Навіть якщо скористатися пошуком. А тепер подивимося, як виглядає таблиця, де червоним кольором виділені дефіцитні товари.

І справді, орієнтуватися набагато легше. 

Щоправда, цей приклад навчальний, дещо спрощений порівняно з реальною картиною. А збережені секунди та хвилини при регулярному використанні таких таблиць перетворюються на години. Тепер достатньо просто поглянути на таблицю, щоб зрозуміти, яких саме товарів є дефіцит, а не годинами розбирати кожну клітинку (якщо таких товарних позицій тисячі).

Якщо є «жовті» товари, то їх потрібно починати купувати. Якщо відповідна позиція червона, то це потрібно зробити негайно. 

За значенням іншої комірки

Тепер давайте розглянемо наступний практичний приклад.

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

Отже, якщо проект ще виконується (тобто він ще не завершений, тому позначений літерою «П»), то нам потрібно зробити його фон червоним. Виконані проекти позначені зеленим кольором.

Послідовність наших дій у цій ситуації буде наступною:

1. Виберіть діапазон значень. 
2. Натисніть кнопку «Умовне форматування» – «Створити правило».
3. Зверніть увагу, що в нашому випадку потрібно застосувати формулу у вигляді правила. Далі використовуємо функцію IFщоб виділити відповідні рядки. 

Далі заповніть рядки, як показано на цьому малюнку.

Важливо! Рядки повинні мати абсолютні посилання. Якщо говорити про комірку, то в даному випадку вона змішана (з фіксацією стовпчиком).

Так само створюється правило для тих робочих проектів, які на сьогоднішній день не завершені. 

Ось так виглядають наші критерії.

І нарешті ми маємо в результаті таку базу даних.

Кілька умов

Для наочності візьмемо діапазон A1:A11 із таблиці, щоб продемонструвати, як у реальному практичному прикладі можна використовувати кілька умов для визначення форматування комірки.

Самі умови наступні: якщо число в комірці перевищує значення 6, то воно підсвічується червоним кольором. Якщо він зелений, то колір зелений. І, нарешті, найбільші числа, понад 20, будуть виділені жовтим кольором. 

Існує кілька способів виконання умовного форматування за кількома правилами.

1. Спосіб 1. Виділяємо наш діапазон, після чого в меню налаштувань умовного форматування вибираємо таке правило виділення осередків, як «Ще». Ліворуч пишеться цифра 6, а праворуч задається форматування. У нашому випадку заливка має бути червоного кольору. Після цього цикл повторюється двічі, але вже задані інші параметри – більше 10 і зелений колір і більше 20 і жовтий колір відповідно. Ви отримаєте такий результат.
   

   

2. Спосіб 2. Зайдіть в головне меню налаштувань інструменту «Умовне форматування» Excel. Там знаходимо меню «Створити правило» і клацаємо по цьому пункту лівою кнопкою миші.
   

   

    Після цього Далі виберіть пункт «Використовувати формулу…» (виділено на малюнку червоною рамкою) і задайте першу умову. Після цього натисніть OK. Потім цикл повторюється для наступних умов так само, як описано вище, але використовується формула.

   

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

А ось скріншот для кращого розуміння.

Наприклад, у нас є число 24. Воно відповідає всім трьом умовам одночасно. У цьому випадку буде заливка, яка відповідає першій умові, незважаючи на те, що вона більше підпадає під третю. Тому, якщо важливо, щоб числа вище 20 були заповнені жовтим кольором, третя умова повинна бути поставлена ​​на перше місце. 

Умовне форматування дати

Тепер давайте подивимося, як умовне форматування працює з датами. У нас такий класний асортимент.

У цьому випадку вам потрібно встановити правило умовного форматування, наприклад «Дата».

Після цього відкривається діалогове вікно з цілим набором умов. Детально з ними можна ознайомитися на цьому скріншоті (вони перераховані в лівій частині екрана у вигляді списку).

Нам залишається лише вибрати відповідний і натиснути кнопку «ОК».

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

Використання формул

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

Для цього в меню умовного форматування натисніть кнопку «Створити правило», а потім виберіть пункт, зображений на скріншоті.

Рядки (за значенням клітинки)

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

Як створити правило

Щоб створити правило, необхідно скористатися відповідним параметром у розділі «Умовне форматування». Розглянемо конкретну послідовність дій. 

Для початку потрібно знайти на стрічці у вкладці «Головна» пункт «Умовне форматування». Його можна впізнати за характерними різнокольоровими клітинками різного розміру з малюнком поруч із перекресленим знаком рівності. 

Є кнопка «Створити правило». Ви також можете натиснути кнопку «Керувати правилами», після чого відкриється діалогове вікно, через яке також можна створити правило, натиснувши відповідну кнопку у верхньому лівому куті. 

Також у цьому вікні можна побачити ті правила, які вже застосовано.

Правила вибору клітин

Гаразд, ми так багато говоримо про те, як створити правила, але що це? Правило вибору комірок відноситься до особливостей, які враховуються програмою, щоб вирішити, як форматувати комірки, які підходять до них. Наприклад, умови можуть бути більше певного числа, менше певного числа, формула тощо. Їх є цілий набір. З ними можна ознайомитися і потренуватися в їх використанні «в пісочниці». 

Форматування всіх клітинок на основі їх значень

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

Форматуйте лише унікальні або повторювані клітинки

Існує велика кількість правил, які безпосередньо включені в програму. Ознайомитися з ними можна в меню «Умовне форматування». Зокрема, щоб форматування поширювалося виключно на повторювані значення або тільки на унікальні, існує спеціальна опція – «Повторювані значення».

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

Значення формату в діапазоні нижче і вище середнього

Для форматування значень нижче або вище середнього також є спеціальна опція в цьому ж меню. Але потрібно вибрати інше підменю – «Правила вибору першого та останнього значень». 

Форматування лише першого та останнього значень

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

Форматування лише клітинок із певним вмістом

Щоб відформатувати лише клітинки з певним вмістом, потрібно вибрати правило форматування «Дорівнює» або «Текст містить». Різниця між ними полягає в тому, що в першому випадку рядок має відповідати критерію повністю, а в другому – лише частково. 

Як бачите, умовне форматування - це багатофункціональна функція програми Excel, яка дає величезну кількість переваг людині, яка її освоїла. Ми знаємо, що на перший погляд все це може здатися складним. Але насправді до нього тягнуться руки, як тільки він з'являється виділити певний фрагмент тексту червоним кольором (або відформатувати його будь-яким іншим чином), виходячи з його змісту або за іншим критерієм. Ця функція входить в базовий набір знань Excel, без неї неможлива навіть аматорська робота з електронними таблицями і базами даних.