Как использовать расширенный фильтр Excel — Полное руководство с примерами

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

• Извлечение уникального списка
• Фильтрация в одном столбце по нескольким условиям (соответствие любому из условий)
• Фильтрация в нескольких столбцах по нескольким условиям
  • С использованием логики И (соответствие всем условиям)
  • С использованием логики ИЛИ (соответствие любому из условий)
  • С использованием логики И и ИЛИ одновременно
• Расширенный фильтр с использованием подстановочных знаков
• Извлечение только определённых столбцов

Расширенный фильтр против обычного фильтра

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

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

Примеры использования расширенного фильтра

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

Извлечение уникального списка

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

1. Перейдите на вкладку «Данные» и выберите «Дополнительно» в группе «Сортировка и фильтр».
   
2. В диалоговом окне «Расширенный фильтр» выполните следующие настройки.
   1. В разделе «Действие» выберите нужный вариант. Поскольку я хочу разместить уникальный список в другом месте, выбираю опцию «Скопировать в другое место».
   2. Укажите диапазон списка:
      • Извлечение уникальных значений из одного столбца:
        Выберите столбец, из которого вы хотите извлечь уникальные значения. Например, чтобы получить уникальные имена клиентов, выделите диапазон A1:A11.
      • Извлечение уникальных строк на основе нескольких столбцов:
        Выделите диапазон, включающий все столбцы, которые вы хотите учитывать. В данном случае, чтобы извлечь уникальные строки по имени клиента, продажам и региону, выделите весь диапазон A1:C11.
   3. В разделе «Копировать в» укажите, куда вы хотите вставить уникальный список.
   4. Установите флажок «Только уникальные записи».
   5. Нажмите кнопку «ОК». Смотрите скриншот:
      

Результат

Как показано на скриншоте ниже, из исходного диапазона данных извлечены только уникальные строки.

Фильтрация в одном столбце по нескольким условиям (соответствие любому из условий)

Фильтрация данных в одном столбце по нескольким условиям позволяет отображать строки, которые соответствуют хотя бы одному из заданных критериев. Это особенно полезно при работе с большими наборами данных, когда необходимо сузить информацию по нескольким возможным совпадениям. Вот как это можно сделать с помощью расширенного фильтра Excel:

Шаг1: Подготовьте исходный диапазон данных

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

Шаг2: Настройка диапазона критериев

1. Над или рядом с исходным диапазоном создайте диапазон критериев. Заголовки в диапазоне критериев должны точно совпадать с заголовками в исходном диапазоне. В данном примере мой диапазон критериев расположен над исходным списком.
2. Под заголовком перечислите каждый критерий, который вы хотите учесть. Каждый критерий должен быть в отдельной ячейке, непосредственно под предыдущим. Такая настройка сообщает Excel, что нужно искать совпадения с любым из этих критериев.
   В этом примере я ищу студентов с «оценками больше95 или меньше60», чтобы отфильтровать как отличников, так и студентов с низкими баллами. Поэтому каждый критерий я ввожу в отдельную строку под заголовком «Оценка». Весь диапазон критериев показан ниже:
   

Шаг3: Примените расширенный фильтр

Теперь вы можете применить расширенный фильтр следующим образом.

1. Перейдите на вкладку «Данные» и выберите «Дополнительно» в группе «Сортировка и фильтр».
   
2. В диалоговом окне «Расширенный фильтр» выполните следующие настройки.
   1. В разделе «Действие» выберите нужный вариант. Поскольку я хочу разместить результат фильтрации в другом месте, выбираю опцию «Скопировать в другое место».
   2. В разделе «Диапазон списка» выберите весь диапазон A7:D17.
   3. В разделе «Диапазон условий» выберите весь диапазон критериев A2:D4.
   4. В разделе «Копировать в» укажите, куда вы хотите вставить результат фильтрации (в данном случае я выбираю ячейку F8).
   5. Нажмите «ОК», чтобы применить фильтр. Смотрите скриншот:
      

Результат

Теперь вы увидите, что извлечены только те строки, где в столбце «Оценка» значение соответствует хотя бы одному из критериев (>95 или <60).

Фильтрация в нескольких столбцах по нескольким условиям

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

• Для применения логики И разместите критерии в одной строке.
• Для применения логики ИЛИ разместите критерии в отдельных строках.

С использованием логики И (соответствие всем условиям)

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

Шаг1: Подготовьте исходный диапазон данных

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

