Расширенный фильтр в excel: примеры. как сделать расширенный фильтр в excel и как им пользоваться?

Отмена расширенной фильтрации в Эксель

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

Передвигаемся в раздел под наименованием «Главная».
Находим блок команд «Фильтр».
Кликаем на кнопку «Очистить».

Второй метод, отменяющий расширенную фильтрацию:

Передвигаемся в раздел под наименованием «Главная».
Жмем левой клавишей мышки на элемент «Редактирование»
На следующем этапе раскрываем небольшой перечень «Сортировка и фильтр».
В возникшем контекстном меню кликаем ЛКМ на элемент под названием «Очистить».

1213

Фильтр в Excel

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

Фильтр устанавливается на заголовки и подзаголовки таблиц; главное, чтобы ячейки, на которые будет устанавливаться фильтр, не были пустыми. А располагается он в меню книги Excel на вкладке «Данные», раздел «Сортировка и фильтр»:

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

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

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

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

В нем можно задать следующие условия:

Условия «равно» и «не равно» пояснений не требуют, т.к. с ними все предельно ясно;
«больше», «меньше», «больше или равно» и «меньше или равно». Как строки можно сравнить друг с другом? Чтобы это понять, вспомните, каким образом Excel выполняет сортировку. Т.е. чем дальше в списке сортировки будет находиться строка, чем больше ее значение. Следующие утверждения являются истинными (верными): A А; А Значение», т.к. ничто в один и тот же момент не может быть и больше, и меньше одного и того же показателя.

При использовании «ИЛИ» должно соблюдаться хотя бы одно из заданных условий.

В самом конце окна пользовательского автофильтра приведена подсказка. Первая ее часть: «Знак вопроса »?» обозначает один любой знак …». Т.е. при проставлении условий, когда нельзя точно определить символ в конкретном месте строки, подставьте на его место «?». Примеры условий:

Начинается с «?ва» (начинается с любого символа за которым следуют символы «ва») вернет результаты: «Иванов», «Иванова», «кварц», «сват» и другие строки, подходящие под условие;
Равно «. » – вернет в результате строку, которая содержит 7 любых символов.

Вторая часть подсказки: «Знак »*» обозначает последовательность любых знаков». Если в условии невозможно определить, какие символы и в каком количестве должны находиться в строке, то вместо них подставьте «*». Примеры условий:

Заканчивается на «о*т» (заканчивается символами «о», после которого идет любая последовательность символов, затем символ «т») вернет результат: «пот», «торт», «оборот» и даже такой – «рвнщуооовиунистврункт».
Равно «*» – вернет строку, которая содержит хотя бы один символ.

Помимо текстовых фильтров бывают «Числовые фильтры», которые в основном принимают те же самые условия, что и текстовые, но имеют и дополнительные, относящиеся только к числам:

«Выше среднего» и «Ниже среднего» – возвращает значения, которые выше и ниже среднего значения соответственно. Среднее значение рассчитывается, исходя из всех числовых значений списка столбца;
«Первые 10…» – клик по данному пункту вызывает окно:

Здесь можно задать какие элементы выводить первые из наибольших или первые из наименьших. Также, какое количество элементов вывести, если в последнем поле выбран пункт «элементов списка». Если же выбран пункт «% от количества элементов», второе значение задает данный процент. Т.е. если в списке 10 значений, то будет выбрано самое высокое (или самое низкое) значение. Если в списке 1000 значений, то либо первые, либо последние 100.

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

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

Как извлечь значения, игнорируя пустые ячейки

Если исходный список содержит пустые ячейки, формула, которую мы только что обсудили, вернет ноль для каждой пустой строки, что может быть проблемой. Это вы и наблюдаете на скриншоте чуть выше. Чтобы исправить это, сделаем несколько небольших корректировок.

Формула массива для извлечения различных значений, исключая пустые ячейки:

Аналогичным образом вы можете получить список различных значений, исключая пустые ячейки и ячейки с числами:

Напоминаем, что в приведенных выше формулах A2: A13 – это исходный список, а B1 – ячейка прямо над первой позицией формируемого списка.

На этом скриншоте показан результат отбора:

Быть может, кому-то будет полезна еще одна формула –

Она работает с числами и текстом, игнорирует пустые ячейки.

Как пользоваться автофильтром

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

Выбор уникальных значений

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

Выделите нужный диапазон клеток.
Кликните на кнопку «Сортировка».
Выберите инструмент «Фильтр».

