Умовне форматування дозволяє виділяти важливі дані та застосовувати конкретне форматування до комірок, які відповідають певним критеріям.
Умовне форматування Excel — це потужна функція, яка дозволяє змінювати формат комірок на основі умови (або критеріїв). Умовне форматування допомагає виділяти, підкреслювати або відрізняти дані, що зберігаються в електронній таблиці Excel.
Усі правила умовного форматування засновані на простій логіці: якщо умови виконуються, то буде застосовано конкретне форматування; якщо умови хибні, форматування не застосовуватиметься.
Умовне форматування допомагає виділити важливі дані в наборі даних, підкреслити аномалії та візуалізувати дані за допомогою панелей даних, кольорів та наборів піктограм. У цьому підручнику ми розглянемо, як застосувати умовне форматування для виділення клітинок або діапазонів комірок на основі умови.
Як застосувати умовне форматування Excel
Наприклад, ви відповідаєте за управління великим списком інвентарю з кількома товарами та їх відповідною кількістю на складі. І якщо кількість певного товару на складі знижується, скажімо, 50, важливо, щоб вам це потрібно було врахувати, щоб ви могли поповнити його запаси.
Якщо в цьому списку інвентаризації є набір даних із сотнями рядків, не дуже ефективно шукати рядок за рядком, щоб побачити, чи є в цьому стовпці числа нижче «50». Ось тут і стане в нагоді функція умовного форматування. Декількома клацаннями миші ви можете виділити всі значення в стовпці, менший ніж «50».
Умовне форматування з правилами виділення
У нашому прикладі ми маємо аркуш, що містить записи про продажі деяких продуктів. ми хочемо виділити суму, що становить менше 500 у продажах.
Для цього спочатку виберіть діапазон комірок, до якого потрібно застосувати правило (умову). У нашому прикладі ми хочемо виділити суму, меншу за 500, у стовпці «Сума», тому виберіть стовпець D. Ви можете виділити значення в діапазоні комірок або кількох діапазонах комірок або навіть на всьому аркуші.
Потім перейдіть на вкладку «Головна» та натисніть «Умовне форматування». Виберіть «Правила виділення клітинок» зі спадного меню і, оскільки ми хочемо знайти менше 500 значень, натисніть опцію «Менше ніж». Excel пропонує сім попередньо встановлених правил виділення. Ви можете використовувати будь-який з них для виділення даних.
Далі з’явиться діалогове вікно «Менше ніж». При цьому введіть «500» у полі «Формат клітинок, які є МЕНШЕ» та виберіть форматування для виділення у спадному меню поруч із ним.
Тепер клітинки, значення яких містять менше 500, будуть виділені у вибраному форматуванні.
Давайте подивимося, як виділити значення, які містять певний текстовий рядок. У наступному прикладі ми хочемо виділити всіх співробітників з Нового Південного Уельсу (NSW).
Для цього перейдіть на вкладку «Головна» ->Умовне форматування ->Правила виділення клітинок ->Текст, що містить.
Ви можете використовувати інші параметри в горизонтальному спадному меню «Правила виділення клітинок», щоб виділити повторювані значення, дати, більше, рівно або між значеннями.
У діалоговому вікні «Текст, який містить» введіть «NSW» у поле, виберіть форматування та натисніть «ОК».
Результат:
Умовне форматування з правилами зверху/знизу
Правила зверху/знизу — це ще одне корисне вбудовані правила умовного форматування, доступні в Excel. Ці правила дозволяють звернути увагу на верхню (n) кількість елементів, верхню (n) кількість відсотків, нижню (n) кількість елементів, нижню (n) кількість відсотків або значення клітинок, які знаходяться вище середній або нижче середнього.
Скажімо, у вас є запис балів учня в електронній таблиці, і ви хочете дізнатися 10 найкращих виконавців (10 найкращих рейтингів) із цього списку. За допомогою умовного форматування ви можете виділити 10 головних або 15 найпопулярніших або будь-яку (n) кількість найпопулярніших елементів. Щоб виділити 10 найкращих елементів у діапазоні клітинок, спочатку виберіть діапазон (Усього) у таблиці.
Потім перейдіть до «Умовне форматування», розгорніть «Правила зверху/знизу» та виберіть параметр «10 найкращих елементів...».
У діалоговому вікні «10 найкращих елементів» змініть кількість рангів за допомогою крихітних стрілок у лівому полі. Якщо ви хочете виділити перші 20 рангів (загальна оцінка) у своєму списку оцінок, то встановіть число на 20. За замовчуванням вже 10, тому ми залишимо його. Виберіть форматування комірки в правому полі та натисніть «ОК».
Перші 10 балів у стовпці «Усього» виділено, як показано нижче.
Скажімо, ви хочете знайти оцінки нижче середнього в стовпці «Іспит 1» на тому самому аркуші оцінок. Для цього перейдіть на вкладку «Головна» –>Умовне форматування –>Правила зверху/знизу –>Нижче середнього.
Ви можете налаштувати власне умовне форматування замість того, щоб вибирати одну із попередньо визначених умов. Щоб створити власне форматування, виберіть опцію «Спеціальний формат» у вікні «Нижче середнього».
Відкриється нове вікно форматування комірок, тут ви можете налаштувати форматування комірок. Ми вибираємо помаранчевий колір, щоб виділити оцінки нижче середнього. Після завершення двічі натисніть «ОК», щоб побачити результат.
Результат:
Застосуйте панелі даних
Панелі даних — це просто горизонтальні смуги у ваших клітинках. Розмір стовпчика відповідає значенню комірки відповідно до значення інших комірок у вибраному діапазоні. Це означає, що значення короткого стовпчика є низьким порівняно з іншими значеннями клітинки, а довгий стовпець означає, що значення є високим порівняно з іншими значеннями клітинки.
Спочатку виберіть діапазон комірок, які потрібно відобразити за допомогою панелей даних.
Далі перейдіть до «Умовне форматування» на вкладці «Головна», потім розгорніть «Панелі даних» у спадному меню та виберіть панель даних.
Різниця між форматом панелі даних та іншими форматами полягає в тому, що він відображається у всіх клітинках замість того, щоб відповідати певній умові.
Застосувати колірні шкали
Кольорові шкали дуже схожі на панелі даних, оскільки обидва вони співвідносять значення окремої клітинки зі значенням інших комірок у вибраному діапазоні. Проте панелі даних візуалізують значення комірки за довжиною смуги, тоді як колірні шкали роблять це за допомогою колірних градієнтів.
Кожен варіант колірної шкали використовує дво- або триколірні візерунки градієнта. Один колір призначається до найвищих значень, інший колір — до найнижчих значень, а всі інші значення між ними отримують суміш цих двох кольорів.
Для цього ми використаємо той самий приклад, що й для панелей даних. Виберіть діапазон комірок, перейдіть до Головна –> Умовне форматування –> Кольорові шкали. Потім виберіть діапазон кольорів у горизонтальному спадному меню Color Scales.
Коли ми вибрали перший діапазон кольорів, червоний колір призначається найнижчому значенню, зеленому — найвищому, а всі значення між ними є кольорами, які є сумішшю червоного та зеленого кольорів (як показано нижче). Оскільки жовтий отримують шляхом поєднання червоного та зеленого з однаковою інтенсивністю, йому приписуються клітинки із середніми значеннями.
Застосувати набори іконок
Набори значків — це ще один метод візуалізації даних всередині кожної клітинки та співвіднесення клітинок одна з одною.
Виділіть клітинки та натисніть Головна –> Умовне форматування –> Набори значків. Ви можете вибрати будь-який із цих наборів піктограм, який більше підходить для ваших потреб. Для нашого прикладу ми виберемо перший варіант у розділі Directional.
Червоні, жовті та зелені стрілки відповідно вказують на товари з низькою, середньою або високою ціною.
Видалити умовне форматування
Щоб видалити умовне форматування, поверніться до параметра «Умовне форматування» на вкладці «Головна» та натисніть «Очистити правила». Потім виберіть, які правила ви хочете очистити.
Ми виберемо «Очистити правила з усього аркуша», щоб видалити все умовне форматування з аркуша.
Тепер ви дізналися, як використовувати умовне форматування для форматування комірок із попередньо встановленими умовами Excel.