Как отфильтровать столбцы в excel

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

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

Как сделать расширенный фильтр в Excel?

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

  1. задать более двух критериев отбора;
  2. скопировать результат фильтрации на другой лист;
  3. задать условие любой сложности с помощью формул;
  4. извлечь уникальные значения.

Алгоритм применения расширенного фильтра прост:

  1. Делаем таблицу с исходными данными либо открываем имеющуюся. Например, так:
  2. Создаем таблицу условий. Особенности: строка заголовков полностью совпадает с «шапкой» фильтруемой таблицы. Чтобы избежать ошибок, копируем строку заголовков в исходной таблице и вставляем на этот же лист (сбоку, сверху, снизу) или на другой лист. Вносим в таблицу условий критерии отбора.
  3. Переходим на вкладку «Данные» — «Сортировка и фильтр» — «Дополнительно». Если отфильтрованная информация должна отобразиться на другом листе (НЕ там, где находится исходная таблица), то запускать расширенный фильтр нужно с другого листа.

  • В открывшемся окне «Расширенного фильтра» выбираем способ обработки информации (на этом же листе или на другом), задаем исходный диапазон (табл. 1, пример) и диапазон условий (табл. 2, условия). Строки заголовков должны быть включены в диапазоны.
  • Чтобы закрыть окно «Расширенного фильтра», нажимаем ОК. Видим результат.
  • Верхняя таблица – результат фильтрации. Нижняя табличка с условиями дана для наглядности рядом.

    Как пользоваться расширенным фильтром в Excel?

    Чтобы отменить действие расширенного фильтра, поставим курсор в любом месте таблицы и нажмем сочетание клавиш Ctrl + Shift + L или «Данные» — «Сортировка и фильтр» — «Очистить».

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

    В таблицу условий внесем критерии. Например, такие:

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

    Для поиска точного значения можно использовать знак «=». Внесем в таблицу условий следующие критерии:

    Excel воспринимает знак «=» как сигнал: сейчас пользователь задаст формулу. Чтобы программа работала корректно, в строке формул должна быть запись вида: ="=Набор обл.6 кл."

    После использования «Расширенного фильтра»:

    Теперь отфильтруем исходную таблицу по условию «ИЛИ» для разных столбцов. Оператор «ИЛИ» есть и в инструменте «Автофильтр». Но там его можно использовать в рамках одного столбца.

    В табличку условий введем критерии отбора: ="=Набор обл.6 кл." (в столбец «Название») и =" ””;МАКС($A$1:A1)+1)’ >

    Рядом с выпадающим списком ячейку введем следующую формулу: МАКС($B$1:$O$1);"";ГПР(СТОЛБЕЦ(A:A);$B$1:$O$33;ПОИСКПОЗ($A$12;$A$4:$A$9;)+3))’ > Ее задача – выбирать из таблицы те значения, которые соответствуют определенному товару

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

    В этом курсе:

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

    Читайте также:  Как оцифровать видеокассеты через телевизор

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

    Фильтрация диапазона данных

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

    Выберите фильтр> данных .

    Щелкните стрелку в заголовке столбца.

    Выберите текстовые фильтры или Числовые фильтры, а затем выберите Сравнение, например между.

    Введите условия фильтрации и нажмите кнопку ОК.

    Фильтрация данных в таблице

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

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

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

    Нажмите кнопку ОК.

    Стрелка заголовка столбца превращается в значок фильтра . Щелкните этот значок, чтобы изменить или очистить фильтр.

    Статьи по теме

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

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

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

    Два типа фильтров

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

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

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

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

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

    Кнопка фильтра означает, что фильтр применен.

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

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

    Данные были добавлены, изменены или удалены в диапазон ячеек или столбец таблицы.

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

    Не используйте смешанные типы данных

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

    Фильтрация данных в таблице

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

    Выделите данные, которые нужно отфильтровать. На вкладке Главная нажмите кнопку Форматировать как таблицу и выберите команду Форматировать как таблицу.

    В диалоговом окне Создание таблицы вы можете выбрать, есть ли в таблице заголовки.

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

    Не устанавливайте флажок, если вы хотите, чтобы Excel Online добавить заполнители (которые можно переименовывать) над данными таблицы.

    Читайте также:  Как выиграть в игре клевер

    Нажмите кнопку ОК .

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

    Фильтрация диапазона данных

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

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

    На вкладке " данные " нажмите кнопку " Фильтр".

    Параметры фильтрации для таблиц и диапазонов

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

    Выбрав параметр Числовые фильтры вы можете применить один из перечисленных ниже настраиваемых фильтров.

    В этом примере, чтобы отобрать регионы, в которых сумма продаж за март была меньше 6000, можно применить настраиваемый фильтр:

    Вот как это сделать.

    Щелкните стрелку фильтра в ячейке со словом "Март", выберите пункт Числовые фильтры и условие Меньше и введите значение 6000.

    Нажмите кнопку ОК.

    Excel Online применяет фильтр и отображает только регионы с продажами ниже $6000.

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

    Удаление фильтра из столбца

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

    Удаление всех фильтров из таблицы или диапазона

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

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

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

    Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

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

    Простой фильтр в Экселе

    Рассмотрим по пунктам, как установить фильтр в Экселе по столбцам:

    1. Кликните по любой ячейке внутри таблицы (а лучше – выделите всю таблицу с шапкой)
    2. Нажмите на ленте Данные – Сортировка и фильтр – Фильтр
    3. В первой строке таблицы появятся кнопки со стрелками, обозначающие, что для столбца можно задать фильтр:

    Как теперь отфильтровать? Например, нужно в таблице примера выбрать всех людей из Алушты:

    1. В колонке «Город» нажмем на значок фильтра
    2. Откроется список, где галками отмечены все города, а также, форма поиска
    3. В поиске напишем «Алушта» и нажмем Ок

    Теперь у нас на экране только те строки, где город – Алушта. Остальные строки не удалены, а только скрыты.

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

    Чтобы повторно применить установленный фильтр, когда информация изменилась, нажмите Данные – Сортировка и фильтр – Повторить , или комбинацию клавиш Ctrl+Alt+L .

    А когда нужно сбросить все примененные фильтры, жмем Данные – Сортировка и фильтр – Очистить .

    Вы можете фильтровать в нескольких колонках одновременно, даже во всех!

    Текстовый фильтр в Эксель

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

    Читайте также:  Как набрать кавычки на клавиатуре ноутбука

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

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

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

    Расскажу, как поставить фильтр в Excel на два условия в одной ячейке. Для этого кликнем Текстовые фильтры – Настраиваемый фильтр .

    Пусть нам понадобилось отобрать людей с именем Богдан или Никита. Запишем логику, как на картинке

    А вот результат:

    Как определить, какой выбрать оператор сравнения, «И» или «ИЛИ»? Логика такая:

    • И – когда нужно, чтобы выполнились оба условия одновременно
    • ИЛИ – когда достаточно выполнения хотя бы одного условия из двух

    Больше про логические операторы вы можете прочесть в этой статье.

    Кроме того, в условии можно использовать операторы:

    • ? – это один любой символ
    • * – любое количество любых символов

    Например, чтобы выбрать ФИО, в котором присутствует строка «ктор», запишем условие так: *ктор*.

    Как поставить фильтр в Экселе на столбец с числами

    Числовые фильтры тоже можно гибко настраивать, есть такие способы отбора:

    • Равно или не равно
    • Больше, больше или равно, меньше, меньше или равно
    • Между (в интервале)
    • Первые 10
    • Выше среднего, ниже среднего

    Видите, что есть интересные опции. Давайте попробуем выбрать людей с продажами 200 тыс. – 500 тыс. Выберем пункт МЕЖДУ . Вот так:

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

    Фильтрация дат

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

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

    В группе команд « Фильтр по дате » содержится большой перечень популярных запросов:

    Чтобы настроить произвольный интервал, выберите пункт МЕЖДУ .

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

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

    Выберите свой вариант из списка.

    Расширенный фильтр в Excel

    Быстрый способ фильтрации предоставляет Расширенный фильтр. Заключается он в том, что вы создаете предварительную таблицу данных, которые нужно отобрать, а потом – основную. Поля шапки должны совпадать. Вот как это будет выглядеть, если нам нужно выбрать людей только из городов Агрыз и Азов:

    Теперь нужно сделать:

    1. Нажмите Данные – Сортировка и фильтр – Дополнительно
    2. В открывшемся окне выберите:
      • Исходный диапазон – таблица с данными
      • Диапазон условий – диапазон со значениями, которые нужно отфильтровать
      • Только уникальные записи – поставьте галку, если хотите, чтобы программа скрыла дубликаты
      • Обработка – выберите «скопировать результат в другое место», если хотите, чтобы отфильтрованные данные располагались в другом месте на том же листе
      • Нажмите Ок

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

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

      Дополним рассмотренный пример. Теперь нужно выбрать те же города, но записи, в которых продажи свыше 500 тыс. Условия будут такими:

      Снова применим расширенный фильтр, но в таблицу фильтров включим и новые колонки. Результат на картинке выше.

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

      Adblock
      detector