Виділити рядки на основі значення комірки в Excel (умовне форматування)

Перегляд відео - виділення рядків на основі значень комірок у Excel

Якщо ви віддаєте перевагу читанню письмових інструкцій, нижче наведено підручник.

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

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

Щоб навести вам приклад, нижче у мене є набір даних, де я виділив усі рядки, де ім’я торгового представника - Боб.

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

Натисніть тут завантажити файл прикладу та продовжити.

Виділити рядки на основі текстових критеріїв

Припустимо, у вас є набір даних, як показано нижче, і ви хочете виділити всі записи, де ім’я торгового представника - Боб.

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

1. Виберіть весь набір даних (A2: F17 у цьому прикладі).
2. Натисніть вкладку Домашня сторінка.
3. У групі Стилі натисніть Умовне форматування.
4. Натисніть «Нові правила».
5. У діалоговому вікні "Нове правило форматування" натисніть "Використовувати формулу, щоб визначити, які клітинки відформатувати".
6. У поле формули введіть таку формулу: = $ C2 = "Боб"
7. Натисніть кнопку «Формат».
8. У діалоговому вікні, що відкриється, встановіть колір, яким потрібно виділити рядок.
9. Натисніть OK.

Це виділить усі рядки, де ім’я торгового представника - «Боб».

Натисніть тут завантажити файл прикладу та продовжити.

Як це працює?

Умовне форматування перевіряє кожну клітинку на умови, які ми вказали, тобто = $ C2 = "Боб"

Тож під час аналізу кожної комірки рядка А2 вона перевірить, чи має клітинка С2 ім’я Боб чи ні. Якщо це так, ця клітинка буде виділена, інакше - ні.

Зауважте, що тут хитрість полягає у використанні знака долара ($) перед алфавітом стовпця ($ C1). Зробивши це, ми заблокували стовпець так, щоб він завжди був C. Отже, навіть коли клітинка A2 перевіряється на формулу, вона перевірятиме C2, а коли A3 перевірятиме стан, перевірятиме C3.

Це дозволяє виділити весь рядок умовним форматуванням.

Виділити рядки на основі кількісних критеріїв

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

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

Припустимо, у мене є ті ж дані (як показано нижче), і я хочу виділити всі рядки, де кількість більше 15.

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

1. Виберіть весь набір даних (A2: F17 у цьому прикладі).
2. Натисніть вкладку Домашня сторінка.
3. У групі Стилі натисніть Умовне форматування.
4. Натисніть «Нові правила».
5. У діалоговому вікні "Нове правило форматування" натисніть "Використовувати формулу, щоб визначити, які клітинки відформатувати".
6. У поле формули введіть таку формулу: = $ D2> = 15
7. Натисніть кнопку «Формат». У діалоговому вікні, що відкриється, встановіть колір, яким потрібно виділити рядок.
8. Натисніть OK.

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

Аналогічно, ми також можемо використовувати це, щоб мати критерії для дати.

Наприклад, якщо ви хочете виділити всі рядки, де дата після 10 липня 2021-2022 років, ви можете скористатися формулою дати нижче:

= $ A2> ДАТА (2018,7,10)

Виділити рядки на основі кількох критеріїв (І/АБО)

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

Наприклад, якщо ви хочете виділити всі рядки, де ім’я торгового представника - «Боб», а кількість - більше 10, ви можете зробити це, виконавши такі дії:

1. Виберіть весь набір даних (A2: F17 у цьому прикладі).
2. Натисніть вкладку Домашня сторінка.
3. У групі Стилі натисніть Умовне форматування.
4. Натисніть «Нові правила».
5. У діалоговому вікні "Нове правило форматування" натисніть "Використовувати формулу, щоб визначити, які клітинки відформатувати".
6. У поле формули введіть таку формулу: = І ($ C2 = "Боб", $ D2> 10)
7. Натисніть кнопку «Формат». У діалоговому вікні, що відкриється, встановіть колір, яким потрібно виділити рядок.
8. Натисніть OK.

У цьому прикладі виділяються лише ті рядки, де виконуються обидві умови (це робиться за допомогою формули І).

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

