Создание поля поиска в Excel — пошаговое руководство

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

• Легко создайте поле поиска с помощью функции ФИЛЬТР
  (доступно в Excel 2019 и более поздних версиях, Excel для Microsoft 365)
• Создайте поле поиска с использованием Использовать условное форматирование
  (доступно во всех версиях Excel)
• Создайте поле поиска с помощью комбинаций формул
  (доступно во всех версиях Excel)

Легко создайте поле поиска с помощью функции FILTER

• Эта функция автоматически обновляет результат при изменении данных.
• Функция ФИЛЬТР может возвращать любое количество результатов — от одной строки до тысяч, в зависимости от того, сколько записей в наборе данных соответствует заданным критериям.

Здесь я покажу, как с помощью функции ФИЛЬТР создать поле поиска в Excel.

Шаг 1: Вставка текстового поля и настройка свойств

1. Перейдите на вкладку «Разработчик», нажмите «Элементы управления» > «Текстовое поле (элемент управления ActiveX)».
   Совет: Если вкладка «Разработчик» не отображается на Лента, вы можете включить её, следуя инструкциям в этом руководстве:Как отобразить вкладку «Разработчик» в Лента Excel?
   
2. Курсор примет форму креста — перетащите его, чтобы нарисовать текстовое поле в нужном месте на листе. После создания текстового поля отпустите кнопку мыши.
   
3. Щелкните правой кнопкой мыши по текстовому полю и выберите в контекстном меню пункт «Свойства».
   
4. На панели «Свойства» свяжите текстовое поле с ячейкой, указав ссылку на неё в поле «LinkedCell». Например, ввод «J2» обеспечит автоматическую синхронизацию данных: всё, что вы введёте в текстовое поле, будет мгновенно отражаться в ячейке J2, и наоборот.
   
5. Нажмите «Режим конструктора» на вкладке «Разработчик», чтобы выйти из этого режима.
   

Теперь вы можете вводить текст в текстовое поле.

Шаг 2: Применение функции FILTER

1. Перед использованием функции ФИЛЬТР скопируйте исходную строку заголовков в новую область. Я разместил строку заголовков прямо под полем поиска.
   
2. Выберите ячейку под первым заголовком (например, I5 в данном примере), введите в неё следующую формулу и нажмите клавишу «Enter», чтобы получить результат.

   =FILTER(Sheet2!$A$5:$G$281,Sheet2!$B$5:$B$281=J2,"No data found")

   
   Как показано на приведённом выше снимке экрана, поскольку текстовое поле теперь не содержит данных, формула отображает результат «Данные не найдены» в ячейке I5.

Результат: Проверка поля поиска

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

Создание поля поиска с помощью Использовать условное форматирование

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

Шаг 1: Вставка текстового поля и настройка свойств

1. Перейдите на вкладку «Разработчик» и выберите «Вставить» > «Текстовое поле (элемент управления ActiveX)».
   Совет. Если вкладка «Разработчик» не отображается на ленте, вы можете включить её, следуя инструкциям из этого руководства:Как отобразить вкладку разработчика на ленте Excel?
   
2. Курсор примет форму креста; затем перетащите курсор, чтобы нарисовать текстовое поле в нужном месте на листе. После создания текстового поля отпустите кнопку мыши.
   
3. Щелкните правой кнопкой мыши текстовое поле и выберите в контекстном меню пункт «Свойства».
   
4. На панели «Свойства» свяжите текстовое поле с ячейкой, указав ссылку на неё в поле «LinkedCell». Например, если ввести «J3», все данные, введённые в текстовое поле, будут автоматически обновляться в ячейке J3 — и наоборот.
   
5. Нажмите «Режим конструктора» на вкладке «Разработчик», чтобы выйти из этого режима.
   

Теперь вы можете вводить текст в текстовое поле.

Шаг 2: Применение Использовать условное форматирование для поиска данных

1. Выделите весь диапазон данных, в котором необходимо выполнить поиск. В данном случае я выделяю диапазон A3:G279.
2. На вкладке «Главная» нажмите  «Использовать условное форматирование» > «Создать правило».
   
3. В диалоговом окне «Создание правила форматирования»:
   1. В параметрах «Выбор типа правила» выберите «Использовать формулу для определения форматируемых ячеек».
   2. Введите следующую формулу в поле «Форматировать значения, для которых формула возвращает ИСТИНУ».

      =$B3=$J$3

      Здесь «$B3» обозначает первую ячейку в столбце, который необходимо сопоставить с критериями поиска в Выберите диапазон, а «$J$3» — ячейка, связанная с полем поиска.
   3. Нажмите кнопку «Формат», чтобы задать Цвет заполнения для результатов поиска.
   4. Нажмите кнопку «OK». См. снимок экрана:
      

Результат

Теперь протестируем поле поиска: как только вы введёте имя клиента, все строки, содержащие этого клиента в столбце B, мгновенно выделятся заданным цветом заливки.

Создание поля поиска с помощью комбинаций формул

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

Шаг 1: Создание списка уникальных значений из столбца поиска

1. В данном случае я выделяю и копирую диапазон «B4:B281» в Новый лист.
2. После вставки диапазона на новый лист оставьте вставленные данные выделенными, перейдите на вкладку «Данные» и выберите «Удалить дубликаты».
   
3. В открывшемся диалоговом окне «Удалить дубликаты» нажмите кнопку «ОК».
   
4. Затем появится диалоговое окно Microsoft Excel с указанием количества удалённых дубликатов — нажмите «ОК».
   
5. После удаления дубликатов выделите все уникальные значения в списке, исключая заголовок, и присвойте этому диапазону имя, указав его в поле «Имя». В данном случае я назвал диапазон «Customer».
   

Шаг 2: Вставка поля со списком и настройка свойств

1. Вернитесь на лист с исходными данными, по которым нужно выполнить поиск. Перейдите на вкладку «Разработчик» и выберите «Вставить» > «Поле со списком (элемент управления ActiveX)».
   Совет. Если вкладка «Разработчик» не отображается на ленте, вы можете включить её, следуя инструкциям из этого руководства:Как отобразить вкладку разработчика на ленте Excel?
   
2. Курсор примет форму креста; затем перетащите курсор, чтобы нарисовать поле со списком в том месте на листе, где вы хотите разместить поле поиска. После создания поля со списком отпустите кнопку мыши.
   
3. Щелкните правой кнопкой мыши поле со списком и выберите в контекстном меню пункт «Свойства».
   
4. На панели «Свойства»:
   1. Свяжите поле со списком с ячейкой, указав ссылку на неё в поле «LinkedCell». В данном случае я ввожу «M2».
   2. В поле «ListFillRange» введите «Имя ячейки», указанное для уникального списка на шаге 1.
   3. Измените значение поля «MatchEntry» на «2 – fmMatchEntryNone».
   4. Закройте панель «Свойства».
      
5. Нажмите «Режим конструктора» на вкладке «Разработчик», чтобы выйти из этого режима.
   

Теперь вы можете выбрать любой элемент из выпадающего списка или ввести текст для поиска.

Шаг 3: Применение формул

1. Создайте три вспомогательных столбца рядом с исходным диапазоном данных. См. снимок экрана:
   
2. В ячейку H5 под заголовком первого вспомогательного столбца введите указанную ниже формулу и нажмите «Enter».

   =ROWS($B$5:B5)

   Здесь «B5» — это ячейка с именем первого клиента из столбца, по которому выполняется поиск.
   
3. Дважды щёлкните по правому нижнему углу ячейки с формулой — формула автоматически заполнит последующие ячейки.
   
4. В ячейку I5 под заголовком второго вспомогательного столбца введите следующую формулу и нажмите «Enter». Затем дважды щелкните по правому нижнему углу ячейки с формулой, чтобы автоматически заполнить ею все ячейки ниже.

   =IF(ISNUMBER(SEARCH($M$2,B5)),H5,"")

   Здесь M2 — это ячейка, связанная с полем со списком.
   
5. В ячейку (J5) под заголовком третьего вспомогательного столбца введите следующую формулу и нажмите «Enter». Затем дважды щёлкните по правому нижнему углу ячейки с формулой, чтобы автоматически заполнить этой формулой ячейки ниже.

   =IFERROR(SMALL($I$5:$I$281,H5),"") 

   
6. Скопируйте исходную строку заголовков в новую область. В данном случае я размещаю строку заголовков под полем поиска.
   
7. Выберите ячейку под первым заголовком (например, L5 в данном примере), введите в неё следующую формулу и нажмите клавишу Enter.

   =IFERROR(INDEX($A$5:$G$281,$J5,COLUMNS($L$4:L4)),"")

   Здесь «A5:G281» — это весь диапазон данных, который вы хотите отобразить в ячейке результата.
   
8. Выделите эту ячейку с формулой, перетащите маркер заполнения вправо, а затем вниз, чтобы применить формулу к соответствующим столбцам и строкам.
   
   Примечания:

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

Результат

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

Создание поля поиска в Excel значительно улучшает взаимодействие с данными, делая ваши таблицы более динамичными и удобными в использовании. Независимо от того, выбираете ли вы простоту функции ФИЛЬТР, визуальную поддержку условного форматирования или гибкость комбинаций формул — каждый метод открывает ценные возможности для эффективной работы с данными. Поэкспериментируйте с этими подходами, чтобы найти идеальное решение именно для ваших задач и сценариев! А если вы стремитесь раскрыть весь потенциал Excel, на нашем сайте вас ждёт множество полезных обучающих материалов.Узнайте больше советов и приёмов работы в Excel здесь.

Связанные статьи