Умовне форматування в MS EXCEL

1. ПОРІВНЯННЯ З постійне значення (константи)
2. ПОРІВНЯННЯ СО значення в осередках (АБСОЛЮТНА ПОСИЛАННЯ)
3. Попарного порівняння СТРОК / стовпців (ВІДНОСНІ ПОСИЛАННЯ)
4. ВИДІЛЕННЯ СТРОК
5. ВИДІЛЕННЯ ОСЕРЕДКІВ З ТЕКСТОМ
6. ВИДІЛЕННЯ ОСЕРЕДКІВ З ЧИСЛАМИ
7. ВИДІЛЕННЯ ОСЕРЕДКІВ з датами
8. ВИДІЛЕННЯ ОСЕРЕДКІВ з повтором
9. ЗАСТОСУВАННЯ КІЛЬКОХ ПРАВИЛ
10. ПРІОРИТЕТ ПРАВИЛ
11. УМОВНЕ ФОРМАТИРОВАНИЕ і ФОРМАТ ОСЕРЕДКІВ
12. ВІДЛАДКА ПРАВИЛ УМОВНОГО ФОРМАТУВАННЯ
13. ВИКОРИСТАННЯ В ПРАВИЛА ПОСИЛАНЬ НА ІНШІ ТОВАРІВ
14. ПОШУК ОСЕРЕДКІВ З умовного форматування
15. ІНШІ визначених правил
16. ПРАВИЛА З ВИКОРИСТАННЯМ ФОРМУЛ

Умовне форматування - один з найкорисніших інструментів EXCEL. Уміння ним користуватися може заощадити користувачеві багато часу і сил.

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

Ці правила використовуються досить часто, тому в EXCEL 2007 вони винесені в окреме меню Правила виділення осередків.

Ці правила також же доступні через меню.

Розглянемо кілька завдань:

ПОРІВНЯННЯ З постійне значення (константи)

Задача1. Порівняємо значення з діапазону A1: D1 з числом 4.

• введемо в діапазон A1: D1 значення 1, 3, 5, 7
• виділимо цей діапазон;
• застосуємо до виділеного діапазону Умовне форматування на значення Менше ();
• в лівому полі вікна, що з'явилося введемо 4 - відразу ж побачимо результат застосування умовного форматування.
• Натиснемо ОК.

Результат можна побачити в файлі прикладу на аркуші Задача1.

ПОРІВНЯННЯ СО значення в осередках (АБСОЛЮТНА ПОСИЛАННЯ)

Трохи ускладнити попередню задачу: замість введення в якості критерію безпосередньо значення (4), введемо посилання на осередок, в якій міститься значення 4.

Задача2. Порівняємо значення з діапазону A1: D1 з числом з осередку А2.

• введемо в осередок А2 число 4;
• виділимо діапазон A1: D1;
• застосуємо до виділеного діапазону Умовне форматування на значення Менше ();
• в лівому полі вікна, що з'явилося введемо посилання на осередок A2 натиснувши на кнопочку, розташовану в правій частині вікна (EXCEL за замовчуванням використовує абсолютну посилання $ А $ 2).

Натисніть ОК.

В результаті, все значення з виділеного діапазону A 1: D 1 будуть порівнюватися з одним осередком $ А $ 2. Ті значення з A 1: D 1, які менше A 2 будуть виділені заливкою фону комірки.

Результат можна побачити в файлі прикладу на аркуші Задача2.

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

Попарного порівняння СТРОК / стовпців (ВІДНОСНІ ПОСИЛАННЯ)

Тепер будемо виробляти попарне порівняння значень в рядках 1 і 2.

Задача3. Порівняти значення осередків діапазону A 1: D 1 зі значеннями з осередків діапазону A 2: D 2. Для цього будемо використовувати відносну посилання.

• введемо в осередку діапазону A 2: D 2 числові значення (можна вважати їх критеріями);
• виділимо діапазон A 1: D 1;
• застосуємо до виділеного діапазону Умовне форматування на значення Менше ()
• в лівому полі вікна, що з'явилося введемо відносне посилання на комірку A 2 (тобто просто А2 або змішану посилання А $ 2). Переконайтеся, що знак $ відсутня перед назвою стовпця А.

Тепер кожне значення в рядку 1 буде порівнюватися з відповідним йому значенням з рядка 2 в тому ж стовпці! Виділено будуть значення 1 і 5, тому що вони менше відповідно 2 і 6, розташованих в рядку 2.

Результат можна побачити в файлі прикладу на аркуші Задача3.

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

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

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

: Щоб дізнатися адресу активної комірки (вона завжди одна на аркуші) можна подивитися в поле Ім'я (знаходиться зліва від рядки формул ). У задачі 3, після виділення діапазону A1: D1 (клавіша миші повинна бути відпущена), в поле Ім'я , Там буде відображений адреса активної клітинки A1 або D 1. Чому можливо 2 Виріанти і в чому різниця для правил умовного форматування?

Подивимося уважно на другий крок рішення попередньої задачі3 - виділення діапазону A 1: D 1. Зазначений діапазон можна виділити двома способами: виділити осередок А1, потім, не відпускаючи клавіші миші, виділити весь діапазон, рухаючись вправо до D1; або, виділити осередок D1, потім, не відпускаючи клавіші миші, виділити весь діапазон, рухаючись вліво до А1. Різниця між цими двома способами принципова: у першому випадку, після завершення виділення діапазону, активною коміркою буде А1, а в другому D 1!

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

Якщо ми виділили діапазон першим способом, то, ввівши в правило умовного форматування відносне посилання на комірку А2, ми тим самим сказали EXCEL порівнювати значення активної комірки А1 зі значенням в А2. Оскільки правило поширюється на діапазон A 1: D 1, то B 1 буде порівнюватися з В2 і т.д. Завдання буде коректно вирішена.

Якщо при створенні правила умовного форматування активної був осередок D1, то саме її значення буде порівнюватися зі значенням осередки А2. А значення з A 1 буде тепер порівнюватися зі значенням з осередку XFB2 (не знайшовши осередків лівіше A 2, EXCEL обере останнє вічко XFD для С1, потім передостанню для B 1 і, нарешті XFB2 для А1). Переконатися в цьому можна, подивившись створене правило:

• виділіть клітинку A1;
• натисніть;
• тепер видно, що стосовно діапазону $ A $ 1: $ D $ 1 застосовується правило Значення комірки <XFB2 (або <XFB $ 2).

EXCEL відображає правило форматування (Значення комірки <XFB2) стосовно активної осередку, тобто до A1. Правильно застосоване правило, в нашому випадку, виглядає так:

ВИДІЛЕННЯ СТРОК

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

ВИДІЛЕННЯ ОСЕРЕДКІВ З ТЕКСТОМ

В розділі Умовне Форматування Текстових значень наведено ряд спеціалізованих статей про виділення умовним форматуванням осередків що містять текст:

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

Основна стаття - Виділення осередків c текст із застосуванням умовного форматування в MS EXCEL

ВИДІЛЕННЯ ОСЕРЕДКІВ З ЧИСЛАМИ

В розділі Умовне Форматування Числових значень наведено ряд спеціалізованих статей про виділення умовним форматуванням осередків що містять числа.

ВИДІЛЕННЯ ОСЕРЕДКІВ з датами

В розділі Умовне Форматування Дат наведено ряд статей про виділення умовним форматуванням осередків містять дати.

ВИДІЛЕННЯ ОСЕРЕДКІВ з повтором

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

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

Часто потрібно виділити значення або навіть окремі рядки в залежності від того діапазону, якому належить значення. Наприклад, якщо Число менше 0, то його потрібно виділити червоним тлом, якщо більше - то зеленим. Про такому прикладі можна прочитати в статті Виділення Умовним форматуванням Чисел належать різним діапазонам .

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

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

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

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

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

УМОВНЕ ФОРМАТИРОВАНИЕ і ФОРМАТ ОСЕРЕДКІВ

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

Через Формат ячеек можна задати користувальницький формат комірки, який досить гнучкий і іноді навіть зручніше, ніж Умовне форматування. Детальніше див. Статтю Призначений для користувача числовий формат в MS EXCEL (через Формат ячеек) .

ВІДЛАДКА ПРАВИЛ УМОВНОГО ФОРМАТУВАННЯ

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

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

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

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

До 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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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