ВИДІЛЕННЯ РЯДКІВ

ВИДІЛЕННЯ осередків з числами

ПРІОРИТЕТ ПРАВИЛ

Для перевірки застосованих до діапазону правил використовуйте Диспетчер правил умовного форматування(Головна/ Стилі/ Умовне форматування/ Управління правилами).

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

Наприклад, в осередку знаходиться число 9 і до неї застосовано два правила Значення осередку >6(заданий формат: червоний фон) та Значення осередку >7(заданий формат: зелене тло), див. малюнок вище. Т.к. правило Значення осередку >6(заданий формат: червоне тло) розташовується вище, воно має вищий пріоритет, і тому осередок зі значенням 9 матиме червоне тло. На Прапорець Зупинити, якщо істинаможна не звертати увагу, він встановлюється для забезпечення зворотної сумісності з попередніми версіями EXCEL, які не підтримують одночасне застосування кількох правил умовного форматування. Хоча його можна використовувати для скасування одного або декількох правил при одночасному використанні кількох правил, встановлених для діапазону (коли між правилами немає конфлікту). Докладніше можна.

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

УМОВНЕ ФОРМАТУВАННЯ та ФОРМАТ осередків

Умовне форматування не змінює застосований до цієї комірки Формат (вкладка Головна група Шрифт, або натиснути CTRL+SHIFT+F). Наприклад, якщо у Форматі осередку встановлено червоне заливання осередку, і спрацювало правило Умовного форматування, згідно з яким заливка цього осередку має бути жовтим, то заливка Умовного форматування "переможе" - осередок буде виділено жовтим. Хоча заливка Умовного форматування наноситься поверх заливки Формату комірки, вона не змінює (не скасовує її), а її просто не видно.

Налагодження правил умовного формування

Щоб перевірити чи правильно виконуються правила Умовного форматування, скопіюйте формулу з правила в будь-яку порожню комірку (наприклад, комірку праворуч від комірки з Умовним форматуванням). Якщо формула поверне ІСТИНА, то правило спрацювало, якщо брехня, то умова не виконана і форматування осередку не повинно бути змінено.

Повернемося до задачі 3 (див. розділ про відносні посилання). У рядку 4 напишемо формулу із правила умовного форматування =A1

У тих стовпцях, де результат формули дорівнює ІСТИНА, умовне форматування буде застосоване, а де брехня - ні.

ВИКОРИСТАННЯ У ПРАВИЛАХ ПОСИЛАНЬ НА ІНШІ ЛИСТИ

До MS Excel 2010 для правил Умовного форматуванняне можна було безпосередньо використовувати посилання на інші аркуші чи книги. Обійти це обмеження можна було за допомогою використання. Якщо в Умовне форматуванняпотрібно зробити, наприклад, посилання на комірку А2 іншого листа, то потрібно спочатку визначити ім'ядля цього осередку, а потім послатися на це ім'яу правилі Умовного форматування. Як це реалізовано Див. файл прикладу на аркуші Посилання з іншого листа.

ПОШУК осередків з умовним формуванням

• на вкладці Головнав групі Редагуванняклацніть стрілку поруч із командою Знайти та виділити,
• виберіть у списку пункт Умовне форматування.

Будуть виділені всі осередки, для яких задані правила Умовного форматування.

ІНШІ ПОПЕРЕДЖЕНІ ПРАВИЛА

У меню Головна/ Стилі/ Умовне форматування/ Правила виділення осередківрозробниками EXCEL створено різноманітні правила форматування.

Щоб знову не винаходити велосипед, подивимося на деякі з них уважніше.

• Текст містить…Наведемо приклад. Нехай у осередку є слово Дриль Текст містить…Якщо ​​як критерій запишемо ре(Виділити слова, в яких міститься склад ре), то слово Дрильбуде виділено.

Тепер подивимося на щойно створене правило через меню Головна/ Стилі/ Умовне форматування/ Управління правилами...

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

