Как сделать расширенный фильтр в excel

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

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

Но что, если необходимо отфильтровать данные по нескольким критериям одновременно? Или использовать при фильтрации формулу? Или отобрать значения одновременно в двух и более столбцах? На помощь в такой ситуации может прийти Расширенный фильтр (Advanced Filter) .

Процесс работы с этим фильтром существенно отличается от того, к которому все привыкли, и состоит из нескольких этапов:

• Подготовка диапазона для условий фильтрации
• Формирование условий фильтрации
• Работа с мастером «Расширенного фильтра»

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

Разберем каждый из этапов его применения поподробнее.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как скопировать данные на другой лист с помощью расширенного фильтра в Excel?

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

Вот простой способ решения этой задачи: сделайте следующее:

1. Активируйте рабочий лист, на который вы хотите скопировать отфильтрованный результат, с помощью функции расширенного фильтра.

2, Затем нажмите Данные > Дополнительно, см. снимок экрана:

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

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

Скопируйте данные на другой лист с помощью расширенного фильтра с помощью кода VBA

Если у вас есть опыт работы с кодом VBA, вы можете завершить эту работу с помощью следующего кода VBA.

1. Активируйте рабочий лист, который вы хотите отфильтровать, и скопируйте результат на другой лист.

2. Удерживайте ALT + F11 ключи в Excel, и он открывает Microsoft Visual Basic для приложений окно.

3. Нажмите Вставить > модульи вставьте следующий код в модуль Окно.

Код VBA: скопируйте данные на другой лист с помощью расширенного фильтра:

Sub Advancedfiltertoanothersheet()
'Updateby Extendoffice
    Dim xStr As String
    Dim xAddress As String
    Dim xRg As Range
    Dim xCRg As Range
    Dim xSRg As Range
    On Error Resume Next
    xAddress = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select the filter range:", "Kutools for Excel", xAddress, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Set xCRg = Application.InputBox("Please select the criteria range:", "Kutools for Excel", "", , , , , 8)
    If xCRg Is Nothing Then Exit Sub
    Set xSRg = Application.InputBox("Please select the output range:", "Kutools for Excel", "", , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    xRg.AdvancedFilter xlFilterCopy, xCRg, xSRg, False
    xSRg.Worksheet.Activate
    xSRg.Worksheet.Columns.AutoFit
End Sub

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

5, Затем нажмите OK, и появляется другое диалоговое окно, напоминающее вам о выборе диапазона критериев, на основе которого вы хотите выполнить фильтрацию, см. снимок экрана:

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

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

Автофильтр

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

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

19

1. Выбираем один из фильтров, заполняем поля в окне «Пользовательский автофильтр» и жмем «ОК». Настройки будут применены к диапазону ячеек.

20

Рассмотрим опцию «Настраиваемый фильтр». С ее помощью пользователи могут самостоятельно установить нужные настройки фильтрации.

1. Снова открываем текстовые или числовые фильтры. В конце списка находится нужный пункт, по которому следует кликнуть.

21

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

Стоит обратить внимание на пункты И/ИЛИ в окне настройки автофильтра. От них зависит то, как будут применены настройки – вместе или частично

Как наложить фильтр по дате на табличный массив

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

Способ 1. Использование опции «Фильтр»

Самый простой способ фильтрации табличных данных в Excel, который подразумевает соблюдение следующего алгоритма действий:

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

Исходный табличный массив

1. Выделить составленную таблицу левой клавишей манипулятора.
2. Перейти во вкладку «Главная» в верхней панели инструментов главного меню Excel.
3. Нажать на кнопку «Фильтр» в отобразившейся панели опций. Также в этом разделе есть функция «Сортировка», которая меняет порядок отображения строк или столбцов в исходной таблице, сортируя их по какому-либо параметру.

Путь к наложению фильтра на табличный массив в Excel

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

Стрелочки в заголовках столбцов, которые отобразятся после наложения фильтра

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

Выбор объекта, по которому будет производиться фильтрация

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

Финальный результат фильтрации

Способ 2. Использование опции «Фильтр по дате»

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

1. Аналогичным образом наложить фильтр на исходную таблицу.
2. В окне фильтрации найти строку «Фильтр по дате» и левой клавишей манипулятора кликнуть по стрелочке, расположенной справа от нее.
3. Раскроется выпадающее меню. Здесь представлены варианты фильтрации данных по дате.

Методы расширенной фильтрации в Excel

1. К примеру, нажать на кнопку «Между…».
2. Откроется окошко «Пользовательский автофильтр». Здесь в первой строчке необходимо указать начальную дату, а во второй конечную.

Заполнение окна «Пользовательский автофильтр» после выбора функции “Между…”

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

Способ 3. Выполнение фильтрации вручную

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

1. В исходном табличном массиве найти даты, которые не нужны пользователю.
2. Выделить найденные строки левой клавишей мышки.
3. Нажать на кнопку «Backspace» с клавиатуры компьютера, чтобы удалить выделенные значения.

Способ 4. Использование расширенного фильтра по дате

Выше был рассмотрен метод фильтрации значений в табличном массиве на основе опции «Между…». Для полного раскрытия темы необходимо обсудить несколько вариантов расширенного фильтра. Рассматривать все разновидности фильтра нецелесообразно в рамках данной статьи. Чтобы наложить на таблицу тот или иной фильтр по дате, необходимо:

1. Наложить фильтр на таблицу через вкладку «Главная». О том, как это сделать, было рассказано выше.
2. Раскрыть выпадающий список в заголовке любого столбика в таблице и щелкнуть ЛКМ по строчке «Фильтр по дате».
3. Указать любой из вариантов. Для примера кликнем по строке «Сегодня».

Выбор опции «Сегодня» в расширенном фильтре Эксель

1. Информация в массиве отфильтруется по указанной дате. Т.е. в таблице останутся только те данные, у которых указана сегодняшняя дата. При выставлении такого фильтра Excel будет ориентироваться по дате, выставленной на компьютере.
2. Выбрав вариант «Больше…», пользователю придется задать определенное число. После этого в табличном массиве останутся даты, которые больше указанной. Все остальные значения будут удалены.

Фильтр в Excel

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

Фильтр устанавливается на заголовки и подзаголовки таблиц; главное, чтобы ячейки, на которые будет устанавливаться фильтр, не были пустыми. А располагается он в меню книги Excel на вкладке «Данные», раздел «Сортировка и фильтр»:

Кликнув по значку «Фильтр», верхние ячейки диапазона будут определены как заголовки и в фильтрации участия принимать не будут. Заголовки снабдятся значком . Кликните по нему, чтобы ознакомиться с возможностями фильтра:

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

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

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

В нем можно задать следующие условия:

• Условия «равно» и «не равно» пояснений не требуют, т.к. с ними все предельно ясно;
• «больше», «меньше», «больше или равно» и «меньше или равно». Как строки можно сравнить друг с другом? Чтобы это понять, вспомните, каким образом Excel выполняет сортировку. Т.е. чем дальше в списке сортировки будет находиться строка, чем больше ее значение. Следующие утверждения являются истинными (верными): A А; А Значение», т.к. ничто в один и тот же момент не может быть и больше, и меньше одного и того же показателя.

При использовании «ИЛИ» должно соблюдаться хотя бы одно из заданных условий.

В самом конце окна пользовательского автофильтра приведена подсказка. Первая ее часть: «Знак вопроса »?» обозначает один любой знак …». Т.е. при проставлении условий, когда нельзя точно определить символ в конкретном месте строки, подставьте на его место «?». Примеры условий:

• Начинается с «?ва» (начинается с любого символа за которым следуют символы «ва») вернет результаты: «Иванов», «Иванова», «кварц», «сват» и другие строки, подходящие под условие;
• Равно «. » – вернет в результате строку, которая содержит 7 любых символов.

Вторая часть подсказки: «Знак »*» обозначает последовательность любых знаков». Если в условии невозможно определить, какие символы и в каком количестве должны находиться в строке, то вместо них подставьте «*». Примеры условий:

• Заканчивается на «о*т» (заканчивается символами «о», после которого идет любая последовательность символов, затем символ «т») вернет результат: «пот», «торт», «оборот» и даже такой – «рвнщуооовиунистврункт».
• Равно «*» – вернет строку, которая содержит хотя бы один символ.

Помимо текстовых фильтров бывают «Числовые фильтры», которые в основном принимают те же самые условия, что и текстовые, но имеют и дополнительные, относящиеся только к числам:

• «Выше среднего» и «Ниже среднего» – возвращает значения, которые выше и ниже среднего значения соответственно. Среднее значение рассчитывается, исходя из всех числовых значений списка столбца;
• «Первые 10…» – клик по данному пункту вызывает окно:

Здесь можно задать какие элементы выводить первые из наибольших или первые из наименьших. Также, какое количество элементов вывести, если в последнем поле выбран пункт «элементов списка». Если же выбран пункт «% от количества элементов», второе значение задает данный процент. Т.е. если в списке 10 значений, то будет выбрано самое высокое (или самое низкое) значение. Если в списке 1000 значений, то либо первые, либо последние 100.

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

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

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

У Вас недостаточно прав для комментирования.

Расширенные возможности пользовательского автофильтра

Допустим нас интересует 10 продаж с наибольшими ценами. Быстрее всего можно реализовать данную задачу с помощью специальной опции автофильтра. Из выпадающего списка на столбце «Цена» выберите опцию: «Числовые фильтры»-«Первые 10». Данная функция автофильтра позволяет находить в этой таблице первые десять товаров с наибольшими ценами или 10 товаров (при необходимости и больше, например, 12) с наибольшей или наименьшей суммой продаж и т.п. Как видно из названия группы опции «Числовые фильтры» ее можно применять только к числовым значениям в столбцах таблицы, а также к датам (ведь дата в Excel – это число).

Допустим мы визуально анализируем отчет по продажам товаров на несколько сотен позиций и наименований, которые повторяются в смешанном порядке. Нас интересуют в первую очередь: какие товары из магазина №5 попали в ТОП-7 самых продаваемых? Если мы отсортируем все товары по наименованию, а потом будем суммировать количество проданных товаров по отдельности, то это займет много времени. Вместо суммирования групп позиций по отдельности можно воспользоваться промежуточными итогами или автофильтром. В несколько кликов мышки мы скроем ненужную информацию и оставим только необходимые данные содержащие соответственные итоговые значения. Для этого:

1. Сначала удалите критерий фильтрования из предыдущего примера: «ДАННЫЕ»-«Сортировка и фильтр»-«Очистить».
2. Из выпадающего списка на столбце «Магазин» отметьте галочкой только опцию: «Магазин 5».

Из выпадающего списка на столбце «Сумма» выберите опцию: «Числовые фильтры»-«Первые 10».
В появившемся окне «Наложения условия по списку» установите следующие параметры: наибольших; 7; элементов списка. И нажмите ОК.

В результате мы получили список из ТОП-7 самых продаваемых товаров в Магазине №5.