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

У подавляющего большинства пользователей 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  
27.05.2015 12:08:23
не получается что то, при вводе условия все строки скрываются.... это когда макрос прикрутил стало происходить, это возможно из-за того что использована умная таблица?
29.05.2015 11:41:47
Добрый день Николай! Подскажите, в текущем примере, фильтр работает для уже заполненной таблицы, а если в таблицу данные поступают автоматически из стороннего источника, фильтрации не происходит и новые поступившие данные отображаются как есть (на них фильтр никак не влияет). Можно ли как-то реализовать фильтрацию на автопоступление даных, чтобы показывались только данные в соответствии с фильтром?
29.05.2015 13:02:13
Александр, можно попробовать повесить макрос перезапуска фильтра не на событие изменения листа, а на событие активации листа - измените в заголовке макроса слово Change на Activate.
29.05.2015 15:33:26
Увы, не получается. Происходит сбой. А можно как то в первоначальном коде просто указать максимальный диапазон сток, к примеру не 100,  а 10000? Т.е. как я понимаю в данной строке "Range("A6")"  можно ли как-то   указать нужный диапазон?
07.06.2015 11:52:06
Николай, здравствуйте!

Большое спасибо за статью. Очень пригодилась в ежедневном использовании.

Возник один вопрос. Как автоматизировать фильтрацию по маске *дом*?. То есть фильтр выдает все варианты, где ДОМ может быть в любых вариациях и в любом месте, например, домовой, придомовой, дом и т.д.
11.06.2015 11:43:20
Николай, здравствуйте.
Идею с макросом для фильтра решил использовать для облегчения работы сторонних пользователей с файлом отчетности.
А именно, есть ячейка, откуда через выпадающий список выбирается название товарной группы, эта же ячейка связана (является источником) с полем условия расширенного фильтра (фильтр на один столбец). Предполагается, что в данной ячейке выбирается товарная группа, и она ссылкой идет в поле расширенного фильтра, и таблица фильтруется. Но вот проблема: фильтр не срабатывает, когда выбираю ячейку и она автоматом подставляется в условие фильтра. Когда руками встаю в ячейку с условием и формулой и нажимаю Enter, то срабатывает, а когда выбираю, то не работает. Получается, что условие фильтра не реагирует на изменение состава ячейки согласно формулы?
Возможно ли что-то здесь исправить?
Ниже схематичный пример таблицы.

Шапка расширенного фильтраВыбор товарной группы (источник для условия)
Условие фильтра = Ячейка с тов. группой
Шапка таблицы
Сама таблицы
07.07.2015 22:02:59
Присоединяюсь к вопросу. Примерно такая же ситуация: источником является выпадающий список на ActiveX, он меняет значение в диапазоне условий расширенного фильтра, но он не срабатывает.

Правда у меня ещё одна проблема: К основному диапазону применено "форматировать как таблицу" и при ручном заполнении диапазона условий фильтр всегда выдаёт пустые строки, начинающиеся со следующей за последней строкой таблицы.  
22.10.2015 22:36:41
По ещё одной проблеме: желтые строки условий фильтра + НОВАЯ "шапка" должны быть ограничены снизу и СВЕРХУ пустыми строками, при условии,  что НОВАЯ "шапка" находится НЕ в первой строке.  
28.08.2015 18:15:48
Здравствуйте Николай!  Давно и успешно применяю этот макрос, за что Вам огоромное Спасибо!
Возникла необходимость фильтрации данных через дробь, 40/20/10 (площадь квартиры) Есть  ли способ отфильтровать по отдельности каждый критерий?
Ну например **/**/>=10
Спасибо.
03.09.2015 10:41:56
Николай, в аналогичном фильтре у меня не фильтруются числа и цифры, не могу разобраться. Данные фильтруются в форме, при вводе чисел не фильтруются. Тексты фильтруются нормально.
14.10.2015 19:04:10
Здравствуйте Николай. Прекрасный фильтр. Применил, здорово. Спасибо.
16.10.2015 08:24:19
Здравствуйте Николай. Прекрасный фильтр, очень хотелось бы использовать. Но для моей таблицы работать никак не хочет, вводишь значение в диапазон условий и все пропадает, очищаешь, но таблица с данными появляется на несколько секунд и опять исчезает. Вывести ее возможно только кнопкой очистить фильтр. Не могли бы вы посмотреть в чем проблема? Заранее благодарен.
пациент
30.10.2015 12:33:30
Спасибо, все работает. Подскажите, в чем была ошибка в моем варианте?
31.10.2015 09:17:34
Желтые строки условий фильтра + НОВАЯ "шапка" должны быть ограничены снизу и СВЕРХУ пустыми строками, при условии,  что НОВАЯ "шапка" находится НЕ в первой строке.
Сравните оба варианта - свой и мой, разница в одной пустой строке. ;-)
03.11.2015 04:25:31
Еще раз, огромное спасибо!
29.10.2018 01:53:56
Была такая же проблема. Огромное спасибо!
22.10.2015 12:13:37
Здравствуйте. Подскажите пожалуйста текст этого макроса но только для Кнопки.
22.11.2015 04:30:21
Помогите, пожалуйста, у меня не получается фильтр. Диапазон сам меняется каждый раз. Что не так?
http://www.ex.ua/251718725031
27.11.2015 16:12:11
При добавлении чекбокса, отключающего подсвечивание дат, отрубается фильтрация по условиям из желтых полей. В чем может быть проблема?
23.03.2016 15:43:59
Николай, спасибо за фильтр!

У меня возникла необходимость использовать этот макрос для фильтрации записей по тегам, но пока что не получается. У меня есть таблица с контактами, в которой есть столбец "Группа". Так вот там могут быть значения вроде: "Семья", "Работа", "Друзья", "Отдых 2008" и т.д. Естественно, часть контактов можно отнести сразу к нескольким группам, для них значения будут следующими: "Семья, Работа", "Друзья, Отдых 2008", "Отдых 2008, Друзья, Работа" и т.д. Получается нечто вроде тегов к записям, т.к. каждому контакту может соответствовать сразу несколько групп (тегов).

Мне необходимо, чтобы можно было фильтровать такие записи сразу по нескольким группам. Сейчас это работает не всегда. Если вводить в одну строку группы через пробелы со звездочками перед и после названия, то результат будет адекватным, только если угадать порядок этих групп в контактах. Я же хочу добиться того, чтобы порядок не имел значения.
04.04.2016 17:02:58
Доброго Времени сеток , хотел бы узнать можно ли отфильтровать только уникальные значения !
используя Критерий   запросов !?
29.04.2016 11:23:46
Здравствуйте, Николай!
Огромная благодарность за данную статью! Очень помогает сэкономить время!
Но, прям было бы вообщеее супеееер, если бы:

выдаваемые значения сортировались в том же порядке, в котором вводятся данные наверху

То есть, я ввожу в B2,B3,B4 соответственно Банан, лук, персик
И  в фильтруемой части (внизу) показывались бы сначала все строчки, которые содержат банан, затем все строчки, которые содержат лук, ну а затем персик.

Соответственно, если в нашей табличке только по одной строчке где содержатся банан, лук и персик, то в фильтре выдавалось бы 3 строчки именно в заданной последоватальности.

Может, помимо Николая кто подскажет - как бы это реализовать???
Спасибо!
08.06.2016 17:48:28
Николай, здравствуйте! Спасибо за публикацию, очень полезна. Скажите, столкнулся с такой проблемой. Поделился файлом с коллегой и у него данный фильтр не работает. Не фильтрует ничего, выдает пустые строки. При запуске файла, на запрос безопасности отвечено Да. Возможно в настройках офиса что то не так. Что посмотреть не подскажите? Буду весь и весьма благодарен.
10.06.2016 04:35:03
Доброго времени суток! Николай, подскажите, пожалуйста, а можно ли ячейки, у которых ставятся условия, связать с полем-списком ActiveX (у Вас на эту тему не менее классный урок) - дабы выбирать критерий из списка, он подтягивался в ячейку-критерий доп фильтра, а макрос уже делал бы фильтр?
13.07.2016 10:48:34
Спасибо, за прием. Очень помог. Но у меня вопрос: мне иногда надо воспользоваться классическим фильтром. Я его устанавливаю, пользуюсь, но если использую расширенный, то классический снимается. И для того чтобы им воспользоваться, необходимо вновь его установить. Ка это можно поправить в коде?
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
Николай, спасибо за макрос. Уже частично работает. Вот только почему-то работает  только в первых двух столбцах. Как это можно исправить?
Страницы: 1  2  3  
Наверх