Расширенный фильтр и его настройка
Недостатки стандартного пользовательского фильтра.
Наверное, каждому пользователю, работающему в программе Microsoft Excel, известен пользовательский фильтр, используемый для выбора значений. С помощью пользовательского фильтра делают построчную выборку необходимых позиций из обрабатываемой таблицы. Это удобно, если требуется из множества имеющихся позиций отобрать только нужные строки.
Такой фильтр удобен для выбора заданной по условию информации и сокрытия позиций, не соответствующих условию. Можно быстро выбрать заданные строки в зависимости с предъявляемыми к ним требованиями. Из-за этих возможностей стандартный фильтр часто применяется пользователями. Однако стандартный автофильтр имеет и недостатки, и эти недостатки во многих случаях сводят преимущество пользовательского фильтра на нет.
1. Невозможность применения гибкого отбора необходимых позиций из таблицы. Если требуется выбрать хотя бы три варианта значений, то это уже становится проблемой, особенно если варианты разные. Фильтр хорош, если надо отобрать ячейки с аналогичным содержимым. На примере ниже идет обзор ячеек с, содержащих слово будильник;
Рисунок 1.
Однако, если нужно выбрать и будильники, и чайники, и напольные весы – фильтр ставит в тупик. Отмечать и убирать галочки на соответствующих позициях не удобно, а вариант с применением для выбора настраиваемого фильтра дает возможность отбора максимум двух значений.
Рисунок 2.
Причем с числовыми данными дело обстоит если и лучше, то ненамного. Можно выбрать один диапазон значений, но когда таких диапазонов нужно выбрать несколько, то вновь попадаем в тупик. Снова приходится выбирать с помощью чекбоксов-галочек, так как расчищенный способ предлагает только один вариант – или выбор между двумя указанными значениями, либо вне их. Иногда этого недостаточно.
2. Не оптимизирована обработка с помощью фильтра дат. Максимум – выбор групп значений помесячно. Если же требуется проанализировать с помощью фильтра реализацию поквартально или в разрезе дней недели – ничего не получится. Таких вариантов в обычном пользовательском фильтре просто нет. Максимум – выбор периода, да и то в узком промежутке. Например, на прошлой неделе. А как быть, если интересует не прошлая неделя, а первая неделя прошлого года?
Рисунок 3.
3. Набор условий, которые применяются для отбора позиций, не отличается разнообразием. но применяемые решения однотипные. Возможен выбор первых 10 наименьших или наибольших значений. Возможен вариант получения с помощью фильтра значения выше или ниже среднего значения в колонке. При этом проанализируется среднее значение для всех ячеек в выбранном для фильтрации диапазоне. А если требуется выбрать данные выше среднего значения для определенных групп, и не одной, и не двух? А если надо узнать не значения общего среднего , а выше медианного значения? А если интересует первый либо последний квартиль?
4. Статичность. В данном случае речь идет о статичном расположении отфильтрованной таблицы. Другими словами, если кто-то решит применить новые правила фильтрации, то старые варианты собьются. Если потребуется к ним вернуться, то все настройки фильтрации понадобится задавать заново.
Пользователи Excel привыкли обходить такие неприятные моменты своими методами. Наиболее часто применяется копирование отфильтрованных значений в новое место. Так же выполняют фильтр несколько раз, если требуется выбрать различные параметры для фильтра одновременно. Если же необходимо отобрать значения по признакам, которые нельзя задать стандартным фильтром, то либо от такого отбора отказываются совсем, либо применяют по возможности похожий вариант, разумеется, с минимумом успеха.
И в то же время немногие люди знают, и еще меньше людей применяют на практике расширенный, или дополнительный фильтр, значок которого располагается на вкладке «Данные» рядом со значком стандартного фильтра.
Рисунок 4.
Преимущества дополнительного фильтра.
Использование дополнительного расширенного (в дальнейшем будем такой вариант фильтра называть расширенным) фильтра дает возможность избежать тех проблем, о которых говорилось выше, и не только. Единственный недостаток, если это можно так назвать, это необходимость составления отдельного диапазона с условиями. С одной стороны, это требует времени. А с другой, разве меньше времени тратиться при выборе различных вариантов при стандартной фильтрации? Особенно если варианты отбора по заданному фильтру, и не по одному, требуется указывать в разных колонках исходной таблицы? К примеру, если необходимо показать реализацию именно заданных категорий товара в конкретном городе с учетом заданного диапазона цен в первом квартале интересующего года? Применяя же расширенный фильтр, пользователь получает гибкие и мощные инструменты для фильтрации. Впрочем, судите сами.
1. У количества позиций отбираемых значений нет ограничений. Сколько вариантов укажете, причём разных, столько он и отфильтрует. Причём, в отличие от стандартного фильтра, для отбора значений, совпадающих с выбранным, не нужны никакие манипуляции с настраиваемой сортировкой.
2. Возможно указание с помощью правил произвольных вариантов сортировки. Чтобы их задать, стоит только указать нужные логические выражения.
3. В полном соответствии с предыдущим пунктом можно отобрать произвольные даты, задав требуемый период в условии для фильтра.
4. Допускается не только фильтр таблицы на месте расположения, но и копирование итогов фильтрации выполнения в новое расположение. Другими словами, вместо двух действий получаем только одно.
5. Допускается отбрасывание дубликатов в фильтруемой графе таблицы. Это тоже полезно на практике, когда существует вероятность дублирования записей из-за механических ошибок при вводе значений.
6. Допустимо применение своих правил отбора для каждого заданного условия для выбора показателей. К примеру, отобрать данные по наличию на складе брюк в ценовом диапазоне со 3500 до 7000, рубашек – в диапазоне с 1500 до 4500, ботинок от 38 до 42 размера. И это при выполнении одной сортировки. В обычном же случае пользователю бы пришлось сортировать таблицу трижды и при этом итог каждой сортировки по отдельности копировать в новое место во избежание потери результата.
Таких преимуществ у расширенного фильтра по сравнению со стандартным вариантом на самом деле гораздо больше. Так как же настроить и применить настраиваемую сортировку? Рассмотрим использование расширенного фильтра в Excel на практике с использованием наглядных примеров.
Параметры расширенной сортировки.
После запуска дополнительного фильтра откроется окно с настройками расширенной фильтрации. Этих настроек немного сортировки немного. Рассмотрим их по порядку и заодно ознакомимся с тонкостями при настройке параметров.
Рисунок 5.
1. Режим обработки полученных при работе фильтра итогов. Они могут быть либо применены к исходной таблице, либо результат фильтра скопируется в новое место. В таком случае исходная таблица останется неизменной. Если выбран вариант «скопировать результат в другое место», то надо учесть, что такое копирование возможно только в диапазон на том же листе, что и исходная таблица.
2. Исходный диапазон значений. При выборе учитываем следующую тонкость. Независимо, выбрана ли одна ячейка в исходной таблице или диапазон ячеек, исходная таблиц будет выбрана полностью. В качестве границ будут использоваться первая же пустая строка и пустая колонка внутри таблицы. А это значит, что выбор программы далеко не всегда будет совпадать с тем, который нужен исполнителю. Поэтому тут пригодится простой совет. Вначале выберите ПУСТУЮ ячейку ВНЕ таблицы, которую нужно обработать. Запустите настройку параметров расширенного фильтра и только тогда указывайте нужный диапазон в обрабатываемой таблице. Кроме этого, перед работой позаботьтесь о заголовках в исходной таблице. В отличии от автофильтра, расширенный фильтр очень «нервно» реагирует на их пустые ячейки в заголовке, а также на присутствие в заголовке объединенных ячеек. В качестве примера приведем две ситуации с одной и той же таблицей.
а) стандартный автофильтр. Как видно, все в порядке, проблем не наблюдается.
Рисунок 6.
б) расширенный фильтр. Как говорят – комментарии излишни.
Рисунок 7.
3. Диапазон условий. Главный момент для применения расширенного фильтра. От правильной настройки условий зависит результат применения фильтра. Содержит заголовок поля таблицы (графы таблицы), к которому применяются правила., а также параметры этих правил, указанные построчно. Подробнее о применяемых условиях и настройке условий поговорим ниже.
4. Диапазон, в который будет помещен результат обработки таблицы дополнительным фильтром. Указывается, если при настройке режима обработки выбран вариант «копировать результат в другое место». При выборе задайте только верхнюю левую ячейку будущего диапазона.
5. Данный пункт позволяет отбросить дубликаты записей. В результате итоги не будут содержать повторяющихся значений. Однако в итоге при копировании результата в другое место диапазону с скопированными значениями будет присвоено стандартное имя «Извлечь». Если вы решите таким образом расправится с повторами в отдельно выбранной колонке, например, чтобы затем по ним провести выборочное суммирование – скорее всего получите проблему.
Настройка простых условий.
Как же настраиваются условия для расширенной фильтрации? На самом деле это не так и сложно. Для начала рассмотрим простой пример с использованием самих значений из фильтруемых столбцов.
1. Прежде всего определитесь с колонками, для позиций которых будет применен фильтр. В диапазоне пользовательских условий необходимо задать эти заголовки. Таким образом программа «понимает», какие значения из выбранного столбца или столбцов будет использоваться для фильтра. Можно указать несколько колонок, причем их порядок вовсе не должен соответствовать порядку колонок в самой таблице. В нашем примере идет выбор информации по Москве и Московской области из графы «Подразделение Отделение» с значением выше двух миллионов по графе «Реализация, литров».
Рисунок 8.
2. Внесите значения, которые хотите выбрать. При этом для выпора точного значения укажите конкретный текст или число, для выбора разных вариантов используйте подстановочные знаки в виде звездочек и вопросительных знаков. Звездочки позволят заменить произвольный набор произвольных символов, и в том числе их полное отсутствие. Вопросительный же знак определяет возможное наличие только одного произвольного символа. Для сравнения чисел можно использовать стандартные операторы сравнения. Только не перепутайте расположение, как на рисунке 9!
Рисунок 9
3. На последнем шаге указываем в параметрах расширенного фильтра необходимые значения адресов исходной таблицы, области условий и места вставки.
Рисунок 10
В итоге получаем результат, похожий на указанный ниже. Для экономии места позиции были сгруппированы.
Рисунок 11.
Теперь попробуем отобрать в другой таблице только позиции будильников с любыми характеристиками и артикулом на первых складах Нур-Султана и Алматы с количеством не равным нулю. Обратите внимание на значение в первой строке для первого склада Алматы и во второй строке для склада 1 в Нур-Султане!
Рисунок 12.
А теперь выберем именно весы и отсечем различные весы напольные, кухонные и прочие. В этом случае нужно указать интересующее слово явным образам в виде формулы, где после знака равенства указано в кавычках нужное значение. В нашем случае формула будет такой.
=«весы»
Сама компания Microsoft рекомендует для указания условий отбора вариант
=”=Фрукты”
Однако, как видно из примера, эти варианты равнозначны.
Рисунок 13.
Похожая настройка выполняется и для дат. Только в этом случае необходимо сцепить знаком амперсанда нужный поверочный знак и дату. Дело в том, что и то, и другое надо задать в кавычках, а если написать сразу, то получится, что нет кавычек и после знака сравнение, и перед значением даты.
В показанном ниже примере выведены все продажи продуктов начиная с 1 сентября 2018 года. посмотрите на формулу
Рисунок 14.
Более сложные ситуации.
Разумеется, в реальной практике встречаются и более сложные ситуации. Их можно разбить на две части.
Первый вопрос, приходящий на ум – как быть если необходимо указать не просто значение, равное выбранному, либо только меньше или больше его. Как указать значение внутри определенного промежутка или вне его? Ответ прост. Надо продублировать в условиях нужную колонку, указав в ней нужные границы. На примере ниже отобраны все инструменты с ценой начиная с 2300 и до 3000 включительно.
Рисунок 15.
А вот так настроим выбор продаж за период с мая по ноябрь
Рисунок 16.
Как видно, такое размещение аналогично функции «И». А вот размещение данных по разным строкам равносильно обработке условий функцией «ИЛИ».
На очередном примере отобрана информация по пассатижам с ценой от трех до четырех тысяч, а также по реализации лосьона в мае 2018 года.
Рисунок 17.
И еще раз усложняем ситуацию.
Разберем вопрос, как применить произвольную формулу для отбора значений. В таком случае есть два способа работы. При использовании первого создается отдельная колонку, в которой текущее значение выбранного столбца сравнивается с эталонным. В свою очередь, эталонное значение тоже извлекается с помощью формулы. Для запуска фильтра в качестве условия используется заголовок данного столбца и значение «ИСТИНА» в нем. После применения фильтра данный столбец можно удалить. Ниже с помощью расширенного фильтра отобраны товары с ценой, превышающей медианную.
Рисунок 18.
При применении второго способа создается диапазон условий с произвольным названием. В нем указывается логическое выражение для поверки интересующего столбца, причем в качестве критерия задается значение первой ячейки заданной колонки. Ниже с помощью расширенного фильтра и контрольного условия тоже отобраны товары с ценой, превышающей медианную.
Рисунок 19.
А вот таким образом обобраны все товары, где стоимость превысила среднее значение для данного наименования. Обратите внимание на формулу!
Рисунок 20.
Если же требуется отобрать позиции, где заданное слово отсутствует, используем комбинацию функции ЕОШИБКА и функцию ПОИСК, а для одновременной их проверки применяем функцию «И». Например, чтобы выбрать все позиции в таблице, где нет ни пассатижей, ни лосьона, применяем такую формулу в условии.
=И(ЕОШИБКА(ПОИСК(“пассатижи”;B3));ЕОШИБКА(ПОИСК(“лосьон”;B3)))
Рисунок 21.
Можно так же принять комбинацию функций ВПР и ЕНД. Однако предлагаем попробовать создать такую формулу для проверки самостоятельно
Делаем выводы.
Расширенный фильтр действительно позволяет более гибко отобрать значения для фильтра в отличии от стандартного варианта. Используя расширенный фильтр можно указать самые разные варианты отбора значений, количество и характер которых зависят только от желания и фантазии специалиста.
Но и это еще не все. Дело в том, что способы указания условий для расширенного фильтра работают и при указании диапазона с критериями для функции БДСУММ, позволяющей провести суммирование независимо от количества и расположения критериев, по которым выполняется суммирование. Подробнее об этой и других функциях будет рассказано в новой статье.
Текущее же занятие подошло к концу. Желаем нашим читателям успешной работы. читателями.
До встречи на наших тренингах по Excel и другим программам!
