Как отфильтровать список элементов из длинного-длинного списка

Каким образом можно найти и выделить полезную информацию из большой таблицы?! В Excel есть как минимум два метода. Первый заключается в использовании формул с функциями поиска частичного соответствия. Второй способ дает аналогичный результат, но не содержит формул – его мы и рассмотрим в сегодняшнем номере EXCELTIP. Добро пожаловать в Расширенный Фильтр, да детка!

Расширенный фильтр

Команда закреплена на вкладке Данные рядом с обычным Фильтром.

Качаем этот файл с примером чтобы разобраться

Задача — выделим из длинного списка только те строки, в которых встречается слово Фреза или Сверло

Итак, есть длинный неупорядоченный список товаров. Нужно получить из него список товаров, в названии которых есть слова «фреза» или «сверло». Самое простое и бесхитростное решение — воспользоваться штатным Фильтром.

Обычный фильтр

…и теперь можно долго водить глазами по его списку, внимательно расставляя, один за другим, флажки.

Процедуру можно упростить, для этого Фильтр содержит поле с поиском.

поле с поиском

… по ключевым словам Excel сам находит именно то, что нужно. Если ввести в поле поиска “фреза“, Фильтр покажет только те элементы, в которых есть это слово. Также можно добавлять новые поисковые запросы к предыдущим, установив флажок «добавить выделенный фрагмент в фильтр“.

Но и этот способ хорошо работает только при небольшом количестве значений фильтра. Если нужно отфильтровать список по, скажем, 10 и более значениям, то нужно повторять эти действия довольно долго и можно допустить ошибку. Этого я не люблю.

Для быстрой и безошибочной работы с длинной таблицей или списком отлично подходит команда Расширенный Фильтр.

Перед запуском команды задаётся диапазон условий. Это отдельный список имён, которые, собственно, требуется найти в таблице. В примере он установлен ячейками E2:E4

Диапазон условий должен включать названия столбцов!

диапазон условий

Перейдите на вкладку Данные -> Сортировка и фильтр -> Дополнительно

Сортировка и фильтр

В этом примере использую «Копировать в другое место». Конечно, можно было и «Фильтровать список на месте», то есть в исходном местоположении источника.

Список должен быть длинее

Неплохо, но так Расширенный Фильтр отобрал только те строки, которые начинаются с «фреза» или «сверло».

Чтобы результат включал строки, содержащие имя, используем простой приём в виде символа подстановки *. Заключим условия между двумя «звездочками» (*), тогда Excel интерпретирует их как всё, где встречается группа символов «фреза» или «сверло».

Символ подстановки

Однако и этого всё ещё недостаточно, т.к. критериям фильтра соответствуют и фрезы и бор-фрезы (которые нам не нужны), но как гласит легенда: «There can be only one».

Чтобы получить строки только со словом «фреза», воспользуемся ещё одной хитростью — поставим пробел до и после него.

Использование пробелов

Она заставляет Excel искать (пробел)фреза(пробел) и теперь «бор-фреза» не соответствует условиям фильтра. Хитрость основана на предположении, что каждое слово разделено пробелом и как следствие этого исключает из результата два возможных случая:

  1. Имена начинаются с «фреза»
  2. Имена заканчиваются на «фреза»

Чтобы учесть вышеупомянутые возможности, необходимо расширить диапазон критериев до E6, а именно:

E3- * фреза * ‘Содержит слово фреза (только отдельно и не в начале или конце)

E4 — *сверло* ‘Содержит слово сверло (в любом месте)

E5 — =»=фреза *» ‘Начинается со слова фреза

E6 — =»=*фреза» ‘Заканчивается словом фреза

Условия по полной

Примечание: * в конце не обязательна. Она стоит там, чтобы было видно где кончается строка

Получение выражений для E5 и E6 в рамки статьи не входит. Достаточно изучить их конструкцию и, сделав несколько упражнений, пользоваться ими будет проще, чем два байта переслать.

Итак, если требуется отфильтровать список строк независимо от позиции в исходных данных, то есть на частичное совпадение, можно легко это сделать Расширенным фильтром. Нужно только помнить о положении символа * перед строкой в диапазоне условий, как здесь:

* фреза

*сверло

 


2 комментария

  1. Ренат, приветствую!
    Вот мне прям именно это и нужно — фильтровать по списку слов, но вот только не в Экселе, а в LibreOffice.
    Там вроде все менюшки по аналогии с Экселем, но вот именно «Расширенный фильтр» выглядит по-другому, и у меня не получается перенести Ваш алгоритм туда =(

Добавить комментарий для Колесо-топ Отменить ответ

Ваш e-mail не будет опубликован. Обязательные поля помечены *