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

Формирование условий фильтрации

Этот этап является ключевым и раскрывает всю мощь инструмента. Для начала Вы должны научиться правильно задавать критерии для отбора.

Они могут быть 3 видов:

– текстовые критерии

Если в качестве текстового критерия ввести в поле какое-то слово, например, “Москва”, то будут отобраны ВСЕ строки, в которых в заданном столбце запись начинается со слова “Москва”

Если слово или часть слова нужно искать не с начала строки, а во всей, то необходимо пользоваться подстановочными знаками. В примере ниже найдены все строки, в которых в столбце “Город” есть слово “Петербург”

Если нужно найти точное вхождение слова или фразы, то критерий придется задать несколько необычной формулой. Например, чтобы найти строки, в которых записано “Петербург” и не отображать строки “Санкт-Петербург”, нужно ввести формулу: =”=Петербург” (именно так, с двумя знаками “=”).

– числовые критерии и даты

В качестве критерия можно вводить число (и тогда будут отобраны строки, в которых значения столбца равны этому числу)

Также можно вводить выражения с использованием логических операторов (>, <, >=, <=, <>). Например, найти строки с суммой больше 500 000 можно введя критерий >500000

Особо внимательным нужно быть при вводе критериев в виде даты. Даты обязательно необходимо вводить через косую черту. Например, чтобы отобрать все сделки после 4 января 2017, нужно ввести критерий по полю “Дата” – >04/01/2017 (в некоторых версиях Excel требуется осуществлять ввод в формате ММ/ДД/ГГГГ, то есть сначала указывать месяц. Имейте это в виду при работе).

– формулы

Самое лучшее, что умеет расширенный фильтр – это использовать в качестве критерия формулы. Чтобы все работало, задаваемая Вами формула должна возвращать значение ИСТИНА (и тогда строка выведется) или ЛОЖЬ (строка будет скрыта)

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

Например, если нужно показать топ 5 строк по полю сумма, то необходимо будет ввести следующую формулу:

=F10>НАИБОЛЬШИЙ($F$10:$F$37;6),

где F10 – ячейка первой строки в столбце “Сумма” (она не закреплена, так как формула будет перебирать строки по очереди), $F$10:$F$37 – ссылка на диапазон, который занимает столбец “Сумма” (ссылка закреплена, так как столбец не изменяется).

В результате формула пройдет по всем строкам (от 10-ой до 37-ой) и скроет все, кроме тех, где значение больше шестого по величине (то есть оставит ТОП 5).

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

Итак, основные концепции, которые Вам нужно усвоить для успешного применения Расширенного фильтра:

– заголовок столбца, в котором пишем критерий отбора, должен быть точно таким же, как у того столбца, к которому применяем этот критерий. То есть, если отбираем строки, в которых в столбце “Сумма” значение больше 500, то и условие >500 пишем под шапку “Сумма”;

– условия, записанные в одной строке, воспринимаются фильтром как связанные оператором И. Например, на картинке ниже записано условие И год 2017, И город Москва, И менеджер Петров.

– условия, записанные в разных строках, воспринимаются фильтром как связанные оператором ИЛИ. Условия могут применяться как к одному столбцу, так и к разным. Например, на картинке ниже записано условие ИЛИ город Москва, ИЛИ менеджер Иванов. Таким образом, каждая строка – это единый набор условий.

– если нужно задать условие И, но при этом использовать один и тот же столбец (например, И сумма больше 500 000, И сумма меньше 600 000), то заголовок такого столбца нужно продублировать дважды. Пример:

Теперь Вы знаете, какие критерии можно задавать, и как их правильно комбинировать. Этого достаточно, чтобы создавать сложные запросы, которые не под силу обычному автофильтру. Например, если нужно показать все сделки в Москве за 2017 год с суммой больше 500 000, а также одновременно отобразить все сделки Иванова за 2016 год, которые входят в ТОП5, то критерии будут выглядеть вот так:

Примеры работы с фильтром в Excel

Оставим для рассмотрения тот же образец таблицы, созданной ранее на три столбца.

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

5

После активации список будет выглядеть следующим образом:

6

Рассмотрим еще один пример работы фильтра:

• Таблица разделена на три столбца, и в последнем представлены цены на каждый вид продукции. В нем необходимо провести корректировку. Допустим нам нужно отфильтровать продукты, цена которых ниже значения «45».
• Жмем на значок фильтрации в выбранной нами ячейки. Так как столбец заполнен числовыми значениями, то в окне можно увидеть, что строка «Числовые фильтры» находится в активном состоянии.
• Наведя на нее курсор, открываем новую вкладку с различными возможностями фильтрации цифровой таблицы. В ней выбираем значение «меньше».

7

Дальше вписываем цифру «45» или выбираем путем открытия списка чисел в пользовательском автофильтре.

Также с помощью данной функции производится фильтрации цен в определенном цифровом диапазоне. Для этого в пользовательском автофильтре необходимо активировать кнопку «ИЛИ». Затем вверху задать значение «меньше», а внизу «больше». В строках интерфейса справа задаются необходимые параметры ценового диапазона, которые необходимо оставить. К примеру, меньше 30 и больше 45. В итоге таблица сохранит числовые значения 25 и 150.

8

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

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

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

Способ 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. Выбрав вариант «Больше…», пользователю придется задать определенное число. После этого в табличном массиве останутся даты, которые больше указанной. Все остальные значения будут удалены.

Обычная (простая) сортировка

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

Быстрая организация данных в таблице возможна благодаря набору инструментов «Сортировка и фильтр». Эта кнопка находится на вкладке «Главная», в правой ее части. Опции сортировки соответствуют формату выбранных ячеек.

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

1. Попробуем развернуть таблицу в обратную сторону – от конца к началу. Вновь необходимо открыть меню сортировки, теперь выбираем функцию «Сортировка от Я до А».

2

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

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

Пример создания расширенного фильтра — необходимо задать диапазон исходных данных, фильтров и диапазон, куда помещаются отфильтрованные данные

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

Но тут важно придерживаться правил: если нужно, чтобы значения отбирались по двум параметрам (к примеру, фамилия студента и оценка), то условия записываются в одну строку; если же критерии будут отбираться в режиме «ИЛИ» (марка машины или объем двигателя), тогда они записываются в разные строки

Допустим, есть таблица с 2 колонками — наименование товара и количество. Всего товаров 3 — бананы, апельсины, мандарины, а количество — 10, 20 и 15 штук соответственно. После того как будет скопирована шапка, можно создать условие, например, показать товары, количество которых меньше или равно 15. То есть под скопированной шапкой в колонке «Кол-во» надо написать <=15. Затем надо запустить расширенный фильтр, указать исходный диапазон (исходная таблица), диапазон условий (таблица, где указано «кол-во <=15») и нажать «ОК». Исходная таблица изменится: теперь тут будут отображены только бананы (10 штук) и мандарины (15 штук).

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

Простой фильтр в Экселе

Рассмотрим по пунктам, как установить фильтр в Экселе по столбцам:

1. Кликните по любой ячейке внутри таблицы (а лучше – выделите всю таблицу с шапкой)
2. Нажмите на ленте Данные – Сортировка и фильтр – Фильтр
3. В первой строке таблицы появятся кнопки со стрелками, обозначающие, что для столбца можно задать фильтр:

Как теперь отфильтровать? Например, нужно в таблице примера выбрать всех людей из Алушты:

1. В колонке «Город» нажмем на значок фильтра
2. Откроется список, где галками отмечены все города, а также, форма поиска
3. В поиске напишем «Алушта» и нажмем Ок

Теперь у нас на экране только те строки, где город – Алушта. Остальные строки не удалены, а только скрыты.

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

