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

Почти все юзеры ПК (Персональный компьютер — компьютер, предназначенный для эксплуатации одним пользователем) отлично знакомы с пакетом товаров для работы с различного рода документами под заглавием Microsoft Office. Посреди программ данной компании есть MS Excel. Данная утилита создана для работы с электрическими таблицами.

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

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

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

Например, если у нас есть электрическая таблица со сведениями обо всех учениках школы (рост, вес, класс, пол и т. п.), то мы с легкостью сможем выделить посреди их, скажем, всех мальчишек с ростом 160 из 8-го класса. Создать это можно, используя функцию «Расширенный фильтр» в Excel. О ней мы и будем детально говорить дальше.

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

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

Как созодать верно?

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

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

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

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

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

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

Для этого нужно:

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

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

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

Расширенная фильтрация. Главные правила использования при работе «Экселе»

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

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

Пример в «Экселе 2010»

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

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

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

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

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

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

Интересно почитать: Как скрыть все примечания в excel одновременно

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

Маленькое заключение

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

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

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

Как созодать верно?

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

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

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

Для этого нужно:

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

Расширенная фильтрация. Главные правила использования при работе «Экселе»

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

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

Пример в «Экселе 2010»

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

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

Интересно почитать: Что нужно уметь делать в excel

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

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

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

Плюсы и минусы расширенного фильтра в программке «Эксель»

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

Плюсы расширенной фильтрации:

можно применять формулы.

Минусы расширенной фильтрации:

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

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

Фильтрация по двум отдельным аспектам. Как верно ее создать?

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

Сделать пространство для ввода параметра фильтрования. Удобнее всего оставлять это пространство над главный таблицей и не забывать копировать шапку (наименования столбцов), чтоб не запутаться, в какую колонку вводить этот аспект.
Ввести подходящий показатель для фильтрации. К примеру, все записи, чьи значения столбца больше 1000 (> 1000).
Пройти во вкладку «Данные». В разделе «Фильтрация и сортировка» избрать пункт «Добавочно».
В открывшемся окошке указать спектры рассматриваемых значений и ячейку со значением рассматриваемого аспекта.
Надавить на Ок. Опосля этого будет выведена отфильтрованная по данному аспекту таблица.
Скопировать итог разграничения. Вставить отфильтрованную таблицу куда-нибудь в сторону на том же листе Excel. Можно пользоваться иной страничкой.
Избрать «Очистить». Данная клавиша находится во вкладке «Данные» в разделе «Фильтрация и сортировка». Опосля ее нажатия отфильтрованная таблица возвратятся в начальный вид. И можно будет работать с ней.
Дальше нужно опять выделить свободное пространство для таблицы, которая будет отфильтрована.
Позже необходимо скопировать шапку (наименования столбцов) основного поля и перенести их в первую строку освобожденного под отфильтрованную структуру места.
Пройти во вкладку «Данные». В разделе «Фильтрация и сортировка» избрать «Добавочно».
В открывшемся окошке избрать спектр записей (столбцов), по которому будет проводиться фильтрация.
Добавить адресок ячейки, в которой записан аспект разграничения, к примеру, «город Одесса».
Надавить на Ок. Опосля этого произойдет фильтрация по значению «Одесса».
Скопировать отфильтрованную таблицу и вставить ее или на иной лист документа, или на той же страничке, но в стороне от главный.
Опять надавить на «Очистить». Все, готово. Сейчас у вас имеются три таблицы. Основная, отфильтрованная по одному значению (>1000), также та, что отфильтрована по другому значению (Одесса).

Маленькое заключение

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

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

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

Обыденный и расширенный фильтр

В Excel представлен простой фильтр, который запускается с вкладки «Данные» — «Фильтр» (Data — Filter в английской версии программки) либо с помощью ярлычка на панели инструментов, схожего на конусообразную воронку для переливания воды в ёмкости с узеньким горлышком.

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

1-ое внедрение расширенного фильтра

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

Интересно почитать: Excel группировка в сводной таблице

Как применять расширенный фильтр в Excel, примеры, разглядим ниже.

A	B	C	D	E	F
1	Продукция	Наименование	Месяц	Денек недельки	Город	Заказчик
2	овощи	Краснодар	«Ашан»
3
4	Продукция	Наименование	Месяц	Денек недельки	Город	Заказчик
5	фрукты	персик	январь	пн	Москва	«Пятёрочка»
6	овощи	помидор	февраль	пн	Краснодар	«Ашан»
7	овощи	огурец	март	пн	Ростов на дону-на-Дону	«Магнит»
8	овощи	баклажан	апрель	пн	Казань	«Магнит»
9	овощи	свёкла	май	среда	Новороссийск	«Магнит»
10	фрукты	яблоко	июнь	четверг	Краснодар	«Бакаль»
11	зелень	укроп	июль	четверг	Краснодар	«Пятёрочка»
12	зелень	петрушка	август	пятница	Краснодар	«Ашан»

Применение фильтра

В приведённой таблице строчки 1 и 2 предусмотрены для спектра критерий, строчки с 4 по 7 — для спектра начальных данных.

Для начала следует ввести в строчку 2 надлежащие значения, от которых будет отталкиваться расширенный фильтр в Excel.

Пуск фильтра осуществляется при помощи выделения ячеек начальных данных, опосля что нужно избрать вкладку «Данные» и надавить клавишу «Добавочно» (Data — Advanced соответственно).

В открывшемся окне отобразится спектр выделенных ячеек в поле «Начальный спектр». Согласно приведённому примеру, строчка воспринимает значение «$A$4:$F$12».

Поле «Спектр критерий» обязано заполниться значениями «$A$1:$F$2».

Окошко также содержит два условия:

фильтровать перечень на месте;
скопировать итог в другое пространство.

1-ое условие дозволяет сформировывать итог на месте, отведённом под ячейки начального спектра. 2-ое условие дозволяет сформировать перечень результатов в отдельном спектре, который следует указать в поле «Поместить итог в спектр». Юзер выбирает удачный вариант, к примеру, 1-ый, окно «Расширенный фильтр» в Excel запирается.

Основываясь на введённых данных, фильтр сформирует последующую таблицу.

A	B	C	D	E	F
1	Продукция	Наименование	Месяц	Денек недельки	Город	Заказчик
2	овощи	Краснодар	«Ашан»
3
4	Продукция	Наименование	Месяц	Денек недельки	Город	Заказчик
5	овощи	помидор	февраль	пн	Краснодар	«Ашан»

При использовании условия «Скопировать итог в другое пространство» значения из 4 и 5 строк отобразятся в данном юзером спектре. Начальный спектр же остается без конфигураций.

Удобство использования

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

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

Сложные запросы

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

Таблица знаков для сложных запросов приведена ниже.

возвращает все слова, начинающиеся с буковкы П:

персик, помидор, петрушка (если ввести в ячейку B2);
Пятёрочка (если ввести в ячейку F2).

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

Символ «?» предполагает лишь один знак.

Связки OR и AND

Необходимо знать, что сведения, данные одной строчкой в «Спектре критерий», расцениваются записанными в связку логическим оператором (AND). Это значит, что несколько критерий производятся сразу.

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

Таблица значений воспримет последующий вид:

A	B	C	D	E	F
1	Продукция	Наименование	Месяц	Денек недельки	Город	Заказчик
2	фрукты
3	овощи
4
5	Продукция	Наименование	Месяц	Денек недельки	Город	Заказчик
6	фрукты	персик	январь	пн	Москва	«Пятёрочка»
7	овощи	помидор	февраль	пн	Краснодар	«Ашан»
8	овощи	огурец	март	пн	Ростов на дону-на-Дону	«Магнит»
9	овощи	баклажан	апрель	пн	Казань	«Магнит»
10	овощи	свёкла	май	среда	Новороссийск	«Магнит»
11	фрукты	яблоко	июнь	четверг	Краснодар	«Бакаль»

Сводные таблицы

Ещё один метод фильтрования данных осуществляется при помощи команды «Вставка — Таблица — Сводная таблица» (Insert — Table — PivotTable в английской версии).

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

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

Заключение

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

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

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

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

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