Расширенный фильтр и немного магии
У подавляющего большинства пользователей 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
Николай, можно ли это перенести в диалоговое окно чтобы данные выводились на 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
Ремонт квартир
Ремонт квартир Москва
Ремонт квартир Ульяновск
и т.д.
При попытке воспользоваться расширенным фильтром для фильтрации слов, он ищет только среди тех слов, которые находятся в первом вхождении столбика, т.е. отыскивает только слово "Ремонт". Как сделать так, чтобы я могу найти и те слова, которые находятся в середине и в конце ключевой фразы?
И это одно из них...
Мда, так уж получилось, что мои мозги програмирование воспринимать отказываются органически, сколько ни пытался и Делфи и SQL, мертвый номер... В итоге достаточно сложная база данных работает на танцах с бубном в виде многоэтажных алгоритмов из макросов, гиперссылок и элементов управления. Максимум на что меня хватило это записать макросы встроенным ридером и чуток доработать напильником, пилой дружба и конфетами ириска ))) В общем все работает и доступно для модернизации своими силами, а это особенно важно когда ты в принципе не знаешь точно даже того чего хочешь от программы сегодня и тем более чего ты захочешь от нее завтра, писать при таких запросах тех. задание для профи смысла нет.. Такой вот магический элемент - то самое чего мне катастрофически не хватло для организации быстрого поиска по базе, пользовался встроенным поиском но долго и громоздко ((
СПАСИБО огромное Николай!!!
И ещё, Николай может Вы подскажете какая команда или набор команд может позволить реализовать такой алгоритм действия:
Просмотреть столбик А диапазона А1 если очередная ячейка не пустая то найти её содержимое в столбике А диапазона Б1 и все строки диапазона содержащие этот ключ перенести в диапазон С1 с привязкой к динамической ячейке сдвигающейся вниз после каждой вставки или просто в очередную свободную строку столбика допустим А в диапазоне С1
P. S. Или подскажите в каком разделе можно почитать на эту тему..., что то похожее нашёл там где рассматривается скрытие столбиков и строк по заданным параметрам, но все мои попытки сделать на их основе требуемый макрос увы успехом пока не увенчались...
макрос вроде бы работает, но только с текстовыми значениями, а вот как с его помощью фильтровать числовые значения? имеется таблица товарных поставок на 500000 строк в 50 колонках. нужно фильтровать по колонке "КОД ТОВАРА", в которой числовые значения от 4 до 10 знаков. например - нужно выбрать товары с кодами 66* и 7777* - но так не находит НИ ОДНОЙ строки. изменение типа ячеек на текстовые тоже не помогло. Что посоветуете?
И тогда можно будет по этому действительно текстовому столбцу использовать фильтры типа 777* и т.п.
как-то руками можно сделать?
столбцу ? Заранее благодарен ....
К примеру мне нужно вывести строки, в которых в данном столбце будут следующие значения 4556, 5425, 5789, 8563 и 9563.
Классный фильтр.
В однострочной шапке все работает отлично, а можно ли как-нибудь сделать так, чтобы фильтр работал с двух-трех строчной шапкой?
Пример шапки
Пробовал делать фильтр и по вашему скрипту и по автоматически создаваемому.
Как добавить диаграмму которая меняет свои показатели в зависимости появления списка?
Например, набираю текст "Арбузы" и смотрю график продаж за этот год, затем набираю "Дыни" и смотрю уже другой график
У меня возникли сложности с фильтрацией данных с помощью Расширенного фильтра.
Мне необходимо на отдельном листе с помощью расширенного фильтра выбрать строки со значениями больше, чем строки с другими значениями. Если конкретно, то выбрать те строки, где "план расходов" больше, чем "план продаж".
Попробовал в верхней (жёлтой) таблице, в столбце "Дата", поменять формат ячейки на "Дата" и можно вводить дату обычно-привычным способом (через точку или тире).
В ожидании новых уроков...
Как сделать что бы выборка данных была по нужному диапазону значений, а не конкретному значению?
Например, есть база в которой нужно отобразить людей по возрасту в пределах 20-50 лет. В идеале, чтобы можно было выбирать через выпадающий список нужное значение (20-50 лет, 18-55, до 60 ....). Буду очень благодарен!
Фильтр Павлова не запрещает использовать обычный фильтр8)
А как организовать чекбокс в выпадающем списке? Но что бы он искал не по точному совпадению текста в ячейче Есть колонка должности, но человек может числится на нескольких должностях. Разбивать на дополнительные колонки не вариант - если в первой колонке укажем Директор, во второй Сварщик, тогда при поиске можем не найти этого человека, если будем искать в обратном порядке (вбивать в первую Сварщик, а во вторую Директор). А таких колонок может быть до 5 ...
Может подскажете, как точно такое же реализовать в LibreOffice или лучше даже - в google таблицах?
Private Sub Worksheet_Change(ByVal Target As Range) 'срабатывает на любое изменение листа, передаёт переменную Target по значению If Not Intersect(Target, Range("A2:K6")) Is Nothing Then 'если нашлось хотя бы одно значение в пересечении диапазонов Target и "A2:K6" On Error Resume Next 'в случае ошибки следующей строки пропустить её ActiveSheet.ShowAllData 'снять все фильтры Range("A9").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1").CurrentRegion 'От указанной ячейки определяется диапазон данных и применяется расширенный фильтр End If 'Action определяет что фильтруем "на месте" End Sub 'CriteriaRange передается диапазон критериев, по которым будет фильтроваться таблицаПоставьте точку останова в теле процедуры и в окне отладки наберите:
? Target.Address
1. Excel действительно ориентирован на американский формат дат (что называется, "US-centric"). И вводить дату нужно полностью.
Ничего поделать с этим нельзя, кроме...
Наряду с форматом типа
12/24/2013
с переставленными месяцем и днем можно получить правильный порядок, только надо использовать английские аббревиатуры месяцев:
24-Dec-2013
Месяцы: Jan; Feb; Mar; Apr; May; Jun; Jul; Aug; Sep; Oct; Nov; Dec.
Например:
>=24-Dec-2013
Или вот так через слэш или дефис в обратном порядке:
>=2013/12/24
>=2013-12-24
Последний лучше. Установить формат для поля даты: ГГГГ-ММ-ДД
По крайней мере, без извращений.
2. Если надо фильтровать с учетом регистра символов, то для этого надо добавить дополнительное поле с именем, не совпадающим с именами полей базы данных, и использовать функцию СОВПАД().
Например,
Имя поля: Case-sensitive filter
Значение: =СОВПАД(В8;"лук")
где В8 - это ссылка на соответствующую ячейку первой строки данных в базе данных.
Найдет "лук", а не "Лук" и пр.