Несколько фильтров на одном листе Excel

Обновлено: 21.11.2024

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

Чтобы отфильтровать данные сначала по Месяцу (показать 21 февраля), а затем по Общему объему продаж (более 400 долл. США), выполните следующие действия. эти шаги:

<р>1. Чтобы отобразить кнопки фильтров в заголовках столбцов, выберите любую ячейку в диапазоне данных (например, B2:G16) и на ленте выберите Главная > Сортировка и фильтрация > Фильтр.

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

<р>2. Теперь нажмите кнопку фильтра для Месяц (ячейка D2), выберите только Февраль (снимите флажок Январь) и нажмите ОК. Функция фильтра распознает даты и группирует их по годам и месяцам.

В результате выполнения шага 2 все строки, содержащие 21 января в столбце D, отфильтровываются, и отображаются только строки с 21 февраля. Кроме того, внешний вид кнопки фильтра для Месяц (D2) отличается, поэтому ясно, что диапазон фильтруется по месяцам.

<р>3. Теперь вы можете фильтровать данные по другому столбцу. Нажмите кнопку фильтра для общего объема продаж (G2), выберите «Числовые фильтры» и нажмите «Больше».

Обратите внимание, что вы также можете выбрать "Равно", "Не равно", "Меньше чем" и т. д.

<р>4. Во всплывающем окне введите нижний предел (в данном случае $400) и нажмите OK. Здесь вы также можете добавить дополнительные условия или изменить оператора.

Конечным результатом является исходный диапазон данных, отфильтрованный по параметрам Месяц (Фев-21) и Общий объем продаж (более 400 долларов США).

Применить несколько фильтров к столбцам в Google Таблицах

<р>1. Чтобы создать кнопки фильтра, выберите любую ячейку в диапазоне данных (B2:G16) и в меню выберите Данные > Создать фильтр.

<р>2. Нажмите кнопку фильтра для Месяц (D2), выберите только Фев-21 (снимите флажок Янв-21) и нажмите ОК.

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

<р>3. Нажмите кнопку фильтра для Общий объем продаж (G2), выберите «Фильтровать по условию» и в раскрывающемся меню выберите «Больше чем».

Как и в Excel, вы также можете выбрать "Меньше чем", "Равно" и т. д.

<р>4.В текстовом поле, которое появляется под условием «Больше чем», введите нижний предел (400) и нажмите «ОК».

Наконец, отображаются строки, содержащие 21 февраля в столбце D со значением продаж более 400 долл. США, а остальные строки скрыты.

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

При применении функции "Фильтр" после фильтрации одного столбца следующие столбцы будут фильтроваться только на основе результатов предыдущего отфильтрованного столбца. Это означает, что только критерии И могут применяться к более чем одному столбцу. Как в этом случае можно применить критерии И и ИЛИ для одновременной фильтрации нескольких столбцов на листе Excel?

Одновременно фильтровать несколько столбцов с помощью расширенного фильтра

Например, у меня есть диапазон данных, теперь мне нужно отфильтровать их на основе критериев из нескольких столбцов: Продукт = AAA-1 и Заказ> 80 или Общая цена> 10000, чтобы получить следующий результат фильтрации:

Расширенный фильтр может помочь вам решить эту задачу по мере необходимости, пожалуйста, делайте это шаг за шагом:

<р>1. Создайте критерии фильтра в соответствии с вашими потребностями, в этом примере я введу критерии в диапазоне G1: I2, см. снимок экрана:

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

<р>2. После создания критериев нажмите Данные > Дополнительно, см. снимок экрана:

<р>3. В диалоговом окне «Расширенный фильтр» выполните следующие действия:

(1.) Выберите «Фильтровать список на месте» в разделе «Действие»;

(2.) Нажмите кнопку , чтобы выбрать диапазон ячеек, которые вы хотите отфильтровать, из списка Диапазон диапазона;

(3.) Затем нажмите кнопку , чтобы выбрать критерии фильтрации, которые вы хотите отфильтровать, на основе диапазона критериев;

<р>4. Затем нажмите OK, и несколько столбцов будут отфильтрованы одновременно на основе критериев, см. снимок экрана:

Одновременно фильтровать несколько столбцов с помощью Kutools for Excel

Если вы не можете применить расширенный фильтр умело, суперфильтр Kutools for Excel также может оказать вам услугу. С ним вам просто нужно выбрать критерии так же, как и при использовании функции «Фильтр», не вводя критерии вручную.

После установки Kutools for Excel сделайте следующее:

<р>1. Нажмите «Предприятие» > «Суперфильтр», см. снимок экрана:

<р>2. В появившемся диалоговом окне «Суперфильтр»:

(1.) Отметьте «Указано» и нажмите кнопку, чтобы выбрать диапазон данных, который вы хотите отфильтровать;

(2.) Выберите взаимосвязь между критериями И или Или из раскрывающегося списка Отношения;

(3.) Затем выберите нужные критерии в списке критериев.

<р>3.После завершения критериев нажмите кнопку «Фильтр», и данные будут отфильтрованы на основе критериев нескольких столбцов одновременно, см. снимок экрана:

Демонстрация: одновременная фильтрация нескольких столбцов с помощью Kutools for Excel

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Скачать и бесплатно попробовать прямо сейчас!

поиск меню

Урок 19. Фильтрация данных

Введение

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

Чтобы отфильтровать данные:

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

Рабочий лист со строкой заголовка

Нажатие команды "Фильтр"

Нажав стрелку раскрывающегося списка для столбца B

Снятие флажка «Выбрать все»

Выбор данных для фильтрации и нажатие кнопки "ОК"

Отфильтрованные данные

К параметрам фильтрации также можно получить доступ с помощью команды "Сортировка и фильтр" на вкладке "Главная".

Доступ к параметрам фильтра на вкладке "Главная"

Чтобы применить несколько фильтров:

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

Нажав стрелку раскрывающегося списка для столбца D

Выбор данных для фильтрации и нажатие кнопки "ОК"

Отфильтрованные данные

Чтобы очистить фильтр:

После применения фильтра вы можете удалить или удалить его со своего листа, чтобы иметь возможность фильтровать содержимое различными способами.

Нажав стрелку раскрывающегося списка для столбца D

Очистка фильтра

Очищенный фильтр

Чтобы удалить все фильтры с листа, нажмите команду "Фильтр" на вкладке "Данные".

Нажатие команды "Фильтр" для удаления фильтров

Расширенная фильтрация

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

Чтобы отфильтровать с помощью поиска:

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

Выберите вкладку «Данные», затем нажмите команду «Фильтр». В ячейке заголовка для каждого столбца появится стрелка раскрывающегося списка. Примечание. Если вы уже добавили фильтры на лист, этот шаг можно пропустить.
Нажмите стрелку раскрывающегося списка для столбца, который нужно отфильтровать. В нашем примере мы будем фильтровать столбец C.

Нажав стрелку раскрывающегося списка для столбца C

Введите поисковый запрос и нажмите OK

Лист, отфильтрованный по поисковому запросу

Чтобы использовать расширенные текстовые фильтры:

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

Выберите вкладку «Данные», затем нажмите команду «Фильтр». В ячейке заголовка для каждого столбца появится стрелка раскрывающегося списка. Примечание. Если вы уже добавили фильтры на лист, этот шаг можно пропустить.
Нажмите стрелку раскрывающегося списка для столбца, который нужно отфильтровать. В нашем примере мы будем фильтровать столбец C.

Нажав стрелку раскрывающегося списка для столбца C

Выбор текстового фильтра

Применение текстового фильтра

Примененный текстовый фильтр

Чтобы использовать расширенные фильтры даты:

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

Выберите вкладку «Данные», затем нажмите команду «Фильтр». В ячейке заголовка для каждого столбца появится стрелка раскрывающегося списка. Примечание. Если вы уже добавили фильтры на лист, этот шаг можно пропустить.
Нажмите стрелку раскрывающегося списка для столбца, который нужно отфильтровать. В нашем примере мы отфильтруем столбец D, чтобы просмотреть только определенный диапазон дат.

Нажав стрелку раскрывающегося списка для столбца D

Выбор фильтра даты

Примененный фильтр даты

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

Чтобы использовать расширенные числовые фильтры:

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

Выберите вкладку «Данные» на ленте, затем нажмите команду «Фильтр». В ячейке заголовка для каждого столбца появится стрелка раскрывающегося списка. Примечание. Если вы уже добавили фильтры на лист, этот шаг можно пропустить.
Нажмите стрелку раскрывающегося списка для столбца, который нужно отфильтровать. В нашем примере мы отфильтруем столбец A, чтобы просмотреть только определенный диапазон идентификационных номеров.

Нажав стрелку раскрывающегося списка для столбца A

Выбор числового фильтра

Применение числового фильтра и нажатие кнопки "ОК"

Примененный числовой фильтр

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

В этой статье я расскажу о методах использования нескольких фильтров, включая код VBA. Наконец, я покажу функцию ФИЛЬТР, которая эффективно фильтрует и автоматически обновляет данные.

Скачать рабочую тетрадь

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

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

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

Приступим.

1. Несколько фильтров простым способом

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

Например, если вы хотите получить количество посещений образовательных сайтов и мобильной платформы, вы можете просто использовать параметр "Фильтр".