Чтобы повторно применить установленный фильтр, когда информация изменилась, нажмите Данные – Сортировка и фильтр – Повторить, или комбинацию клавиш Ctrl+Alt+L.

А когда нужно сбросить все примененные фильтры, жмем Данные – Сортировка и фильтр – Очистить.

Вы можете фильтровать в нескольких колонках одновременно, даже во всех!

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

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

1. Создать место для ввода параметра фильтрования. Удобнее всего оставлять это место над основной таблицей и не забывать копировать шапку (названия столбцов), чтобы не запутаться, в какую колонку вводить этот критерий.
2. Ввести нужный показатель для фильтрации. Например, все записи, чьи значения столбца больше 1000 (> 1000).
3. Пройти во вкладку «Данные». В разделе «Фильтрация и сортировка» выбрать пункт «Дополнительно».
4. В открывшемся окошке указать диапазоны рассматриваемых значений и ячейку со значением рассматриваемого критерия.
5. Нажать на Ок. После этого будет выведена отфильтрованная по заданному критерию таблица.
6. Скопировать результат разграничения. Вставить отфильтрованную таблицу куда-нибудь в сторону на том же листе Excel. Можно воспользоваться другой страницей.
7. Выбрать «Очистить». Данная кнопка находится во вкладке «Данные» в разделе «Фильтрация и сортировка». После ее нажатия отфильтрованная таблица вернутся в первоначальный вид. И можно будет работать с ней.
8. Далее необходимо снова выделить свободное место для таблицы, которая будет отфильтрована.
9. Потом нужно скопировать шапку (названия столбцов) основного поля и перенести их в первую строчку освобожденного под отфильтрованную структуру места.
10. Пройти во вкладку «Данные». В разделе «Фильтрация и сортировка» выбрать «Дополнительно».
11. В открывшемся окошке выбрать диапазон записей (столбцов), по которому будет проводиться фильтрация.
12. Добавить адрес ячейки, в которой записан критерий разграничения, например, «город Одесса».
13. Нажать на Ок. После этого произойдет фильтрация по значению «Одесса».
14. Скопировать отфильтрованную таблицу и вставить ее либо на другой лист документа, либо на той же странице, но в стороне от основной.
15. Снова нажать на «Очистить». Все, готово. Теперь у вас имеются три таблицы. Основная, отфильтрованная по одному значению (>1000), а также та, что отфильтрована по другому значению (Одесса).

Настраиваемый тестовый фильтр

Расскажу, как поставить фильтр в Excel на два условия в одной ячейке. Для этого кликнем Текстовые фильтры – Настраиваемый фильтр.

Пусть нам понадобилось отобрать людей с именем Богдан или Никита. Запишем логику, как на картинке

А вот результат:

Как определить, какой выбрать оператор сравнения, «И» или «ИЛИ»? Логика такая:

• И – когда нужно, чтобы выполнились оба условия одновременно
• ИЛИ – когда достаточно выполнения хотя бы одного условия из двух

Кроме того, в условии можно использовать операторы:

• ? – это один любой символ
• * – любое количество любых символов

Например, чтобы выбрать ФИО, в котором присутствует строка «ктор», запишем условие так: *ктор*.

Сортировка в Excel

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

• Первыми идут цифры от меньшего к большему;
• Затем идут специальные символы ($, %, * и т.д.);
• За ними следуют знаки препинания;
• Далее начинаются алфавиты, сначала английский, затем русский. Также не учитывается регистр, т.е. заглавные и прописные буквы равны друг другу (А=а).

Для того чтобы выполнить упорядочивание, пройдите на вкладку «Данные» и выберите раздел «Сортировка и фильтр». Для сортировки предназначено 3 значка:

Сортировка по возрастанию (от А до Я), по убыванию (от Я до А) и значок сортировки для задания условий по нескольким столбцам одновременно.

Рассмотрим все пункты подробнее.

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

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

Установка порядка первого столбца таблицы при выборе первого варианта:

Как видно строки сохранили свою целостность, и имена по-прежнему соответствуют своим фамилиям.

Те же самые действия, но при выборе второго варианта:

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

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

Если возникла необходимость задать порядок по нескольким столбцам, то в этом поможет значок «Сортировка», который уже упоминался. Выделите диапазон, который необходимо упорядочить

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

Для примера будем использовать следующую таблицу:

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

Выделите диапазон нашей таблицы и кликните по значку «Сорировка». Появится следующее окно:

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

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

Следующий выпадающий список задает порядок: по возрастанию, по убыванию и т.п.

Для добавления второго и последующих столбцов для сортировки воспользуйтесь кнопкой «Добавить уровень» и задайте для него параметры.

Для нашего примера используем трехуровневую сортировку. Для первого уровня зададим сортировку по «Столбец1», порядок «От Я до А». Для второго уровня зададим сортировку по «Столбец2», порядок «По возрастанию». Для третьего уровня по «Столбец3», порядок «По убыванию». Параметры для сортировки везде «значения».

Рассмотрим получившийся результат:

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

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

Также в окне сортировки можно задавать различные параметры:

• Поставив флажок на поле «Мои данные содержать заголовки», программа учтет, что верхние ячейки диапазонов упорядочивать не нужно;
• Нажав кнопку «Параметры» Вы получите окно, в котором можно задать:

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

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

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

У подавляющего большинства пользователей Excel при слове «фильтрация данных» в голове всплывает только обычный классический фильтр с вкладки Данные — Фильтр (Data — Filter) :

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

Для начала вставьте над вашей таблицей с данными несколько пустых строк и скопируйте туда шапку таблицы — это будет диапазон с условиями (выделен для наглядности желтым):

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

Именно в желтые ячейки нужно ввести критерии (условия), по которым потом будет произведена фильтрация. Например, если нужно отобрать бананы в московский «Ашан» в III квартале, то условия будут выглядеть так:

Чтобы выполнить фильтрацию выделите любую ячейку диапазона с исходными данными, откройте вкладку Данные и нажмите кнопку Дополнительно (Data — Advanced) . В открывшемся окне должен быть уже автоматически введен диапазон с данными и нам останется только указать диапазон условий, т.е. A1:I2:

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

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

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

Добавляем макрос

«Ну и где же тут удобство?» — спросите вы и будете правы. Мало того, что нужно руками вводить условия в желтые ячейки, так еще и открывать диалоговое окно, вводить туда диапазоны, жать ОК. Грустно, согласен! Но «все меняется, когда приходят они » — макросы!

Работу с расширенным фильтром можно в разы ускорить и упростить с помощью простого макроса, который будет автоматически запускать расширенный фильтр при вводе условий, т.е. изменении любой желтой ячейки. Щелкните правой кнопкой мыши по ярлычку текущего листа и выберите команду Исходный текст (Source Code) . В открывшееся окно скопируйте и вставьте вот такой код:

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

Так все гораздо лучше, правда?

Реализация сложных запросов

Теперь, когда все фильтруется «на лету», можно немного углубиться в нюансы и разобрать механизмы более сложных запросов в расширенном фильтре. Помимо ввода точных совпадений, в диапазоне условий можно использовать различные символы подстановки (* и ?) и знаки математических неравенств для реализации приблизительного поиска. Регистр символов роли не играет. Для наглядности я свел все возможные варианты в таблицу:

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

Рассмотрим применение расширенного фильтра в Excel с целью отбора строк, содержащих слова «Москва» или «Рязань». Условия для фильтрации должны находиться в одном столбце. В нашем примере – друг под другом.

Заполняем меню расширенного фильтра:

Получаем таблицу с отобранными по заданному критерию строками:

Выполним отбор строк, которые в столбце «Магазин» содержат значение «№1», а в столбце стоимость – «>1 000 000 р.». Критерии для фильтрации должны находиться в соответствующих столбцах таблички для условий. На одной строке.

