Найти и выделить в Excel

Обновлено: 20.11.2024

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

Хотя в Excel нет прямого способа сделать это, вы можете создать функцию поиска с помощью условного форматирования.

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

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

Что-то вроде того, как показано ниже (когда я ввожу название элемента в ячейку B2 и нажимаю Enter, выделяется вся строка):

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

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

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

Поиск и выделение соответствующих ячеек

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

Как показано ниже:

Вот шаги для поиска и выделения всех ячеек с совпадающим текстом:

Теперь введите что-нибудь в ячейку B1 и нажмите Enter. Будут выделены совпадающие ячейки в наборе данных, содержащие ключевое слово в ячейке B1.

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

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

В приведенном выше примере мы проверяем каждую ячейку по формуле =A4=$B$1

Условное форматирование проверяет каждую ячейку и проверяет, совпадает ли содержимое ячейки с содержимым ячейки B1. Если это то же самое, формула возвращает ИСТИНА, и ячейка выделяется. Если это не то же самое, формула возвращает FALSE и ничего не происходит.

Поиск и выделение строк с совпадающими данными

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

Ниже приведен пример, в котором выделяется вся строка, если тип продукта совпадает с типом в ячейке B1.

Вот шаги для поиска и выделения всей строки:

  1. Выберите набор данных, к которому вы хотите применить условное форматирование (A4:F19 в этом примере).
  2. Перейдите на вкладку "Главная".
  3. В группе "Стили" нажмите "Условное форматирование".
  4. В раскрывающемся списке выберите Новое правило.
  5. В диалоговом окне "Новое правило форматирования" выберите параметр "Использовать формулу для определения форматируемых ячеек".
  6. Введите следующую формулу: =$B4=$B$1
  7. Нажмите кнопку "Форматировать...".
  8. Укажите форматирование (чтобы выделить ячейки, соответствующие искомому ключевому слову).
  9. Нажмите "ОК".

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

    Обратите внимание, что при этом будет проверяться только столбец элементов. Если вы введете здесь имя торгового представителя, это не сработает. Если вы хотите, чтобы это работало для имени торгового представителя, вам нужно изменить формулу на =$C4=$B$1

    Примечание. Причина, по которой выделяется вся строка, а не только соответствующая ячейка, заключается в том, что мы использовали знак $ перед ссылкой на столбец ($B4). Теперь, когда условное форматирование анализирует ячейки в строке, оно проверяет, равно ли значение в столбце B этой строки значению в ячейке B1. Таким образом, даже когда он анализирует A4, B4 или C4 и т. д., он проверяет только значение B4 (поскольку мы заблокировали столбец B с помощью знака доллара).

    Поиск и выделение строк (на основе частичного совпадения)

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

    Например, если у вас есть такие элементы, как "Белая доска", "Зеленая доска" и "Серая доска", и вы хотите выделить их все по слову "Доска", вы можете сделать это с помощью функции ПОИСК.

    Как показано ниже:

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

    1. Выберите набор данных, к которому вы хотите применить условное форматирование (A4:F19 в этом примере).
    2. Перейдите на вкладку "Главная".
    3. В группе "Стили" нажмите "Условное форматирование".
    4. В раскрывающемся списке выберите Новое правило.
    5. В диалоговом окне "Новое правило форматирования" выберите параметр "Использовать формулу для определения форматируемых ячеек".
    6. Введите следующую формулу: =И($B$1<>””,ЕЧИСЛО(ПОИСК($B$1,$B4)))
    7. Нажмите кнопку "Форматировать...".
    8. Укажите форматирование (чтобы выделить ячейки, соответствующие искомому ключевому слову).
    9. Нажмите "ОК".

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

        Функция ищет строку поиска/ключевое слово во всех ячейках подряд. Он возвращает ошибку, если ключевое слово для поиска не найдено, и возвращает число, если находит совпадение. Функция преобразует ошибку в FALSE, а числовые значения в TRUE. функция проверяет наличие дополнительного условия – ячейка C2 не должна быть пустой.

      Теперь всякий раз, когда вы вводите ключевое слово в ячейку B1 и нажимаете Enter, выделяются все строки, в которых есть ячейки, содержащие это ключевое слово.

      Дополнительный совет: если вы хотите сделать поиск чувствительным к регистру, используйте функцию НАЙТИ вместо ПОИСК.

      Функциональность динамического поиска и выделения (подсветка по мере ввода)

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

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

      Как показано ниже:

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

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

      <Р>