Умовне форматування в Excel: інструкції та використання

Використовуйте ці інструкції для створення різноманітних форматів

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

Умовне форматування має сенс і допомагає вам

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

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

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

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

Приклади:

• Працівник складу хоче виділити кольоровим фоном усі запаси до 15 штук.
• Усі міста, де проживає понад 150 000 жителів, слід позначити у таблиці.
• Виробнича компанія хоче виділити червоним кольором усі товари, вироблені між 1 січня 2022-2023 років та 31 січня 2022-2023 року.

Швидкий формат:

Вибір однієї або декількох клітин.

Натисніть «Пуск» на вкладці.

Перейдіть до стрілки біля кнопки «Умовне форматування» у «Стилі».

Натисніть «Правила виділення клітинок».

Виберіть потрібну команду, наприклад B. більше ніж, менше, між датою тощо.

Введіть значення та виберіть формат.

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

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

2. Перейдіть на вкладку «Домашня сторінка», у групі Стилі натисніть стрілку поруч із пунктом «Умовне форматування».

3. Натисніть "Керувати правилами": З'явиться діалогове вікно "Менеджер правил умовного форматування".

4. Виберіть дію:

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

5. Щоб змінити діапазон полів у області значень "Застосувати правило до" звіту зведеної таблиці, виконайте наведені нижче дії.

   • Вибір: Натисніть "Лише ці клітинки"
   • Відповідне поле: Натисніть "Усі клітинки з однаковими полями"
   • Поле значення: Натисніть "Усі клітинки"

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

Тепер ви можете виконувати дії в розділі "Редагувати опис правил" у полі списку "Форматувати клітинки лише за допомогою":

Формат за номером, датою чи часом:

Для цього виберіть значення комірки та оператор порівняння та введіть номер, дату чи час. приклад: Для "Між" введіть 300 та 500 або виберіть "Дорівнює" та введіть дату.

Ви можете використовувати формулу, щоб повернути число, дату або час.

Формат за текстом:

Виберіть "Специфічний текст" та "Реляційний оператор".

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

Можна ввести максимум 255 символів.

Значення можна повернути за допомогою формул. Для цього формула повинна починатися з (=). Недійсні формули не відформатовані. Бажано спочатку перевірити формулу на наявність помилок.

Формат за датою:

• Виберіть дату та дату порівняння, напр. Б. Вчора, наступного тижня чи цього місяця

Потім: Щоб вказати формат, натисніть "Формат", з'явиться діалогове вікно "Форматувати клітинки". Виберіть потрібний формат числа, формат заповнення, рамку або шрифт, натисніть "OK".

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

Умовне форматування з базами даних, колірними схемами та наборами символів

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

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

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

Швидкий формат:

Позначте потрібну область (клітинку, таблицю або весь аркуш).

На вкладці «Домашня сторінка» натисніть кнопку «Умовне форматування».

Перейдіть до "панелі даних" і виберіть заповнення.

Форматувати клітинки з кольоровими рамками

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

2-х кольорова гама

Швидкий формат:

Виберіть клітинку, таблицю або весь аркуш.

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

Перейдіть до "колірної шкали" та натисніть потрібний формат двоколірної шкали. Верхній колір означає вищі значення, нижній колір - нижчі.

3-кольорова гама

Третій колір дозволяє візуалізувати середні значення- на додаток до кольорового представлення високих та низьких значень, як у двоколірній шкалі.

Швидкий формат:

Виберіть клітинку, таблицю або весь аркуш.

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

Перейдіть до "колірної шкали" та натисніть потрібний формат триколірної шкали. Верхній колір означає вищі значення, нижній колір - нижчі.

Форматувати клітинки з наборами символів

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

Швидкий формат:

Виберіть клітинку, таблицю або весь аркуш.

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

Перейдіть до "Набори символів" і виберіть потрібний набір символів

Створіть правила форматування самостійно

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

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

Перейдіть до "Умовне форматування" на вкладці "Домашня сторінка".

Виберіть "Нове правило" і підтвердіть за допомогою "OK".

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

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

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

Покрокові інструкції щодо створення правила

1. На вкладці "Домашня сторінка" у групі "Стилі" перейдіть до стрілки біля "Умовне форматування" та натисніть "Керувати правилами". З'явиться діалогове вікно Диспетчер правил умовного форматування.

2. Можливі такі дії:

   • Якщо ви хочете додати умовний формат, перейдіть до "Нове правило".
   • Змінити умовний формат:
   • • Перевірте, чи ви на правильному аркуші.
     • По -перше, у Застосовується до, натисніть Згорнути діалогове вікно. Потім виберіть новий діапазон комірок і перейдіть до "Розгорнути діалогове вікно".
     • Виберіть потрібне правило, натисніть "Змінити правило". З'явиться повідомлення "Змінити правило форматування".

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

   • У розділі "Редагувати опис правила" введіть формулу у "Форматувати значення, для яких це правило відповідає дійсності". Формула починається з (=) і повинна повертати “true” (1) або “false” (0).
   • Натисніть кнопку Формат, щоб відкрити діалогове вікно Форматування клітинок.
   • Тепер виберіть номер, шрифт, формат заповнення тощо, які слід використовувати, якщо значення комірки істинне, і підтвердьте це з "OK". Вибрані формати відображаються в "Попередній перегляд".

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

