1.11.5. Фильтрация списка

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

Списки Excel можно фильтровать двумя способами:

 автоматическая фильтрация (автофильтр) – для фильтрации по простым критериям;

 расширенная фильтрация – для фильтрации по сложным (произвольным) критериям.

Автоматическая фильтрация

Для того, чтобы автоматически отфильтровать список, необходимо сначала установить курсор на одну из его ячеек. Затем выполнить команду Данные  Фильтр  Автофильтр. Excel проанализирует список и добавит в строку заголовков полей элементы управления – раскрывающиеся списки, как показано на рис. 1.36. В каждом элементе собраны уникальные значения, расположенные в этом столбце. Выбор какого-либо значения приводит к сокрытию всех строк с другими значениями. Список можно одновременно фильтровать по нескольким столбцам.

Рис. 1.36. Пример использования автофильтра

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

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

Кроме всех значений столбца, раскрывающийся список содержит следующие элементы:

 Все показывает все элементы столбца. Используется для отмены фильтрации столбца.

 Первые 10 выбирает 10 (или другое количество) наибольших или наименьших элементов списка.

 Условие позволяет фильтровать список по нескольким условиям.

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

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

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

Если пользователю необходимо фильтровать списки с использованием нескольких критериев, используется элемент раскрывающегося списка Условие. Появившееся диалоговое окно, приведенное на рис. 1.37, позволяет установить Пользовательский автофильтр, который имеет несколько опций:

 значения больше или меньше установленного (например, отобрать все записи, указывающие на цены товаров, превышающие 2000 р.);

 значения в интервале (например, отобрать все записи, указывающие на цены товаров, превышающие 1000 р. и не превышающие 5000 р.);

 два отдельных значения (например, отобрать записи, в которых находится информация об объеме продаж в городах Гомель ИЛИ Брест);

 выборка по шаблону, используя символы подстановки * и ? (например, для того, чтобы вывести на экран записи только о тех товарах, наименование которых начинается с буквы М, используется шаблон М*).

Рис. 1.37. Диалоговое окно Пользовательский автофильтр

Расширенная фильтрация

Если недостаточно возможностей автофильтра, используют расширенную фильтрацию. Этот фильтр предоставляет следующие возможности:

 определять более сложный критерий фильтрации;

 устанавливать вычисляемый критерий фильтрации;

 перемещать копии строк, отвечающих определенному критерию, в указанную часть таблицы.

Расширенная фильтрация выполняется командой Данные  Фильтр  Расширенный фильтр. На экране появляется диалоговое окно, приведенное на рис. 1.38. При этом для использования расширенной фильтрации, кроме исходного диапазона необходимо задать диапазон условий (критериев).

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

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

1. Текстовые или числовые критерии (при фильтрации используется сравнение с числом или строкой с помощью операторов «равно» (=), «больше чем» (>), «меньше чем» (<), «не равно» (<>), «больше или равно» (>=), «меньше или равно» (<=).

Например:

 отобрать слова, начинающиеся с буквы Л и заканчивающиеся буквой Я (ответ: >=Л);

 отобрать слова, кроме тех, которые начинаются с буквы С (ответ: <>С*);

 отобрать все слова Январь (ответ: =Январь);

 отобрать слова, начинающиеся с букв См (ответ: См*);

 отобрать слова, начинающиеся с буквы С и заканчивающиеся буквой С (ответ: С*С);

 отобрать слова размером из трех символов, начинающиеся с буквы С и заканчивающиеся буквой С (ответ: С?С).

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

 не использовать заголовки полей списка в диапазоне критериев, создавать новый заголовок или оставлять пустую ячейку;

 можно использовать любое количество вычисляемых критериев, а также сочетание вычисляемых критериев с невычисляемыми;

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

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