- Сообщения
- 8,030
- Решения
- 14
- Реакции
- 6,805
У подавляющего большинства пользователей Excel при слове "фильтрация данных" в голове всплывает только обычный классический фильтр с вкладки Данные - Фильтр (Data - Filter):
Такой фильтр - штука привычная, спору нет, и для большинства случаев вполне сойдет. Однако бывают ситуации, когда нужно проводить отбор по большому количеству сложных условий сразу по нескольким столбцам. Обычный фильтр тут не очень удобен и хочется чего-то помощнее. Таким инструментом может стать расширенный фильтр (advanced filter), особенно с небольшой "доработкой напильником" (по традиции).
Основа
Для начала вставьте над вашей таблицей с данными несколько пустых строк и скопируйте туда шапку таблицы - это будет диапазон с условиями (выделен для наглядности желтым):
Между желтыми ячейками и исходной таблицей обязательно должна быть хотя бы одна пустая строка.
Именно в желтые ячейки нужно ввести критерии (условия), по которым потом будет произведена фильтрация. Например, если нужно отобрать бананы в московский "Ашан" в III квартале, то условия будут выглядеть так:
Чтобы выполнить фильтрацию выделите любую ячейку диапазона с исходными данными, откройте вкладку Данные и нажмите кнопку Дополнительно (Data - Advanced). В открывшемся окне должен быть уже автоматически введен диапазон с данными и нам останется только указать диапазон условий, т.е. A1:I2:
Обратите внимание, что диапазон условий нельзя выделять "с запасом", т.е. нельзя выделять лишние пустые желтые строки, т.к. пустая ячейка в диапазоне условий воспринимается Excel как отсутствие критерия, а целая пустая строка - как просьба вывести все данные без разбора.
Переключатель Скопировать результат в другое место позволит фильтровать список не прямо тут же, на этом листе (как обычным фильтром), а выгрузить отобранные строки в другой диапазон, который тогда нужно будет указать в поле Поместить результат в диапазон. В данном случае мы эту функцию не используем, оставляем Фильтровать список на месте и жмем ОК. Отобранные строки отобразятся на листе:
Добавляем макрос
"Ну и где же тут удобство?" - спросите вы и будете правы. Мало того, что нужно руками вводить условия в желтые ячейки, так еще и открывать диалоговое окно, вводить туда диапазоны, жать ОК. Грустно, согласен! Но "все меняется, когда приходят они ©" - макросы!
Работу с расширенным фильтром можно в разы ускорить и упростить с помощью простого макроса, который будет автоматически запускать расширенный фильтр при вводе условий, т.е. изменении любой желтой ячейки. Щелкните правой кнопкой мыши по ярлычку текущего листа и выберите команду Исходный текст (Source Code). В открывшееся окно скопируйте и вставьте вот такой код:
VB.NET / VBA:
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
Так все гораздо лучше, правда?
Реализация сложных запросов
Теперь, когда все фильтруется "на лету", можно немного углубиться в нюансы и разобрать механизмы более сложных запросов в расширенном фильтре. Помимо ввода точных совпадений, в диапазоне условий можно использовать различные символы подстановки (* и ?) и знаки математических неравенств для реализации приблизительного поиска. Регистр символов роли не играет. Для наглядности я свел все возможные варианты в таблицу:
Критерий Результат
гр* или гр все ячейки начинающиеся с Гр, т.е. Груша, Грейпфрут, Гранат и т.д.
=лук все ячейки именно и только со словом Лук, т.е. точное совпадение
*лив* или *лив ячейки содержащие лив как подстроку, т.е. Оливки, Ливер, Залив и т.д.
=п*в слова начинающиеся с П и заканчивающиеся на В т.е. Павлов, Петров и т.д.
а*с слова начинающиеся с А и содержащие далее С, т.е. Апельсин, Ананас, Асаи и т.д.
=*с слова оканчивающиеся на С
=???? все ячейки с текстом из 4 символов (букв или цифр, включая пробелы)
=м??????н все ячейки с текстом из 8 символов, начинающиеся на М и заканчивающиеся на Н, т.е. Мандарин, Мангостин и т.д.
=*н??а все слова оканчивающиеся на А, где 4-я с конца буква Н, т.е. Брусника, Заноза и т.д.
>=э все слова, начинающиеся с Э, Ю или Я
<>*о* все слова, не содержащие букву О
<>*вич все слова, кроме заканчивающихся на вич (например, фильтр женщин по отчеству)
= все пустые ячейки
<> все непустые ячейки
>=5000 все ячейки со значением больше или равно 5000
5 или =5 все ячейки со значением 5
>=3/18/2013 все ячейки с датой позже 18 марта 2013 (включительно)
Тонкие моменты:
- Знак * подразумевает под собой любое количество любых символов, а ? - один любой символ.
- Логика в обработке текстовых и числовых запросов немного разная. Так, например, ячейка условия с числом 5 не означает поиск всех чисел, начинающихся с пяти, но ячейка условия с буквой Б равносильна Б*, т.е. будет искать любой текст, начинающийся с буквы Б.
- Если текстовый запрос не начинается со знака =, то в конце можно мысленно ставить *.
- Даты надо вводить в штатовском формате месяц-день-год и через дробь (даже если у вас русский Excel и региональные настройки).
Условия записанные в разных ячейках, но в одной строке - считаются связанными между собой логическим оператором И (AND):
Т.е. фильтруй мне бананы именно в третьем квартале, именно по Москве и при этом из "Ашана".
Если нужно связать условия логическим оператором ИЛИ (OR), то их надо просто вводить в разные строки. Например, если нам нужно найти все заказы менеджера Волиной по московским персикам и все заказы по луку в третьем квартале по Самаре, то это можно задать в диапазоне условий следующим образом:
Если же нужно наложить два или более условий на один столбец, то можно просто продублировать заголовок столбца в диапазоне критериев и вписать под него второе, третье и т.д. условия. Вот так, например, можно отобрать все сделки с марта по май:
В общем и целом, после "доработки напильником" из расширенного фильтра выходит вполне себе приличный инструмент, местами не хуже классического автофильтра.
Источник
