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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Больше про логические операторы вы можете прочесть в этой статье.

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

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

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

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

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

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

1. В диалоговом окне Расширенный фильтр выбрать вариант записи результатов: фильтровать список на месте или скопировать результат в другое место .

1. Указать Исходный диапазон , выделяя исходную таблицу вместе с заголовками столбцов.
2. Указать Диапазон условий , отметив курсором диапазон условий, включая ячейки с заголовками столбцов.
3. Указать при необходимости место с результатами в поле Поместить результат в диапазон , отметив курсором ячейку диапазона для размещения результатов фильтрации.
4. Если нужно исключить повторяющиеся записи, поставить флажок в строке Только уникальные записи .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Теперь нужно сделать:

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

Смотрите на фото, получили список только для Агрыза и Азова:

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

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

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

Сортировка в Excel по дате и месяцу

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

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

1. В ячейке A1 введите название столбца «№п/п», а ячейку A2 введите число 1. После чего наведите курсор мышки на маркер курсора клавиатуры расположенный в нижнем правом углу квадратика. В результате курсор изменит свой внешний вид с указательной стрелочки на крестик. Не отводя курсора с маркера нажмите на клавишу CTRL на клавиатуре в результате чего возле указателя-крестика появиться значок плюсик «+».
2. Теперь одновременно удерживая клавишу CTRL на клавиатуре и левую клавишу мышки протяните маркер вдоль целого столбца таблицы (до ячейки A15).

В результате чего столбец автоматически заполниться последовательностью номеров транзакций от 1 до 14.

Полезный совет! В Excel большинство задач имеют несколько решений. Для автоматического нормирования столбцов в Excel можно воспользоваться правой кнопкой мышки. Для этого достаточно только лишь навести курсор на маркер курсора клавиатуры (в ячейке A2) и удерживая только правую кнопку мышки провести маркер вдоль столбца. После того как отпустить правую клавишу мышки, автоматически появиться контекстное меню из, которого нужно выбрать опцию «Заполнить». И столбец автоматически заполниться последовательностью номеров, аналогично первому способу автозаполнения.

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

1. Ячейки D1, E1, F1 заполните названиями заголовков: «Год», «Месяц», «День».
2. Соответственно каждому столбцу введите под заголовками соответствующие функции и скопируйте их вдоль каждого столбца:

• D1: =ГОД(B2);
• E1: =МЕСЯЦ(B2);
• F1: =ДЕНЬ(B2).

В итоге мы должны получить следующий результат:

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

Допустим мы хотим выполнить сортировку дат транзакций по месяцам. В данном случае порядок дней и годов – не имеют значения. Для этого просто перейдите на любую ячейку столбца «Месяц» (E) и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Сортировка по возрастанию».

Теперь, чтобы сбросить сортировку и привести данные таблицы в изначальный вид перейдите на любую ячейку столбца «№п/п» (A) и вы снова выберите тот же инструмент «Сортировка по возрастанию».

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

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

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

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

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

Когда вспомогательная таблица готова, можно приступать к следующему шагу. Поместив курсор на любую ячейку начальной или вспомогательной таблицы, в основном меню программы кликните по вкладке «Данные», выберите из открывшегося блока инструментов “Фильтр” пункт “Дополнительно”.

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

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

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

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

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

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

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

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

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

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

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

Таким образом, принцип работы фильтрации в Microsoft 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, то критерии будут выглядеть вот так:

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

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

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

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

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

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

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

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

Теперь нужно сделать:

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

Смотрите на фото, получили список только для Агрыза и Азова:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Срезы

Программа Microsoft Excel позволяет прикрепить к таблицам интерактивные элементы для сортировки и фильтрации – срезы. После выхода версии 2013-о года появилась возможность подключать срезы к обычным таблицам, а не только к сводным отчетам. Разберемся, как создать и настроить эти опции.

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

1. Кликаем по одной из ячеек таблицы – на панели инструментов появится вкладка «Конструктор», которую нужно открыть.

1. На вкладке следует найти кнопку «Вставить срез» – кликаем по ней и переходим к новому открывшемуся окну.

22

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

23

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

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

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

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

24

1. Также возможно изменить положение срезов на экране. Воспользуемся кнопками «Переместить вперед» и «Переместить назад» в разделе «Упорядочить». Необходимо выбрать один из срезов и нажать кнопку на панели инструментов. Теперь при перемещении по экрану срез будет оказываться поверх всех срезов или попадет под них.
2. Удаление срезов – несложная операция. Выберите лишнее окно и нажмите клавишу «Delete» на клавиатуре. Срез исчезнет с экрана и перестанет влиять на фильтрацию данных в таблице.

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

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

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

Подведём итоги

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

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

Скачать файл, на примере которого я рассматривал работу с фильтрами, вы можете по этой ссылке.

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

• Фильтры Excel – прайс лист.zip