Как выделить строки с определенным текстом в Excel

Обновлено: 21.11.2024

Условное форматирование обычно проверяет значение в одной ячейке и применяет форматирование к другим ячейкам. Отличным применением условного форматирования является выделение всей строки или нескольких строк на основе значения ячейки и условия, указанного в формуле.

Это очень полезно, потому что набор данных с огромным количеством ценности становится громоздким для анализа, просто читая данные. Итак, если мы выделим несколько строк на основе некоторых условий, пользователю станет легче делать выводы из набора данных. Например, в колледже некоторые студенты были занесены в черный список из-за какой-то незаконной деятельности. Таким образом, в записи Excel администратор может выделить строки, в которых присутствуют эти записи учащихся.

В этой статье мы рассмотрим, как выделить строки на основе значений ячеек, используя подходящий пример в реальном времени, показанный ниже:

Пример. Рассмотрим данные о сотрудниках компании. Следующая таблица состоит из данных о проектах, назначенных сотрудникам, их возрасте и идентификаторе. Сотрудник может работать над несколькими проектами. Пустая ячейка в проекте означает, что этому сотруднику не назначен ни один проект.

Выделение строк

<р>1. На основе совпадения текста:

Цель: выделить все строки, где имя сотрудника — «Сришти».

Шаги:

<р>1. В нашем случае выберите весь набор данных от A3 до D14.

<р>2. На вкладке «Главная» выберите «Условное форматирование». Откроется раскрывающееся меню.

<р>3. Выберите «Новое правило» в раскрывающемся списке. Откроется диалоговое окно.

<р>4. В диалоговом окне «Новое правило форматирования» выберите «Использовать формулу для определения форматируемых ячеек» в параметре «Выбор типа правила».

<р>5. В поле формулы напишите формулу:

Сначала формула проверяет, присутствует ли имя «Сришти» в ячейке B2. Теперь, поскольку ячейка B заблокирована, в следующий раз проверка будет выполняться из ячейки B3 и так далее, пока не будет выполнено условие.

<р>6. В поле «Предварительный просмотр» выберите «Формат», затем перейдите к «Заливка», затем выберите подходящий цвет для выделения и нажмите «ОК».

<р>7. Теперь нажмите OK, и строки будут выделены.

Выделенные строки

<р>2. Нетекстовое сопоставление на основе числа:

Цель: выделить все строки, в которых возраст меньше 25 лет.

Подход:

Повторите описанные выше шаги, как описано, и в формуле напишите:

Выделенные строки

<р>3. На основе условия ИЛИ/И:

ИЛИ, И используются, когда у нас есть несколько условий. Это логические операторы, которые работают со значением True.

И: если все условия ИСТИНА, И возвращает ИСТИНА.

ИЛИ: хотя бы одно из условий должно быть ИСТИНА, чтобы вернуть значение ИСТИНА.

Цель: выделить все строки сотрудников, которые работают над проектом 1 или над проектом 4.

Подробности проекта указаны в столбце D. Таким образом, формула будет выглядеть так:

Цель: Предположим, сотрудник Ришаб завершил проект P-3. Администратора просят выделить строку и вести учет завершенных проектов.

Название находится в столбце B, а сведения о проекте — в столбце D. Формула будет следующей:

Выделенная строка

<р>4. На основе любой пустой строки:

Цель: проверить, есть ли пустые строки. Если существуют, то выделите их.

Сведения о проекте приведены в столбце D. Мы будем использовать функцию СЧЁТЕСЛИ( ) для проверки количества пустых записей. Формула будет:

Приведенная выше формула проверяет все столбцы один за другим и определяет, есть ли хотя бы одна пустая строка. Если значение формулы больше нуля, будет выполнено выделение, иначе СЧЁТЕСЛИ вернёт значение ЛОЖЬ и выделение не будет выполнено.

<р>5. На основе нескольких условий, и каждое условие имеет разный цвет:

Цель. Предположим, компания хочет различать сотрудников по возрасту. Сотрудники, возраст которых превышает 25 лет, являются старшими сотрудниками, а чей возраст превышает 20, но меньше или равен 25 годам, — младшими разработчиками или стажерами. Итак, администратора просят выделить эти две категории разными цветами.

Реализация:

В поле Формула напишите формулу:

При этом будут выделены все строки, в которых возраст превышает 25 лет.

Снова в поле Формула напишите формулу:

При этом будут выделены все строки больше 20.Это фактически изменит цвет строк, где возраст больше 25. Поскольку, если число больше 20, то оно определенно больше 25. Таким образом, все ячейки, имеющие возраст больше 20, будут выделены одинаково цвет.

  • Отмените предыдущие шаги, нажав CTRL+Z.
  • Выберите весь набор данных.
  • Перейдите к разделу «Условное форматирование», а затем «Управление правилами».

Необходимо изменить порядок условия. Верхнее условие будет иметь больший приоритет, чем нижнее. Итак, нам нужно переместить второе условие в начало первого с помощью значка вверх после выбора условия.

Можно заметить, что строки теперь разделены на две категории: желтый цвет предназначен для сотрудников старшего уровня, а зеленый цвет — для младших сотрудников и стажеров в компании в зависимости от их возраста.

Используйте инструмент "Быстрый анализ" для условного форматирования ячеек в диапазоне с повторяющимся текстом, уникальным текстом и текстом, совпадающим с указанным вами текстом. Вы даже можете условно отформатировать строку на основе текста в одной из ячеек в строке.

Применить условное форматирование на основе текста в ячейке

Выберите ячейки, к которым вы хотите применить условное форматирование. Щелкните первую ячейку в диапазоне и перетащите курсор к последней ячейке.

Нажмите ГЛАВНАЯ > Условное форматирование > Правила выделения ячеек > Содержащий текст. В поле "Текст, который содержит" слева введите текст, который нужно выделить.

Выберите формат цвета для текста и нажмите OK.

Хотите еще?

В первом видео курса мы рассмотрели, как условно форматировать ячейки в диапазоне, содержащем текст «Нефть», с помощью быстрого анализа.

Вы также можете использовать Быстрый анализ для условного форматирования ячеек в диапазоне, содержащем повторяющийся текст, уникальный текст и текст, совпадающий с указанным вами текстом.

Но что, если вы хотите условно отформатировать строку на основе текста в одной из ячеек в строке?

В этом примере я хочу, чтобы строка была закрашена серым цветом, если для параметра "Прекращено" для строки задано значение "Да".

Для этого выберите ячейки, которые вы хотите условно отформатировать. Обратите внимание, что A2 — активная ячейка; нам это понадобится позже.

Нажмите «Условное форматирование», а затем нажмите «Новое правило». Выберите Использовать формулу, чтобы определить ячейки для форматирования.

Поскольку активной ячейкой является A2, в поле правила нам нужно ввести формулу, которая действительна для строки 2 и будет корректно применяться ко всем остальным строкам.

Для этого введите =$C2="Да". Мы используем абсолютную ссылку для столбца C, $C, так что условное форматирование для каждой строки оценивает значение в столбце C для этой строки.

И мы заключаем Да в кавычки. Кавычки гарантируют, что Excel оценивает слово «Да» как текст.

Затем мы нажимаем «Формат», выбираем нужный цвет заливки, нажимаем «ОК», а затем снова нажимаем «ОК».

И строки с продуктами, которые сняты с производства, окрашены серым цветом.

Если вы предпочитаете читать письменные инструкции, ниже приведен учебник.

Условное форматирование позволяет форматировать ячейку (или диапазон ячеек) на основе содержащегося в ней значения.

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

В качестве примера ниже у меня есть набор данных, в котором я выделил все строки, где имя торгового представителя — Боб.

В этом руководстве я покажу вам, как выделять строки на основе значения ячейки, используя условное форматирование и различные критерии.

Нажмите здесь, чтобы загрузить файл примера и следовать инструкциям.

Это руководство охватывает:

Выделение строк на основе текстовых критериев

Предположим, у вас есть набор данных, как показано ниже, и вы хотите выделить все записи, в которых имя торгового представителя — Боб.

Вот как это сделать:

При этом будут выделены все строки, в которых имя торгового представителя "Боб".

Нажмите здесь, чтобы загрузить файл примера и следовать инструкциям.

Как это работает?

Условное форматирование проверяет каждую ячейку на соответствие заданному нами условию, то есть =$C2="Боб"

Поэтому, когда он анализирует каждую ячейку в строке A2, он проверяет, имеет ли ячейка C2 имя Bob или нет. Если это так, эта ячейка выделяется, иначе нет.

Обратите внимание, что трюк здесь заключается в использовании знака доллара ($) перед алфавитом столбца ($C1). Сделав это, мы заблокировали столбец так, чтобы он всегда был C. Таким образом, даже когда ячейка A2 проверяется на наличие формулы, она будет проверять C2, а когда A3 проверяется на условие, она проверяет C3.

Это позволяет выделить всю строку с помощью условного форматирования.

Выделение строк на основе числовых критериев

В приведенном выше примере мы видели, как проверить имя и выделить всю строку.

Тот же метод можно использовать для проверки числовых значений и выделения строк в зависимости от условия.

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

Вот как это сделать:

  1. Выберите весь набор данных (в этом примере A2:F17).
  2. Перейдите на вкладку "Главная".
  3. В группе "Стили" нажмите "Условное форматирование".
  4. Нажмите "Новые правила".
  5. В диалоговом окне "Новое правило форматирования" нажмите "Использовать формулу для определения форматируемых ячеек".
  6. В поле формулы введите следующую формулу: =$D2>=15
  7. Нажмите кнопку «Формат». В открывшемся диалоговом окне задайте цвет, которым вы хотите выделить строку.
  8. Нажмите "ОК".

    9. При этом будут выделены все строки, в которых количество больше или равно 15.

    Аналогичным образом мы также можем использовать это, чтобы иметь критерии для даты.

    Например, если вы хотите выделить все строки, в которых дата находится после 10 июля 2018 года, можно использовать следующую формулу даты:

    Выделение строк на основе нескольких критериев (И/ИЛИ)

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

    Например, если вы хотите выделить все строки, в которых имя торгового представителя "Боб" и количество больше 10, вы можете сделать это, выполнив следующие действия:

    1. Выберите весь набор данных (в этом примере A2:F17).
    2. Перейдите на вкладку "Главная".
    3. В группе "Стили" нажмите "Условное форматирование".
    4. Нажмите "Новые правила".
    5. В диалоговом окне "Новое правило форматирования" нажмите "Использовать формулу для определения форматируемых ячеек".
    6. В поле формулы введите следующую формулу: =И($C2="Боб",$D2>10)
    7. Нажмите кнопку «Формат». В открывшемся диалоговом окне задайте цвет, которым вы хотите выделить строку.
    8. Нажмите "ОК".

      9. В этом примере выделяются только те строки, в которых выполняются оба условия (это делается с помощью формулы AND).

      Точно так же можно использовать условие ИЛИ. Например, если вы хотите выделить строки, где торговым представителем является Боб или количество больше 15, вы можете использовать следующую формулу:

      Нажмите здесь, чтобы загрузить файл примера и следовать инструкциям.

      Выделение строк разным цветом в зависимости от нескольких условий

      Иногда может потребоваться выделить строки цветом в зависимости от условия.

      Например, вы можете выделить все строки, в которых количество больше 20, зеленым цветом, а где количество больше 15 (но меньше 20) — оранжевым.

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

      Вот как это сделать:

      Вышеуказанные шаги сделают все строки с количеством больше 20 зеленым цветом, а с количеством больше 15 (но меньше 20 – оранжевым).

      Порядок правил:

      При использовании нескольких условий важно убедиться, что порядок условий правильный.

      В приведенном выше примере условие зеленого цвета выше условия оранжевого цвета.

      Если наоборот, то все строки будут окрашены только в оранжевый цвет.

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

      Вы можете изменить порядок условий, используя кнопки Вверх/Вниз.

      Нажмите здесь, чтобы загрузить файл примера и следовать инструкциям.

      Выделить строки, в которых любая ячейка пуста

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

      Вот как это сделать:

      1. Выберите весь набор данных (в этом примере A2:F17).
      2. Перейдите на вкладку "Главная".
      3. В группе "Стили" нажмите "Условное форматирование".
      4. Нажмите "Новые правила".
      5. В диалоговом окне "Новое правило форматирования" нажмите "Использовать формулу для определения форматируемых ячеек".
      6. В поле формулы введите следующую формулу: =СЧЁТЕСЛИ($A2:$F2",")>0
      7. Нажмите кнопку «Формат». В открывшемся диалоговом окне установите оранжевый цвет.
      8. Нажмите "ОК".

        9. Приведенная выше формула подсчитывает количество пустых ячеек. Если результат больше 0, это означает, что в этой строке есть пустые ячейки.

        Если какая-либо из ячеек пуста, выделяется вся строка.

        По теме: Прочитайте это руководство, если вы хотите выделить только пустые ячейки.

        Выделение строк на основе раскрывающегося списка

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

        В этой части руководства я покажу вам, как сделать его динамическим (чтобы вы могли ввести условие в ячейку Excel, и оно автоматически выделило строки на его основе).

        Ниже приведен пример, когда я выбираю имя из раскрывающегося списка, и все строки с этим именем выделяются:

        Вот шаги, чтобы создать это:

        1. Создайте раскрывающийся список в ячейке A2. Здесь я использовал имена торговых представителей для создания выпадающего списка.Вот подробное руководство о том, как создать раскрывающийся список в Excel.
        2. Выберите весь набор данных (в данном примере C2:H17).
        3. Перейдите на вкладку "Главная".
        4. В группе "Стили" нажмите "Условное форматирование".
        5. Нажмите "Новые правила".
        6. В диалоговом окне "Новое правило форматирования" нажмите "Использовать формулу для определения форматируемых ячеек".
        7. В поле формулы введите следующую формулу: =$E2=$A$2
        8. Нажмите кнопку «Формат». В открывшемся диалоговом окне установите оранжевый цвет.
        9. Нажмите "ОК".

          10. Теперь, когда вы выбираете любое имя в раскрывающемся списке, автоматически выделяются строки, в которых имя совпадает с именем, которое вы выбрали в раскрывающемся списке.

          Хотите узнать больше о том, как искать и выделять в Excel? Посмотрите видео ниже.

          <Р>