Как сделать несколько фильтров на одном листе в excel

Обновлено: 06.07.2024

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

Excel фильтр более 2 критериев

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

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

1 - фильтр Excel 2013 по одному критерию

Первый и самый простой способ фильтрации по одному или максимум 2 критериям - применить фильтр в меню Data => Filter.

ABC для записей, содержащих ABC,
ABC * для записей, начинающихся с ABC,
* ABC для записей, заканчивающихся ABC.

Это также способ сделать несколько фильтров Excel несколькими столбцами, выбрав фильтр для применения в каждом столбце относительно данных, хранящихся в этом столбце.

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

2 - Двойной фильтр в Excel

В открывшемся окне вы можете ввести один или два критерия - если вы выберете OR, оба будут применены. Если вы выберете AND (по умолчанию), будут выбраны только те элементы, которые соответствуют двум критериям.

Таким образом, вы можете сделать двойной фильтр Excel в своей электронной таблице и как удвоить фильтр в Excel на одном столбце.

3 - Excel расширенный фильтр нескольких критериев одного столбца

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

И вуаля! В приведенном ниже примере список отелей был отфильтрован по всем отелям из Holiday Inn, Crowne Plaza, Radisson или Novotel с расширенным фильтром Excel 2013, в результате чего в пользовательском автофильтре Excel было более 2 критериев, также называемых расширенным Excel. Отфильтруйте несколько критериев в одном столбце, так как разные критерии применяются одновременно к одному и тому же столбцу.

Специальный пользовательский фильтр Excel, содержащий более 2 критериев, приведет к отображению только элементов, содержащих любой из отображаемых фильтров.

Ниже приведен результат с использованием нескольких фильтров Excel.

Пользовательский фильтр Excel по нескольким критериям

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

Затем выберите столбец для фильтрации. Откройте меню> Данные> Фильтр> Дополнительно.

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

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

Фильтр по столбцам в Excel

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

С его помощью которого можно отобразить только ту информацию, которая необходима.

Для того, чтобы в Экселе (Excel) сделать фильтр по столбцам, нужно:

1) Выделить нужные столбцы таблицы. Если в фильтр требуется включить все столбцы, то можно щёлкнуть на любой ячейке из диапазона.

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

2) На панели инструментов Excel выбрать "Данные" -> "Фильтр".

3) Для фильтрации данных нужно щёлкнуть по специальной стрелочке, которая расположена в первой ячейке (то есть заголовке) нужного вам столбца.

Самый простой вариант фильтрации - отбор определённых текстовых значений из списка.

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

Удобнее сделать так - снять галочку с пункта "Выделить всё" (это автоматически снимет выделение со всех значений), а затем поставить галочку рядом с интересующим нас значением.

В результате на листе Эксель отобразиться только одна строка из таблицы, а стрелка фильтрации в столбце "ФИО сотрудника" изменит свой вид.

Чтобы убрать фильтр достаточно щёлкнуть ещё раз на кнопке "Фильтр" (она будет выделена).

Рассмотрим другой пример использования фильтра в Excel - отбор по числовым значениям.

Например, нам требуется показать только тех сотрудников, которые имеют зарплату > 30000 рублей.

Сделать такой фильтр можно так:

1) Открываем фильтр для столбца "Зарплата".

2) Для работы с числами используются "Числовые фильтры". В списке условий выбираем "Больше".

3) Появится окно, где указываем значение "30000", а затем нажимаем на "OK".

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

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

Например, нужно отобразить сотрудников с зарплатой больше 30000 рублей, которые относятся к отделу "Руководство".

Для этого нужно применить оба фильтра последовательно.

1) Сначала устанавливаем фильтр "Зарплата > 30000 рублей".

2) Затем выбираем фильтр столбца "Отдел". В списке значений будут отображаться не все отделы, а только те отделы, в которых хотя бы 1 сотрудник получает зарплату > 30000 рублей.

Excel нам покажет следующие строки:

В заголовках столбцов "Отдел" и "Зарплата" будет специальный значок, означающий, что фильтр был сделан именно по ним.

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

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

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

Устанавливаем фильтр по подписи

В нашем примере в сводной таблице отображены данные различных месяцев за период с 2008 по 2013 год. Чтобы сравнить данные первых шести месяцев каждого года, необходимо установить фильтр по подписи. Для этого жмем на иконку справа от поля Месяц, в выпадающем меню выбираем Фильтры по подписи -> Меньше…

В появившемся диалоговом окне Фильтры по подписи, указываем параметр фильтрации (в нашем случае, это значение 7)

Предположим, теперь вам требуется увидеть месяца с наибольшим значением, для этого необходимо применить Фильтр по значению… для этого же поля. Опять жмем на иконку справа от поля Месяц, в выпадающем меню выбираем Фильтры по значению -> Первые 10…

Теперь сводная таблица отображает первые 3 месяца с наибольшим значением, но фильтр по подписи пропал. На рисунке отображены значения 2, 6 и 9-го месяцев.

Меняем параметры фильтров сводной таблицы

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

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

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

При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… [Top 10…] и др.
При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит. , начинается с… и др.
При выборе опции Фильтры по дате варианты фильтрации – завтра, на следующей неделе, в прошлом месяце и др.
Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… [Custom…], используя который можно задать одновременно два условия отбора, связанные отношением И [And] – одновременное выполнение 2 условий, ИЛИ [Or] – выполнение хотя бы одного условия.

Если данные после фильтрации были изменены, фильтрация автоматически не срабатывает, поэтому необходимо запустить процедуру вновь, нажав на кнопку Повторить [Reapply] в группе Сортировка и фильтр на вкладке Данные.

Отмена фильтрации

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

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

Срезы

Срезы – это те же фильтры, но вынесенные в отдельную область и имеющие удобное графическое представление. Срезы являются не частью листа с ячейками, а отдельным объектом, набором кнопок, расположенным на листе Excel. Использование срезов не заменяет автофильтр, но, благодаря удобной визуализации, облегчает фильтрацию: все примененные критерии видны одновременно. Срезы были добавлены в Excel начиная с версии 2010.

Создание срезов

В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.

Для этого нужно выполнить следующие шаги:

Форматирование срезов

Выделить срез.
На ленте вкладки Параметры [Options] выбрать группу Стили срезов [Slicer Styles], содержащую 14 стандартных стилей и опцию создания собственного стиля пользователя.

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

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.

Расширенный фильтр

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

Задание условий фильтрации

В диалоговом окне Расширенный фильтр выбрать вариант записи результатов: фильтровать список на месте [Filter the list, in-place] или скопировать результат в другое место [Copy to another Location].

Указать Исходный диапазон [List range], выделяя исходную таблицу вместе с заголовками столбцов.
Указать Диапазон условий [Criteria range], отметив курсором диапазон условий, включая ячейки с заголовками столбцов.
Указать при необходимости место с результатами в поле Поместить результат в диапазон [Copy to], отметив курсором ячейку диапазона для размещения результатов фильтрации.
Если нужно исключить повторяющиеся записи, поставить флажок в строке Только уникальные записи [Unique records only].

Функция расширенного фильтра в Microsoft Excel

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

Использование расширенного фильтра в Excel

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

Шаг 1: Создание таблицы с условиями отбора

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

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

Шаг 2: Запуск расширенного фильтра

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

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

Многие пользователи ПК хорошо знакомы с пакетом продуктов для работы с различного рода документами под названием Microsoft Office. Среди программ этой компании есть MS Excel. Данная утилита предназначена для работы с электронными таблицами.

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

Что это за функция? Описание

Что значит расширенный фильтр в Excel? Это функция, которая позволяет разграничивать выбранные данные (по столбцам в “Экселе”) относительно введенных требований.

Что значит автофильтр?

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

Как делать правильно?

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

Инструкция по расширенной фильтрации электронной таблицы:

Необходимо создать место выше основной таблицы. Там и будут располагаться результаты фильтрации. Должно быть достаточное количества места для готовой таблицы. Также требуется еще одна строка. Она будет разделять отфильтрованную таблицу от основной.
В самую первую строку освобожденного места скопировать всю шапку (названия колонок) основной таблицы.
Ввести необходимые данные для фильтрации в нужный столбец. Отметим, что запись должна выглядеть следующим образом: = “= фильтруемое значение”.
Теперь необходимо пройти в раздел “Данные”. В области фильтрации (значок в виде воронки) выбрать “Дополнительно” (находится в конце правого списка от соответствующего знака).
Далее во всплывшем окошке нужно ввести параметры расширенного фильтра в Excel. “Диапазон условий” и “Исходный диапазон” заполняются автоматически, если была выделена ячейка начала рабочей таблицы. Иначе их придется вводить самостоятельно.
Нажать на Ок. Произойдет выход из настроек параметров расширенной фильтрации.

После проделанных шагов в основной таблице останутся только записи по заданному разграничивающему значению. Чтобы отменить последнее действие (фильтрацию), нужно нажать на кнопку “Очистить”, которая находится в разделе “Данные”.

Работа с расширенным фильтром в “Экселе”

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

Для этого необходимо:

Разместить условия разграничения (=”-Самара”) под предыдущим запросом (=”=Ростов”).
Вызвать меню расширенного фильтра (раздел “Данные”, вкладка “Фильтрация и сортировка”, выбрать в ней “Дополнительно”).
Нажать Ок. После этого расширенная фильтрация закроется в Excel. А на экране появится готовая таблица, состоящая из записей, в которых указан город Самара или Ростов.

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

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

Расширенная фильтрация. Основные правила использования при работе “Экселе”

Критериями отбора называются результаты исходной формулы.
Результатом могут быть только два значения: “ИСТИНА” или “ЛОЖЬ”.
При помощи абсолютных ссылок указывается исходный диапазон фильтруемой таблицы.
В результатах формулы будут показаны только те строки, которые получают по итогу значение “ИСТИНА”. Значения строк, которые получили по итогу формулы “ЛОЖЬ”, не будут высвечиваться.

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

Пример в “Экселе 2010”

Рассмотрим пример расширенного фильтра в Excel 2010 и использования в нем формул. К примеру, разграничим значения какого-нибудь столбца с числовыми данными по результату среднего значения (больше или меньше).

Инструкция для работы с расширенным фильтром в Excel по среднему значению колонки:

Для начала необходимо выбрать формулу среднего значения данных столбца. Для этого нужно выбрать диапазон результатов от начальной записи до конечной. Просуммировать их и разделить на количество строк (количество записей).
После этого выбрать в разделе “Фильтрование и сортировка” пункт “Дополнительно”. Вписать туда нужные данные (по диапазонам).
После этого нажать Ок. В результате получится таблица, в которой есть записи, не превышающие среднего значения по заданному столбцу.

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

Главное – не забывать вписывать критерии фильтрации над основной таблицей в столбце, по которому будет проводиться разграничение.

Внимание! При работе записи, которые не прошли критерий фильтрации, не удаляются. Они просто не показываются (не отображаются). Расширенная фильтрация предназначена для того, чтобы можно было скопировать результат и вставить его куда-нибудь в другое место. После этого необходимо нажать на “Очистить” в разделе “Фильтрация и сортировка”. Тогда ваша исходная таблица вернется на экран.

Автофильтр. Пример использования

Небольшое заключение

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

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

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

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

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

Итак, у нас есть таблица с данными, которые требуется отфильтровать.

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

Как отменить расширенную фильтрацию

Заключение

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

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