Умовне форматування в Excel: правила, формули та приклади

By Mirko Peters · 2026-11-12

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

Automatically translated with AI from the original post. Read the English original

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

Головне

• Виділіть клітинки та виберіть Conditional Formatting на вкладці Home. Можна також скористатися кнопкою Quick Analysis або комбінацією Ctrl+Q.
• Highlight Cells Rules і Top/Bottom Rules покривають більшість повсякденних потреб: Greater Than, Between, Text that Contains, Duplicate Values, Above Average.
• Для власної логіки виберіть New Rule, а далі Use a formula to determine which cells to format. Формула має починатися зі знака = і повертати TRUE або FALSE.
• У Manage Rules правила виконуються згори вниз. Коли два правила суперечать одне одному, перемагає вище розташоване, а Stop If True зупиняє подальшу перевірку.
• Умовне форматування не може посилатися на інші книги, а текстові критерії обмежені 255 символами.

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

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

Microsoft пише, що умовне форматування можна застосувати до вибраного діапазону, іменованого діапазону або таблиці Excel. У Excel для Windows воно також працює зі звітами зведених таблиць. Є два способи це зробити.

Через стрічку.

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

За допомогою Експрес-аналізу.

1. Виділіть дані. Невелика кнопка Quick Analysis з'являється в нижньому правому куті виділення.
2. Натисніть кнопку або Ctrl+Q.
3. На вкладці Форматирование наведіть вказівник на варіант, щоб переглянути його, а потім клацніть його. Якщо з'явиться діалогове вікно Текст содержит, введіть текст і натисніть OK.

Варіанти на цій вкладці залежать від даних. Для виділення лише з текстом пропонуються такі варіанти, як Текст, Дубликат, Уникальный, Равно і Очистить. Для числового виділення пропонуються Гистограммы, Цвета, Наборы значков, Больше, Первые 10% і Очистить. Це найшвидший спосіб переглянути вигляд, перш ніж його обрати.

Меню на вкладці «Головна» — це справжній набір інструментів, і решта статті йтиме за ним: Правила виділення клітинок, Правила для перших і останніх значень, Гістограми, Колірні шкали, Набори значків, Створити правило, Керувати правилами та Очистити правила. Якщо ви хочете дізнатися більше про саму функцію, перегляньте сторінку умовне форматування.

Виділення клітинок простими правилами

Правила виділення клітинок — це швидкі правила. Microsoft перелічує такі правила, як «Між», «Дорівнює», «Текст містить», «Дата» та «Повторювані значення» (правила «Більше» і «Менше» розташовані в тому самому меню). Кожне з них ставить просте запитання про клітинку та виділяє ті клітинки, де відповідь «так».

Типові випадки використання:

• Більше для стовпця продажів, щоб позначити все, що перевищує ціль.
• Між, щоб показати значення в допустимому діапазоні, і те саме правило з протилежним умовою за межами діапазону, якщо вас цікавлять виключення.
• Дорівнює для точного значення, наприклад коду статусу або оцінки 100.
• Текст містить, щоб позначити кожну клітинку зі словом на кшталт «пізно» або назвою продукту. Часткові збіги також спрацьовують, адже правило шукає текст у будь-якому місці клітинки.
• Дата, щоб позначити дати, які потрапляють у обраний період. У діалоговому вікні Excel пропонує готові варіанти, тож оберіть один із них замість того, щоб вводити дату вручну.
• Повторювані значення, щоб позначити повтори у списку. Це найшвидша перевірка перед створенням зведеної таблиці чи пошуку.

Правила для перших і останніх значень працюють із ранжуванням: «Перші 10 елементів», «Останні 10 %», «Вище середнього» та «Нижче середнього». Обмеження Microsoft для них: кількість перших або останніх значень становить від 1 до 1000, або від 1 до 100, якщо використовується відсоток, а процентилі — від 0 до 100 (число вводиться без знака відсотка).

Невеликий приклад робить це зрозумілішим. Припустimo, стовпець C містить суми замовлень. Виділіть C2 до останнього рядка, оберіть Правила виділення клітинок > Більше, введіть свою ціль і оберіть зелену заливку. Потім додайте правило Менше з мінімальним порогом і помаранчевою заливкою для тих самих клітинок. Тепер найсильніші й найслабкіші замовлення виділяються, а середні лишаються без змін. Змініть число, і кольори теж зміняться.

