Как использовать расширенный фильтр 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. Вы хотите отфильтровать эти данные и оставить только столбцы «Имя», «Балл» и «Оценка». Ниже приведено пошаговое руководство, как это сделать.

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

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

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

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

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

Результат

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

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

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