3.2 Расширенный фильтр
Расширенный фильтр задаётся командой Данные \ Дополнительно в окне Расширенный фильтр. При этом используется два типа критериев для фильтрации записей:
критерий сравнения;
вычисляемый критерий.
Если критерий формируется в нескольких полях (столбцах), то его называют множественным критерием (поэтому фильтр называется расширенным).
Особенностью расширенного фильтра является то, что критерии отбора данных формируются вне таблицы и производится в 2 этапа:
1 Сначала формируется диапазон условия (область критерия) в отдельном блоке ячеек листа MS Excel – вне таблицы БД, на свободном месте Листа.
2 Затем командой Данные \ Дополнительно в окне Расширенный фильтр выполняется фильтрация записей списка по сформированному вне таблицы множественному критерию.
3.2.1 Формирование критерия сравнения
Область критериев поиска представляет собой таблицу, которая состоит из строки имён полей и нескольких строк для задания условий по каждому столбцу.
Для создания таблицы рекомендуется сначала скопировать на свободное место листа (или в другой лист, в другую книгу) всю строку с именами столбцов. Далее ненужные столбцы можно будет удалить.
Ниже имён столбцов располагаются строки с условиями (критериями) сравнения типа:
точного значения;
значения, формируемого с помощью операторов отношений (<, >, =, <>, <=, >=) и
шаблона значения с символами *, ?.
Условия (критерии), указанные в каждом столбце одной строки связаны логическим оператором И. Условия, записанные в нескольких строках связаны оператором ИЛИ.
Пример 1 Отобрать записи о сдаче экзамена студентами группы КТ-11 на оценки 4 и 5 преподавателю с кодом 1.
Расширенный фильтр может быть составлен тремя способами, т.е. на свободном месте листа нужно создать одну из таблиц:
1 вариант. Связка И, критерии по № группы и Код преп заданы как точные значения, критерий по Оценка – с оператором отношения >.
|
№ группы |
Оценка |
Код преп |
|
КТ-11 |
>3 |
1 |
|
|
|
|
Установим курсор в список и выполним команду Данные\ Сортировка и фильтр \ Дополнительно (окно Расширенный фильтр) -рис. 9.7.
Установим курсор в поле Исходный диапазон окна Расширенный фильтр и протянем мышью по всей таблице, включая строку имён полей A2:E12;
Переведём курсор в поле Диапазон условий, протянем мышью по ячейкам области условий A14:C16 и нажмем ОК;
Поставим флажок в строке "скопировать результат в другое место" и выделим ячейки A18:E28 (блок должен быть не меньше исходного)
Переключатель Только уникальные записи позволяет исключить дублирование записей.
Результаты фильтрации – рис. 9.7:
Рисунок 9.7 – Исходная БД, ввод критерия и результат фильтрации
2 вариант. Связка ИЛИ, условия (точные значения) записаны в двух строках.
|
№ группы |
Оценка |
Код преп |
|
КТ-11 |
4 |
1 |
|
КТ-11 |
5 |
1 |
Результат фильтрации – рис. 9.8:
Рисунок 9.8 – Исходная БД, критерий и результат фильтрации
3.2.2 Формирование вычисляемого критерия
Вычисляемый критерий – это формула в строке для задания условий (в таблице отбора), которая состоит из адресов ячеек, встроенных функций, констант и операторов отношения.
Примечание. Имя столбца с формулой вычисляемого критерия должно отличаться от имени столбца в списке.
Пример 2 Выбрать записи о сдаче экзаменов студентами группы КТ-11 с оценкой ниже среднего балла или записи с оценкой 5.
Здесь возможны 3 варианта: