Создание и обработка базы данных в среде Excel и Access

Размещено на http://www.allbest.ru

Размещено на http://www.allbest.ru

ПОЯСНИТЕЛЬНАЯ ЗАПИСКА К КУРСОВОЙ РАБОТЕ

Создание и обработка БД в среде Excel и Access



Введение

Базы данных - это файл специального формата, содержащий информацию структурированную заданным образом => «Сетевые», «Иерархические», «Реалиционные». В Excelобрабатываются только однотабличные базы данных. Эти базы в Excel называются таблицами. Таблицы Excelпохожи на базы данных, но предназначены не столько для хранения информации, сколько для проведения математических и статистических расчётов. Формулы и функции Excel способны обрабатывать величины как конкретных ячеек таблицы, так и целых блоков (например, строк или столбцов).

Формулы Excel могут быть очень сложными, а результат их вычислений, в свою очередь, можно использовать в других расчётах. Неоспоримых преимуществом электронной таблицы является то, что при изменении данных листа результаты вычислений моментально обновляются. Все вычисления в Excelвыполняются с помощью формул, которые можно вводить в любые ячейки листа. Если содержимое ячейки начинается со знака равенства(=). Сама формула отображается в ячейке только в том случае, если ячейка находится в режиме редактирования текста (то есть после двойного щелчка по ней). Если ячейка просто выделена, то её формула выводится в строке формул в верхней части окна Excel.



1. Задание к курсовой работе

ВАРИАНТ № 7

Учебные заведения Петербурга

База данных должна содержать следующие элементы:

название учебного заведения;

количество учащихся;

штатное количество преподавателей;

фонд заработной платы;

материальные затраты;

название министерства, которому подчинено учебное заведение;

статус учебного заведения (высшее, среднее, среднее специальное);

затраты на обучение одного учащегося (вычислить).

Задачи:

Вывод сведений обо всех вузах, где количество учащихся находится в выбранном диапазоне.

Подсчет минимальных затрат на обучение одного учащегося в учебном заведении выбранного статуса.

Вывод сведений обо всех учебных заведениях, сгруппированных по статусу учебного заведения, с подсчетом средней заработной платы преподавателей.

Формирование отчета об учебных заведениях, где количество студентов ниже среднего.



2.Список файлов курсовой работы с указанием пути доступа

Курсовая работа содержит два файла, хранящиеся на DVD - диске. Доступ к данным файлам производится по адресу:

Диск E/Курсовая по Excel.xlsxи Диск E/Курсовая по Access.accdb.

Курсовая работа выполнена в среде MicrosoftExcel 2010 и MicrosoftAccess 2010.

Файл «Курсовая по Excel.xlsxm». Лист MicrosoftExcel содержит исходную базу данных, решение 2 задач, сводную таблицу, диаграмму.

Файл «Курсовая по Access.accdb». Приложение MicrosoftAccess содержит таблицу базы данных, запросы, необходимые для решения задач, отчёт по четвертой задаче и форму базы данных.



3. Создание и обработка базы данных в среде Excel

3.1 Краткое описание пакета

Excel обеспечивает:

- Ввод, хранение и корректировку данных;

- Автоматическое проведение вычислений по заданным формулам;

- Наглядность и естественную форму документов, представляемых пользователю на экране;

- Построение различного рода диаграмм и графиков на основе табличных данных;

- Оформление и печать электронных таблиц;

- Создание итоговых и сводных таблиц;

- Работу с электронными таблицами как с базами данных;

- Сортировку таблиц, выборку данных по запросам.

3.2 Структура базы данных

Любая база данных имеет определённую структуру, определяющая:

- количество полей в базе данных;

- Тип хранящейся в ней информации;

- Размер данных.

Название поля	Тип данных	примечание
Название учебного заведения	Текстовый
Количество учащихся	Числовой
Количество преподавателей	Числовой
Фонд заработной платы	Денежный
Материальные затраты	Денежный
Министерство подчинения	Текстовый
Затраты на обучение одного учащегося	Денежный	Вычислялось по формуле: «Затраты» = «Материальные затраты» / «Количество учащихся»
Заработная плата преподавателей	Денежный	Вычислялось по формуле: «Зарплата»= «Фонд заработной платы» / «Количество преподавателей»

