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

⇐ ПредыдущаяСтр 13 из 17Следующая ⇒

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1 вариант

№ группы	Оценка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

3 вариант

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

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

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

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

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

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

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

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

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

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

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

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

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

Таким образом, MS Excel позволяет:

· создать однозаписевую форму списка (таблицы, БД). Перемещение между записями выполняется кнопками Назад, Далее.

· Изменение данных в форме – редактирование, добавление, удаление и т.п. - приводит к их изменению и в списке, по которому форма создавалась.

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

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

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

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

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

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

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

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

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

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

Задание

1 Создать в MS 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

⇐ Предыдущая 8 9 10 11 121314 15 16 17 Следующая ⇒