Создание и обработка базы данных

Введение. Программа MS Excel — это мощный и достаточно простой в использовании пакет обработки электронных таблиц, предназначенный для решения широкого круга планово-экономических, учетно-статистических, научно-технических и других задач, в которых числовая, текстовая или графическая информация с некоторой регулярной, повторяющейся структурой представлена в табличном виде. 
Электронная таблица так же, как и обычная таблица, состоит из строк и столбцов, на пересечении которых располагаются ячейки. Однако, в отличие от обыкновенной, электронная таблица служит не только для наглядного представления числовой, текстовой и графической информации. Основное отличие электронной таблицы от обычной состоит в возможности размещения различных расчетных формул в ее ячейках, а также в широкой автоматизации обработки представленных в табличном виде данных. БД является специальным образом организованной системой хранения массивов описания фрагментов предметной области, которая обеспечивает пользователю оперативный доступ к хранящейся информации. Состав хранимой в такой БД информации определяется как предметной областью, так и перечнем решаемых задач.
СУБД разрабатывались из-за потребности в выделении и обобщении информационной системы. БД является обязательным компонентом любой технологии, она включает данные о производственной системе предприятия, в нее также поступает информация с внешнего окружения предприятия.
Объект исследования данной работы – понятие ИТ, а предмет исследования – СУБД MS Access и MS Excel. Цель курсовой работы – получение концептуального представления о данных программных продуктах.
Для достижения поставленной цели необходимо решить следующие задачи:
подготовить таблицу в MS Excel и заполнить ее данными с использованием стандартной формы;
выполнить в MS Excel необходимые вычисления, фильтрацию и сортировку данных, подведение итогов по группам;
спроектировать структуру БД в MS Access;
создать необходимые формы для ввода и корректировки данных в таблицах MS Access;
MS Access сформировать и выполнить запросы;
MS Access подготовить требуемые отчеты.
Постановка задачиВариант 16. Телевизионные передачи
База данных должна содержать следующие элементы:
- название телевизионной передачи;
- номер телеканала;
- периодичность (количество выходов в эфир за неделю);
- время выхода в эфир;
- продолжительность передачи;
- жанр передачи (развлекательная, публицистическая, спортивная, новостная, …); - время, отведенное на рекламу (в минутах).
Задачи, выполняемые информационной технологией:
Заполнение и редактирование таблиц базы данных.
Формирование списка развлекательных передач, оканчивающихся позднее запрашиваемого времени.
Подсчет суммарной продолжительности передач выбранного пользователем жанра по каждому телеканалу за неделю.
Подсчет максимального и минимального отношения времени на рекламу к продолжительности передачи по каждому телеканалу.
Формирование отчета, включающего полную информацию о телевизионных передачах, с группировкой по жанрам и подсчетом общего времени трансляции по группе.
Работа со списками в табличном процессоре MS EXCEL
Создание стандартной формыЧтобы создать форму заполняем шапку таблицы, записываем первую запись, выделяем диапазон ячеек (шапку с первой записью) и щелкаем по кнопке «Форма» на панели быстрого доступа в правом верхнем углу. Заполняем данные таблицы→ «Добавить». После окончания ввода щелкаем «Закрыть». Выполняем редактирование таблицы: включаем «Перенос текста», выравниваем текст посередине и по центру и выделяем жирным текстом заголовки таблицы (рис. 1).

Рисунок 1 – Заполнение данных в форме Excel
Заполнение и редактирование таблицы Заполняем и редактируем данные в самой таблице (рис. 2).

Рисунок 2 – Заполнение и редактирование данных в таблице Excel
Формирование списка развлекательных передач, оканчивающихся позднее запрашиваемого времени
В новый лист «Запрос 1» скопируем исходную таблицу, удалим лишние поля. Добавим расчетное поле Окончание телепередачи, рассчитываемое по формуле: =ВРЕМЯ(0;C3;0)+B3.
Далее выделим ее шапку и вызовем меню Данные-фильтр. Для поля Жанр передачи установим условие – развлекательная. Для поля Окончание телепередачи установим условие – фильтр числовой больше – значение 20:00:00. В результате получим список развлекательных передач, оканчивающихся позднее 20:00:00 (рис. 3).

