✅ Поле поиска с динамическим фильтром Excel (извлекайте данные по мере ввода)

Содержание

Создание окна поиска с динамическим фильтром Excel

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

Что-то вроде того, что показано ниже:

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

Посмотреть видео - Создание окна поиска с динамическим фильтром Excel

Создание окна поиска с динамическим фильтром Excel

Этот динамический фильтр Excel можно создать в 3 этапа:

Получение уникального списка предметов (в данном случае стран). Это будет использоваться при создании раскрывающегося списка.
Создание поля поиска. Здесь я использовал поле со списком (элемент управления ActiveX).
Установка данных. Здесь я бы использовал три вспомогательных столбца с формулами для извлечения совпадающих данных.

Вот как выглядят необработанные данные:

ПОЛЕЗНЫЙ СОВЕТ: почти всегда полезно преобразовать данные в таблицу Excel. Вы можете сделать это, выбрав любую ячейку в наборе данных и используя сочетание клавиш Control + T.

Шаг 1 - Получение уникального списка предметов

Выберите все страны и вставьте его в новый лист.
Выберите список стран -> Перейти к данным -> Удалить дубликаты.
В диалоговом окне «Удалить дубликаты» выберите столбец, в котором есть список, и нажмите «ОК». Это удалит дубликаты и даст вам уникальный список, как показано ниже:
Еще один дополнительный шаг - создать именованный диапазон для этого уникального списка. Сделать это:
- Перейдите на вкладку "Формула" -> "Определить имя"
- В диалоговом окне определения имени:
  - Имя: CountryList
  - Объем: Рабочая тетрадь
  - Относится к: = UniqueList! $ A $ 2: $ A $ 9 (у меня есть список на отдельной вкладке с именем UniqueList в A2: A9. Вы можете ссылаться на него, где бы ни находился ваш уникальный список)

ПРИМЕЧАНИЕ. Если вы используете метод «Удалить дубликаты» и расширяете свои данные, добавляя больше записей и новые страны, вам придется повторить этот шаг еще раз. Как вариант, вы также можете составить формулу, чтобы сделать этот процесс динамичным.

Шаг 2 - Создание окна поиска с динамическим фильтром Excel

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

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

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

Перейдите на вкладку разработчика -> Элементы управления -> Вставить -> Элементы управления ActiveX -> Поле со списком (элементы управления ActiveX).
- Если у вас нет видимой вкладки разработчика, вот шаги, чтобы включить ее.
Щелкните в любом месте листа. Он вставит поле со списком.
Щелкните правой кнопкой мыши поле со списком и выберите «Свойства».
В окне свойств внесите следующие изменения:
- Связанная ячейка: K2 (вы можете выбрать любую ячейку, в которой вы хотите отображать входные значения. Мы будем использовать эту ячейку при настройке данных).
- ListFillRange: CountryList (это именованный диапазон, который мы создали на шаге 1. Это покажет все страны в раскрывающемся списке).
- MatchEntry: 2-fmMatchEntryNone (это гарантирует, что слово не будет автоматически завершено при вводе)
Выбрав поле со списком, перейдите на вкладку разработчика -> Элементы управления -> щелкните режим дизайна (это выведет вас из режима дизайна, и теперь вы можете ввести что угодно в поле со списком. Теперь все, что вы вводите, будет отражено в ячейке K2 в настоящее время)

Шаг 3 - Установка данных

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

Столбец помощника 1: Введите серийный номер для всех записей (в данном случае 20). Для этого вы можете использовать формулу ROWS ().

Столбец помощника 2: Во вспомогательном столбце 2 мы проверяем, соответствует ли текст, введенный в поле поиска, тексту в ячейках столбца страны.

Это можно сделать с помощью комбинации функций ЕСЛИ, ЕЧИСЛО и ПОИСК.

Вот формула:

= ЕСЛИ (ЕЧИСЛО (ПОИСК ($ K $ 2; D4)); E4; "")

Эта формула будет искать содержимое в поле поиска (которое связано с ячейкой K2) в ячейке с названием страны.

Если есть совпадение, эта формула возвращает номер строки, в противном случае возвращается пустое значение. Например, если поле со списком имеет значение «США», все записи со страной как «США» будут иметь номер строки, а все остальные будут пустыми («»).

Столбец помощника 3: В вспомогательном столбце 3 нам нужно собрать все номера строк из вспомогательного столбца 2, сложенные вместе. Для этого мы можем использовать комбинацию формул IFERROR и SMALL. Вот формула:

= ЕСЛИОШИБКА (МАЛЕНЬКАЯ ($ F $ 4: $ F $ 23; E4); "")

Эта формула складывает все совпадающие номера строк вместе. Например, если поле со списком имеет значение US, все номера строк с «US» складываются вместе.

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

= ЕСЛИОШИБКА (ИНДЕКС ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ I $ 3: I3)), "")

Эта формула состоит из 2 частей:
ПОКАЗАТЕЛЬ - Это извлекает данные на основе номера строки.
ЕСЛИ ОШИБКА - Если нет данных, возвращается пустое значение.

Вот снимок того, что вы наконец получили:

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

Проявите творческий подход! Попробуйте несколько вариаций

Вы можете попробовать и настроить его в соответствии с вашими требованиями. Вы можете создать несколько фильтров Excel вместо одного. Например, вы можете отфильтровать записи, в которых торговый представитель - Майк, а страна - Япония. Это можно сделать точно так же, как и с некоторыми изменениями в формуле во вспомогательных столбцах.

Другим вариантом может быть фильтрация данных, которые начинаются с символов, которые вы вводите в поле со списком. Например, когда вы вводите «I», вы можете захотеть извлечь страны, начинающиеся с I (по сравнению с текущей конструкцией, где она также даст вам Сингапур и Филиппины, поскольку она содержит алфавит I).

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

Примечание. Если вы используете Office 365, вы можете использовать функцию ФИЛЬТР, чтобы быстро фильтровать данные по мере ввода. Это проще, чем метод, показанный в этом руководстве.