• ВІДПОЧИНЕННЯ: таким чином можна змінювати кольори рядків автоматично
• ІДЕНТИЧНО: За допомогою цієї формули дані, які ви шукаєте, можна позначити у таблиці Excel, що відповідає певному значенню
• ПОМИЛКА: автоматично позначає порожні клітинки в таблиці Excel або змушує значення помилок зникати з книги
• NOT і ISNUMBER: Форматує всі комірки Excel, які не містять цифр
• MAX: найбільше значення комірки виділено в таблиці
• MIN: дозволяє умовне форматування найменшого значення у списку Excel, щоб мінімум можна було побачити з першого погляду
• ВЛІВО: залежно від рядка символів на початку, клітинки Excel автоматично форматуються та надають однорідний колір тексту
• LARGE та LEFT: дозволяють забарвлювати клітинку з новим вмістом
• ПРАВО: Ячейки можна форматувати на основі останнього символу рядка
• Сьогодні
• МІСЯЦЬ або РІК: дозволяють розділити список Excel на місяці чи роки; За допомогою YEAR можна також позначити дати поточного року та виділити річні значення
• АБО та ТИДЕНЬ: виділяє кольором значення відповідно до дня тижня

Приклад створення правила для перевірок AND та OR:

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

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

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

Формула створення першого правила з використанням зеленого кольору виглядає наступним чином:

= І (ЯКЩО ($ B $ 4 <= 80000,1); IF (ABS ($ B $ 5) <= 1200,1))

Формула для створення другого правила з використанням червоного кольору виглядає наступним чином:

= АБО (ЯКЩО ($ B $ 4 = 1200,1))

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

Умовне форматування можна скопіювати та застосувати до інших або нових даних на аркуші або знову видалити.

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

Щоб скопіювати умовне форматування та вставити його в інші клітинки або діапазони комірок, скористайтеся кнопкою «Передати формат».

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

Тепер перейдіть до "Пуск", а потім "Передати формат". Курсор відображається у вигляді пензля.

Ви можете додати умовне форматування до комірки або діапазону клітинок, перемістивши пензлик по клітинці.

За допомогою ESC ви припиняєте використання пензля.

Ви все ще можете використовувати пензлик, щоб вставити умовне форматування, двічі клацнувши на «Передати формат».

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

Чітке умовне форматування

Розрізняють процес видалення на аркуші та процес видалення в діапазоні клітинок.

Стерти на робочому аркуші або на всьому аркуші

Перейдіть до "Умовне форматування" на вкладці "Домашня сторінка".

Потім натисніть "Видалити правила"

Потім натисніть "Видалити правила у всьому аркуші".

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

Видалити в діапазоні комірок

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

Натисніть кнопку "Збільшувальне скло для аналізу читання" (кнопка розташована внизу праворуч поруч із вибраними даними).

Перейдіть до "Видалити формат".

Знайдіть і видаліть однакові умовні формати на робочому аркуші

Клацніть клітинку з відповідним умовним форматом.

На вкладці "Домашня сторінка" натисніть стрілку поруч із пунктом "Знайти та вибрати", а потім натисніть "Вибрати вміст".

Натисніть «Умовні формати».

У розділі "Перевірка даних" перейдіть до "Те саме" (тут буде вибрано всі комірки з однаковим умовним форматуванням).

На вкладці "Пуск" перейдіть до "Умовне форматування", потім "Видалити правила" та "Видалити правила у вибраних осередках".

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

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

• Деякі умовні формати не працюють у полях у діапазоні значень звіту зведеної таблиці.
• Якщо ви будь -яким чином зміните макет звіту зведеної таблиці, умовне форматування зберігатиметься, доки поля в базових даних не будуть видалені.
• Умовне форматування полів у діапазоні значень може ґрунтуватися на ієрархії даних. Діапазон цього умовного форматування визначається наступним нижчим або наступним вищим рівнем ієрархії в рядках для одного або кількох стовпців або у стовпцях для одного або кількох рядків.
• Діапазон умовного форматування для полів у діапазоні значень можна визначити за допомогою трьох методів:

• • вибір
  • Відповідне поле
  • Поле значення

Метод за замовчуванням - вибір.

Визначення області шляхом виділення

Перейдіть до "Застосувати правило форматування до".

Натисніть діалогове вікно «Нове правило форматування» або діалогове вікно «Редагувати правило форматування».

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

Визначення області через відповідне поле

Цей метод працює, якщо у вас є

• хочуть уникнути необхідності вибирати багато не пов'язаних між собою елементів
• хочуть умовно відформатувати кілька полів у діапазоні значень для рівня ієрархії даних
• Хочете виключити часткові результати

Визначення області через поле значення

Цей метод працює, якщо у вас є

• хочуть уникнути необхідності вибирати багато не пов'язаних між собою елементів
• хочу умовно відформатувати кілька полів у діапазоні значень для всіх рівнів ієрархії
• Хочете включити підсумки та часткові результати.

Висновок: Умовне форматування в Excel полегшує відстеження даних

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

Ще статті про умовне форматування

• Як автоматично генерувати стрілки у списках Excel
• Відображення негативних значень часу в таблицях Excel
• Відсоток негативного кольору червоний
• Визнайте негативні відсотки з першого погляду
• Форматуйте номери з користувацьким форматом у кольорі
• Відформатуйте клітинки Excel, які не мають зазначеного значення
• Автоматично зберігати клітинки з відповідними форматами валют
• Позначте клітинки кольоровими за допомогою функції макросу
• Використовуйте умовне форматування та виділяйте дані, більші або рівні третьої за величиною клітинки
• Зворотні значення істини