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

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

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

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

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

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

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

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

Припустимо, є список цін на бензин в різних штатах, і Ви хочете, щоб ціни, що перевищують $3.7, Були виділені червоним, а менші або рівні $3.45 — зеленим.

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

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

  1. Виділіть таблицю або діапазон, в якому Ви хочете змінити колір заливки осередків. У цьому прикладі ми виділяємо $ B $ 2: $ H $ 10 (Заголовки стовпців і перший стовпець, що містить назви штатів, які не виділяємо).
  2. Відкрийте вкладку Home (Головна), в розділі Styles (Стилі) натисніть Conditional Formatting (Умовне форматування)>New Rule (Створити правило).

Далі натисніть кнопку Format (Формат), щоб вибрати, який колір заливки повинен бути застосований, якщо виконується задана умова.

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

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

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

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

Як налаштувати постійний колір осередки, грунтуючись на її поточному значенні

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

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

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

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

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

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

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

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

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

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

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

Ось так можна виділити всі комірки з заданим значенням (значеннями) за допомогою опції Find All (Знайти всі) в Excel.

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

Ізмененяются кольору заливки виділених осередків за допомогою діалогового вікна «Формат ячеек»

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

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

  • натиснувши Ctrl + 1.
  • клікнувши по будь виділеної комірці правою кнопкою миші і вибравши в контекстному меню пункт Format Cells (Формат ячеек).
  • на вкладці Home (Головна)>Cells (Осередки)>Format (Формат)>Format Cells (Формат ячеек).

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

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

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

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

Змінюємо колір заливки для особливих осередків (порожні, з помилкою в формулі)

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

Використовуємо формулу для зміни кольору заливки особливих осередків в Excel

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

Цей спосіб вирішення завдання Ви, найімовірніше, будете використовувати в 99% випадків, тобто заливка комірок буде змінюватися відповідно до заданого Вами умовою.

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

  1. на вкладці Home (Головна) в розділі Styles (Стилі) натисніть Conditional Formatting (Умовне форматування)>New Rule (Створити правило). Точно також, як на 2-му кроці прикладу Як динамічно змінювати колір осередки, грунтуючись на її значенні.
  2. У діалоговому вікні New Formatting Rule (Створення правила форматування) виберіть варіант Use a formula to determine which cellsto format (Використовувати формулу для визначення форматується осередків). Далі в поле Format values ​​where this formula is true (Форматувати значення, для яких така формула є істинною) введіть одну з формул:

щоб змінити заливку порожніх клітинок

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

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

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

Змінюємо колір заливки особливих осередків статично

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

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

  1. Виділіть таблицю або діапазон і натисніть F5, Щоб відкрити діалогове вікно Go To (Перехід), потім натисніть кнопку Special (Виділити).

Якщо Ви хочете виділити осередки, що містять формули з помилками, відзначте варіант Formulas (Формули)> Errors (Помилки). Як видно на малюнку вище, Вам доступно безліч інших параметрів.

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