Нехай знову в осередку є слово Дриль. Виділимо осередок і застосуємо правило Текст містить… Якщо як критерій запишемо р?,то слово Дрильбуде виділено. Критерій означає: виділити слова, в яких містяться склади ре, ра, ре і т.д.Треба розуміти, що також буде виділено слова із фразами р2, рм, рQ, т.к. знак? означає будь-який символ. Якщо як критерій запишемо ?????? (Виділити слова, в яких не менше 6 букв), то, відповідно, слово Дрильне буде виділено. Можна, звичайно, подібного результату досягти за допомогою формул з функціями ПСТР() , ЛЕВСИМВ() , ДЛСТР() , але цей підхід, погодьтеся, швидше.

• Значення, що повторюються…Це правило дозволяє швидко налаштувати Умовне форматуваннядля відображення та повторюваних значень. Під унікальним значенням Умовне форматуванняпередбачає неповторне значення, тобто. значення, яке зустрічається єдиний раз у діапазоні, до якого застосовано правило. Щоб виділити унікальні значення (тобто всі значення без їх повторів), див.
• Дата…На малюнку нижче наведено критерії відбору цього правила. Для того, щоб досягти такого ж результату за допомогою формул, потрібно набагато більше часу.

• Значення осередку.Це правило доступне через меню. У вікні вибрати пункт форматувати комірки, що містять.Вибір опцій дозволить виконати більшість завдань, пов'язаних із виділенням числових значень.

Раджу також звернути увагу на наступні правила меню Головна/ Стилі/ Умовне форматування/ Правила відбору перших та останніх значень.

• Останні 10 елементів.

Задача4. Нехай є 21 значення, для зручності. Застосуємо правило Останні 10 елементівта встановимо, щоб було виділено 3 значення (елемента). файл прикладу , лист Задача4.

Слова "Останні 3 значення" означають 3 найменші значення. Якщо у списку є повтори, будуть виділені всі відповідні повтори. Наприклад, у разі 3-м найменшим є третє зверху значення 10. Т.к. у списку є ще 10 повтори (їх всього 6), то будуть виділені і вони.

Відповідно, правила, застосовані до нашого списку: "Остання 1 значення", "Останні 2 значення", ... "Останні 6 значень" призводитимуть до однакового результату - виділення 6 значень рівних 10.

Застосування правила "Останні сім значень" призведе до виділення додатково всіх значень рівних 11, .т.к. 7 мінімальним значенням є перше зверху значення 11.

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

• Останні 10%

Розглянемо інше споріднене правило Останні 10%.

Зверніть увагу, що на зображенні вище не встановлена ​​галочка "% від виділеного діапазону". Ця галочка встановлюється або вручну або при застосуванні правила Останні 10%.

У цьому правилі визначається відсоток найменших значень від загальної кількості значень у списку. Наприклад, поставивши 20% останніх, буде виділено 20% найменших значень.

Спробуємо задати 20% останніх у нашому списку з 21 значення: буде виділено шість значень 10 (див. файл прикладу, лист Задача4). 10 - мінімальне значення у списку, тому у будь-якому випадку будуть виділені усі його повтори.

Задаючи відсотки від 1 до 33%, отримаємо, що виділення не зміниться. Чому? Задавши, наприклад, 33%, отримаємо, що необхідно виділити 693 значення. Т.к. можна виділити лише цілу кількість значень, умовне форматування округляє до цілого, відкидаючи дробову частину. І це при 34% вже потрібно виділити 7,14 значень, тобто. 7, а з урахуванням повторів наступного за 10 значення 11, буде виділено 6+3=9 значень.

ПРАВИЛА З ВИКОРИСТАННЯМ ФОРМУЛ

Створення правил форматування на основі формул обмежено лише фантазією користувача. Тут розглянемо лише один приклад, інші приклади використання Умовного форматуванняможна знайти у цих статтях: ; ; ; .

Припустимо, що необхідно виділяти осередки, що містять помилкові значення:

• Виділіть комірки, до яких потрібно застосувати Умовне форматування(Нехай це осередок А1 ).
• Викличте інструмент Умовне форматування (Головна/ Стилі/ Умовне форматування/ Створити правило)
• Виберіть Використовувати формулу для визначення осередків, що форматуються.

