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

Создание и обработка базы данных в среде 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 База данных «Учебные заведения Санкт - Петербурга»

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

3.4.1 Задача 1

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

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

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

3.4.2 Задача 2

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

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

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

Получаем:

.4.3 Задача 3

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

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

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

ебный

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

.5 Диаграмма

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

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

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

.6Работа с макросами

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

.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 Объекты Курсовой работы

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

.5Запросы

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

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

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

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

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

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

Получаем:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

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

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

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

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

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

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

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

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

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

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