2 способи змінити колір заливки клітинок в Excel на основі їх значень

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

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

• Змінюйте колір заливки клітинок на основі їх значень (динамічно) – колір заливки змінюватиметься автоматично, коли змінюється значення клітинки.
• Змінити колір клітинки на основі її поточного значення (статично) – після встановлення колір заливки не змінюється незалежно від того, як змінюється значення клітинки.
• Зміна кольору окремих комірок (порожніх, з помилками, з формулами)

Як динамічно змінювати колір клітинки в Excel на основі її значення

Колір заливки змінюватиметься залежно від значення комірки.

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

Рішення: Використовуйте умовне форматування в Excel, щоб виділити значення, більші за X, менші за Y або між X і Y.

Припустімо, у вас є список цін на газ у різних штатах, і ви хочете, щоб ціни були вищими, ніж 3.7 XNUMX$, були виділені червоним і менші або рівні 3.45 XNUMX$ – зелений.

Примітка: Знімки екрана для цього прикладу було зроблено в Excel 2010, однак у Excel 2007 і 2013 кнопки, діалогові вікна та параметри будуть абсолютно такими самими або дещо відрізнятимуться.

Отже, ось що вам потрібно зробити крок за кроком:

1. Виберіть таблицю або діапазон, у якому потрібно змінити колір заливки комірки. У цьому прикладі ми виділяємо $B$2:$H$10 (заголовки стовпців і перший стовпець з назвами штатів не виділені).
2. Натисніть Головна (Додому), в розд Стилі (Стилі) натисніть Умовне форматування (Умовне форматування) > Нові правила (Створіть правило).
3. У верхній частині діалогового вікна Нове правило форматування (Створити правило форматування) у полі Виберіть тип правила (Виберіть тип правила) виберіть Форматувати лише комірки, що містять (Відформатуйте лише клітинки, які містять).
4. У нижній частині діалогового вікна в полі Форматувати лише клітинки з (Відформатуйте лише клітинки, які відповідають наступній умові) Встановіть умови для правила. Ми обираємо форматувати лише клітинки з умовою: Значення комірки (значення клітинки) – більше (більше) – 3.7як показано на малюнку нижче.Потім натисніть кнопку Розмір (Формат), щоб вибрати колір заливки, який слід застосувати, якщо виконано вказану умову.
5. У діалоговому вікні, що з’явиться Формат ячеек Вкладка (Формат клітинок). Заповнювати (Заливка) і виберіть колір (ми вибрали червонуватий) і натисніть OK.
6. Після цього ви повернетеся до вікна Нове правило форматування (Створення правила форматування), де в полі попередній перегляд (Зразок) покаже зразок вашого форматування. Якщо ви задоволені, натисніть OK.

Результат налаштувань форматування виглядатиме приблизно так:

Оскільки нам потрібно встановити іншу умову, яка дозволяє нам змінити колір заливки на зелений для комірок зі значеннями, меншими або рівними 3.45, потім знову натисніть кнопку Нові правила (Створити правило) і повторіть кроки 3–6, установивши потрібне правило. Нижче наведено приклад другого правила умовного форматування, яке ми створили:

Коли все буде готово – натисніть OK. Тепер у вас є гарно відформатована таблиця, яка дозволяє швидко побачити максимальні та мінімальні ціни на газ у різних штатах. Добре їм там, у Техасі! 🙂

Порада: Таким же чином можна змінити колір шрифту в залежності від значення комірки. Для цього достатньо відкрити вкладку шрифт (Шрифт) у діалоговому вікні Формат ячеек (Формат клітинки), як ми робили в кроці 5, і виберіть потрібний колір шрифту.

Як встановити постійний колір комірки на основі її поточного значення

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

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

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

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

Знайдіть і виділіть усі клітинки, які відповідають заданій умові

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

Якщо ви хочете розфарбувати комірки певним значенням, наприклад, 50, 100 or 3.4 – потім на табл Головна (Додому) в розділі Редагування (Редагування) натисніть Знайти Вибрати (Знайти та виділити) > знайти (Знайти).

Введіть потрібне значення та натисніть Знайти все (Знайти все).

Порада: У правій частині діалогового вікна Знайти і замінити (Знайти та замінити) є кнопка Опції (Параметри), натиснувши яку, ви отримаєте доступ до ряду розширених налаштувань пошуку, таких як Match Case (з урахуванням регістру) і Відповідає вмісту всього комірки (Всю клітинку). Ви можете використовувати символи підстановки, такі як зірочка (*), щоб відповідати будь-якому рядку символів, або знак питання (?), щоб відповідати будь-якому окремому символу.

Щодо попереднього прикладу, якщо нам потрібно знайти всі ціни на бензин з 3.7 до 3.799, тоді ми встановимо такі критерії пошуку:

Тепер натисніть будь-який із знайдених елементів у нижній частині діалогового вікна Знайти і замінити (Знайти та замінити) і натисніть Ctrl + Aщоб виділити всі знайдені записи. Після цього натисніть кнопку Фермер (Закрити).

Таким чином ви можете вибрати всі клітинки з заданим значенням (значеннями) за допомогою параметра Знайти все (Знайти все) в Excel.

Однак насправді нам потрібно знайти всі ціни на бензин, які перевищують 3.7 XNUMX$. На жаль інструмент Знайти і замінити (Знайти та замінити) не може нам у цьому допомогти.

Змініть кольори заливки вибраних клітинок за допомогою діалогового вікна «Формат клітинок».

Тепер у вас вибрано всі комірки з заданим значенням (або значеннями), ми щойно зробили це за допомогою інструмента Знайти і замінити (Знайти і замінити). Все, що вам потрібно зробити, це встановити колір заливки для виділених клітинок.

Відкрийте діалогове вікно Формат ячеек (Формат комірки) будь-яким із 3 способів:

• пресування Ctrl + 1.
• клацнувши на будь-якій виділеній комірці правою кнопкою миші та вибравши пункт контекстного меню Формат ячеек (формат комірки).
• таб Головна (Додому) > Клітини. Клітини. (клітини) > Розмір (Формат) > Формат ячеек (формат комірки).

Далі налаштуйте параметри форматування на свій розсуд. Цього разу ми встановимо помаранчевий колір заливки, просто для різноманітності 🙂

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

Ось результат наших змін форматування в Excel:

На відміну від попереднього методу (з умовним форматуванням), встановлений таким чином колір заливки ніколи не зміниться сам без вашого відома, як би не змінювалися значення.

Змінити колір заливки спеціальних комірок (порожні, з помилкою у формулі)

Як і в попередньому прикладі, ви можете змінити колір заливки окремих комірок двома способами: динамічним і статичним.

Використовуйте формулу, щоб змінити колір заливки спеціальних клітинок у Excel

Колір комірки змінюватиметься автоматично залежно від значення комірки.

Ви, швидше за все, скористаєтеся таким способом вирішення задачі в 99% випадків, тобто наповнення комірок буде змінюватися відповідно до заданої вами умови.

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

1. На вкладці Додатково Головна (Додому) в розділі Стилі (Стилі) натисніть Умовне форматування (Умовне форматування) > Нові правила (Створіть правило). Так само, як у 2-му кроці прикладу Як динамічно змінювати колір клітинки на основі її значення.
2. У діалоговому вікні Нове правило форматування (Створити правило форматування) виберіть параметр За допомогою формули визначте, які клітинки відформатувати (Використовуйте формулу, щоб визначити, які клітинки потрібно відформатувати). Далі в поле Форматувати значення, де ця формула відповідає дійсності (Відформатуйте значення, для яких справедлива наступна формула) введіть одну з формул:
   • щоб змінити заповнення порожніх комірок

     =ISBLANK()

     =ЕПУСТО()

   • щоб змінити затінення клітинок, що містять формули, які повертають помилку

     =ISERROR()

     =ЕОШИБКА()

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

   =ISBLANK(B2:H12)

   =ЕПУСТО(B2:H12)

   

3. Натисніть кнопку Розмір (Формат), виберіть потрібний колір заливки на вкладці Заповнювати (Заповнити), а потім натисніть OK. Детальні інструкції наведено в кроці 5 прикладу «Як динамічно змінити колір комірки на основі її значення». Зразок умовного форматування, яке ви налаштували, виглядатиме приблизно так:
4. Якщо ви задоволені кольором, натисніть OK. Ви побачите, як створене правило буде негайно застосовано до таблиці.

Статично змінюйте колір заливки спеціальних комірок

Після налаштування заливка залишатиметься незмінною, незалежно від значення комірки.

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

1. Виберіть таблицю або діапазон і натисніть F5щоб відкрити діалогове вікно Перейти до (Стрибок), потім натисніть кнопку спеціальний (Виділити).
2. У діалоговому вікні Перейдіть до Special (Виділити групу комірок) поставте прапорець заготовки (Пусті клітинки), щоб вибрати всі порожні клітинки.Якщо ви хочете виділити комірки, що містять формули з помилками, позначте цей параметр Формули (формули) > помилки (Помилки). Як ви бачите на малюнку вище, вам доступно багато інших налаштувань.
3. Нарешті, змініть заливку вибраних клітинок або встановіть будь-які інші параметри форматування за допомогою діалогового вікна Формат ячеек (Форматувати клітинки), як описано в розділі Зміна заповнення виділених клітинок.

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