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

Застосування умовного форматування у звіті зведеної таблиці

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

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

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

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

  Примітка.

  В ієрархії даних дочірні елементи не успадковують від батьківського елемента умовне форматування, а батьківський елемент не успадковує умовне форматування від дочірніх елементів.

• Існують три методи визначення області умовного форматування полів в області значень: за виділенням, за відповідним полем і за полем значення.

Стандартний метод, за яким визначається область полів в області значень, – це виділення. Метод визначення області можна змінити на визначення за відповідним полем або полем значення за допомогою перемикача Застосувати правило форматування до, діалогового вікна Нове правило форматування або діалогового вікна Редагувати правило форматування.

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

Використання кнопки "Швидкий аналіз" " для застосування вибраного умовного форматування до виділених даних. Кнопка "Швидкий аналіз" з'являється автоматично, коли ви виділяєте дані.

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

2. Натисніть кнопки " Швидкий аналіз " " або комбінацію клавіш Ctrl+Q.

3. У спливаючому вікні, що з'явиться, на вкладці "Форматування " переміщуйте вказівник миші на різні параметри, щоб побачити, як виглядатимуть дані, а потім виберіть потрібний параметр форматування.
   

   Примітка.

   • Параметри форматування, що відображаються на вкладці " Форматування ", залежать від виділених даних. Якщо виділений діапазон містить лише текст, доступні такі параметри: Текст містить, Повторювані, Унікальні, Дорівнює та Очищення. Якщо виділений діапазон містить лише числа або текст і числа, доступні такі параметри: Гістограми, Шкала кольорів, Набір піктограм, Більше, Перші 10 % і Очищення.
   • Функція динамічного попереднього перегляду відображає лише ті варіанти форматування, які можна застосувати до вибраних даних. Наприклад, якщо вибрати параметр Повторювані, а вибрані клітинки не містять однакових даних, динамічний попередній перегляд не працюватиме.

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

Завантаження зразка книги

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

Завантажити: Приклади умовного форматування в Excel

Форматування клітинок за допомогою двоколірної шкали

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

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

1. Виділіть одну або кілька клітинок у діапазоні, таблиці або звіті зведеної таблиці.
2. На вкладці Основне у групі Стилі клацніть стрілку розкривного меню Умовне форматування та виберіть пункт Кольорові шкали.
   
3. Виберіть двоколірну шкалу.
   Наведіть вказівник миші на піктограми колірних шкал, щоб побачити, яка з них двоколірна. Верхній колір позначає вищі значення, а нижній – нижчі.

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

Розширене форматування

1. Виділіть одну або кілька клітинок у діапазоні, таблиці або звіті зведеної таблиці.

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

3. Виконайте одну з таких дій:

   • Щоб додати абсолютно новий умовний формат, натисніть кнопку "Створити правило". Відкриється діалогове вікно Нове правило форматування.

   • Щоб додати новий умовний формат на основі вже наявного в списку, виберіть правило та натисніть кнопку "Повторити правило". Повторюване правило з'явиться в діалоговому вікні. Виділіть дублікат і натисніть " Змінити правило". Відкриється діалогове вікно Редагувати правило форматування.

   • Щоб змінити умовний формат, наведені нижче дії.

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

4. У розділі Застосувати правило до, щоб за потреби змінити область для полів в області значень звіту зведеної таблиці за:

   • виділенням (виберіть пункт Виділених клітинок);
   • Усі клітинки підпису значення: клацніть усі клітинки зі <значеннями підписів значень>.
   • Усі клітинки підпису значення (за винятком проміжних підсумків і загального підсумку): Виберіть усі клітинки, які містять <значення підписів> значень для <підпису рядка>.

5. У розділі Виберіть тип правила: виберіть пункт Форматувати всі клітинки на основі їхніх значень (за промовчанням).

6. У розділі Редагувати опис правила в полі списку Стиль формату виберіть пункт Двоколірна шкала.

7. Щоб вибрати тип у полі Тип для Мінімального значення та Максимального значення, виконайте одну з наведених нижче дій.

   • Форматування найнижчих і найвищих значень. Виберіть пункти Найменше значення та Найбільше значення.
     У цьому разі не потрібно вводити Мінімальне та Максимальне Значення.

   • Форматування значення числа, дати та часу.Виберіть елемент "Число" та введіть мінімальне та максимальне значення.

   • Форматування процентного співвідношення. Введіть " МінімальнеЗначення " та "МаксимальнеЗначення".
     Припустимі значення: від 0 (нуля) до 100. Не вводьте знак відсотка.
     Використовуйте процентне значення, якщо потрібно пропорційно візуалізувати всі значення, оскільки розподіл значень є пропорційним.

   • Форматування процентилю.Виберіть пункт Процентиль і введіть мінімальне та максимальне значення. Припустимі значення: від 0 (нуля) до 100.
     Використовуйте процентилі, якщо потрібно візуалізувати групу найвищих значень (наприклад, процентиль верхніх 20ти значень) у пропорції одноколірної градації, а також групу низьких значень (наприклад, процентиль нижніх 20ти значень) у іншій пропорції одноколірної градації, оскільки їх представлено крайніми значеннями, які можуть спричинити асиметрію у візуалізації даних.

   • Форматування результату формули. Виберіть пункт Формула та введіть Мінімальне та Максимальне значення.

     • Формула має повертати значення числа, дати або часу.

     • Формула має починатися знаком рівності (=).

     • Форматування не застосовується, якщо формула неприпустима.

     • Радимо перевірити формулу, щоб переконатися, що вона не повертає значення помилки.

       Примітка.

       • Переконайтеся, що Мінімальне значення менше за Максимальне значення.
       • Для мінімального значення та максимального значення можна вибрати різні типи. Наприклад, для мінімального значення можна вибрати числовий тип, а для максимального значення – відсотковий.

8. Щоб вибрати для шкали кольорів значення Мінімальне і Максимальне, виберіть для кожного з них розділ Колір, після чого вкажіть колір.
   Якщо потрібно вибрати додаткові кольори або створити власний колір, виберіть пункт Інші кольори. Вибрану шкалу кольорів буде відображено в полі Попередній перегляд.