• У полі " Форматувати значення, для яких наступна формула є істинною» введіть =ЕОШ(A1) – якщо бажаємо, щоб виділялися осередки, які мають помилкові значення, тобто. будуть виділені #ЗНАЧ!, #ПОСИЛКА!, #СДІЛ/0!, #КІСЛО!, #ІМ'Я?або #ПУСТО!(крім #Н/Д)
• Виберіть потрібний формат, наприклад, червоний колір заливки.

Того ж результату можна досягти інакше:

• Викличте інструмент Умовне форматування (Головна/ Стилі/ Умовне форматування/ Створити правило)
• Виділіть пункт Форматувати лише комірки, які містять;
• В розділі Форматувати лише комірки, для яких виконується така умова:у самому лівому списку вибрати Помилки.

Коментарі

Тетяна (не перевірено)

Тетяна (не перевірено)

Creator

Знову я. (не перевірено)

Creator

Тетяна (не перевірено)

Creator

Олександр 555 (не перевірено)

Creator

На цьому уроці ми розглянемо основи застосування умовного форматування в Excel.

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

Основи умовного форматування в Excel

Використовуючи умовне форматування, ми можемо:

• зафарбовувати значення кольором
• міняти шрифт
• ставити формат кордонів

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

Де знаходиться умовне форматування в Екселі?

Кнопка "Умовне форматування" знаходиться на панелі інструментів, на вкладці "Головна":

Як зробити умовне форматування в Excel?

При застосуванні умовного форматування системі необхідно задати два налаштування:

• Яким осередкам ви хочете задати формат;
• За якими умовами буде надано формат.

Нижче ми розглянемо як застосувати умовне форматування. Уявімо, що у нас є таблиця з динамікою курсу долара у рублях за рік. Наше завдання виділити червоним кольором дані, в яких курс знижувався попереднього місяця. Отже, виконаємо такі кроки:

• У таблиці з даними виділимо діапазон, для якого ми хочемо застосувати виділення кольором:

• Перейдемо на вкладку “Головна” на панелі інструментів та натисніть пункт “Умовне форматування”. У списку ви побачите кілька типів формату на вибір:
  • Правила виділення
  • Правила відбору перших та останніх значень
  • Гістограми
  • Колірні шкали
  • Набір піктограм
• У прикладі ми хочемо виділити кольором дані з негативним значенням. І тому виберемо тип “Правила виділення осередків” => “Менше”:

Доступні такі умови:

1. Значення більші або рівні будь-якому значенню;
2. Виділяти текст, який містить певні літери чи слова;
3. Виділяти кольором дублікати;
4. Виділяти певні дати.

• У спливаючому вікні в полі “Форматувати комірки МЕНШЕ” вкажемо значення “0”, тому що нам потрібно виділити кольором негативні значення. У списку праворуч виберемо формат відповідних умовам:

• Для присвоєння формату ви можете використовувати налаштувані колірні палітри, а також створити свою палітру. Для цього натисніть на пункт:

• У спливаючому вікні формату вкажіть:
  • колір заливки
  • колір шрифту
  • шрифт
  • межі осередків

• Після завершення налаштувань натисніть кнопку “ОК”.

Нижче наведено приклад таблиці із застосуванням умовного форматування за заданими нами параметрами. Дані з негативними значеннями виділені червоним кольором:

Як створити правило

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

• Виділимо діапазон даних. Клацніть на пункт "Умовне форматування" в панелі інструментів. У списку виберемо пункт “Нове правило”:

• У спливаючому вікні потрібно вибрати тип застосовуваного правила. У нашому прикладі нам підійде тип "Форматувати лише комірки, які містять". Після цього поставимо умову виділяти дані, значення яких більше "57", але менше "59":

• Клікнемо на кнопку “Формат” і поставимо формат, як ми це робили у прикладі вище. Натисніть кнопку “ОК”:

Умовне форматування за значенням іншого осередку

