Создайте поле поиска в Excel – Пошаговое руководство

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

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

Легко создайте поле поиска с помощью функции ФИЛЬТР

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

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

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

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

Теперь текстовое поле позволяет вводить текст.

Шаг 2: Примените функцию ФИЛЬТР

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. Нажмите кнопку «ОК». См. скриншот:
      

Результат

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

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

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

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

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

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

1. Вернитесь к рабочему листу, содержащему набор данных, который вы хотите найти. Перейдите на вкладку «Разработчик», нажмите «Вставить» > «Комбинированное поле (Элемент управления ActiveX)».
   Совет: Если вкладка «Разработчик» не отображается на ленте, вы можете включить её, следуя инструкциям из этого руководства: Как показать/отобразить вкладку Разработчик в ленте Excel?
   
2. Курсор превратится в крестик, затем вам нужно перетащить курсор, чтобы нарисовать комбинированное поле в том месте рабочего листа, где вы хотите разместить поле поиска. После рисования комбинированного поля отпустите мышь.
   
3. Щелкните правой кнопкой мыши по комбинированному полю и выберите «Свойства» из контекстного меню.
   
4. В панели «Свойства»:
   1. Свяжите комбинированное поле с ячейкой, введя ссылку на ячейку в поле «LinkedCell». Здесь я ввожу «M2».
      Совет: Указание этого поля гарантирует, что любые данные, введенные в комбинированное поле, автоматически обновятся в ячейке 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 здесь.

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