Формирование критерия сравнения

Область критериев поиска представляет собой таблицу, которая состоит из строки имён полей и нескольких строк для задания условий по каждому столбцу.

Для создания таблицы рекомендуется сначала скопировать на свободное место листа (или в другой лист, в другую книгу) всю строку с именами столбцов. Далее ненужные столбцы можно будет удалить.

Ниже имён столбцов располагаются строки с условиями (критериями) сравнения типа:

▪ точного значения;

▪ значения, формируемого с помощью операторов отношений (<, >, =, <>, <=, >=) и

▪ шаблона значения с символами *,?.

Условия (критерии), указанные в каждом столбце одной строки связаны логическим оператором И. Условия, записанные в нескольких строках связаны оператором ИЛИ.

Пример 1 Отобрать записи о сдаче экзамена студентами группы КТ-11 на оценки 4 и 5 преподавателю с кодом 1.

Расширенный фильтр может быть составлен тремя способами, т.е. на свободном месте листа нужно создать одну из таблиц:

1 вариант. Связка И, критерии по № группы и Код преп заданы как точные значения, критерий по Оценка – с оператором отношения >.

 

№ группы	Оценка	Код преп
КТ-11	>3	1

 

1) Установим курсор в список и выполним команду Данные\ Сортировка и фильтр \ Дополнительно (окно Расширенный фильтр) -рис. 9.7.

2) Установим курсор в поле Исходный диапазон окна Расширенный фильтр и протянем мышью по всей таблице, включая строку имён полей A2:E12;

3) Переведём курсор в поле Диапазон условий, протянем мышью по ячейкам области условий A14:C16 и нажмем ОК;

4) Поставим флажок в строке "скопировать результат в другое место" и выделим ячейки A18:E28 (блок должен быть не меньше исходного)

Переключатель Только уникальные записи позволяет исключить дублирование записей.

Результаты фильтрации – рис. 9.7:

 

 

Рисунок 9.7 – Исходная БД, ввод критерия и результат фильтрации

 

2 вариант. Связка ИЛИ, условия (точные значения) записаны в двух строках.

№ группы	Оценка	Код преп
КТ-11	4	1
КТ-11	5	1

 

 

Результат фильтрации – рис. 9.8:

 

 

Рисунок 9.8 – Исходная БД, критерий и результат фильтрации

 

Формирование вычисляемого критерия

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

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

Пример 2 Выбрать записи о сдаче экзаменов студентами группы КТ-11 с оценкой ниже среднего балла или записи с оценкой 5.

Здесь возможны 3 варианта:

Вариант

№ группы	Оценка1
КТ-11	=ИЛИ(D3<=СРЗНАЧ($D$3:$D$12); D3=5)

Столбец Оценка назван отличающимся именем Оценка1, формула введена с помощью Мастера функций, где для логической функции ИЛИ в строке Логическое 1 записано выражение D3<=СРЗНАЧ($D$3:$D$12, а в строке Логическое 2 - выражение D3=5.

Результат фильтрации приведён на рис. 9.9. Средняя оценка составляет 4,1 балла.

Рисунок 9.9 – Фильтрация по вычисляемому критерию, вариант 1

2 вариант.

№ группы	Оценка1
КТ-11	=D3<=СРЗНАЧ($D$3:$D$12)
КТ-11	= D3=5

Результат фильтрации приведён на рис. 9.10:

 

Рисунок 9.10 – Фильтрация по вычисляемому критерию, вариант 2

Вариант

№ группы	Оценка1	Оценка
КТ-11	=D3<=СРЗНАЧ($D$3:$D$12)
КТ-11		5

 

Результат фильтрации приведён на рис. 9.11:

Рисунок 9.11 – Фильтрация по вычисляемому критерию, вариант 3

После ввода вычисляемого критерия в ячейке должна появиться логическая константа Истина или Ложь, как результат применения критерия к первой строке списка, а формула отобразится в строке ввода (рис. 9.7).

 

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

Для отключения фильтра, т.е. снятия действия условий фильтрации выполняется команда Данные\Фильтр