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

Обновлено: 20.11.2024

выражение Выражение, возвращающее объект Range.

Параметры

Имя	Обязательный/Необязательный	Тип данных	Описание
Поле	Необязательно	Вариант	Целочисленное смещение поля, на котором вы хотите основывать фильтр (от левого края списка; крайнее левое поле — это поле номер один).< /td>
Критерий1	Необязательно< /td>	Вариант	Критерий (строка, например, "101"). Используйте " <>", чтобы найти непустые поля, и ">, чтобы выбрать поля (без данных) в типах данных.

Возвращаемое значение

Примечания

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

Excel для Mac не поддерживает этот метод. Поддерживаются аналогичные методы для Selection и ListObject.

В отличие от формул, в подполях не требуются скобки для включения пробелов.

Пример

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

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

В этом примере выполняется фильтрация таблицы Table1 на Sheet1 для отображения только тех записей, в которых значения поля 1 имеют "(отображаемое значение)", равное 1, 3, Seattle или Redmond.

К типам данных можно применять несколько фильтров SubField. В этом примере выполняется фильтрация таблицы Table1 на Sheet1 для отображения только тех записей, в которых значения первого поля содержат подполе, часовой пояс(а), где значением является тихоокеанский часовой пояс, а подполе с именем Date Founded равно 1851 или есть "(Нет данных)".

В этом примере выполняется фильтрация таблицы Table1 на Sheet1 для отображения первых 10 записей для первого поля на основе подполя Population.

В этом примере выполняется фильтрация таблицы Table1 на Sheet1 для отображения всех записей за январь 2019 г. и февраль 2019 г. для первого поля. Не обязательно должна быть строка, содержащая 31 января.

Поддержка и обратная связь

Есть вопросы или отзывы об Office VBA или этой документации? См. раздел Поддержка и отзывы об Office VBA, чтобы узнать, как получить поддержку и оставить отзыв.

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

Уровень квалификации: средний

Скачать файл

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

Руководство по автофильтрам VBA.xlsm (100,5 КБ)

Написание макросов для фильтров в Excel

Фильтры — отличный инструмент для анализа данных в Excel. Для большинства аналитиков и частых пользователей Excel фильтры являются частью нашей повседневной жизни. Мы используем раскрывающиеся меню фильтров, чтобы применить фильтры к отдельным столбцам в наборе данных. Это помогает нам связывать цифры с отчетами и проводить расследование по нашим данным.

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

В этой статье объясняется, как создавать макросы для автоматизации процесса фильтрации. Это подробное руководство по методу AutoFilter в VBA.

У меня также есть статьи с примерами для различных фильтров и типов данных, включая пустые поля, текст, числа, даты, цвета и значки, а также фильтры очистки.

Запись макросов — ваш друг (и враг)

Мы можем легко получить код VBA для фильтров, включив средство записи макросов, а затем применив один или несколько фильтров к диапазону/таблице.

Вот шаги для создания макроса фильтра с помощью средства записи макросов:

Включите запись макросов:

Вкладка "Разработчик" > "Записать макрос".
Дайте макросу имя, выберите место для сохранения кода и нажмите OK.

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

Код будет выглядеть примерно так.

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

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

Объяснение метода автофильтра

Метод AutoFilter используется для очистки и применения фильтров к одному столбцу в диапазоне или таблице в VBA. Он автоматизирует процесс применения фильтров через раскрывающиеся меню фильтров и делает все это за нас. 🙂

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

Написание кода автофильтра

Вот пошаговые инструкции по написанию строки кода для автофильтра

Шаг 1. Ссылка на диапазон или таблицу

Метод AutoFilter является членом объекта Range. Поэтому мы должны ссылаться на диапазон или таблицу, к которой применяются фильтры на листе. Это будет весь диапазон, к которому применяются фильтры.

Следующие примеры включают/отключают фильтры в диапазоне B3:G1000 на листе руководства по автофильтру.

Вот пример использования таблиц Excel.