Формирование базы данных в Excel проводилось ручным вводом данных с клавиатуры.

3.3 База данных «Учебные заведения Санкт - Петербурга»

Название учебного заведения	Количество учащихся	Количество преподавателей	Фонд заработной платы	Материальные затраты	Министерство подчинения	Статус учебного заведения	Зат. на об.одн.уч	Зарп.преп.
С - П Гос. Тех. Институт	5447	456	4 375 000,00р.	3 869,00р.	Мин.обр.РФ	Высшее	803,19р.	9 594,30р.
С-П Гос.Тех.училище.Раст.Полимер.	6186	533	2 665 000,00р.	1 896,00р.	Мин.обр.РФ	Сред.спец.	430,81р.	5 000,00р.
С - П Гос.Универ.	5264	675	4 245 000,00р.	3 765,00р.	Мин.обр.РФ	Высшее	806,42р.	6 288,89р.
С - П Гос.Универ.аэрокос.приборост.	4748	349	4 890 000,00р.	3 926,00р.	Мин.обр.РФ	Высшее	1 029,91р.	14 011,46р.
С - П Гос.Универ. Водных комун.	6567	778	4 825 000,00р.	3 824,00р.	Мин.тран.РФ	Высшее	734,73р.	6 201,80р.
С - П Гос.Универ.ИТМО.	5359	793	4 965 000,00р.	3 874,00р.	Мин.кул.РФ	Высшее	926,48р.	6 261,03р.
С - П Гос.Универ.кино и телевид.	4329	798	3 990 000,00р.	3 698,00р.	Мин.кул.РФ	Высшее	921,69р.	5 000,00р.
С - ПГос.лицей. Культ.и Искусств.	5264	746	2 605 000,00р.	1 965,00р.	Мин.обр.РФ	Среднее	494,87р.	3 491,96р.
С-П Гос.училище.низТемпер.Пищ.Тех.	6137	564	2 835 000,00р.	1 785,00р.	Мин.обр.РФ	Сред.спец.	461,95р.	5 026,60р.
С - П Гос.Универ.телекомуникаций	4197	748	4 730 000,00р.	3 978,00р.	Мин.связ.РФ	Высшее	1 127,00р.	6 323,53р.
С - ПГос.гимназия.техн.и дизайна	6545	697	2 369 000,00р.	1 923,00р.	Мин.обр.РФ	Среднее	361,96р.	3 398,85р.
С - ПГос.училище.эконом. и финан.	4897	734	3 012 000,00р.	3 527,00р.	Мин.обр.РФ	Сред.спец.	615,07р.	4 103,54р.
С - П Гос.Универ. Элект.- тех.ЛЭТИ	5433	895	4 475 000,00р.	3 976,00р.	Мин.обр.РФ	Высшее	823,67р.	5 000,00р.
С - П Гос.Гуман. Универ.профсоюз.	5395	647	4 896 000,00р.	3 899,00р.	Негос.АкВ.	Высшее	907,51р.	7 567,23р.
С - П Гос.Инст.Внешнеэкон.Связи	5757	521	4 923 000,00р.	4 021,00р.	Негос.АкВ.	Высшее	855,13р.	9 449,14р.
С - П Гос.Инст. Машин.Строения	4425	533	4 512 000,00р.	4 001,00р.	Мин.обр.РФ	Высшее	1 019,66р.	8 465,29р.

3.4 Решение задач

3.4.1 Задача 1

Условие: Вывод сведений обо всех вузах, где количество учащихся находится в выбранном диапазоне

Решение: Для решения задачи использовался автофильтр числовые фильтры между.

В данном примере вводим диапазон от 4500 до 6000.

3.4.2 Задача 2

Условие: Подсчёт минимальных затрат на обучение одного учащегося в учебном заведении выбранного статуса

Решение: Для решения задачи использовалась функция «ДМИН», из категории «Работа с базой данных». Критерием являлось поле «Статус учебного заведения».

Для удобства просмотра оставляем только столбцы «Статус» и «Материальные затраты».

Получаем:

Статус учебного заведения	Минимальные затраты
Среднее	1 923,00р.

3.4.3 Задача 3

Условие: Вывод сведений обо всех учебных заведениях, сгруппированных по статусу учебного заведения, с подсчётом средней заработной платы преподавателей

Решение: Для решения задачи была использована сводная таблица.

Сводную таблицу составляем из полей: «Статус», «Название учебного заведения», «Заработная плата». В поле «Значения» заходим в «параметры полей значений» и выбираем операцию “среднее”. база данные уч

ебный

В результате выполнения задачи получаем:

Средняя заработная плата преподавателей
Статус учебного заведения	Название учебного заведения	Итог
Высшее	С - П Гос. Тех. Институт	9 594,30р.
	С - П Гос.Гуман. Универ.профсоюз.	7 567,23р.
	С - П Гос.Инст. Машин.Строения	8 465,29р.
	С - П Гос.Инст.Внешнеэкон.Связи	9 449,14р.
	С - П Гос.Универ.	6 288,89р.
	С - П Гос.Универ. Водных комун.	6 201,80р.
	С - П Гос.Универ. Элект.- тех.ЛЭТИ	5 000,00р.
	С - П Гос.Универ.аэрокос.приборост.	14 011,46р.
	С - П Гос.Универ.ИТМО.	6 261,03р.
	С - П Гос.Универ.кино и телевид.	5 000,00р.
	С - П Гос.Универ.телекомуникаций	6 323,53р.
Высшее Итог		7 651,15р.
Сред.спец.	С - ПГос.училище.эконом. и финан.	4 103,54р.
	С-П Гос.Тех.училище.Раст.Полимер.	5 000,00р.
	С-П Гос.училище.низТемпер.Пищ.Тех.	5 026,60р.
Сред.спец. Итог		4 710,05р.
Среднее	С - ПГос.гимназия.техн.и дизайна	3 398,85р.
	С - ПГос.лицей. Культ.и Искусств.	3 491,96р.
Среднее Итог		3 445,40р.
Общий итог		6 573,98р.

3.5 Диаграмма

Для создания диаграммы был использован «Мастер диаграмм». Диаграмму получаем из сводной таблицы:

Перемещаем её на отдельный лист.



В результате выполнения диаграммы получаем:

3.6Работа с макросами

3.6.1 Список макросов

№	Имя макроса	Название кнопки	Предназначение	Расположение
1	В_БД	База Данных	Переход на лист «База Данных»	Лист «Меню»
2	Выход_в_меню	Меню	Переход на лист «Меню»	На всех листах, кроме «Меню»
3	Диаграмма	Диаграмма	Переход на лист «Диаграмма»	Лист «Меню»
4	Задание1н	Задание 1	Переход на лист «Задание1»	Лист «Меню»
5	Задание2	Задание2	Переход на лист «Задание2»	Лист «Меню»
6	Задание_1	Решение	Выполнение задания1	Лист «Задание1»
7	Макрос 8	Решение	Выполнение задания2	Лист «Задания2»
8	Обновить	Обновить	Обновление задания 3	Лист Свод_таб
9	Очистка	Очистка	Очистка	Лист задания 1
10	Очистка2	Очистка	Очитска	Лист задания 2
11	Сведение	Свед.об.уч	Переход на лист «Об авторе»	Лист «Меню»



3.6.2 Распечатка макросов

Sub В_БД()

'

' В_БД Макрос

''

Sheets("БД").Select

End Sub

Sub Задание2()

'

' Задание2 Макрос

'

'

Sheets("Задание2").Select

End Sub

Sub Сводтаб()

'

' Сводтаб Макрос

'

'

Sheets("Свод_таб").Select

End Sub

Sub Диаграмма()

'

' Диаграмма Макрос

'

'

Sheets("Диаграмма1").Select

End Sub

Sub Сведение()

'

' Сведение Макрос

'

'

Sheets("Свед. об уч.").Select

End Sub

Sub Макрос8()

'

' Макрос8 Макрос

'

M = InputBox("Введите статус учебного заведения, 1 - Высшее, 2 - Среднее, 3 - Ср.специальное")

Select Case M

Case 1

a = "Высшее"

Case 2

a = "Среднее"

Case 3

a = "Ср.специальное"

End Select

'

Range("G6").Select

Selection.Copy

Range("F25").Select

ActiveSheet.Paste

Range("F26").Select

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = a

Range("G25").Value = "Минимальные затраты"

Range("G26").Select

ActiveCell.FormulaR1C1 = _

"=DMIN(R[-20]C[-6]:R[-4]C[2],R[-20]C[-2],R[-1]C[-1]:RC[-1])"

End Sub

3.7 Инструкция по использованию программы

Чтобы начать работу, необходимо загрузить пакет MicrosoftExcel(Пуск > Программы > MicrosoftOffice > MicrosoftExcel)и открыть файл «Курсовая по Excel.xlsxm» (D:/Курсовая по Excel.xlsxm). На экране появится меню, состоящее из 6 кнопок. Нажатием по каждой из них можно перейти к задачам, диаграмме, исходной базе данных. С любого листа файла можно выйти в меню нажатием кнопки «Меню» . Такая структура позволяет быстро переходить к нужному листу. Для просмотра решения задачи, пользователь может нажать на кнопку «Решение». Для возврата базы данных в исходное состояние пользователь может нажать на кнопку «Очистка», которая произведет очистку.



4. Создание и обработка базы данных в среде Access

4.1 Краткое описание пакета

MicrosoftAccess- это система управления базами данных (СУБД),предназначенная для создания и обслуживания баз данных, обеспечения доступа к данным и их обработки.

СУБД MicrosoftAccess 2007 ориентирована на работу с объектами 6 различных типов. Рассмотрим их:

1. Таблицы - основной объект базы данных, хранит все данные и структуру базы (поля, их типы, свойства).

2. Запросы служат для поиска данных из одной или нескольких связанных таблиц. Используются как источники данных для форм и отчетов.

3. Формы служат для ввода и корректировки данных в удобном и привычном для пользователя виде. Формы могут использоваться и для просмотра запросов и таблиц. При выводе данных с помощью форм можно применять специальные средства оформления.

4. Отчёты предназначены для вывода на печать. Их можно также просматривать на экране, По своим свойствам и структуре отчёты во многом подобны формам.

5. Макросы предназначены для автоматизации повторяющихся операций при работе с СУБД.

6. Модули пишутся на языке программирования VisualBasicforApplication (VBA). Эти программы могут разрабатываться пользователем для реализации нестандартных процедур при создании приложения.



4.2 Многотабличная база данных

Формирование базы данных в MicrosoftAccess производилось путём копирования таблицы из MicrosoftExcel. Для создания многотабличной базы данных использован «мастер анализа таблицы». В результате действий, произведённых мастером, получаем 4 таблицы, имеющие связь 1-ко-многим

Схема данных

Таблица «Министерство»:

Таблица «Прочие данные»:

Таблица «Статус»

Таблица «Учебные заведения»

4.3 Объекты Курсовой работы

Объект	Имя объекта	Комментарий
Таблица 1	Министерство	Каждому министерству присваивается код
Таблица 2	Прочие данные	База данных со сведениями об учебных заведениях
Таблица 3	Статус	Каждому статусу присваивается код
Таблица 4	Уч зав	Сведения об учебных заведениях по статусу
Запрос 1	Задача1	Вывод сведений обо всех вузах, где количество учащихся находится в выбранном диапазоне
Запрос 2	Задача2	Подсчёт минимальных затрат на обучение одного учащегося в учебном заведении выбранного статуса
Запрос 3	Задача3	Вывод сведений обо всех учебных заведениях, сгруппированных по статусу учебного заведения, с подсчётом средней заработной платы преподавателей
Запрос 4	Задача4	Формирование отчета об учебных заведениях, где количество студентов ниже среднего
Запрос 5	Обновление	Обновляет поле «Затраты на обучение одного учащегося», при возможном изменении значений в полях «Материальные затраты» и «Количество учащихся»
Отчёт	Информация об учебных заведениях	Вывод сведений об учебных заведениях, где количество студентов ниже среднего

4.4 Структура базы данных

Имя таблицы	Имя поля	Тип данных	Размер поля
Министерсво	Количество учащихся	Числовой	Целое
Прочие данные	Количество преподавателей	Числовой	Целое
Статус	Фонд заработной латы	Денежный	-
Учебные заведения	Материальные затраты	Денежный	-
	Затраты на обучение одного учащегося	Денежный	-
	Заработная плата преподавателей	Денежный	-
	Учзав_Ко	Числовой	Длинное целое
	Мин_Код	Числовой	Длинное целое
	Обновление	Денежный	-

4.5Запросы

Все задачи решаются с помощью создания запросов в режиме конструктора.

4.5.1 Запрос на обновление

Условие: Заполнить вычисляемое поле «Затраты на обучение одного учащегося»

Решение: Для создания запроса на обновления мы переходим в таблицу «Прочие данные», в режим конструктора и добавляем новое поле «Затраты на обучение одного учащегося»



В итоге поле «Затраты на обучение одного учащегося» заполнилось данными:

Прочие данные
Зат на ободнуч
0,71р.
0,29р.
0,72р.
0,90р.
0,70р.
0,37р.
0,72р.
0,58р.
0,73р.
0,83р.
0,72р.
0,85р.
0,95р.
0,72р.
0,31р.
0,29р.

4.5.2 Запрос 1

Условие: Вывод сведений обо всех вузах, где количество учащихся находится в выбранном диапазоне.

Решение:Для решения задачи используем параметрический запрос на выборку. Для решения задачи используем поля: «Название учебного заведения» «Количество учащихся», «Статус учебного заведения», «Министерство подчинения», «Количество преподавателей», «Фонд заработной платы преподавателей», «Материальные затраты», «Затраты на обучение одного учащегося», «Заработная плата преподавателей». В графе «Условие отбора» по полю «Количество учащихся» используем логическую операцию And(или) для ввода двух диапазонов: « >[Количество учащихся от] And<[Кол - во уч до] »

В результате ввода значений:

Получаем:

4.5.3 Запрос 2

Условие: Подсчёт минимальных затрат на обучение одного учащегося в учебном заведении выбранного статуса.

Решение: Для решения задачи используем итоговый запрос на выборку с параметром. Для решения задачи используем два поля: «Номер модели» и «Стоимость». В графе «»Итог» появляется строка групповая операция. В столбце «Зат на ободнуч» использовали функцию «Min». Задаём параметр в поле «Код» на ввод статуса учебного заведения.



В результате ввода значений:

Получаем:

4.5.4 Запрос 3

Условие: Вывод сведений обо всех учебных заведениях, сгруппированных по статусу учебного заведения, с подсчётом средней заработной платы преподавателей.

Решение: Для решения задачи мы используем сводную таблицу, которую создаём после добавления полей «Статус», «Название учебного заведения», «Заработная плата преподавателей».

Выбрав режим «Сводная таблица» мы добавляем поле «Статус» и «Название учебного заведения», после чего выделяем поле «Заработная плата преподавателей» и нажимаем «Добавить в Детали». Выделяем в таблице поле «Заработная плата преподавателей», заходим в «Автовычисления»> «Среднее». Выделяем строку «Итоги» и меняем размер и цвет граф.

В результате выполнения получаем сводную таблицу:

Рис. 13 а. Сводная таблица

4.5.5 Запрос 4

Условие:Формирование отчета об учебных заведениях, где количество студентов ниже среднего.

Решение: Этот запрос мы создаем для создания отчёта. Для решения задачи мы используем параметрический запрос на выборку. Мы добавляем все поля нашей базы данных, и в поле «Фирма_Код» в графе «Условие отбора» вводим: «[введи значение от 1-5, где 1-Apple, 2-HTC, 3-Nokia, 4-Samsung, 5-Sony Ericson] ».

В результате ввода кода 5:

Получаем:

Фирма	Номер модели	Год выпуска	Заводская цена	Наценка магазина	Количество проданных телефонов	Стоимость
SonyEricsson	C510	2009	4 400,00р.	1 590,00р.	350000	5 990,00р.
SonyEricsson	K750i	2007	5 400,00р.	1 390,00р.	880000	6 790,00р.
SonyEricsson	XperiaRay	2011	14 500,00р.	3 590,00р.	320000	18 090,00р.
SonyEricsson	K810i	2007	6 700,00р.	2 390,00р.	790000	9 090,00р.

4.6 Создание отчёта

Отчёт выполнен с помощью «Мастера отчётов»:

Выбираем источник информации, по которому будет выполнен отчёт (Запрос: Задача4) , и поля, необходимые для отчёта (все поля).

Выбираем вид представленных данных, и если потребуется, добавляем уровни группировки.



Добавляем сумму по количеству проданных телефонов в графе «итоги»:

Выбираем вид макета для отчёта: Макет > ступенчатый, Ориентация > альбомная. Ставим галочку в графе «Настроить ширину полей для размещения на одной странице». Далее задаём имя отчёта: «Информация о телефонах» и нажимаем готово.

После этого мы выполняем редактирование в режиме конструктора:



Отчёт «Информация о телефонах» сохраняется в отдельной графе «Отчёты», и в любое время мы можем воспользоваться им:

4.7 Создание формы

Форма выполнена с помощью «Мастера форм»

Для создания формы выбираем необходимые поля из таблицы «Министерство» и таблицы «Прочие данные»

При выборе вида представления данных выбираем «подчиненные формы». Выбираем «табличный» внешний вид подчинённой формы. Задаём имена: для формы «Фирма», для подчинённой формы «Сведения подчинённая форма»

Форма готова. В режиме конструктора создаём кнопки: первая запись, предыдущая запись, закрыть форму, следующая запись, последняя запись.

4.8 Инструкция по использованию программы

Чтобы начать работу, необходимо загрузить пакет MicrosoftAccess(Пуск>Программы>MicrosoftOffice>MicrosoftOfficeAccess 2010) E:\ Курсовая по Access.accdb.Переход к задачам осуществляется с помощью «Области навигации». В ней пользователь может перейти к базе данных, запросам, просмотру отчета или формы.



Заключение

СУБД ориентирована на работу с объектами 6 различных типов:

Таблицы - основной объект базы данных, хранит все данные и структуру базы (поля, их типы, свойства).

Запросы служат для поиска данных из одной или нескольких связанных таблиц. Используются как источники данных для форм и отчетов.

Формы служат для ввода и корректировки данных в удобном и привычном для пользователя виде. Формы могут использоваться и для просмотра запросов и таблиц. При выводе данных с помощью форм можно применять специальные средства оформления.

Отчёты предназначены для вывода на печать. Их можно также просматривать на экране, По своим свойствам и структуре отчёты во многом подобны формам.

Макросы предназначены для автоматизации повторяющихся операций при работе с СУБД.

Модули пишутся на языке программирования VisualBasicforApplication (VBA). Эти программы могут разрабатываться пользователем для реализации нестандартных процедур при создании приложения.



Список литературы

1. Байдина Н.В., Костянко Н.Ф. Основы работы с электронной таблицей Excel: Учебное пособие.-СПб.: Петербургский государственный университет путей сообщения, 2005.-59 с.

2. Гуда А.Н., Бутакова М.А., Нечитайло Н.М., Чернов А.В. Информатика. Общий курс: Учебник/Под ред. Академика РАН В.И. Колесникова.-М.: Издательско-торговая корпорация «Дашков и К»; Ростов н/Д: Наука-Пресс, 2010.-400 с.

3. Байдина Н.В., проф. Костянко Н.Ф. Сборник заданий к выполнению лабораторных, контрольных и курсовой работ по дисциплине Информатика для студентов заочной формы обучения всех специальностей Наука-Пресс; 2009.-400с

Размещено на Allbest.ru