Для этого выполните следующие действия.

Во-первых, выберите свой набор данных. Затем откройте опцию «Фильтр». Вы можете открыть опцию двумя способами. Первый — щелкнуть вкладку «Главная»> «Фильтр» (на панели команд «Сортировка и фильтр»). Другой — на вкладке «Данные»> «Фильтр».

После этого вы увидите стрелку раскрывающегося списка для каждого поля. Теперь вам нужно отфильтровать нужные данные. Сначала выберите поле «категория сайтов». Теперь снимите флажок рядом с «Выбрать все», чтобы отменить выбор всех параметров данных. Затем установите флажок рядом с «образование». Позже нажмите OK.

Снова нажмите на поле "Платформы" и установите флажок рядом с платформой "Мобильная версия" ранее.

После фильтрации двух полей вы получите следующее количество посещений.

2.Несколько фильтров с использованием параметра автофильтра

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

Если вы хотите найти «Название сайтов» с количеством посещений от 5000 до 10000, а «Новых подписчиков» больше 200, вы можете сделать это следующим образом.

Во-первых, выберите набор данных и выберите параметр «Фильтр». Вы можете использовать эффективное сочетание клавиш, например CTRL+SHIFT+L.
Нажмите стрелку раскрывающегося списка в поле "Количество посещений". Затем выберите вариант «Между».

Вставьте 5000 в первое пустое место диалогового окна «Пользовательский автофильтр», а затем 10000 во второе место. Наконец, нажмите OK.

Снова откройте диалоговое окно «Новые подписчики» предыдущим способом, за исключением того, что вам нужно выбрать вариант «Больше, чем». Поэтому заполните пробел, набрав 200.

И вы получите следующий результат для вашего запроса.

3. Несколько фильтров с использованием расширенного фильтра

В предыдущих двух методах вы видите применение нескольких фильтров отдельно для каждого поля. У вас не было возможности указать критерии.

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

Например, вы можете указать три критерия: категория сайтов будет образовательной, количество посещений будет больше 10 000, а количество новых подписчиков будет больше 400.

Теперь запишите приведенные выше критерии в отношении их полей. Я записал эти критерии в диапазон ячеек H4:J5.

Затем откройте параметр «Расширенный фильтр», щелкнув вкладку «Данные»> «Фильтр»> «Дополнительно»

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

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

И вы увидите следующий вывод.

Похожие чтения:

Фильтрация нескольких критериев в Excel (4 подходящих способа)
Фильтрация данных в Excel с помощью формулы
Как одновременно фильтровать несколько столбцов в Excel (3 способа)
Поиск нескольких элементов в фильтре Excel (2 способа)

4. Несколько фильтров с использованием кода VBA

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

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

Теперь давайте посмотрим, как вы можете применить код VBA в нашем наборе данных.

Во-первых, откройте модуль, нажав Разработчик>Visual Basic>Вставить>Модуль.

Здесь мы увидим два приложения автофильтра VBA, использующие оператор ИЛИ и оператор И соответственно.

При использовании автофильтра VBA необходимы следующие вещи.

Диапазон: относится к диапазону ячеек для фильтрации, например. B4:G19.
Поле: это индекс номера столбца из крайней левой части набора данных. Значение первого поля будет равно 1.
Критерий 1. Первые критерии для поля, например. Критерий1="15000"
Оператор: оператор Excel, определяющий определенные требования к фильтрации, например. Оператор:=xlOr, Оператор:=xlAnd и т. д.

я. Код VBA для нескольких фильтров с использованием оператора OR (логика)

Если вы хотите отфильтровать сайты с количеством посещений меньше 10 000 или больше 15 000, а категория сайтов будет образовательной, вы можете использовать следующий код.

Если вы запустите приведенный выше код, вы получите следующий результат.

ii. Код VBA для нескольких фильтров с использованием оператора AND (логика)

Что еще более важно, если вы хотите получить образовательные сайты с количеством посещений от 5000 до 15000, вы можете использовать следующий код.

И вы получите следующий результат.

5. Эффективная альтернатива: несколько фильтров с использованием функции FILTER

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

Вот почему Microsoft предлагает обновленную функцию ФИЛЬТР, которая автоматически обновляет отфильтрованные данные.

Синтаксис функции

Аргументы:

массив: диапазон или массив для фильтрации.

include: Логический массив, указанный в качестве критерия.

if_empty: возвращаемое значение при отсутствии результатов. Это необязательное поле.

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

В этом случае формула будет такой-

Здесь B5:F19 — это наш набор данных, D5:D19 — дата, синтаксис MONTH(D5:D19) > 5 возвращает дату за июнь.

И вы получите следующий результат.

Заключение

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

Читайте также: