Лабораторная работа № 5 «Базы данных в ms Excel»
Цель работы:Научиться создавать базы данных вExcel. Изучить возможности работы с базами данных.
Задание:
Оформление базы данных.
Создание формы.
Сортировка элементов базы данных.
Фильтрация данных.
Суммирование чисел в базе данных.
Пример выполнения задания:
Создать базу данных сотрудников предприятия, заголовок которой имеет вид:
|
№ |
Фамилия |
Имя |
Отчество |
Должность |
Код |
|
1 |
Иванов |
Иван |
Иванович |
Менеджер |
3750 |
Заполнить базу данных на 15 сотрудников.
Оформление базы данных производится так же, как и оформление любой таблицы в Excel. Только следует помнить, что таблица для базы данных не должна иметь пустых строк и пустых столбцов, а также не допускается объединение ячеек для данной таблицы.
В нашем случае заголовок имеет вид:
Теперь в ячейку A4 запишем:
=СТРОКА(А4)-3
и растянем эту формулу до ячейки А18.
Далее заполняем ячейки B4:F18 данными, получим:
Рассмотрим работу с формой базы данных, для этого добавим при помощи формы еще двух сотрудников. Выполним: \Данные\Форма...
получим диалоговое окно вида:
Теперь если мы хотим добавить запись в базу данных, то нажмем кнопку «Добавить» и получим:
Заметим, что поле ввода номера не доступно, так как там заложена формула. Мы заполняем только доступные нам поля и получаем:
Заметим, что для перехода из одного поля ввода в другое необходимо нажимать клавишу TAB.
Теперь для того, чтобы добавить еще одного сотрудника, снова нажмите кнопку «Добавить» и введите данные этого сотрудника.
Для завершения добавлений в базу данных необходимо нажать клавишу «Закрыть». Произойдет выход из режима «Форма», а также добавление сотрудников в базу данных.
Обращаем ваше внимание на то, что сотрудники добавляются не по алфавиту, а в конце списка.
В результате наша база данных приобретет вид:
Теперь рассмотрим сортировку базы данных.
Сортировать базу данных в Excel можно по столбцу любого типа в порядке возрастания или убывания. Допускается задание от одного до трех критериев сортировки.
Установите курсор в любую ячейку базы данных. В меню «Данные» выберите пункт «Сортировка...»:
Получим диалоговое окно вида:
Теперь заполним поле «Сортировать по». Для начала отсортируем нашу базу данных по «Фамилиям» в порядке возрастания, получим:
Обратите внимание, что заданная нами формула для «№» позволяет сохранять правильную нумерацию независимо от сортировки.
Теперь отсортируем нашу базу данных по двум столбцам. Сначала отсортируем по «Должности», а потом по «Коду». Для этого в диалоговом окне сортировки наберем:
В результате получим:
В результате такой сортировки на первые позиции встали все «Координаторы», причем между собой они отсортированы по «Коду». В конце у нас «Менеджеры», которые тоже отсортированы между собой по «Коду».
Фильтрация базы данных предусмотрена для того, чтобы быстро извлекать из документа записи, которые соответствуют указанным критериям, а затем переносить эту информацию в другие части листа или применять в отчетах.
Для извлечения информации из базы данных по заданному критерию установите курсор в любую ячейку базы данных и выполните:
\Данные\Фильтр\Автофильтр
В результате вы получите:
Обратите внимание, что в строке заголовка появились кнопки со стрелками.
Теперь с помощью фильтра оставим всех «Менеджеров» с «Кодом» из диапазона от 3755 до 3760. Для этого нажмем кнопку со стрелочкой в столбце «Должность» и выберем «Менеджер»:
Далее нажмем кнопку со стрелочкой в столбце «Код» и выберем «Условие», в результате получим диалоговое окно вида:
Заполнив это окно так, как показано на рисунке, получим:
Обратите внимание на то, что вы не удаляли ничего, а просто выбрали лишь те элементы, которые вам необходимы. Стрелочки в столбцах, по которым задавались критерии для фильтрации, выделены цветом, так что если вам необходимо отменить данную фильтрацию, достаточно у выделенных стрелочек выбрать раздел «Все».
Если вы хотите снять фильтр, то выберите:
\Данные\Фильтр\Автофильтр
Примечание. Для того чтобы не только извлечь, но и переместить данные, выполните команду: \Данные\ Фильтр\Расширенный фильтр... В открывшемся диалоговом окне включите опцию «Скопировать результат в другое место» и укажите, куда копировать результат.
Вы можете подводить промежуточный и окончательный итоги, анализируя любой список базы данных, содержащий числовую информацию. Для получения промежуточных итогов весь список должен быть разбит на отдельные группы записей. Чтобы программа Excelраспознала эти группы, список следует отсортировать.
Для того чтобы изучить эту возможность, добавим в нашу базу данных еще один столбец «Зарплата» и отсортируем ее по «Фамилиям», получим:
Установим курсор в любую ячейку базы данных и выполним:
\Данные\Итоги...
получим диалоговое окно вида:
Укажем подводить промежуточные итоги при каждом изменении «Должности», операция «Сумма», а итоги подводить только по «Зарплате» и нажмем кнопку «ОК», получим:
Теперь отменим итоги, для чего выполним \Данные\Итоги...и в появившемся диалоговом окне нажмем кнопку «Убрать все». Отсортируем базу данных по «Должности» и вновь установим итоги по тому же принципу, что и ранее, получим:
Для того чтобы вывести только промежуточные
итоги, необходимо щелкнуть по кнопке
со знаком «-» (
),
которая расположена в левой части окна.
В результате получим:
Оставшийся минус в левой части окна позволяет вам увидеть только итоговый результат.
ЛАБОРАТОРНАЯ РАБОТА № 6
«Разработка информационной модели базы данных. Создание объектов базы данных»
|
Цели:oсоздавать объекты базы данных, oопределять их логическую структуру, oсоздавать структуру таблицы. Выполнение работы. Задание 1.Создание базы данных, содержащей сведения о студентах. Определим логическую структуру создаваемой базы данных. Поскольку почти все поля базы данных являются уникальными, создаем одну сводную таблицу, состоящую из записей, в которые входят поля Фамилия, Имя, Группа, Дата рождения и Домашний адрес. 1. Загрузите MicrosoftAccess. 2.В появившемся окнеMicrosoft AccessвыберитеНовая база данных. Нажмите кнопку<ОК>. 3.В появившемся окне выберитеСоздать, на панели задач выберитеНовая база данных… 4.Откроется окно создания (или сохранения) файла. Выберите папку, в которую вы сохраните базу данных, и ее имя (имя файла)Student. Сохраните файл. Создание структуры новой таблицы 5.В появившемся окнеStudent: База данныхвыберите объектТаблицыи нажмите кнопкуСоздание таблицы в режиме конструктора. 6.В первой строке в столбцеИмя полявведите:Фамилия. Нажмите клавишу вправо →. 7.Щелкните на появившейся стрелке раскрывающегося списка ячейкиТип данныхи выберите пунктТекстовый. 8.В нижней части экрана, вСвойствах поля, на вкладкеОбщие,в строкеРазмер поляустановите 20. 9.В строкеОбязательное полепри помощи кнопки раскрывающегося меню укажитеДа. 10.Установите курсор в первой строке в столбцеОписание. Введите:Фамилия студента. 11.Во второй строке в столбцеИмя полявведите:Имя.Установите тип данных – текстовый. 12.В полеОписание введите:Имя студента.УстановитеРазмер поля– 10. 13.В третьей строке в столбцеИмя полявведите:Группа. Установитетип данных–числовой. 14.В раскрывающемся спискеРазмер полявыберитеЦелое. 15.В полеОписаниевведите:Номер группы. 16.В четвертой строке в столбцеИмя полявведите:Дата рождения. 17.Установите тип данныхДата/время. Установить курсор в наборном полеФормат поля. 18.В раскрывающемся спискеФормат поляустановите –Краткий формат даты. 19.Установите курсор в полеМаска ввода, находящееся в нижней части экрана, и щелкните мышью на кнопке с тремя точками. На запрос подтвердите сохранение таблицы под именемТаблица 1и создание ключевого поля. 20.В появившемся диалоговом окнеСоздание масок ввода выберитеКраткий формат даты. Нажмите кнопкуДалее. 21.В следующем окне можно выбрать знак заполнителя для отображения в поле. Нажмите кнопкуГотово. 22.Обратите внимание, что в первой строкеMicrosoft Accessавтоматически добавил дополнительноеключевое поле Код, которое играет роль уникального идентификатора записей, и установил для негоТип данных Счетчик. 23.В шестой строке в столбцеИмя полявведите:Домашний адрес. 24.Установите тип данныхПоле MEMO. В полеОписаниевведите:Домашний адрес. 25.Закройте текущее окноТаблица 1: таблицас сохранением изменений.
Заполнение таблицы 26.В окнеStudent:База данныхвыберите объектТаблицы, установите курсор на названииТаблица 1и нажмите кнопкуОткрыть. 27.В полеФамилиявведите свою фамилию, в полеИмявведите свое имя и в полеГруппавведите номер своей группы. 28.Переместите курсор в полеДата рожденияи введите дату своего рождения в формате ДД.ММ.ГГГГ, например 12.05.85.Примечание: вводить следует только числа, а остальноеMicrosoft Accessподставит автоматически по заданной маске. 29.Переместите курсор в полеДомашний адреси введите свой домашний адрес. 30.Подобным образом введите еще 7 записей. В полеГруппав любых двух строках введите № группы – 1 группу, в остальных – 2. Если потребуется изменить ширину столбца, то это можно сделать при помощи мыши аналогично работе вExcel. 31.Обратите внимание, что в полеКодцифры изменяются автоматически по мере ввода новых строк, каждый раз увеличиваясь на 1. 32.Нажмите на значок закрытия текущего окнаТаблица 1: таблица. 33.В окнеStudent: База данных установите курсор мыши на словоТаблица 1и правой кнопкой мыши вызовите контекстное меню. 34.Выберите командуПредварительный просмотр. Если потребуется, измените масштаб для более удобного просмотра созданной таблицы с помощью пиктограммы с лупой или ниспадающего списка масштабов. Закройте окно просмотра. 35.В окнеStudent:База данныхснова установите курсор на словеТаблица 1и вызовите контекстное меню. Выберите команду Переименовать. Введите новое имя таблицыТ_Студенты. Создание запросов Как создать запросы базы данных Студенты? Задание 2.Из общего списка студентов необходимо выбрать студентов в определенной группе. Создание запросов. 1.Откройте базу данныхStudent. В окне базы данныхStudent:Базаданныхвыберите объектЗапросы. Нажмите кнопкуСоздание запросав режиме конструктора. 2.В окнеДобавлениетаблицывыберите объектТаблицыТ_Студенты. Нажмите кнопкиДобавитьиЗакрыть. 3.Перед Вами окажется окноКонструкторазапросовс заголовкомЗапрос1:запроснавыборку. В его верхней части отображаются списки полей таблицы, к которым обращается запрос. Нижняя область содержит бланк выбора полей таблиц, условий отбора и режимов сортировки. Указывается также название таблицы, которой принадлежит выбранное поле. 4.Поочередно щелкните два разалевойкнопкоймышина следующих полях:Код,Фамилия,Имя,Группа,Датарождения,Домашний адрес. 5.Установите курсор в нижней части окна в столбцеФамилияв полеСортировка. В раскрывающемся списке этого поля установите По возрастанию. 6.Нажмите на значок закрытия текущего окна «Запрос 1: запрос на выборку». Подтвердите сохранение структуры запроса. 7.В наборном полеИмя запросав окнеСохранениевведите имя запросаСписоквсехстудентов. Нажмите кнопку<ОК>. 8.Откройте и просмотрите запрос «Список всех студентов». Обратите внимание, что в записях фамилии расположены в алфавитном порядке. Закройте запрос. 9.Установите курсор на запрос «Список всех студентов». 10.Нажмите клавишу <Ctrt>, и ухватившись за значок запроса (зажав левую клавишу мыши), переместите курсор мыши в сторону. Отпустите кнопку. Возникнет новый ярлыкКопия Список всех студентов. 11.Установите курсор на новый ярлык и переименуйте его вСписок студентов 1 группы. 12.Откройте запросСписок студентов 1 группы. 13.В менюВидвыберите режим работыКонструктор. 14.В столбцеГруппав строкеУсловие отборавведите 1. 15.Нажмите на значок закрытия текущего окна, подтвердите сохранение макетаСписок студентов 1 группы: запрос на выборку. 16.Откройте запросСписок студентов 1 группы. Просмотрите содержимое запроса. Закройте текущее окно. 17.В исходной таблице в одной из записей изменитеномер группынаномер 1. Закройте таблицу. 18.Вновь откройте запросСписок студентов 1 группы. Убедитесь, что содержание запроса изменилось.
Как создать формы базы данных Студенты? Задание 3. Разработать специальную форму для ввода данных в таблицу. Создание формы при помощи Мастера форм 1 этап. 1.В окнеStudent:База данныхвыберите объектФормы, нажмите кнопкуСоздание формы с помощью мастера.
1.Мастер форм позволяет сберечь время и быстро сконструировать привлекательную форму для записей любой таблицы. 2.В первом окнеМастера форм, показанного на рис.1, в спискеТаблицы и запросывыберите таблицуТ_Студенты. 3.Щелкните на кнопке >>, чтобы добавить в списокВыбранные поля: все поля таблицы. 4.Выделите пунктКоди щелчком на кнопке < уберите это поле обратно в левый список. Содержимое этого поля генерируется автоматически, а его значение несущественно для пользователя, поэтому не следует включать его в форму. 5.Щелкните на кнопкеДалее. 6.В следующем окне диалога выберите для формы стильВ один столбеци щелкните на кнопкеДалее. 7.В списке третьего окна выберите понравившийся Вам стиль оформления и снова щелкните на кнопкеДалее. 8.В последнем окнеМастеращелкните на кнопкеГотово, не изменяя никаких параметров.Мастерсгенерирует форму и откроет ее в режиме просмотра данных. 9.Окно формы содержит названия полей и области отображения данных исходной таблицы. В нижней части формы расположены кнопки перемещения по записям. 10.Щелкните несколько раз на кнопкеСледующая запись, чтобы добраться до пустой строки, и введите запись еще об одном человеке. Создание формы при помощи Конструктора Недостатком форм, создаваемых Мастером,является то, что они однообразны и не содержат пояснительных надписей, а также не имеют элементов управления. Чтобы приукрасить форму, расположить поля более удобным способом, следует воспользоваться Конструктором форм, который позволяет создавать новые формы и редактировать имеющиеся. 11.Выберите вкладкуФормы.Установите режимКонструктор форм.В окнеКонструктора формпоявится разметочная сетка, вертикальная и горизонтальная линейки, позволяющие позиционировать объекты. Изменение позиции объекта происходит при помощи методов, обычных дляWINDOWS. 12.Одним щелчком выделите подписьФамилия, установите курсор внутри объекта и измените подпись наФамилия студента.
1.Щелкните на объектеФамилия студента правой кнопкой мыши, в контекстном меню выберите командуСвойства, откроется окно свойствПоле: Фамилия (рис.2). Во вкладкеМакетустановите цвет фона: кликните по кнопке справа …, когда откроется окно с палитрой цветов выберите голубой, размер шрифта – 12, оформление приподнятое и другие свойства по своему желанию. Если, текст не будет помещаться в рамку, при помощи маркеров измените границы объекта. 2.Измените внешний вид других объектов формы. Создание элементов управления
При открытии конструктора на экране
появляется Панель элементов. Если ее
нет щелкните на кнопке Панель
элементов 3.Поместите указатель мыши на угол области формы. 4.Перетащите этот угол вправо вниз, чтобы увеличить форму. 5.С помощью командыПравка – Выделитьвсевыделите все элементы формы. 6.Нажмите клавишуCtrlи, не отпуская ее, нажатием клавиш со стрелками переместите элементы формы вниз и вправо, так чтобы они отцентрированы относительно новых границ формы.
7.Щелкните на кнопкеНадпись 8.Растяните рамку надписи в верхней части формы на ширину области данных. 9.Введите надпись, которая будет являться заголовком формы:Список студентов. 10.Находясь в области заголовка, вызовите контекстное меню и выберите командуСвойства. 11.Во вкладкеМакет установите следующие параметры: ширина границы – 3 пункта, цвет фона – розовый, размер шрифта 14, курсив – Да, от левого края – 3 см.
12.Щелкните на кнопкеРисунок 13.Внизу формы растяните рамку рисунка. 14.В открывшемся окне выбора файла найдите папку с рисункамиWindows, выберите любой понравившийся Вам рисунок и щелкните на кнопке<ОК>.По умолчанию рисунки вставляются в форме урезания рисунка рамкой элемента. 15.Чтобы изменить режим размещения, щелкните на рисунке правой кнопкой мыши и в контекстном меню выберите команду Свойства. 16.В спискеУстановка размеровоткрывшегося окна параметров выберитепункт Вписать в рамку. 17.Закройте окно параметров.
18.Щелкните на кнопкеКнопка 19.Перенесите указатель мыши в область формы и щелкните левой кнопкой мыши внизу формы. 20.В открывшемся окнеСоздание кнопок(рис.3) выберите категорию действияПереходы по записям, в качествеДействия выберите –Следующая запись. Нажмите кнопкуДалее.
1.Во втором окнеМастера создания кнопоквыберите рисунок на кнопку, напримерСтрелка вправо (синяя). Нажмите кнопкуДалее. 2.В третьем окнеМастера создания кнопоквыберите название кнопки –Следующая запись. Нажмите кнопкуГотово. 3.Используя пункты 86–90, создайте кнопкиПредыдущая запись,Найти запись, Выход из формы. 4.Установите режим работы с формой (команда менюВид – Режим формы). 5.Проверьте действие кнопок. |
2
этап
панели
инструментов. С помощью кнопокПанели
элементовв форму можно добавлять
различные объекты. Элементы управления
форм и отчетов сходны между собой,
поэтому такая же панель имеется в
Конструкторе отчетов.
Панели
элементов.
Панели
элементов.
Панели
инструментов
3
этап