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

Подавляющему большинству пользователей Excel при слове «фильтрация данных» в голову приходит только обычный классический фильтр из вкладки Данные – Фильтр (Данные — Фильтр):

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

База

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

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

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

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

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

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

Добавление макроса

«Ну и где здесь удобство?» спросите вы и будете правы. Вам нужно не только руками вводить условия в желтые ячейки, но и открывать диалоговое окно, вводить туда диапазоны, нажимать OK. Грустно, согласен! Но «все меняется, когда приходят ©» — макросы!

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

Private Sub Worksheet_Change(Цель по ByVal как диапазон) Если не пересекается(Цель, Диапазон("A2:I5")) Ничего, Тогда при ошибке Возобновите следующий ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range("A1").CurrentRegion End If End Sub  

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

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

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

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

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

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

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

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

Те. фильтр-бананы мне в третьем квартале, именно в Москве и одновременно из Ашана.

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

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

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

• Суперфильтр по макросам
• Что такое макросы, куда и как вставить код макроса в Visual Basic
• Умные таблицы в Microsoft Excel