Лабораторная работа № 4
«Средства для работы с базами данных в электронных таблицах»
Цель работы: изучение возможностей создания баз данных в MS Excel
Краткие теоретические сведения
База данных - такой способ организации данных для хранения большого объема информации (например, библиотечный каталог, телефонный справочник, экзаменационная ведомость), что найти нужную информацию можно легко и быстро.
Информационная система имеет в своем составе:
− базу данных (БД);
− систему управления базой данных (СУБД).
В терминологии MS Excel база данных - набор записей, т.е. список. Фрагмент списка экзаменационной ведомости студентов нескольких специальностей и разных форм обучения, сдающих экзамен по информатике, приведен в табл. 1.
Таблица 1
Каждый столбец представляет определенную однородную категорию информации - поле в терминологии БД.
Фамилия, шифр, факультет, специальность, форма обучения, экзаменационная оценка - отдельные типы данных.
Каждая строка, называемая записью, - набор данных об одном студенте или другом единичном элементе. Все записи, включенные в один файл, составляют файл базы данных (в Ехсеl - список). Список Ехсеl содержит 256 полей и 65535 записей.
Ехсеl функционирует как ПЛОСКАЯ база данных, работающая в каждый момент времени только с одним списком. Для создания базы данных обеспечивающей одновременный доступ к данным из нескольких списков (таблиц), следует воспользоваться другим пакетом программ - системой управления базами данных (СУБД), например MS Ассеss.
Имея базу данных (см. табл.1), можно ставить, например, следующие задачи:
сортировка списков по факультетам, по фамилиям студентов, по специальностям, отбор сведений о студентах, получивших неудовлетворительные оценки и т.д.
Упрощают работу со списками диалоговые команды Формы данных, Сортировка, Фильтр, Итоги.
Порядок выполнения работы
Задание 1. Создать базу данных согласно табл. 1.
Задание 2. С помощью команд ДАННЫЕ=>Форма организовать просмотр и удаление записей;
Задание 3. Осуществить сортировку информации в базе данных.
Задание 4. Организовать просмотр данных об оценках студентов.
Задание 5. Осуществить сохранение фамилий студентов в виде пользовательских списков.
Выполнение задания 1
Открыть новую книгу и создать ЭТ табл. 1.
Выполнение задания 2
Работа с меню Данные - Форма.
2.2.1. Удаление записи о студенте Лоцмане Ю.Н.
Выполнить команды:
• Данные - Форма. Появится окно Форма (рис. 1);
• нажимать кнопку Далее до тех пор, пока не появится фамилия Лоцман Ю.Н.;
• нажать кнопку Удалить.
Рис. 1
2.2.2. Введение в список факультета ТВМ студента Лойко И.Н.
Выполнить команды:
• Добавить (все поля очистятся);
• ввести информацию о Лойко Ю.Н., ТВМ, вечерняя форма обучения, спец. 1205, оценка 4 по разделу 1 и щелкнуть по кнопкам Добавить, затем Закрыть.
2.2.3. Поиск оценок Михайлина Б.Н.
Выполняем команды:
• нажать кнопку Критерии;
• в поле Фамилия набрать Михайлин Б.Н.;
• нажать Далее. Появятся записи о студенте Михайлине Б.Н.;
• нажать кнопку Закрыть.
Выполнение задания 3
Работа с меню Данные - Сортировка.
2.3.1. Сортировка записей в БД по одному уровню - по факультетам
а) Поставить курсор в поле списка.
б) Выполнить команды Данные - Сортировка. Появится окно Сортировка диапазона (рис.2)
Рис. 2
в) В появившемся диалоговом окне Сортировка в первом поле Сортировка по выбрать (или ввести) Факультет и выбрать пункт О По возрастанию
г) Нажать кнопку Оk. В результате произведенной сортировки табл. 1 будет преобразована в табл. 2.
Таблица 2
2.3.2. Сортировка записей по двум уровням Выполнить команды:
а) повторить пп. а) и б) из п. 2.3.1;
б) в появившемся диалоговом окне Сортировка в первом поле Сортировать по выбрать Факультет (пункт По возрастанию);
в) во втором поле Затем по выбрать Специальность (пункт По возрастанию);
г) нажать клавишу Оk. В результате табл. 1 будет преобразована в табл. 3.
Таблица 3
2.3.3. Сортировка записей по трем уровням
а) Выполнить пп. а), б), в) из п. 2.3.2;
б) В третьем поле диалогового окна Сортировка В последнюю очередь выбрать Фамилия И.О.,выбрать пункт По возрастанию;
в) нажать кнопку Оk.
В результате табл. 1 будет преобразована в табл. 4.
Примечание. Если внизу окна Сортировка выбрать окно Заголовки столбцов, то можно выбирать не названия столбцов, а их номера А, В, С и т.д.
2.3.4. Сортировка по порядковому номеру
Для восстановления БД по табл. 1 (сортировка по порядковому номеру) выполним команды:
а) поставить курсор в поле списка;
б) Данные - Сортировка, Сортировать по;
в) выбрать имя столбца Номер, пункт По возрастанию;
г) в окне Затем по и В последнюю очередь по выбрать Не сортировать;
д) нажать Оk.
е) Переименовать Лист1,на котором расположена база данных в БД.
Таблица 4
Выполнение задания 4
Организовать просмотр данных об оценках студентов.
2.4.1. Добавить в книгу чистый лист. (Вставка — Лист).
· Назвать его Просмотр.
· Ввести в ячейку А1 заголовок Фамилия И.О. студента.
· Ввести в ячейку В1 заголовок Оценка.
На этом листе организуем просмотр оценок студентов: при вводе фамилии студента в ячейку А2 в ячейке В2 должна высвечиваться его оценка.
Данные в графу «Оценка» будут вводиться с использованием функции ПРОСМОТР.
Функция ПРОСМОТР просматривает диапазон, в который входят значения только одной строки или одного столбца (так называемый вектор) в поисках определенного значения и возвращает значение из другого столбца или строки.
Таким образом, если известна фамилия студента, то функция ПРОСМОТР автоматически поставит в ячейки графы «Оценка» новой таблицы оценку студента с листа «БД».
Синтаксис функции ПРОСМОТР:
ПРОСМОТР (искомое_значение; просматриваемый_вектор; вектор_результатов)
Искомое_значение - это значение, которое ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение.
Просматриваемый_вектор - это интервал, содержащий только одну строку или один столбец. Значения в аргументе просматриваемый_вектор могут быть текстами, числами или логическими значениями.
Вектор_результатов - это интервал, содержащий только одну строку или один столбец. Он должен быть того же размера, что и просматриваемый_вектор.
Важно! Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания: в противном случае функция ПРОСМОТР может вернуть неверный результат. Тексты в нижнем и верхнем регистре считаются эквивалентными.
Для использования данной функции необходимо:
• Открыть лист БД;
• Поставить курсор в область поля Фамилия И.О. нажать кнопку Сортировка по возрастанию на стандартной панели инструментов (или произвести сортировку аналогично п. 2.3);
• Выделить диапазон ячеек В1:В16, нажать кнопку Ctrl и удерживая ее, выделить диапазон ячеек G1:G16;
• Выполнить команды Вставка - Имя - Создать;
• В открывшемся диалоговом окне выбрать × В строке выше (этим блокам ячеек будут присвоены соответственно имена Фамилия_И.О и Оценка;
• Открыть Просмотр;
• Выполнить команды Данные — Проверка...;
• Вкладка Параметры - Тип данных выбрать Список;
• Щелкнуть мышью в открывшемся окне Источник: нажать кнопку FЗ на клавиатуре, выбрать Фамилия_И.О., нажать Оk;
• Нажать кнопку Оk (рядом с ячейкой А2 появится значок списка, при нажатии на который появится список всех студентов);
• Выберите из открывшегося списка фамилию студента Ложкин Н.В.
• Установить курсор в ячейку В2.
• В строке формул нажать кнопку Вставка функции (или выбрать пункт меню Вставка|Функция...)
• В открывшемся окне в области Категория выбрать Ссылки и массивы, в области Выберите функцию - ПРОСМОТР и нажать Оk.
• В следующем окне выбрать искомое_значение;
просматриваемый вектор;вектор_результов и нажать Оk.
• Передвинуть открывшееся окно так, чтобы просматривались данные таблицы.
• Установив курсор в окне Искомое_значение, щелкнуть левой кнопкой мыши в ячейке А2 текущей таблицы.
• Установить курсор в окно Просматриваемый_вектор и нажать функциональную клавишу FЗ на клавиатуре.
• В открывшемся окне выбрать имя блока Фамилия_И.О. и нажать Оk.
• Установить курсор в окно Вектор_результатов и нажать функциональную клавишу FЗ.
• В открывшемся окне выбрать имя блока Оценка и нажать ОК.
• Нажать ОК в главном окне функции ПРОСМОТР.
В результате выполненных действий в ячейке В2 появилось оценка студента Ложкина Н.В., а в строке формул отражена формула:
=ПРОСМОТР(А2;Фамилия_И.О.;Оценка).
Выполнение задания 5
Осуществить сохранение фамилий студентов из БД в виде пользовательского списка.
Если в работе с таблицами приходится повторять ввод конкретной последовательности в рабочем листе, например списка имен, то можно создать пользовательский список. После создания такого списка его можно ввести в диапазон ячеек, простым вводом в ячейку любого элемента из этого списка с последующим перетаскиванием маркера заполнения в нужные ячейки.
Пользовательский список можно создать при помощи команды меню Сервис|Параметры|Списки, при выполнении которой открывается диалоговое окно Параметры с вкладкой Списки, путем ввода элементов нового списка в соответствующее окно Элементы списка. Также можно использовать кнопку Импорт, расположенную в правой нижней части вкладки Списки диалогового окна Параметры.
В данном примере список будет создан с использованием кнопки Импорт, так как исходный список существует на листе «БД» в блоке ячеек А2:А16 (Фамилия И.О.).
Для этого необходимо:
2.5.1. Добавить новый лист и назвать его Список. В ячейку А1 ввести заголовок Список сдавших экзамен по информатике.
2.5.2. Перейти на лист БД.
2.5.3. Активизировать меню Сервис
2.5.4. Выбрать команду Параметры.
2.5.5. Щелкнуть по вкладке Списки.
2.5.6.Установить курсор мыши в окно Импорт списка из ячеек: в нижней части вкладки Списки.
2.5.7. Выделить в таблице блок ячеек А2:А16.
2.5.8. Нажать кнопку Импорт, а затем кнопку Добавить на вкладке Списки.
2.5.9. Нажать ОК.
2.5.10. Перейти на лист «Список» и установить курсор в ячейку А2.
2.5.11. Набрать с клавиатуры фамилию Васильев Л.В. (первый элемент списка) и нажать Епtег.
2.5.12. Установить курсор на маркер заполнения (указатель мыши поместить в правый нижний угол ячейки А2, чтобы он принял вид тонкого крестика) и перетащить его вниз до ячейки А16, не отпуская левой кнопки мыши.
2.5.13. Ячейки А2:А16 заполнились элементами пользовательского списка. Теперь этот список можно использовать многократно.
3. Контрольные вопросы
1) Какие компоненты входят в информационную систему?
2) Что называют базой данных?
3) Что называют системой управления базами данных?
4) Опишите состав системы управления базами данных.
5) Какие способы логической организации данных вы знаете?
6) Какая логическая модель данных используется в MS Excel?
7) Что называют полем и записью в базе данных MS Excel?
8) Почему базу данных в MS Excel называют плоской?
9) Какие диалоговые команды MS Excel упрощают работу со списками?
ЗАДАНИЕ