= АБО ($ C2 = "Боб", $ D2> 15)

Натисніть тут завантажити файл прикладу та продовжити.

Виділіть рядки різними кольорами на основі кількох умов

Іноді ви можете виділити рядки кольором залежно від умови.

Наприклад, ви можете виділити всі рядки, де кількість більше 20 зеленим кольором і де кількість більше 15 (але менше 20) оранжевим кольором.

Для цього вам потрібно створити два правила умовного форматування та встановити пріоритет.

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

1. Виберіть весь набір даних (A2: F17 у цьому прикладі).
2. Натисніть вкладку Домашня сторінка.
3. У групі Стилі натисніть Умовне форматування.
4. Натисніть «Нові правила».
5. У діалоговому вікні "Нове правило форматування" натисніть "Використовувати формулу, щоб визначити, які клітинки відформатувати".
6. У поле формули введіть таку формулу: = $ D2> 15
7. Натисніть кнопку «Формат». У діалоговому вікні, що відкриється, встановіть колір на помаранчевий.
8. Натисніть OK.
9. У діалоговому вікні "Менеджер правил умовного форматування" натисніть "Нове правило".
10. У діалоговому вікні "Нове правило форматування" натисніть "Використовувати формулу, щоб визначити, які клітинки відформатувати".
11. У поле формули введіть таку формулу: = $ D2> 20
12. Натисніть кнопку «Формат». У діалоговому вікні, що відкриється, встановіть колір на зелений.
13. Натисніть OK.
14. Натисніть Застосувати (або ОК).

Вищезазначені кроки зробили б усі рядки, кількість яких перевищує 20 у зеленому кольорі, і ті, у яких більше 15 (але менше ніж 20 у помаранчевому).

Розуміння порядку правил:

При використанні кількох умов важливо переконатися, що порядок умов правильний.

У наведеному вище прикладі умова зеленого кольору вище умови помаранчевого кольору.

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

Чому?

Тому що рядок, де кількість більше 20 (скажімо 23), задовольняє нашим умовам (= $ D2> 15 і = $ D2> 20). А оскільки помаранчевий стан у верхній частині, він отримує перевагу.

Ви можете змінити порядок умов за допомогою кнопок Перемістити вгору/вниз.

Натисніть тут завантажити файл прикладу та продовжити.

Виділіть рядки, де будь -яка клітинка порожня

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

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

1. Виберіть весь набір даних (A2: F17 у цьому прикладі).
2. Натисніть вкладку Домашня сторінка.
3. У групі Стилі натисніть Умовне форматування.
4. Натисніть «Нові правила».
5. У діалоговому вікні "Нове правило форматування" натисніть "Використовувати формулу, щоб визначити, які клітинки відформатувати".
6. У поле формули введіть таку формулу: = COUNTIF ($ A2: $ F2, ””)> 0
7. Натисніть кнопку «Формат». У діалоговому вікні, що відкриється, встановіть колір на помаранчевий.
8. Натисніть OK.

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

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

Виділити рядки на основі випадаючого вибору

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

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

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

Ось кроки, щоб створити це:

1. Створіть розкривний список у комірці А2. Тут я використав назви торгового представника для створення розкривного списку. Ось докладний посібник про те, як створити розкривний список у Excel.
2. Виберіть весь набір даних (C2: H17 у цьому прикладі).
3. Натисніть вкладку Домашня сторінка.
4. У групі Стилі натисніть Умовне форматування.
5. Натисніть «Нові правила».
6. У діалоговому вікні "Нове правило форматування" натисніть "Використовувати формулу, щоб визначити, які клітинки відформатувати".
7. У поле формули введіть таку формулу: = $ E2 = $ A $ 2
8. Натисніть кнопку «Формат». У діалоговому вікні, що відкриється, встановіть колір на помаранчевий.
9. Натисніть OK.

Тепер, коли ви вибираєте будь-яке ім’я з розкривного списку, воно автоматично виділятиме рядки, де ім’я таке саме, яке ви вибрали зі спадного меню.

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