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

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

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

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

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

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

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

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Edge, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

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

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

Результат

Как показано на снимке экрана ниже, уникальные строки извлекаются из исходного диапазона данных.

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

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

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

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

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

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

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

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

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

Результат

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

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

Рассмотрев фильтрацию по нескольким критериям в одном столбце, теперь мы обратим внимание на фильтрацию по нескольким столбцам. В этом разделе вы узнаете, как применять несколько критериев к разным столбцам с использованием логики «И», «ИЛИ» и комбинированной логики «И/ИЛИ».

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

С логикой AND (соответствует всем критериям)

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

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

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

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

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

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

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

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

Результат

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

С логикой ИЛИ (соответствует любым критериям)

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

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

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

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

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

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

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

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

Результат

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

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

С И, а также с логикой ИЛИ

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

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

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

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

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

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

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

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

Результат

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

В этом примере расширенный фильтр вернет только учащихся с баллами выше 90 в классе A или учащихся с оценкой B в классе B.

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

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

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

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

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

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

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

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

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

Результат

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

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

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

Предположим, ваш набор данных находится в диапазоне A7:D17, и вы хотите отфильтровать эти данные на основе критериев, указанных в B2:D4, и извлечь только Фамилия, Счет и Класс столбцы. Вот как это сделать.

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

Ниже или рядом с вашим набором данных напишите заголовки столбцов, которые вы хотите извлечь. Это определяет диапазон «Копировать в», в котором будут отображаться отфильтрованные данные. В этом примере я ввожу Фамилия, Счет и Класс заголовки в диапазоне Ф7:Н7.

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

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

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

Результат

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

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

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