Застосування умовного форматування в програмі Excel

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

Параметри умовного форматування

Умовне форматування за допомогою кольорових шкал

Вибрані параметри умовного форматування

Умовне форматування у вигляді триколірної шкали

Примітка : У версіях, випущених до появи Excel 2007, правила умовного форматування створювалися за допомогою формул і підтримували лише три рівні. Починаючи з версії Excel 2007, засіб умовного форматування постійно поповнювався численними попередньо визначеними правилами, які підтримують до 64 рівнів і застосовуються без зусиль. Якщо потрібне форматування не можна створити, використовуючи попередньо визначені правила умовного форматування, його можна забезпечити за допомогою формули.

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

Повторювані елементи в стовпці даних можна виділити кольором:

Виділені повторювані значення умовного форматування

  1. Виберіть діапазон, у якому потрібно відформатувати повторювані значення кольором, наприклад у цьому випадку це буде стовпець "Ім’я працівника".

  2. На вкладці Основне послідовно виберіть елементи Умовне форматування > Правила виділення клітинок > Дублювати значення.

    Повторювані значення

  3. Виберіть формат із розкривного списку параметрів, а потім натисніть кнопку OK, щоб форматувати клітинки.

    Параметри форматування виділення повторень

Після форматування повторювані значення в діапазоні виділяються вибраним кольором.

Сортування за кольором

Щоб пришвидшити пошук повторюваних значень, можна відсортувати список за кольором. Так буде простіше застосовувати групові зміни до повторюваних значень, наприклад видалити їх.

  1. Клацніть будь-де в діапазоні даних, а потім на вкладці Дані виберіть Сортування й фільтр > Сортувати.

  2. Виберіть у полі Сортувати за стовпець Ім’я працівника, клацніть Сортування за > Колір клітинки. У стовпці Порядок виберіть потрібний колір сортування, виберіть Згори та натисніть кнопку OK.

Дані > Сортування > Параметри сортування за кольором

У відсортованій таблиці повтори групуються вгорі:

Умовне форматування з повторюваними значеннями, відсортованими вгорі списку

Вибравши 10 перших елементів, можна швидко визначити десятку найвизначніших показників у діапазоні, наприклад 10 найважливіших клієнтів у списку відповідно до їхнього рейтингу. Крім того, можна також вибрати 10 останніх елементів, Перші/Останні 10 % або Більше/Менше середнього. Вибравши правило, число 10 можна змінити на будь-яке інше значення. У наведеному нижче прикладі виділено 10 найбільших показників доходу.

10 найпопулярніших елементів умовного форматування, застосованих до діапазону
  1. Виділіть діапазон, який потрібно відформатувати. У наведеному вище прикладі це стовпець "Дохід".

  2. На вкладці Основне послідовно виберіть Умовне форматування > Правила для перших і останніх значень > 10 перших елементів.

    10 найпопулярніших параметрів умовного форматування
  3. Потім можна збільшити або зменшити кількість, а також вибрати колір заливки.

    Вибрані 10 найпопулярніших параметрів умовного форматування

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

Гістограми умовного форматування, застосовані до діапазону
  1. Виділіть діапазон, який слід відформатувати. У цьому випадку це стовпець "Дохід".

  2. На вкладці Основне виберіть Умовне форматування > Гістограми. Виберіть один зі стилів у розділі Градієнтна заливка або Суцільна заливка.

    Колекція стилів гістограм для умовного форматування
  3. Щоб відсортувати список, до якого застосовано форматування у вигляді гістограми, просто виберіть стовпець і на вкладці Дані в групі Сортування й фільтр натисніть кнопку Швидке сортування за зростанням або Швидке сортування за спаданням , щоб сортувати дані за зростанням або спаданням.

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

Набір піктограм умовного форматування, застосованих до діапазону
  1. Виділіть діапазон, який потрібно відформатувати.

  2. На вкладці Основне виберіть Умовне форматування > Набори піктограм, а потім виберіть бажаний стиль набору піктограм.

    Параметри набору піктограм умовного форматування
  3. Excel спробує інтерпретувати дані й відформатувати їх відповідним чином, але якщо потрібно внести змінити, на вкладці Основне виберіть Умовне форматування > Керувати правилами. Потім виберіть правило Набір піктограм і клацніть Редагувати правило. Змініть параметри в розділі Відображати кожну піктограму за такими правилами. У цьому випадку ми налаштуємо правило таким чином, щоб усі значення, більші за 45 000, вважалися позитивними, значення в діапазоні від 40 000 до 45 000 – нейтральними, а ті, що менші за 40 000 – негативними.

    Діалогове вікно з параметрами набору піктограм умовного форматування

Умовне форматування у вигляді триколірної шкали

За допомогою кольорових шкал можна виділити значення, щоб продемонструвати діапазон і порівняти великі та малі величини (у цьому випадку в стовпцях із "Січ" до "Чер").

  1. Виділіть діапазон, що містить значення, які слід відформатувати.

  2. На вкладці Основне виберіть Умовне форматування > Кольорові шкали > бажана шкала. У цьому випадку використано кольорову шкалу червоний – жовтий – зелений.

    Кольорова шкала "червоний – жовтий – зелений"

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

Колекція стилів таблиць Excel

Якщо потрібно лише застосувати форматування таблиці, не застосовуючи інші її функціональні можливості, можна перетворити таблицю назад на діапазон. Клацніть будь-де в діапазоні таблиці, перейдіть на вкладку Робота з таблицями, потім на стрічці виберіть Конструктор > Інструменти > Перетворити на діапазон.

Докладніше про таблиці Excel можна дізнатися тут: Створення або видалення таблиці Excel на аркуші.

Якщо жоден із запропонованих вище варіантів не підходить, можна створити власне правило умовного форматування.

  1. Виділіть клітинки, які потрібно відформатувати.

  2. На вкладці Основне послідовно виберіть елементи Умовне форматування > Створити правило.

    Нове правило форматування

  3. Створіть власне правило та вкажіть для нього параметри форматування, а потім натисніть кнопку OK. Докладні відомості див. в статті Використання формули для застосування умовного форматування.

Потрібна додаткова довідка?

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

Див. також

Короткий посібник. Застосування умовного форматування

Застосування умовного форматування за допомогою формули

Виділення дат в Excel за допомогою умовного форматування

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×