Основні поняття умовного форматування і як його створити, excel для всіх
Умовне форматування - досить маловикористовувані інструмент Excel. Але це якраз той інструмент, за допомогою якого можна змінити форматування осередків (колір заливки, шрифт, кордону) в залежності від заданого умови, не вдаючись до допомоги Visual Basic for Applications.
Умовне форматування може значно спростити виділення певних осередків або діапазону комірок і візуалізацію даних за допомогою гістограми, колірних шкал і наборів значків. Воно змінює зовнішній вигляд діапазону комірок на основі зазначеного умови (або критерію). Якщо умова виконується, то діапазон комірок форматується відповідно до заданого для умови форматом; якщо умова не виконується, то діапазон вічок не форматується.
Наприклад, можна виділити осередок з поточною датою; осередок з числом, що входять в зазначений діапазон; осередок з певним текстом і т.п.
Умовне форматування можна застосувати до діапазону комірок, таблиці або звіту зведеної таблиці Excel.
Для чого може стати в нагоді Умовне форматування? Уявімо, що необхідно у великій таблиці даних зафарбувати червоним кольором все осередки, значення в яких перевищує 100. Що робиться зазвичай в таких випадках? Вірно. Встановлюється фільтр-Більше 100 і відфільтровані рядки зафарбовуються. Але. Якщо значення цих осередків формуються за допомогою формул або просто змінюються по ходу роботи з таблицею - досить накладно буде кожен раз відшукувати значення більше 100. Встановивши ж Умовне форматування виділяти нічого не треба буде - осередки будуть пофарбовані червоним автоматично, без Вашої участі.
- При створенні умовного форматування можна посилатися на інші осередки тільки в межах одного аркуша; не можна посилатися на осередки інших листів однієї і тієї ж книги або використовувати зовнішні посилання на іншу книгу;
- При зміні кольору заливки осередків, кольору шрифту, меж, форматування тексту за допомогою умовного форматування - зміни, зроблені за допомогою стандартного форматування не показуються в осередках, формати яких були змінені умовним форматуванням.
У статті розглянемо:
ДЕ РОЗТАШОВАНЕ УМОВНЕ ФОРМАТИРОВАНИЕ І ЯК СТВОРИТИ
Для створення умовного форматування необхідно:
Головний недолік встановлених правил - їх не можна застосовувати до осередків на підставі значень інших осередків. Вони застосовуються виключно для тих осередків, в яких самі значення. Наприклад, не можна зробити відображення гістограм в діапазоні А1: А10. але значення для гістограм брати з осередків В1: В10.
Повторювані значення:
Якщо в випадаючому списку вибрати повторювані. то виділені будуть всі значення, які зустрічаються в діапазоні більш ніж один раз.
Якщо вибрати унікальні. то виділені будуть всі значення, які зустрічаються тільки один раз.
Хмарно заливка (Solid fill) і Градиентная заливка (Gradient fill). Відрізняються між собою візуалізацією бару. Особисто мені візуально більше подобається градієнтна. Для чого їх можна застосовувати: наприклад, в стовпці послідовно записані дані з продажу за місяць і необхідно наочно відобразити їх різницю між собою.
Що важливо знати при застосуванні даних умов. Вони працюють тільки при застосуванні до діапазону комірок з числовими даними. 100% -му заповнення шкали відповідає максимальне значення серед виділених осередків, 1% -му заповнення - осередок з мінімальним значенням. Тобто осередок з максимальним значенням буде заповнена повністю, осередок з мінімальним - ледь буде видна смужка бару, а інші комірки будуть заповнені щодо процентного відношення даних в самій комірці до показників мінімального і максимального значення всіх осередків. Наприклад, якщо виділено 4 осередки з числами: 1, 25, 50 і 100, то осередок з 1 буде ледь заповнена, осередок з 25 - заповнена на чверть, осередок з 50 - на половину, а осередок з 100 - повністю.
Працює за тим же принципом, що і Гістограми (Data Bars). працює на основі числових значень виділених осередків, але зафарбовує не частина осередку методом шкали, а всю осередок, але з різною інтенсивністю або кольором. Можна створити умову, за якої осередок з максимальною продажем буде зафарбована найбільш насиченим кольором, а мінімальна - практично непомітно:
або додати до цього ще відмінність за кольорами:
У цьому випадку крім насиченості кольору значення будуть відрізнятися ще й самим кольором. Серед наборів шкал є розбивка на два і на три кольори. При цьому колір призначається за принципом поділу на кількість квітів: перші 33% одним кольором, від 34% до 66% іншим кольором, а решта - третім. Якщо кольору два - то ділиться по 50%.
Служить все для тих же цілей, що і шкали і гістограми, але має менш гнучку систему відображення відмінностей. Відображає відмінності між значеннями осередків по 2-х, 3-х, 4-х або 5-ти ступеневою системою. Це означає, що якщо обраний набір з 3-х значків, то різниця між мінімальним і максимальним значенням буде поділена на 3 та кожна третя частина буде зі своїм значком. Більш наочно можна побачити, застосувавши цю умову до числам від 1 до 9:
Для відображення різниці між значеннями так само дуже добре підходять значки у вигляді міні-гістограм:
Якщо необхідно виділяти форматуванням не тільки конкретний осередок, що задовольняє умові, а весь рядок таблиці на основі осередки одного стовпця, то в пункті 1 виділяється не стовпець, а всю таблицю, а посилання на стовпець з критерієм закріплюємо: ПОШУК ОСЕРЕДКІВ З умовного форматування Пошук всіх осередків з умовним форматуванням Пошук осередків з однаковим умовним форматуванням ВИДАЛЕННЯ УМОВНОГО ФОРМАТУВАННЯ Видалення умовного форматування з усього листа Вкладка Головна (Home) (Home) - група Стилі (Styles) - Умовне форматування (Conditional formatting) - Видалити правила (Clear Rules) - Видалити правила з усього листа (Clear Rules from Entire Sheet).
= $ A1 = МАКС ($ A $ 1: $ A $ 20)
при виділеному діапазоні A1: F20 (діапазон застосування умовного форматування), буде виділена рядок A7: F7. якщо в осередку A7 буде максимальне число.
Так само можна застосовувати не до конкретно однім стовпці, а до повністю діапазону. Але в цьому випадку треба знати принцип зміщення посилань у формулах, щоб умови застосовувалися саме до потрібних осередків. Наприклад, якщо задати умову для діапазону B1: D10 в вигляді формули: = B1
Якщо до однієї або декількох осередків на аркуші застосовано умовне форматування, можна швидко знайти їх для копіювання, зміни або видалення умовного формату.
