Как закрепить фильтр в excel
Перейти к содержимому

Как закрепить фильтр в excel

  • автор:

Очистка и удаление фильтра

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

Существует несколько вариантов:

  • Очистка фильтра из определенного столбца
  • Очистка всех фильтров
  • Удаление всех фильтров

Очистка фильтра из столбца

Нажмите кнопку Фильтр

рядом с заголовком столбца и выберите очистить фильтр .

Например, на рисунке ниже показан пример очистки фильтра из столбца «Страна».

Кнопка

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

Очистка всех фильтров на

На вкладке Данные нажмите кнопку Очистить.

Кнопка

Как узнать, что к данным был применен фильтр?

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

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

Фильтр используется для фильтрации или сортировки данных в столбце.

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

Примененные фильтры

Удалите все фильтры на листе

Если вы хотите полностью удалить фильтры, перейдите на вкладку Данные и нажмите кнопку Фильтр или используйте клавиши ALT+D+F+F.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Как закрепить фильтр в excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Добавление фильтра в шапку таблицы

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

Давайте посмотрим на следующий пример:

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

И так, что бы нам пришлось сделать в таком случае, если бы мы делали всё вручную? Давайте составим пошаговый алгоритм:

  1. Выяснить, есть ли в шапке таблицы объединенные ячейки. Если есть, то необходимо определить, какие именно.
  2. Снять объединение со всех ячеек шапки таблицы (в нашем примере оно имеется в столбцах A, H, O)
  3. Выделить весь диапазон данных таблицы, включая нижнюю строку шапки (в нашем случае это диапазон [A3:O7])
  4. Применить фильтр
  5. Объединить те диапазоны ячеек, с которых ранее было снято объединение в п.1 (в нашем случае это [A1:A3],[H1:H3],[O1:O3])

Наверняка, многие сталкивались с такими ситуациями когда-нибудь. Но существует ли более быстрый и лёгкий способ сделать это? Давайте попробуем проделать то же самое с помощью т.н. макросов – кода на языке Visual Basic (for Applications).

Первое с чего начнём – создание процедуры в редакторе VBE. Назовем процедуру CreateHeadingFilter. Весь последующий код (за исключением отдельных функций и процедур) будем размещать именно в ней.

Теперь, давайте пройдем по вышеописанному алгоритму от п.1 до п.5 и попробуем проделать то же самое, только с помощью кода.

Выясняем наличие объединенных ячеек и сохраняем их адреса в память. На данном этапе мы будем использовать словарь (Scripting.Dictionary) из встроенной библиотеки “scrrun.dll”.

Dim dicAddr As Object Dim sh As Worksheet Dim vAddrList As Variant Dim rngWhole As Range Dim rngCell As Range Dim sAddress As String Set rngWhole = Selection Set dicAddr = CreateObject("Scripting.Dictionary") '//Reading current structure For Each rngCell In rngWhole With rngCell If .MergeCells Then sAddress = .MergeArea.Address If Not dicAddr.exists(sAddress) Then dicAddr.Add Key:=sAddress, Item:=vbNullString End If End If End With Next rngCell 

Перед запуском процедуры пользователь должен выделить шапку таблицы, в которой впоследствии будет установлен фильтр. Выделению мы присваиваем задекларированный диапазон rngWhole. Далее в цикле мы перебираем все элементы данного диапазона в поисках объединённых ячеек. Как только объединенные ячейки нашлись, их адрес записывается в текстовую переменную sAddress и добавляется в словарь. После этого переходим к действиям из п.2

  1. Отмена объединений ячеек во всей шапке таблицы Далее необходимо “разобъединить” выделенные ячейки (см. строка 9), адреса которых мы записали ранее в словарь. Также мы запомним в переменные границы шапки таблицы – для того, чтобы в дальнейшем понимать в какой строке у нас находится низ шапки таблицы и с какого по какой столбец необходимо проставлять фильтр
Dim sh As Worksheet Dim lRowHeading As Long Dim lRow As Long Dim iLCol As Integer Dim iRCol As Integer '//Unmerging selection With rngWhole .UnMerge lRowHeading = .Row + .Rows.Count - 1 iRCol = .Column + .Columns.Count - 1 iLCol = .Column Set sh = .Parent End With Set rngWhole = Nothing 

3-4. Установка фильтра в таблице Пункты 3 и 4 были выделены специально отдельными блоками, чтобы отделить сам процесс установки фильтров от подготовительных операций. При написании кода, т.к. мы заблаговременно сохранили основные сведения о границах шапки таблицы (столбец_начало, столбец_конец, строка_начало, строка_конец), то у нас часть работы отпадает и остается установить фильтр, как таковой.

