Умовне форматування даних

 Умовний формат

Умовний формат — це формат (наприклад, візерунок комірки або колір шрифту), який Microsoft Excel автоматично застосовує до комірки, якщо виконується зазначена умова.

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

Порядок дій:

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

2. У меню Формат виберіть команду Умовне форматування.

3. Виконайте відповідну дію.

Додавання умовного формату

Для використання значень виділених комірок як умов форматування необхідно вибрати параметр значення, вибрати операцію порівняння, а потім ввести стале значення або формулу. Перед формулою слід поставити знак рівності (=).

Для використання формули як умови форматування (для оцінки даних або умов, відмінних від значень виділених комірок) необхідно вибрати параметр Формула, а потім ввести формулу, яка набуває логічного значення TRUE або FALSE.

4. Порядок подальших дій:

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

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

Щоб додати іншу умову, натисніть кнопку     Додати, а потім повторіть кроки.

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

Пошук комірок, які мають умовне форматування

Порядок дій:

1. Щоб знайти всі комірки з умовним форматуванням, клацніть будь-яку комірку.

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

2. У меню Правка виберіть команду Перейти.

3. Натисніть кнопку Виділити.

4. Виберіть умовні формати.

5. Виконайте одну з таких дій.

Щоб знайти комірки з умовним форматуванням, виберіть параметр "усіх" у групі Перевірка даних.

Щоб знайти комірки з однаковим умовним форматом, виберіть  параметр "цих самих" у групі Перевірка даних.

Вибір кольору для виділення дубльованих комірок

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

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

Копіювання умовного формату до решти комірок

Нам треба скопіювати умовне форматування комірки A2 вниз до решти комірок діапазону. Не залишаючи комірки A2, виберіть команду  Копіювати в  меню  Правка. Натисніть клавіші CTRL+пропуск, щоб виділити весь стовпець. Потім виберіть команду Спеціальна вставка в меню Правка. У вікні Спеціальна вставка виберіть перемикач Формати й натисніть кнопку OK.

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

Не завадить перейти до комірки A3 та поглянути на умовну формулу (після того як її було скопійовано з A2). Виділіть комірку A3 і виберіть команду Умовне форматування в меню Формат. Видно, що формула в полі Формула змінилася й тепер обчислює, скільки разів дані з комірки A3 зустрічаються у стовпці A.

Умовне форматування може обробляти до 65536 комірок, порівнюючи кожну окрему комірку з рештою 65535 комірками. З  технічної точки зору, формула на першому кроці мала б виглядати так: =COUNTIF($A$2:$A$1751;A2)>1.

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

2.  Сортування даних

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

Спочатку введіть у комірці B1 заголовок Дублікат. Потім введіть у комірці B2 таку формулу:=COUNTIF(A:A;A2)>1.

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

Тепер можна відсортувати стовпці за стовпцем B (за спаданням), а потім за стовпцем A (за зростанням), і дубльовані номери рахунків опиняться на початку діапазону.