Bazaprogram.ru

Новости из мира ПК
6 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Макрос фильтрации в excel

Канал в Telegram

Вы здесь

Расширенный фильтр в Excel

Диалоговое окно Расширенный фильтр может показаться сложным из-за наличия параметров:

Способ обработки исходных данных.

Чтобы показать результат фильтрации на месте, скрыв ненужные строки (Action:= xlFilterInPlace), выберите переключатель «фильтровать список на месте». Для копирования результата фильтрации в другой диапазон (Action:= xlFilterCopy) выберите «скопировать результат в другое место».

Исходный диапазон: диапазон, строки которого будем фильтровать.

Диапазон условий (CriteriaRange): диапазон с условиями для отбора значений.

Поместить результат в диапазон (CopyToRange): выходной диапазон (указываются только заголовки столбцов), если выбран способ обработки исходных данных «скопировать результат в другое место».

Только уникальные записи (Unique). Если нужно отобрать только уникальные значения из заданного диапазона, то устанавливаем флажок.

Воспользуемся расширенным фильтром с помощью интерфейса программы.

Исходная таблица содержит данные о поступлении товаров.

В условиях можно использовать знаки сравнения(>, =,

Отберем строки с датой в интервале с 13.08.2018 до 14.08.2018 и номенклатурой «Апельсин».

Условия в разных строках объединяются логической операцией ИЛИ.

Отберем строки с датой в интервале от 13.08.2018 до 14.08.2018 и номенклатурой («Апельсин» или «Лимон»).

Для фильтрации строк в другую область листа скопируем заголовок исходного диапазона

Заполним параметры расширенного фильтра

Команда Данные- Сортировка и фильтр – Дополнительно –Расширенный фильтр может быть выполнена методом VBA Range.AdvancedFilter

И в результате выполнения макроса

Фильтр не сработал? Дело в том, что в диапазоне условий есть условие для данных типа Дата (ячейки J2, J3, K2, K3).

Объекты Range и Cells имеют свойства Text, Value и Value2. Рассмотрим каждое из этих свойств

Text — позволяет получить значение ячейки диапазона в виде значения типа String

Value — позволяет получить и установить основное значение ячейки. Тип может быть разным

Value2 — то же, что и Value ,за исключением ячеек, отформатированных как Валюта и Дата

Вставим в ячейки M2 и M3 начальную и конечную дату периода для фильтрации данных

​Range(«M2″).Text=»13.08.2018» (тип String)

Range(«M2»).Value=#13.08.2018# (тип Date)
Range(«M2»).Value2=43325 (тип Double)

Для того, чтобы мы могли сравнить дату в исходном диапазоне с датой в диапазоне условий, воспользуемся свойством Value2. Подредактируем код макроса

Результат выполнения макроса

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

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

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

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

Основа

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

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

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

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

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

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

Читать еще:  Формат даты в excel

Добавляем макрос

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Функция натурального логарифма в excel

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

Макросы для фильтра сводной таблицы в Excel

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

Как сделать фильтр в сводной таблице макросом

Чтобы получить данные для одного из 4-х магазинов фирмы, достаточно лишь открыть выпадающий список со всеми магазинами и выбрать один из них. Пример результата этих действий изображено на рисунке:

Изменение настроек, которые доступны в интерактивных инструментах сводной таблице доступны так же на уровне программирования макросов из редактора VBA. Выбор магазина, который является элементом поля СТРАИЦЫ реализуется с помощью свойства CurrentPage.

Просто как параметр для этого свойства следует указать название поля. Например, напишем простой код макроса, который сам выберет «Магазин3» как критерий для фильтрования данных по оборотам в сводной таблице:

Sub Magazin3()
ActiveSheet.PivotTables( «ТаблицаМ» ).PivotFields( «Магазины» ).CurrentPage = «Магазин 3»
End Sub

Чтобы создать такой макрос сначала откройте редактор VisualBasic (ALT+F11), а потом создайте новый модуль в редакторе: «Insert»-«Module» и введете в него выше указанный VBA-код:

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

В методе PivotTables указан аргумент «ТаблицаМ» – это всего лишь ссылка на внутренне имя, которое было присвоено для сводной таблицы еще на этапе ее создания. Читайте пример создания где он детально описан: Макрос для создания сводной таблицы в Excel. Далее рассмотрим, как можно скрывать часть данных из области видимости значений.

Как скрыть столбец в сводном отчете макросом

Во многих ситуациях необходимо скрытие части данных, чтобы сосредоточить внимание аналитика на конкретно выбранной задачи или поставленной цели. Естественно сводная таблица обладает такой функцией без потери данных, а только лишь их визуальным скрытием. Чтобы скрыть в таблице данные за 2017 год с помощью макроса, введите в модуль следующий код:

Sub Hidden2017()
ActiveSheet.PivotTables( «ТаблицаМ» ).PivotFields( «Год» ).PivotItems( «2017» ).Visible = False
End Sub

Пример VBA-макроса в действии:

Чтобы снова включить (или сделать второй режим для переключателя скрыть/показать) в таблицу данные за 2017 год достаточно лишь в этом коде поменять параметр Fale на True.

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

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Расширенный фильтр в Excel с символами подстановки и уникальными значениями на VBA

Когда вы работаете с большими объемами данных, нельзя представить себе эту деятельность без фильтров. Однозначно, что в Excel разработан очень мощный и удобный инструмент фильтрации, который позволяет выбирать определенные данные из диапазона. А с приходом Excel 2007 добавилась возможность выбирать данные с определенной заливкой. Все это облегчает рабочие будни Excel юзеров и упрощает жизнь. Однако, есть еще один элемент, который пока еще не реализован в стандартных фильтрах Excel, но приятно было бы иметь в арсенале, я говорю о фильтрации с помощью символов подстановки, который мы попытаемся сегодня реализовать с помощью VBA.

Оригинальную статью можно найти на сайте http://yoursumbuddy.com/

В данной статье описан способ создания фильтра на основе формы ListBox на VBA. Фильтр использует оператор Like, для поиска соответствий в заданном диапазоне. К примеру, набрав в фильтре celti, программа вернет мне значение Exceltip, но это не самое главное, так как стандартный фильтр позволяет проводить подобные манипуляции. Гораздо интереснее, что оператор Like позволяет использовать символы подстановки, таким образом введя в текстовое поле значение /*/201? , Excel вернет все даты начиная с 2010 года. Плюс ко всему, данный фильтр позволяет задавать чувствительность к регистру и отбирать уникальные значения.

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

Читать еще:  Excel символ кавычки

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

Sub ResetFilter()
Dim rngTableCol As Excel.Range
Dim varTableCol As Variant
Dim RowCount As Long
Dim collUnique As Collection
Dim FilteredRows() As String
Dim i As Long
Dim ArrCount As Long
Dim FilterPattern As String
Dim UniqueValuesOnly As Boolean
Dim UniqueConstraint As Boolean
Dim CaseSensitive As Boolean

‘звездочка возвращает все значения списка
If Not Val >Then
Exit Sub
End If
FilterPattern = «*» & Me.txtFilter.Text & «*»

UniqueValuesOnly = Me.chkUnique.Value
CaseSensitive = Me.chkCaseSensitive

‘используется, если Уникальные значения равно ИСТИНА
Set collUnique = New Collection
Set rngTableCol = loActive.ListColumns(1).DataBodyRange
‘обратите внимание, функция Transpose не работает с больше, чем 65536 строчек
varTableCol = Application.WorksheetFunction.Transpose(rngTableCol.Value)
RowCount = UBound (varTableCol)
ReDim FilteredRows(1 To 2, 1 To RowCount)
For i = 1 To RowCount
If UniqueValuesOnly Then
On Error Resume Next
‘сброс цикла
UniqueConstraint = False
‘не добавляет, если не уникальное значение
collUnique.Add Item:= «test» , Key:=CStr(varTableCol(i))
If Err.Number <> 0 Then
UniqueConstraint = True
End If
On Error GoTo 0
End If

If Not UniqueConstraint Then
‘Оператор Like чувствителен к регистру,
‘поэтому необходимо использовать команду Lcase, если галка не стоит
If ( Not CaseSensitive And LCase(varTableCol(i)) Like LCase(FilterPattern)) _
Or (CaseSensitive And varTableCol(i) Like FilterPattern) Then
‘добавить в массив, если пункт из ListBox соответствует фильтру
ArrCount = ArrCount + 1
‘в ListBox есть скрытый столбец для нумерования элементов
FilteredRows(1, ArrCount) = i
FilteredRows(2, ArrCount) = varTableCol(i)
End If
End If
Next i
If ArrCount > 0 Then
‘удаляем пустые элементы массива
‘ListBox не может содержать более 65536 элементов
ReDim Preserve FilteredRows(1 To 2, 1 To Application.WorksheetFunction.Max(ArrCount, 65536))
Else
Erase FilteredRows
End If
If ArrCount > 1 Then
Me.lstDetail.List = Application.WorksheetFunction.Transpose(FilteredRows)
Else
Me.lstDetail.Clear
‘добавляем отдельно, если найден только один элемент
If ArrCount = 1 Then
Me.lstDetail.AddItem FilteredRows(1, 1)
Me.lstDetail.List(0, 1) = FilteredRows(2, 1)
End If
End If
End Sub

В этом макросе использована возможность элемента Collection хранить только уникальные значения. Если на форме установлена галочка Уникальные, то макрос проверит его прежде, чем поместит в массив ListBox.

Переменная FilterPattern имеет звездочки в начале и конце строки. Это позволяет находить записи внутри таблицы.

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

Private Sub lstDetail_Change()
GoToRow
End Sub

Sub GoToRow()
If Me.lstDetail.ListCount > 0 Then
Application. Goto loActive.ListRows(Me.lstDetail.Value).Range.Cells(1), True
End If
End Sub

Скорость макроса достаточно приемлема для таблиц с менее чем 10000 строками. Но даже с превышением этого порога, макрос будет работать, главное, чтобы число строк было менее 65536.

Для лучшего понимания прикладываю файл с макросом.

Вам также могут быть интересны следующие статьи

4 комментария

Статья хорошая, но практического применения не имеет.
1. Символы подстановки * и ? можно использовать в стандартом фильтре Excel 2010 и в формулах.
Например, в фильтре пишем *По?та России* и выводятся строки содержащие Почта России или Поста России.
Тоже самое с формулами:
=СУММЕСЛИМН(data!$L:$L;data!$A:$A;$N50;data!$C:$C;»*По?та России*»)
или так:
=СУММЕСЛИМН(data!$L:$L;data!$A:$A;ЕСЛИ($B$2=»Все поставщики»;»*»;$B$2))
2. Если бы уникальные значения выводились в диапазоне данных, а не в ListBox-е, еще можно думать о применении, хотя расширенный фильтр Excel 2010 прекрасно справляется и с этой задачей.

Только начал читать блог. Оказалось что я мало что знаю об Экселе. Подача материала радует, всё понятно.

День добрый Ренат,
подскажите пож-та как сделать такой же фильтр только который сразу находится на рабочем листе (отображает 10-15 первых значений) и имеет возможность фильтрации как у вас. то есть такой же фильтр только сразу на странице а не вызываемый через кнопку?

Добрый день! Возникла задача, не знаю как реализовать). Есть 2 массива данных. Первый: таблица из 2х столбцов: номер карты и дата издания (Список карт находящихся на судне). Второй массив это таблица вообще всех изданных карт. Содержит столбцы: номер карты, дата последнего издания, масштаб, название. Задача состоит в том чтобы по номерам карт из первого массива вытащить данные из второго массива. И если дата издания не совпадает(т.е. Вышла новое издание карты) пдсветить номер карты в первом массиве.

Ссылка на основную публикацию
Adblock
detector