Гістограми, колірні шкали та набори значків

Ці три інструменти показують, як значення порівнюються в межах діапазону, а не те, чи проходять вони перевірку. Так їх описують сторінки Microsoft:

• Гістограми додають смугу в кожну клітинку. Довша смуга означає більше значення. Можна обрати градієнтну або суцільну заливку, показати межі, відобразити лише смуги та налаштувати формат від’ємних значень.
• Колірні шкали використовують два або три кольори. У двоколірній шкалі один колір позначає великі значення, а інший — малі. Трикольорова шкала додає проміжний колір.
• Набори значків використовують від трьох до п’яти категорій, і кожен значок охоплює певний діапазон значень. Можна змінити порядок значків, показати лише значки або приховати значок для окремих клітинок за допомогою Немає значка в клітинці.

Кроки для всіх трьох однакові: виділіть клітинки, перейдіть до Головна > Умовне форматування, наведіть вказівник на тип і клацніть стиль. На сторінці Microsoft про гістограми, колірні шкали та набори значків є практична порада: якщо розширити стовпець із гістограмами, різницю між значеннями легше побачити. Набори значків можна поєднувати з іншими умовними форматами.

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

Щодо обмежень для колірних шкал Microsoft зазначає, що мінімум має бути меншим за максимум, а середина за замовчуванням становить 50 відсотків.

Створіть власне правило за допомогою формули

Коли вбудованих правил недостатньо, скористайтеся командою Нове правило. Вона відкриває діалогове вікно Створення правила форматування, де ви вибираєте Використовувати формулу для визначення клітинок, які потрібно форматувати. За правилом Microsoft формула має починатися зі знака = і повертати значення TRUE або FALSE. Клітинки, для яких формула повертає TRUE, отримують формат. Спочатку перевірте формулу на помилки, а якщо помилки можливі, використовуйте функції IS або IFERROR.

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

Приклади Microsoft показують, що можливо:

• Строки дії. =B2<TODAY() для червоної заливки прострочених бейджів і =B2<TODAY()+60 для жовтої заливки бейджів, строк дії яких спливає протягом 60 днів. Червоне правило має стояти в списку вище за жовте, інакше жовте правило також захоплюватиме прострочені клітинки.
• Чергування рядків. =MOD(ROW(),2)=1 затінює кожен непарний рядок.
• Дублікати. =COUNTIF($A$2:$A$400,A2)>1 позначає дублікати в діапазоні A2:A400.
• Дві умови. У прикладі Microsoft з бюджетом використовуються AND і OR, щоб пофарбувати результат у зелений, коли і перший внесок, і щомісячний платіж вкладаються в бюджет, і в червоний, коли не вкладається жоден з них.

Зверніть увагу на шаблон у прикладі з COUNTIF: діапазон є абсолютним ($A$2:$A$400), а клітинка, яку перевіряємо, є відносною (A2). Саме так виглядають більшість корисних формульних правил.

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

Робочий приклад: трекер завдань

Ось налаштування, яке можна скопіювати за п'ять хвилин. У стовпці A містяться назви завдань, у стовпці B – строки виконання, у стовпці C – статус, наприклад Open або Done, а в стовпці D – години.

1. Виділіть клітинки від B2 до останнього рядка. Додайте правило з формулою =B2<TODAY() і червоною заливкою. Так позначаються всі прострочені завдання.
2. Для тих самих клітинок додайте =B2<TODAY()+7 із жовтим заливанням і перемістіть червоне правило вище за нього в Manage Rules. Тепер прострочені завдання червоні, завдання з терміном протягом тижня жовті, а решта залишаються без змін.
3. Виділіть усю таблицю, від A2 до D100, і додайте правило з формулою на кшталт =$C2="Done" із сірим шрифтом. Знак долара фіксує стовпець, тому весь рядок стає сірим, коли в статусі стоїть Done. Ця формула є моїм власним шаблоном, а не одним із прикладів Microsoft, але вона відповідає тим самим правилам: починається зі знака рівності й повертає TRUE або FALSE.
4. Виділіть D2 і нижче та застосуйте Гістограми, щоб найважчі завдання було легко помітити.

Може піти не так два моменти. Якщо червоне правило розташоване нижче за жовте, прострочені завдання виглядатимуть жовтими, адже обидві умови істинні, а перемагає вище розташоване правило. Якщо правило Done стоїть угорі і ви ввімкнули Stop If True, кольори дат для завершених завдань зникнуть, що, ймовірно, саме те, чого ви хочете. Спершу перевірте це на копії й змініть порядок, щоб побачити ефект.

Керування порядком правил

Відкрийте Умовне форматування > Керування правилами, щоб побачити Диспетчер правил умовного форматування. Microsoft пояснює, як це працює:

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

Якщо ви бачите несподіваний колір, шукайте причину в Диспетчері правил.

Копіювання, пошук і очищення умовного форматування

• Копіювання на інші клітинки. Використовуйте Format Painter на вкладці Home. Двічі клацніть по ньому, щоб застосувати формат до кількох ділянок, і натисніть Esc, щоб зупинити. Якщо правило використовує формулу, перевірте посилання після копіювання.
• Пошук відформатованих клітинок. Використовуйте Find & Select > Conditional Formatting або Go To Special > Conditional formats.
• Очистьте правила. Виділіть клітинки, а потім виберіть Главная > Умовне форматування > Очистити правила > Очистити правила у виділених клітинках або Очистити правила на всьому аркуші.

Одне обмеження від Microsoft: вставлення відформатованих клітинок в інший екземпляр Excel не переносить правило, тож якщо правила мають зберегтися, копіюйте в межах одного екземпляра.

Обмеження та причини, чому правило може не працювати

• Інша книга. Умовне форматування не може посилатися на зовнішні книги. Спочатку імпортуйте дані в поточний файл.
• Зведені таблиці. В області значень зведеної таблиці не можна форматувати за текстом, датою або за унікальними чи повторюваними значеннями. Там працюють лише правила для чисел.
• Довгий текст. Текстові критерії обмежені 255 символами.
• Порядок. Правило вище має пріоритет над нижнім. Перемістіть конкретніше правило вгору.
• Ручна заливка. Якщо в клітинки є ручна заливка, а правило істинне, перемагає правило. Якщо результат виглядає неправильно, перевірте, чи правило справді істинне.
• Excel для вебу. На сторінці, яку я використовував, не описано кроків і обмежень, специфічних для вебу. Я б не обіцяв, що кожен тип правил поводиться в браузері однаково. Тестуйте на копії.

Типові помилки

• Написання формули для неправильної клітинки, через що підсвічування зсувається на один рядок.
• Використання абсолютних посилань ($A$1), коли потрібне відносне, і фарбування всього або нічого.
• Нагромадження правил на правилах, доки ніхто не розуміє, чому клітинка червона. Відкрийте Керування правилами і наведіть лад.
• Вставлення відформатованих клітинок в інше вікно Excel із очікуванням, що правила перенесуться. Microsoft каже, що ні.
• Покладатися лише на червоний і зелений.
• Застосування правила до всього стовпця, коли потрібні лише 200 рядків.

Пов'язані теми для вивчення

• Знаходьте дублікати перед видаленням. Правило «Повторювані значення» — добра перевірка перед очищенням списку.
• Розкривні списки і правила разом. Позначайте рядок за статусом, який хтось обирає зі списку.
• Умовне форматування в зведених таблицях. В області значень працюють лише правила для чисел, тож плануйте макет заздалегідь.
• Панелі показників. Поєднання гістограм даних, піктограм і срізів робить звіт читабельним з першого погляду.
• Продуктивність на великих аркушах. Тримайте діапазони компактними, а кількість правил невеликою. Сторінка Microsoft не публікує цифр, тож тестуйте на своїх даних.

Наступний крок

Відкрийте справжній аркуш і застосуйте одне правило через «Правила виділення клітинок». Потім спробуйте правило з формулою, наприклад =B2<TODAY() для стовпця з датами, і відкрийте Керування правилами, щоб побачити, як працює порядок. Тримайте умовне форматування в Excel простим: один чіткий сигнал на стовпець краще за веселку.