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

У подавляющего большинства пользователей Excel при слове "фильтрация данных" в голове всплывает только обычный классический фильтр с вкладки Данные - Фильтр (Data - Filter):

advanced-filter1.png

Такой фильтр - штука привычная, спору нет, и для большинства случаев вполне сойдет. Однако бывают ситуации, когда нужно проводить отбор по большому количеству сложных условий сразу по нескольким столбцам. Обычный фильтр тут не очень удобен и хочется чего-то помощнее. Таким инструментом может стать расширенный фильтр (advanced filter), особенно с небольшой "доработкой напильником" (по традиции).

Основа

Для начала вставьте над вашей таблицей с данными несколько пустых строк и скопируйте туда шапку таблицы - это будет диапазон с условиями (выделен для наглядности желтым):

advanced-filter2.png

Между желтыми ячейками и исходной таблицей обязательно должна быть хотя бы одна пустая строка.

Именно в желтые ячейки нужно ввести критерии (условия), по которым потом будет произведена фильтрация. Например, если нужно отобрать бананы в московский "Ашан" в III квартале, то условия будут выглядеть так:

advanced-filter3.png

Чтобы выполнить фильтрацию выделите любую ячейку диапазона с исходными данными, откройте вкладку Данные и нажмите кнопку Дополнительно (Data - Advanced). В открывшемся окне должен быть уже автоматически введен диапазон с данными и нам останется только указать диапазон условий, т.е. A1:I2:

advanced-filter5.png

Обратите внимание, что диапазон условий нельзя выделять "с запасом", т.е. нельзя выделять лишние пустые желтые строки, т.к. пустая ячейка в диапазоне условий воспринимается Excel как отсутствие критерия, а целая пустая строка - как просьба вывести все данные без разбора.

Переключатель Скопировать результат в другое место позволит фильтровать список не прямо тут же, на этом листе (как обычным фильтром), а выгрузить отобранные строки в другой диапазон, который тогда нужно будет указать в поле Поместить результат в диапазон. В данном случае мы эту функцию не используем, оставляем Фильтровать список на месте и жмем ОК. Отобранные строки отобразятся на листе:

advanced-filter6.png

Добавляем макрос

"Ну и где же тут удобство?" - спросите вы и будете правы. Мало того, что нужно руками вводить условия в желтые ячейки, так еще и открывать диалоговое окно, вводить туда диапазоны, жать ОК. Грустно, согласен! Но "все меняется, когда приходят они ©" - макросы!

Работу с расширенным фильтром можно в разы ускорить и упростить с помощью простого макроса, который будет автоматически запускать расширенный фильтр при вводе условий, т.е. изменении любой желтой ячейки. Щелкните правой кнопкой мыши по ярлычку текущего листа и выберите команду Исходный текст (Source Code). В открывшееся окно скопируйте и вставьте вот такой код:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2:I5")) Is Nothing Then
        On Error Resume Next
        ActiveSheet.ShowAllData
        Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1").CurrentRegion
    End If
End Sub

Эта процедура будет автоматически запускаться при изменении любой ячейки на текущем листе. Если адрес измененной ячейки попадает в желтый диапазон (A2:I5), то данный макрос снимает все фильтры (если они были) и заново применяет расширенный фильтр к таблице исходных данных, начинающейся с А7, т.е. все будет фильтроваться мгновенно, сразу после ввода очередного условия:

advanced-filter-work.gif

Так все гораздо лучше, правда? :)

Реализация сложных запросов

Теперь, когда все фильтруется "на лету", можно немного углубиться в нюансы и разобрать механизмы более сложных запросов в расширенном фильтре. Помимо ввода точных совпадений, в диапазоне условий можно использовать различные символы подстановки (* и ?) и знаки математических неравенств для реализации приблизительного поиска. Регистр символов роли не играет. Для наглядности я свел все возможные варианты в таблицу:

Критерий Результат
гр* или гр все ячейки начинающиеся с Гр, т.е. Груша, Грейпфрут, Гранат и т.д.
=лук все ячейки именно и только со словом Лук, т.е. точное совпадение
*лив* или *лив ячейки содержащие лив как подстроку, т.е. Оливки, Ливер, Залив и т.д.
=п*в слова начинающиеся с П и заканчивающиеся на В т.е. Павлов, Петров и т.д.
а*с слова начинающиеся с А и содержащие далее С, т.е. Апельсин, Ананас, Асаи и т.д.
=*с слова оканчивающиеся на С
=???? все ячейки с текстом из 4 символов (букв или цифр, включая пробелы)
=м??????н все ячейки с текстом из 8 символов, начинающиеся на М и заканчивающиеся на Н, т.е. Мандарин, Мангостин  и т.д.
=*н??а все слова оканчивающиеся на А, где 4-я с конца буква Н, т.е. Брусника, Заноза и т.д.
>=э все слова, начинающиеся с Э, Ю или Я
<>*о* все слова, не содержащие букву О
<>*вич все слова, кроме заканчивающихся на вич (например, фильтр женщин по отчеству)
= все пустые ячейки
<> все непустые ячейки
>=5000 все ячейки со значением больше или равно 5000
5 или =5 все ячейки со значением 5
>=3/18/2013 все ячейки с датой позже 18 марта 2013 (включительно)

Тонкие моменты:

  • Знак * подразумевает под собой любое количество любых символов, а ? - один любой символ.
  • Логика в обработке текстовых и числовых запросов немного разная. Так, например, ячейка условия с числом 5 не означает поиск всех чисел, начинающихся с пяти, но ячейка условия с буквой Б равносильна Б*, т.е. будет искать любой текст, начинающийся с буквы Б.
  • Если текстовый запрос не начинается со знака =, то в конце можно мысленно ставить *.
  • Даты надо вводить в штатовском формате месяц-день-год и через дробь (даже если у вас русский Excel и региональные настройки).

Логические связки И-ИЛИ

Условия записанные в разных ячейках, но в одной строке - считаются связанными между собой логическим оператором И (AND):

advanced-filter3.png

Т.е. фильтруй мне бананы именно в третьем квартале, именно по Москве и при этом из "Ашана".

Если нужно связать условия логическим оператором ИЛИ (OR), то их надо просто вводить в разные строки. Например, если нам нужно найти все заказы менеджера Волиной по московским персикам и все заказы по луку в третьем квартале по Самаре, то это можно задать в диапазоне условий следующим образом:

advanced-filter7.png

Если же нужно наложить два или более условий на один столбец, то можно просто продублировать заголовок столбца в диапазоне критериев и вписать под него второе, третье и т.д. условия. Вот так, например, можно отобрать все сделки с марта по май:

advanced-filter8.png

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

Ссылки по теме



