Використання перевірки даних із умовним форматуванням

Чому варто поєднувати ці два інструменти?

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

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

Як вони працюють разом

Головна ідея — обидва інструменти можуть використовувати однакові логічні умови. Якщо перевірка даних відхиляє знижки понад 30%, умовне форматування може підсвічувати будь-яку клітинку, де знижка перевищує 30%, червоним кольором. Одне правило контролює, інше — візуалізує.

Налаштування умовного форматування

1. Виділіть потрібні клітинки;
2. Перейдіть у Головна → Умовне форматування → Створити правило;
3. Оберіть Використовувати формулу для визначення клітинок, які потрібно форматувати;
4. Введіть формулу;
5. Встановіть формат (колір заливки, колір шрифту, межі);
6. Натисніть OK.

Приклад 1: Підсвічування некоректних знижок

Правило перевірки вже відхиляє знижки понад 30%. Але що робити зі значеннями, які потрапили до цього правила? Умовне форматування дозволяє виявити їх візуально.

Застосовується до стовпця Discount % (J):

=J2>30

Формат: червона заливка. Будь-яка клітинка зі значенням понад 30% одразу стає червоною.

Приклад 2: Позначення дати завершення раніше дати початку

Перевірка даних блокує нові порушення, але існуючі мають бути видимими. Припустимо, Start Date у стовпці K та End Date у стовпці L:

Застосовується до стовпця End Date L:

=AND(L2<>"", L2<=K2)

Формат: помаранчеве заповнення. Перевірка L2<>"" гарантує, що порожні End Dates не будуть позначені без потреби.

Приклад 3: Виділення неповних рядків

Рядок, у якому Status має значення Closed, але End Date порожній, є логічно неповним. Зробіть його помітним:

Застосовується до стовпця Status (M):

=AND(M2="Closed", L2="")

Форматування: червоний шрифт. Будь-яке закрите замовлення без End Date одразу видно.

Приклад 4: Зелений — для коректних, червоний — для некоректних

Для стовпця на кшталт Customer Email можна використати два правила для створення ефекту світлофора:

• Заливка зелена: =ISNUMBER(FIND("@", G2)) — коректна електронна адреса;
• Заливка червона: =NOT(ISNUMBER(FIND("@", G2))) — некоректна електронна адреса.

Умовне форматування перевіряє правила по черзі — переконайтеся, що більш специфічне правило знаходиться вище у списку пріоритетів (Керування правилами → використовуйте стрілки для зміни порядку).

Керування пріоритетом правил

Коли до однієї клітинки застосовується кілька правил умовного форматування, Excel оцінює їх зверху вниз і застосовує перше співпадіння. Це важливо, коли правила перекриваються.

Для керування пріоритетом:

1. Перейдіть до Головна → Умовне форматування → Керування правилами;
2. Виберіть область листа у верхній частині;
3. Використовуйте стрілки вгору/вниз для зміни порядку правил;
4. Позначте Зупинити, якщо істинно, щоб нижчі правила не перекривали співпадіння.

Завдання

1. Застосувати правило умовного форматування з червоним заповненням до стовпця Discount %:

   • Формула: =J2>30

2. Застосувати правило з помаранчевим заповненням до стовпця End Date:

   • Формула: =AND(L2<>"", L2<=K2)

3. Застосувати правило з червоним шрифтом до стовпця Status:

   • Формула: =AND(M2="Closed", L2="")

4. Застосувати двоправильне світлофорне форматування до стовпця Customer Email:

   • Зелене заповнення: =ISNUMBER(FIND("@", G2))
   • Червоне заповнення: =NOT(ISNUMBER(FIND("@", G2)))

5. Навмисно ввести некоректні значення в кожен стовпець і переконатися, що форматування спрацьовує правильно;

6. Відкрити Manage Rules і потренуватися змінювати порядок правил для email — звернути увагу, як пріоритет впливає на те, яке форматування застосовується, якщо виконуються обидві умови;

7. Додати рядок зі статусом Closed та без End Date — переконатися, що у клітинці Status з'являється червоний шрифт.