Рисунок 3 – Список развлекательных передач, оканчивающихся позднее 20:00:00
Данный запрос можно решить и с помощью Расширенного фильтра. На новый лист «Запрос 1-2» скопируем исходную таблицу, удалим лишние поля. Сформируем таблицу условий и вызовем Расширенный фильтр. В результате получим аналогичное решение (рис. 4).

Рисунок 4 – Список развлекательных передач, оканчивающихся позднее 20:00:00
В процессе выполнения данного фильтра запишем макрос и создадим кнопку его вызова (рис. 5-6).

Рисунок 5 – Макрос для выполнения фильтра список развлекательных передач, оканчивающихся позднее указанного времени

Рисунок 6 – Кнопка вызова макроса фильтра
Подсчет суммарной продолжительности передач выбранного пользователем жанра по каждому телеканалу за неделю
В новый лист «Запрос 2» скопируем исходную таблицу, удалим лишние поля. Создадим новое поле Суммарная продолжительность передачи в неделю в мин, вычисляемое по формуле: =D2*E2 (Периодичность*Продолжительность).
Выделим ее шапку и вызовем меню Данные-фильтр. Для поля Жанр передачи установим условие – развлекательная (или любой по желанию пользователя).
Далее вызовем меню Данные – Промежуточный итог. Выберем итого по Суммарной продолжительности передачи в неделю в мин и операцию суммирования при каждом изменении поля Телеканал. Отформатируем таблицу и получим следующие данные (рис. 7).

Рисунок 7 – Суммарная продолжительность передач выбранного жанра по каждому телеканалу за неделю
Подсчет максимального и минимального отношения времени на рекламу к продолжительности передачи по каждому телеканалу
В новый лист «Запрос 3» скопируем исходную таблицу, удалим лишние поля. Создадим новое поле % рекламы, вычисляемое как: =D2/C2*100 (отношение продолжительности рекламы к продолжительности телепередачи).
Вызовем меню Вставка – Сводная таблица, укажем необходимые поля для отображения. В качестве столбцов используем минимальные и максимальные значения % рекламы. В качестве строк - телеканалы (рис. 8).

Рисунок 8 – Список максимального и минимального % рекламы в телепередачах по каждому телеканалу
Формирование отчета, включающего полную информацию о телевизионных передачах, с группировкой по жанрам и подсчетом общего времени трансляции по группеВ новый лист «Отчет» скопируем исходную таблицу, удалим лишние поля. Создадим новое поле Итоговая продолжительность мин, вычисляемое как произведение продолжительности телепередачи на ее периодичность в неделю. Отсортируем данные по полю Жанр.
Вызовем меню Вставка – Промежуточный итог. Выберем сумму для поля Итоговая продолжительности в мин при каждом изменении поля Жанр передачи. Отформатируем таблицу и получим следующие данные (рис. 9).

Рисунок 9 – Отчет по полной информации о телевизионных передачах, с группировкой по жанрам и подсчетом общего времени трансляции по группе
ГистограммаВыделим данные для гистограммы – ячейки с жанров и итоговую продолжительность передач в мин и построим гистограммы при помощи меню Вставка - Диаграммы – Гистограмма, отредактируем гистограмму при помощи макета (рис. 10).

Рисунок 10 – Гистограмма Общее время трансляции телепередач по жанрам
Круговая диаграммаПо этим же данным построим круговую диаграмму при помощи меню Вставка - Диаграммы – Гистограмма, отредактируем гистограмму при помощи макета (рис. 11).

Рисунок 11 – Круговая диаграмма Общее время трансляции телепередач по жанрам
Работа с базами данных в СУБД MS ACCESSСтруктура базы данныхВариант 16. Телевизионные передачи
110934585090002235208509000Таблица 1. «Телепередачи»
Название телепередачи Номер канала Периодичность р/недВремя выхода в эфир Продолжительность мин Жанр передачи Время на рекламу мин
текстовое числовое числовое дата/время числовое текстовое числовое
5862320-762000690244316230690245316230
2997207302500 Таблица 2. «Телеканалы»
Номер канала Название канала
числовое текстовое
800104127500 - ключевое поле. Подразумевается, что на канале не могут быть одноименные телепередачи, а в целом на телевидении – могут (например, передача «Новости»).
Создание таблиц
Открываем MS ACCESS и щелкаем «Пустая база данных». Задаем имя файла «Телевизионные передачи» и щелкаем «Создать». Открывается окно для создания первой таблицы. Правой кнопкой мыши щелкаем по вкладке «Таблица 1» → «Конструктор» → Называем таблицу «Телеканалы» → «ОК» и конструируем таблицу: задаем имя и тип данных для каждого поля. Далее выделяем поле «Номер канала» и определяем его ключевым. Правой кнопкой мыши щелкаем по названию таблицы → «Сохранить» → Закрываем конструктор (рис. 12).

