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

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

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

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

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

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

1. Перейдите в Застройщик вкладку нажмите Вставить > Тext Box (элемент управления ActiveX).
   Функции: Если Застройщик вкладка не отображается на ленте, вы можете включить ее, следуя инструкциям в этом руководстве: Как показать / отобразить вкладку разработчика в ленте Excel?
   
2. Курсор превратится в крест, а затем вам нужно будет перетащить курсор, чтобы нарисовать текстовое поле в том месте на листе, где вы хотите разместить текстовое поле. После рисования текстового поля отпустите мышь.
   
3. Щелкните правой кнопкой мыши текстовое поле и выберите Объекты из контекстного меню.
   
4. В Объекты панели свяжите текстовое поле с ячейкой, введя ссылку на ячейку в поле Связанная ячейка поле. Например, набрав "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. Перейдите в Застройщик вкладку нажмите Вставить > Тext Box (элемент управления ActiveX).
   Функции: Если Застройщик вкладка не отображается на ленте, вы можете включить ее, следуя инструкциям в этом руководстве: Как показать / отобразить вкладку разработчика в ленте Excel?
   
2. Курсор превратится в крест, а затем вам нужно будет перетащить курсор, чтобы нарисовать текстовое поле в том месте на листе, где вы хотите разместить текстовое поле. После рисования текстового поля отпустите мышь.
   
3. Щелкните правой кнопкой мыши текстовое поле и выберите Объекты из контекстного меню.
   
4. В Объекты панели свяжите текстовое поле с ячейкой, введя ссылку на ячейку в поле Связанная ячейка поле. Например, набрав "J3" гарантирует, что любые данные, введенные в текстовое поле, автоматически обновляются в ячейке J3, и наоборот.
   
5. Нажмите Режим проектирования под Застройщик вкладка, чтобы выйти из режима разработки.
   

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

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

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

      =$B3=$J$3

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

Результат

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

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

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

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

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

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

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

Статьи по теме