Заполняем параметры фильтрации. Нажимаем ОК.

Оставим в таблице только те строки, которые в столбце «Регион» содержат слово «Рязань» или в столбце «Стоимость» — значение «>10 000 000 р.». Так как критерии отбора относятся к разным столбцам, размещаем их на разных строках под соответствующими заголовками.

Применим инструмент «Расширенный фильтр»:

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

1. Результат формулы – это критерий отбора.
2. Записанная формула возвращает результат ИСТИНА или ЛОЖЬ.
3. Исходный диапазон указывается посредством абсолютных ссылок, а критерий отбора (в виде формулы) – с помощью относительных.
4. Если возвращается значение ИСТИНА, то строка отобразится после применения фильтра. ЛОЖЬ – нет.

Отобразим строки, содержащие количество выше среднего. Для этого в стороне от таблички с критериями (в ячейку I1) введем название «Наибольшее количество». Ниже – формула. Используем функцию СРЗНАЧ.

Выделяем любую ячейку в исходном диапазоне и вызываем «Расширенный фильтр». В качестве критерия для отбора указываем I1:I2 (ссылки относительные!).

В таблице остались только те строки, где значения в столбце «Количество» выше среднего.

Чтобы оставить в таблице лишь неповторяющиеся строки, в окне «Расширенного фильтра» поставьте птичку напротив «Только уникальные записи».

Нажмите ОК. Повторяющиеся строки будут скрыты. На листе останутся только уникальные записи.

Виды фильтров в Excel

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

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

1. Сортировка по возрастанию или по убыванию.
2. «Все» — Excel покажет (восстановит) все строки.
3. «Первые 10» — Excel отобразит первые 10 записей. При выборе этого пункта откроется новое окно, в котором можно указать число записей, выбрать, какие из них показывать (наибольшие или наименьшие), а также установить ограничение на количество отображаемых записей.
4. «Условие» — здесь пользователь может самостоятельно создать 2 критерия отбора данных, объединяя их простыми операторами И, ИЛИ.
5. Любой из элементов. Здесь можно выбрать любое значение, которое находится в колонке. Например, если столбец состоит из наименований товаров, то в перечне элементов будут указаны абсолютно все товары. Пользователь может указать любой из них.

6. «Пустые» и «Непустые» — Excel отобразит пустые (или непустые) ячейки. Данная опция появляется только в том случае, если в столбце есть незаполненные поля.

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

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

Имеется простая таблица, не отформатированная и не объявленная списком. Включить автоматический фильтр можно через главное меню.

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

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

Пользоваться автофильтром просто: нужно выделить запись с нужным значением. Например, отобразить поставки в магазин №4. Ставим птичку напротив соответствующего условия фильтрации:

Сразу видим результат:

Особенности работы инструмента:

1. Автофильтр работает только в неразрывном диапазоне. Разные таблицы на одном листе не фильтруются. Даже если они имеют однотипные данные.
2. Инструмент воспринимает верхнюю строчку как заголовки столбцов – эти значения в фильтр не включаются.
3. Допустимо применять сразу несколько условий фильтрации. Но каждый предыдущий результат может скрывать необходимые для следующего фильтра записи.

У расширенного фильтра гораздо больше возможностей:

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



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

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

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

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

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

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

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

Скачать пример c применением расширенного фильтра в Excel.

Находится расширенный фильтр по следующему пути: вкладка «Данные» -> область «Сортировка и фильтр» -> иконка «Дополнительно».

В качестве примера рассмотрим задачу:

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

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

• Если до окончания срока договора осталось меньше месяца;
• Если среднемесячный заказ не менее 50 000 рублей для Москвы, 40 000 для Санкт-Петербурга, 35 000 для Казани и 30 000 рублей для Краснодара;
• Клиент не должен быть подключен к бонусам.

Вывести отработанный список на отдельный лист.»

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