Private Sub ApplyAutofilter(ByRef sh As Worksheet, _ ByVal LUpperRow As Long, _ ByVal iLeftCol As Integer, _ ByVal iRightCol As Integer) Dim lLowerRow As Long '//Setting filter With sh lLowerRow = .UsedRange.Rows.Count + .UsedRange.Row - 1 If .AutoFilterMode = True Then If .FilterMode Then: .ShowAllData .AutoFilterMode = False End If .Range(.Cells(LUpperRow, iLeftCol), .Cells(lLowerRow, iRightCol)).AutoFilter End With End Sub 

Так как при установке фильтра проверяются различные условия, не связанные с целью основного кода, то я выделил весь код, связанный с установкой фильтров в отдельную функцию ApplyAutofilter. К тому же данная функция может быть использована в дальнейшем в других ситуациях, потому как ни одна из её строк не специфична для конкретной книги, листа и т.д. – функция получает необходимые параметры и проставляет фильтр в диапазоне с заданными координатами. В данной функции хотелось бы отметить один момент – нахождение последней строки (переменная lLowerRow):

Почему-то, нигде в литературе и в интернете не встречал, чтобы при нахождении последнего рядка страницы добавляли бы “+ UsedRange.Row – 1“. Хотя на практике очень часто встречался с ситуациями, когда данные на листе начинаются не со строки №1, а допустим, с третьей. Тогда, если у нас, к примеру 10 строк данных, то конструкция UsedRange.Rows.Count (как обычно используется), вернет результат “10”, но последняя строка листа в действительности будет не десятая(!), а двенадцатая. Именно поэтому я рекомендую делать поправку на первую строку используемого диапазона и при нахождении номера последней строки всегда использовать конструкцию UsedRange.Rows.Count + UsedRange.Row – 1

  1. Объединение ячеек шапки таблицы Проделав все процедуры из пунктов 1-4 мы получили бы на выходе таблицу с фильтрацией, но с удручающим видом самой шапки таблицы – ранее объединенные ячейки теперь “не влазят” в таблицу и скрываются где-то между строками. На ничего не остается, как вернуть красивое форматирование таблице, к тому же, перечень ячеек, который мы “разобъединяли” уже сохранён в объекте словаря.
Dim vAddrList As Variant Dim j As Long If dicAddr.Count > 1 Then '//Merging cells in Heading area vAddrList = dicAddr.keys For j = LBound(vAddrList) To UBound(vAddrList) Set rngCell = sh.Range(vAddrList(j)) rngCell.Merge Next j End If 

В переменную vAddrList заносим перечень адресов из словаря и пробежав по каждому из адресов, мы применяем объединение ячеек.

После объединения всех кусочков кода в одно целое, и после добавления небольших оптимизаций и проверок, получим финальную версию кода (в текстовом файле в конце статьи). Вот, в принципе и всё! Ничего сверхъестественного или особо сложного здесь нет – всё делается довольно прямолинейно и быстро.

Готовый код можно скачать в прилагаемом текстовом файле:

Как закрепить фильтр в excel

Текст представляет собой адаптированный перевод статьи Annie Cushing (Энни Кашинг), оригинал — Advanced Filters: Excel’s Amazing Alternative To Regex. Курсивом выделяются названия пунктов меню для Excel 2013 и Mac 2011 или термины.

Всегда казалось странным, что Excel не поддерживает регулярные выражения (кои в среде программистов зовут регулярками или регэкспами). С их помощью делают сложную сортировку и фильтрацию. Инструмент SeoTools plugin for Excel поддерживает регулярки, но, как и большинство дополнительных ресурсов для Excel, работает только на платформе ПК. Эта статья предназначена, в основном, для пользователей Mac (мы не рекламируем SeoTools).

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

Немного теории

Всегда казалось странным, что Excel не поддерживает регулярные выражения (кои в среде программистов зовут регулярками или регэкспами). С их помощью делают сложную сортировку и фильтрацию. Инструмент SeoTools plugin for Excel поддерживает регулярки, но, как и большинство дополнительных ресурсов для Excel, работает только на платформе ПК. Эта статья предназначена, в основном, для пользователей Mac (мы не рекламируем SeoTools).

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

Подготовка к работе с расширенным фильтром

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

Расширенный фильтр находится:

Для ПК: Данные > Сортировка и фильтр >Дополнительно.

Для Мак: Данные > Сортировка и фильтр > Фильтр > Расширенный фильтр

Пользователи Mac могут щёлкнуть ПКМ по таблице и выбрать нужный пункт из контекстного меню. На ПК такой возможности нет.

Ещё один момент — база данных должна иметь заголовки столбцов, как показано ниже.

Небольшой совет

Подробная информация о фильтрах приведена ниже в пунктах Операторы, Заголовки и Множественные условия.

Базовые операторы

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

  • = равенство, т.е. левая и правая часть должна совпасть: =билет [включит все ячейки, содержащие билет]
  • <> не равно, левая и правая часть не должны совпасть: <> https://www.aviasales.ru/mag* [исключаем данный адрес (блог)]
  • ‘ апостроф преобразует формулу в текст, если поставить его первым в ячейке: ‘=купить авиабилет
  • > больше чем: >500
  • >= больше или равно: B4-C4>=3
  • < меньше чем: C6

Заголовки

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

Набор условий

Вот три базовых конструкции для множественных условий:

ИЛИ: Если имеется несколько критериев и отбор нужен при совпадении хотя бы одного, используется логический оператор ИЛИ. При использовании таблицы условий это достигается расположением критериев вертикально на разных строках. Приведённый пример демонстрирует это:

Фильтр отберёт строки, для которых выполняется хотя бы одно из условий, т.е. либо Позиция меньше 11, либо Количество больше 1000. Оператор ИЛИ можно использовать и для одного столбца. В таком случае, все критерии располагаются друг под другом в целевом столбце.

И: Если необходимо одновременное соблюдение всех критериев, применяется логический оператор И. Для этого критерии располагают на одной строке.

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

ОБА ОПЕРАТОРА: Можно блеснуть своим умением, применив оба оператора для установки критериев.

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

Примечание: Чтобы получить текст вместо формул, достаточно поставить апостроф перед операторами сравнения: ‘=купить или ‘<>самолёт. Майкрософт на своём сайте советует излишне сложный способ, предлагая заключить в двойные кавычки всё выражение, а перед ним ставить знак равенства: .

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

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

Эти условия отберут ключевые слова, для которых Среднемесячное количество находится в промежутке от 3700 до 10000.

Формулы: Это действительно мощный инструмент. Вместо таблицы критериев можно использовать формулы.

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

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

  • Формула должна возвращать логическое значение ЛОЖЬ или ИСТИНА;
  • Заголовки над формулами должны отличаться от заголовков базы данных, можно вообще использовать пустую строку;
  • Формула должна ссылаться на ячейки первой строки под заголовками базы данных;
  • Ссылки на проверяемые ячейки базы должны быть относительными: вида С4, при абсолютных, таких как $C$4 — значение формул будет статичным, т.е. всегда ЛОЖЬ или всегда ИСТИНА.

Общий алгоритм

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

Шаг 1: Как упоминалось ранее, щёлкните любую ячейку внутри подготовленного набора данных и перейдите:

Данные > Сортировка и фильтр >Дополнительно для ПК. Для Мак: Данные > Сортировка и фильтр > Фильтр > Расширенный фильтр.

Откроется меню. Здесь приведён скриншот Excel 2013, но все пункты совпадают с Mac.

Шаг 2: Выберите, где будут размещаться отобранные данные. В большинстве случаев разумнее определить «другое место», это оставит неизменным исходный массив данных. Но выбор за вами.

Шаг 3: Для исходного диапазона Excel по умолчанию отбирает всю «таблицу». Это можно изменить, исключив ненужные столбцы. Но Excel позволит выбрать только прямоугольный диапазон. Чтобы отобрать столбцы с пропусками, введите названия целевых столбцов в свободных ячейках и укажите их адрес для диапазона вывода. В Дополнительных примерах приводится подробное пояснение.

Шаг 4: Для установки диапазона условий щёлкните внутри поля и выделите соответствующий диапазон на листе. Если нужно свернуть меню, нажмите на значок выбора диапазона справа от поля.

Шаг 5: Если выбран пункт Cкопировать результат в другое место, укажите адрес в поле Поместить результат в диапазон. Можно указать единичную ячейку, тогда она станет верхней левой выводимого диапазона, или выбрать ячейки с введёнными названиями нужных столбцов.

Шаг 6: Если нужны только уникальные записи поставьте флажок. К сожалению, большинство людей используют исключительно эту функцию.

Шаг 7: Жмём OK, смотрим на результат.

Шаг 8: Если список фильтровался на месте, можно сбросить фильтр, нажав Очистить, но это верно для ПК. Данный пункт расположен выше пункта Дополнительно. На Маке присутствует аналог — Очистить фильтр, но он недоступен. Ещё один довод в пользу фильтрации на новое место. Чтобы сбросить фильтр нужно отменить действие — нажать Command-Z, или подключить стандартный фильтр.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *