Умовне форматування в Excel. Приклади, колірна шкала, набори значків

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

Робота з великими таблицями Excel

Припустимо, що на одному з аркушів робочої книги Excel створена велика таблиця з даними. Можливо, це зведення з прибутку та інших показників ефективності підприємства в розбивці по роках або кварталах за кілька років, динаміка температури і вологості повітря для конкретного міста по днях тижня протягом місяця або будь-яка інша інформація, яка містить велику кількість різноманітних показників. Щоб дана таблиця мала якесь значення і допомагала судити про будь-яких процесах, її обов'язково треба проаналізувати. Але навіть для професіонала цей масив даних на перший погляд буде виглядати простим набором цифр, в якому необхідно шукати якісь певні моменти.

Для чого потрібно умовне форматування комірок в Excel?

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

Де ж шукати умовне форматування?

Щоб з успіхом користуватися цим інструментом, важливо зрозуміти і запам'ятати, де в Excel умовне форматування розташовується. У старій версії програми Excel 2003 необхідно зайти в меню «Формат» вибрати рядок «Умовне форматування». Для наступних версій Excel 20072010 і 2013 у верхній панелі у вкладці «Головна» слід відшукати розділ «Стилі», в якому і є меню «Умовне форматування». Клікнувши на нього, користувач побачить весь перелік можливих операцій.

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

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

Нововведення умовного форматування в більш пізніх версіях Excel

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

Умовне форматування за допомогою гістограми

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

Умовне форматування колірною шкалою

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

Умовне форматування значками

Наочність великий таблиці з величезною кількістю значень відмінно додасть форматування з використанням піктограм. З їх допомогою можна класифікувати всі значення таблиці на декілька груп, кожна з яких представлятиме певний діапазон. В залежності від обраного набору піктограм таких груп буде три, чотири або п'ять. Наприклад, використовуючи в роботі набір з трьох прапорців, можна отримати наступну картину: червоні прапорці будуть стояти поруч з малими значеннями (тобто менше 33%), жовті поряд з середніми значеннями (знаходяться в діапазоні між 33% і 67%), а зелені – з найбільшими значеннями (понад 67%). Варто відзначити, що умовне форматування в Excel 2010 містить набагато більше варіантів значків у порівнянні з попередньою версією програми. Якщо у вас є бажання використовувати значки з різних наборів для відображення необхідних значень, то варто вибрати пункт «Інші правила». У більш пізніх версіях програми тут можна не тільки вручну задати необхідні умови, але також і вибрати потрібний тип значка для кожного значення. Це дає можливість зробити представлені дані ще більш наочними і досить унікальними.

Переваги умовного форматування

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

Використання формул в умовне форматування

Бувають випадки, коли надані стилі не підходять для конкретної задачі або просто чимось не подобаються користувачеві. Для вирішення таких проблем можна самостійно задати умовне форматування. У програмі Excel 2010 використати формулу для визначення необхідних значень, які будуть відформатовані, досить просто, втім, як і в інших версіях програми. Для цього необхідно вибрати в меню умовного форматування пункт «Створити правило», потім рядок «Використовувати формулу» для визначення клітинок для форматування». Тут необхідно задати параметри, на основі яких буде проводитися в Excel умовне форматування. Формула заноситься в довгу рядок, і її істинні значення будуть визначати клітинки, які піддаються обробці. Щоб вибрати формат на свій розсуд, необхідно натиснути на відповідну кнопку. У вікні у користувача буде можливість вибрати тип, розмір і колір шрифту, формат відображення чисел, вид меж осередків і спосіб заливки. Даним способом можна задавати в Excel умовне форматування рядка, стовпця, окремої клітинки або всієї таблиці.

Приклад використання формули в умовне форматування

Припустимо, створена таблиця, в якій у двох колонках подано дані про виручку, отриману за реалізацію великої кількості різних товарів за березень і квітень. Необхідно виділити суми, які в березні виявилися більше, ніж у квітні. Для цього слід зайти в вищевказане меню та рядок формули ввести наступне: = B5>C5. Тут B – це стовпець березня, З – стовпець квітня. Записане умову можна буде протягнути на весь діапазон, в якому зібрані дані. Його також можна протягнути далі по стовпцях при додаванні нових місяців. Функція умовного форматування робить Excel ще більш зручною і потужною програмою для роботи з таблицями. І користувачі, які знають про плюси цього інструменту, безсумнівно, працюють у програмі з більшою легкістю і задоволенням.