Как вернуть фильтр в excel

Не работает фильтр в Excel: загвоздка, на которую мы часто не обращаем внимания

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

Итак, почему в Excel может не работать фильтр?

1. Есть проблема с совместимостью версий Excel;
2. Плохая структура таблицы (пустые строки и столбцы, нечеткие диапазоны, много объединенных ячеек);
3. Некорректная настройка фильтрации;
4. Фильтр по дате может не работать из-за того, что даты сохранены в виде текста;
5. У столбцов нет заголовков (как вариант, у части столбцов);
6. Наличие сразу нескольких таблиц на одном листе;
7. Много одинаковых данных в разных столбиках;
8. Использование нелицензионной версии Excel.

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

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

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

Проблема с совместимостью

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

Почему фильтр в Excel может не применяться? Все просто. В ранних версиях программы (до 2007 года), сортировка действовала только по 3 условиям. В Экселе же, выпущенном после 2007 года, насчитывается целых 64 условия. Неудивительно, что они не будут работать, если такую книгу открыть в «старушке».

Решение. Ничего не сохраняйте. Закройте книгу. Впредь работайте с ней только в актуальных версиях программы.

Некорректная структура таблицы

Постарайтесь «причесать» свою табличку:

• Удалите пустые строки. Система их воспринимает, как разрыв таблицы, что сбивает сортировку;
• Уберите объединенные ячейки (сведите их количество к предельно допустимому минимуму). Если фильтрация была настроена, когда клеточки «жили» по отдельности, после их слияния она может работать некорректно;
• Приведите структуру в четкий вид.

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

• Выключите фильтр («Главная» – «Сортировка и Фильтр» или «Ctrl+Shift+L»);
• Выделите весь диапазон ячеек (всю таблицу, вместе с шапкой);
• Снова поставьте фильтрацию, не снимая выделение;
• Готово. Должно работать, даже с пустыми строчками.

Неправильная настройка фильтрации

Актуально для вновь созданной сортировки. Рекомендуем все хорошенько проверить. А еще лучше, удалить сортер, который не работает, и поставить новый.

Меню сортировки находится тут:

«Главная» — «Сортировка и фильтры» — «Настраиваемая сортировка».

Дата сохранена в текстовом формате

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

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

• Выделите проблемный столбик;
• Щелкните по нему правой кнопкой мыши;
• Выберите пункт «Формат ячеек»;

• Установите «Дата»;
• Готово.

У столбцов нет заголовков

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

Несколько таблиц на одном листе

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

В разных столбцах много одинаковых данных

Старайтесь избегать подобной путаницы. Или «обзывать» содержимое ячеек по-разному. Например, в перечне проданного товара не стоит делать 5 одинаковых столбцов с названием «Джинсы». Вставьте рядом артикул или номер модели, укажите цвет или просто поставьте порядковый номер. Делов на две минуты, зато фильтрация будет работать правильно.

Нелицензионная версия Excel

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

Мы разобрали, почему фильтр в Эксель может быть не активен, вам осталось только найти свою причину. Есть еще одно универсальное решение. Срабатывает оно не всегда, но нередко. Попробуйте просто скопировать весь массив на другой лист. Или, что еще лучше, в другую книгу. Логичного объяснения тут нет, но метод, действительно, иногда работает. Пусть это будет ваш случай!

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

Иногда встречаются случаи, когда необходимо в уже готовую таблицу 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 заносим перечень адресов из словаря и пробежав по каждому из адресов, мы применяем объединение ячеек.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Повторное повторное фильтрация и сортировка или очистка фильтра

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

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

В этой статье

• Подробнее о повторном повторном повторном фильтрации и сортировке
• Повторное повторное фильтрация или сортировка
• Очистка фильтра для столбца
• Очистка всех фильтров и повторная отрисовка всех строк

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

Чтобы определить, применяется ли фильтр, обратите внимание на значок в заголовке столбца:

Стрелка вниз означает, что фильтрация включена, но фильтр не применяется.

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

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

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

• Данные были добавлены, изменены или удалены из диапазона ячеек или столбца таблицы.
• Фильтр является динамическим фильтром даты и времени, таким как «Сегодня»,«Наэтой неделе» или «Год к дате».
• значения, возвращаемые формулой, изменились, и лист был пересчитан.

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

Повторное повторное фильтрация или сортировка

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

• Чтобы повторно отфильтровать или отсортировать фильтр, на вкладке Главная в группе Редактирование нажмите кнопку Сортировка & фильтри выберите повторно.

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

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

Примечание: Если фильтр в данный момент не применен, эта команда недоступна.

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

• На вкладке Главная в группе Редактирование нажмите кнопку Сортировка & фильтри выберите очистить.

См. также

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