Умовне форматування в Excel: Повний посібник із прикладами

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

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

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

Використання умовного форматування в Excel (приклади)

У цьому уроці я покажу вам сім дивовижних прикладів використання умовного форматування в Excel:

• Швидко ідентифікуйте дублікати за допомогою умовного форматування в Excel.
• Виділити клітинки зі значенням більшим/меншим за число в наборі даних.
• Виділення верхніх/нижніх 10 (або 10%) значень у наборі даних.
• Виділення помилок/пробілів за допомогою умовного форматування в Excel.
• Створення теплових карт із використанням умовного форматування в Excel.
• Виділіть кожен N -й рядок/стовпець за допомогою умовного форматування.
• Пошук і виділення за допомогою умовного форматування в Excel.

1. Швидко ідентифікуйте дублікати

Умовне форматування в Excel можна використовувати для ідентифікації дублікатів у наборі даних.

Ось як це можна зробити:

• Виберіть набір даних, у якому потрібно виділити дублікати.
• Перейдіть додому -> Умовне форматування -> Виділення правил комірки -> Повторювані значення.
• У діалоговому вікні «Повторювані значення» переконайтеся, що у спадному меню ліворуч вибрано «Дублікат». Ви можете вказати формат, який буде застосовано, за допомогою розкривного списку праворуч. Існують деякі існуючі формати, які можна використовувати або вказати власний формат за допомогою опції Спеціальний формат.
• Натисніть OK.

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

Дивитися також: Повний посібник із пошуку та видалення дублікатів у Excel.

2. Виділіть комірки зі значенням більшим/меншим за число

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

Нижче наведено кроки для цього.

• Виберіть весь набір даних.
• Перейдіть додому -> Умовне форматування -> Правила виділення клітинок -> Більше, ніж… / Менше, ніж…
• Залежно від того, який варіант ви вибрали (більший або менший), відкриється діалогове вікно. Скажімо, ви обираєте варіант "Більше". У діалоговому вікні введіть номер у поле зліва. Метою є виділення клітинок, число яких перевищує зазначене число.

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

Це миттєво виділить усі комірки зі значеннями більше 5 у наборі даних.Примітка: Якщо ви хочете виділити значення, більші за 5, вам слід знову застосувати умовне форматування з критерієм «Дорівнює».

Цей же процес можна виконати, щоб виділити клітинки зі значенням, меншим за вказані значення.

3. Виділення зверху/знизу 10 (або 10%)

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

Аналогічно, ви також можете швидко визначити 10 найнижчих елементів або 10% нижчих у наборі даних.

Нижче наведено кроки для цього.

• Виберіть весь набір даних.
• Перейдіть додому -> Умовне форматування -> Правила зверху / знизу -> 10 найпопулярніших елементів (або %) / Нижні 10 елементів (або %).
• Виходячи з того, що ви вибрали, він відкриє діалогове вікно. Скажімо, ви вибрали 10 найкращих пунктів, тоді відкриється діалогове вікно, як показано нижче:
• Укажіть формат, який буде застосовано до клітинок, які відповідають умові, використовуючи спадне меню праворуч. Існують деякі існуючі формати, які можна використовувати або вказати власний формат за допомогою опції Спеціальний формат.
• Натисніть OK.

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

Крім того, якщо у наборі даних є менше 10 клітинок, і ви обираєте параметри для виділення 10 найкращих елементів/10 елементів знизу, усі клітинки будуть виділені.

Ось кілька прикладів того, як буде працювати умовне форматування:

4. Виділення помилок/пробілів

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

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

Припустимо, у нас є набір даних, як показано нижче:

Цей набір даних містить порожню клітинку (A4) та помилки (A5 та A6).

Нижче наведено кроки, щоб виділити клітинки, які порожні або мають помилки:

• Виберіть набір даних, у якому потрібно виділити порожні клітинки та клітинки з помилками.
• Перейдіть додому -> Умовне форматування -> Нове правило.
• У діалоговому вікні Нове правило форматування виберіть Використовувати формулу, щоб визначити, які клітинки відформатувати.
• Введіть у поле в розділі «Редагувати опис правила» таку формулу:
  = АБО (ISBLANK (A1), ISERROR (A1))
  • Наведена вище формула перевіряє всі клітинки на наявність двох умов - чи є вони порожніми чи ні, і чи є у них помилка чи ні. Якщо будь -яка з умов є TRUE, вона повертає TRUE.
• Встановіть формат, який потрібно застосувати до осередків, які є порожніми або мають помилки. Для цього натисніть кнопку Формат. Відкриється діалогове вікно "Форматувати клітинки", де можна вказати формат.
• Натисніть ОК.

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

Примітка: Вам не потрібно використовувати весь діапазон A1: A7 у формулі в умовному форматуванні. Вищезгадана формула використовує лише A1. Коли ви застосовуєте цю формулу до всього діапазону, Excel перевіряє одну клітинку за раз і коригує посилання. Наприклад, коли він перевіряє A1, він використовує формулу = АБО (ISBLANK (A1), ISERROR (A1)). Перевіряючи клітинку A2, вона використовує формулу = АБО (ISBLANK (A2), ISERROR (A2)). Він автоматично регулює посилання (оскільки це відносні посилання) залежно від того, яка клітинка аналізується. Тому вам не потрібно писати окрему формулу для кожної клітинки. Excel досить розумний, щоб змінити посилання на клітинку самостійно 🙂

Дивитися також: Використання IFERROR та ISERROR для обробки помилок у excel.

5. Створення теплових карт

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

Щось, як показано нижче:

Наведений вище набір даних має значення від 1 до 100. Клітинки виділяються на основі значення в ньому. 100 отримує зелений колір, 1 - червоний.

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

• Виберіть набір даних.
• Перейдіть додому -> Умовне форматування -> Кольорові гами та виберіть одну з колірних схем.

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

Примітка. Аналогічним чином можна також застосувати набори даних барда та значків.

6. Виділіть кожен інший рядок/стовпець

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

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

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

Інший спосіб - використання умовного форматування.

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

Ось кроки для виділення альтернативних рядків за допомогою умовного форматування в Excel.

• Виберіть набір даних. У наведеному вище прикладі виберіть A2: C13 (що виключає заголовок). Якщо ви також хочете включити заголовок, виберіть весь набір даних.
• Відкрийте діалогове вікно умовного форматування (Домашня сторінка-> Умовне форматування-> Нове правило) [Комбінація клавіш - Alt + O + D].
• У діалоговому вікні виберіть діалогове вікно "Використовувати формулу, щоб визначити, які клітинки відформатувати".
• Введіть у поле в розділі "Редагувати опис правила" таку формулу:
  = ISODD (ROW ())

• Наведена вище формула перевіряє всі комірки, і якщо число рядка клітинки непарне, то воно повертає TRUE. Зазначений умовний формат буде застосовано до всіх комірок, які повертають TRUE.
• Встановіть формат, який потрібно застосувати до осередків, які є порожніми або мають помилки. Для цього натисніть кнопку Формат. Відкриється діалогове вікно "Форматувати клітинки", де можна вказати формат.
• Натисніть OK.

Це воно! Альтернативні рядки в наборі даних будуть виділені.

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

• Виділіть почергові парні рядки: = ISEVEN (ROW ())
• Виділіть альтернативні рядки додавання: = ISODD (ROW ())
• Виділіть кожен 3 -й рядок: = MOD (ROW (), 3) = 0

7. Пошук та виділення даних за допомогою умовного форматування

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

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

Нижче описано, як створити цю функцію пошуку та виділення:

• Виберіть набір даних.
• Перейдіть додому -> Умовне форматування -> Нове правило (Комбінація клавіш - Alt + O + D).
• У діалоговому вікні "Нове правило форматування" виберіть опцію "Використовувати формулу, щоб визначити, які клітинки відформатувати".
• Введіть у поле в розділі "Редагувати опис правила" таку формулу:
  = І ($ C $ 2 ””, $ C $ 2 = B5)
• Встановіть формат, який потрібно застосувати до осередків, які є порожніми або мають помилки. Для цього натисніть кнопку Формат. Відкриється діалогове вікно "Форматувати клітинки", де можна вказати формат.
• Натисніть OK.

Це воно! Тепер, коли ви вводите що -небудь у клітинку С2 і натискаєте Enter, вона виділить усі відповідні клітинки.

Як це працює?

Формула, що використовується для умовного форматування, оцінює всі комірки в наборі даних. Припустимо, ви в’їжджаєте до Японії у клітині С2. Тепер Excel оцінить формулу для кожної клітинки.

Формула повертає TRUE для клітинки, якщо виконуються дві умови:

• Осередок С2 не порожній.
• Вміст комірки С2 точно відповідає вмісту комірки у наборі даних.

Отже, всі клітинки, що містять текст Японії, виділяються.

Завантажте файл прикладу

Ви можете використовувати ту саму логіку для створення таких варіантів, як:

• Виділіть весь рядок замість клітинки.
• Виділити, навіть якщо є часткове збіг.
• Виділяйте клітинки/рядки під час введення (динамічний) [Вам сподобається цей трюк :)].

Як видалити умовне форматування в Excel

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

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

Щоб видалити умовне форматування:

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

Важливі відомості про умовне форматування в Excel

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