Чем расширенный фильтр отличается от автофильтра в Excel
Обновлено: 01.07.2024
Функция автофильтра Excel позволяет сужать данные на основе определенных критериев и извлекать записи, соответствующие этим критериям. Чтобы включить фильтр, выберите ячейки с данными, которые вы хотите отфильтровать, щелкните вкладку «Данные» на ленте и нажмите «Фильтр». Стрелки раскрывающегося списка появятся в заголовках столбцов, предоставляя вам возможность фильтровать данные на основе критериев, которые вы ищете.
В дополнение к автофильтру вы можете создать собственный фильтр с помощью инструмента расширенного фильтра. Расширенный фильтр позволяет создать уникальный список элементов и извлечь эти элементы в другое место на листе или в книге.
Расширенный фильтр имеет несколько полезных функций. Он позволяет одновременно применять несколько критериев фильтрации ко всему файлу данных, чего не делает автофильтр. Это также позволяет легко изменять критерии, вводя новые значения непосредственно в ячейки критериев. Кроме того, расширенный фильтр позволяет копировать отфильтрованные данные в указанную область на том же листе или на другом листе, а не просто фильтровать данные в исходном файле данных. Это может быть очень полезно при работе с большими файлами данных, такими как журнал оценок.
Вот пример использования расширенного фильтра. Предположим, вы хотите определить каждого учащегося с пятеркой за промежуточную оценку в разделе 1 вашего класса, чтобы вы могли отправить этим учащимся электронное письмо и пригласить их на банкет с отличием, но у вас есть объединенный журнал оценок. Вы можете использовать функцию расширенного фильтра, чтобы отфильтровать эту информацию и скопировать ее в отдельную область рабочего листа или новый рабочий лист для дальнейшего анализа.
Рекомендуется начать с вставки нескольких строк над набором данных. На снимке экрана ниже мы добавили семь строк над исходными данными.
Далее используйте пустые строки, чтобы настроить и применить критерии. Введите «Промежуточная оценка» и «Раздел» в две пустые ячейки, а затем введите «A» в ячейку под «Промежуточной оценкой» и «1» в ячейку под «Раздел», как показано на снимке экрана ниже. При настройке критериев мы предлагаем скопировать заголовки столбцов, которые вы хотите включить, прямо из исходного листа данных.
Теперь вам нужно определить результаты фильтрации, которые вы хотите отобразить. Результаты могут отображаться на том же рабочем листе или на другом рабочем листе в рабочей книге. Для простоты мы покажем вам, как отображать результаты поиска в одной таблице.
Для начала вам нужно указать нужные результаты фильтрации — в данном случае имя учащегося и адрес электронной почты учащегося — и указать, где информация должна отображаться в электронной таблице. Добавьте заголовки столбцов для имени учащегося и электронной почты учащегося справа от исходного файла данных, как показано на снимке экрана ниже. Затем нажмите кнопку "Дополнительно".
Появится всплывающее окно, в котором вас попросят указать диапазон списка, диапазон критериев и место, где должны отображаться результаты. Сначала нажмите стрелку раскрывающегося списка в разделе "Список диапазонов" и выберите диапазон данных.
Далее выберите диапазон критериев. Включите заголовки столбцов и критерии.
Наконец, укажите, где должны отображаться результаты. Выберите параметр «Копировать в другое место», выберите результаты фильтрации, которые вы хотите отобразить (в данном случае имя учащегося и адрес электронной почты учащегося), и место их отображения (в данном случае ячейки под именем учащегося и адресом электронной почты учащегося).< /p>
Нажмите ОК. Как видно из приведенного ниже снимка экрана, результаты показывают, что трое учащихся из Раздела 1 имеют промежуточную оценку A, и отображаются их имена и адреса электронной почты.
Вы когда-нибудь использовали функцию расширенного фильтра Excel? Или вся ваша фильтрация выполняется с помощью автофильтра?
Преимущества автофильтра
По простоте использования функцию автофильтра Excel трудно превзойти. Просто нажмите команду «Фильтр» на вкладке «Данные» ленты Excel, и фильтр готов к работе.
Вы можете щелкнуть стрелку раскрывающегося списка в заголовках столбцов и использовать флажки, чтобы выбрать элементы, которые вы хотите включить в фильтр. Или используйте команды Text, Date или Number Filters для вариантов фильтров.
Еще один быстрый способ фильтрации — щелкнуть правой кнопкой мыши ячейку в списке, затем нажать "Фильтр" и нажать "Фильтровать по значению выбранной ячейки".
Преимущества расширенного фильтра
Расширенный фильтр Excel не так прост в использовании (думаю, именно поэтому его называют расширенным 😉 ), но у него есть некоторые преимущества, которые оправдывают затраченные усилия.
После того как вы нажмете команду «Дополнительно» на вкладке «Данные» ленты Excel, откроется диалоговое окно «Расширенный фильтр». Заполните данные, а затем нажмите кнопку ОК, чтобы отфильтровать данные.
Отфильтровать данные на другой лист
Основная причина, по которой я использую расширенный фильтр, особенно при программировании в Excel, заключается в том, что это быстрый и эффективный способ скопировать определенные данные на другой лист.
На приведенном выше снимке экрана выбран вариант копирования отфильтрованных данных в другое место. Вы можете использовать этот параметр, чтобы быстро создать отчет для каждого отдела или продавца и отправить результаты по электронной почте.
ПРИМЕЧАНИЕ. Расширенный фильтр необходимо запускать с листа назначения. Нажмите на эту ссылку, чтобы просмотреть подробные инструкции и видео.
Создать список уникальных элементов
В Excel 2007 появилась функция удаления дубликатов, но я по-прежнему использую расширенный фильтр для создания списков уникальных элементов. Функция «Удалить дубликаты» удаляет дубликаты из выбранного списка, поэтому вам нужно помнить о работе с копией списка, если вы хотите сохранить исходный список нетронутым.
При использовании расширенного фильтра никакие элементы не удаляются из списка, поэтому вы не рискуете потерять исходные данные.
Сложные фильтры
С помощью расширенного фильтра вы можете создать диапазон критериев на листе и сразу увидеть настройки фильтра. Вы также можете создавать сложные фильтры с параметрами И/ИЛИ, которые выходят за рамки возможностей автофильтра.
Например, с помощью автофильтра вы можете выбрать двух конкретных клиентов и два продукта для этих клиентов.
С помощью расширенного фильтра вы можете создавать условия ИЛИ между столбцами, например, «Клиент А» ИЛИ «Продукт Б» — вы не можете сделать это в автофильтре!
С уважением,
Доминик.
@Dominik Спасибо за совет. Кажется странным, что нам приходится делать это только для того, чтобы скопировать данные на другой лист.
@Jon, похоже, вся твоя работа по программированию стоила затраченных усилий!
@Доминик, спасибо за ответ на вопрос.
@Грегори, рад, что вы поняли, как заставить его работать в вашем коде, и здесь есть короткое видео, показывающее шаги по ручной фильтрации на другой лист:
@Jon
Что касается вашего коммерческого продукта, я согласен, что предлагать код нехорошо.
Как я понимаю, решение VBA не готово к публикации для прямого использования другими.
Но - спасибо за ответ.
@Jon
Мы привыкли пользоваться инструментами каменного века! 🙂
Большое спасибо за ваш подход.
До этого блога я знал, что основное различие между автоматическим фильтром и расширенным фильтром заключается в том, что автоматический фильтр работает в тот же день, когда мы хотим получить результат, а расширенное поле дает возможность фильтровать данные в другом месте. Но сегодня я понял, почему это называется расширенным фильтром.
спасибо за этот пост
Здравствуйте, мне было очень полезно понять концепцию фильтра, и можно сделать два типа фильтров. Но вам
необходимо открыть диалоговое окно расширенного фильтра на целевом листе.
С уважением,
спасибо.
Спасибо, Сандер, я добавил примечание к этому разделу в сообщении блога.
Оставить ответ Отменить ответ
Этот сайт использует Akismet для уменьшения количества спама. Узнайте, как обрабатываются данные ваших комментариев.
Расширенный фильтр позволяет извлекать записи в другое место на том же рабочем листе или на другом листе рабочей книги. Это также позволяет использовать оператор «ИЛИ» в ваших фильтрах. (Пример: какие продажи составили менее 400 долларов «ИЛИ» больше 600 долларов). Автофильтр Excel фильтрует вещи на месте и не позволяет выполнять сложную фильтрацию. Чтобы изучить основы расширенной фильтрации, выполните следующие действия:
Шаг 1. Настройте базу данных
Расширенный фильтр использует концепцию базы данных. База данных обозначается как ряд меток со столбцами данных под каждой из этих меток. Каждый столбец содержит данные определенного типа, такие как дата, число или текст. Вот несколько правил настройки базы данных:
Первая строка вашей базы данных должна состоять только из ярлыков. Жирный шрифт помогает напомнить вам, что они не являются частью ваших данных.
Под каждым ярлыком указаны данные, которые он описывает.
Примечание. Убедитесь, что в базе данных нет пустых строк.
Убедитесь, что вокруг всей базы данных есть пустые ячейки (край страницы считается пустой ячейкой). См. изображение.
Шаг 2. Настройте диапазон критериев.
Диапазон критериев — это еще один способ сказать: «Что вы хотите отфильтровать (поискать)?». Вы устанавливаете правила для данных, которые останутся видимыми после применения фильтра. Вы можете использовать столько правил, сколько вам нужно. Мы проведем вас через процесс фильтрации, используя эту небольшую базу данных:
Примечание. Убедитесь, что автофильтр отключен и всегда есть пустая строка и столбец, отделяющие вашу базу данных от любого другого текста в электронной таблице. В противном случае расширенный фильтр будет считать, что это часть базы данных.
Настройка диапазона критериев.
Скопируйте и вставьте строку метки в другое место на листе. См. изображение.
Здесь нет заданного места, просто убедитесь, что у вас достаточно места, чтобы текст, который вы вводите, не попадал в строку или столбец, связанные с базой данных.
Шаг 3. Использование фильтров.
В расширенной фильтрации вы применяете правила, которые скрывают все, кроме того, что вы ищете. Он действует как очень подробный поиск, когда вы начинаете использовать Правила.
Фильтрация с использованием одного правила.
Сначала вам нужно установить диапазон критериев. В этом примере мы будем использовать диапазон критериев «Какие заказы были меньше 450 долларов США?».
В ваших фильтрах можно использовать следующие символы:
>= больше или равно
Нажмите в любой ячейке базы данных. См. изображение.
Перейдите: вкладка "Данные" > "Сортировка и фильтр" > "Дополнительно".
В открывшемся диалоговом окне «Расширенный фильтр» щелкните поле «Диапазон критериев». См. изображение.
Когда вы отпустите кнопку мыши, вокруг обеих ячеек должна появиться пунктирная линия, и снова появится диалоговое окно "Расширенный фильтр". Нажмите "ОК".
Представление базы данных немедленно изменится, и будут отображаться только те строки, в которых общая сумма меньше 450 долларов США. См. изображение.
Чтобы снова увидеть всю базу данных или выполнить другой процесс фильтрации, вам придется очистить представление вашего последнего фильтра. Вернитесь в раздел «Сортировка и фильтрация» на вкладке «Данные» и нажмите «Очистить». База данных вернется к исходному виду. См. изображение.
Фильтрация с помощью оператора "И".
Сначала вам нужно установить диапазон критериев. В этом примере мы будем использовать диапазон критериев «Какие заказы были у Сида И» Фреда на сумму менее 450 долларов США?».
В ваших фильтрах можно использовать следующие символы:
>= больше или равно
Щелкните левой кнопкой мыши в любой ячейке базы данных. См. изображение.
Перейдите на вкладку "Данные" > "Сортировка и фильтр" > "Дополнительно".
В открывшемся диалоговом окне «Расширенный фильтр» щелкните поле «Диапазон критериев». См. изображение.
Вернитесь к базе данных. В только что созданной области диапазона критериев нажмите «Клиент» и перетащите мышь, чтобы охватить все введенные вами правила. Диалоговое окно «Расширенный фильтр» исчезнет, и его место займет диалоговое окно «Расширенный фильтр — критерии». См. изображение.
Когда вы отпустите кнопку мыши, вокруг правил должна появиться пунктирная линия, и снова появится диалоговое окно «Расширенный фильтр». Нажмите "ОК".
Представление базы данных немедленно изменится, чтобы показать только те строки, в которых общая сумма Фреда "И" Сида меньше 450 долларов США. См. изображение.
Чтобы снова увидеть всю базу данных или выполнить другой процесс фильтрации, вам придется очистить представление вашего последнего фильтра. Вернитесь к сортировке и фильтру на вкладке «Данные» и нажмите «Очистить», база данных вернется к исходному виду. См. изображение.
Фильтрация с использованием операторов "И" и "ИЛИ".
Сначала вам нужно установить диапазон критериев. В этом примере мы будем использовать диапазон критериев «Какие заказы были дороже 400 долларов США, И” меньше 560 долларов США, “ИЛИ” больше 1000 долларов США?”.
В ваших фильтрах можно использовать следующие символы:
>= больше или равно
Давайте разберем вопрос, чтобы понять, как создать диапазон критериев.
Мы хотим выяснить, какие заказы стоили больше 400 долларов И меньше 560 долларов. Для этого добавьте еще одну метку столбца с тем же заголовком, что и метка столбца «И». В этом случае мы добавляем еще одну метку Total. См. изображение.
Используя ваши требования "И" поперек и требования "ИЛИ" вниз, введите диапазон критериев. Поместите> 400 в первый столбец «Итого» и 1000 в первый столбец «Итого». На изображении показано, как должен выглядеть ваш диапазон критериев после создания диапазона критериев. Щелкните в любом месте базы данных. См. изображение.
Перейдите на вкладку "Данные" > "Сортировка и фильтр" > "Дополнительно".
В открывшемся диалоговом окне «Расширенный фильтр» щелкните поле «Диапазон критериев». См. изображение.
Вернитесь к базе данных. В только что созданной области диапазона критериев нажмите «Всего» и перетащите мышь, чтобы охватить все введенные вами правила. Диалоговое окно «Расширенный фильтр» исчезнет, и появится диалоговое окно «Расширенный фильтр — критерии». См. изображение.
Когда вы отпустите кнопку мыши, вокруг правил должна появиться пунктирная линия, и снова появится диалоговое окно «Расширенный фильтр». Нажмите "ОК".
Представление базы данных немедленно изменится, и будут отображаться только те строки, в которых общая сумма больше 400 долларов США и меньше 560 долларов США или больше 1000 долларов США. См. изображение.
Чтобы снова увидеть всю базу данных или выполнить другой процесс фильтрации, вам придется очистить представление вашего последнего фильтра. Вернитесь в раздел «Сортировка и фильтрация» на вкладке «Данные» и нажмите «Очистить». База данных вернется к исходному виду. См. изображение.
Шаг 4. Настройте диапазон извлечения (основы).
Использование параметра "Извлечь диапазон" равносильно утверждению: "Я хочу, чтобы отфильтрованные данные отображались в другом месте". Вы можете указать столбцы, которые хотите извлечь. Если вы хотите извлечь ВСЕ столбцы, вы можете оставить диапазон извлечения пустым.
Фильтрация выполняется так же, как описано в разделе "Использование фильтров". На шаге 6 вступают в действие дополнительные шаги для отображения отфильтрованных результатов в другом месте. В этом примере мы будем использовать диапазон критериев: «Какие заказы были на сумму более 400 долларов США».
Введите >400 (знак $ не нужен) в поле "Итого" в разделе "Диапазон критериев". См. изображение.
выбрать любую ячейку в базе данных.
Перейдите на вкладку "Данные" > "Сортировка и фильтр" > "Дополнительно".
В открывшемся диалоговом окне «Расширенный фильтр» щелкните поле «Диапазон критериев». См. изображение.
Когда вы отпустите кнопку мыши, вокруг обеих ячеек должна появиться пунктирная линия, и снова появится диалоговое окно "Расширенный фильтр". В разделе «Действие» нажмите кнопку «Копировать в другое место». См. изображение.
В диалоговом окне "Расширенный фильтр" нажмите кнопку "Копировать в:". См. изображение.
Диалоговое окно «Расширенный фильтр» закроется, и откроется диалоговое окно «Расширенный фильтр — Копировать в:». Щелкните ячейку, в которой вы хотите отобразить отфильтрованный вывод, и нажмите кнопку «Расширенный фильтр — Копировать в:». См. изображение.
Когда откроется диалоговое окно «Расширенный фильтр», нажмите «ОК». Записи, соответствующие вашему диапазону критериев, будут отображаться, начиная с указанной вами ячейки. См. изображение.
Узнайте больше о фильтрации на другом листе, прочитав статью службы поддержки KB-909527, которая также применима к Excel 2010.
Узнайте больше о расширенной фильтрации, прочитав: Фильтрация по расширенным критериям
Узнайте больше о диапазонах критериев, прочитав: Примеры сложных критериев
Вы также можете получить помощь от сообщества Майкрософт в Интернете, найти дополнительную информацию в службе поддержки Майкрософт или в справке и инструкциях Windows или узнать больше о вариантах поддержки.
Для большей части фильтрации данных Автофильтр Excel вполне подходит. Здесь мы рассмотрим некоторые преимущества перехода от автофильтра к расширенному фильтру в Excel.
Обзор преимуществ автофильтра
По простоте использования функцию автофильтра Excel трудно превзойти. Просто нажмите команду «Фильтр» на вкладке «Данные» ленты Excel, и фильтр готов к работе.
Вы можете щелкнуть стрелку раскрывающегося списка в заголовках столбцов и использовать флажки, чтобы выбрать элементы, которые вы хотите включить в фильтр. Или используйте команды Text, Date или Number Filters для вариантов фильтров.
Еще один быстрый способ фильтрации — щелкнуть правой кнопкой мыши ячейку в списке, затем нажать "Фильтр" и нажать "Фильтровать по значению выбранной ячейки".
Преимущества использования расширенного фильтра в Excel
Расширенный фильтр в Excel не так прост в использовании, но у него есть некоторые преимущества, которые оправдывают затраченные усилия.
С помощью расширенного фильтра вы можете применять несколько критериев фильтрации ко всей базе данных, тогда как с другими типами фильтров вам нужно фильтровать поэтапно, что означает, что когда вы применяете один тип фильтра к данных, для запуска второго и третьего фильтров доступно меньше данных.
После того как вы нажмете команду «Дополнительно» на вкладке «Данные» ленты Excel, откроется диалоговое окно «Расширенный фильтр». Заполните данные, а затем нажмите кнопку ОК, чтобы отфильтровать данные.
Отфильтровать данные на другой лист
Еще одна причина использовать расширенный фильтр, особенно при работе в Excel, заключается в том, что это быстрый и эффективный способ скопировать определенные данные на другой лист.
На приведенном выше снимке экрана выбран вариант копирования отфильтрованных данных в другое место. Вы можете использовать этот параметр, чтобы быстро создать отчет для каждого отдела или продавца и отправить результаты по электронной почте.
Если вы выберете «Фильтровать список на месте», результаты будут отображаться в базе данных.
Совет. Если вы хотите удалить фильтры, вам нужно нажать кнопку "Очистить" на ленте "Данные", поскольку стрелки раскрывающегося списка фильтров исчезают при применении расширенных фильтров.
Создать список уникальных элементов
В Excel 2007 появилась функция удаления дубликатов, но вы по-прежнему можете использовать расширенный фильтр для создания списков уникальных элементов. Функция «Удалить дубликаты» удаляет дубликаты из выбранного списка, поэтому вам нужно помнить о работе с копией списка, если вы хотите сохранить исходный список нетронутым.
При использовании расширенного фильтра никакие элементы не удаляются из списка, поэтому вы не рискуете потерять исходные данные.
Сложные фильтры
С помощью расширенного фильтра вы можете создать диапазон критериев на листе и сразу увидеть настройки фильтра. Вы также можете создавать сложные фильтры с параметрами И/ИЛИ, которые выходят за рамки возможностей автофильтра.
Например, с помощью автофильтра вы можете выбрать двух конкретных клиентов и два продукта для этих клиентов.
С помощью расширенного фильтра вы можете создавать условия ИЛИ между столбцами, например, «Клиент А» ИЛИ «Продукт Б» — вы не можете сделать это в автофильтре!
Как создать таблицу критериев для расширенного фильтра
<р>1. Вставьте копию строки заголовка таблицы данных в пустой раздел электронной таблицы или на другой пустой лист. <р>2. Затем введите критерии поиска под соответствующими заголовками. Если вы введете критерии рядом друг с другом, как в примере ниже, он выполнит фильтр И, то есть покажет результаты, соответствующие ВСЕМ требованиям.Приведенные выше критерии будут показывать людей, чьи фамилии начинаются с буквы C И которые работают в сфере разработки. Также будут показаны люди, чьи фамилии начинаются с буквы F И, которые работают в сфере производства.
Если вы введете критерии в разные строки, как в примере ниже, критерии будут выполнять фильтрацию по ИЛИ, то есть будут показаны результаты, соответствующие ЛЮБЫМ требованиям.
Поиск с подстановочными знаками
<р>? (Вопросительный знак): Любой одиночный символ.Например, по запросу "Черный" будет найдено либо "Черный", либо "Блочный"
* (звездочка): любое количество символов.
Например, «Север*» находит «север», «северо-восток»,
и «северо-запад».
Читайте также: