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

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

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

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

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

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

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

Условия (критерии), указанные в каждом столбце одной строки связа-

ны логическим оператором И. Условия, записанные в нескольких строках связаны оператором ИЛИ.

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

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

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

 

 

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

2 вариант. Связка И, все критерии заданы как точные значения, поле Оценка используется дважды:

 

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

 

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

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

 

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

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

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

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

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

1 вариант

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

 

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

2 вариант.

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

3 вариант

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

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

Фильтрация записей расширенным фильтром

После выполнения 1 этапа (пп.3.2.1, 3.2.2) сформированный критерий используется следующим образом (этап 2):

▪ установить курсор в список и выполнить команду Данные \ Фильтр \ Расширенный фильтр.

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

▪ перевести курсор в поле Диапазон условий и протянуть мышью по ячейкам области условий и нажать ОК (рис. 6)

На месте списка появится результат фильтрации (рис. 7).

 

Рисунок 6 Задание параметров расширенного фильтра

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

Рисунок 7 Результат фильтрации по 3 варианту

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

 

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

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

Фильтрация с помощью формы

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

Для вызова формы нужно установить курсор в область списка и выполнить команду Данные \ Форма…. Появляется экранная форма Список (рис. 8), в которой выводится одна запись списка. Перемещение по списку выполняется кнопками Назад, Далее, добавление и удаление записей – кнопками Добавить и Удалить. Содержимое формы можно редактировать.

Для фильтрации данных нажимается кнопка Критерии (форма очищается автоматически или с помощью кнопки Очистить, кнопка Критерии заменяется на кнопку Правка), в её поля вводится один или несколько условий отбора (например, >3 в поле Оценка) и при перемещении по форме кнопками Назад и Далее можно просмотреть уже отфильтрованные данные.

Рисунок 8 Экранная форма для работы со списком данных

Возврат к форме производится кнопкой Правка, а выход из формы – кнопкой Закрыть.

Если команды Добавить и Удалить вносят изменения в список на листе книги Excel, то отфильтрованные данные можно просмотреть только в окне формы.

 

4 Контрольные вопросы

1 Что называется списком (базой данных) в Excel?

2 Как называются элементы таблицы в терминах БД?

3 Сортировка данных, её порядок при сортировке нескольких полей.

4 Фильтрация с помощью автофильтра, критерии фильтрации.

5 Формирование критерия сравнения в расширенном фильтре.

6 Формирование вычисляемого критерия в расширенном фильтре.

7 Фильтрация данных расширенным фильтром.

8 Фильтрация данных с помощью формы.

9 Как добавить или изменить данные в списке с помощью формы?

Задание

1 Создать в Excel список согласно варианту задания (не менее 10 записей), недостающие записи заполнить своими данными. Предусмотреть повторение записей в отдельных полях – для выполнения сортировки.

2 Отсортировать данные списка по трём полям (поля выбираются самостоятельно).

3 Выполнить фильтрацию данных списка тремя способами:

▪ с помощью автофильтра,

▪ с помощью расширенного фильтра по критерию сравнения,

▪ с помощью расширенного фильтра по вычисляемому критерию.

Варианты задания (№ варианта - №компьютера в аудитории)

1 вариант.

Таблица "Учебники"

Код	Авторы	Название учебника	Город	Издательство	Год издания
	Макарова Н.В.	Информатика	Москва	Финансы
	Выгодский В.Н.	Высшая математика	Киев	Высшая школа
…	…	…	…	…	…
	Симонов П.А.	Общая химия	Москва	ABF

2 вариант.

Таблица "Врачи"

Код	Фамилия И.О.	Специальность	Должность	Отделение	Зарплата
	Панов Н.В.	Отоляринголог	Глав. врач	Терапевтич.	6300р.
	Перов В.Н.	Стоматолог	Зав. отделением	Стоматологич.	4850р.
…	…	…	…	…	…
	Попов Г.А.	Хирург	Врач	Хирургическое	4200р.

 

3 вариант.

Таблица "Больные"

Код	Ф. И.О. больного	Болезнь	№ палаты	Лечащий врач	Дата пост.
	Ванин Н.В.	Сахар. диабет		Вавлова А.А.	26.03.04.
	Петров В.Н.	Перитонит		Селезнёв П.П.	10.03.04
…	…	…	…	…	…
	Попович Г.А.	Аппендицит		Харатьян С.Г.	22.03.04.

 

4 вариант.

Таблица "Спортсмены"

Код	Ф. И.О.	Вид спорта	Дата рожд.	Разряд	Тренер
	Витаанен Н.В.	Тяжёлая. атлетика	06.12. 1990	Мастер	Власов А.А.
	Провский В.Н.	Бокс	15.03.1998	ЗМС	Родин П.Э.
…	…	…	…	…	…
	Гуревич Г.А.	Таэквондо	12.10.1997	МСМК	Второв В.Д..

5 вариант.

Таблица "Маршруты"

Код	Водитель	Маршрут	Дата	Время	Марка автобуса
	Ветров Н.В.	Краснодар - Сочи	06.12. 04	6.40	"Икарус"
	Вронский В.Н.	Армавир - Ростов	07.12.04	10.15	"ЛиАЗ"
…	…	…	…	…	…
	Гуров Г.А.	Темрюк - Туапсе	12.10.04	20.35	"Мерседес"

6 вариант.

Таблица "Рейсы"

Борт №	№ рейса	Аэропорт назнач.	Дата	Время	Тип самолёта
	Ю-1138	Домодедово	06.12. 04	7.30	Як-42
	Ю-1142	Внуково	15.03.04	15.40	Ил-62
…	…	…	…	…	…
	Ю-1136	Домодедово	12.10.04	20.20	Ту-154

7 вариант.

Таблица "Работники"

Код	Ф. И.О.	Должность	Профессия	Зарплата	Стаж, лет
	Витаанен Н.В.	Мастер	Токарь	8200р.
	Провский В.Н.	Рабочий	Электрик	9650р.
…	…	…	…	…	…
	Гуревич Г.А.	Начальник цеха	Механик	16800р.

8 вариант.

Таблица "Штат"

Код	Ф. И.О.	Должность	Звание.	Уч. степень	Дата рождения
	Азаров Н.В.	Декан	Профессор	Д.т.н	25.05.1949
	Ржевский В.Н.	Зам. декана	Доцент	К.т.н.	20.02.1959
…	…	…	…	…	…
	Кудасова Г.А.	Секретарь	Инженер	-	22.03.1980

 

9 вариант.

Таблица "Команда"

Код	Ф. И.О.	Специализация	Дата рожд.	Разряд	Тренер
	Витин Н.В.	Вратарь	06.12. 1990	Мастер	Власов А.А.
	Провский В.Н.	Нападающий	15.03.1998	ЗМС	Родин П.Э.
…	…	…	…	…	…
	Вуйкич Г.А.	Полузащитник	12.10.1997	МСМК	Власов А.А.

10 вариант.

Таблица "Поезда"

Код	№ поезда	Маршрут	Время отправления	Дата отправления.	Вагон, место	Стоим. билета
		Москва-Курская	23.40	06.12. 04	П	520р.
		Москва-Павелец.	15.30	15.03.04	СВ	1080р.
…	…	…		…	…
		Харьков	6.12	12.10.047	К	775р.

11 вариант.

Таблица "Телефоны"

Код	Ф. И.О.абонента	Адрес	№ телефона	Район	Дата устан.
	Ванин Н.В.	Красная, 32-34	135-14-56	Централ.	26.03.04.
	Петров В.Н.	Калинина, 78-1	135-23-36	Централ.	10.03.04
…	…	…	…	…	…
	Попович Г.А.	Крымская, 63-3	123-45-67	Прикуб.	22.03.04.

12 вариант.

Таблица "Спортсмены"

Код	Ф. И.О.	Вид спорта	Дата рожд.	Разряд	Тренер
	Ртаанен Н.В.	Лёгкая атлетика	06.12. 1990	Мастер	Власов А.А.
	Шровская В.Н.	Теннис	15.03.1998	ЗМС	Родин П.Э.
…	…	…	…	…	…
	Гуревич Г.А.	Таэквондо	12.10.1997	МСМК	Второв В.Д..

13 вариант.

Таблица "Автобусы"

Код	Водитель	Маршрут	Дата	Время	Марка автобуса
	Петров Н.В.	Ростов - Сочи	06.12. 04	6.40	"Икарус"
	Троян В.Н.	Киев - Ростов	07.12.04	10.15	"ЛиАЗ"
…	…	…	…	…	…
	Дуров Г.А.	Сочи - Туапсе	12.10.04	20.35	"Газель"

14 вариант.

Таблица "Аэтопорт"

Борт №	№ рейса	Аэропорт назнач.	Дата	Время	Тип самолёта
	Ю-1138	Шереметьево	06.12. 04	7.30	Як-42
	Ю-1142	Внуково	06.12. 04	15.40	Ил-62
…	…	…	…	…	…
	Ю-1136	Домодедово	12.10.04	20.20	Ту-154

15 вариант.

Таблица "Цех"

Код	Ф. И.О.	Должность	Профессия	Зарплата	Стаж, лет
	Виталин Н.В.	Мастер	Токарь	8200р.
	Прованский В.Н.	Рабочий	Электрик	9650р.
…	…	…	…	…	…
	Пуревич Г.А.	Начальник цеха	Механик	16800р.

16 вариант.

Таблица "Кафедра"

Код	Ф. И.О.	Должность	Звание.	Уч. степень	Дата рождения
	Назаров Н.В.	Декан	Профессор	Д.т.н	25.05.1949
	Ряжевский В.Н.	Зам. декана	Доцент	К.т.н.	20.02.1959
…	…	…	…	…	…
	Кудасова Г.А.	Секретарь	Инженер	-	22.03.1980

17 вариант.

Таблица "Футболисты"

Код	Ф. И.О.	Специализация	Дата рожд.	Разряд	Тренер
	Ватинин Н.В.	Вратарь	06.12. 1990	Мастер	Власов А.А.
	Проворов В.Н.	Нападающий	15.03.1998	ЗМС	Родин П.Э.
…	…	…	…	…	…
	Зуйкович Г.А.	Полузащитник	12.10.1997	МСМК	Власов А.А.

 

18 вариант.

Таблица "Железная дорога"

Код	№ поезда	Маршрут	Время отправления	Дата отправления.	Вагон, место	Стоим. билета
		Москва-Курская	23.40	06.12. 04	П	1080р.
		Москва-Павелец.	15.30	15.03.04	СВ	1080р.
…	…	…		…	…
		Харьков	6.12	12.10.047	К	775р.

19 вариант.

Таблица "Военкомат"

Код	Ф. И.О.	Адрес	№ телефона	Звание	Род войск.
	Ванин Н.В.	Красная, 32-34	135-14-56	Полковник	Авиация
	Петров В.Н.	Калинина, 78-1	135-23-36	Полковник	Артиллерия
…	…	…	…	…	…
	Попович Г.А.	Крымская, 63-3	123-45-67	Майор	Миномётн.

 

20 вариант.

Таблица "Ателье"

Код	Ф. И.О. мастера	Специальность	Дата рожд.	Разряд	№ зала
	Ртищева Н.В.	Макияж	06.12. 1990
	Перовская В.Н.	Парикмахер	15.03.1998
…	…	…	…	…	…
	Гуревич Г.А.	Маникюр	12.10.1997

Содержание отчёта

1 Название работы (в скобках – имя файла описания лаб. работы)

2 Цель работы

3 Содержание работы (порядок выполнения)

4 Заполненный вариант задания, результаты сортировки и фильтрации.

5 Письменные ответы на контрольные вопросы

6 Выводы по работе

 

 

Лабораторная работа №10