2015-05-15
2200
Фильтры обеспечивают выборку подмножества данных из списка. Имеются две разновидности этой команды: Автофильтр и Расширенный фильтр.
Автофильтр выбирается на вкладке Данные, группа Сортировка и фильтрация, команда Фильтр. Для каждого столбца строится список значений, который используется для задания условий фильтрации. По отдельному столбцу можно указать следующие условия: равно, не равно, начинается с, заканчивается на, содержит, не содержит.
Рис. 2.8
Если условие фильтрации для значений в столбце более сложное, выбирается режим Настраиваемый фильтр, который выводит диалоговое окно Пользовательский автофильтр (рис. 2.9).
Рис. 2.9
Условие для отбора записей по значению в определенном столбце может состоять из двух самостоятельных частей, соединенных логической связкой И/ ИЛИ. Каждая часть условия включает:
· оператор отношения: равно, не равно, больше, больше или равно, меньше;
· значение, которое может выбираться из списка или содержать шаблонные символы «*» и «?».
Можно задать условия отбора для нескольких столбцов независимо друг от друга, фильтрация записей выполняется по всем условиям одновременно. Все записи, не прошедшие через фильтр, будут скрыты. Отфильтрованные записи можно выделить и скопировать в другое место, выделить и очистить содержимое или удалить.
Расширенный фильтр используется для более сложных критериев отбора. И по сравнению с командой Автофильтр имеет некоторые дополнительные возможности:
· допускается задавать условия, соединенные логическим оператором ИЛИ, для нескольких столбцов (оператор И расширенного фильтра аналогичен тому, если дважды применить Автофильтр);
· допускается задавать три и более условий для конкретного столбца;
· допускается задавать вычисляемые условия;
· позволяет сразу копировать отобранные строки без применения команд Копировать и Вставить.
Команда Расширенный фильтр требует задания условий отбора строк в отдельном диапазоне рабочего листа (лучше выше или ниже списка, чтобы не скрыть их при фильтрации). Диапазон условий должен содержать, по крайней мере, две строки (заголовок и условие). За исключением вычисляемых условий, заголовки в диапазоне условий должны точно совпадать с заголовками столбцов списка.
Условия на одной сроке считаются соединенными логическим оператором И, на разных строках – ИЛИ. Пустая ячейка – любое значение.
Расширенный фильтр обеспечивает использование двух типов критериев для фильтрации записей списка:
· критерий сравнения;
· вычисляемый критерий.
Обычно критерий фильтрации формируется в нескольких столбцах и тогда его называют множественным критерием соответствующего типа. Важной особенностью этого режима является необходимость формирования заранее до выполнения самой команды фильтрации специального блока (области) для задания сложных поисковых условий, называемых интервалом критериев.
Условие для отбора записей по значению в определенном столбце может состоять из двух самостоятельных частей, соединенных логической связкой И/ ИЛИ. Каждая часть условия включает:
· оператор отношения: равно, не равно, больше, больше или равно, меньше;
· значение, которое может выбираться из списка или содержать шаблонные символы «*» «и»?.
Технология использования расширенного фильтра включает два этапа:
1-й этап – формирование интервала критериев;
2-й этап – фильтрация записей списка.
1-й этап. Формирование интервала критериев для расширенного фильтра. В случае использования простого критерия сравнения интервал критериев содержит строку имен столбцов и произвольное число строк для задания поисковых условий. Проще всего скопировать первую строку области списка в отдельное место (на тот же или другой лист, в другую рабочую книгу). Далее ненужные имена столбцов из интервала критериев можно удалить. Порядок следования имен столбцов в интервале критериев – произвольный. Критерий сравнения формируется при соблюдении нескольких требований.
1. Состав столбцов интервала критериев определяется столбцами, по которым задаются условия фильтрации записей, или составом столбцов извлекаемой информации, причем порядок их следования безразличен.
2. Метки критериев должны точно совпадать с названиями столбцов исходного списка.
3. Ниже меток располагаются критерии сравнения типа:
· точного значения;
· значения, формируемого с помощью операторов отношения;
· шаблона значения, включающего символы «*» и «?».
Правила формирования множественного критерия:
1) если критерии (условия) указываются в каждом столбце на одной строке, то они считаются связанными условием И;
2) если условия записаны в нескольких строках, то они считаются связанными условием ИЛИ.
Пример
1. Условие выбора записей о поставщике Аврора за 06.09.2008:
| Наименование поставщика | Дата поставки |
| Аврора | 06.09.2008 |
2. Условие выбора записей о поставщике ВосходИЛИ по товару Ткань:
| Наименование поставщика | Наименование товара |
| Восход | |
| Ткань |
3. Условие выбора записей о поставке товара Шуба за 07.09.2008 ИЛИ 08.09.2008:
| Наименование товара | Дата поставки |
| Шуба | 07.09.2008 |
| Шуба | 08.09.2008 |
Вычисляемый критерий. Вычисляемые условия отличаются от обычных условий сравнения тем, что позволяют использовать значения, возвращаемые формулой. Вычисляемый критерий представляет собой формулу, записанную в строке условий интервала критериев, которая возвращает логическое значение ИСТИНА или ЛОЖЬ. Формула строится с использованием: адресов ячеек, встроенных функций, констант различных типов (число, текст, логическая константа), операторов отношения.
Даже опытные пользователи Excel могут попасть в затруднительное положение при использовании вычисляемых условий. Ниже приведены три правила, которые могут вам помочь.
· Заголовок над вычисляемым условием должен отличаться от любого из столбцов списка. Заголовок условия может быть пустым или содержать произвольный текст. Обратите внимание, что это ограничение полностью противоположно требованию для обычных условий.
· Ссылки на ячейки, находящиеся вне списка, должны быть абсолютными ($).
· Ссылки на ячейки в списке должны быть относительными (исключение составляет зафиксированный диапазон, например, для подсчета среднего балла студентов. В этом случае диапазон оценок фиксируется, для того чтобы он не изменялся при применении фильтра для всех студентов).
Пример
1. Выбрать записи о товарах с фактической суммой поставки ниже средней:
2. Выбрать записи о поставщике Аврора с суммой поставки фактической выше средней:
После завершения ввода вычисляемого критерия в ячейке должна появиться логическая константа ИСТИНА или ЛОЖЬ – результат применения сформированного вычисляемого критерия по отношению к первой записи списка.
2 этап. Фильтрация записей расширенным фильтром. После подготовки интервала критериев курсор устанавливается в список и на вкладке Данные, группа Сортировка и фильтрация, выбирается команда Дополнительно (рис. 2.10).
Рис. 2.10
Фильтровать записи списка можно на месте либо копировать в указанную область на текущем рабочем листе. Для копии на другой лист или книгу следует установить курсор по месту копии, а затем выполнить команду фильтрации, указывая соответствующие интервалы списка и критериев.
Интервалы списка и критериев включают все строки, в том числе и строку наименования столбцов. Если предполагается копирование результата в другое место, указывается левая верхняя ячейка области. Переключатель Только уникальные записи позволяет исключить дублирование записей. Для сложных по логике обработки запросов фильтрация записей списка может выполняться постепенно, т.е. копируется первый результат фильтрации, к нему применяется следующий вариант фильтрации и т.д. Для снятия действия условий фильтрации выполняются команды Данные, Сортировка и фильтр, Очистить.
