Формирование вычисляемого критерия
Вычисляемый критерий – это формула в строке для задания условий (в таблице отбора), которая состоит из адресов ячеек, встроенных функций, констант и операторов отношения.
Примечание.Имя столбца с формулой вычисляемого критерия должно отличаться от имени столбца в списке.
Пример 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 Экранная форма для работы со списком данных
Возврат к форме производится кнопкой Правка, а выход из формы – кнопкой Закрыть.
Если команды Добавить и Удалить вносят изменения в список на листе книги MSExcel, то отфильтрованные данные можно просмотреть только в окне формы.
Таким образом, MSExcel позволяет:
· создать однозаписевую форму списка (таблицы, БД). Перемещение между записями выполняется кнопками Назад, Далее.
· Изменение данных в форме – редактирование, добавление, удаление и т.п. - приводит к их изменению и в списке, по которому форма создавалась.
4 Контрольные вопросы
1 Что называется списком (базой данных) в MSExcel?
2 Как называются элементы таблицы в терминах БД?
3 Сортировка данных, её порядок при сортировке нескольких полей.
4 Фильтрация с помощью фильтра, критерии фильтрации.
5 Формирование критерия сравнения в расширенном фильтре.
6 Формирование вычисляемого критерия в расширенном фильтре.
7 Фильтрация данных расширенным фильтром.
8 Фильтрация данных с помощью формы.
9 Как добавить или изменить данные в списке с помощью формы?
Задание
1 Создать в MSExcelсписок согласно варианту задания (не менее 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