Расширенный фильтр и немного магии
У подавляющего большинства пользователей Excel при слове "фильтрация данных" в голове всплывает только обычный классический фильтр с вкладки Данные - Фильтр (Data - Filter):
Такой фильтр - штука привычная, спору нет, и для большинства случаев вполне сойдет. Однако бывают ситуации, когда нужно проводить отбор по большому количеству сложных условий сразу по нескольким столбцам. Обычный фильтр тут не очень удобен и хочется чего-то помощнее. Таким инструментом может стать расширенный фильтр (advanced filter), особенно с небольшой "доработкой напильником" (по традиции).
Основа
Для начала вставьте над вашей таблицей с данными несколько пустых строк и скопируйте туда шапку таблицы - это будет диапазон с условиями (выделен для наглядности желтым):
Между желтыми ячейками и исходной таблицей обязательно должна быть хотя бы одна пустая строка.
Именно в желтые ячейки нужно ввести критерии (условия), по которым потом будет произведена фильтрация. Например, если нужно отобрать бананы в московский "Ашан" в III квартале, то условия будут выглядеть так:
Чтобы выполнить фильтрацию выделите любую ячейку диапазона с исходными данными, откройте вкладку Данные и нажмите кнопку Дополнительно (Data - Advanced). В открывшемся окне должен быть уже автоматически введен диапазон с данными и нам останется только указать диапазон условий, т.е. A1:I2:
Обратите внимание, что диапазон условий нельзя выделять "с запасом", т.е. нельзя выделять лишние пустые желтые строки, т.к. пустая ячейка в диапазоне условий воспринимается Excel как отсутствие критерия, а целая пустая строка - как просьба вывести все данные без разбора.
Переключатель Скопировать результат в другое место позволит фильтровать список не прямо тут же, на этом листе (как обычным фильтром), а выгрузить отобранные строки в другой диапазон, который тогда нужно будет указать в поле Поместить результат в диапазон. В данном случае мы эту функцию не используем, оставляем Фильтровать список на месте и жмем ОК. Отобранные строки отобразятся на листе:
Добавляем макрос
"Ну и где же тут удобство?" - спросите вы и будете правы. Мало того, что нужно руками вводить условия в желтые ячейки, так еще и открывать диалоговое окно, вводить туда диапазоны, жать ОК. Грустно, согласен! Но "все меняется, когда приходят они ©" - макросы!
Работу с расширенным фильтром можно в разы ускорить и упростить с помощью простого макроса, который будет автоматически запускать расширенный фильтр при вводе условий, т.е. изменении любой желтой ячейки. Щелкните правой кнопкой мыши по ярлычку текущего листа и выберите команду Исходный текст (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, т.е. все будет фильтроваться мгновенно, сразу после ввода очередного условия:
Так все гораздо лучше, правда? :)
Реализация сложных запросов
Теперь, когда все фильтруется "на лету", можно немного углубиться в нюансы и разобрать механизмы более сложных запросов в расширенном фильтре. Помимо ввода точных совпадений, в диапазоне условий можно использовать различные символы подстановки (* и ?) и знаки математических неравенств для реализации приблизительного поиска. Регистр символов роли не играет. Для наглядности я свел все возможные варианты в таблицу:
| Критерий | Результат |
| гр* или гр | все ячейки начинающиеся с Гр, т.е. Груша, Грейпфрут, Гранат и т.д. |
| =лук | все ячейки именно и только со словом Лук, т.е. точное совпадение |
| *лив* или *лив | ячейки содержащие лив как подстроку, т.е. Оливки, Ливер, Залив и т.д. |
| =п*в | слова начинающиеся с П и заканчивающиеся на В т.е. Павлов, Петров и т.д. |
| а*с | слова начинающиеся с А и содержащие далее С, т.е. Апельсин, Ананас, Асаи и т.д. |
| =*с | слова оканчивающиеся на С |
| =???? | все ячейки с текстом из 4 символов (букв или цифр, включая пробелы) |
| =м??????н | все ячейки с текстом из 8 символов, начинающиеся на М и заканчивающиеся на Н, т.е. Мандарин, Мангостин и т.д. |
| =*н??а | все слова оканчивающиеся на А, где 4-я с конца буква Н, т.е. Брусника, Заноза и т.д. |
| >=э | все слова, начинающиеся с Э, Ю или Я |
| <>*о* | все слова, не содержащие букву О |
| <>*вич | все слова, кроме заканчивающихся на вич (например, фильтр женщин по отчеству) |
| = | все пустые ячейки |
| <> | все непустые ячейки |
| >=5000 | все ячейки со значением больше или равно 5000 |
| 5 или =5 | все ячейки со значением 5 |
| >=3/18/2013 | все ячейки с датой позже 18 марта 2013 (включительно) |
Тонкие моменты:
- Знак * подразумевает под собой любое количество любых символов, а ? - один любой символ.
- Логика в обработке текстовых и числовых запросов немного разная. Так, например, ячейка условия с числом 5 не означает поиск всех чисел, начинающихся с пяти, но ячейка условия с буквой Б равносильна Б*, т.е. будет искать любой текст, начинающийся с буквы Б.
- Если текстовый запрос не начинается со знака =, то в конце можно мысленно ставить *.
- Даты надо вводить в штатовском формате месяц-день-год и через дробь (даже если у вас русский Excel и региональные настройки).
Логические связки И-ИЛИ
Условия записанные в разных ячейках, но в одной строке - считаются связанными между собой логическим оператором И (AND):
Т.е. фильтруй мне бананы именно в третьем квартале, именно по Москве и при этом из "Ашана".
Если нужно связать условия логическим оператором ИЛИ (OR), то их надо просто вводить в разные строки. Например, если нам нужно найти все заказы менеджера Волиной по московским персикам и все заказы по луку в третьем квартале по Самаре, то это можно задать в диапазоне условий следующим образом:
Если же нужно наложить два или более условий на один столбец, то можно просто продублировать заголовок столбца в диапазоне критериев и вписать под него второе, третье и т.д. условия. Вот так, например, можно отобрать все сделки с марта по май:
В общем и целом, после "доработки напильником" из расширенного фильтра выходит вполне себе приличный инструмент, местами не хуже классического автофильтра.
Ссылки по теме
- Суперфильтр на макросах
- Что такое макросы, куда и как вставлять код макросов на Visual Basic
- Умные таблицы в Microsoft Excel
То, что искал!
Подскажите пожалуйста, как задать несколько отрицающих условий для одного столбца, связав их логическим И?
Например, когда в списке банковских услуг нужно показать НЕ *кредит* И НЕ *вклад*
Если записать в разных строках - фильтр считает их связанными логическим ИЛИ и показывает весь массив.
Спасибо за вопрос, кстати - сейчас допишу к статье этот момент.
Подскажите, пожалуйста, как результаты фильтра выкинуть на другой лист?
Уважаемый Николай! Как сделать чтобы он работал в книге с общим доступом?
Спасибо.
Сделал подобный "расширенный фильтр" для управления фильтром даты сводной таблицы, при вводе даты в ячейку EXCEL воспринимает ее в штатовском формате, как описано выше. Есть ли возможность изменить восприятие как дд/мм/гггг?
Очень надеюсь на Вашу помощь. И еще вопрос, я скачала надстройку PLEX, но не могу найти в ней такой же макрос или в надстройке его нет? И где можно почитать подробную помощь по надстройке, для меня это темный лес.
А насчет огромного количества ячеек - это только 10 часть всех данных, я их вынесла на отдельный лист. Но именно для этого мне нужен макрос. Я же вручную все это считать просто сдохну!
Еще не объясните, почему после использования каких-либо формул на странице в Excell , а затем их стирания, другие формулы и макросы перестают работать? Я не знаю, может это только у меня так? Но мне каждый раз приходится все копировать в новую книгу Excel для того, чтобы использовать новые формулы.
Подскажите, как правильно составить запрос: что бы отфильтровались значения, которые содержат английские буквы?
Спасибо!
Допилил его под себя и стало вообще отлично. Теперь он у меня по нажатию одной кнопки устанавливается на любой лист в любой файл. И той же кнопкой сносится, если больше не нужен
Этот макрос должен быть в личной книге макросов, чтобы запускаться в любой книге Excel.
И в центре управления безопасностью Excel должен быть открыт доступ к объектной модели проектов VBA.
Перед применением макроса нужно выделить заголовки таблицы (все или часть), которые будут скопированы в шапку расширенного фильтра.
Проверял работоспособность в Excel 2007 и 2010.
Ну и повесть макрос на кнопку или горячую клавишу - это совсем просто, если нужно.
Sub Расширенный_фильтр() 'Проверяем защищен ли проект VBA If ActiveWorkbook.VBProject.Protection = 1 Then MsgBox "Проект защищен. Создание расширенного фильтра невозможно": Exit Sub 'Количество строк расширенного фильтра kol = 6 'Проверяем установлин ли уже расширенный фильтр. Если да, то удаляем. If Rows(2).Interior.Color <> 49407 Then If Selection.Rows.Count > 1 Then MsgBox "Нужно выделить не более одной строки", vbCritical: Exit Sub 'Определяем границы выделенной области row1 = Selection.Row col1 = Selection.Column col2 = col1 + Selection.Cells.Count - 1 'Вставляем строчки для расширенного фильтра For i = 1 To kol Range("A1").EntireRow.Insert If i > 1 Then Rows(1).Interior.Color = 49407 Next i 'Копируем шапку Range(Cells(row1 + kol, col1), Cells(row1 + kol, col2)).Select Selection.Copy Cells(1, col1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False 'Формируем текст макроса MacroText = "Private Sub Worksheet_Change(ByVal Target As Range)" & Chr(13) MacroText = MacroText & "If Not Intersect(Target, Range(cells(2," & col1 & "),cells(" & kol - 1 & "," & col2 & "))) Is Nothing Then" & Chr(13) MacroText = MacroText & "On Error Resume Next" & Chr(13) MacroText = MacroText & "ActiveSheet.ShowAllData" & Chr(13) MacroText = MacroText & "cells(" & row1 + kol & "," & col1 & ").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=cells(1," & col1 & ").CurrentRegion" & Chr(13) MacroText = MacroText & "End If" & Chr(13) MacroText = MacroText & "End Sub" 'Добавляем макрос ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule.AddFromString (MacroText) Else: 'Удаляем строки расширенного фильтра Range("A1:A" & kol).EntireRow.Delete Application.CutCopyMode = False 'Удаляем макрос With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule If .Find("Worksheet_Change", 1, 1, .CountOfLines, 1) = True Then iStartLine = .ProcStartLine("Worksheet_Change", 0) iCountLines = .ProcCountLines("Worksheet_Change", 0) .DeleteLines iStartLine, iCountLines End If End With End If End SubУ вас сколько строк?
Range("I1:O2").Select Range("A1:G3000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _ ("I1:O2"), CopyToRange:=Range("I9:O3039"), Unique:=FalseПодскажите, пожалуйста, как переписать макрос , чтобы копировать результат в другое место, а не преобразовывать текущую таблицу?
Возможно ли такое?
<5 фильтрует
А как задать условие <5 и >=4 , что бы выдало все оценки между 4 и 5 ??????????
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Is Nothing Then On Error Resume Next ActiveSheet.Unprotect Password:="123" ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1").CurrentRegion ActiveSheet.Protect Password:="123" End If End SubПоэтому Ваш сайт для меня палочка выручалочка 8), а про PLEX и разговора нет. Зарубежные MVP правда тоже иногда выручают...но я их реже смотрю (по мере обновления))))
Так что спасибо Вам!
Большое спасибо за статью. Очень пригодилась в ежедневном использовании.
Возник один вопрос. Как автоматизировать фильтрацию по маске *дом*?. То есть фильтр выдает все варианты, где ДОМ может быть в любых вариациях и в любом месте, например, домовой, придомовой, дом и т.д.
Идею с макросом для фильтра решил использовать для облегчения работы сторонних пользователей с файлом отчетности.
А именно, есть ячейка, откуда через выпадающий список выбирается название товарной группы, эта же ячейка связана (является источником) с полем условия расширенного фильтра (фильтр на один столбец). Предполагается, что в данной ячейке выбирается товарная группа, и она ссылкой идет в поле расширенного фильтра, и таблица фильтруется. Но вот проблема: фильтр не срабатывает, когда выбираю ячейку и она автоматом подставляется в условие фильтра. Когда руками встаю в ячейку с условием и формулой и нажимаю Enter, то срабатывает, а когда выбираю, то не работает. Получается, что условие фильтра не реагирует на изменение состава ячейки согласно формулы?
Возможно ли что-то здесь исправить?
Ниже схематичный пример таблицы.
Правда у меня ещё одна проблема: К основному диапазону применено "форматировать как таблицу" и при ручном заполнении диапазона условий фильтр всегда выдаёт пустые строки, начинающиеся со следующей за последней строкой таблицы.
Возникла необходимость фильтрации данных через дробь, 40/20/10 (площадь квартиры) Есть ли способ отфильтровать по отдельности каждый критерий?
Ну например **/**/>=10
Спасибо.
Сравните оба варианта - свой и мой, разница в одной пустой строке.
У меня возникла необходимость использовать этот макрос для фильтрации записей по тегам, но пока что не получается. У меня есть таблица с контактами, в которой есть столбец "Группа". Так вот там могут быть значения вроде: "Семья", "Работа", "Друзья", "Отдых 2008" и т.д. Естественно, часть контактов можно отнести сразу к нескольким группам, для них значения будут следующими: "Семья, Работа", "Друзья, Отдых 2008", "Отдых 2008, Друзья, Работа" и т.д. Получается нечто вроде тегов к записям, т.к. каждому контакту может соответствовать сразу несколько групп (тегов).
Мне необходимо, чтобы можно было фильтровать такие записи сразу по нескольким группам. Сейчас это работает не всегда. Если вводить в одну строку группы через пробелы со звездочками перед и после названия, то результат будет адекватным, только если угадать порядок этих групп в контактах. Я же хочу добиться того, чтобы порядок не имел значения.
используя Критерий запросов !?
Огромная благодарность за данную статью! Очень помогает сэкономить время!
Но, прям было бы вообщеее супеееер, если бы:
выдаваемые значения сортировались в том же порядке, в котором вводятся данные наверху
То есть, я ввожу в B2,B3,B4 соответственно Банан, лук, персик
И в фильтруемой части (внизу) показывались бы сначала все строчки, которые содержат банан, затем все строчки, которые содержат лук, ну а затем персик.
Соответственно, если в нашей табличке только по одной строчке где содержатся банан, лук и персик, то в фильтре выдавалось бы 3 строчки именно в заданной последоватальности.
Может, помимо Николая кто подскажет - как бы это реализовать???
Спасибо!