После этого вы увидите, что около первой ячейки появится символ выпадающего списка.
Кликните на него.
Благодаря этому появится список уникальных значений. Вам нужно поставить галочки около тех данных, которые должны выводиться на экран.
Чтобы сохранить изменения, необходимо нажать на «OK».

В качестве примера оставим все значения, кроме цифры «4».

Результат будет следующим.

Числовые фильтры

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

Работает это очень просто.

Кликаем на иконку около первой клетки.
Выбираем пункт «Числовые фильтры».
Указываем нужный критерий отбора информации.

В качестве примера выберем «больше или равно».

Благодаря этому откроется окно, в котором можно будет указать необходимые условия

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

В качестве примера укажем цифру «3».

Сразу после этого вы увидите, что все числа, которые были меньше 3, исчезли из поля видимости.

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

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

Кликните на иконку в первой клетке.
Выберите пункт «Удалить фильтр из столбца 1». В вашем случае название будет отличаться.
Благодаря этому книга примет прежний вид.

Настраиваемый фильтр

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

Нажмите на инструмент автофильтра.
Выберите пункт «Числовые фильтры».
Кликните на указанный вариант.

После этого вас попросят указать детали сортировки.
Раскройте список и прокрутите вниз. Вы увидите, что там есть такие условия, которые в меню изначально отсутствуют.
Затем нужно будет указать сам критерий отбора и сохранить фильтр нажатием на кнопку «OK».

Упорядочивание элементов

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

Нажимаем на указанную иконку.
Выбираем нужный вам способ для упорядочивания данных.

В результате этого вы увидите, что все данные перемешались согласно нашим указаниям. Мы в качестве примера выбрали вариант «по возрастанию».

Упорядочивание по цвету

Для демонстрации этой возможности нужно будет некоторые клетки выделить каким-нибудь цветом.

Затем порядок действий будет таков:

Снова нажимаем на иконку автофильтра.
Выбираем пункт «Сортировка по цвету».
Затем указываем нужный вам вариант.

Для более детальной настройки нужно будет выбрать пункт «Пользовательская сортировка».

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

Фильтр по цвету

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

Нажимаем на указанную иконку.
Выбираем «Фильтр по цвету».
Указываем какой-нибудь цвет.

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

Работа с датами

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

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

Выделяем наш диапазон ячеек.
Нажимаем на инструмент «Сортировка».
Выбираем вариант «Фильтр». В дальнейшем можете использовать горячие клавиши Ctrl+Shift+L.

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

Функция ФИЛЬТР Excel

Функция ФИЛЬТР фильтрует диапазон данных и динамически возвращает совпадающие результаты в соответствии с указанными вами критериями.

Примечание: Функция ФИЛЬТР доступна только в Excel для Microsoft 365, Excel 2021 или более поздних версиях Excel, а также в Excel для Интернета.

аргументы

массив (обязательно): Диапазон или массив для фильтрации.
включить (обязательно): Логический тест, который возвращает массив логических значений (ИСТИНА или ЛОЖЬ), указывающих, должна ли соответствующая строка или столбец в массиве проходить через ФИЛЬТР.
(необязательно): Возвращаемое значение, если во включенном массиве нет совпадающих результатов.

Примечания к функциям

Массив может быть предоставлен в виде строки значений, столбца значений или комбинации строк и столбцов значений.
Размеры включают должно быть равно значению массив аргумент.
Чтобы добавить два или более критериев в включают, следует заключить каждый критерий в скобки и соединить их звездочкой (*).
Результат, возвращаемый ФИЛЬТРОМ, будет распространяться на диапазон ячеек таким же образом, как ваши исходные данные. массив организован. Если одна или несколько ячеек в диапазоне сброса не пусты, #ПРОЛИВАТЬ! ошибка будет возвращена.
Если какое-либо из значений, предоставленных в качестве аргумента включения, является ошибкой или не может быть преобразовано в логическое значение, FILTER вернет ошибку.
Текстовые значения в включают or если_пусто аргументы должны быть заключены в двойные кавычки. В противном случае ФИЛЬТР вернет # ИМЯ? ошибка.
Чтобы ничего не отображалось при отсутствии соответствующих данных, укажите пустую строку («») для если_пусто.
ФИЛЬТР вернет #РАССЧЕТ! ошибка, если нет совпадающих данных и значения для если_пусто не предусмотрено.
ФИЛЬТР можно использовать между разными книгами. Однако убедитесь, что указанные книги открыты. В противном случае #REF! ошибка будет возвращена, если вы обновите ячейку результата.
Результаты FILTER являются динамическими, то есть автоматически обновляются при изменении значений в исходных данных. Однако результаты не будут обновлены, если вы добавите новые записи в массив. Чтобы исправить это, вы должны использовать структурированные ссылки как массив аргумент.

Пример

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

=ФИЛЬТР(B3: E14,(B3:B14=»Class A»)*(E3:E14>60),»НЕТ ЗАПИСЕЙ»)

Или используйте ссылки на ячейки, чтобы сделать формулу динамической:

=ФИЛЬТР(B3: E14,(B3:B14=H3)*(E3:E14>J3),»НЕТ ЗАПИСЕЙ»)

Пример отсутствия совпадений

Предположим, вы хотите отфильтровать учащихся из класса А и набрать менее 60 баллов (очевидно, нет баллов меньше 60), чтобы отобразить результаты как ничего или определенную текстовую строку, вы можете скопировать приведенные ниже формулы и Нажмите Enter чтобы увидеть результаты.

=ФИЛЬТР(B3: E14,(B3:B14=»Class A»)*(E3:E14<60),»») >>> Возврат ничего

=ФИЛЬТР(B3: E14,(B3:B14=»Class A»)*(E3:E14<60),»НЕТ ЗАПИСЕЙ») >>> Возврат НЕТ ЗАПИСЕЙ

Связанные функции

Функция SORT сортирует содержимое диапазона или массива в порядке возрастания или убывания.

Функция SORTBY сортирует содержимое одного диапазона или массива на основе значений в соответствующем диапазоне или массиве, независимо от того, включен ли соответствующий диапазон или массив в диапазон или массив для сортировки или нет.

Функция UNIQUE извлекает уникальные значения из диапазона данных. Данные могут включать текст, числа, даты, значения ошибок и т. д.

Базовые формулы для получения уникальных значений.

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

Уникальные значения — это значения, которые присутствуют в списке только один раз. Например:

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

Формула уникальных значений массива (заполняется нажатием Ctrl + Shift + Enter):

Можно воспользоваться и обычной формулой (вводится нажатием Enter):

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

A2: A10 – исходных перечень данных.
B1 — верхняя ячейка уникального списка минус одна строка. В этом примере мы начинаем создавать список уникальных в B2, и поэтому мы записываем B1 в формулу (B2 — 1 строка = B1). Если ваш список начинается, скажем, с ячейки C3, измените $B$1:B1 на $C$2:C2.

В этом примере мы извлекаем уникальные имена из столбца A (точнее из диапазона A2: A10), а следующий скриншот демонстрирует формулу в действии:

Вот наш порядок действий:

Измените любую из формул в соответствии с вашим диапазоном данных.
Введите ее в первую ячейку, с которой начнётся формирование списка (в данном примере B2).
Если вы используете формулу массива, нажмите . Если вы выбрали обычную, нажмите просто клавишу .
Скопируйте вниз настолько, насколько это необходимо, перетащив мышкой маркер заполнения. Поскольку обе формулы заключены в функцию ЕСЛИОШИБКА, вы можете скопировать вниз с запасом. Это не испортит ваши данные какими-либо ошибками, независимо от того, сколько уникальных значений было извлечено.

Отбор уникальных значений по условию.

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

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

В ячейке G2 указываем нужного нам заказчика, а в H2 записываем эту формулу массива:

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

Усложним задачу. Определим список не только для этого покупателя, но также и для определённого менеджера.

Вот наша формула массива:

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

В случае, если условий будет больше, нужно просто добавить соответствующий критерий в функцию ЕСЛИ и изменить число 2 на 3 или большее (в зависимости от количества условий).

Использование фильтра

Числовой

Применим «Числовой…»
к столбцу «Цена»
. Кликаем на кнопку в верхней ячейке и выбираем соответствующий пункт из меню. Из выпадающего списка можно выбрать условие, которое нужно применить к данным. Например, отобразим все товары, цена которых ниже «25»
. Выбираем «меньше».

В соответствующем поле вписываем нужное значение. Для фильтрации можно применять несколько условий, используя логическое «И»
и «ИЛИ»
. При использовании «И»
– должны соблюдаться оба условия, при использовании «ИЛИ»
– одно из заданных. Например, можно задать: «меньше»
– «25»
– «И»
– «больше»
– «55»
. Таким образом, мы исключим товары, цена которых находится в диапазоне от 25 до 55.

В примере у меня получилось так. Здесь отображены все данные с «Ценой»
ниже 25.