Рисунок 12 – Таблица Телеканалы. Конструктор
Аналогично конструируем вторую таблицу – Телепередачи (рис. 13).

Рисунок 13 – Таблица Телепередачи. Конструктор

Рисунок 14 – Таблица Телепередачи. Конструктор
Обращаю внимание, что поле Номер телеканала таблицы Телепередачи заполняется при помощи раскрывающегося списка значений из таблицы Телеканалы. А поле Жанр передачи – при помощи раскрывающегося списка готовых значений.
Слева в области переходов появилось изображение созданных таблиц. Для создания связей между таблицами на ленте «Работа с базами данных» выбираем команду «Схема данных». Появляется вкладка «Схема данных» и в ней окно «Добавление таблицы». Добавляем по очереди наши таблицы кнопкой «Добавить» внизу и закрываем окно. Перетаскиваем поле Код изготовителя из одной таблицы в другую. В появившемся окне ставим флажок в «Обеспечение целостности данных» и щелкаем по кнопке «Создать» (рис. 15). Получаем связь между таблицами типа «Один ко многим». Сохраняем и закрываем макет схемы данных.

Рисунок 15 – Схема данных БД Телевизионные передачи
Создание форм
На ленте «Создание» выбираем «Мастер форм» и дальше по шагам создаем форму для всех полей таблицы «Телеканалы» и «Телепередачи». Выбираем внешний вид формы «в один столбец» и получаем сложную форму (рис. 16). Добавим в нее управляющие кнопки с помощью панели конструирования.

Рисунок 16 – Форма Телеканалы и телепередачи
Заполнение и редактирование таблиц базы данныхЗаполнять базу данными можно в режиме таблиц или с помощью форм. Заполним БД в режиме таблиц (рис. 17-18).

Рисунок 17 – Таблица Телеканалы

Рисунок 18 – Таблица Телепередачи
Формирование списка развлекательных передач, оканчивающихся позднее запрашиваемого времениНа ленте «Создание» вызываем «Конструктор запросов». Добавляем одну таблицу «Телепередачи» и заполняем поля запроса. Условие по полю Жанр передачи – «развлекательная». Создаем вычисляемое поле Время окончания:
IIf(FormatNumber(DateAdd("n";[Телепередачи]![Продолжительность мин];[Телепередачи]![Время выхода в эфир]))>1;DateAdd("n";[Телепередачи]![Продолжительность мин];[Телепередачи]![Время выхода в эфир])-1;DateAdd("n";[Телепередачи]![Продолжительность мин];[Телепередачи]![Время выхода в эфир])).
Для этого поля выставляем условие: >FormatNumber([Введите время]) (рис. 19-20).

Рисунок 19 – Запрос 1. Конструктор

Рисунок 20 – Результат запроса
В формуле учета времени окончания учитывается время до полуночи.
Подсчет суммарной продолжительности передач выбранного пользователем жанра по каждому телеканалу за неделю
На ленте «Создание» вызываем «Конструктор запросов». Добавляем обе таблицы и заполняем поля запроса (рис. 21-22). Жанр – параметр для ввода жанра. Далее выбираем группировку по названию канала и жанру передачи. Добавляем расчетное поле Суммарная продолжительность передач мин, рассчитываемое по формуле:
Sum([Телепередачи]![Продолжительность мин]*[Телепередачи]![Периодичность р/нед])

Рисунок 21 – Запрос 2. Конструктор

Рисунок 22 – Результат запроса
Подсчет максимального и минимального отношения времени на рекламу к продолжительности передачи по каждому телеканалу
На ленте «Создание» вызываем «Конструктор запросов». Добавляем обе таблицы и заполняем поля запроса. Создаем расчетные поля:
Максимальный % рекламы: Max([Телепередачи]![Время на рекламу мин]/[Телепередачи]![Продолжительность мин]*100)
Минимальный % рекламы: Min([Телепередачи]![Время на рекламу мин]/[Телепередачи]![Продолжительность мин]*100)
Делаем группировку по Названию канала (рис. 23-24).

Рисунок 23 – Запрос 3. Конструктор

