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

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

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

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

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

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

1. Виберіть клітинки, які потрібно відформатувати.
2. Створіть нове правило форматування, клацнувши відповідний пункт у контекстному меню, що з’являється після натискання кнопки «Умовне форматування» на вкладці «Головна».

1. Після цього з'явиться діалогове вікно, де потрібно вибрати параметр «використовувати формулу для визначення форматованих комірок». Далі запишіть таку формулу: = C2> 4 в полі нижче. Природно, ви можете вставити власну адресу комірки та власний текст, а також замінити знак > на < або =. Крім того, важливо не забути поставити знак $ перед посиланням на комірку, щоб зафіксувати це при копіюванні. Це дає можливість прив'язати колір лінії до значення комірки. Інакше при копіюванні адреса «виїде».
2. Натисніть «Формат» і перейдіть на останню вкладку, щоб вказати потрібний відтінок. Якщо вам не подобаються відтінки, запропоновані програмою, ви завжди можете натиснути «Більше кольорів» і вибрати потрібний відтінок.
3. Після завершення всіх операцій необхідно двічі клацнути по кнопці «ОК». Ви також можете встановити інші типи форматування (тип шрифту або певний стиль межі комірки) на інших вкладках цього вікна.
4. У нижній частині вікна є панель попереднього перегляду, де можна побачити, як буде виглядати комірка після форматування.
5. Якщо вас все влаштовує, натисніть кнопку «ОК», щоб застосувати зміни. Все, після виконання цих дій всі рядки, в яких осередки містять число більше 4, будуть синіми.

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

Застосуйте кілька правил відповідно до їх пріоритету

У попередньому прикладі показано варіант використання одного правила умовного форматування, але ви можете застосувати кілька одночасно. Що робити в такому випадку? Наприклад, можна додати правило, згідно з яким рядки з числом 10 і більше будуть підсвічуватися рожевим. Тут необхідно додатково записати формулу = C2> 9, а потім установіть пріоритети, щоб усі правила могли застосовуватися без конфлікту одне з одним.

1. На вкладці «Головна» в групі «Стилі» потрібно натиснути на «Умовне форматування» і в меню, що з’явилося вибрати «Керування правилами» в самому кінці списку.
2. Далі ви повинні відобразити всі правила, характерні для цього документа. Для цього потрібно вгорі знайти список «Показати правила форматування для», і вибрати там пункт «Цей аркуш». Крім того, за допомогою цього меню ви можете налаштувати правила форматування для окремих вибраних клітинок. У нашому випадку нам потрібно керувати правилами для всього документа.
3. Далі вам потрібно вибрати правило, яке ви хочете застосувати першим, і перемістити його вгору списку за допомогою стрілок. Ви отримаєте такий результат.
4. Після встановлення пріоритетів потрібно натиснути «ОК», і ми побачимо, як відповідні рядки змінили свій колір, відповідно до пріоритету. Спочатку програма перевірила, чи значення в стовпці «Кількість» перевищує 10, а якщо ні, то чи перевищує воно 4.

Зміна кольору цілого рядка на основі тексту, написаного в комірці

Припустімо, що під час роботи з електронною таблицею вам важко швидко відстежити, які товари вже доставлено, а які ні. Або, можливо, деякі застаріли. Щоб спростити це завдання, ви можете спробувати виділити рядки на основі тексту, який знаходиться в комірці «Доставка». Припустимо, нам потрібно встановити такі правила:

1. Якщо замовлення прострочено через кілька днів, колір тла відповідного рядка буде оранжевим.
2. Якщо товар уже доставлений, відповідний рядок стає зеленим.
3. Якщо доставка товару прострочена, то відповідні замовлення повинні бути виділені червоним кольором.

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

В цілому логіка дій для доставлених і прострочених замовлень буде такою ж, як і в прикладі, описаному вище. У вікні умовного форматування необхідно прописати формули =$E2=»Доставлено» и =$E2=»Прострочено» відповідно. Трохи складніше завдання для угод, які закінчуються протягом кількох днів.

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

Для цього випадку є функція =ПОШУК(“До закінчення”, $E2)>0, де:

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

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

Порада. Параметр >0 у формулі означає, що неважливо, де в тексті комірки розташований вхідний запит.

Наприклад, стовпець «Доставка» може містити текст «Терміново, термін за 6 годин», а відповідна клітинка все одно матиме правильний формат.

Якщо необхідно застосувати правила форматування до рядків, де ключова комірка починається з потрібної фрази, то у формулі потрібно писати =1 замість >0. 

Усі ці правила можна записати у відповідному діалоговому вікні, як у прикладі вище. В результаті ви отримаєте наступне:

Як змінити колір клітинки на основі значення в іншій клітинці?

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

Як застосувати кілька умов для форматування

Якщо вам потрібно застосувати кілька правил умовного форматування до рядків, то замість того, щоб писати окремі правила, вам потрібно створити одне з формулами =АБО or =И. Перше означає «одне з цих правил вірне», а друге означає «обидва ці правила вірні».

У нашому випадку запишемо такі формули:

=ИЛИ($F2=»Оплата через 1 день», $F2=»Оплата через 3 дні»)

=ИЛИ($F2=»Оплата через 5 день», $F2=»Оплата через 7 дні»)

І формула =И можна використовувати, наприклад, для того, щоб перевірити, чи відповідає число в стовпці Qty. більше або дорівнює 5 і менше або дорівнює 10.

Користувач може використовувати більше однієї умови у формулах.

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