Создание и ведение списков с помощью Форм
Лабораторная работа № 7.
Базы данных в Excel . Сортировка и фильтрация данных. Использование форм.
Цели работы:
· познакомиться с использованием электронной таблицы в качестве базы данных;
· научиться осуществлять поиск информации в базе данных по различным критериям;
· научиться производить сортировку информации;
· ознакомиться с использованием форм для заполнения баз данных, поиска и корректировки записей в Excel
Задание 1. Заполните таблицу, содержащую информацию о планетах Солнечной системы согласно Рис. 1.
| Планета | Период | Расстояние | Диаметр | Масса | Спутники |
| Венера | 0,615 | 108 | 12 | 4,86 | 0 |
| Меркурий | 0,241 | 58 | 4,9 | 0,32 | 0 |
| Земля | 1 | 150 | 12,8 | 6 | 1 |
| Плутон | 247,7 | 5900 | 2,8 | 0,1 | 1 |
| Нептун | 164,8 | 4496 | 50,2 | 103,38 | 2 |
| Марс | 1,881 | 288 | 6,8 | 0,61 | 2 |
| Уран | 84,01 | 2869 | 49 | 87,2 | 14 |
| Юпитер | 11,86 | 778 | 142,6 | 1906,98 | 16 |
| Сатурн | 29,46 | 1426 | 120,2 | 570,9 | 17 |
Рис.1.
Единицы измерения, используемые в таблице:
- период обращения по орбите - в земных годах;
- среднее расстояние от Солнца - в млн. км;
- экваториальный диаметр - в тыс. км;
- масса – в 1024 кг.
Основные понятия баз данных
Область таблицы A1:F10 можно рассматривать как базу данных. Столбцы A, B, С, D, Е, F этой таблицы называются полями, а строки с 2 по 10 - записями.
|
|
|
Область A1:F1 содержит имена полей.
Существуют ограничения, накладываемые на структуру базы данных:
· первый ряд базы данных должен содержать неповторяющиеся имена полей;
· остальные ряды базы данных должны содержать записи, которые не являются пустыми рядами;
· информация по полям (столбцам) должна быть однородной, т. е. только цифры или только текст.
Основная работа с любой базой данных заключается в поиске информации по определенным критериям. С увеличением количества записей поиск информации затрудняется. MS Excel позволяет упростить этот процесс путем фильтрации данных.
Фильтрация данных
Команды Данные-> Фильтр позволяют выделять (фильтровать) нужные записи. Фильтрация возможна как через автоматический фильтр – Автофильтр, так и через ручной Расширенный.
Автофильтр
При использовании Автофильтра необходимо переместить курсор в область, содержащую базу данных, или выделить ее. Затем нужно выполнить команды Данные- > Фильтр- > Автофильтр. На именах полей появятся кнопки с изображением стрелок вниз. Нажимая на кнопки, можно задавать критерии фильтрации. В появляющемся подменю пункт Все отключает фильтрацию, а пункт Настройка вызывает диалоговое окно, в котором можно установить параметры фильтрации. Для одного поля могут быть заданы два условия одновременно, связанные логическим И или ИЛИ.
|
|
|
Задание 2. С использованием Автофильтра осуществить поиск планет, начинающихся на букву С или букву Ю с массой менее 600*1024 кг.
2.1. Выполните команды Данные-> Фильтр-> Автофильтр.
2.2. Нажмите на кнопку в поле Планета. Выберите пункт Условие.
2.3. В диалоговом окне задайте критерий отбора: равно с* или равно ю*.
Проверьте! В базе данных осталась информация о планетах Юпитер, Сатурн.
2.4. Нажмите на кнопку на поле Масса. Выберите пункт Условие.
2.5. В диалоговом окне задайте критерий: меньше 600.
Проверьте! Остался только Сатурн.
2.6. Выполните команды меню Данные-> Фильтр-> Отобразить все.
Задание 3. С использованием Автофильтра самостоятельно:
· осуществите поиск планет, имеющих экваториальный диаметр менее 50 тыс. км. и массу менее 50*1024. кг (ответ: Меркурий, Венера, Земля, Марс, Плутон);
· осуществите поиск планет, находящихся от Солнца на расстоянии не менее 100 млн. км, имеющих массу в диапазоне от 3*1024 кг. до 600 *1024. кг, а также не более 2 спутников (ответ: Венера, Земля, Нептун).
|
|
|
Задание 4.
Заполните таблицу:
· С помощью автофильтра выберите в списке такие принтеры, цена которых не превышает 200 у.е.
· Выберите из списка такие компьютеры, цена на которые превышает 2000 у.е.
Расширенный фильтр
При использовании Расширенного фильтра необходимо сначала определить (создать) три области (см. рис. 2):
- интервал списка - область базы данных (А1:F10);
- интервал критериев - область, где задаются критерии фильтрации (A13:F14);
- интервал извлечения - область, в которой будут появляться результаты фильтрации (A17:F19).
| Планета | Период | Расстояние | Диаметр | Масса | Спутники |
| ||
| Венера | 0,615 | 108 | 12 | 4,86 | 0 |
| ||
| Меркурий | 0,241 | 58 | 4,9 | 0,32 | 0 |
| ||
| Земля | 1 | 150 | 12,8 | 6 | 1 |
| ||
| Плутон | 247,7 | 5900 | 2,8 | 0,1 | 1 |
| ||
| Нептун | 164,8 | 4496 | 50,2 | 103,38 | 2 |
| ||
| Марс | 1,881 | 288 | 6,8 | 0,61 | 2 |
| ||
| Уран | 84,01 | 2869 | 49 | 87,2 | 14 |
| ||
| юпитер | 11,86 | 778 | 142,6 | 1906,98 | 16 |
| ||
| Сатурн | 29,46 | 1426 | 120,2 | 570,9 | 17 |
| ||
|
|
|
|
|
|
|
| ||
|
|
|
|
|
|
|
| ||
| Планета | Период | Расстояние | Диаметр | Масса | Спутники | Спутники | ||
|
| >10 |
|
|
| >2 | <17 | ||
|
|
|
|
|
|
|
| ||
|
|
|
|
|
|
|
| ||
| Планета | Период | Расстояние | Диаметр | Масса | Спутники |
| ||
| Уран | 84,01 | 2869 | 49 | 87,2 | 14 |
| ||
| юпитер | 11,86 | 778 | 142,6 | 1906,98 | 16 |
|
Рис. 2
Имена полей во всех интервалах должны точно совпадать.
Для выполнения действий по фильтрации необходимо воспользоваться командами меню Данные-> Фильтр-> Расширенный фильтр. В диалоговом окне надо указать координаты всех запрошенных интервалов.
Если необходимо получать результаты фильтрации в интервале извлечения, нужно выбрать Скопировать результат в другое место.
Задание 5.
С использованием Расширенного фильтра осуществить поиск планет с периодом обращения более 10 земных лет и количеством спутников не менее 2.
4.1. Создайте интервал критериев и интервал извлечения (см. рис.2).
4.2. Запишите критерии поиска в интервал критериев.
4.3. Поместите курсор в область базы данных.
4.4. Выполните команды Данные-> Фильтр-> Расширенный фильтр.
4.5. Установите скопировать результат в другое место. Установите Поместить результат в диапазон.
4.6. Укажите исходный диапазон, диапазон условий и диапазон результата (A17:F21). Нажмите кнопку ОК.
Проверьте! Должны быть найдены планеты Юпитер, Сатурн. Уран, Нептун.
Задание 6. С использованием Расширенного фильтра сделать самостоятельно:
· найдите планеты, имеющие период обращения более 2 лет и экваториальный диаметр менее 50 тыс. км (ответ: Уран, Плутон);
· осуществите поиск планет, находящихся от Солнца на расстоянии более 100 млн. км., а также имеющих более 1 спутника (ответ: Марс, Сатурн, Уран, Нептун, Юпитер).
Задание 7.
С помощью Расширенного фильтра отобразите фамилии сотрудников некоторой фирмы, которые получают свыше 500 000 руб. или чей возраст превышает 50 лет.
| A | B | C | D | E | |
| 1 | Фамилия | Имя | Возраст | Оклад | Стаж |
| 2 | Пашков | Игорь | 50 | 322.000 | 25 |
| 3 | Андреева | Анна | 33 | 573.000 | 8 |
| 4 | Ерохин | Владимир | 48 | 494.000 | 20 |
| 5 | Попов | Алексей | 43 | 420.000 | 15 |
| 6 | Тюньков | Владимир | 58 | 332.900 | 30 |
| 7 | Ноткин | Евгений | 39 | 599.500 | 12 |
| 8 | Кубрина | Марина | 38 | 367.000 | 10 |
Для этой же исходной таблицы отобразите фамилии сотрудников, чей возраст не превышает 45 лет и чей стаж работы свыше 12 лет.
Сортировка данных
Команды Данные-> Сортировка позволяют упорядочивать (сортировать) базу данных. Для выполнения сортировки необходимо выделить область базы данных или поместить в нее курсор, а затем выполнить команды Данные-> Сортировка. В появившемся диалоговом окне выбрать названия полей, по которым нужно производить сортировку, указать метод сортировки: по возрастанию или по убыванию и установить параметр Идентифицировать поля по подписям.
После указанных действий база будет упорядочена. Символьные поля упорядочиваются в алфавитном порядке.
Задание 8.
· Отсортируйте данные в таблице в порядке убывания количества спутников
· Отсортируйте данные в таблице в алфавитном порядке названий планет.
· Отсортируйте данные в порядке увеличения их расстояния от Солнца
Задание 9.
Введите данные о сотрудниках организации в соответствии с нижеприведенной таблицей:
| A | B | C | |
| 1 | Фамилия | Должность | Стаж |
| 2 | Иванов | Зав. отдела | 25 |
| 3 | Петров | математик | 12 |
| 4 | Сидоров | экономист | 8 |
| 5 | Иванов | инженер | 5 |
| 6 | Победоносцев | менеджер | 3 |
| 7 | Приблудин | наладчик | 16 |
| 8 | Иванов | инженер | 8 |
| 9 | Казаков | бухгалтер | 22 |
| 10 | Ржевский | программист | 10 |
| 11 | Петров | математик | 6 |
| 12 | Казаков | менеджер | 4 |
| 13 | Сидоров | экономист | 3 |
| 14 | Победоносцев | бухгалтер | 7 |
| 15 | Иванов | наладчик | 20 |
| 16 | Петров | наладчик | 12 |
Проведите сортировку списка сотрудников по фамилиям, затем по должностям и, в последнюю очередь, – по стажу.
Создание и ведение списков с помощью Форм
Всегда можно вносить новую информацию в список, переходя к первой пустой строке в нижней части списка (т.е. путем ввода данных с клавиатуры). Однако проще делать это с помощью команды Форма меню Данные, которая выводит одну строку списка. Перед ее использованием необходимо выделить любую ячейку в списке. Команда Данные –> Форма открывает окно диалога.
В верхней части формы Excel выводит имя листа, содержащего список, ниже находятся все заголовки столбцов списка. В правом верхнем углу формы выводится информация об общем количестве строк в списке и номере строки, отображаемой в форме в данный момент. В правой части формы находится несколько кнопок, предназначенных для работы со списком.
Чтобы вставить в список новую строку, нужно нажать кнопку Добавить. Excel выведет пустую форму, в которой можно ввести значения для этой строки. Чтобы вернуться в лист, нужно нажать кнопку Закрыть.
Чтобы изменить некоторое значение в списке, нужно использовать для перехода к нужной строке полосу прокрутки в окне формы и внести изменения в соответствующее поле ввода.
Для удаления строки из списка переходят к ней с помощью полосы прокрутки и нажимают кнопку Удалить.
Поиск строк с помощью Формы
Для поиска нужной строки с помощью Формы необходимо:
· Выделить щелчком любую заполненную ячейку списка (таблицы).
· Выполнить команду Данные –> Форма.
· В появившемся окне щелкнуть на кнопке с надписью Критерии.
· Заполнить поля Формы условиями поиска. Чем больше полей вы заполните, тем точнее будет поиск и тем меньше придется просматривать записей.
· Установить указатель мыши на кнопку Найтиследущее и нажимать левую кнопку мыши до тех пор, пока не появится нужная запись.
· После нахождения нужной строки (записи) щелкнуть на команде Закрыть.
Задание 10.
Заполните с помощью формы таблицу:
(Указание: данные в ячейки A1:E1 введите обычным образом, в остальные – с помощью Формы).
В режиме Формы найдите в списке фамилию Бобиков и замените ее на Шариков, затем удалите запись с фамилией Трепало. Введите с помощью формы еще 5–7 записей, продублируйте фамилию Иванов. Произведите поиск всех записей, содержащих эту фамилию.
Дата добавления: 2019-11-16; просмотров: 282; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!