Формирование критерия сравнения Область критериев поиска представляет собой таблицу, которая состоит из строки имён полей и нескольких строк для задания условий по каждому столбцу.
Для создания таблицы рекомендуется сначала скопировать на свободное место листа (или в другой лист, в другую книгу) всю строку с именами столбцов. Далее ненужные столбцы можно будет удалить.
Ниже имён столбцов располагаются строки с условиями(критериями) сравнения типа:
▪ точного значения;
▪ значения, формируемого с помощью операторов отношений (<, >, =, <>, <=, >=) и
▪ шаблона значения с символами *,?.
Условия (критерии), указанные в каждом столбце одной строки связа-
ны логическим оператором И. Условия, записанные в нескольких строках связаны оператором ИЛИ.
Пример 1 Отобрать записи о сдаче экзамена студентами группы КТ-11 на оценки 4 и 5 преподавателю с кодом 1.
Расширенный фильтр может быть составлен тремя способами, на свободном месте листа нужно создать одну из таблиц:
1 вариант. Связка И, критерии по № группы и Код преп заданы как точные значения, критерий по Оценка – с оператором отношения > табл. 1).
Таблица 1
№ группы
| Оценка
| Код преп
| КТ-11
| >3
|
| 2 вариант. Связка И, все критерии заданы как точные значения, поле Оценка используется дважды (табл. 2):
Таблица 2
№ группы
| Оценка
| Оценка
| Код преп
| КТ-11
|
|
|
|
3 вариант. Связка ИЛИ, условия (точные значения) записаны в двух строках (табл. 3).
Таблица 3
№ группы
| Оценка
| Код преп
| КТ-11
|
|
| КТ-11
|
|
|
Формирование вычисляемого критерия
Вычисляемый критерий – это формула в строке для задания условий (в таблице отбора), которая состоит из адресов ячеек, встроенных функций, констант и операторов отношения.
Примечание. Имя столбца с формулой вычисляемого критерия должно отличаться от имени столбца в списке.
Пример 2 Выбрать записи о сдаче экзаменов студентами группы КТ-11 с оценкой ниже среднего балла или записи с оценкой 5.
Здесь также возможны 3 варианта.
1 вариант. Таблица 4
№ группы
| Оценка1
| КТ-11
| =ИЛИ(D3<=СРЗНАЧ($D$3:$D$12); D3=5)
| Столбец Оценка назван отличающимся именем Оценка1, формула введена с помощью Мастера функций, где для логической функции ИЛИ в строке Логическое 1 записано выражение D3<=СРЗНАЧ($D$3:$D$12, а в строке Логическое 2 - выражение D3=5.
2 вариант. Таблица 5
№ группы
| Оценка1
| КТ-11
| =D3<=СРЗНАЧ($D$3:$D$12)
| КТ-11
| = D3=5
|
3 вариант Таблица 6
№ группы
| Оценка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 cписок согласно варианту задания (не менее 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
|