Фильтр Excel - одна из наиболее часто используемых функций при работе с данными. Кто читает эту статью и еще пока не знаком с фильтрами то Вам сначала сюда.
Эта статья будет полезна тем, кто работает с большими списками и по роду деятельности необходимо постоянно в этом списке что-то искать, смотреть и анализировать.
На простом примере мы разберем принцип как настроить окно поиска с динамическим фильтром Excel, чтобы оно фильтровало данные в зависимости от того, что вы вводите в поле поиска и выводило отдельным списком на экран.
Звучит сложно, но на самом деле все намного проще чем Вы предполагаете и дочитав статью до конца останетесь довольны на 100%
В качестве примера мы рассмотрим как настроить динамический поиск в таблице с данными содержащими наименование Федерального округа, наименование субъекта РФ, наименование регионального центра и код субъекта.
Приступим.
Создание окна поиска с динамическим фильтром
Для начала подготовим наш список для поставленной задачи. Для этого нам понадобятся четыре вспомогательных столбца. Но мы пока используем один, в котором объединим с помощью функции СЦЕПИТЬ() все данные по строкам в нашем диапазоне.
=СЦЕПИТЬ(A2;", ";B2;", ";C2;", ";D2)
В дальнейшем из этого столбца будет осуществляться поиск по заданным параметрам.
Затем присвоим имя получившемуся диапазону, как присваивать имена см. статью.
Е2:Е87 - имя диапазона Поиск
Далее основное, что нам нужно сделать это создать поле поиска, которое выглядит так:
Чтобы создать это поле убедитесь, что на Панели вкладок у Вас есть вкладка с наименованием "Разработчик". Если у Вас ее нет, тогда давайте в пару кликов мыши ее настроим:
- Щелкните правой кнопкой мыши любую из существующих вкладок на ленте и выберите «Настройка ленты». Откроется диалоговое окно параметров Excel.
- В диалоговом окне «Параметры Excel» у вас будут параметры «Настройка ленты». Справа на панели «Основные вкладки» установите флажок «Разработчик» и нажмите ОК
Теперь у вас будет отображаться «Разработчик» как одна из вкладок
Теперь когда мы настроили вкладку «Разработчик» нам больше ничего не мешает осуществить задуманное
1. Перейдите на вкладку Разработчика затем нажмите кнопку Вставить и выберете в Элементах управления ActiveX значок с описанием Поле со списком (элементы управления ActiveX).
2. Выберете любое свободное место на листе и вставится поле со списком
3. Правой кнопкой мыши щелкаем по нашему созданному поле поиска и нажимаем Свойства
В окне свойств внесите следующие изменения:
- LinkedCell: Необходимо указать связанную ячейку, через которую будет осуществлена связь формы с таблицей. Я в этом примере укажу J2 (Вы можете выбрать любую ячейку).
- ListFillRange: Здесь указываем именованный диапазон, который мы создали ранее - Поиск.
- MatchEntry: выбираем позицию 2-fmMatchEntryNone (эта настройка не даст нам возможность завершать автоматически вводимые слова)
4. Завершив настройки, перейдите на вкладку «Разработчик» далее «Элементы управления» и нажмите кнопку «Режим конструктора» (это выведет вас из режима разработчика, и теперь Вы можете ввести что угодно в поле со списком. Теперь все, что Вы вводите, будет отражено в ячейке J2)
Форму настроили!!!
Теперь нам предстоит настроить связь формы с оставшимися тремя вспомогательными столбцами.
Установка связи со вспомогательными столбцами
Вспомогательный столбец 2
Здесь все просто, нам в каждой строке ввести порядковые номера для всех записей от 1 до 86 в нашем случае.
Вспомогательный столбец 3
В этом столбце мы проверяем, соответствует ли текст, введенный в поле поиска, тексту в ячейках в именованном диапазоне Поиск.
Это можно сделать с помощью комбинации функций ЕСЛИ(), ЕЧИСЛО() и ПОИСК() .
Вот формула:
= ЕСЛИ(ЕЧИСЛО (ПОИСК ($ J $ 2; E2)); F2; "")
Эта формула будет искать содержимое в поле поиска (которое связано с ячейкой J2) в ячейках именованного диапазона Поиск.
Если есть совпадение, эта формула будет возвращать порядковый номер записи из Вспомогательного столбца 2, в противном случае будет возвращаться пустое значение.
Вспомогательный столбец 4
Во Вспомогательном столбце 4 нам нужно собрать все номера строк из Вспомогательного столбца 3, в которых имеются значения. Для этого мы будем использовать комбинацию формул ЕСЛИОШИБКА() и НАИМЕНЬШИЙ(). Вот формула:
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ($G$2:$G$87;F2);"")
Теперь, когда мы настроили таблицу и у нас есть номера извлеченных строк нам просто нужно извлечь данные из этих строк. И это легко сделать с помощью формулы ИНДЕКС()
Выберем любое место в нашей таблице и создадим такую же шапку с наименованиями как и в исходной таблице, это будет наш диапазон, в который мы будем выводить отфильтрованные по нашему запросу записи. Введем формулу ИНДЕКС()
Вот формула:
=ЕСЛИОШИБКА(ИНДЕКС($A$2:$H$87;$H2;1);"")
Обратите внимание, что значение аргумента, отвечающего за номер столбца необходимо будет поменять в ручную. Так, в столбце Код данный аргумент будет - 1, в столбце Название регионов - 2, в столбце Столица - 3, в столбце Округ - 4.
Настало время проверить как работает поле поиска с динамическим фильтром
Начинаем вводить слово Западный и фильтр отбирает только те строки, в которых содержится это слово.
Работает!!!
Согласен в первый раз может показаться немного сложным и запутанным, но поверьте сделав эту настройку несколько раз для Вас уже не составит труда ее повторить в слепую и с пониманием как она работает.
Для удобства динамический фильтр можно создать и на другом листе или в отдельной книге.