Книга: Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)
Назад: Минимальные и максимальные значения с условиями
Дальше: Функции баз данных
Расширенный фильтр
Файл с примерами: Расширенный фильтр и функции БД.xlsxРасширенный фильтр — это инструмент для фильтрации данных по одному или нескольким наборам условий, в том числе не пересекающихся друг с другом. Он гораздо мощнее обычного автофильтра.
Напомним, что обычный автофильтр можно включить на вкладке «Данные» → «Фильтр» (Data → Filter), а также с помощью сочетания клавиш Ctrl + Shift + L (
+ ⇑ + F). Кроме того, при создании таблицы (Ctrl + T или Ctrl + L) кнопки фильтра тоже появятся.
+ ⇑ + F). Кроме того, при создании таблицы (Ctrl + T или Ctrl + L) кнопки фильтра тоже появятся.Расширенный же фильтр (диалоговое окно с его настройками) находится справа от обычного: в русскоязычном Excel это кнопка «Дополнительно», а в англоязычном интерфейсе — Advanced.
Отличия расширенного фильтра от обычного фильтра на рабочем листе в следующем:
• можно фильтровать результат на месте (как в обычном фильтре), а можно сразу выводить результаты в другое место, не фильтруя исходный диапазон;
• условия задаются не в самом фильтре, а в отдельных ячейках;
• можно фильтровать данные по нескольким независимым наборам условий (когда одному значению в одном столбце соответствует другое значение в другом).
Поясним последний пункт на примере. Допустим, у нас есть такие данные.
С помощью обычного фильтра (или срезов таблицы — slicers) мы можем отобрать строки с продуктами «Курс» и «Консультация» и каналом продаж «Сайт».
А если перед нами задача выбрать строки с консультациями, проданными через сайт, и курсами, проданными через рассылку?
Это уже потребует двух отдельных фильтров. Одним обычным фильтром это сделать невозможно: если мы и выберем два продукта (консультации и курсы) в соответствующем столбце, мы не сможем для каждого из них настраивать фильтрацию в другом столбце отдельно (для консультаций — один канал продаж, а для курсов — другой). Мы можем только выбрать несколько продуктов и несколько каналов продаж для каждого из них. А если же у нас непересекающиеся условия, то необходим расширенный фильтр.
Здесь поможет только расширенный фильтр — много обычных в одном. Для начала нужно задать условия фильтрации, они задаются в ячейках в любом месте рабочего листа Excel. Первой строкой таблицы с условиями должны быть заголовки из исходной таблицы (форматирование сохранять не обязательно, только точное совпадение текста, то есть можно скопировать лишь значение или ячейку вместе с форматом или просто ввести заголовок вручную).
Столбцы нужны не все, а только те, на которые будут накладываться условия фильтрации. В нашем примере — «Продукт» и «Откуда».
Под этими заголовками нужно ввести условия — критерии фильтрации. В нашем примере (напомним: строки с консультациями, проданными через сайт, и курсами, проданными через рассылку) условия будут выглядеть так.
После того как вы сформировали таблицу условий, можно вызывать диалоговое окно расширенного фильтра (Advanced).
Рассмотрим подробнее диалоговое окно расширенного фильтра.
Запись условий для расширенного фильтра
Критерии (условия) записываются как в обычных функциях подсчета и суммирования — в виде точного текстового значения или числа; со знаками <>=; можно ставить условия на даты. Если оставить ячейку пустой, то в соответствующем поле может быть любое значение.
В критериях можно использовать те же символы подстановки, что и в функциях СУММЕСЛИМН / SUMIFS, СЧЁТЕСЛИМН / COUNTIFS, СРЗНАЧЕСЛИМН / AVERAGEIFS, — звездочку * (ноль или более любых символов), знак вопроса ? (один любой символ).
Рассмотрим несколько примеров условий.
Пустое условие
Если в условиях расширенного фильтра какая-либо ячейка не заполнена — это соответствует любому значению. В следующем примере будут отфильтрованы все строки с продуктом «Консультация» (с любым каналом продаж) и строки с продуктом «Курс», но только со значением «Рассылка» в столбце «Откуда».
Числовое условие
На столбцы с числами можно накладывать условия в виде неравенств, как в функциях СУММЕСЛИМН / SUMIFS.
Например, в следующем случае мы фильтруем строки с курсами и консультациями с суммой строго выше 15 000.
А в следующем — строки с продажами курсов от 14 000 и продажами консультаций на любую сумму.
Условие «не равно»
Если вам нужно исключить определенные значения, можно использовать знаки «больше» и «меньше» — это значит «не равно». Например, следующим условием мы исключаем курсы: <>Курс
Один столбец с условием: фильтрация по списку значений
В некоторых ситуациях расширенный фильтр может пригодиться, даже если условие накладывается только на один столбец.
Допустим, вам нужно отфильтровать данные по десяти филиалам, когда в таблице их несколько десятков или даже сотни. Конечно, в таком случае можно обойтись и обычным фильтром, но выбирать в выпадающем списке десять-двадцать значений из сотен — мучительно. Проще сделать список в отдельных ячейках, добавить к нему заголовок из исходной таблицы и с помощью расширенного фильтра получить результат.
Символы подстановки
Что, если мы хотим отфильтровать данные только по юрлицам (ООО и ОАО, но не ИП)?
Можно воспользоваться символами подстановки. Напомним: звездочка заменяет любой текст от 0 до бесконечности символов, а знак вопроса — это один любой символ.
Вот такое условие позволит отфильтровать по условию «ячейка начинается с буквы О, любого символа и затем снова буквы О», которому будут соответствовать и ООО, и ОАО:
О?О*
Следующие условия — это продажи курсов и консультаций ООО и ОАО.
А если в примере с филиалами вам нужны модели только одного бренда, можно добавить условие на названия товаров — бренд, окруженный звездочками (то есть любой текст + название бренда + любой текст).
