Очистка и удаление фильтра
Если определенные данные на нем не находятся, возможно, они скрыты фильтром. Например, если на вашем компьютере есть столбец с датами, в этом столбце может быть фильтр, ограничивающий значения определенными месяцами.
Существует несколько вариантов:
- Очистка фильтра из определенного столбца
- Очистка всех фильтров
- Удаление всех фильтров
Очистка фильтра из столбца
Нажмите кнопку Фильтр рядом с заголовком столбца и выберите очистить фильтр .
Например, на рисунке ниже показан пример очистки фильтра из столбца «Страна».
Примечание: Удалить фильтры из отдельных столбцов нельзя. Фильтры можно отключать для всего диапазона. Если вы не хотите, чтобы кто-то фильтрует определенный столбец, вы можете скрыть его.
Очистка всех фильтров на
На вкладке Данные нажмите кнопку Очистить.
Как узнать, что к данным был применен фильтр?
Если фильтрация применима к таблице на бумаге, в заголовке столбца вы увидите указанные ниже кнопки.
Фильтр доступен и не использовался для сортировки данных в столбце.
Фильтр используется для фильтрации или сортировки данных в столбце.
На следующем сайте фильтр доступен для столбца «Товар», но еще не использовался. Для сортировки данных использовался фильтр в столбце «Страна».
Удалите все фильтры на листе
Если вы хотите полностью удалить фильтры, перейдите на вкладку Данные и нажмите кнопку Фильтр или используйте клавиши ALT+D+F+F.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Фильтрация данных в Excel
Для обработки части большого диапазона данных можно воспользоваться фильтрацией. При фильтрации остаются видимыми только те строки, которые удовлетворяют заданным условиям, а остальные скрываются до тех пор, пока не будет отменен фильтр.
В Excel предусмотрено три типа фильтров:
- Автофильтр – для отбора записей по значению ячейки, по формату или в соответствии с простым критерием отбора.
- Срезы – интерактивные средства фильтрации данных в таблицах.
- Расширенный фильтр – для фильтрации данных с помощью сложного критерия отбора.
Автофильтр
- Выделить одну ячейку из диапазона данных.
- На вкладке Данные [Data] найдите группу Сортировка и фильтр [Sort&Filter].
- Щелкнуть по кнопке Фильтр [Filter] .
- В верхней строке диапазона возле каждого столбца появились кнопки со стрелочками. В столбце, содержащем ячейку, по которой будет выполняться фильтрация, щелкнуть на кнопку со стрелкой. Раскроется список возможных вариантов фильтрации.
- Выбрать условие фильтрации.
Варианты фильтрации данных
- Фильтр по значению – отметить флажком нужные значения из столбца данных, которые высвечиваются внизу диалогового окна.
- Фильтр по цвету – выбор по отформатированной ячейке: по цвету ячейки, по цвету шрифта или по значку ячейки (если установлено условное форматирование).
- Можно воспользоваться строкой быстрого поиска
- Для выбора числового фильтра, текстового фильтра или фильтра по дате (в зависимости от типа данных) выбрать соответствующую строку. Появится контекстное меню с более детальными возможностями фильтрации:
- При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… [Top 10…] и др.
- При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит. , начинается с… и др.
- При выборе опции Фильтры по дате варианты фильтрации – завтра, на следующей неделе, в прошлом месяце и др.
- Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… [Custom…], используя который можно задать одновременно два условия отбора, связанные отношением И [And] – одновременное выполнение 2 условий, ИЛИ [Or] – выполнение хотя бы одного условия.
Если данные после фильтрации были изменены, фильтрация автоматически не срабатывает, поэтому необходимо запустить процедуру вновь, нажав на кнопку Повторить [Reapply] в группе Сортировка и фильтр на вкладке Данные.
Отмена фильтрации
Для того чтобы отменить фильтрацию диапазона данных, достаточно повторно щелкнуть по кнопке Фильтр.
Чтобы снять фильтр только с одного столбца, достаточно щелкнуть по кнопке со стрелочкой в первой строке и в контекстном меню выбрать строку: Удалить фильтр из столбца.
Чтобы быстро снять фильтрацию со всех столбцов необходимо выполнить команду Очистить на вкладке Данные
Срезы
Срезы – это те же фильтры, но вынесенные в отдельную область и имеющие удобное графическое представление. Срезы являются не частью листа с ячейками, а отдельным объектом, набором кнопок, расположенным на листе Excel. Использование срезов не заменяет автофильтр, но, благодаря удобной визуализации, облегчает фильтрацию: все примененные критерии видны одновременно. Срезы были добавлены в Excel начиная с версии 2010.
Создание срезов
В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.
Для этого нужно выполнить следующие шаги:
-
Выделить в таблице одну ячейку и выбрать вкладку Конструктор [Design].
- В диалоговом окне отметить поля, которые хотите включить в срез и нажать OK.
Форматирование срезов
- Выделить срез.
- На ленте вкладки Параметры [Options] выбрать группу Стили срезов [Slicer Styles], содержащую 14 стандартных стилей и опцию создания собственного стиля пользователя.
- Выбрать кнопку с подходящим стилем форматирования.
Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.
Расширенный фильтр
Расширенный фильтр предоставляет дополнительные возможности. Он позволяет объединить несколько условий, расположить результат в другой части листа или на другом листе и др.
Задание условий фильтрации
Вначале надо скопировать шапку таблицы. Построить таблицу условий отбора данных можно либо на активном листе, либо на другом. Предпочтительнее на другом листе, иначе после фильтрации эти условия или их часть могут быть скрыты.
Записать условия фильтрации. Условия, записанные в одной строке, выполняются одновременно (как условие « И »), а в разных строках — как условие выбора (« ИЛИ »). В качестве условия может быть совпадение значения, которое заносится в ячейку, или сравнение с заданным в ячейке значением с помощью знаков или > . Если один столбец должен удовлетворять двум условиям, его заголовок нужно повторить еще раз и записать в этом столбце второе условие.
- В диалоговом окне Расширенный фильтр выбрать вариант записи результатов: фильтровать список на месте [Filter the list, in-place] или скопировать результат в другое место [Copy to another Location].
- Указать Исходный диапазон [List range], выделяя исходную таблицу вместе с заголовками столбцов.
- Указать Диапазон условий [Criteria range], отметив курсором диапазон условий, включая ячейки с заголовками столбцов.
- Указать при необходимости место с результатами в поле Поместить результат в диапазон [Copy to], отметив курсором ячейку диапазона для размещения результатов фильтрации.
- Если нужно исключить повторяющиеся записи, поставить флажок в строке Только уникальные записи [Unique records only].
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Как отфильтровать список элементов из длинного-длинного списка
Опубликовано 25.06.2020 Автор Ренат Лотфуллин
Каким образом можно найти и выделить полезную информацию из большой таблицы?! В Excel есть как минимум два метода. Первый заключается в использовании формул с функциями поиска частичного соответствия. Второй способ дает аналогичный результат, но не содержит формул – его мы и рассмотрим в сегодняшнем номере EXCELTIP. Добро пожаловать в Расширенный Фильтр, да детка!
Команда закреплена на вкладке Данные рядом с обычным Фильтром.
Задача — выделим из длинного списка только те строки, в которых встречается слово Фреза или Сверло
Итак, есть длинный неупорядоченный список товаров. Нужно получить из него список товаров, в названии которых есть слова «фреза» или «сверло». Самое простое и бесхитростное решение — воспользоваться штатным Фильтром.
…и теперь можно долго водить глазами по его списку, внимательно расставляя, один за другим, флажки.
Процедуру можно упростить, для этого Фильтр содержит поле с поиском.
… по ключевым словам Excel сам находит именно то, что нужно. Если ввести в поле поиска “фреза“, Фильтр покажет только те элементы, в которых есть это слово. Также можно добавлять новые поисковые запросы к предыдущим, установив флажок «добавить выделенный фрагмент в фильтр“.
Но и этот способ хорошо работает только при небольшом количестве значений фильтра. Если нужно отфильтровать список по, скажем, 10 и более значениям, то нужно повторять эти действия довольно долго и можно допустить ошибку. Этого я не люблю.
Для быстрой и безошибочной работы с длинной таблицей или списком отлично подходит команда Расширенный Фильтр.
Перед запуском команды задаётся диапазон условий. Это отдельный список имён, которые, собственно, требуется найти в таблице. В примере он установлен ячейками E2:E4
Диапазон условий должен включать названия столбцов!
Перейдите на вкладку Данные -> Сортировка и фильтр -> Дополнительно
В этом примере использую «Копировать в другое место». Конечно, можно было и «Фильтровать список на месте», то есть в исходном местоположении источника.
Неплохо, но так Расширенный Фильтр отобрал только те строки, которые начинаются с «фреза» или «сверло».
Чтобы результат включал строки, содержащие имя, используем простой приём в виде символа подстановки *. Заключим условия между двумя «звездочками» (*), тогда Excel интерпретирует их как всё, где встречается группа символов «фреза» или «сверло».
Однако и этого всё ещё недостаточно, т.к. критериям фильтра соответствуют и фрезы и бор-фрезы (которые нам не нужны), но как гласит легенда: «There can be only one».
Чтобы получить строки только со словом «фреза», воспользуемся ещё одной хитростью — поставим пробел до и после него.
Она заставляет Excel искать (пробел)фреза(пробел) и теперь «бор-фреза» не соответствует условиям фильтра. Хитрость основана на предположении, что каждое слово разделено пробелом и как следствие этого исключает из результата два возможных случая:
- Имена начинаются с «фреза»
- Имена заканчиваются на «фреза»
Чтобы учесть вышеупомянутые возможности, необходимо расширить диапазон критериев до E6, а именно:
E3- * фреза * ‘Содержит слово фреза (только отдельно и не в начале или конце)
E4 — *сверло* ‘Содержит слово сверло (в любом месте)
E5 — =»=фреза *» ‘Начинается со слова фреза
E6 — =»=*фреза» ‘Заканчивается словом фреза
Примечание: * в конце не обязательна. Она стоит там, чтобы было видно где кончается строка
Получение выражений для E5 и E6 в рамки статьи не входит. Достаточно изучить их конструкцию и, сделав несколько упражнений, пользоваться ими будет проще, чем два байта переслать.
Итак, если требуется отфильтровать список строк независимо от позиции в исходных данных, то есть на частичное совпадение, можно легко это сделать Расширенным фильтром. Нужно только помнить о положении символа * перед строкой в диапазоне условий, как здесь:
Вам также могут быть интересны следующие статьи
- Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel
- Создание списка уникальных значений в Excel с помощью формул массивов
- Расширенный фильтр в Excel с символами подстановки и уникальными значениями на VBA
- Импорт данных с Excel книги, находящейся на веб-сайте
- Формулы массивов в Excel — синтаксис формул массивов и массивов констант
- SkyDrive Excel или Как организовать одновременную работу нескольких людей в одном файле
- Именованные диапазоны в Excel — несколько трюков использования
- Гиперссылка в Excel — создание, изменение и удаление
- Инструмент Найти и заменить в Excel
- Группировка в сводных таблицах
Рубрика: Ввод данных | Метки: фильтрация | 2 комментария | Permalink
2 комментария
Ренат, приветствую!
Вот мне прям именно это и нужно — фильтровать по списку слов, но вот только не в Экселе, а в LibreOffice.
Там вроде все менюшки по аналогии с Экселем, но вот именно «Расширенный фильтр» выглядит по-другому, и у меня не получается перенести Ваш алгоритм туда =(
А почему вы не стали использовать: Фильтр — Текстовые фильтры — Содержит — «фраза»?
Вроде бы очевидный способ найти в столбце упоминания фразы
Добавить комментарий Отменить ответ
Рубрики
- Google Таблицы (3)
- PowerPivot (8)
- Без рубрики (1)
- Ввод данных (13)
- Визуализация (12)
- Дашборды (15)
- Диаграммы (16)
- Макросы/VBA (4)
- Математика (5)
- Надстройки (7)
- Основы (16)
- Сводные таблицы (10)
- Формат (3)
- Формулы (20)
Подписаться на e-mail рассылку
Популярные статьи
- Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel
- Как рассчитать коэффициент корреляции в Excel
- Несколько условий ЕСЛИ в Excel
- 14 способов вставки Excel, о которых вы должны знать
- Что такое стандартное отклонение — использование функции СТАНДОТКЛОН для расчета стандартного отклонения в Excel
- Как расчитать дисперсию в Excel с помощью функции ДИСП.В
- Печать таблицы в Excel — настройка области и границ печати Excel
- Поиск и выделение конкретных значений
- Как сравнить два столбца в Excel — методы сравнения данных Excel
- Четыре способа использования ВПР с несколькими условиями
Фильтры в таблицах
В Excel и подобных программах фильтры встроены в шапку таблицы. Такое расположение стало привычным для пользователей.
Этот способ фильтрации подходит для таблиц — то есть списков, где данные разделены по колонкам.
Описание работы
Фильтр можно собрать из разных контролов: чекбоксов, полей с датами, строки поиска. Разберем работу фильтра на примере набора чекбоксов.
Изначально фильтр не применен и чекбоксы в фильтре не проставлены. Когда пользователь выбирает один пункт, фильтр сразу включается — в таблице остаются только отмеченные варианты.
В таблицах Excel другой принцип: по умолчанию отмечены все галки. Чтобы отфильтровать по нескольким параметрам, придется сначала снять галку «Все», а потом выбрать нужное.
В наших сервисах более частый сценарий — отфильтровать список по 1–2 параметрам. Для этого описанный способ подходит лучше: чтобы получить желаемое, достаточно отметить нужные варианты.
После выбора фильтр не закрывается, и пользователь может продолжить отмечать пункты. Если фильтр применяется с задержкой, покажите лоадер поверх списка, но не перекрывайте лоадером фильтр.
Фильтр закрывается по клику в любом месте вокруг него.
Если фильтр сложно применять мгновенно, попробуйте поискать решение вместе с разработчиком. Например, если дело в нагрузке на сервис, можно отправлять запрос не после каждого клика, а с задержкой. Пока пользователь отмечает несколько пунктов, пауза будет незаметной, а сервис получит один запрос вместо нескольких.
Если при работе с таблицей чаще нужно «получить все варианты, кроме. », добавьте пункт «Все». Пользователь сможет поставить все галки, а потом убрать ненужные.
Отмеченный пункт «Все», не включает фильтр. В таком случае если закрыть фильтр и открыть снова — чекбоксы не будут отмечены.
Строка поиска в фильтре
Если список вариантов большой или постоянно меняется, добавьте строку поиска. Например, список из 5–10 организаций еще можно пролистать, но для большего количества удобнее использовать поиск.
При этом нехорошо показывать и скрывать поле поиска в зависимости от количества вариантов. Оно может часто меняться, когда пользователь редактирует справочник с этими значениями или добавляет и убирает строки из таблицы. Лучше, чтобы поле поиска было в таком фильтре всегда.
В открытом фильтре поле поиска сразу в фокусе.
Пересечение фильтров
Если пересечение фильтров пустое, покажите это сразу — заблокируйте пункт, который приведет к нулевому результату.
Например, для выбранных организаций нет требований, где квитанция отправлена. Тогда пункт «Отправлена» блокируем.
Составной фильтр
Фильтр можно составить из нескольких контролов.
У счетов-фактур уникальные номера, и поиск по номеру будет удобнее длинного списка чекбоксов — добавляем поле поиска. Те же счета-фактуры удобно фильтровать по периодам — добавим диапазон дат.
Составляйте фильтр, исходя из сценариев работы, не бойтесь комбинировать.
Результат применения фильтров и сброс
Покажите в интерфейсе, сколько элементов найдено, и дайте возможность сбросить фильтры. Поместите результат и кнопку сброса как можно ближе к данным. Хороший способ — поставить результат под шапку, но можно и в другие части таблицы.
Фильтры не сбрасываются, когда пользователь:
- переходит внутрь элементов списка и возвращается назад;
- обновляет страницу.
Фильтры сбросятся, если пользователь перейдет по навигации в другую часть системы или выйдет из сервиса.
Если пользователю необходимо сохранять примененные фильтры, добавьте такую возможность. Хороший способ — сохранять примененные фильтры в адресе. Тогда можно поделиться с коллегой ссылкой на отфильтрованный список.
Сортировка
Если в таблице присутствуют и фильтры, и сортировка, рекомендуем в обоих случаях показывать раскрывающийся список с вариантами. Так поведение по клику на заголовок будет единообразным. Кроме того, пользователь явно увидит, что означают иконки сортировки.
По клику на один из вариантов сортировка применяется, а раскрывающийся список закрывается. Иконка напротив заголовка колонки показывает направление сортировки.
Отдельный способ отменить сортировку не нужен, ее отменит сортировка по другой колонке.
Бывает, что строки изначально не отсортированы ни по одной колонке и этот порядок важен. Например, загруженные из 1С счета-фактуры. В таком случае добавьте третий пункт сортировки вроде «Порядок по умолчанию».
Если в таблице нет фильтров, и есть только сортировка, можно обойтись без раскрывающегося списка и фильтровать сразу по клику на заголовок:
Еще раз, в одной таблице нельзя комбинировать два способа. То есть если в таблице есть фильтр хотя бы у одной колонки, то у всех колонок по клику должен появляться раскрывающийся список.
Сортировка и фильтр
Одновременно к колонке можно применить и фильтр, и сортировку.
Дизайн и работа с клавиатурой
Иконка «стрелка вниз» подсказывает, что по колонке можно фильтровать или сортировать. По ховеру появляется серая плашка, а заголовок и иконка становятся темнее.
По клику появляется список вариантов. Серая плашка у заголовка остается, пока список раскрыт. Ховер и выделение не подсвечивают пункт меню под курсором, а влияют на вложенный элемент.
Пример
Есть список требований от налоговой. Новые требования всегда сверху — включена сортировка по дате.
Например, мы хотим посмотреть все требования о представлении пояснений
Видим, какой фильтр включен, результат поиска и можем сбросить фильтр.
При фильтрации список перестраивается, поэтому не страшно, что строка результата его сдвинет. Если при скроле шапка залипает, результаты не залипают вместе с ней.