Застосування умовного форматування за допомогою формули Excel для Mac
Умовне форматування дозволяє швидко виділити на аркуші важливу інформацію. Але іноді вбудованих правил форматування недостатньо. Створивши власну формулу для правила умовного форматування, ви зможете виконувати дії, які не під силу вбудованим правилам.
Припустимо, що ви стежите за днями народження пацієнтів свого стоматологічного кабінету, а потім відзначаєте тих, хто вже одержав від вас вітальну листівку.
За допомогою умовного форматування, яке визначається двома правилами з формулою, на цьому аркуші відображаються необхідні відомості. Правило у стовпці A форматує майбутні дні народження, а правило у стовпці C форматує комірки після введення символу "Y", що означає відправлену вітальну листівку.
Як створити перше правило
- Виділіть комірки від A2 до A7 (для цього клацніть і перетягніть вказівник миші з комірки A2 до комірки A7).
- На вкладці Головна виберіть Умовне форматування >Створити правило.
- У полі Стиль виберіть Класичний.
- Під полем Класичний виберіть елемент Форматувати лише перші чи останні значення і змініть його на Використовувати формулу для визначення осередків, що форматуються..
- У наступному полі введіть формулу: =A2>СЬОГОДНІ() Функція СЬОГОДНІ використовується у формулі для визначення значень дат у стовпці A, що перевищують значення сьогоднішньої дати (майбутніх дат). Осередки, що задовольняють цій умові, форматуються.
- У полі Форматувати за допомогою виберіть формат користувача.
- У діалоговому вікні Формат осередків відкрийте вкладку Шрифт.
- У полі Колір виберіть значення Червоний. У полі Накреслення шрифту виберіть Напівжирний.
- Натисніть кнопку ОК кілька разів закрити всі діалогові вікна.
Тепер форматування застосовано до стовпця A.
Як створити друге правило
- Виділіть комірки від C2 до C7.
- На вкладці Головна виберіть Умовне форматування >Створити правило.
- У полі Стиль виберіть Класичний.
- Під полем Класичний виберіть елемент Форматувати лише перші чи останні значення і змініть його на Використовувати формулу для визначення осередків, що форматуються..
- У наступному полі введіть формулу: =C2="Y" Формула визначає комірки у стовпці C, що містять символ "Y" (прямі лапки навколо символу "Y" вказують Excel, що це текст). Осередки, що задовольняють цій умові, форматуються.
- У полі Форматувати за допомогою виберіть формат користувача.
- У верхній частині вікна відкрийте вкладку Шрифт.
- У полі Колір виберіть Білий. У полі Накреслення шрифту виберіть Напівжирний.
- У верхній частині вікна відкрийте вкладку Заливання та для параметра Колір тла виберіть значення Зелений.
- Натисніть кнопку ОК кілька разів закрити всі діалогові вікна.
Тепер форматування застосовано до стовпця C.
Спробуйте попрактикуватися
У наведених прикладах ми використовували прості формули для умовного форматування. Поекспериментуйте самостійно та спробуйте використати інші відомі вам формули.
Ось ще один приклад для тих, хто хоче дізнатися більше. У книзі створіть таблицю даних із значеннями, наведеними нижче. Почніть із комірки A1. Потім виділіть комірки D2:D11 і задайте нове правило умовного форматування за допомогою наступної формули:
При створенні правила переконайтеся, що воно застосовується до осередків D2:D11.Задайте колірне форматування, яке має застосовуватися до осередків, що задовольняють умові (тобто якщо назва міста зустрічається в стовпці D більше одного разу, а це Москва і Мурманськ).
Умовне форматування в Excel: повний посібник з прикладами
Умовне форматування – одна з найпростіших, але потужних функцій електронних таблиць Excel.
Як випливає з назви, можна використовувати умовне форматування в Excel, якщо хочете виділити комірки, що відповідають зазначеній умові.
Це дає можливість швидко додати шар візуального аналізу до вашого набору даних. Ви можете створювати теплові карти, показувати значки, що збільшуються / спадають, бульбашки Харві і багато іншого, використовуючи умовне форматування в Excel.
Використання умовного форматування в Excel (приклади)
У цьому посібнику я покажу вам сім дивовижних прикладів використання умовного форматування в Excel:
- Швидке визначення дублікатів за допомогою умовного форматування Excel.
- Виділіть комірки зі значенням більше/менше числа в наборі даних.
- Виділення 10 верхніх/нижніх (або 10%) значень у наборі даних.
- Виділення помилок/пробілів за допомогою умовного форматування в Excel.
- Створення теплових карток з використанням умовного форматування в Excel.
- Виділіть кожний N-й рядок / стовпець, використовуючи умовне форматування.
- Пошук та виділення за допомогою умовного форматування в Excel.
1. Швидке визначення дублікатів
Умовне форматування Excel можна використовувати для виявлення дублікатів у наборі даних.
Ось як це можна зробити:
- Виберіть набір даних, у якому ви бажаєте виділити дублікати.
- Перейдіть на головну -> Умовне форматування -> Виділення правил осередків -> Значення, що повторюються.
- У діалоговому вікні «Повторювані значення» переконайтеся, що у лівому списку вибрано «Дублювати». Ви можете вказати формат, який буде застосовуватися, використовуючи правий список, що розкривається. Існують деякі існуючі формати, які можна використовувати або вказати свій власний формат за допомогою параметра «Користувачський формат».
- Натисніть кнопку ОК.
Це миттєво виділить усі комірки, які мають дублікати у вибраному наборі даних. Ваш набір даних може знаходитися в одному стовпці, кількох стовпцях або в несуміжному діапазоні осередків.
Дивіться також: Повний посібник з пошуку та видалення дублікатів в Excel.
2. Виділіть комірки зі значенням більше/менше числа.
Ви можете використовувати умовне форматування в Excel, щоб швидко виділити комірки, що містять значення більше/менше зазначеного значення. Наприклад, виділення всіх осередків із вартістю продажів менше 100 мільйонів або виділення осередків із відмітками менше порогового значення.
Ось як це зробити:
- Виберіть весь набір даних.
- Перейдіть на головну сторінку -> Умовне форматування -> Виділення правил осередків -> Більше, ніж… / Менше, ніж…
- Залежно від того, який варіант ви виберете (більше чи менше), відкриється діалогове вікно. Допустимо, ви вибрали варіант «Більше ніж». У діалоговому вікні введіть число у полі зліва. Мета полягає в тому, щоб виділити осередки, кількість яких перевищує вказане число.
- Вкажіть формат, який буде застосовуватися до осередків, що задовольняють умові, за допомогою списку, що розкривається праворуч. Існують деякі існуючі формати, які можна використовувати або вказати власний формат за допомогою параметра «Користувачський формат».
- Натисніть кнопку ОК.
Це миттєво виділить усі комірки зі значеннями більше 5 у наборі даних. Примітка: Якщо ви бажаєте виділити значення більше 5, вам слід знову застосувати умовне форматування з критерієм "Рівне".
Той самий процес можна виконати, щоб виділити комірки зі значенням менше вказаного.
3. Виділення верхніх/нижніх 10 (або 10%).
Умовне форматування в Excel дозволяє швидко визначити 10 найпопулярніших елементів або 10% найкращих із набору даних.
Так само ви також можете швидко визначити 10 нижніх елементів або 10% нижніх елементів у наборі даних.
Ось як це зробити:
- Виберіть весь набір даних.
- Перейдіть на головну -> Умовне форматування -> Правила зверху/знизу -> 10 перших елементів (або %) / 10 останніх елементів (або %).
- Залежно від того, що ви оберете, відкриється діалогове вікно.
- Вкажіть формат, який буде застосовуватися до осередків, що задовольняють умові, за допомогою розкривного списку праворуч.
- Натисніть кнопку ОК.
Це миттєво виділить 10 найкращих елементів у вибраному наборі даних.
Крім того, якщо у вас менше 10 осередків у наборі даних, і ви вибираєте параметри, щоб виділити перші 10 елементів / останні 10 елементів, тоді всі осередки будуть виділені.
Ось кілька прикладів того, як працюватиме умовне форматування:
4.Виділення помилок / перепусток
Якщо ви працюєте з великою кількістю числових даних та розрахунків в Excel, ви знаєте, як важливо виявляти та обробляти осередки, в яких є помилки або порожні. Якщо ці осередки використовувати в подальших обчисленнях, це може призвести до помилкових результатів.
Умовне форматування в Excel може допомогти вам швидко визначити та виділити осередки з помилками або порожні.
Припустимо, у нас є набір даних, як показано нижче:
У цьому наборі даних є порожній осередок (A4) та помилки (A5 та A6).
Ось кроки, щоб виділити комірки, які порожні або містять помилки:
- Виберіть набір даних, у якому ви бажаєте виділити порожні комірки та комірки з помилками.
- Перейдіть на головну -> Умовне форматування -> Нове правило.
- У діалоговому вікні «Нове правило форматування» виберіть «Використовувати формулу», щоб визначити, які осередки потрібно форматувати.
- Введіть таку формулу в полі в розділі "Редагувати опис правила":
= АБО (ПОМИЛКА (A1); ПОМИЛКА (A1))
- Наведена вище формула перевіряє всі осередки наявність двох умов - порожня вона чи ні і чи є у ній помилка. Якщо будь-яка з умов ІСТИНА, повертається ІСТИНА.
Це миттєво виділить усі осередки, які або порожні, або містять помилки.
Примітка: Необов'язково використовувати весь діапазон A1: A7 у формулі за умовного форматування. Вищезгадана формула використовує лише A1. Коли ви застосовуєте цю формулу до всього діапазону, Excel перевіряє одну комірку за один раз і коригує посилання. Наприклад, під час перевірки A1 використовується формула = OR (ISBLANK (A1), ISERROR (A1)). Коли він перевіряє комірку A2, він використовує формулу = АБО (ISBLANK (A2), ISERROR (A2)).Він автоматично коригує посилання (оскільки це відносні посилання) залежно від того, який осередок аналізується. Таким чином, вам не потрібно писати окрему формулу для кожного осередку. Excel досить розумний, щоб самостійно змінити посилання на комірку 🙂
Дивіться також: Використання IFERROR та ISERROR для обробки помилок у Excel.
5. Створення теплових карт
Теплова карта - це візуальне подання даних, де колір представляє значення в осередку. Наприклад, ви можете створити теплову карту, на якій осередок з найбільшим значенням забарвлений у зелений колір, а при зменшенні значення спостерігається зсув у бік червоного кольору.
Щось на кшталт того, що показано нижче:
Наведений вище набір даних має значення від 1 до 100. Осередки виділяються в залежності від значення в ньому. 100 отримує зелений колір, 1 – червоний колір.
Ось кроки для створення теплових карток з використанням умовного форматування в Excel.
- Виберіть набір даних.
- Перейдіть на головну -> Умовне форматування -> Колірні шкали та виберіть одну із колірних схем.
Як тільки ви натиснете значок теплової карти, він застосує форматування до набору даних. Ви можете вибирати з кількох колірних градієнтів. Якщо вас не влаштовують існуючі параметри кольору, можна вибрати більше правил і вказати потрібний колір.
Примітка. Аналогічно можна застосувати набори Data Bard та Icon.
6. Виділіть решту рядків / стовпців.
Ви можете виділити альтернативні рядки, щоб підвищити зручність читання даних.
Вони називаються лініями зебри і можуть бути особливо корисними під час друку даних.
Тепер є два способи створити ці лінії зебри. Найшвидший спосіб – перетворити табличні дані в таблицю Excel. Він автоматично застосовував колір до рядів, що чергуються.Ви можете прочитати більше про це тут.
Інший спосіб – умовне форматування.
Припустимо, у вас є набір даних, як показано нижче:
Ось кроки, щоб виділити альтернативні рядки за допомогою умовного форматування Excel.
- Виберіть набір даних. У наведеному вище прикладі виберіть A2: C13 (без заголовка).
- Відкрийте діалогове вікно "Умовне форматування" ("Домашня сторінка" -> "Умовне форматування" -> "Нове правило"). [Поєднання клавіш – Alt + O + D].
- У діалоговому вікні виберіть «Використовувати формулу, щоб визначити, які осередки потрібно форматувати».
- Введіть таку формулу в полі в розділі "Редагувати опис правила":
= ISODD (РЯДОК ())
- Вищезгадана формула перевіряє всі комірки, і якщо номер РЯДКУ комірки непарний, вона повертає ІСТИНА.
- Вкажіть формат, який потрібно застосувати до порожніх осередків або осередків з помилками. Для цього натисніть кнопку «Форматувати».
- Натисніть кнопку ОК.
Ось і все! Альтернативні рядки у наборі даних будуть виділені.
Ви можете використовувати ту ж техніку у багатьох випадках. Все, що вам потрібно зробити, це використовувати відповідну формулу в умовному форматуванні.
- Виділіть парні парні рядки: = ОДИНИЦІ (РЯДОК ())
- Виділіть альтернативні рядки додавання: = ISODD (ROW())
- Виділіть кожний 3-й рядок: = MOD (ROW(), 3) = 0
7. Пошук та виділення даних за допомогою умовного форматування.
Це трохи просунуте використання умовного форматування. Це зробило б схожим на рок-зірку Excel.
Припустимо, у вас є набір даних, показаний нижче, з назвою продуктів, торговим представником та географічним розташуванням. Ідея полягає в тому, щоб ввести рядок у комірку C2, і якщо вона збігається з даними у будь-якому комірці (ах), вона має бути виділена. Щось на кшталт того, що показано нижче:
Ось кроки для створення цієї функції пошуку та виділення:
- Виберіть набір даних.
- Перейдіть на головну сторінку -> Умовне форматування -> Нове правило. (Поєднання клавіш - Alt + O + D).
- У діалоговому вікні «Нове правило форматування» виберіть параметр «Використовувати формулу для визначення комірок форматування».
- Введіть таку формулу в полі в розділі "Редагувати опис правила":
= І ($ C $ 2 ””, $ C $ 2 = B5)
- Вкажіть формат, який ви хочете застосувати до порожніх осередків або осередків з помилками. Для цього натисніть кнопку "Форматувати". Відкриється діалогове вікно «Формат осередків», у якому ви можете вказати формат.
- Натисніть кнопку ОК.
Ось і все! Тепер, коли ви вводите що-небудь в комірку C2 і натискаєте клавішу ВВЕДЕННЯ, він виділяє всі комірки, що збігаються.
Як це працює?
Формула, яка використовується в умовному форматуванні, оцінює всі осередки в наборі даних. Допустимо, ви вводите Японію в комірку C2. Тепер Excel оцінюватиме формулу для кожного осередку.
Формула поверне ІСТИНА для комірки при виконанні двох умов:
- Комірка C2 не порожня.
- Вміст комірки C2 точно відповідає вмісту комірки у наборі даних.
Отже, усі осередки, що містять текст Japan, будуть виділені.
Завантажте прикладний файл
Ви можете використовувати ту ж логіку для створення таких варіацій, як:
- Виділіть весь рядок замість комірки.
- Виділіть навіть якщо є частковий збіг.
- Виділяйте комірки/рядки у міру введення (динамічно) [Вам сподобається цей трюк :)].
Як видалити умовне форматування в Excel
Після застосування умовне форматування залишається на місці, якщо ви не видалите його вручну.
Оскільки він є нестабільним, це може призвести до повільної роботи книги Excel.
Щоб видалити умовне форматування:
- Виділіть комірки, з яких ви бажаєте видалити умовне форматування.
- Перейдіть на головну -> Умовне форматування -> Очистити правила -> Очистити правила з вибраних осередків.
- Якщо потрібно видалити умовне форматування з усього аркуша, виберіть «Очистити правила з усього аркуша».
Важливі відомості про умовне форматування в Excel
- Умовне форматування у volatile. Це може призвести до повільної роботи книги.
- Коли ви копіюєте комірки вставки, що містять умовне форматування, також копіюється умовне форматування.
- Якщо ви застосуєте кілька правил до одного і того ж набору осередків, усі правила залишаться активними.
