Посмотреть видео - выделение строк на основе значений ячеек в Excel
Если вы предпочитаете читать письменные инструкции, ниже вы найдете руководство.
Условное форматирование позволяет форматировать ячейку (или диапазон ячеек) на основе значения в ней.
Но иногда, вместо того, чтобы просто выделить ячейку, вы можете выделить всю строку (или столбец) на основе значения в одной ячейке.
Чтобы дать вам пример, ниже у меня есть набор данных, в котором я выделил все строки, в которых имя торгового представителя - Боб.
В этом руководстве я покажу вам, как выделять строки на основе значения ячейки, используя условное форматирование с использованием разных критериев.
кликните сюда чтобы загрузить файл примера и следовать за ним.
Выделение строк на основе критериев текста
Предположим, у вас есть набор данных, как показано ниже, и вы хотите выделить все записи, в которых торговым представителем является Боб.
Вот как это сделать:
- Выберите весь набор данных (A2: F17 в этом примере).
- Щелкните вкладку "Главная".
- В группе «Стили» щелкните «Условное форматирование».
- Щелкните «Новые правила».
- В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
- В поле формулы введите следующую формулу: = $ C2 = "Боб"
- Нажмите кнопку «Форматировать».
- В открывшемся диалоговом окне установите цвет, которым должна быть выделена строка.
- Щелкните ОК.
Это выделит все строки, в которых имя торгового представителя - «Боб».
кликните сюда чтобы загрузить файл примера и следовать за ним.
Как это работает?
Условное форматирование проверяет каждую ячейку на соответствие указанному нами условию, а именно: = $ C2 = "Боб"
Поэтому, когда он анализирует каждую ячейку в строке A2, он проверяет, имеет ли ячейка C2 имя Боб или нет. Если это так, эта ячейка выделяется, в противном случае - нет.
Обратите внимание, что здесь хитрость заключается в том, чтобы использовать знак доллара ($) перед алфавитом столбца ($ C1). Сделав это, мы заблокировали столбец, чтобы он всегда был C. Таким образом, даже когда ячейка A2 проверяется на наличие формулы, она проверяет C2, а когда A3 проверяется на условие, она проверяет C3.
Это позволяет выделить всю строку условным форматированием.
Выделите строки на основе числовых критериев
В приведенном выше примере мы увидели, как проверить имя и выделить всю строку.
Мы можем использовать тот же метод для проверки числовых значений и выделения строк в зависимости от условия.
Предположим, у меня есть те же данные (как показано ниже), и я хочу выделить все строки, в которых количество больше 15.
Вот как это сделать:
- Выберите весь набор данных (A2: F17 в этом примере).
- Щелкните вкладку "Главная".
- В группе «Стили» щелкните «Условное форматирование».
- Щелкните «Новые правила».
- В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
- В поле формулы введите следующую формулу: = $ D2> = 15
- Нажмите кнопку «Форматировать». В открывшемся диалоговом окне установите цвет, которым должна быть выделена строка.
- Щелкните ОК.
Это выделит все строки, в которых количество больше или равно 15.
Точно так же мы также можем использовать это, чтобы иметь критерии для даты.
Например, если вы хотите выделить все строки с датой после 10 июля 2021-2022, вы можете использовать формулу даты ниже:
= $ A2> ДАТА (2018,7,10)
Выделение строк на основе нескольких критериев (И / ИЛИ)
Вы также можете использовать несколько критериев для выделения строк с помощью условного форматирования.
Например, если вы хотите выделить все строки, в которых имя торгового представителя - «Боб», а количество больше 10, вы можете сделать это, выполнив следующие действия:
- Выберите весь набор данных (A2: F17 в этом примере).
- Щелкните вкладку "Главная".
- В группе «Стили» щелкните «Условное форматирование».
- Щелкните «Новые правила».
- В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
- В поле формулы введите следующую формулу: = И ($ C2 = "Боб", $ D2> 10)
- Нажмите кнопку «Форматировать». В открывшемся диалоговом окне установите цвет, которым должна быть выделена строка.
- Щелкните ОК.
В этом примере выделяются только те строки, где выполняются оба условия (это делается с помощью формулы И).
Точно так же вы можете использовать условие ИЛИ. Например, если вы хотите выделить строки, в которых либо торговым представителем является Боб, либо количество больше 15, вы можете использовать следующую формулу:
= ИЛИ ($ C2 = "Боб", $ D2> 15)
кликните сюда чтобы загрузить файл примера и следовать за ним.
Выделите строки разным цветом в зависимости от нескольких условий
Иногда вам может потребоваться выделить строки цветом в зависимости от условия.
Например, вы можете выделить все строки, в которых количество больше 20, зеленым, а где количество больше 15 (но меньше 20) - оранжевым.
Для этого нужно создать два правила условного форматирования и установить приоритет.
Вот как это сделать:
- Выберите весь набор данных (A2: F17 в этом примере).
- Щелкните вкладку "Главная".
- В группе «Стили» щелкните «Условное форматирование».
- Щелкните «Новые правила».
- В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
- В поле формулы введите следующую формулу: = $ D2> 15
- Нажмите кнопку «Форматировать». В открывшемся диалоговом окне установите оранжевый цвет.
- Щелкните ОК.
- В диалоговом окне «Диспетчер правил условного форматирования» нажмите «Новое правило».
- В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
- В поле формулы введите следующую формулу: = $ D2> 20
- Нажмите кнопку «Форматировать». В открывшемся диалоговом окне установите цвет на зеленый.
- Щелкните ОК.
- Нажмите Применить (или ОК).
Вышеупомянутые шаги сделают все строки с количеством больше 20 зеленым, а строки с количеством больше 15 (но меньше 20 - оранжевым).
Понимание порядка правил:
При использовании нескольких условий важно убедиться, что порядок условий правильный.
В приведенном выше примере условие зеленого цвета выше условия оранжевого цвета.
В противном случае все строки будут окрашены только в оранжевый цвет.
Почему?
Потому что строка, в которой количество больше 20 (скажем, 23), удовлетворяет обоим нашим условиям (= $ D2> 15 и = $ D2> 20). А поскольку оранжевое состояние находится наверху, ему отдается предпочтение.
Вы можете изменить порядок условий, используя кнопки «Вверх» / «Вниз».
кликните сюда чтобы загрузить файл примера и следовать за ним.
Выделите строки, где любая ячейка пуста
Если вы хотите выделить все строки, в которых любая из ячеек пуста, вам необходимо проверить каждую ячейку, используя условное форматирование.
Вот как это сделать:
- Выберите весь набор данных (A2: F17 в этом примере).
- Щелкните вкладку "Главная".
- В группе «Стили» щелкните «Условное форматирование».
- Щелкните «Новые правила».
- В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
- В поле формулы введите следующую формулу: = СЧЁТЕСЛИ ($ A2: $ F2, ””)> 0
- Нажмите кнопку «Форматировать». В открывшемся диалоговом окне установите оранжевый цвет.
- Щелкните ОК.
Приведенная выше формула подсчитывает количество пустых ячеек. Если результат больше 0, это означает, что в этой строке есть пустые ячейки.
Если какая-либо из ячеек пуста, выделяется вся строка.
Выделение строк на основе раскрывающегося списка
В рассмотренных примерах все условия были указаны в диалоговом окне условного форматирования.
В этой части руководства я покажу вам, как сделать его динамическим (чтобы вы могли ввести условие в ячейку в Excel, и оно автоматически выделяло строки на его основе).
Ниже приведен пример, в котором я выбираю имя из раскрывающегося списка, и все строки с этим именем выделяются:
Вот шаги, чтобы создать это:
- Создайте раскрывающийся список в ячейке A2. Здесь я использовал имена торговых представителей для создания раскрывающегося списка. Вот подробное руководство по созданию раскрывающегося списка в Excel.
- Выберите весь набор данных (в этом примере C2: H17).
- Щелкните вкладку "Главная".
- В группе «Стили» щелкните «Условное форматирование».
- Щелкните «Новые правила».
- В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
- В поле формулы введите следующую формулу: = $ E2 = $ A $ 2
- Нажмите кнопку «Форматировать». В открывшемся диалоговом окне установите оранжевый цвет.
- Щелкните ОК.
Теперь, когда вы выбираете любое имя из раскрывающегося списка, оно автоматически выделяет строки, в которых имя совпадает с именем, которое вы выбрали из раскрывающегося списка.
Хотите узнать больше о том, как искать и выделять в Excel? Посмотрите видео ниже.