Форматування клітинок за допомогою триколірної шкали в Excel

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

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

1. Виділіть одну або кілька клітинок у діапазоні, таблиці або звіті зведеної таблиці.
2. На вкладці Основне у групі Стилі клацніть стрілку розкривного меню Умовне форматування та виберіть пункт Кольорові шкали.
   
3. Виберіть триколірну шкалу. Верхній колір представляє вищі значення, центральний – середні, а нижній – нижчі значення.
   Наведіть вказівник миші на піктограми колірних шкал, щоб побачити, яка з них триколірна.

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

Розширене форматування

1. Виділіть одну або кілька клітинок у діапазоні, таблиці або звіті зведеної таблиці.

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

3. Виконайте одну з таких дій:

   • Щоб додати новий умовний формат, натисніть кнопку "Створити правило". Відкриється діалогове вікно Нове правило форматування.

   • Щоб додати новий умовний формат на основі вже наявного в списку, виберіть правило та натисніть кнопку "Повторити правило". Повторюване правило копіюється й відображається в діалоговому вікні. Виділіть дублікат і натисніть " Змінити правило". Відкриється діалогове вікно Редагувати правило форматування.

   • Щоб змінити умовний формат, наведені нижче дії.

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

4. У розділі Застосувати правило до, щоб за потреби змінити область для полів в області значень звіту зведеної таблиці за:

   • виділенням (виберіть пункт Лише ці клітинки);
   • Відповідне поле: Виберіть пункт Усі <клітинки полів> значень з однаковими полями.
   • Поле значення. Виберіть пункт Усі <клітинки поля> значень.

5. У розділі Виберіть тип правила: виберіть пункт Форматувати всі клітинки на основі їх значень.

6. У розділі Редагувати опис правила в полі списку Стиль формату виберіть пункт Триколірна шкала.

7. Виберіть тип для Мінімального значення, Середнього значення та Максимального значення. Виконайте одну з наведених нижче дій.

   • Форматування найнижчих і найвищих значень. Виберіть пункт Середнє значення.
     У цьому разі не потрібно вводити Найменше та Найвище значення.

   • Форматування значення числа, дати та часу. Виберіть пункт Число та введіть Мінімальне, Середнє й Максимальне значення.

   • Форматування процентного співвідношення.Виберіть пункт "Відсоток" і введіть мінімальне, середнє ймаксимальне значення. Припустимі значення: від 0 (нуля) до 100. Не вводьте знак відсотка (%).
     Використовуйте відсоткові значення, якщо потрібно пропорційно візуалізувати всі значення, оскільки використання відсоткових значень забезпечує пропорційний розподіл значень.

   • Форматування процентилю. Виберіть пункт Процентиль і введіть значення для Мінімального значення, Середнього значення та Максимального значення.
     Припустимі процентилі: від 0 (нуля) до 100.
     Використовуйте процентилі, якщо потрібно візуалізувати групу найвищих значень (наприклад, процентиль верхніх 20ти значень) у пропорції одноколірної градації, а також групу низьких значень (наприклад, процентиль нижніх 20ти значень) у іншій пропорції одноколірної градації, оскільки їх представлено крайніми значеннями, які можуть спричинити асиметрію у візуалізації даних.

   • Форматування результату формули. Виберіть пункт Формула та введіть Мінімальне, Середнє й Максимальне значення.
     Формула має повертати значення числа, дати або часу. Формула має починатися знаком рівності (=). Форматування не застосовується, якщо формула неприпустима. Радимо перевірити формулу, щоб переконатися, що вона не повертає значення помилки.

     Примітка.

     • Для діапазону клітинок можна встановити мінімальне, середнє й максимальне значення. Переконайтеся, що значення в полі Мінімальне значення менше за значення в полі Середнє значення, яке, у свою чергу, менше за значення в полі Максимальне значення.
     • Для Мінімального значення, Середнього значення та Максимального значення можна вибрати різні типи. Наприклад, можна вибрати тип ''Число'' для Мінімального значення, тип ''Процентиль'' для Середнього значення та тип ''Відсоток'' для Максимального значення.
     • У багатьох випадках найкращим рішенням для Середнє значення є значення за промовчанням у 50%, проте ви можете змінити його відповідно до конкретних потреб.

8. Щоб вибрати для шкали кольорів значення Мінімальне, Середнє значення або Максимальне, відкрийте для кожного значення розділ Колір і виберіть колір.

   • Щоб вибрати додаткові кольори або створити власний колір, виберіть пункт Інші кольори.
   • Вибрану шкалу кольорів буде відображено в полі Попередній перегляд.

Форматування клітинок за допомогою гістограм

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

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

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

1. Виділіть одну або кілька клітинок у діапазоні, таблиці або звіті зведеної таблиці.
2. На вкладці Основне у групі Стиль натисніть стрілку поруч із розділом Умовне форматування, виберіть пункт Гістограми, після чого виберіть піктограму лінійки даних.
   

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

Розширене форматування

1. Виділіть одну або кілька клітинок у діапазоні, таблиці або звіті зведеної таблиці.

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

3. Виконайте одну з таких дій:

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

4. У розділі Застосувати правило до, щоб за потреби змінити область для полів в області значень звіту зведеної таблиці за:

   • виділенням (виберіть пункт Лише ці клітинки);
   • Відповідне поле: Виберіть пункт Усі <клітинки полів> значень з однаковими полями.
   • Поле значення. Виберіть пункт Усі <клітинки поля> значень.

5. У розділі Виберіть тип правила: виберіть пункт Форматувати всі клітинки на основі їх значень.

6. У розділі Редагувати опис правила у полі списку Стиль формату виберіть пункт Лінійка даних.