На прикладах вище ми задавали формат осередків, на основі їхніх власних значень. В Excel можна задавати формат, на основі значень інших осередків. Наприклад, у таблиці з даними курсу долара ми можемо виділити кольором осередку за правилом. Якщо курс долара нижче, ніж у попередньому місяці, то значення курсу в поточному місяці буде виділено кольором.

Для створення умови за значенням іншого осередку виконаємо такі кроки:

• Виділимо перший осередок призначення правила. Клацніть пункт “Умовне форматування” на панелі інструментів. Виберемо умову "Менше".
• У спливаючому вікні вказуємо посилання на комірку, з якою порівнюватиметься даний осередок. Вибираємо формат. Натискаємо кнопку "ОК".

• Повторно виділимо лівою клавішею миші осередок, якому ми надали формат. Клацніть на пункт “Умовне форматування”. Виберемо у випадаючому меню "Управління правилами" => клікнемо на кнопку "Змінити правило":

• У полі зліва спливаючого вікна "очистимо" посилання від знака "$". Натискаємо кнопку "ОК", а потім кнопку "Застосувати".

• Тепер нам потрібно присвоїти налаштований формат на інші осередки таблиці. Для цього виділимо комірку з наданим форматом, потім у лівому верхньому куткупанелі інструментів натиснемо на "валик" і надамо формат іншим осередкам:

На скріншоті нижче кольором виділені дані, в яких курс валюти став нижчим до попереднього періоду:

Як застосувати кілька правил умовного форматування до одного осередку

Можна застосовувати кілька правил до одного осередку.

Наприклад, у таблиці із прогнозом погоди ми хочемо зафарбувати різними кольорами показники температури. Умови виділення кольором: якщо температура вище 10 градусів – зеленим, якщо вище 20 градусів – жовтий, якщо вище 30 градусів – червоним.

Для застосування кількох умов до одного осередку виконаємо такі дії:

• Виділимо діапазон з даними, до яких ми хочемо застосувати умовне форматування => клікнемо за пунктом "Умовне форматування" на панелі інструментів => виберемо умову виділення "Більше ..." і вкажемо першу умову (якщо більше 10, то зелена заливка). Такі ж дії повторимо для кожної з умов (більше 20 і більше 30). Незважаючи на те, що ми застосували три правила, дані в таблиці зафарбовані зеленим кольором:

Умовне форматування – це інструмент Excel, який служить для присвоєння особливого формату осередкам або цілим діапазонам осередків на основі критеріїв умов, визначених користувачем. Ви ознайомитеся з прикладами використання умов, заснованих на складних формулах. А також навчитеся керувати такими функціями як:

• поля даних;
• палітра кольорів;
• налаштування шрифтів.

Навчіться працювати зі значеннями, які можна вставляти в комірки залежно від їхнього вмісту.

Як зробити умовне форматування в Excel

Спочатку розглянемо: як підбирати відповідні критерії форматування і як їх змінювати. Принцип його дії найпростіше зрозуміти на готовому прикладі:

Допустимо стовпець, містить діапазон осередків з числовими значеннями. Якщо визначити їх відповідною умовою форматування, всі значення з числом більш ніж 100 будуть відображатися червоним кольором. Щоб реалізувати це завдання, даний інструмент Excel проаналізує відповідно до умов критеріїв значення кожного осередку заданого діапазону. Результати аналізу дають позитивний результат, наприклад (A2>100=ІСТИНА), тоді буде надано попередньо заданий новий формат (червоний колір). У протилежному результаті (A2>100=БРЕХНЯ), формат комірок не змінюється.

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

Як створити правило умовного форматування в Excel

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

Допустимо в осередку A1 знаходитися числове значення 50:

Визначимо такі умови для формату відображення значень A1:

1. Якщо число більше 15, шрифт буде відображатися зеленим кольором.
2. Якщо число більше 30, шрифт буде відображатися жовтим кольором.
3. Якщо число більше 40, шрифт буде відображатися червоним.

Визначено ви помітили, що значення 50 в осередку A1 відповідає всім умовам (A1>15, A1>30 і A1>40 = ІСТИНА). Яким кольором шрифту Excel відобразить числове значення 50?

