Фильтрация с применением инструмента

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

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

– задавать условия фильтрации для нескольких столбцов, соединенных оператором отношения;

– задавать несколько условий для данных столбца списка;

– задавать вычисляемые условия;

– помещать результаты фильтрации в любое место рабочего листа.

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

Формирование диапазона условий

Условия фильтрации для инструмента Расширенный фильтр формируются в соответствии с правилами:

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

– в ячейках строк, расположенных ниже строки заголовков, записываются выражения условий с использованием знаков сравнения (=, >=, <=, <>);

– считается, что выражения условий, записанные в одной строке, соединены отношением И, а записанные в разных строках - отношением ИЛИ;

– диапазон условий не должен содержать пустых строк или пустых столбцов.

Пример 3.4 Требуется составить выражение условий для фильтрации исходного списка в соответствии с условиями: дата операции 10-20января включительно, номера счетов 5-14 включительно.

Решение

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

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

Рис. 3.23

Выполнение фильтрации

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

– установить курсор в область исходного списка и выполнить команду меню Данные/Фильтр/Расширенный фильтр - откроется окно диалога Расширенный фильтр (рис. 3.24);

Рис. 3.24 Диалоговое окно Расширенный фильтр

– в группе Обработка диалогового окна установить соответствующий переключатель Фильтровать список на месте или Скопировать результат в другое место;

– в поле Исходный диапазон ввести адрес диапазона, содержащего исходный список (если курсор был предварительно установлен в области списка, то адрес диапазона будет установлен автоматически);

– в поле Диапазон условий ввести адрес диапазона, содержащего условия;

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

– если в исходном списке могут быть повторяющиеся записи, а в результирующую таблицу нужно поместить только одну из них, то нужно установить флажок Только уникальные записи, эта опция добавляет к имеющемуся еще один фильтр - Скрытие полностью повторяющихся строк, который работает только в том случае, если результат копируется в другое место;

– щелкнуть на кнопке ОК, и в указанном для вывода диапазоне будет получен результат фильтрации.

Для условий примера 3.4 результат фильтрации приведен на рис. 3.25.

Рис. 3.25 Результат фильтрации по заданным условиям

 

В диапазоне условий можно ввести любое количество условий.

Пример 3.5 Требуется отфильтровать записи исходной таблицы, которые отвечают условиям: дата операции 10-20 января включительно, номера счетов с 5-го по 14-й, сумма прихода от 1000 до 2500 включительно, а также сведения обо всех расходных операциях в период с 24 по 29 января с суммой больше 1100 по клиентам, фамилии которых начинаются с букв «Я» или «Б».

Решение

Сформируем строку заголовка для условия фильтрации. Из условия задачи следует, что условия должны быть наложены на данные столбцов исходного списка с именами Дата операции, № счета, Приход, Расход и Фамилия клиента, поэтому на свободном участке рабочего листа в ячейки диапазона, например Н3:О3, введем эти названия.

В строках, расположенных ниже строки заголовка, запишем условия, исходя из следующих соображений: условия «дата операции 10- 20 января включительно, номера счетов с 5-го по 14-й, сумма прихода от. 1000 до 2500 включительно» выполняются совместно, к ним применима операция отношения И, поэтому запишем их в одной строке под строкой заголовка; условия «дата операции 10-20 января включительно, номера счетов с 5-го по 14-й, сумма прихода от 1000 до 2500 включительно, фамилии которых начинаются с буквы Я» связано с первым условием отношением ИЛИ, поэтому в диапазоне условий запишем его в строке ниже; условия «дата операции 10-20 января включительно, номера счетов с 5-го по 14-й, сумма прихода от 1000 до 2500 включительно, фамилии которых начинаются с буквы Б» связано с предыдущими условиями отношением ИЛИ, поэтому в диапазоне условий запишем его в следующей строке.

Результат фильтрования по сформированному условию приведен на рисунке 3.26.

Рис. 3.26