Рисунок 24 – Результат запроса
Формирование отчета, включающего полную информацию о телевизионных передачах, с группировкой по жанрам и подсчетом общего времени трансляции по группе
Создадим отчет по данным таблицы Телеканалы. В Конструкторе отчетов создадим расчетное поле Итого продолжительность передач по жанру в мин по формуле:
=Sum([Продолжительность мин]*[Периодичность р/нед])
Аналогично сделаем с суммирование промежуточных итогов (рис. 25-27).

Рисунок 25 – Отчет общего времени трансляции по жанрам. Конструктор

Рисунок 26 – Отчет общего времени трансляции по жанрам
ЗаключениеПрограмма обработки электронных таблиц MS Excel обеспечивает пользователя богатым набором возможностей создания и изменения таблиц, Которые могут содержать числа, текст, даты, денежные единицы, графику, а также математические и иные формулы для выполнения вычислений. Ввод данных в таблицы, их форматирование и выполнение различного рода вычислений в значительной степени автоматизированы. Предусмотрены средства представления числовых данных в виде диаграмм, создания, сортировки и фильтрации списков, статистического анализа данных и решения оптимизационных задач.
В результате проделанной работы были получены практические навыки разработки БД в MS Access с помощью Конструкторов и Мастеров, Панелей инструментов, построителя выражений и макросов. Кроме того, были получены навыки вычисления значений, сортировки, фильтрации, подведения итогов и построения диаграмм в MS Excel. Данные навыки будут мной использованы в моей дальнейшей профессиональной деятельности.
СУБД Microsoft Access объединяет сведения из разных источников в одной реляционной базе данных. В Access предусмотрены все необходимые средства для определения и обработки данных, а также для управления ими при работе с большими объемами информации. Microsoft Access позволяет: обновлять и изменять данные, получать ответы на запросы, осуществлять поиск нужных данных, анализировать данные, печатать отчеты, диаграммы.
При выполнении данной работы результаты расчетов в обоих программных продуктах оказались идентичными. Однако MS Access более удобен в плане наглядности представления данных и удобства их ввода.
При выполнении запросов MS Excel более удобен в части представления итоговых данных. А графическое представление расчетных данных в виде диаграмм позволяет грамотно интерпретировать их. Хотя в MS Access также возможно построение диаграмм в формах.
Отчеты лучше смотрятся в MS Access, хотя в MS Excel позволяет получить итоговые данные и сгруппировать их.
Мне показались удобными оба программных продукта. На основании вышеизложенного можно сделать вывод о том, что поставленные задачи решены, а цель достигнута.
Список используемых источниковПечатные издания
Бекаревич Ю.Б. Самоучитель MS Office Access 2016. Изд.: БХВ-Петербург, 2017. – 408 с.
Электронные ресурсы
Национальный открытый Университет «Интуит»: «Введение в программные системы и их разработку». Лекция 7:  «Электронные таблицы»: https://www.intuit.ru/studies/courses/3632/874/lecture/14301?page=2 (дата обращения 19.05.2021)
Сайт Microsoft. Основные сведения о запросах (основы Access, часть 3): https://support.office.com/ru-ru/article/Основные-сведения-о-запросах-основы-Access-часть-3-ce3b5537-14c6-4994-ba67-4de898df7c0b #office (дата обращения 19.05.2021)
Сайт Microsoft. Разработка и создание таблиц для базы данных (основы Access часть 1): https://support.office.com/ru-ru/article/Разработка-и-создание-таблиц-для-базы-данных-основы-Access-часть-1-bff6e7b2-3055-419b-8751-1ade558ea31f #office (дата обращения 19.05.2021)
Сайт Microsoft. Создание связи между таблицами (основы Access, часть 2): https://support.office.com/ru-ru/article/Создание-связей-между-таблицами-основы-Access-часть-2-a93d9491-8724-4cd3-96df-ce504914692f #office (дата обращения 19.05.2021)
Сайт Microsoft: Краткое руководство по началу работы с Access2013: https://support.office.com/ru-ru/article/Краткое-руководство-по-началу-работы-с-Access-2013-aa404d26-ce42-4dd2-ac5e-51f9f39f7275?CorrelationId=4992aa0a-d461-40d5-a201-6bd790491acf&ui=ru-RU&rs=ru-RU&ad=RU&ocmsassetID=HA103673689 (дата обращения 19.05.2021)