Текстовый

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

Оставим в таблице продукты, которые начинаются с «ка»
. В следующем окне, в поле пишем: «ка*»
. Нажимаем «ОК»
.

«*»
в слове, заменяет последовательность знаков. Например, если задать условие «содержит»
– «с*л»
, останутся слова: стол, стул, сокол и так далее. «?»
заменит любой знак. Например, «б?тон»
– батон, бутон, бетон. Если нужно оставить слова, состоящие из 5 букв, напишите «?????»
.

Вот так я оставила нужные «Названия продуктов»
.

По цвету ячейки

Фильтр можно настроить по цвету текста или по цвету ячейки.

Сделаем «Фильтр по цвету»
ячейки для столбика «Название продукта»
. Кликаем по кнопочке со стрелкой и выбираем из меню одноименный пункт. Выберем красный цвет.

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

По цвету текста

Теперь в используемом примере отображены только фрукты красного цвета.

Новосибирский
Государственный Университет
экономики
и управления

Кафедра
Экономической информатики

БОРИДЬКО
О.Н.

Методические
указания по выполнению
лабораторной
работы

Фильтрация
в Microsoft Excel 2007,функции базы данных

по
дисциплине «
Информатика

для
студентов 1 курса дневного
отделения
экономических специальностей

Новосибирск

Табличный
процессор
Microsoft

Excel 2007

Методические
указания к выполнению
лабораторной
работы № 3

”Фильтрация
в Excel,функции
базы данных”

1СПИСКИ
данных в EXCEL 3

2Фильтрация
данных в EXCEL 3

2.1Типы
критериев 3

2.1.1Критерии
на основе сравнения 4

2.1.2Критерии
в виде образца-шаблона 5

2.1.3Множественные
критерии на основе логических операций 5

2.1.4Вычисляемые
критерии на основе логических формул 5

3Средства
фильтрации 6

3.1Автофильтр 6

3.2Расширенный
фильтр 9

4ВСТРОЕННЫЕ
ФУНКЦИИ базы данных 15

5Вопросы
к защите лабораторной работы 18

СПИСКИ данных в
EXCEL

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

Таблица
в Excel
представляет собой однотабличную

базу

данных

.

В
Excel
базы данных называются списками

.

Список

– определенным образом сформированный
на рабочем листе Excel
массив данных со столбцами и строками.

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

,
а столбцы являются полями

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

Извлечь уникальные значения из диапазона.

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

Используем формулу массива

Здесь A2:C9 обозначает диапазон, из которого вы хотите извлечь уникальные значения. E1 – это первая ячейка столбца, в который вы хотите поместить результат. $2:$9 указывает на строки, содержащие данные, которые вы хотите использовать. $A:$C указывает на столбцы, из которых вы берёте исходные данные. Пожалуйста, измените их на свои собственные.

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

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

Как сделать автофильтр в Excel

Для включения необходимо щелкнуть в любом месте таблицы с данными, перейти на вкладку «Данные» — «Сортировка и фильтр». Нажать кнопку «Фильтр». Или нажмите комбинацию горячих клавиш CTRL+SHIFT+L.

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

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

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

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

Нажимаем кнопку автофильтра у заголовка столбца с числовыми значениями. Выбираем «Числовые фильтры» — раскрывается список условий.
Выберем, например, «больше или равно». Откроется пользовательский автофильтр в Excel.
Зададим в качестве критерия для фильтрации значение «3000». Введем эти цифры в поле напротив условия.
На листе останутся только те данные, которые соответствуют заданному критерию.

Чтобы снять фильтр, нажимаем на значок в заголовке столбца и выбираем «Снять фильтр…».

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

Нажимаем кнопку автофильтра у заголовка «Цена». В списке «Числовых фильтров» выбираем оператор «Первые 10».
Открывается меню «Наложение условия по списку». Определяемся, какие значения хотим видеть: наибольшие или наименьшие. С помощью счетчика задаем, сколько таких значений должно отобразиться в таблице.
Если отбор будет производиться по числам, то назначаем условие «Элементов списка». Если необходимо отфильтровать значения в процентах, то выбираем «% от количества элементов».
Чтобы закрыть окно и выполнить условие поиска, жмем ОК.

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

В качестве критерия можно задать несколько условий поиска.

Назначим фильтр для столбца «Дата»:

Нажимаем кнопку автофильтра. Открываем выпадающий список «Фильтры по дате».
Чтобы отобразить данные за второе полугодие 2014 г., назначим критерий «После». В открывшемся окне «Пользовательского автофильтра» для критерия «После» напишем условие «01.06.2014». Выберем функцию «И». Назначим второй критерий – «До». Условие – «31.12.2014». Дату можно вводить вручную, а можно выбрать в «Календаре».
После нажатия кнопки ОК пользователю становится доступна информация за второе полугодие 2014 года.

Отфильтруем текстовый диапазон ячеек:

Нажимаем кнопку у заголовка «Наименование». При наведении курсора на «Текстовые фильтры» откроется список доступных операторов сравнения, по которым можно сделать отбор.
Допустим, нам нужно отобразить информацию по товарам, в названии которых есть цифра «2». Выбираем критерий «Содержит». Вводим условие «2».
После нажатия ОК.

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

«?» — заменяет один любой знак. Условие для фильтрации данных в столбце «Наименование» — «Содержит «1?»:
«*» — заменяет несколько знаков.

В конце любого списка фильтров (числовых, текстовых, по дате) есть «Настраиваемый фильтр». Эта кнопка тоже открывает окно пользовательского автофильтра в Excel.

Как всё вернуть обратно

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

Используем знакомую нам иконку.
Убираем галочку около нужной информации.
Сохраняем нажатием на «OK».

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

Теперь снова выделяем наш диапазон клеток.
Кликаем на указанную кнопку.
Нажимаем на пункт «Фильтр»

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

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

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

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

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

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

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

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

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

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

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

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

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

Страница справки MSDN

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

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

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

В приведенном ниже примере поле 4 является столбцом
«Продукт», поскольку это 4-й столбец в диапазоне фильтра / таблице.

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

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

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

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

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

Общие правила для Criteria1 и Criteria2

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

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

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

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

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

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

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

Это основы написания строки кода для метода AutoFilter. Будет сложнее с различными типами данных.

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

Как исправить фильтрацию таблиц Excel?

1 Выберите весь столбец, чтобы применить фильтр к

Выберите весь столбец электронной таблицы таблицы, щелкнув букву столбца.
Щелкните вкладку Данные.
Затем нажмите кнопку «Фильтр», чтобы применить фильтр ко всему столбцу.
Нажмите кнопку со стрелкой фильтра ячеек, чтобы открыть параметры фильтрации, показанные непосредственно ниже.
Вы можете снять флажок «Пустые поля», чтобы не отображать пустые ячейки.

2 Удалите пустые ячейки из столбца таблицы

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

Выделите все строки пустых ячеек, удерживая клавишу Ctrl. Затем щелкните правой кнопкой мыши и выберите параметр «Удалить».

3 разгруппировать листы

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

4 Снять защиту листа

Выберите вкладку «Обзор» в Excel.
Нажмите кнопку Снять защиту листа.
Если открывается окно Снять защиту листа, введите пароль для рабочего листа в текстовое поле.
Щелкните кнопку ОК.

5 Разъединить ячейки

Нажмите горячую клавишу Ctrl + F.
Нажмите кнопку «Форматировать» в окне «Найти и заменить».
Щелкните Объединить ячейки на вкладке «Выравнивание», показанной непосредственно ниже.
Нажмите кнопку ОК.
Нажмите кнопку ” Найти все”
После этого в окне «Найти и заменить» будут перечислены все ссылки на ячейки с объединенными ячейками.

.
Чтобы разделить объединенные ячейки, выберите объединенную ячейку.
На вкладке «Главная» выберите параметр «Объединить и центрировать».
Выберите параметр «Разъединить ячейки».

6 Установите новый фильтр

Если в вашей таблице есть строки, которые не фильтруются, попробуйте настроить новый фильтр. Для этого выберите вкладку «Данные».
Нажмите кнопку «Очистить» в группе «Сортировка и фильтр».
Затем выберите курсором весь диапазон столбцов таблицы.
Нажмите кнопку «Фильтр» на вкладке «Данные» Excel.

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

Какое из решений решило для вас проблему профессиональной фильтрации Excel? Дайте нам знать, оставив сообщение в разделе комментариев ниже.

СВЯЗАННЫЕ СТАТЬИ ДЛЯ ПРОВЕРКИ:

Как исправить ошибку нарушения общего доступа в Excel
Как исправить ошибку “Файл не загружен полностью” в Microsoft Excel
Microsoft Excel не может добавлять новые ячейки? Ознакомьтесь с этими советами

Источник записи: windowsreport.com

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

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

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

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