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

Тема в разделе "Макросы Word, Excel (VBA)", создана пользователем Dragokas, 10 окт 2016.

  1. Dragokas
    Оффлайн

    Dragokas Very kind Developer Команда форума Супер-Модератор Разработчик Клуб переводчиков

    Сообщения:
    4.476
    Симпатии:
    4.305


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

    [​IMG]

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

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

    [​IMG]



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

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

    [​IMG]



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

    [​IMG]



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

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

    [​IMG]

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

    Работу с расширенным фильтром можно в разы ускорить и упростить с помощью простого макроса, который будет автоматически запускать расширенный фильтр при вводе условий, т.е. изменении любой желтой ячейки. Щелкните правой кнопкой мыши по ярлычку текущего листа и выберите команду Исходный текст (Source Code). В открывшееся окно скопируйте и вставьте вот такой код:
    Код (vb.net):
    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, т.е. все будет фильтроваться мгновенно, сразу после ввода очередного условия:

    [​IMG]



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

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

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

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

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

    [​IMG]

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

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

    [​IMG]

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

    [​IMG]

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

    Источник
     
    orderman нравится это.

Поделиться этой страницей