Метод AutoFilter имеет 5 необязательных параметров, которые мы рассмотрим далее. Если мы не укажем ни один из параметров, как в примерах выше, то метод AutoFilter будет включать/выключать фильтры для указанного диапазона. Это переключатель. Если фильтры включены, они будут отключены, и наоборот.

Диапазоны или таблицы?

Фильтры работают одинаково как для обычных диапазонов, так и для таблиц Excel.

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

5 (или 6) параметров автофильтра

Метод AutoFilter имеет 5 (или 6) необязательных параметров, которые используются для указания критериев фильтрации для столбца. Вот список параметров.

Имя	Запрос/Опция	Описание
Поле< /td>	Необязательно	Номер столбца в диапазоне фильтра, к которому будет применяться фильтр. Это номер столбца в диапазоне фильтра, а НЕ номер столбца рабочего листа.
Criteria1	Необязательно	Строка заключенный в кавычки, который используется для указания критериев фильтра. Операторы сравнения могут быть включены для фильтров меньше или больше. Многие правила применяются в зависимости от типа данных столбца. См. примеры ниже.
Оператор	Необязательно	Указывает тип фильтра для различных типов данных и критериев, используя один из Константы кслаутофилтероператор. Подробный список см. на этой справочной странице MSDN, а также список в примерах макросов ниже.
Criteria2	Необязательно	Используется в сочетании с параметром Оператор и Критерием1, чтобы создать фильтры для нескольких критериев или диапазонов. Также используется для определенных фильтров даты для нескольких элементов.
VisibleDropDown	Необязательно	Отображает или скрывает кнопку раскрывающегося списка фильтра для отдельный столбец (поле).
Подполе	Необязательно	Пока не уверен…

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

Шаг 2. Параметр поля

Первый параметр — это Поле. Для параметра Поле мы указываем число, которое является номером столбца, к которому будет применяться фильтр. Это номер столбца в диапазоне фильтра, который является родительским для метода автофильтра. Это НЕ номер столбца на листе.

В приведенном ниже примере поле 4 – это столбец "Продукт", поскольку это четвертый столбец в диапазоне/таблице фильтра.

Фильтр столбцов очищается, когда мы указываем только параметр Поле и никакие другие критерии.

Мы также можем использовать переменную для параметра "Поле" и установить ее динамически. Я объясню это более подробно ниже.

Шаг 3. Параметры критериев

Есть два параметра, которые можно использовать для указания критерия фильтра: критерий1 и критерий2. Мы используем комбинацию этих параметров и параметра Оператор для разных типов фильтров. Здесь все становится сложнее, поэтому давайте начнем с простого примера.

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

Общие правила для Критериев1 и Критериев2

Значения, которые мы указываем для Criteria1 и Criteria2, могут оказаться сложными. Ниже приведены некоторые общие рекомендации о том, как ссылаться на значения параметра "Критерии".

Значение критерия – это строка, заключенная в кавычки. Есть несколько исключений, когда критерий является постоянным для периода даты и времени и выше/ниже среднего.
При указании фильтров для отдельных чисел или дат форматирование чисел должно соответствовать форматированию чисел, применяемому в диапазоне/таблице.
Оператор сравнения больше/меньше также заключен в кавычки перед числом.
Кавычки также используются для фильтрации пробелов «=» и непустых «<>».

Шаг 4. Параметр оператора

Что делать, если мы хотим выбрать несколько элементов из раскрывающегося списка фильтров? Или сделать фильтр по диапазону дат или чисел?

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

< /tr> < td >11 < /tr>

Имя	Значение	Описание
xlAnd	1	Включите критерии Criteria1 и Criteria2. Может использоваться для диапазонов дат или чисел.
xlBottom10Items	4	Отображаемые элементы с наименьшим значением (количество элементов, указанное в Criteria1).
xlBottom10Percent	6	Отображаемые элементы с наименьшим значением (процент указан в Criteria1).
xlFilterCellColor	8	Цвет заливки ячейки
xlFilterDynamic	Динамический фильтр, используемый для периодов выше/ниже среднего и даты
xlFilterFontColor	9	Цвет шрифта в ячейке
xlFilterIcon	10	Значок фильтра, созданный условным форматированием
xlFilterValues	7	Используется для фильтров с несколькими критериями, указанными с помощью функции Array.
xlOr	2	Включить Критерии1 или Критерии2. Может использоваться для диапазонов дат и чисел.
xlTop10Items	3	Отображаемые элементы с наибольшим значением (количество элементов, указанное в Criteria1).
xlTop10Percent	5	Отображаемые элементы с наивысшим значением (процент, указанный в Criteria1).

Вот ссылка на страницу справки MSDN, которая содержит список констант для перечисления XlAutoFilterOperator.

Оператор используется в сочетании с критериями Criteria1 и/или Criteria2, в зависимости от типа данных и типа фильтра. Вот несколько примеров.

Итак, это основы написания строки кода для метода AutoFilter. Это становится более сложным с различными типами данных. Поэтому ниже я привел множество примеров, содержащих большинство комбинаций Критериев и Операторов для различных типов фильтров.

Автофильтр НЕ является аддитивным

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

Это означает, что он НЕ является аддитивным. Следующие 2 строки НЕ будут создавать фильтр для Продукта 1 и Продукта 2. После запуска макроса столбец Продукт будет отфильтрован только для Продукта 2.

Если вы хотите применить фильтр с несколькими критериями к одному столбцу, вы можете указать это с помощью параметров "Критерии" и "Оператор".

Как динамически установить номер поля

Если мы добавим/удалим/переместим столбцы в диапазон фильтра, то номер поля для отфильтрованного столбца может измениться. Поэтому я стараюсь по возможности избегать жесткого кодирования числа для параметра Field.

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

Номер столбца будет найден каждый раз, когда мы запускаем макрос. Нам не нужно беспокоиться об изменении номера поля при перемещении столбца. Это экономит время и предотвращает ошибки (беспроигрышный вариант)! 🙂

Использование таблиц Excel с фильтрами

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

Нам не нужно переопределять диапазон в VBA, так как диапазон данных меняет размер (строки/столбцы добавляются/удаляются). На всю таблицу ссылается объект ListObject.
После применения фильтров легко обращаться к данным в таблице. Мы можем использовать свойство DataBodyRange для ссылки на видимые строки для копирования/вставки, форматирования, изменения значений и т. д.
У нас может быть несколько таблиц на одном листе и, следовательно, несколько диапазонов фильтров. С обычными диапазонами у нас может быть только один отфильтрованный диапазон на листе.
Проще написать код для очистки всех фильтров для таблицы.

Фильтры и типы данных

Параметры раскрывающегося меню фильтра меняются в зависимости от типа данных в столбце. У нас есть разные фильтры для текста, чисел, дат и цветов. Это создает МНОГО различных комбинаций операторов и критериев для каждого типа фильтра.

Я создал отдельные сообщения для каждого из этих типов фильтров. Посты содержат пояснения и примеры кода VBA.

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

Почему метод автофильтра такой сложный?

На создание этой публикации меня вдохновил вопрос Криса, участника курса VBA Pro. Комбинации Критериев и Операторов могут быть запутанными и сложными. Почему это?

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

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

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

Пожалуйста, оставьте комментарий ниже с любыми вопросами или предложениями. Спасибо! 🙂

Многие функции Excel также доступны для использования в VBA, и метод автофильтра является одной из таких функций.

Если у вас есть набор данных и вы хотите отфильтровать его по критерию, это легко сделать с помощью параметра "Фильтр" на ленте "Данные".

А если вам нужна более продвинутая версия, в Excel также есть расширенный фильтр.

Зачем тогда вообще использовать автофильтр в VBA?

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

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

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

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

В таком случае использование автофильтра VBA может ускорить работу и сэкономить время.

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

Это руководство охватывает:

Синтаксис автофильтра Excel VBA