Відповідь наступна: формат буде присвоєно ту, яка відповідає останнім умовам. А отже це червоний колір. Важливо запам'ятати про цей принцип, коли потрібно конструювати складніші умови.

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

Створити друге правило

Другий приклад. Допустимо, нам потрібно відформатувати витрати в колонці C наступним чином:

Усі суми в межах 300$-600$ повинні зафарбувати свої осередки жовтим фоном, а суми менше ніж 500$ повинні мати і червоний колір шрифту.

Спробуємо сконструювати дані умови:

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

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

Як зробити умовне форматування в Excel

Інструмент «Умовне форматування» знаходиться на головній сторінціу розділі "Стилі".

При натисканні стрілки праворуч відкривається меню для умов форматування.

Порівняємо числові значення в діапазоні Excel із числовою константою. Найчастіше використовуються правила «більше/менше/рівно/між». Тому вони винесені в меню "Правила виділення осередків".

Введемо в діапазон А1: А11 ряд чисел:

Виділимо діапазон значень. Відкриваємо меню "Умовного форматування". Вибираємо «Правила виділення осередків». Задамо умову, наприклад, «більше».

Введемо в ліве поле число 15. У праве - спосіб виділення значень, що відповідають заданій умові: "більше 15". Відразу видно результат:

Виходимо з меню, натиснувши кнопку ОК.

Умовне форматування за значенням іншого осередку

Порівняємо значення діапазону А1:А11 з числом у комірці В2. Введемо до неї цифру 20.

Виділяємо вихідний діапазон та відкриваємо вікно інструменту «Умовне форматування» (нижче скорочено згадується «УФ»). Для даного прикладузастосовуємо умову «менше» («Правила виділення осередків» - «Менше»).

Результат форматування відразу видно на аркуші Excel.

Значення діапазону А1:А11, які менші за значення комірки В2, залиті вибраним фоном.

Задамо умову форматування: порівняти значення осередків у різних діапазонах та показати однакові. Порівнюватимемо стовпець А1: А11 зі стовпцем В1: В11.

Виділимо вихідний діапазон (А1: А11). Натисніть «УФ» – «Правила виділення осередків» – «Рівне». У лівому полі – посилання на комірку В1. Посилання має бути ЗМІШАНЕ або ВІДНОСНЕ!, а не абсолютне.

Кожне значення у стовпці А програма порівняла з відповідним значенням у стовпці В. Поодинокі значення виділені кольором.

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

У нашому прикладі в момент виклику інструменту був активний осередок А1. Посилання $B1. Отже, Excel порівнює значення осередку А1 зі значенням В1. Якби ми виділяли стовпець не зверху вниз, а знизу вгору, то активним був би осередок А11. І програма порівнювала б В1 із А11.

Порівняйте:

Щоб інструмент «Умовне форматування» правильно виконав завдання, слідкуйте за цим моментом.

Перевірити правильність заданої умови можна так:

1. Виділіть першу комірку діапазону з умовним форматуванням.
2. Відкрийте меню інструмента, натисніть "Керування правилами".

У вікні видно, яке правило і якого діапазону застосовується.

Умовне форматування – кілька умов

Вихідний діапазон - А1: А11. Необхідно виділити червоним числа, які більші за 6. Зеленим – більше 10. Жовтим – більше 20.

• 1 спосіб. Виділяємо діапазон А1: А11. Застосовуємо до нього "Умовне форматування". "Правила виділення осередків" - "Більше". У ліве поле вводимо число 6. У правому - "червона заливка". ОК. Знову виділяємо діапазон А1: А11. Задаємо умову форматування "більше 10", спосіб - "заливання зеленим". За таким же принципом "заливаємо" жовтим числа більше 20.
• 2 спосіб. У меню інструмента «Умовне форматування вибираємо «Створити правило».

Заповнюємо параметри форматування за першою умовою:

Натискаємо ОК. Аналогічно задаємо другу та третю умову форматування.

