Виділення даних за допомогою умовного форматування. Умовне форматування: інструмент Microsoft Excel для візуалізації даних Правила з використанням формул

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Перейдемо на вкладку "Головна" на панелі інструментів і кликнемо на пункт "Умовне форматування". У випадаючому списку ви побачите кілька типів формату на вибір:
  • Правила виділення
  • Правила відбору перших і останніх значень
  • гістограми
  • кольорові шкали
  • набори значків
• У нашому прикладі ми хочемо виділити кольором дані з від'ємним значенням. Для цього виберемо тип "Правила виділення осередків" \u003d\u003e "Менше":

Також, доступні наступні умови:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Повторно виділимо лівою клавішею миші клітинку, якої ми присвоїли формат. Кликнемо на пункт "Умовне форматування". Виберемо в випадаючому меню "Управління правилами" \u003d\u003e кликнемо на кнопку "Змінити правило":

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

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

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

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

Можливо застосовувати кілька правил до одній комірці.

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

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

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

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

Для того щоб скористатися даною можливістю, потрібно перейти на вкладку «Головна» і клікнути на кнопку «Умовне форматування».

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

• правила виділення осередків;

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

• гістограми;

• колірні шкали;

• набори значків;

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

У цьому розділі також дуже багато різних варіантів форматування. Розберемо кожен з них.

більше

1. Для початку виділіть якусь сходинку. В даному випадку це будуть постраждалі в першій шахті.

1. Потім перейдіть на вкладку «Головна» і натисніть на кнопку «Умовне форматування». У меню натискаємо на пункт «Правила виділення осередків». Потім вибираємо варіант «Більше».

1. Після цього з'явиться вікно, в якому потрібно вказати значення для порівняння виділених елементів. Можна вбити що завгодно або клікнути на якусь клітинку. Натисніть на середнє значення. Даний показник цілком підходить для порівняння.

1. Відразу після цього посилання на осередок підставить автоматично (а вона сама виділиться пунктирною лінією). Для вставки натисніть на кнопку «OK».

1. В результаті цього ми побачимо, що осередки, в яких значення більше 27, підсвітили іншим кольором.

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

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

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

1. Виділіть яку-небудь рядок. Натисніть на іконку «Умовне форматування», розташовану на вкладці «Головна». Виберіть пункт «Правила виділення осередків», а потім - «Менше».

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

1. В результаті цього відбудеться підстановка потрібної адреси. Для збереження налаштувань натисніть на кнопку «OK».

1. В результаті цього ми бачимо, що всі осередки, значення яких менше 24, виділилися іншим кольором.

1. Виділіть яку-небудь рядок без правил форматування. Заходимо в той же розділ меню, але на цей раз обираємо пункт «Між».

1. Потім редактор Ексель сам запропонує якісь проміжні значення. Можна залишити все без змін.

1. Або підставити щось своє, що зручніше вам. Наприклад, більше 14, але менше 17. Для збереження натисніть на кнопку «OK».

1. В результаті цього все те, що знаходиться між цими цифрами, виділилося іншим кольором.

1. Виділяємо іншу вільну від форматування осередок. Проробляємо той же самий шлях на панелі інструментів і вибираємо пункт «Так само».

1. Нас попросять вказати посилання на осередок для порівняння або ж готове числове значення. Введемо, наприклад, цифру 18. Оскільки вона зустрічається в виділеному рядку. Для збереження натискаємо на кнопку «OK».

1. Завдяки цьому осередок, яка відповідає вказаним значенням, стала підсвічена іншим кольором.

1. Для перевірки можна спробувати змінити що-небудь. Для прикладу візьмемо сусідню клітку. Виправимо там 19 на 18. Після натискання на клавішу Enter, ви побачите наступне.

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

текст містить

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

1. Насамперед виділіть якусь рядок з декількома цифрами. Потім за допомогою знайомого нам меню виберіть пункт «Текст містить ...».

1. В результаті цього з'явиться вікно, в якому потрібно вказати який-небудь фрагмент тексту. Це може бути буква або цифра. Для прикладу введемо число «2». Для збереження форматування натисніть на кнопку «OK».

1. В результаті цього виділилися осередки з числами 20 і 23, оскільки в обох з них є цифра 2.

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

1. Для початку додамо рядок, в якій напишемо кілька дат. Бажано, щоб вони йшли поспіль. Так простіше буде порівнювати.

1. Після цього виділяємо всю цю рядок. Потім переходимо в меню «Умовне форматування» і вибираємо пункт «Дата».