7. Виберіть значення Minimum і MaximumType. Виконайте одну з таких дій:

   • Форматування найнижчих і найвищих значень. Виберіть пункти Найменше значення та Найбільше значення.
     У цьому разі не потрібно вводити значення для Мінімального значення та Максимального значення.

   • Форматування значення числа, дати та часу.Виберіть елемент "Число" та введіть мінімальне та максимальне значення.

   • Форматування процентного співвідношення. Виберіть пункт Відсоток і введіть Мінімальне та Максимальне значення.
     Припустимі значення: від 0 (нуля) до 100. Не вводьте знак відсотка (%).
     Використовуйте відсоткові значення, якщо потрібно пропорційно візуалізувати всі значення, оскільки використання відсоткових значень забезпечує пропорційний розподіл значень.

   • Форматування процентилю Виберіть пункт Процентиль і введіть Мінімальне значення та Максимальне значення.
     Припустимі процентилі: від 0 (нуля) до 100.
     Використовуйте процентилі, якщо потрібно візуалізувати групу найвищих значень (наприклад, процентиль верхніх 20ти значень) у пропорції лінійки даних, а також групу низьких значень (наприклад, процентиль нижніх 20ти значень) у іншій пропорції лінійки даних, оскільки їх представлено крайніми значеннями, які можуть спричинити асиметрію у візуалізації даних.

   • Форматування результату формулиВиберіть пункт "Формула" та введіть значення для полів "Мінімум" і "Максимум".

     • Формула має повертати значення числа, дати або часу.
     • Формула має починатися знаком рівності (=).
     • Форматування не застосовується, якщо формула неприпустима.
     • Радимо перевірити формулу, щоб переконатися, що вона не повертає значення помилки.

   Примітка.

   • Переконайтеся, що Мінімальне значення менше за Максимальне значення.
   • Для Мінімального значення та Максимального значення можна вибрати різні типи. Наприклад, для Мінімального значення можна вибрати числовий тип, а для Максимального значення – відсотковий.

8. Щоб вибрати колірну шкалу для Мінімального значення та Максимального значення, виберіть пункт Колір.
   Якщо потрібно вибрати додаткові кольори або створити власний колір, виберіть пункт Інші кольори. Вибраний колір лінійки буде відображено в полі Попередній перегляд.

9. Для відображення в клітинці тільки лінійки даних без значень виберіть пункт Відображати лише рядок.

10. Щоб застосувати до гістограм суцільну межу, у списку Рамка виберіть пункт Суцільна межа та виберіть колір межі.

11. Щоб вибрати суцільну або градієнтну смугу, у списку Заливка виберіть пункт Суцільна заливка або Градієнтна заливка.

12. Щоб форматувати від’ємну смугу, натисніть кнопку Від’ємне значення та вісь, а потім у діалоговому вікні Від’ємне значення та настройки осі виберіть параметри заливки від’ємної смуги та кольорів меж. Для осі можна вибрати настройки розташування й кольору. Вибравши потрібні параметри, натисніть кнопку OK.

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

Форматування клітинок за допомогою набору піктограм

Використовуйте набір піктограм для додавання до даних приміток і класифікації даних за трьома-п’ятьма категоріями, відокремленими граничним значенням. Кожна піктограма позначає діапазон значень. Наприклад, у наборі піктограм "3 стрілки" зелена стрілка вверх позначає вищі значення, жовта стрілка вбік – середні, а червона вниз – нижчі.

Наведений приклад працює з кількома прикладами наборів піктограм умовного форматування.

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

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

1. Виділіть клітинки, до яких потрібно застосувати умовне форматування.
2. На вкладці Основне у групі Стиль натисніть стрілку поруч із розділом Умовне форматування, виберіть пункт Набір піктограм, після чого виберіть набір піктограм.
   

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

Розширене форматування

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

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

3. Виконайте одну з таких дій:

   • Щоб додати умовний формат, виберіть команду Створити правило. Відкриється діалогове вікно Нове правило форматування.

   • Щоб додати новий умовний формат на основі вже наявного в списку, виберіть правило та натисніть кнопку "Повторити правило". Повторюване правило копіюється й відображається в діалоговому вікні. Виділіть дублікат і натисніть " Змінити правило". Відкриється діалогове вікно Редагувати правило форматування.

   • Щоб змінити умовний формат, наведені нижче дії.

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

4. У розділі Застосувати правило до, щоб за потреби змінити область для полів в області значень звіту зведеної таблиці за:

   • виділенням (виберіть пункт Лише ці клітинки);
   • Відповідне поле: Виберіть пункт Усі <клітинки полів> значень з однаковими полями.
   • Поле значення. Виберіть пункт Усі <клітинки поля> значень.

5. У розділі Виберіть тип правила: виберіть пункт Форматувати всі клітинки на основі їх значень.

6. У розділі Редагувати опис правила у полі списку Стиль формату виберіть пункт Набір піктограм.

   1. Виберіть набір піктограм. Набір за промовчанням – 3 світлофори (необрамлені). Кількість піктограм, операторів порівняння за промовчанням і значень порога для кожної піктограми може відрізнятися для кожного набору піктограм.

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

   3. Виконайте одну з таких дій:

      • Форматування значення числа, дати та часу. Виберіть пункт Числовий.

      • Форматування процентного співвідношення. Виберіть пункт Відсоток.
        Припустимі значення: від 0 (нуля) до 100. Не вводьте знак відсотка (%).
        Використовуйте відсоткові значення, якщо потрібно пропорційно візуалізувати всі значення, оскільки використання відсоткових значень забезпечує пропорційний розподіл значень.

      • Форматування процентилю.Виберіть пункт "Процентиль". Припустимі процентилі: від 0 (нуля) до 100.
        Використовуйте процентилі, якщо групу найвищих значень (наприклад, процентиль верхніх 20ти значень) потрібно візуалізувати за допомогою певної піктограми, а групу низьких значень (наприклад, процентиль нижніх 20ти значень) – за допомогою іншої піктограми, оскільки їх представлено крайніми значеннями, які можуть спричинити асиметрію візуалізації даних.

      • Форматування результату формули. Виберіть пункт Формула і введіть формулу в кожному полі Значення.

        • Формула має повертати значення числа, дати або часу.
        • Формула має починатися знаком рівності (=).
        • Форматування не застосовується, якщо формула неприпустима.
        • Радимо перевірити формулу, щоб переконатися, що вона не повертає значення помилки.

   4. Щоб перша піктограма представляла нижчі значення, а остання піктограма представляла вищі значення, натисніть кнопку Зворотний порядок піктограм.

   5. Для відображення у клітинці тільки піктограми, без значення, виберіть команду Відображати лише піктограми.

      Примітка.

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