Зверніть увагу: значення деяких осередків відповідають одночасно двом та більше умовам. Пріоритет обробки залежить від порядку перерахування правил у «Диспетчері»-«Управління правилами».

Тобто до 24, яке одночасно більше 6, 10 і 20, застосовується умова = А1>20 (перше в списку).

Умовне форматування дати в Excel

Виділяємо діапазон із датами.

Застосуємо до нього "УФ" - "Дата".

У вікні з'являється список доступних умов(Правил):

Вибираємо потрібне (наприклад, за останні 7 днів) та тиснемо ОК.

Червоним кольором виділено осередки з датами останнього тижня (дата написання статті – 02.02.2016).

Умовне форматування в Excel з використанням формул

Якщо стандартних правил недостатньо, користувач може застосувати формулу. Практично будь-яку: можливості даного інструментубезмежні. Розглянемо найпростіший варіант.

Є стовпець із числами. Необхідно виділити кольором осередки з парними. Використовуємо формулу: = ОСТАТ ($ А1; 2) = 0.

Виділяємо діапазон із числами – відкриваємо меню «Умовного форматування». Вибираємо "Створити правило". Натискаємо «Використовувати формулу для визначення осередків, що форматуються». Заповнюємо наступним чином:

Для закриття вікна та відображення результату – ОК.

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

Завдання: виділити кольором рядок, що містить комірку з певним значенням.

Таблиця для прикладу:

Необхідно виділити червоним кольором інформацію щодо проекту, який знаходиться ще в роботі (Р). Зеленим – завершено («З»).

Виділяємо діапазон із значеннями таблиці. Натискаємо «УФ» – «Створити правило». Тип правила – формула. Застосуємо функцію ЯКЩО.

Порядок заповнення умов для форматування «завершених проектів»:

Аналогічно визначаємо правила форматування для незавершених проектів.

У «Диспетчері» умови виглядають так:

Отримуємо результат:

Коли задані параметри форматування для всього діапазону, умова виконуватиметься одночасно із заповненням осередків. Наприклад, «завершимо» проект Димитрової за 28.01 – поставимо замість «Р» «З».

"Розмальовка" автоматично змінилася. Стандартними засобами Excel до таких результатів довелося б довго йти.

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

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

Наведемо найпростіший прикладзведеної таблиці, що описує обсяги продажу якогось товару по регіонах:

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

Найпростіший варіант – використовувати колірні шкали. Для цього виділяємо поле "Обсяг продажів", охоплюючи всі періоди. Залишилося відкрити вкладку «Головна», де натискаємо кнопку «Умовне форматування» (якщо ви раптом використовуєте англійську версію, то дана функціяназивається "Conditional Formatting"). Наведіть курсор на "Гістограми".

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

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

Отже – готові сценарії, здатні допомогти у більшості ситуацій:

Перші 10 елементів;

Останні 10 елементів;

Перші 10%;

Останні 10%;

Більше середнього;

Найменше середнього.

Видалення умовного форматування, що вже використовується, в Excel 2010 відбувається за наступною схемою: у зведеній таблиці переходимо на вкладку «Головна», натискаємо на «Умовне форматування», далі «Стилі», і у випадаючому меню використовуємо команду «Видалити правила» - «Видалити правила з цієї зведеної таблиці» (в англійському варіанті – «Clear Rules» та «Clear Rules from this PivotTable»).

Втім, можна легко створювати власні правила форматування.

Ця таблиця – ускладнений варіант першої, отже відразу переходимо наприклад. Давайте відстежимо обсяг продажу та виручку за годину. Ми будемо використовувати умовне форматування Excel 2010 для прискорення пошуку збігів та відмінностей. Виділяємо "Обсяг продажів". Далі за стандартною процедурою активуємо сценарій ("Головна" - "Умовне форматування"), але вибираємо не готовий варіант, а функцію "Створити правило" (або "New Rule").

Саме тут можна визначити осередки, на які застосовуватимете умовне форматування в Excel 2010, тип використовуваного правила і, власне, параметри форматування. Спочатку задаються осередки, і тут є досить простий вибір:

Виділені (Selected Cells);

Вхідні в стовпець "Обсяг продажів" ("All Cells Showing "Sales_Amount" Values"), включаючи проміжні та загальні підсумки. Даний варіант, до речі, добре підходить для аналізу тих даних, які вимагають визначення середнього, відсоткового співвідношення або іншими величинами, що так чи інакше є різними рівнями однієї величини;

Вхідні в категорію "Обсяг продажів" тільки для "Ринку збуту" (All Cells Showing "Sales_Amount" Values ​​for "Market"). Даний варіант повністю виключає загальні та проміжні результати, що зручно для аналізу деяких окремих значень.

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

На наш приклад найвигіднішим є варіант «3», тому використовується такий варіант:

При виборі правила (розділ «Вибрати правило» або «Select a Rule Туре») вказуємо саме те, що відповідає нашим вимогам.

Це може бути:

- Форматування осередків на підставі значень (Format All Cells Based on Their Values). Використовується для форматування осередків, які відповідають діапазону значень, що використовується. Найкраще підходить для визначення різних відхилень, якщо доводиться працювати з величезним набором даних.

- "Форматування осередків, що містять" ("Format Only Cells That Contain"). Форматує осередки, що відповідають відповідним умовам. У разі порівняння значень форматованих осередків зі звичайними немає. Використовується для порівняння загального набору даних із зазначеною раніше характеристикою.

- "Форматування перших та останніх значень" ("Format Only Top or Bottom Ranked Values").

- Форматувати значення нижче або вище середнього (Format Only Values ​​That Are Above or Below the Average).

- "Використовувати формулу визначення форматованих осередків" ("Use a Formula to Determine Which Cells to Format"). Тут умови умовного форматування спираються на формулу, задану самим користувачем. Якщо значення комірки (з підставлених у формулу) приходить зі значенням true, то до комірки застосовують форматування. У разі значення «false» форматування не застосовується.

Застосування гістограм, наборів значків та колірних шкал можливе лише тоді, коли форматування виділених осередків відбувається на підставі значень, занесених до них. Для цього встановлюємо перший перемикач на Форматування всіх осередків на підставі значень (Format All Cells Based on Their Values). Для позначення проблемних областей можна використовувати набір піктограм, що також добре підходить для цього сценарію.

Ну і лишилося визначити точні параметри нашого форматування. Тут знадобиться розділ "Зміни опису правила" ("Edit the Ruie Description"). Для додавання значків у проблемні осередки, ми використовуємо меню «Стиль формату» («Format Style») і вибираємо «Набори значків» («Icon Sets»).

У списку «Стилю значка» залишається вибрати значення «3 знаки» Це добре підійде, якщо таблицю неможливо повністю розфарбувати. В результаті в вікні у нас має вийти таке:

При такій конфігурації Excel самостійно додаватиме в комірки значки, при цьому дотримуючись функцій:

>=67, >=33 та

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

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

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

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

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

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

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

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

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

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

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

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

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

• Більше;
• Менше;
• Рівно;
• між;
• Дата;
• Значення, що повторюються.

Розглянемо застосування цих процесів на прикладах. Виділимо діапазон осередків, і натисніть на пункт «Більше…».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ми раді, що змогли допомогти Вам у вирішенні проблеми.

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

Урок 8. Форматування осередку

Можна змінити формат комірки, запам'ятати його та застосувати до іншої таблиці. Спочатку розглянемо можливості форматування осередку. Для цього виділимо кілька осередків, потім натисніть на них правою кнопкоюта викличемо режимФормат осередків.

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

Вкладка Вирівнювання дозволяє встановити: де буде знаходитись текст у осередку. Припустимо, ми набрали текст в осередок.

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

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

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

І текст пересунеться вище.

Цікавий режим автопідбір ширини, який дозволяє програмі автоматично збільшувати розмір комірки (по горизонталі та вертикалі), якщо значення виходить за рамки наявних. Наприклад, збільшимо розмір шрифту до 24 у створеній на минулих уроках таблиці при параметрі автопідбору.

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

На вкладці Шрифт є можливість встановити вид шрифту, його зображення, розмір, колір, встановити його як закреслений, надрядковий, підрядковий.

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

У полі Зразок можна подивитися, як виглядатиме текст.

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

Як видно, текст налазить на текст інших осередків. Скористаємося режимомФормат на вкладці Головна.

У панно, що з'явилося, виберемо режимАвтопідбір ширини тексту. Отримаємо:

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

Виділимо їх та скористаємося вкладкоюКордон .

Ми вибрали колір – помаранчевий, тип лінії – подвійний та натиснули на кнопку зовнішні.

Тепер знову виділимо таблицю, і знову скористаємося вкладкоюКордон .

Ми вибрали інший колір, тип лінії та натиснули на кнопку внутрішні. Можна було не виходити з режиму встановлення меж, встановити колір, тип лінії, натиснути кнопку зовнішні, потім змінити тип лінії, колір і натиснути кнопку внутрішні.

Вкладка Заливка дозволяє встановити заливку осередків. Знову виділимо попередні комірки та встановимо колір. Можна вибрати колір, і тоді осередки будуть зафарбовані однорідним кольором, але ми вибралиВізерунок і до нього Колір візерунка.

Збереження стилю осередків . Припустимо, що стилем ми будемо користуватися при створенні наступних таблиць. Тому виділимо знову чотири осередки та натиснемо на кнопкуСтилі осередків на вкладці Головна.

Тут вже є стилі, встановлені в програмі, але нам потрібно створити власний стиль. Тому натиснемо на написСтворити стиль осередків.

На екрані з'явиться вікно, в якому є елементи, для яких створюється стиль, назвемо його Проба. Поставимо галочки у всіх перемикачах та натиснемо на кнопкуОК . Тепер новий стиль запам'ятається у програмі та коли викличемо режимСтилі осередків , то він з'явиться у списку Користувальницькі.

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

Іноді потрібно виділити цифри залежно від певних умов. Так, якщо в таблиці представлені порівняльні дані за категоріями населення, що зловживають певними продуктами, то осіб, схильних до спиртних напоїв, краще виділити похилим шрифтом, вегетаріанців, що поїдають їжу з грядок - шрифтом з підкресленням, а споживають безконтрольно велику кількість їжі - жир. Крім того, використання кольору може бути продиктовано деякими умовами. Наприклад, якщо температура в квартирі взимку не піднімається вище 0, кількість таких квартир краще показувати синім кольором, при температурі 0 - 10 градусів - зеленим, при діапазоні 10 - 20 градусів - жовтим, а понад 30 градусів - червоним кольором.

Повернемося до створеної таблиці. Виділимо частину таблиці з чисельними значеннями та скористаємося режимом на вкладціГоловна → Умовне форматування. На екрані з'явиться вікно режиму, який відображається на малюнку. У цьому вікні виберемо режимСтворити правило.

З'явиться вікно, де встановимо значення для правил.

Поставимо завдання мати колір фону осередків залежно від їхнього значення. Виберемо верхнє правило Форматувати всі осередкина підставі їх значень та натисніть на кнопкуОК.

Якщо змінимо колір на синій, то отримаємо наступну таблицю.

Виберемо режим у стилі формату – триколірна шкала та натиснемо на кнопкуОК.

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

Можна встановити значення гістограма. Тоді вказаним кольором буде заповнено осередки залежно від значення.

Можна встановити піктограми біля значень за допомогою режиму набори значків.

Можна виконати умовне форматування не з таблицею, а з її частиною. Є й інші режими. Наприклад,Умовне форматування→ Правила виділення осередків→ Між.

У вікні всі значення, які знаходяться між 25 і 72 будуть виділені світло-червоною заливкою та темно-червоним кольором. Ці зміни можна змінити, ввівши їх із клавіатури.

У списку значень на які можна поміняти формат є формат користувача, де можна поміняти тип шрифту, накреслення та ін. Наприклад, можна зробити накреслення напівжирним.

Зазначимо, що можна використовувати кілька правил однієї таблиці.