Умовне форматування: інструмент Microsoft Excel для візуалізації даних
Дивлячись на сухі цифри таблиць, важко з першого погляду вловити загальну картину, яку вони представляють. Але, в програмі Microsoft Excel є інструмент графічної візуалізації, за допомогою якого можна наочно представити дані, що містяться в таблицях. Це дозволяє більш легко і швидко засвоїти інформацію. Даний інструмент називається умовним форматуванням. Давайте розберемося, як використовувати умовне форматування в програмі Microsoft Excel.
зміст
Найпростіші варіанти умовного форматування
Для того, щоб провести форматування певній галузі осередків, потрібно виділити цю область (найчастіше стовпець), і перебуваючи у вкладці «Головна», клікнути на кнопку «Умовне форматування», яка розташована на стрічці в блоці інструментів «Стилі».
Після цього, відкривається меню умовного форматування. Тут представляється три основних види форматування:
- гістограми;
- Цифрові шкали;
- Значки.
Для того, щоб зробити умовне форматування у вигляді гістограми, виділяємо стовпець з даними, і натискаємо по відповідному пункту меню. Як бачимо, представляється на вибір кілька видів гістограм з градієнтної і суцільною заливкою. Виберете ту, яка, на ваш погляд, найбільше відповідає стилю і змісту таблиці.
Як бачимо, гістограми з'явилися в виділених комірках стовпчика. Чим більше числове значення в осередках, тим гістограма довше. Крім того, в версіях Excel 2010, 2013 і 2016 років, є можливість коректного відображення від'ємних значень в гістограмі. А ось, у версії 2007 років такої можливості немає.
При використанні замість гістограми колірної шкали, також існує можливість вибрати різні варіанти даного інструменту. При цьому, як правило, чим більше значення розташоване в осередку, тим більш насиченим колір шкали.
Найбільш цікавим і складним інструментом серед даного набору функцій форматування є значки. Існує чотири основні групи значків: напрямки, фігури, індикатори та оцінки. Кожен обраний користувачем варіант передбачає використання різних значків при оцінці вмісту комірки. Вся виділена область сканується Excel, і все значення осередків поділяються на частини, відповідно до величинам, зазначеним у них. До найбільших величинам застосовуються значки зеленого кольору, до величинам середнього діапазону - жовтого, і величини, що розташовуються в самій меншій третини - позначаються значками червоного кольору.
При виборі стрілок, як значків, крім колірного оформлення, використовується ще сигналізування у вигляді напрямків. Так, стрілка, повернена покажчиком вгору, застосовується до великих величинам, вліво - до середніх, вниз - до малих. При використанні фігур, кругом позначаються найбільші величини, трикутником - середні, ромбом - малі.
Правила виділення осередків
За замовчуванням, використовується правило, при якому всі осередки виділеного фрагмента позначаються певним кольором або значком, згідно розташованим в них величинам. Але, використовуючи меню, про який ми вже говорили вище, можна застосовувати і інші правила позначення.
Кількома по пункту меню «Правила виділення осередків». Як бачимо, існує сім основних правил:
- більше;
- менше;
- Так само;
- між;
- Дата;
- Повторювані значення.
Розглянемо застосування цих дій на прикладах. Виділимо діапазон комірок, і кликнемо за пунктом «Більше ...».
Відкривається вікно, в якому потрібно встановити, значення більше якого числа будуть виділятися. Робиться це в поле «Форматувати осередки, які більше». За замовчуванням, сюди автоматично вписується середнє значення діапазону, але можна встановити будь-яке інше, або ж вказати адресу комірки, в якій міститься це число. Останній варіант підійде для динамічних таблиць, дані в яких постійно змінюються, або для осередку, де застосовується формула. Ми для прикладу встановили значення в 20000.
В наступному полі, потрібно визначитися, як будуть виділятися осередки: світло-червона заливка і темно-червоний колір (за замовчуванням); жовта заливка і темно-жовтий текст; червоний текст, і т.д. Крім того, існує користувальницький формат.
При переході на цей пункт, відкривається вікно, в якому можна редагувати виділення, практично, як завгодно, застосовуючи різні варіанти шрифту, заливки, і кордони.
Після того, як ми визначилися, зі значеннями у вікні настройки правил виділення, тиснемо на кнопку «OK».
Як бачимо, осередки виділені, згідно зі встановленим правилом.
За таким же принципом виділяються значення при застосуванні правил «Менше», «Між» і «Так само». Тільки в першому випадку, виділяються осередки менше значення, встановленого вами; у другому випадку, встановлюється інтервал чисел, осередки з якими будуть виділятися; в третьому випадку задається конкретне число, а виділятися будуть осередки тільки містять його.
Правило виділення «Текст містить», головним чином, застосовується до осередків текстового формату. У вікні установки правила слід вказати слово, частина слова, або послідовний набір слів, при знаходженні яких, відповідні осередки будуть виділятися, встановленим вами способом.
Правило «Дата» застосовується до осередків, які містять значення в форматі дати. При цьому, в налаштуваннях можна встановити виділення осередків по тому, коли відбулося або відбудеться подія: сьогодні, вчора, завтра, за останні 7 днів, і т.д.
Застосувавши правило «Що повторюються значення» можна налаштувати виділення осередків, згідно відповідності розміщених в них даних одному з критеріїв: повторювані це дані або унікальні.
Правила відбору перших і останніх значень
Крім того, в меню умовного форматування є ще один цікавий пункт - «Правила відбору перших і останніх значень». Тут можна встановити виділення тільки найбільших або найменших значень в діапазоні осередків. При цьому, можна використовувати відбір, як по порядковим величинам, так і щодо процентних. Існують наступні критерії відбору, які вказані у відповідних пунктах меню:
- Перші 10 елементів;
- Перші 10%;
- Останні 10 елементів;
- Останні 10%;
- Вище середнього;
- Нижче середнього.
Але, після того, як ви кликнули по відповідному пункту, можна трохи змінити правила. Відкривається вікно, в якому проводиться вибір типу виділення, а також, при бажанні, можна встановити інший кордон відбору. Наприклад, ми, перейшовши за пунктом «Перші 10 елементів», у вікні,, в поле «Форматувати перші осередки» замінили число 10 на 7. Таким чином, після натискання на кнопку «OK», будуть виділятися не 10 найбільших значень, а тільки 7.
створення правил
Вище ми говорили про правила, які вже встановлені в програмі Excel, і користувач може просто вибрати будь-який з них. Але, крім того, при бажанні, користувач може створювати свої правила.
Для цього, потрібно натиснути в будь-якому підрозділі меню умовного форматування на пункт «Інші правила ...», розташований в самому низу списку ». Або ж клікнути по пункту «Створити правило ...», який розташований в нижній частині основного меню умовного форматування.
Відкривається вікно, де потрібно вибрати один з шести типів правил:
- Форматувати все осередки на підставі їх значень;
- Форматувати тільки осередки, які містять;
- Форматувати тільки перші і останні значення;
- Форматувати тільки значення, які перебувають вище або нижче середнього;
- Форматувати тільки унікальні або повторювані значення;
- Використовувати формулу для визначення форматується осередків.
Відповідно до обраного типу правил, в нижній частині вікна потрібно налаштувати зміна опису правил, встановивши величини, інтервали та інші значення, про які ми вже говорили нижче. Тільки в даному випадку, установка цих значень буде більш гнучка. Тут же задається, за допомогою зміни шрифту, меж і заливки, як саме буде виглядати виділення. Після того, як всі налаштування виконані, потрібно натиснути на кнопку «OK», для збереження проведених змін.
управління правилами
У програмі Excel можна застосовувати відразу кілька правил до одного і того ж діапазону комірок, але відображатися на екрані буде тільки останнє введене правило. Для того, щоб регламентувати виконання різних правил щодо певного діапазону комірок, потрібно виділити цей діапазон, і в основному меню умовного форматування перейти по пункту управління правилами.
Відкривається вікно, де представлені всі правила, які відносяться до виділеного діапазону комірок. Правила застосовуються зверху вниз, так як вони розміщені в списку. Таким чином, якщо правила суперечать один одному, то за фактом на екрані відображається виконання тільки самого останнього з них.
Щоб поміняти правила місцями, існують кнопки у вигляді стрілок спрямованих вгору і вниз. Для того, щоб правило відображалося на екрані, потрібно його виділити, і натиснути на кнопку у вигляді стрілки спрямованої вниз, поки правило не займе найостаннішу пишу в списку.
Є й інший варіант. Потрібно встановити галочку в колонці з найменуванням «Зупинити, якщо істина» навпроти потрібного нам правила. Таким чином, перебираючи правила зверху вниз, програма зупиниться саме на правилі, біля якого стоїть дана позначка, і не буде опускатися нижче, а значить, саме це правило буде фактично виконуватися.
У цьому ж вікні є кнопки створення і зміни виділеного правила. Після натискання на ці кнопки, запускаються вікна створення і зміни правил, про які ми вже вели мову вище.
Для того, щоб видалити правило, потрібно його виділити, і натиснути на кнопку «Видалити правило».
Крім того, можна видалити правила і через основне меню умовного форматування. Для цього, натискаємо по пункту «Видалити правила». Відкриється підменю, де можна вибрати один з варіантів видалення: або видалити правила тільки на виділеному діапазоні осередків, або видалити абсолютно все правила, які є на відкритому аркуші Excel.
Як бачимо, умовне форматування є дуже потужним інструментом для візуалізації даних в таблиці. З його допомогою, можна налаштувати таблицю таким чином, що загальна інформація на ній буде засвоюватися користувачем з першого погляду. Крім того, умовне форматування надає велику естетичну привабливість документу.