Умовне форматування під час порівняння списків у Excel

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

Виділіть значення, які існують у List1, але не в List2

На малюнку показано умовне форматування, яке порівнює клієнтів у 2013 і 2014 роках, виділяючи тих клієнтів у 2014 році, які є новими клієнтами (вони не були клієнтами у 2013 році).

Щоб створити це основне правило форматування, виконайте такі дії:

Виберіть клітинки даних у вашому цільовому діапазоні (у цьому прикладі — клітинки E4:E28), натисніть вкладку «Головна» на стрічці Excel, а потім виберіть «Умовне форматування»→ «Нове правило».

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

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

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

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

Зауважте, що ви використовуєте функцію COUNTIF, щоб оцінити, чи знайдено значення цільової клітинки (E4) у вашому діапазоні порівняння ($B$4:$B$21). Якщо значення не знайдено, функція COUNTIF поверне 0, таким чином ініціюючи умовне форматування. Як і у випадку зі стандартними формулами, вам потрібно переконатися, що ви використовуєте абсолютні посилання, щоб кожне значення у вашому діапазоні порівнювалося з відповідною коміркою для порівняння.

=COUNTIF($B$4:$B$21,E4)=0

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

Натисніть кнопку Формат.

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

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

Якщо вам потрібно відредагувати правило умовного форматування, просто помістіть курсор у будь-яку з клітинок даних у межах вашого форматованого діапазону, а потім перейдіть на вкладку «Головна» та виберіть «Умовне форматування»→ «Керувати правилами». Відкриється діалогове вікно Диспетчер правил умовного форматування. Натисніть правило, яке потрібно редагувати, а потім натисніть кнопку Редагувати правило.

Виділіть значення, які існують у List1 і List2

На малюнку показано умовне форматування, яке порівнює клієнтів 2013 і 2014 років, виділяє тих клієнтів у 2014 році, які є в обох списках.

Щоб створити це основне правило форматування, виконайте такі дії:

Виберіть клітинки даних у вашому цільовому діапазоні (комірки E4:E28 у цьому прикладі), перейдіть на вкладку «Головна» на стрічці Excel і виберіть «Умовне форматування»→ «Нове правило».

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

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

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

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

Зауважте, що ви використовуєте функцію COUNTIF, щоб оцінити, чи знайдено значення цільової клітинки (E4) у вашому діапазоні порівняння ($B$4:$B$21). Якщо значення знайдено, функція COUNTIF повертає число більше 0, таким чином запускаючи умовне форматування. Як і у випадку зі стандартними формулами, вам потрібно переконатися, що ви використовуєте абсолютні посилання, щоб кожне значення у вашому діапазоні порівнювалося з відповідною коміркою для порівняння.

=COUNTIF($B$4:$B$21,E4)>0

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

Натисніть кнопку Формат.

Це відкриє діалогове вікно Формат клітинок, де у вас є повний набір параметрів для форматування шрифту, межі та заливки для цільової комірки. Після того, як ви завершили вибір параметрів форматування, натисніть кнопку OK, щоб підтвердити зміни та повернутися до діалогового вікна «Нове правило форматування».

Повернувшись у діалогове вікно Нове правило форматування, натисніть кнопку OK, щоб підтвердити правило форматування.

Якщо вам потрібно відредагувати правило умовного форматування, просто помістіть курсор у будь-яку з клітинок даних у межах вашого форматованого діапазону, а потім перейдіть на вкладку «Головна» та виберіть «Умовне форматування»→ «Керувати правилами». Відкриється діалогове вікно Диспетчер правил умовного форматування. Натисніть правило, яке потрібно редагувати, а потім натисніть кнопку Редагувати правило.