Форматування клітинок, що містять текст, числа або значення дати чи часу

Щоб спростити процес пошуку певних клітинок, можна відформатувати їх за допомогою оператора порівняння. Наприклад, на інвентарному аркуші із сортуванням за категоріями можна виділити жовтим кольором продукти, яких залишилося менше 10 одиниць. Або на аркуші підсумків роботи мережі роздрібної торгівлі можна визначити магазини, які мають прибуток понад 10%, обсяг продажу менше 100 000 грн., а також регіон, ідентичний ''південно-східному''.

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

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

1. Виділіть одну або кілька клітинок у діапазоні, таблиці або звіті зведеної таблиці.
2. На вкладці Основне у групі Стиль клацніть стрілку поруч із пунктом Умовне форматування й виберіть команду Виділити правила клітинок.
   
3. Виберіть потрібну команду, наприклад Між, "Дорівнює", Текст, який містить або Дата.
4. Введіть потрібні значення й виберіть формат.

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

Розширене форматування

1. Виділіть одну або кілька клітинок у діапазоні, таблиці або звіті зведеної таблиці.

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

3. Виконайте одну з таких дій:

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

4. У розділі Застосувати правило до, щоб за потреби змінити область для полів в області значень звіту зведеної таблиці за:

   • виділенням (виберіть пункт Лише ці клітинки);
   • Відповідне поле: Виберіть пункт Усі <клітинки полів> значень з однаковими полями.
   • Поле значення. Виберіть пункт Усі <клітинки поля> значень.

5. У розділі Виберіть тип правила виберіть команду Форматувати лише клітинки, які містять.

6. У розділі Редагувати опис правила у полі списку Форматувати лише клітинки, які містять виконайте одну з наведених нижче дій.

   • Форматування за числом, датою або часом. Виберіть пункт Значення клітинки, виберіть оператора порівняння та введіть число, дату або час.
     Наприклад, виберіть пункт Між і введіть значення 100 й 200 або виберіть пункт Дорівнює та введіть значення 01.01.2009.
     Ви можете також ввести формулу, яка повертає значення числа, дати або часу.

     • Якщо ви вводите формулу, почніть її зі знака рівності (=).
     • Форматування не застосовується, якщо формула неприпустима.
     • Радимо перевірити формулу, щоб переконатися, що вона не повертає значення помилки.

   • Форматування за текстом. Виберіть пункт Певний текст, вибравши оператор порівняння, а потім введіть текст.
     Наприклад, виберіть пункт містить і введіть значення Срібло або виберіть пункт Починається з і введіть значення Три.
     Лапки включено в рядок пошуку; можна також використовувати символи узагальнення. Максимальна довжина рядка – 255 символів.
     Ви можете також ввести формулу, яка повертає текст.

     • Якщо ви вводите формулу, почніть її зі знака рівності (=).
     • Форматування не застосовується, якщо формула неприпустима.
     • Радимо перевірити формулу, щоб переконатися, що вона не повертає значення помилки.

   • Форматування за датою. Виберіть пункт Дати, а потім виберіть порівняння дат.
     Наприклад, виберіть значення Учора або Наступного тижня.

   • Форматування пустих клітинок або клітинок без пробілів. Виберіть пункт пусті клітинки або Без пробілів.
     Пусте значення – це клітинка, яка не містить жодні дані й відрізняється від клітинки, яка містить один або кілька пробілів (пробіли вважаються текстом).

   • Форматування клітинок зі значенням "Помилки" або "Немає помилок". Виберіть пункт "Помилки" або "Немає помилок".
     Значення помилок включають: #####, #VALUE!, #DIV/0!, #NAME?, #N/A, #REF!, #NUM! і #NULL!.

7. Щоб указати формат, натисніть кнопку Форматувати. Відкриється діалогове вікно Формат клітинок.

8. Виберіть формат числа, шрифту, межі або заливки, який потрібно застосувати, якщо значення клітинки відповідає умові, і натисніть кнопку OK.
   Можна вибрати кілька форматів. Вибрані формати буде відображено в полі Попередній перегляд.

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

Ви можете знайти найвищі й найнижчі значення в діапазоні клітинок, які базуються на визначеному граничному значенні. Наприклад, можна знайти 5 продуктів, які продаються найкраще, у регіональному звіті або останні 15% продуктів в опитуванні споживачів, або 25 найвищих заробітних плат у відділі.

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

1. Виділіть одну або кілька клітинок у діапазоні, таблиці або звіті зведеної таблиці.
2. На вкладці Основне у групі Стиль клацніть стрілку поруч із пунктом Умовне форматування і виберіть пункт Правила для визначення перших і останніх елементів.
   
3. Виберіть потрібну команду, наприклад 10 перших елементів або Останні 10 %.
4. Введіть потрібні значення й виберіть формат.

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

Розширене форматування

1. Виділіть одну або кілька клітинок у діапазоні, таблиці або звіті зведеної таблиці.

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

3. Виконайте одну з таких дій:

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

4. У розділі Застосувати правило до, щоб за потреби змінити область для полів в області значень звіту зведеної таблиці за:

   • виділенням (виберіть пункт Лише ці клітинки);
   • Відповідне поле: Виберіть пункт Усі <клітинки полів> значень з однаковими полями.
   • Поле значення. Виберіть пункт Усі <клітинки поля> значень.

5. У розділі Виберіть тип правила виберіть команду Форматувати лише перші або останні значення.

6. У розділі Редагувати опис правила у полі списку Форматувати лише виберіть значення Перші або Останні.

