Умовне форматування за кількох умов
Розглянемо кілька варіантів вирішення даного завдання.
Ми вже раніше вивчали застосування даного правила, але тим не менше йдемо по порядку. Нагадую - вихідний діапазон у нас виділено. Переходимо в меню «Умовне форматування», вибираємо «Правила виділення осередків» і задаємо умову - «більше»
У випадаючому вікні в лівому полі задаємо параметр, «10», а в правому, вибираємо спосіб відображення даних в яких значення, більше встановленого нами числа (жовтий колір)
Натискаємо кнопку «ok» і повторюємо цю процедуру для іншого порога «20», тільки колір вибираємо (зелений). І те ж саме проробляємо для третього правила числа «40» і колір природно вибираємо (червоний) У результаті отримуємо таблицю з виділеними даними по заданих нами умов форматування
Спробуйте застосувати цю функцію в копіях своїх напрацювань, до речі, параметри відображення (способи заливки, вибір кольору) Ви можете визначати самі. Ось один з варіантів:
І їх дуже багато. Як це зробити? я обов'язково розповім в одному з наступних уроків.
Маючи виділений діапазон, заходимо в меню «Умовне форматування» і вибираємо «створити правило»
У випадаючому вікні, в розділі «вибір типу правил» вибираємо «Використовувати формулу для визначення форматується осередків».
У розділі «змінити опис правил» в рядку формул ставимо відносну або змішану посилання, як описано в попередньому уроці (див. Тут) або прописуємо вручну, як показано в прикладі:
Потім після відносної посилання в формулу ставимо знак «>» і цифру «10», трохи нижче натискаємо кнопку «формат», для вибору палітри відображення (в нашому випадку «жовтий колір». Натискаємо «ok»
Далі проробляємо ті ж операції для значення «20» і вибираємо природно «зелений» колір І далі задаємо правило для останнього значення, числа «40» і вибираємо червоний колір. От і все
Тепер, для того щоб закріпити матеріал, спробуйте застосувати вивчену функцію у своєму доробку там, де на вашу ця «примочка» могла б бути корисною.
створити правило
Трохи відвернемося від вивчення варіантів застосування функцій умовного форматування за кількох умов і заглянемо у внутрішні настройки даного сервісу. Ви вже помітили, при виборі пункту «створити правило» у випадаючому вікні (у верхній частині) «виберете тип правила», ми бачимо цілий список пропонованих правил.
Умовне форматування дати
Ну а я тим часом, опишу ще одну чудову функцію. Найчастіше наші бази з даними містять колонку «Дата». І як раз за датою нам необхідно наприклад знайти якусь інформацію. Для прикладу беремо стовпець з датами. Виділяємо обраний діапазон. в головному меню відкриваємо функцію «умовне форматування» / «правило виділення осередків» / «дата»
У лівій частині випав вікна вибираємо із запропонованого списку умова, за яким необхідно виділити дати.
Наприклад (на поточного тижня). Тиснемо «ok» і отримуємо ось такий результат
До речі ми можемо поставити друге умови для даної функції і виділити його іншим кольором. Наприклад, (на попереднього тижня) і колір зелений і далі ставимо третє правило і вибираємо колір, відмінний від обраних.
Умовне форматування рядка за значенням осередки
Умовою для даної функції УФ є виділення рядка за певним значенням в осередку. Для прикладу візьмемо яку ні будь таблицю з даними (в нашому випадку я буду використовувати відомості з ремонтів обладнання)
Визначимося з умовами виділення інформації, наприклад, нам необхідно що б, відомості де ще ремонт не закінчився (Р), відображалися червоним кольором. А там, де роботи завершені (З) - зеленим І так поставимо умову для завершених ремонтів. Виділяємо діапазон з усіма даними ремонтної таблиці. Заходимо в меню «умовного форматування», вибираємо «створити правило»
У меню вибираємо тип правила (натискаємо 1 раз ЛКМ за назвою правила) - «використовувати формулу для визначення форматується осередків» і в рядку для формул забиваємо логічну функцію, як показано на прикладі.
А наша таблиця буде мати ось такий вигляд:
Умовне форматування з використанням формул
Якщо до цього нам пропонувався вибір якихось стандартних правил умовного форматування, то можливості цієї функції на мій погляд, просто не обмежені. Адже за допомогою формул і макросів в Excel вирішуються будь-які проблеми, і ми обов'язково на подальших заняттях будемо вивчати застосування цих ласощів. Використання формул в умовному форматуванні дає можливість встановлювати правила для осередку посилаючись на іншу клітинку, на інший аркуш або навіть на іншу книгу (за умови її відкриття). Складати умови (задавати правила) з використанням логічних, математичних, інженерних та інших формул. Одним словом, тут стільки хорошого, що мені навіть важко вирішити, з якого прикладу почати навчання. Почнемо як завжди з простого:
простий приклад
Використовуємо наявний у нас ряд чисел. Наша умова - виділити з усього списку «непарні» числа. Для цього виділяємо обраний діапазон, відкриваємо меню «Умовне форматування» - створити правило і в верхній частині випав вікна вибираємо «використовувати формулу для визначення форматується осередків», а в нижній частині вставляємо формулу як показано в прикладі і задаємо формат виділення.
Натискаємо «ok» і результат повинен бути таким:
Ну і в кінці нашого сьогоднішнього заняття розберемо приклад складніша, створимо інформаційне вікно навпроти наявної бази з даними.
інформаційне вікно
Використовувати будемо все той же діапазон чисел. Наприклад, колонка з підсумковими цифрами (в нашому випадку діапазон A1: A15) має постійно мінливі дані в яких ми повинні контролювати певні параметри. Задамо відразу граничні значення: до 30 - це «норма», понад 30 необхідно взяти під «увагу», ну а понад 40 - це «перебір» або «понад норму», а тепер в осередку B1 прописуємо ось таку формулу: = ЕСЛИ ( A1> 40; »сверхнорми»; ЯКЩО (A1> 30; »увагу»; ЯКЩО (A1<30;»норма»;»»)))
потім простягаємо формулу до B15 і повинні побачити наступне:
Потім для кожного з граничних значень ми повинні створити правило умовного форматування, порядок такий: Виділяємо діапазоні B1: B15, в меню «умовного форматування» вибираємо «створити правило».
Потім у випадаючому вікні вибираємо правило, створюємо умова і задаємо формат для значення «норма», як показано на скріншоті.
Потім повторюємо цю операцію для значення «увагу» і «понад норму» і в диспетчері завдань ми повинні отримати ось таку картину:
От і все. Шановні колеги, не бійтеся експериментувати і найголовніше намагайтеся відразу застосовувати отримані знання в якихось своїх напрацюваннях. Причому, вибирати умови і створювати для них правила ви так само можете на свій розсуд. Мої приклади це, тільки вказівка шляху до необмежених можливостей Excel. Ну а на наступному занятті ми з Вами будемо використовувати формули і звичайно-ж умовне форматування залежне від баз даних зберігаються на інших (прихованих) аркушах. Така функція дуже зручна, коли доступ сторонніх до даних небажаний або просто необхідно створити залежність інформації від бази даних зберігаються на іншому аркуші. Одним словом, можливостей використання формул, макросів, посилань і застосування тут же функцій умовного форматування в Excel, дає нам просто необмежені можливості. Потрібно просто не бояться експериментувати і у Вас Все вийде.