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

 














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

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



Введение


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

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




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


ВАРИАНТ № 7

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

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

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

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

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

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

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

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

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

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

Задачи:

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

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

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

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



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


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

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

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

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

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



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


.1 Краткое описание пакета

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

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

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

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

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

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

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

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

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


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


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

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

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

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


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

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



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


Название учебного заведенияКоличество учащихсяКоличество преподавателей Фонд заработной платыМатериальные затратыМинистерство подчиненияСтатус учебного заведенияЗат. на об.одн.учЗарп.преп.С - П Гос. Тех. Институт54474564 375 000,00р.3 869,00р.Мин.обр.РФВысшее803,19р.9 594,30р.С-П Гос.Тех.училище.Раст.Полимер. 61865332 665 000,00р.1 896,00р.Мин.обр.РФСред.спец.430,81р.5 000,00р.С - П Гос.Универ.52646754 245 000,00р.3 765,00р.Мин.обр.РФВысшее806,42р.6 288,89р.С - П Гос.Универ.аэрокос.приборост. 47483494 890 000,00р.3 926,00р.Мин.обр.РФВысшее1 029,91р.14 011,46р.С - П Гос.Универ. Водных комун.65677784 825 000,00р.3 824,00р.Мин.тран.РФВысшее734,73р.6 201,80р.С - П Гос.Универ.ИТМО.53597934 965 000,00р.3 874,00р.Мин.кул.РФВысшее926,48р.6 261,03р.С - П Гос.Универ.кино и телевид.43297983 990 000,00р.3 698,00р.Мин.кул.РФВысшее921,69р.5 000,00р.С - ПГос.лицей. Культ.и Искусств. 52647462 605 000,00р.1 965,00р.Мин.обр.РФСреднее494,87р.3 491,96р.С-П Гос.училище.низТемпер.Пищ.Тех. 61375642 835 000,00р.1 785,00р.Мин.обр.РФСред.спец.461,95р.5 026,60р.С - П Гос.Универ.телекомуникаций41977484 730 000,00р.3 978,00р.Мин.связ.РФВысшее1 127,00р.6 323,53р.С - ПГос.гимназия.техн.и дизайна 65456972 369 000,00р.1 923,00р.Мин.обр.РФСреднее361,96р.3 398,85р.С - ПГос.училище.эконом. и финан.48977343 012 000,00р.3 527,00р.Мин.обр.РФСред.спец.615,07р.4 103,54р.С - П Гос.Универ. Элект.- тех.ЛЭТИ54338954 475 000,00р.3 976,00р.Мин.обр.РФВысшее823,67р.5 000,00р.С - П Гос.Гуман. Универ.профсоюз.53956474 896 000,00р.3 899,00р.Негос.АкВ.Высшее907,51р.7 567,23р.С - П Гос.Инст.Внешнеэкон.Связи57575214 923 000,00р.4 021,00р.Негос.АкВ.Высшее855,13р.9 449,14р.С - П Гос.Инст. Машин.Строения44255334 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р.

.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р.

.5 Диаграмма


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


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



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



.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ОчисткаОчисткаОчисткаЛист задания 110Очистка2ОчисткаОчитска Лист задания 211СведениеСвед.об.учПереход на лист «Об авторе»Лист «Меню»

.6.2 Распечатка макросовВ_БД()

'

' В_БД Макрос

''

Sheets("БД").SelectSubЗадание2()

'

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

'

'

Sheets("Задание2").SelectSubСводтаб()

'

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

'

'

Sheets("Свод_таб").SelectSubДиаграмма()

'

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

'

'

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

Sub Сведение()

'

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

'

'

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

Sub Макрос8()

'

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

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

Select Case M1= "Высшее"2= "Среднее"3= "Ср.специальное"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


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


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



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


.1 Краткое описание пакета

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

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

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

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

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

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

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

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



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


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


Схема данных


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


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


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

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


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


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

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


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

.5Запросы


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


.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р.

.5.2 Запрос 1

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

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



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



Получаем:



.5.3 Запрос 2

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

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



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



Получаем:


.5.4 Запрос 3

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

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



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

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


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


.5.5 Запрос 4

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

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



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

Получаем:


ФирмаНомер моделиГод выпускаЗаводская ценаНаценка магазинаКоличество проданных телефоновСтоимостьSonyEricssonC51020094 400,00р.1 590,00р.3500005 990,00р.SonyEricssonK750i20075 400,00р.1 390,00р.8800006 790,00р.SonyEricssonXperiaRay201114 500,00р.3 590,00р.32000018 090,00р.SonyEricssonK810i20076 700,00р.2 390,00р.7900009 090,00р.

.6 Создание отчёта


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

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



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



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



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

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




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



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


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

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

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

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



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


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



Заключение


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

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

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

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

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

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

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



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


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

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

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


ПОЯСНИТЕЛЬНАЯ ЗАПИСКА К КУРСОВОЙ РАБОТЕ Создание и обработка БД в среде Excel и Access Введени

Больше работ по теме:

КОНТАКТНЫЙ EMAIL: [email protected]

Скачать реферат © 2017 | Пользовательское соглашение

Скачать      Реферат

ПРОФЕССИОНАЛЬНАЯ ПОМОЩЬ СТУДЕНТАМ