Шаг2: Настройка диапазона критериев

1. Создайте диапазон критериев над или рядом с исходным диапазоном, введя заголовки, которые точно совпадают с заголовками в исходном диапазоне. В данном примере мой диапазон критериев расположен над исходным списком.
2. Для логики И все критерии размещайте в одной строке под соответствующими заголовками. Например, если я хочу отфильтровать студентов из «Класса А» с оценками выше85, диапазон критериев должен выглядеть так:
   

Шаг3: Примените расширенный фильтр

Теперь вы можете применить расширенный фильтр следующим образом.

1. Перейдите на вкладку «Данные» и выберите «Дополнительно» в группе «Сортировка и фильтр».
   
2. В диалоговом окне «Расширенный фильтр» выполните следующие настройки.
   1. В разделе «Действие» выберите нужный вариант. Поскольку я хочу разместить результат фильтрации в другом месте, выбираю опцию «Скопировать в другое место».
   2. В разделе «Диапазон списка» выберите весь диапазон A7:D16.
   3. В разделе «Диапазон условий» выберите весь диапазон критериев A2:D3.
   4. В разделе «Копировать в» укажите, куда вы хотите вставить результат фильтрации (в данном случае я выбираю ячейку F6).
   5. Нажмите «ОК», чтобы применить фильтр. Смотрите скриншот:
      

Результат

В результате будут отображены или скопированы только те строки, которые соответствуют всем условиям по указанным столбцам. В нашем примере будут извлечены только студенты из класса А с оценками выше85.

С использованием логики ИЛИ (соответствие любому из условий)

Чтобы отфильтровать данные в нескольких столбцах с использованием логики ИЛИ (соответствие любому из условий) в расширенном фильтре Excel, выполните следующие шаги:

Шаг1: Подготовьте исходный диапазон данных

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

Шаг2: Настройка диапазона критериев

1. Создайте диапазон критериев над или рядом с исходным диапазоном, введя заголовки, которые точно совпадают с заголовками в исходном диапазоне. В данном примере мой диапазон критериев расположен над исходным списком.
2. Для логики ИЛИ каждую группу условий для одного столбца размещайте в отдельных строках или вносите каждое условие в отдельную строку под соответствующим заголовком. Например, если я хочу отфильтровать студентов с оценками выше90 или с оценкой F, диапазон критериев должен выглядеть так:
   

Шаг3: Примените расширенный фильтр

Теперь вы можете применить расширенный фильтр следующим образом.

1. Перейдите на вкладку «Данные» и выберите «Дополнительно» в группе «Сортировка и фильтр».
   
2. В диалоговом окне «Расширенный фильтр» выполните следующие настройки.
   1. В разделе «Действие» выберите нужный вариант. Поскольку я хочу разместить результат фильтрации в другом месте, выбираю опцию «Скопировать в другое место».
   2. В разделе «Диапазон списка» выберите весь диапазон A7:D17.
   3. В разделе «Диапазон условий» выберите весь диапазон критериев A2:D4.
   4. В разделе «Копировать в» укажите, куда вы хотите вставить результат фильтрации (в данном случае я выбираю ячейку F8).
   5. Нажмите «ОК», чтобы применить фильтр. Смотрите скриншот:
      

Результат

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

В этом случае фильтр вернёт только студентов с оценками выше90 или с оценкой F.

С использованием логики И и ИЛИ одновременно

Чтобы отфильтровать данные в нескольких столбцах с использованием комбинации логики И и ИЛИ в расширенном фильтре Excel, выполните следующие шаги.

Шаг1: Подготовьте исходный диапазон данных

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

Шаг2: Настройка диапазона критериев

1. Создайте диапазон критериев над или рядом с исходным диапазоном. Включите заголовки столбцов, которые точно совпадают с заголовками в исходном диапазоне. В данном примере мой диапазон критериев расположен над исходным списком.
2. Под заголовками введите критерии, используя комбинацию логики И и ИЛИ.
   • Для логики И критерии из разных столбцов размещайте в одной строке.
   • Для логики ИЛИ критерии размещайте в отдельных строках.
   • Для комбинированной логики И-ИЛИ каждую группу условий ИЛИ оформляйте отдельным блоком строк, а внутри блока критерии И размещайте в одной строке.
     Например, чтобы отфильтровать студентов из класса А с оценками выше90 или из класса B с оценкой B, настройте диапазон критериев следующим образом:
     