Страницы: 1  2  3  
12.08.2016 21:44:23
Доброго времени суток!
Николай, можно ли это перенести в диалоговое окно чтобы данные выводились на listBox?
Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("A2:I5") Is Nothing Then
 On Error Resume Next
 ActiveSheet.ShowAllData
 Range("A7"CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1").CurrentRegion
   End If
End Sub
29.09.2016 17:53:42
Здравствуйте! Есть массив ключевых фраз:
Ремонт квартир
Ремонт квартир Москва
Ремонт квартир Ульяновск
и т.д.
При попытке воспользоваться расширенным фильтром для фильтрации слов, он ищет только среди тех слов, которые находятся в первом вхождении столбика, т.е. отыскивает только слово "Ремонт". Как сделать так, чтобы я могу найти и те слова, которые находятся в середине и в конце ключевой фразы?
23.10.2016 06:24:20
ЧУДЕСА СЛУЧАЮТСЯ!!!
И это одно из них...
Мда, так уж получилось, что мои мозги програмирование воспринимать отказываются органически, сколько ни пытался и Делфи и SQL, мертвый номер... В итоге достаточно сложная база данных работает на танцах с бубном в виде многоэтажных алгоритмов из макросов, гиперссылок и элементов управления. Максимум на что меня хватило это записать макросы встроенным ридером и чуток доработать напильником, пилой дружба и конфетами ириска ))) В общем все работает и доступно для модернизации своими силами, а это особенно важно когда ты в принципе не знаешь точно даже того чего хочешь от программы сегодня и тем более чего ты захочешь от нее завтра, писать при таких запросах тех. задание для профи смысла нет.. Такой вот магический элемент - то самое чего мне катастрофически не хватло для организации быстрого поиска по базе, пользовался встроенным поиском но долго и громоздко ((
СПАСИБО огромное Николай!!!
23.10.2016 10:26:16
Не за что, Евгений! Удачи ;)
02.11.2016 12:34:07
Добрай день, экспериментируя с фильтром столкнулся с проблемой, на примере - фильтрую список публикаций добавил в зону поиска одного автора, второго, третьего все хорошо, а потом захотел ещё от фильтровать по году, допустим хочу посмотреть все источники содержащие указанных авторов за 2010 и 2004 годы, введением куда то в жёлтый диапазон только 2 значений этого не сделать? Если с одним я ещё понял и догадываясь что сделав столбик ещё один с названием год и прославив в каждую строчку равенство верхней ячейке, можно добиться нужного результата, но может есть какой то менее громоздкий способ?

И ещё, Николай может Вы подскажете какая команда или набор команд может позволить реализовать такой алгоритм действия:
Просмотреть столбик А диапазона А1 если очередная ячейка не пустая то найти её содержимое в столбике А диапазона Б1 и все строки диапазона содержащие этот ключ перенести в диапазон С1 с привязкой к динамической ячейке сдвигающейся вниз после каждой вставки или просто в очередную свободную строку столбика допустим А в диапазоне С1

P. S. Или подскажите в каком разделе можно почитать на эту тему..., что то похожее нашёл там где рассматривается скрытие столбиков и строк по заданным параметрам, но все мои попытки сделать на их основе требуемый макрос увы успехом пока не увенчались...
Большое спасибо за фильтр. Всё отлично работает. Но при открытии на маке ничего не работает( Не подскажите в чем может быть причина
22.04.2017 10:49:32
Проверьте защиту от макросов - не включена ли?
18.12.2016 19:15:03
Здравствуйте, да, согласен, макрос отличный, только у меня ситуация: если вставлять этот фильтр начиная с первых строчек листа, то всё ок работатает, если над фильтром 1-2 строчки вставлено, то нет, и при вводе значений, он просто всё стирает и всё, не подскажите как это можно исправить?
22.12.2016 16:36:53
а если требуется задать фильтр (пример от 3 до 40) то какой командой он обозначается?
29.01.2017 12:57:32
Подскажите, как можно отфильтровать так же пустые значения? И возможно ли это?
19.03.2017 19:47:46
Николай, спасибо за макрос. Уже частично работает. Вот только почему-то работает  только в первых двух столбцах. Как это можно исправить?
30.03.2017 17:23:00
добрый день,
макрос вроде бы работает, но только с текстовыми значениями, а вот как с его помощью фильтровать числовые значения? имеется таблица товарных поставок на 500000 строк в 50 колонках. нужно фильтровать по колонке "КОД ТОВАРА", в которой числовые значения от 4 до 10 знаков. например - нужно выбрать товары с кодами 66* и 7777* - но так не находит НИ ОДНОЙ строки. изменение типа ячеек на текстовые тоже не помогло. Что посоветуете?
22.04.2017 10:56:12
Скопируйте столбец с числами, преобразуйте числа в текст специальным макросом (изменение формата на текстовый тут не поможет) - должны появиться зеленые треугольники на ячейках.
И тогда можно будет по этому действительно текстовому столбцу использовать фильтры типа 777* и т.п.
01.06.2017 17:07:13
в бесплатной версии это недоступно?
как-то руками можно сделать?
01.05.2017 15:50:35
Классная вещь давно искал:):):)
01.06.2017 01:25:16
Николай возможно ли ваш макрос использовать для To-Do-List чтобы сортировка происходила в автоматическом режиме по приоритету по
столбцу ? Заранее благодарен ....
10.08.2017 13:45:57
Добрый день. Спасибо за информацию. Николай, не подскажите, есть возможность отфильтровать строки по столбцу, в котором не 1 или 2 жестких соответствия с числом, а сразу с несколькими?
К примеру мне нужно вывести строки, в которых в данном столбце будут следующие значения 4556, 5425, 5789, 8563 и 9563.
11.10.2017 09:21:29
Здравствуйте, Николай.
Классный фильтр.
В однострочной шапке все работает отлично, а можно ли как-нибудь сделать так, чтобы фильтр работал с двух-трех строчной шапкой?
Пример шапки http://clip2net.com/s/3Ot38GN
Пробовал делать фильтр и по вашему скрипту и по автоматически создаваемому.
30.10.2017 12:52:27
Здравствуйте,
Как добавить диаграмму которая меняет свои показатели в зависимости появления списка?
Например, набираю текст "Арбузы" и смотрю график продаж за этот год, затем набираю "Дыни" и смотрю уже другой график
10.11.2017 22:29:15
Здравствуйте, Николай!
У меня возникли сложности с фильтрацией данных с помощью Расширенного фильтра.
Мне необходимо на отдельном листе с помощью расширенного фильтра выбрать строки со значениями больше, чем строки с другими значениями. Если конкретно, то выбрать те строки, где "план расходов" больше, чем "план продаж".
22.11.2017 15:07:07
Большое спасибо! Информация очень помогла в работе.
30.11.2017 18:55:05
Спасибо за очередной, полезный урок!
Попробовал в верхней (жёлтой) таблице, в столбце "Дата", поменять формат ячейки на "Дата" и можно вводить дату обычно-привычным способом (через точку или тире).
В ожидании новых уроков...
12.12.2017 12:32:08
Здравствуйте.
Как сделать что бы выборка данных была по нужному диапазону значений, а не конкретному значению?
Например, есть база Пример в которой нужно отобразить людей по возрасту в пределах 20-50 лет. В идеале, чтобы можно было выбирать через выпадающий список нужное значение (20-50 лет, 18-55, до 60 ....). Буду очень благодарен!
16.12.2017 22:03:26
Здравствуйте.
Фильтр Павлова не запрещает использовать обычный фильтр8)
17.12.2017 11:10:21
"Фильтр Павлова" - это сильно. Повеселили :)
У меня один вопрос может ли работать данный фильтр из урока при защите листа (ячеек листа)?
18.12.2017 12:11:41
Спасибо! Как-то увлекся этим фильтром и забыл про стандартный фильтр)
А как организовать чекбокс в выпадающем списке? Но что бы он искал не по точному совпадению текста в ячейче Есть колонка должности, но человек может числится на нескольких должностях. Разбивать на дополнительные колонки не вариант - если в первой колонке укажем Директор, во второй Сварщик, тогда при поиске можем не найти этого человека, если будем искать в обратном порядке (вбивать в первую Сварщик, а во вторую Директор). А таких колонок может быть до 5 ...
18.12.2017 12:32:06
без чекбоксов: Ctrl+F, Alt+Н (найти все)
16.01.2018 10:56:53
Не удобно будет для слабых пользователей в Экселе:(
02.03.2018 13:18:34
Один минус в том что в строке выше (7) не возможно добавить формулы суммирования для итогов!;)
A V
23.03.2018 11:38:57
Автор, спасибо вам огромное, шикарный скрипт.
Может подскажете, как точно такое же реализовать в LibreOffice или лучше даже - в google таблицах?
Очень классный фильтр, помог, огромное спасибо за подробный урок. У меня один вопрос может ли работать данный фильтр из урока при защите листа (ячеек листа)?
29.05.2018 21:37:21
Очень хотелось бы понять как всё-таки данный фильтр. Сам пока изучаю азы VBA. У меня получается как-то так:

Private Sub Worksheet_Change(ByVal Target As Range)                            'срабатывает на любое изменение листа, передаёт переменную Target по значению
    If Not Intersect(Target, Range("A2:K6")) Is Nothing Then                     'если нашлось хотя бы одно значение в пересечении диапазонов Target и "A2:K6"
        On Error Resume Next                                                                   'в случае ошибки следующей строки пропустить её
        ActiveSheet.ShowAllData                                                               'снять все фильтры
        Range("A9").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1").CurrentRegion    'От указанной ячейки определяется диапазон данных и применяется расширенный фильтр
    End If                                                                                                                                                               'Action определяет что фильтруем "на месте"
End Sub                                                                                                                                                                'CriteriaRange передается диапазон критериев, по которым будет фильтроваться таблица
 
Совсем не могу понять что делает  Intersect(Target, Range("A2:K6")), судя по справочной информации эта функция определяет пересечение диапазонов. Но какой диапазон сейчас у переменной Target?  
Выделенный диапазон активного листа (одна или несколько ячеек).
Поставьте точку останова в теле процедуры и в окне отладки наберите:
? Target.Address
27.08.2018 04:56:09
=*с Не работает если в конце фильтруемого списка стоит пробел, например в списке присутствует 10тыс. и после точки пробел.
"Тонкие моменты"...
1. Excel действительно ориентирован на американский формат дат (что называется, "US-centric"). И вводить дату нужно полностью.
Ничего поделать с этим нельзя, кроме...

Наряду с форматом типа
12/24/2013
с переставленными месяцем и днем можно получить правильный порядок, только надо использовать английские аббревиатуры месяцев:
24-Dec-2013
Месяцы: Jan; Feb; Mar; Apr; May; Jun; Jul; Aug; Sep; Oct; Nov; Dec.
Например:
>=24-Dec-2013
Или вот так через слэш или дефис в обратном порядке:
>=2013/12/24
>=2013-12-24
Последний лучше. Установить формат для поля даты: ГГГГ-ММ-ДД
По крайней мере, без извращений.

2. Если надо фильтровать с учетом регистра символов, то для этого надо добавить дополнительное поле с именем, не совпадающим с именами полей базы данных, и использовать функцию СОВПАД().
Например,
Имя поля: Case-sensitive filter
Значение: =СОВПАД(В8;"лук")
где В8 - это ссылка на соответствующую ячейку первой строки данных в базе данных.
Найдет "лук", а не "Лук" и пр.
12.11.2018 11:43:43
Добрый день, Николай, а не подскажите как "допилить" макрос, что бы он добавлял/удалял строки поля поиск по мере заполнения какого то столбика? То есть что бы в поле поиск всегда было допустим три свободные строки... если заполнена одна из них автоматически вставляться еще одна, если очищено поле лишние строки удаляются.
Страницы: 1  2  3  
Наверх