7. Виконайте одну з наведених нижче дій.

   • Щоб указати перше або останнє число, введіть число та зніміть прапорець % вибраного діапазону. Припустимі значення: від 1 до 1000.
   • Щоб указати перше або останнє процентне значення, введіть число та зніміть прапорець % вибраного діапазону. Припустимі значення: від 1 дo 100.

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

   • стовпця та його батьківського поля рядка, вибравши кожну Групу стовпця;
   • рядка та його батьківського поля стовпця, вибравши кожну Групу рядка.

9. Щоб указати формат, натисніть кнопку Форматувати. Відкриється діалогове вікно Формат клітинок.

10. Виберіть формат числа, шрифту, межі або заливки, який потрібно застосувати, якщо значення клітинки відповідає умові, і натисніть кнопку OK.
    Можна вибрати кілька форматів. Вибрані формати буде відображено в полі Попередній перегляд.

Форматування тільки значень, більших або менших за середнє

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

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

1. Виділіть одну або кілька клітинок у діапазоні, таблиці або звіті зведеної таблиці.
2. На вкладці Основне у групі Стиль клацніть стрілку поруч із пунктом Умовне форматування і виберіть пункт Правила для визначення перших і останніх елементів.
   
3. Виберіть потрібну команду, наприклад Більше середнього або Менше середнього.
4. Введіть потрібні значення й виберіть формат.

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

Розширене форматування

1. Виділіть одну або кілька клітинок у діапазоні, таблиці або звіті зведеної таблиці.

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

3. Виконайте одну з таких дій:

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

4. У розділі Застосувати правило до, щоб за потреби змінити область для полів в області значень звіту зведеної таблиці за:

   • виділенням (виберіть пункт Лише ці клітинки);
   • Відповідне поле: Виберіть пункт Усі <клітинки полів> значень з однаковими полями.
   • Поле значення. Виберіть пункт Усі <клітинки поля> значень.

5. У розділі Виберіть тип правила виберіть команду Форматувати лише значення, більші або менші за середнє.

6. У розділі Редагувати опис правила у полі списку Форматувати значення, які виконайте одну з наведених нижче дій.

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

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

   • стовпця та його батьківського поля рядка, вибравши кожну Групу стовпця;
   • рядка та його батьківського поля стовпця, вибравши кожну Групу рядка.

8. Натисніть кнопку Форматувати, щоб відобразити діалогове вікно Формат клітинок.

9. Виберіть формат числа, шрифту, межі або заливки, який потрібно застосувати, якщо значення клітинки відповідає умові, і натисніть кнопку OK.
   Можна вибрати кілька форматів. Вибрані формати буде відображено в полі Попередній перегляд.

Форматування тільки унікальних або повторюваних значень

У наведеному прикладі умовне форматування використовується для стовпця "Інструктор", щоб знайти інструкторів, які навчають учнів у більш, ніж одному класі (повторювані імена інструкторів виділено блідо-червоним кольором). Значення "Кваліфікація", які повторюються у стовпці "Кваліфікація" тільки один раз (унікальні значення), виділено зеленим кольором.

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

1. Виділіть клітинки, до яких потрібно застосувати умовне форматування.
2. На вкладці Основне у групі Стиль клацніть стрілку поруч із пунктом Умовне форматування й виберіть команду Виділити правила клітинок.
   
3. Виберіть пункт Повторювані значення.
4. Введіть потрібні значення й виберіть формат.

Розширене форматування

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

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

3. Виконайте одну з таких дій:

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

4. У розділі Виберіть тип правила виберіть команду Форматувати лише унікальні або повторювані значення.

5. У розділі "Редагувати опис правила" в полі списку "Формат усі " виберіть унікальне або дублююче значення.

6. Натисніть кнопку Форматувати, щоб відобразити діалогове вікно Формат клітинок.

7. Виберіть формат числа, шрифту, межі або заливки, який потрібно застосувати, якщо значення клітинки відповідає умові, і натисніть кнопку OK.
   Можна вибрати кілька форматів. Вибрані формати буде відображено в полі Попередній перегляд.

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

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

1. Виберіть клітинки, які потрібно відформатувати.
2. На вкладці "Основне" натисніть кнопку "Створити правилоумовного форматування>".
   
3. Створіть власне правило та вкажіть для нього параметри форматування, а потім натисніть кнопку OK.
   Якщо потрібні параметри не відображаються, щоб визначити, які клітинки потрібно форматувати, можна скористатися формулою (покрокові інструкції див. в наступному розділі).

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

Якщо потрібні параметри для створення власного правила умовного форматування не відображаються, можна скористатися логічною формулою для визначення умов форматування. Наприклад, вам може знадобитися порівняти значення в виділеному фрагменті з результатом, повернутим функцією, або оцінити дані у клітинках за межами вибраного діапазону, які можуть бути в іншому аркуші тієї самої книги. Формула має повертати значення "Істина" або "Хибність" (1 або 0), але ви можете скористатися умовною логікою, щоб зв'язати в рядок набір відповідних умовних форматів, наприклад різні кольори для кожного з невеликих наборів текстових значень (наприклад, назви категорій товарів).

1. На вкладці Основне у групі Стилі клацніть стрілку розкривного меню Умовне форматування та виберіть пункт Керувати правилами.
   
   Відкриється діалогове вікно Диспетчер правил умовного форматування.
   

2. Виконайте одну з таких дій:

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

3. Щоб за потреби змінити область для полів в області значень звіту зведеної таблиці, у розділі " Застосувати правило до" зробіть ось що:

   • Визначення області за виділенням: Виберіть пункт "Виділені клітинки".
   • Визначення області за відповідним полем: Виберіть пункт Усі клітинки зі <значеннями полів>.
   • Визначення області за полем значення: Виберіть пункт "Усі клітинки", showing <Values field> for <Row (Значення>)

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

   1. У розділі Редагувати опис правила в полі списку Форматувати значення, для яких ця формула має значення ІСТИНА введіть формулу.

      Формула має починатися знаком рівності (=) і повертати логічне значення TRUE (1) або FALSE (0).

   2. Натисніть кнопку Форматувати, щоб відобразити діалогове вікно Формат клітинок.

   3. Виберіть формат числа, шрифту, межі або заливки, який потрібно застосувати, якщо значення клітинки відповідає умові, і натисніть кнопку OK.

      Можна вибрати кілька форматів. Вибрані формати буде відображено в полі Попередній перегляд.
      Приклад 1. Використання двох умовних форматів з умовою, у якій використовуються перевірки AND і OR
      У наведеному нижче прикладі показано використання двох правил умовного форматування. Якщо перше правило не застосовується, застосовується друге.
      Перше правило: покупець будинку запланував виплатити 75 000 доларів США як перший внесок і 1 500 доларів США на місяць як іпотечний платіж. Якщо перший внесок та іпотечний кредит разом відповідають цим умовам, то після виконання форматування клітинки B4 та B5 буде забарвлено зеленим кольором.
      Друге правило: якщо початковий внесок або іпотечний платіж не задовольняють бюджет покупця, клітинки B4 та B5 форматуються червоним кольором. Потрібно змінити деякі значення, наприклад річні відсоткові ставки, період, на який береться позика, перший внесок і суму оплати, щоб в умовно відформатованих клітинках відобразилися зміни.

      Формула для першого правила (застосовується зелений колір)	=AND(IF($B$4<=75000,1),IF(ABS($B$5)<=1500,1))
      Формула для другого правила (застосовується червоний колір)	=OR(IF($B$4>=75000,1),IF(ABS($B$5)>=1500,1))

      
      Приклад 2. Затінювання через рядок за допомогою функцій MOD і ROW 
      Умовне форматування, застосоване до кожної клітинки на цьому аркуші, забарвлює синім кольором кожну другу клітинку діапазону. Можна виділити всі клітинки на аркуші, клацнувши квадрат над рядком 1 і ліворуч від стовпця A. Функція MOD повертає залишок від ділення числа (перший аргумент) на дільник (другий аргумент). Функція ROW повертає номер поточного рядка. Коли номер поточного рядка ділять на 2, завжди отримують залишок 0 для парного числа або 1 для непарного. Оскільки 0 відповідає значенню FALSE, а 1 – значенню TRUE, спеціальне оформлення отримує кожен непарний рядок. У правилі використовується така формула: =MOD(ROW();2)=1.
      

      Примітка.

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

У цьому відео розглянуто основи використання формул з умовним форматуванням.

Копіювання та вставлення умовного форматування

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

1. Клацніть клітинку з умовним форматуванням, яке потрібно скопіювати.

2. Натисніть кнопку "Основне>форматувати за зразком".
   
   Вказівник перетвориться на пензель.

   Порада.

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

3. Щоб вставити умовне форматування, перетягніть пензель через клітинки або діапазони клітинок, які потрібно відформатувати.

4. Щоб припинити використовувати пензель, натисніть клавішу Esc.

Пошук клітинок з умовним форматуванням

Якщо аркуш містить умовне форматування, клітинки можна швидко знайти, щоб скопіювати, змінити або видалити умовне форматування. Скористайтеся командою " Перейти до особливого ", щоб знайти лише клітинки з певним умовним форматуванням або всі клітинки з умовним форматуванням.

Пошук усіх клітинок з умовним форматуванням

1. Клацніть будь-яку клітинку без умовного форматування.
2. На вкладці Основне в групі Редагування клацніть стрілку поруч із кнопкою Знайти й виділити та виберіть Умовне форматування.
   

Пошук клітинок з однаковим умовним форматуванням

1. Клацніть будь-яку клітинку з умовним форматуванням, яке потрібно знайти.
2. На вкладці Основне в групі Редагування клацніть стрілку поруч із кнопкою Знайти й виділити та виберіть Перейти до особливого.
3. Установіть перемикач умовні формати.
4. У розділі перевірка даних установіть перемикач цих самих.
   

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

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

• Відомості про пріоритет правил умовного форматування
  Створювати, редагувати, видаляти або переглядати всі правила умовного форматування у книзі можна в діалоговому вікні Диспетчер правил умовного форматування. (На вкладці Основне натисніть Умовне форматування, а потім оберіть Керування правилами.)
  
  Відкриється діалогове вікно Диспетчер правил умовного форматування.
  
  Якщо застосовано два або кілька правил умовного форматування, ці правила виконуватимуться в порядку пріоритету (зверху вниз) відповідно до списку в цьому діалоговому вікні.
  Цей приклад стосується ідентифікаційних карток, строк дії яких закінчується. Ми хочемо позначити дійсні картки, що закінчуються на протязі 60 дні, жовтим кольором фону, а картки, що скінчилися – червоним кольором фону.
  
  У цьому прикладі клітинки з ідентифікаційними номерами тих працівників, чий термін авторизації завершиться в межах наступних 60 днів, виділено жовтим кольором, а ідентифікаційні номери тих працівників, чий термін авторизації завершився, виділено червоним кольором. Правила показано на наведеному нижче зображенні.
  
  Перше правило (яке, якщо виконується, визначає червоний колір тла клітинки) перевіряє значення дати у стовпці B відносно поточної дати (отриманої за використання функції TODAY у формулі). Призначте формулу першому значенню даних у стовпці B, яке являє собою B2. Формула для цього правила: =B2<TODAY(). Ця формула перевіряє клітинки у стовпці B (клітинки B2:B15). Якщо формула для будь-якої клітинки стовпця B матиме значення "Істина", відповідна клітинка у стовпці A (наприклад, A5 відповідає B5, A11 відповідає B11) буде відформатовано червоним кольором фону. Після перевірки всіх клітинок, указаних у полі "Стосується", за допомогою цього першого правила, перевіряється друге правило. Ця формула перевіряє, чи значення у стовпці B зберігаються менше ніж через 60 днів від поточної дати (наприклад, припустімо, що сьогодні 11.08.2010). Менше ніж через 60 днів після сьогоднішнього значення клітинки B4 після 04.10.2010, тому вона отримує значення "Істина" та має жовтий колір тла. Формула для цього правила: =B2<TODAY()+60. Будь-яка клітинка, яка спочатку була відформатована червоним кольором за найвищим правилом у списку, залишається в спокої.
  Правило, яке розташоване у списку вище, має пріоритет над правилом, що розташоване нижче. За промовчанням нові правила завжди розміщуються вгорі списку, і, таким чином, маються вищий пріоритет. Однак пріоритет правил можна змінити, скориставшись у діалоговому вікні стрілками Вгору та Вниз.
  

• Що відбувається, коли виконуються умови для кількох правил умовного форматування
  Іноді виконуються умови для кількох правил умовного форматування. Ось як вони застосовуються, коли вони не конфліктують або коли конфліктують.
  Коли правила не конфліктують Наприклад, якщо одне правило форматує клітинку жирним шрифтом, а інше правило форматує ту саму клітинку червоним кольором, клітинка відформатується жирним шрифтом і червоним кольором. Оскільки конфлікту між цими форматами немає, застосовуються обидва правила.
  Коли правила конфліктують Наприклад, одне правило форматує шрифт клітинки червоним кольором, а інше – зеленим. Оскільки правила конфліктують, застосовується лише одне з них. Застосовується правило з вищим пріоритетом (розташованим вище у списку в діалоговому вікні).

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

• Що відбувається, якщо виникає конфлікт між умовним і ручним форматуванням
  Якщо правило умовного форматування отримує значення "Істина", воно отримує пріоритет над будь-яким наявним ручним форматуванням для такого самого виділення. Це означає, що в разі конфлікту умовне форматування застосовується, а ручне форматування – ні. Якщо видалити правило умовного форматування, у діапазоні клітинок залишиться ручне форматування.
  Ручне форматування не відображається в діалоговому вікні Диспетчер правил умовного форматування й не використовується для визначення пріоритету.

• Керування припиненням перевірки правил за допомогою прапорця "Припинити, якщо значення ІСТИНА"
  Щоб забезпечити зворотну сумісність, установіть прапорець Припинити, якщо значення TRUE в діалоговому вікні " Керування правилами ", щоб імітувати відображення умовного форматування в попередніх версіях Excel, у яких не підтримується використання більше трьох правил умовного форматування або застосування кількох правил до одного діапазону.
  Наприклад, якщо в діапазоні клітинок більше трьох правил умовного форматування, у попередній версії програми Excel використовується така версія:

  • перевіряються лише перші три правила;
  • застосовується перше за пріоритетом правило, для якого виконується умова;
  • наступні за пріоритетом правила зі значенням TRUE (істина) пропускаються.

  Усі можливі умови для перших трьох правил перелічено в таблиці нижче.

  Якщо правило	Отримує значення	І якщо правило	Отримує значення	І якщо правило	Отримує значення	То
  Перше	TRUE (істина)	Друге	TRUE (істина) або FALSE (хибність)	Третє	TRUE (істина) або FALSE (хибність)	Застосовується перше правило, а друге та третє правила пропускаються.
  Перше	FALSE (хибність)	Друге	TRUE (істина)	Третє	TRUE (істина) або FALSE (хибність)	Застосовується друге правило, а перше та третє правила пропускаються.
  Перше	FALSE (хибність)	Друге	FALSE (хибність)	Третє	TRUE (істина)	Застосовується третє правило.
  Перше	FALSE (хибність)	Друге	FALSE (хибність)	Третє	FALSE (хибність)	Не застосовується жодне правило.

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

  • Щоб перевірити лише перше правило, установіть прапорець Припинити, якщо значення TRUE для першого правила.
  • Щоб перевірити лише перше та друге правила, установіть прапорець Припинити, якщо значення TRUE для другого правила.

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

Змінення порядку обчислення правил умовного форматування

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

1. На вкладці Основне у групі Стилі клацніть стрілку розкривного меню Умовне форматування та виберіть пункт Керувати правилами.
   
   Відкриється діалогове вікно Диспетчер правил умовного форматування.
   
   Відобразяться правила умовного форматування для вибраного діапазону, зокрема тип правила, форматування, діапазон клітинок, до якого застосовується правило, і параметр Припинити, якщо значення ІСТИНА.
   Якщо потрібне правило не відображається у списку Відображати правила форматування для, переконайтеся, що виділено відповідний діапазон клітинок, аркуш, таблицю або звіт зведеної таблиці.

2. Виберіть правило. Одночасно можна вибрати тільки одне правило.

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

   

4. Щоб за потреби припинити перевірку правил на певному етапі, установіть для відповідного правила прапорець Припинити, якщо значення ІСТИНА.

Очищення умовного форматування

Очищення умовного форматування на аркуші

• На вкладці " Основне " клацніть елементи "Умовне форматування",>"Очистити правила>", "Очистити правила в усьому аркуші".

Якщо ви хочете вилучити умовне форматування на аркуші, виконайте наведені нижче дії.

Для всього аркуша

• На вкладці " Основне " клацніть елементи "Умовне форматування",>"Очистити правила>", "Очистити правила в усьому аркуші".

У діапазоні клітинок

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

2. Натисніть кнопку зображення кнопки "Швидкий аналіз" із правим нижнім кутом виділених даних.

   Примітка.

   Кнопка Швидкий аналіз не відображається, якщо:

   • усі клітинки у вибраному діапазоні пусті;
   • лише верхня ліва клітинка вибраного діапазону містить запис, а всі інші – пусті.

3. Виберіть Очищення формату.
   

Пошук і вилучення однакового умовного форматування на всьому аркуші

1. Клацніть клітинку з умовним форматуванням, яке потрібно вилучити на всьому аркуші.
2. На вкладці Основне клацніть стрілку поруч із кнопкою Знайти й виділити та виберіть пункт Перейти до особливого.
3. Установіть перемикач умовні формати.
4. У розділі перевірка даних установіть перемикач цих самих. , щоб вибрати всі клітинки з однаковими правилами умовного форматування.
5. На вкладці "Основне" клацніть елементи "Умовне форматування",>"Очистити правила>","Очистити правила у вибраних клітинках".

Додавання нового правила умовного форматування

1. Відкрийте електронну таблицю в вебпрограма Excel.

2. Виділіть клітинки, які потрібно відформатувати, а потім виберіть "Стилі домівок >> " Умовне форматування > Нове правило. Також можна відкрити область умовного форматування та створити правило, не виділяючи діапазон клітинок.

   

3. Перевірте або відкоригуйте клітинки в полі "Застосувати до діапазону".

4. Виберіть тип правила та настройте параметри відповідно до своїх потреб.

5. Завершивши, натисніть кнопку "Готово ", і правило буде застосовано до вашого діапазону.

Редагування правила умовного форматування

1. Виберіть клітинку з умовним форматуванням, яке потрібно змінити. Або ж можна вибрати елементи "Стилі > домівок>", "Умовне форматування>", "Керування правилами", щоб відкрити область завдань умовного форматування та вибрати наявне правило.

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

   

3. Наведіть вказівник миші на правило та виберіть команду "Редагувати ", клацнувши піктограму олівця. Відкриється область завдань для редагування правил.

4. Змініть настройки правил і натисніть кнопку "Готово ", щоб застосувати зміни.

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

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

1. У відкритій книзі виберіть елементи "Стилі > домівок", "Умовне > форматування", > "Керування правилами".
2. Відкриється область завдань умовного форматування , у якій відображаються правила, вибрані в поточному діапазоні.

Тут можна виконати такі дії:

• Вибір іншої області в меню " Керування правилами " - наприклад, якщо вибрати цей аркуш , програма Excel шукатиме всі правила на поточному аркуші.
• Додайте правило, клацнувши елемент "Створити правило " (знак "плюс").
• Щоб видалити всі правила в області, натисніть кнопку Видалити всі правила (смітник).

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

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

У розкривному списку "Тип правила " виберіть пункт "Формула".

Введіть формулу в поле. Ви можете використати будь-яку формулу, яка повертає логічне значення TRUE (1) або FALSE (0), але ви можете використати функції AND і OR для поєднання набору логічних перевірок.

Наприклад, =AND(B3="Зерно";D3<500) істинний для клітинки в рядку 3, якщо обидва значення мають значення TRUE ( B3="Grain'') і D3<500 .

Очищення умовного форматування

Ви можете очистити умовне форматування у вибраних клітинках або на всьому аркуші.

• Щоб очистити умовне форматування у вибраних клітинках, виділіть клітинки на аркуші. Потім виберіть " Стилі домівки > ", > "Умовне форматування", > "Очистити правила > ", "Очистити правила" у вибраних клітинках.
• Щоб очистити умовне форматування на всьому аркуші, виберіть елементи "Стилі >> основного", "Умовне форматування", > "Очистити правила > ", "Очистити правила в усьому аркуші".
• Щоб видалити правила умовного форматування, виберіть "Стилі > основної>", "Умовне форматування>", "Керування правилами" та скористайтеся піктограмою видалення (смітник) для певного правила або кнопкою "Видалити всі правила".

Форматування клітинок за допомогою шкал кольорів

Колірні шкали – це візуальні інструкції, які допомагають зрозуміти розподіл і варіювання даних. У програмі Excel передбачено як двоколірні, так і триколірні шкали.

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

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

1. Виділіть клітинки, які потрібно умовно відформатувати за допомогою кольорових шкал.
2. Клацніть елементи "Стилі домівок>", > "Кольорові шкали умовного форматування>" та виберіть шкалу кольорів.

Форматування клітинок за допомогою гістограм

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

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

Форматування клітинок за допомогою набору піктограм

Використовуйте набір піктограм, щоб додати до даних примітки та класифікувати дані за трьома-п’ятьма категоріями, відокремленими граничним значенням. Кожна піктограма позначає діапазон значень. Наприклад, у наборі піктограм "3 стрілки" зелена стрілка вверх позначає вищі значення, жовта стрілка вбік – середні, а червона вниз – нижчі.

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

Форматування тільки клітинок, що містять текст, числа або значення дати й часу

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

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

1. Виділіть клітинки, до яких потрібно застосувати умовне форматування.
2. Виберіть "Стилі домівки > ", > "Умовне форматування > ", "Правила виділення клітинок".
3. Виберіть порівняння, наприклад "Між", " Дорівнює", "Текст", "Дата" або " Дата".

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

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

Наприклад, виділити п'ять продуктів, які продаються найкраще, у регіональному звіті, останні 15% продуктів в опитуванні споживачів або 25 найвищих заробітних плат у відділі.

1. Виділіть клітинки, до яких потрібно застосувати умовне форматування.
2. Виберіть "Стилі домівки > ", > "Умовне форматування", > "Правила згори й знизу".
3. Виберіть потрібну команду, наприклад 10 перших елементів або Останні 10 %.
4. Введіть потрібні значення та виберіть формат (колір заливки, тексту або границі).

Форматування тільки значень, більших або менших за середнє

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

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

1. Виділіть клітинки, до яких потрібно застосувати умовне форматування.
2. Виберіть "Стилі домівки> ", > "Умовне форматування", > "Правила згори й знизу".
3. Виберіть потрібний варіант, наприклад "Більше середнього " або " Менше середнього".
4. Введіть потрібні значення та виберіть формат (колір заливки, тексту або границі).

Форматування тільки унікальних або повторюваних значень

1. Виділіть клітинки, до яких потрібно застосувати умовне форматування.
2. Виберіть "Стилі домівки > ", > "Умовне форматування > ", "Правила виділення клітинок", "Повторювані > значення".
3. Введіть потрібні значення та виберіть формат (колір заливки, тексту або границі).

Копіювання та вставлення умовного форматування

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

1. Клацніть клітинку, умовне форматування якої потрібно скопіювати.

2. Натисніть кнопку "Основне>форматувати за зразком".
   
   Вказівник перетвориться на пензель.

   Порада.

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

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

4. Щоб припинити використовувати пензель, натисніть клавішу Esc.