Шаг3: Примените расширенный фильтр

Теперь вы можете применить расширенный фильтр следующим образом.

1. Перейдите на вкладку «Данные» и выберите «Дополнительно» в группе «Сортировка и фильтр».
   
2. В диалоговом окне «Расширенный фильтр» выполните следующие настройки.
   1. В разделе «Действие» выберите нужный вариант. Поскольку я хочу разместить результат фильтрации в другом месте, выбираю опцию «Скопировать в другое место».
   2. В разделе «Диапазон списка» выберите весь диапазон A7:D17.
   3. В разделе «Диапазон условий» выберите весь диапазон критериев A2:D4.
   4. В разделе «Копировать в» укажите, куда вы хотите вставить результат фильтрации (в данном случае я выбираю ячейку F8).
   5. Нажмите «ОК», чтобы применить фильтр. Смотрите скриншот:
      

Результат

Excel отобразит только те строки, которые соответствуют вашей сложной комбинации критериев.

В этом примере расширенный фильтр вернёт только студентов с оценками выше90 в классе А или студентов с оценкой B в классе B.

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

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

Шаг1: Подготовьте исходный диапазон данных

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

Шаг2: Настройка диапазона критериев

1. Создайте диапазон критериев над или рядом с исходным диапазоном. Включите заголовки столбцов, которые точно совпадают с заголовками в исходном диапазоне. В данном примере мой диапазон критериев расположен над исходным списком.
2. Под заголовком введите критерии с использованием подстановочных знаков.
   • *: Обозначает любое количество символов и может использоваться в начале, в конце или внутри строки.
   • ?: Обозначает один символ в определённой позиции.
   В этом примере я хочу отфильтровать имена, которые начинаются с буквы «J», поэтому ввожу J* под заголовком «Имя» в диапазоне критериев. Смотрите скриншот:
   

Шаг3: Примените расширенный фильтр

Теперь вы можете применить расширенный фильтр, чтобы отфильтровать все имена, начинающиеся с буквы J.

1. Перейдите на вкладку «Данные» и выберите «Дополнительно» в группе «Сортировка и фильтр».
   
2. В диалоговом окне «Расширенный фильтр» выполните следующие настройки.
   1. В разделе «Действие» выберите нужный вариант. Поскольку я хочу разместить результат фильтрации в другом месте, выбираю опцию «Скопировать в другое место».
   2. В разделе «Диапазон списка» выберите весь диапазон A6:B11.
   3. В разделе «Диапазон условий» выберите весь диапазон критериев A2:B3.
   4. В разделе «Копировать в» укажите, куда вы хотите вставить результат фильтрации (в данном случае я выбираю ячейку D7).
   5. Нажмите «ОК», чтобы применить фильтр. Смотрите скриншот:
      

Результат

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

Извлечение только определённых столбцов

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

Предположим, ваш набор данных находится в диапазоне A7:D17, и вы хотите отфильтровать эти данные по критериям из диапазона B2:D4 и извлечь только столбцы Имя, Оценка и Оценка (Grade). Вот как это сделать.

Шаг1: Укажите столбцы для извлечения

Под или рядом с вашим набором данных напишите заголовки столбцов, которые вы хотите извлечь. Это определяет диапазон «Копировать в», куда будут помещены отфильтрованные данные. В этом примере я ввожу заголовки Имя, Оценка и Оценка (Grade) в диапазон F7:H7.

Шаг2: Примените расширенный фильтр

Теперь вы можете применить расширенный фильтр, чтобы отфильтровать только определённые столбцы по заданным критериям.

1. Перейдите на вкладку «Данные» и выберите «Дополнительно» в группе «Сортировка и фильтр».
   
2. В диалоговом окне «Расширенный фильтр» выполните следующие настройки.
   1. В разделе «Действие» выберите опцию «Скопировать в другое место».
   2. В разделе «Диапазон списка» выберите весь диапазон A7:D17.
   3. В разделе «Диапазон условий» выберите весь диапазон критериев A2:D4.
   4. В разделе «Копировать в» выберите диапазон (в данном случае F7:H7), в который вы ввели заголовки нужных столбцов.
   5. Нажмите «ОК», чтобы применить фильтр. Смотрите скриншот:
      

Результат

Вы увидите, что в результате извлечения будут только указанные столбцы.

Примечания по расширенному фильтру

Связанные статьи