Выражение. Это диапазон, к которому вы хотите применить автоматический фильтр.
Поле: [Необязательный аргумент] Это номер столбца, который вы хотите отфильтровать. Это считается слева в наборе данных. Поэтому, если вы хотите отфильтровать данные на основе второго столбца, это значение будет равно 2.
Критерий 1: [Необязательный аргумент] Это критерий, на основе которого вы хотите отфильтровать набор данных.
Оператор: [Необязательный аргумент] Если вы также используете критерий 2, вы можете объединить эти два критерия на основе оператора. Для использования доступны следующие операторы: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
Критерий2: [Необязательный аргумент]. Это второй критерий, по которому можно фильтровать набор данных.
VisibleDropDown: [Необязательный аргумент] Вы можете указать, хотите ли вы, чтобы значок раскрывающегося списка фильтра отображался в отфильтрованных столбцах или нет. Этот аргумент может быть ИСТИНА или ЛОЖЬ.

Кроме выражения, все остальные аргументы являются необязательными.

Если вы не используете какой-либо аргумент, он просто применит или удалит значки фильтра для столбцов.

Приведенный выше код просто применит метод автофильтра к столбцам (или, если он уже применен, он удалит его).

Это просто означает, что если вы не видите значки фильтров в заголовках столбцов, вы начнете их видеть, когда этот вышеприведенный код будет выполнен, а если вы их увидите, то они будут удалены.

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

Теперь давайте рассмотрим несколько примеров использования автофильтра Excel VBA, которые прояснят его использование.

Пример: фильтрация данных на основе текстового условия

Предположим, у вас есть набор данных, как показано ниже, и вы хотите отфильтровать его на основе столбца "Элемент".

Приведенный ниже код отфильтрует все строки, в которых есть элемент «Принтер».

Вышеприведенный код относится к Sheet1, а внутри него — к A1 (ячейке в наборе данных).

Обратите внимание, что здесь мы использовали Поле:=2, так как столбец элементов – это второй столбец в нашем наборе данных слева.

Теперь, если вы думаете, зачем мне делать это с помощью кода VBA. Это легко сделать с помощью встроенного фильтра.

Если это все, что вам нужно сделать, лучше использовать встроенный фильтр.

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

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

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

Пример: несколько критериев (И/ИЛИ) в одном столбце

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

Приведенный ниже код сделает это:

Обратите внимание, что здесь я использовал оператор xlOR.

Это говорит VBA использовать оба критерия и фильтровать данные, если выполняется любой из двух критериев.

Аналогичным образом вы также можете использовать критерии AND.

Например, если вы хотите отфильтровать все записи, в которых количество больше 10, но меньше 20, вы можете использовать следующий код:

Пример: несколько критериев с разными столбцами

Предположим, у вас есть следующий набор данных.

С помощью автофильтра вы можете одновременно фильтровать несколько столбцов.

Например, если вы хотите отфильтровать все записи, в которых элемент — "Принтер", а торговый представитель — "Марка", вы можете использовать следующий код:

Пример: фильтрация первых 10 записей с использованием метода автофильтра

Предположим, у вас есть приведенный ниже набор данных.

Ниже приведен код, который даст вам 10 лучших записей (на основе столбца количества):

В приведенном выше коде я использовал ActiveSheet. Вы можете использовать имя листа, если хотите.

Обратите внимание, что в этом примере, если вы хотите получить первые 5 элементов, просто измените число в Criteria1:="10" с 10 на 5.

Таким образом, для первых 5 элементов код будет таким:

Это может показаться странным, но независимо от того, сколько основных элементов вам нужно, значение оператора всегда остается xlTop10Items.

Аналогично приведенный ниже код даст вам 10 последних элементов:

А если вам нужны последние 5 элементов, измените число в Criteria1:="10" с 10 на 5.

Пример: фильтрация первых 10 процентов с помощью метода автофильтра

Предположим, у вас есть тот же набор данных (использованный в предыдущих примерах).

Ниже приведен код, который даст вам первые 10 процентов записей (на основе столбца количества):

В нашем наборе данных, поскольку у нас есть 20 записей, он вернет 2 верхние записи (что составляет 10 % от общего числа записей).

Пример: использование подстановочных знаков в автофильтре

Предположим, у вас есть набор данных, как показано ниже:

Если вы хотите отфильтровать все строки, название элемента которых содержит слово "Доска", вы можете использовать следующий код:

В приведенном выше коде я использовал подстановочный знак * (звездочку) до и после слова «Доска» (что является критерием).

Звездочка может обозначать любое количество символов. Таким образом, будет отфильтрован любой элемент, в котором есть слово «доска».

Пример: копирование отфильтрованных строк на новый лист

Если вы хотите не только фильтровать записи на основе критериев, но и копировать отфильтрованные строки, вы можете использовать приведенный ниже макрос.

Он копирует отфильтрованные строки, добавляет новый лист, а затем вставляет эти скопированные строки в новый лист.

Приведенный выше код проверяет, есть ли отфильтрованные строки в Sheet1 или нет.

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

И если есть отфильтрованные строки, он скопирует их, вставит новый рабочий лист и вставит эти строки на этот вновь вставленный рабочий лист.

Пример: фильтрация данных на основе значения ячейки

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

Как показано ниже:

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

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

Ниже приведен код, который это сделает:

Это код события рабочего листа, который выполняется только при изменении рабочего листа и целевой ячейке B2 (где у нас есть раскрывающийся список).

Кроме того, условие If Then Else используется для проверки того, выбрал ли пользователь «Все» в раскрывающемся списке. Если выбрано Все, отображается весь набор данных.

Этот код НЕ помещен в модуль.

Вместо этого его нужно поместить в бэкэнд рабочего листа, содержащего эти данные.

Вот шаги, чтобы поместить этот код в окно кода рабочего листа:

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

Включение/выключение автофильтра Excel с помощью VBA

При применении автофильтра к диапазону ячеек некоторые фильтры уже могут быть установлены.

Вы можете использовать приведенный ниже код, чтобы отключить любые предварительно примененные автоматические фильтры:

Этот код проверяет все листы и удаляет все примененные фильтры.

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

Приведенный выше код проверяет, установлены ли уже фильтры.

Если фильтры уже применены, он удаляет их, иначе ничего не делает.

Аналогично, если вы хотите включить автофильтр, используйте следующий код:

Проверить, применен ли автофильтр

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

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

Показать все данные

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

Приведенный выше код проверяет, имеет ли значение FilterMode значение TRUE или FALSE.

Если это правда, это означает, что был применен фильтр и он использует метод ShowAllData для отображения всех данных.

Обратите внимание, что при этом фильтры не удаляются. Значки фильтров по-прежнему доступны для использования.

Использование автофильтра для защищенных листов

По умолчанию при защите листа фильтры не работают.

Если у вас уже есть фильтры, вы можете включить автофильтр, чтобы убедиться, что он работает даже на защищенных листах.

Для этого установите флажок Использовать автофильтр при защите листа.

Хотя это работает, когда у вас уже есть фильтры, если вы попытаетесь добавить автофильтры с помощью кода VBA, это не сработает.

Поскольку лист защищен, он не позволяет запускать макросы и вносить изменения в автофильтр.

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

Это может быть полезно, если вы создали динамический фильтр (то, что я рассмотрел в примере — «Фильтровать данные на основе значения ячейки»).

Ниже приведен код, который защитит лист, но в то же время позволит вам использовать в нем фильтры, а также макросы VBA.

Этот код необходимо поместить в окно кода ThisWorkbook.

Вот шаги, чтобы поместить код в окно кода ThisWorkbook:

Как только вы откроете книгу и включите макросы, она автоматически запустит макрос и защитит Sheet1.

Однако перед этим будет указано «EnableAutoFilter = True», что означает, что фильтры будут работать и на защищенном листе.

Кроме того, для аргумента UserInterfaceOnly задается значение True. Это означает, что пока рабочий лист защищен, код макросов VBA будет продолжать работать.

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

Фильтрация данных в Excel — одна из самых важных и полезных функций Excel. Это позволяет очень быстро отображать подмножества данных и просматривать только нужную информацию. По сути, вы углубляетесь в просматриваемые данные, потому что можете сортировать несколько столбцов, а также несколько критериев.

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

Автофильтр в синтаксисе макросов Excel:

Диапазон("A1").Поле автофильтра:=2, Criteria1:="Север"

Диапазон. Это должно быть начало набора данных, который необходимо отфильтровать. Это не тот столбец, который вы хотите отфильтровать.
Поле. Это фактический столбец, для которого вы хотите выполнить фильтрацию. Это количество столбцов справа от самого левого столбца в наборе данных, а НЕ фактический номер столбца.
Критерии. Это текст, число или символ, по которым вы хотите отфильтровать набор данных. Оно должно быть заключено в круглые скобки, несмотря ни на что. Вы можете использовать все математические операторы сравнения внутри этих скобок при работе с числами (например, >, = и т. д.).

Пример набора данных для фильтрации в Excel:

столбец A

столбец B

Col C

Фильтрация по региону. Приведенный ниже код автоматически фильтрует только продавцов из северного региона.

Диапазон("A1").Поле автофильтра:=2, Criteria1:="Север"

Отфильтруйте набор данных, чтобы отобразить продавцов из регионов "Север" или "Юг", что означает, что будут отображаться продавцы из обоих регионов. Обратите внимание, что здесь используется оператор «xlor».

Диапазон("A1"). Поле автофильтра:=2, Критерий1:="Север", Оператор:= xlor, Критерий2:="Юг"

Отфильтруйте набор данных, чтобы отобразить продавцов, продажи которых превышают 50 000 долларов США И меньше 75 000 долларов США. При этом используется оператор «xland».

Диапазон("A1").Поле автофильтра:=3, Критерий1: , Оператор:= xland, Критерий2:

Этот макрос автофильтра отфильтрует набор данных, чтобы показать всех продавцов из региона "Север", продажи которых превышают 75 000 долларов США.

Диапазон("A1").Поле автофильтра:=2, Criteria1:="Север"

Диапазон("A1").Поле автофильтра:=3, Критерий1:

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

Вопрос? Спросите об этом на нашем форуме Excel

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков 50+ часов обучения 200+ руководств Excel

С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel. (Опыт работы с VBA не требуется.)

Курс VBA — от новичка до эксперта

Переименовать модуль для макроса Excel.
Учебное пособие. В этом совете по Excel показано, как переименовать модуль в Excel. Это очень важная вещь.

Добавить комментарии к ячейкам с помощью макроса Excel.
Макрос: добавьте комментарии к ячейкам в Excel с помощью этого макроса. Это позволяет быстро и легко добавлять .

Установка макроса в электронную таблицу Excel
Учебное пособие. Этот совет покажет вам, как скопировать макрос Excel в книгу или электронную таблицу. Вы ж.

Фильтрация данных в Excel – автофильтр
Макрос: этот бесплатный макрос Excel фильтрует данные в Excel с помощью функции автофильтра в макросе Excel.

Полное руководство по печати в макросах Excel — метод распечатки в Excel
Макрос: этот бесплатный макрос Excel иллюстрирует все возможные параметры и аргументы, которые вам нужны.

Автоматически запускать макрос через несколько секунд, минут или часов после открытия книги Excel
Макрос: запускать макрос по прошествии определенного времени с момента открытия книги Excel.

Подпишитесь на еженедельные уроки

БОНУС: подпишитесь сейчас, чтобы загрузить нашу электронную книгу Top Tutorials!

Ссылка на наши 15 лучших учебных пособий была отправлена вам, проверьте свою электронную почту, чтобы загрузить ее!

(Если вы не видите электронное письмо, проверьте папку "Спам" или "Промоакции" и обязательно добавьте нас в список контактов, чтобы получать наши электронные письма в будущем.)

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков
50+ часов видео
200+ руководств по Excel

Читайте также:

Продавцы	Регион	Продажи
Майкл Скотт	Север	58000
Энди Бернард< /td>	Юг	78456
Дуайт Шрут	Юг	56000
Джим Халперт	Восток	89000
Пэм Бизли Халперт	Запад	34566
Стэнли Хадсон	Запад	25678
Филлис Вэнс	Север	98000
Барт Симпсон	Восток	76000
Судья Дредд	Север	66000