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

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

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

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

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

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

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

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

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

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

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range(«A2:I5»)) Is Nothing Then On Error Resume Next ActiveSheet.ShowAllData Range(«A7»).CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range(«A1»).CurrentRegion End If End Sub

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

Подготовка диапазона для условий фильтрации

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

Также важно помнить, что при указании диапазона условий на шаге 3 (работа в Мастере «Расширенного фильтра») необходимо выделить заголовок и строки, где есть какие-то критерии. Пустые строки выделять не нужно, иначе фильтр воспримет их как сигнал «Показать все строки”.

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

Правильная организация данных для расширенной фильтрации

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

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

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

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

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

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

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

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

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

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

Следует быть особенно внимательным при вводе критериев в виде даты. Даты необходимо вводить с косой чертой. Например, для выбора всех транзакций после 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.

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

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

Как сделать (наложить) фильтр на таблицу Excel

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

Примечание:

Если вы выберете ВСЕ строки, Excel автоматически установит фильтр на ВСЕ столбцы, в которых есть хоть что-то. Если в столбце ничего нет, то фильтр не применяется.

После того, как вы выбрали нужные столбцы, необходимо настроить фильтр. Самый простой способ — перейти на вкладку ленты «Главная» / «Фильтр и сортировка» и нажать на фильтр. Вот скриншот из Office 2010, в других версиях Excel фильтр применяется к таблице точно так же.

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

Если вы не выделяете несколько ячеек, а просто щелкаете одну, фильтр добавляется ко ВСЕЙ строке, как если бы вы ее выбрали.

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

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

Как пользоваться фильтром в Эксель

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

Значение фильтра:

заключается в том, что Excel оставит только те строки таблицы, которые в ЭТОМ столбце (с настроенным фильтром) содержат ячейку с выбранным значением. Остальные строки будут скрыты.

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

Наложение нескольких фильтров на таблицу Эксель

Фильтры в электронной таблице Excel можно комбинировать. Наложение различных фильтров происходит по принципу логического «И». Что это значит? Давайте посмотрим пример.

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

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

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

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

Как задать несколько параметров

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

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

Теперь нужно дополнительно отделить товары на сумму 100 рублей, для этого воспользуемся проекцией в разделе «Цена».

Нажимаем «ОК» и получаем только товары стоимостью 100 рублей, произведенные в России.

Как поставить расширенный поиск

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

Затем на свободной строке под скопированными заголовками задайте необходимые условия поиска. Например, вам нужно найти товары, произведенные в России, продаваемые менеджером Ивановым, стоимостью менее 300 рублей.

После того, как параметры были введены правильно, нужно снова открыть вкладку «Данные» и выбрать функцию «Дополнительно».

Перед пользователем появится окно, в котором он должен заполнить две строки:

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

Сформировав оба диапазона, нажмите «ОК» и оцените результат.

Как удостовериться, поставлен ли фильтр

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

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

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

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

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

Private Subworksheet_Change (Цель ByVal как диапазон)

Если nointersect(target,range(«A2:I5»)) ничего не значит, то

При ошибке Возобновить Далее

ActiveSheet.ShowAllData

Range(«A7»).CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range(«A1»).CurrentRegion

Это закончится, если

Под конец

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

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

Подробнее о сложных запросах

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

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

Здесь нужно учитывать такие моменты:

Символ * означает любое количество символов, а ? — единственный.
Числовые и текстовые запросы обрабатываются по-разному. Так, если пользователь указывает условную ячейку, в которой указана цифра 5, это не значит, что фильтр будет искать все числа, начинающиеся с этой цифры. Но если в нем указана буква B, то фильтр отберет те данные, которые содержат текст, начинающийся с нее. Простыми словами, если текст не начинается со знака =, то он эквивалентен тому, который заканчивается знаком *.
Если используются даты, они вводятся в том же формате, что и в США. Сначала указывается месяц, затем число, затем год, а элементы перечисления разделяются дробью. Это касается даже локализованного для России Excel.

Логические условия

Большим преимуществом расширенного фильтра является возможность использования логических условий. Что это? Логическое условие — это условие, в котором используется логический оператор И или ИЛИ. Конечно, есть и другие, но они описаны выше.

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

То есть логическое условие «И» на самом деле читается как «если критерий 1 и критерий 2 соответствуют критерию 3». Например, если яблоки и морковь стоят одинаково, то ИЛИ — аналогично, просто вместо «И» вставляется «ИЛИ». На практике это может выглядеть так. Если пользователи приобрели товары у Компании 1 или Компании 2, покажите эти покупки. В этом случае при покупке чего-либо у третьего лица оно не отображается.

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

Итак, что вам нужно знать? Если условия находятся в одной строке, хотя и в разных ячейках, то они считаются связанными друг с другом логическим оператором «И».

В описанном примере фильтр покажет только бананы, купленные в московском Ашане в третьем квартале.

Если условия нужно связать с помощью логического оператора «ИЛИ», то их нужно писать на отдельных строках.

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

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

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

Настройка фильтрации

Для настройки фильтрации используйте:

кнопка «Отфильтровано» во вкладке «Конструктор» ленты (также во вкладке «Главная» или «Данные» для быстрого отчета);
вкладка «Фильтрация» на боковой панели.

Примечание. Чтобы применить расширенные настройки фильтра, используйте язык программирования Fore.

Доступны следующие параметры фильтра:

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

- равно А;
- не равно А;
- большая А;
- минор А;
- больше или равно А;
- меньше или равно А;
- между А и В включительно;
- минор А или мажор В;

Чтобы указать сложную формулу, используйте опцию «выражение». При использовании этого элемента доступна подстановка «значение», соответствующая значению ячейки. При использовании выражения доступны стандартные арифметические операции, объединяющие выражение с квадратными скобками. В запланированных отчетах можно использовать функции прикрепленных модулей>;

Скрыть по формуле. Строки/столбцы, соответствующие формуле, указанной через «Редактор выражений>», будут скрыты>»;
Исключить разметку из фильтрации. Выбранные элементы измерения будут скрыты. Настройка доступна при фильтрации строк и столбцов отдельно.

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

Отключение фильтрации

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

Срезы

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

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

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

Для этого выполните следующие действия:

Выберите ячейку в таблице и выберите вкладку [Макет].
В группе Сервис [Инструменты] (или на вкладке Вставка [Вставка] в группе Фильтры [Фильтры]) нажмите кнопку Вставить[ слайсер] .

В диалоговом окне отметьте поля, которые вы хотите включить в слайсер, и нажмите OK.

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

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

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

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

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

Давайте посмотрим, как фильтровать ячейки с определенным текстом в Excel. Проще всего по аналогии с предыдущим примером ввести в поиск нужный текст (или его часть.

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

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

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

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

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

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

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

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

Подробнее о логических операторах можно прочитать в этой статье.

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

? любой персонаж
* — любое количество символов

Например, чтобы выбрать полное имя, содержащее строку «ctor», мы записываем условие следующим образом: *ctor*.

Как поставить фильтр в Экселе на столбец с числами

Числовые фильтры тоже гибко настраиваются, есть такие методы подбора:

То же самое или не то же самое
Больше, больше или равно, меньше, меньше или равно
Между (в промежутке)
Первые 10
Выше среднего, ниже среднего

Посмотрите, есть интересные варианты. Попробуем выбрать людей с продажами 200 тысяч — 500 тысяч. Выберем элемент МЕЖДУ. Мне это нравится:

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

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

Не стандартным способом. Microsoft Excel выбирает данные только в столбцах. Поэтому необходимо искать другие решения.

Вот примеры расширенных критериев строки фильтра в Excel:

Используйте формулы, чтобы отобразить именно те данные в нужной вам строке. Например, сделать какой-то индикатор выпадающим списком. А в следующую ячейку введите формулу с помощью функции ЕСЛИ. Когда конкретное значение выбрано из выпадающего списка, его параметр появляется рядом с ним.

Чтобы показать пример работы фильтра строк в Excel, давайте создадим таблицу:

Для списка продуктов создайте раскрывающийся список:

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

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

Поэтому с помощью инструмента выпадающего списка и встроенных функций Excel выбирает данные в строках на основе определенных критериев.