9.4 Фильтрация списков
Под фильтрацией списков понимается выделение из всего списка только тех записей, значения полей которых удовлетворяют заданным критериям.
Exсel позволяет осуществлять фильтрацию несколькими способами, которые имеют различные функциональные возможности. В Excel для фильтрации списков есть три инструмента- Автофильтр, который применяется в случае простых условий отбора, Пользовательский автофильтр и Расширенный фильтр для усложненных критериев.
9.4.1 Использование для фильтрации автофильтра
Для вызова Автофильтра нужно выделить любую ячейку списка и выполнить команду меню Данные - Фильтр - Автофильтр.
Откройте рабочую книгу "Списки.xls" выполняйте все приведенные ниже операции
После вызова Автофильтра на листе таблицы рядом с каждым заголовком столбца появятся кнопки автофильтра в виде стрелок (см. рис.)
.
Щелчок по любой стрелке раскроет список значений, допустимых для задания условий отбора по этому столбцу (см. рис.).

Фильтрацию по одному столбцу выполните на примере выделения операций только по счету №1: Сделайте щелчок по стрелке рядом со 2-ым столбцом и выделите условие "равное единице". Результат фильтрации представлен на рисунке.
 
Строка с числом 10 в области условий позволяет найти заданное число (заданный процент) наибольших или наименьших элементов в списке. Выбор этого условия выводит диалоговое окно Наложение условий по списку (см. рис.), используя которое можно найти заданное число (заданный процент) наибольших или наименьших элементов в списке.

Результат фильтрации первых 10 наибольших значений сумм кредита представлен на рисунке.

9.4.2 Пользовательский автофильтр

Строка Условие  в области списка доступных значений Автофильтра позволяет включить диалоговое окно Пользовательский автофильтр, используя которое можно задать более сложное условие для фильтрации.
Пример 27.  Требуется отфильтровать все операции, проведенные в период с 5.01 по 24.01 включительно.
Решение
Откройте рабочую книгу "Списки.xls"
1. Для решения задачи раскройте список в столбце Дата операции и выберите в нем “Условие”. Раскроется диалоговое окно Пользовательский автофильтр (см. рис.)

 
2. В соответствующих полях этого диалогового окна установите значения, как показано на рисунке ( используйте раскрывающиеся списки).
3. Щелчкните на кнопке ОК - список будет отфильтрован и в нем останутся только те строки, которые удовлетворяют заданному условию (см. рис).

 
  9.4.3 Расширенный фильтр
Расширенный фильтр, в отличие от «Автофильтра» позволяет:
• Задавать условия фильтрации для нескольких столбцов, соединенных логическим оператором.
• Задать несколько условий для одного столбца.
• Задать вычисляемые условия. Например, отфильтровать слушателей, получивших оценки на 10% выше среднего балла.
• Скопировать результаты фильтрации в другое место.
Другим отличием от автофильтра является то, что условия для фильтрации записываются на свободном участке рабочего листа.
Задание диапазона условий
Рассмотрим технологию задания диапазона условий на примере.
Пример 28 Требуется отфильтровать сведения об операциях по номерам счетов с 5 по 8 включительно.
Решение
1. Откройте рабочую книгу "spis.xls"
1. В свободных ячейках рабочего листа определите диапазон, в котором будет записано условие для фильтрации (например, G3:H4).
2. Запишите в первой строке этого диапазона наименования столбцов списка, на которые будут наложены условия фильтрации (это лучше сделать копированием), а во второй выражение условия (см. рис.).
 
Замечание 1: При задании выражений для условий на одной строке условия как бы соединяются логическим оператором «И». Если нужно применить логический оператор «ИЛИ», то условия нужно записать на разных строках.
3. Установите курсор в область списка.
4. Включите команду Данные - Фильтр- Расширенный фильтр. Система выведет на экран диалоговое окно (см. рис.).

5. Введите информацию о диапазоне списка (в примере - $A$2:$E$124) и о диапазоне критериев (в примере - $G$3:$H$4).
6. Установите начальную ячейку диапазона, куда будет выведен результат (G6) После щелчка на кнопке «ОК» получим выборку по заданным условиям


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

  Использование условий с вычисляемыми значениями
Вычисляемые условия позволяют использовать в критериях выборки значения, возвращаемые формулой. При их применении необходимо соблюдать правила:
• Заголовок над вычисляемым условием должен отличаться от заголовков столбцов в списке. Это условие противоположно требованиям для фор-мирования обычных условий.
• Ссылки на ячейки, находящиеся вне списка, должны быть абсо-лютными.
• Ссылки на ячейки в списке должны быть относительными.
Упражнение 21. Необходимо отобрать строки в которых значение числа в столбце Кредит больше среднего значения кредита за учетный период.
Решение
1. В любой ячейке вне списка запишите формулу вычисления среднего значения (например, в ячейке G2) (см. рис.).

2. В ячейку G4 запишите «Выборка» (или что-либо другое), а в ячейку G5 выражение вычисляемого условия:  =D3>$G$2. Если условия фильтрации в ячейках записаны правильно, то в них появится запись ИСТИНА или ЛОЖНО.
3. Результирующая выборка приведена на рисунке.
К следующей       К предыдущей   В содержание темы