Русский Русская версия English English version

Фильтрация данных в Excel с использованием расширенного фильтра

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

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

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

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

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

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

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

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

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

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

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

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

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

Критерии.

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

Результат примера 2.

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

Критерий2.

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

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

Результат примера 3.

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

В табличку условий введем критерии отбора: ="=Набор обл.6 кл." (в столбец «Название») и ="<10" (в столбец «Цена»). То есть программа должна отобрать те значения, содержащие ТОЧНО информацию о товаре «Набор обл.6 кл.» ИЛИ информацию по товарам, цена которых <10.

Критерий3.

Обратите внимание: критерии необходимо записать под соответствующими заголовками в РАЗНЫХ строках.

Результат отбора:

Результат отбора.

Расширенный фильтр позволяет использовать в качестве критерия формулы. Рассмотрим пример.

Отбор строки с максимальной задолженностью: =МАКС(Таблица1[Задолженность]).

Задолжонность.

Таким образом мы получаем результаты как после выполнения несколько фильтров на одном листе Excel.

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

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

Несколько критериев.

Применим инструмент «Расширенный фильтр»:

Результат для примера.

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

Отгрузки за 2014 .

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

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

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

Стандартными способами – никак. Программа Microsoft Excel отбирает данные только в столбцах. Поэтому нужно искать другие решения.

Приводим примеры строковых критериев расширенного фильтра в Excel:

  1. Преобразовать таблицу. Например, из трех строк сделать список из трех столбцов и к преобразованному варианту применить фильтрацию.
  2. Отгрузки за 2014.
  3. Использовать формулы для отображения именно тех данных в строке, которые нужны. Например, сделать какой-то показатель выпадающим списком. А в соседнюю ячейку ввести формулу, используя функцию ЕСЛИ. Когда из выпадающего списка выбирается определенное значение, рядом появляется его параметр.

Чтобы привести пример как работает фильтр по строкам в Excel, создадим табличку:

Пример4.

Для списка товаров создадим выпадающий список:

Выпадающий список.

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

Формула.

Рядом с выпадающим списком ячейку введем следующую формулу: Ее задача – выбирать из таблицы те значения, которые соответствуют определенному товару

Формула2. Результат5.

Скачать примеры расширенного фильтра

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