1. Відразу після цього з'явиться вікно, в якому можна буде вибрати кілька варіантів:
   • вчора;
   • сьогодні;
   • завтра;
   • за останні 7 днів;
   • минулого тижня;
   • на поточному тижні;
   • на наступному тижні;
   • минулого місяця;
   • цього місяця;
   • в наступному місяці.

1. Як приклад виберемо варіант «Завтра». Для збереження натисніть на кнопку «OK».

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

1. Поточна дата на момент написання статті - 25 лютий 2018 року.

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

1. Виділіть в таблиці основні значення, які потрібно якось аналізувати.

1. Натисніть на іконку «Умовне форматування» і в «Правилах виділення осередків» виберіть пункт «повторюються значення».

1. Відразу після цього з'явиться вікно, в якому ви зможете вибрати два значення:
   • повторювані;

• унікальні.

У кожному разі буде доступний попередній перегляд, щоб ви могли точно визначитися, що саме вам потрібно. Для збереження потрібно клікнути на кнопку «OK».

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

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

Розглянемо кожен з них.

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

Відлік походить від більшого значення до меншого.

Це означає, що якщо вам необхідно виділити перші 10 клітин, в яких знаходяться найменші цифри, то потрібно вибрати пункт «Останні 10 елементів».

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

Зверніть увагу на те, що якщо буде два осередки з однаковим найбільшим числом, то виділяться обидва!

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

Якщо вказати число 10 (воно використовується за умовчанням), то ви побачите наступне.

Якщо дане правило форматування вам сподобалося, потрібно натиснути на кнопку «OK». В іншому випадку клікніть на «Скасування».

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

Як було сказано вище, в даному випадку виділяються ті осередки, в яких містяться мінімальні дані. Принцип введення той же самий - вказуєте потрібну кількість і клікаєте на кнопку «OK».

Якщо ви вкажете всього 1 клітку, але при цьому мінімальних цифр буде кілька, то станеться виділення всіх (в нашому випадку - дві).

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

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

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

В описаних вище методах порівняння даних використовувався метод суцільної заливки елементів. Іноді це не зовсім зручно.

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

• градиентная;
• суцільна.

Розглянемо кожен із запропонованих варіантів.

градієнтна заливка

1. Насамперед необхідно виділити потрібні рядки і стовпці. Потім клікнути на іконку «Умовне форматування». Після цього перейти в розділ «Гістограми» і вибрати будь-яку із запропонованих заливок.

До значень за замовчуванням відносяться:

• зелена;
• червона;
• помаранчева;
• блакитна;
• фіолетова.

При наведенні на кожен з варіантів вам буде доступний попередній перегляд.

Даний тип маркування сильно не відрізняється від вищеописаного і знаходиться він в тому ж розділі.

Кольори використовуються ті ж самі.

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

Тут ви зможете налаштувати:

• стиль;

• мінімальне і максимальне значення;

• зовнішній вигляд стовпчика.

Зразок того, що ви налаштували, можна побачити в правому нижньому кутку.

Якщо ви хочете чогось більш контрастного, потрібно зробити наступні дії.

1. Виділіть таблицю (основну інформацію для аналізу даних). Натисніть на іконку «Умовне форматування», яка розташована на «Головною» вкладці на панелі інструментів. В меню оберіть пункт «Кольорові шкали». В результаті цього з'явиться великий список з 12 варіантів оформлення.

1. При наведенні на кожен шаблон ви побачите подібне пояснення.

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

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

Відразу після цього ви побачите наступне вікно. Тут можна вказати початковий і кінцевий колір. Для збереження досить натиснути на кнопку «OK».

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

1. Виділіть основні елементи таблиці.
2. Натисніть на панелі інструментів на «Умовне форматування».
3. В меню оберіть категорію «Набори значків».
4. Відразу після цього ви побачите великий список різних шаблонів.

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

До можливих варіантів відносяться (кожен раз при наведенні на будь-яку іконку ви будете бачити попередній перегляд без збереження правила форматування):

• напрямку (близько великих чисел з'явиться стрілка вгору; для середніх - направо; мінімальним цифрам відповідає напрямок вниз);

• фігури (колір залежить від числа в комірці - сірий колір для найбільших значень);

• індикатори (галочка - високі, знак оклику - середні, а хрестик - мінімум);

• оцінки (ступінь заповнення елемента залежить від числа в осередку);

Якщо жодна з іконок вам не сподобалася, ви можете створити своє правило заповнення клітин.

В цьому випадку ви зможете самостійно вказати наступні параметри:

• стиль значка;

• свій варіант значка;

• граничні значення для значків;

Для збереження натисніть на кнопку «OK».

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

1. Для початку потрібно виділити ті елементи, умовне форматування яких необхідно відключити.

1. Потім натисніть на вкладці «Головна» на іконку «Умовне форматування».
2. Після цього виберіть пункт «Видалити правила».
3. Далі натискаємо на «Видалити правила з виділених осередків».

1. Якщо хочете видалити все, то виділяємо другий пункт - «Видалити правила з усього листа».

1. Результат буде таким. Все повернеться до свого колишнього вигляду.

Набір способів форматування можна змінювати за власним бажанням. Це робиться в такий спосіб.

1. Натисніть на кнопку «Умовне форматування».
2. Виберіть пункт «Управління правилами».

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

1. Виберіть пункт «Цей лист».

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

видалення

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

Необхідно бути дуже уважним при виконанні таких дій, оскільки вас додатково не запитають, чи впевнені ви в своєму виборі.

зміна

Редагувати правила досить просто. Це робиться в такий спосіб.

1. Виберіть будь-який рядок.
2. Натисніть на кнопку «Змінити правило».

1. В результаті цього ви побачите наступне вікно. За умовчанням вибраний тип «Форматувати тільки ті осередки, які містять».

1. Тут ви зможете вказати що саме вони містять:
   • текст;
   • дати;
   • порожні;
   • непусті;
   • помилки;
   • без помилок.

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

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

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

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

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

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

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



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

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

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

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

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

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

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

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

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

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

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

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

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

Цей урок з прикладами і відео ми присвятимо умовного форматування - одному з найбільш цікавих і корисних засобів Excel.

Що таке умовне форматування

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

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

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

Отже, зона роботи виділена, кнопка натиснута - що далі? Перед вами відкриється меню умовного форматування, де будуть такі пункти:

1. Правила відбору перших і останніх значень.
2. Кольорові шкали.
3. Додатково: створити, видалити, управління правилами.

Що з цим робити? Давайте по порядку. Цей пункт, в свою чергу, вміщує в себе такі стандартні функції, як

• більше;
• менше;
• Так само;
• Текст містить;
• Дата;
• Повторювані значки.

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

1. Натискаємо «Між» і в новому вікні у відповідних осередках вводимо параметри від і до.
2. Потім вкажіть колір, яким хочете виділити відповідні вам варіанти (нехай у нас це буде «Світло-червона заливка і темно-червоний текст»). Тобто якщо ви працюєте зі стовпчиком цін на мобільні телефони, то введіть цифри мінімальної і максимальної вартості, що вам підходить (нехай у нас це буде 50 і 100).
3. Після того як ви підтвердили, що саме МІЖ цими значеннями хочете почати пошук, в таблиці комірки підсвітяться відповідним чином і ми побачимо ВСЕ осередки з ціною від 50 до 10 доларів пофарбованими в світло-червоний колір і з темно-червоним текстом.

Все це зовсім нескладно, коли на практиці приступити до роботи з програмою.

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

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

Наступний пункт перед нами. Як це працює? Якщо вам потрібно виділити кілька перших або останніх осередків по введених даних, то ви саме там, де треба. Пояснювати тут більше нічого, тому перейдемо до прикладу.

1. Натиснувши «Перші 10 елементів» ми викличемо вікно, де можна управляти цим форматуванням.
2. Тут вкажемо кількість осередків, які нам потрібно виділити: спочатку було названо 10, але нам треба тільки 5, тому виправляємо це у відповідному полі.
3. Потім вибираємо колір форматування: нехай у нас це буде «Червоний кордон».
4. Тоді 5 осередків з найбільшими значеннями буду виділені червоною рамкою.

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

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

кольорові шкали

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

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

• вибираємо «Набори значків» і в розділі «Напрямки» натискаємо на «5 кольорових стрілок». Таким чином, в кожному осередку поля, в якому ми працюємо, з'явиться один з 5 типів стрілки.
• Пояснимо, як вони працюють: весь діапазон значень в виділених нами осередках складає 100%, а кожна по черзі стрілочка відповідає за числа, які входять в кожні 20% по порядку. Нехай у нас в стовпці кількості покупок телефону є значення від 0 до 100. Тоді перша стрілка (зелена вгору) буде стояти біля кожного значення від 80 до 100, а остання (червона вниз) - біля кожного від 0 до 20. Відповідно і всі проміжні стрілки.

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