Умовне форматування: інструмент Microsoft Excel для візуалізації даних

Зміст

• Найпростіші варіанти умовного форматування
• Правила виділення осередків
• Правила відбору перших і останніх значень
• Створення правил
• Управління правилами
• Питання та відповіді

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

Найпростіші варіанти умовного форматування

Для того, щоб зробити форматування певної області осередків, потрібно виділити цю область (найчастіше стовпець), і перебуваючи у вкладці «Головна», клікнути на кнопку «Умовне форматування», яка розташована на стрічці в блоці інструментів «стилі».

Після цього, відкривається меню умовного форматування. Тут представляється три основних види форматування:

• Гістограми;
• Цифрові шкали;
• Значки.

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

Як бачимо, гістограми з'явилися в виділених осередках стовпця. Чим більше числове значення в клітинках, тим гістограма довша. Крім того, у версіях Excel 2010, 2013 і 2016 років, є можливість коректного відображення негативних значень в гістограмі. А ось, у версії 2007 року такої можливості немає.

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

Найбільш цікавим і складним інструментом серед даного набору функцій форматування є значки. Існує чотири основні групи значків: напрямки, фігури, індикатори та оцінки. Кожен обраний користувачем варіант передбачає використання різних значків при оцінці вмісту комірки. Вся виділена область сканується Excel, і всі значення комірок поділяються на частини, згідно величинам, зазначеним в них. До найбільших величин застосовуються значки зеленого кольору, до величин середнього діапазону – жовтого, і величини, розташовані в самій меншій третині – позначаються значками червоного кольору.

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

Правила виділення осередків

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

Натискаємо по пункту меню «Правила виділення осередків». Як бачимо, існує сім основних правил:

• Більше;
• Менше;
• Дорівнює;
• Між;
• Дата;
• Повторювані значення.

Розглянемо застосування цих дій на прикладах.

1. Виділимо діапазон осередків, і кликнемо по пункту «більше...».

2. Відкривається вікно, в якому потрібно встановити, значення більше якого числа будуть виділятися. Робиться це в поле «форматувати осередки, які більше». За замовчуванням, сюди автоматично вписується середнє значення діапазону, але можна встановити будь-яке інше, або ж вказати адресу комірки, в якій міститься це число. Останній варіант підійде для динамічних таблиць, дані в яких постійно змінюються, або для комірки, де застосовується формула. Ми для прикладу встановили значення в 20000.

3. У наступному полі, потрібно визначитися, як будуть виділятися осередки: світло-Червона заливка і темно-червоний колір (за замовчуванням); жовта заливка і темно-жовтий текст; червоний текст, і т.д. крім того, існує призначений для користувача формат.

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

5. Після того, як ми визначилися, зі значеннями у вікні налаштування правил виділення, тиснемо на кнопку «OK».

Як бачимо, осередки виділені, згідно з встановленим правилом.

За таким же принципом виділяються значення при застосуванні правил «Менше», «між» і «дорівнює». Тільки в першому випадку, виділяються осередки менше значення, встановленого вами; у другому випадку, встановлюється інтервал чисел, осередки з якими будуть виділятися; в третьому випадку задається конкретне число, а виділятися будуть осередки тільки містять його.

Правило виділення «Текст Містить», головним чином, застосовується до осередків текстового формату. У вікні установки правила слід вказати слово, частина слова, або послідовний набір слів, при знаходженні яких, відповідні осередки будуть виділятися, встановленим вами способом.

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

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

Правила відбору перших і останніх значень

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

• Перші 10 елементів;
• Перші 10%;
• Останні 10 елементів;
• Останні 10%;
• Вище середнього;
• Нижче середнього.

Але, після того, як ви кликнули по відповідному пункту, можна трохи змінити правила. Відкривається вікно, в якому проводиться вибір типу виділення, а також, при бажанні, можна встановити іншу межу відбору. Наприклад, ми, перейшовши по пункту «перші 10 елементів», у вікні, в поле «форматувати перші осередки» замінили число 10 на 7. Таким чином, після натискання на кнопку «OK», будуть виділятися не 10 найбільших значень, а тільки 7.

Створення правил

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

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

Відкривається вікно, де потрібно вибрати один з шести типів правил:

1. Форматувати всі клітинки на основі їх значень;
2. Форматувати лише клітинки, які містять;
3. Форматувати лише перші та останні значення;
4. Форматувати тільки значення, які знаходяться вище або нижче середнього;
5. Форматувати лише унікальні або повторювані значення;
6. Використовуйте формулу для визначення форматованих комірок.

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

Управління правилами

У програмі Excel можна застосовувати відразу кілька правил до одного і того ж діапазону осередків, але відображатися на екрані буде тільки останнім введене правило. Для того, щоб регламентувати виконання різних правил щодо певного діапазону осередків, потрібно виділити цей діапазон, і в основному меню умовного форматування перейти по пункту управління правилами.

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

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

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

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

Для того, щоб видалити правило, потрібно його виділити, і натиснути на кнопку «Видалити правило».

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

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