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

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

Введение

Рассматриваются основные особенности создания, редактирования, хранения и передачи заказчику баз данных в среде Microsoft Office. Для получения оценок сначала рассматриваются особенности реализации баз данных (БД) в приложении M Excel, а затем в M Access.

После уточнения формирования исходных данных на конкретном примере, заданном вариантом индивидуального задания, разрабатываются запросы. На основании запросов определяются основные характеристики БД: количество полей, тип данных, точность используемых числовых значений и т. д.

Реализация запросов в M Excel, в том числе с использованием макросов, позволяет определить основные характеристики таблицы: количество ячеек. Затем на конкретных примерах иллюстрируется работа запросов в M Excel.

Анализ исходной таблицы позволяет заключить о целесообразности ее деления на части с целью уменьшения условной площади (количества ячеек). На рассматриваемом примере показана эффективность деления исходной таблицы на две части.

Проиллюстрированы варианты реализации запросов в M Access.

Основные положения обоснованы и проиллюстрированы.

1 Постановка задачи



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

2. Электронная таблица EXCEL

2.1 Заполнение таблицы базы данных

Рисунок 1 - Рабочее окно EXCEL

База данных в Excel - это список связанных данных, в котором строки данных являются записями, столбцы - полями. Верхняя строка списка содержит название каждого из столбцов. Ссылка может быть задана либо как диапазон ячеек, либо как имя, соответствующему диапазону списка.

Для ввода информации в ячейки рабочего листа вначале следует сделать ячейку активной, переместив в нее указатель мыши и щелкнуть один раз правой кнопкой мыши. После этого можно либо просто вводить данные с клавиатуры, либо щелкнуть левой кнопкой мыши в строке формул над рабочим листом и после этого вводить данные.

Рисунок 2 - Заполненная база данных

2.2 Формирование списка развлекательных передач, оканчивающихся не позднее запрашиваемого момента времени

Для этого используем автофильтр. Он позволяет вывести на рабочий лист все записи, удовлетворяющие определенным требованиям.

Автофильтр создаётся следующим образом: выделяем всю таблицу и выбираем «Данные-Фильтр-Автофильтр». Возле каждого поля строки заголовка появляется раскрывающиеся списки в виде кнопки с треугольником.

Рисунок 3 - Создание автофильтра

Для создания списка удовлетворяющего запросу. Например, формирование списка станций, введенных в эксплуатацию не позже запрашиваемой даты. Нажимаем на треугольник в графе «Дата и время выхода в эфир» и выбираем «условие». (см. рис.3)

Рисунок 4 - Формирование списка, удовлетворяющего запросу

Далее выбираем нужное условие. Например, до 17.11.14 9:20. (см. рис.5)

Рисунок 5 - Задание условия

Рисунок 6 - Готовый список по запросу

2.3 Подсчет суммарной продолжительности передач выбранного пользователем жанра по каждому телеканалу

Для этого на пустом листе создаём дополнительную таблицу, где передачи по жанрам, но с дополнительной графой «Суммарная продолжительность передач развлекательного жанра (В минутах)»

Для подсчёта суммарной продолжительности передач используем специальную формулу, в нужную ячейку вставляем формулу =СУММЕСЛИ (диапазон; критерий; диапазон суммирования)» и выбираем диапазон ячеек, чьи значения будут вычисляться. Таким образом, мы получаем суммарную продолжительность передач развлекательного жанра, при этом рассортировав их по каналам.



Рисунок 7 - Список передач развлекательного жанра и их суммарная продолжительность

2.4 Подсчет средней продолжительности рекламы по каждому телеканалу

Это делается, как и предыдущие пункты (через автофильтр).

В графе «Номер телеканала» выбирается «сортировка от А до Я». Для подсчета средней продолжительности рекламы используем формулу «=СРЗНАЧЕСЛИ (диапазон; критерий; диапазон усреднения)», выбираем сначала столбец телеканалов, затем пишем критерий выбора телеканалов и в конце выбираем столбец рекламы.

Рисунок 8 - Подсчет средней продолжительности рекламы по каждому телеканалу

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

С помощью автофильтра необходимо выполнить группировку передач по жанрам.

Создать 2 дополнительные мини-таблицы, в которых будут подсчитаны общее число передач каждого жанра и общее время трансляции по группе. Подсчет выполняется по средствам функции =СУММЕСЛИ (диапазон; критерий; диапазон суммирования). В данном случае за диапазон суммирование берется столбец (по числу передач) единиц.

Рисунок 9 - Подсчет общего количества передач по жанрам и их общая продолжительность по группам.

2.6 Макросы

Макрос - это программа, написанная на языке Visual Basic for Aplication. Программа, реагирующая на изменение данных, предназначена для сведения к одному действию изменение таблиц и графиков.

Например, если необходимо сформировать данные по какому-нибудь принципу, чтобы не делать лишних движений, можно записать макрос и при необходимости будет достаточно нажать одну кнопку.

Создание макросов производится так: Выделение ячейки, Сервис - Макрос - Начать запись. Имя макроса присваивается под выполняемые действиями события. Выделение ячейки или таблицы, Сервис - Макрос - Остановить запись. Теперь можно применить макрос к другим ячейкам.

Рисунок 10 - Построение макроса

Далее «Сервис - Настройки - Команды - Макросы» и переносим кнопку на панель инструментов.

Рисунок 11 - Создание кнопки вызова макроса

Рисунок 12 - Кнопки управления макросами

2.7 Построение диаграммы

Построение круговой диаграммы «Продолжительность передачи»

1. Выделить столбец таблицы «Продолжительность передачи».

2. Запустить Мастер диаграмм (Вставка - Диаграмма…).

3. Выбрать тип диаграммы.

4. Выбрать закладку Ряд и установить соответствующие значения в 2 полях ввода.

5. Выбрать закладку Подписи данных и установить флажок на закладке Значения

6. Последний шаг мастера позволяет выбрать один из вариантов расположения графика: на отдельном листе рабочей книги (новом) или на текущем листе.

Рисунок 13 - Построение диаграммы

Рисунок 14 - Выбор диапазона данных для построения диаграммы



Рисунок 15 - Готовая диаграмма

3. Microsoft Access

Общие положения

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

База данных представляет собой организованную структуру, используемую для хранения данных, то есть любых сведений о явлениях, процессах, действиях и т.д. данные несут в себе информацию о событиях, происходящих в материальном мире и, по сути, являют зарегистрированными сигналами, возникшими в результате этих событий. Данные становятся информацией, если пользователь их обработает и осмыслит, используя при этом адекватные данные методы. Сегодня большинство СУБД размещают в своих структурах не только данные, но и методы (программные коды), поэтому можно утверждать, что Microsoft Access - это СУБД, предлагающая широкий диапазон средств для хранения информации и эффективного управления этой информацией.

Базы данных содержат различные объекты, основными из которых являются таблицы. Структура простейшей базы данных соответствует структуре ее двухмерной таблицы, содержащей столбцы и строки. Их аналогами в структуре простейшей базы данных являются поля и записи.

3.1 Создание таблиц в режиме конструктора

Выбор режима осуществляется щелчком на значке «Создание таблицы в режиме конструктора», размещенном в окне базы данных, или режиме «Конструктор» выбирается в окне «Новая база данных», которое открывается после выполнения щелчка на кнопке «Создать» окна базы данных.

После выбора режима на экране выводится окно Конструктора таблицы. Оно разделено на две части. Верхняя часть состоит из трех колонок. В первую колонку вводится, имена создаваемых полей, во второй отображается типы данных. Третья колонка «Описание» может содержать краткую информацию относительно поля таблицы. Этот раздел, как правило, заполняется редко.

Для создания в будущем запросов необходимо создать 2 таблицы, из которых будут запрашиваться данные.

Рисунок 16 - Создание таблицы в режиме конструктора

Рисунок 17 - создание полей для таблицы № 1

Рисунок 18 - Создание полей для таблицы № 2

После создания полей заполняем таблицы (см. рис. 19 и рис. 20). После определения структуры таблицы можно приступать ко второму этапу создания таблицы - вводу в нее данных. Непосредственно ввод данных осуществляется в режиме таблицы. Переход в этот режим осуществляется из окна базы данных щелчком на кнопке «Открыть» при выделенном имени таблицы или двойным щелчком непосредственно на значке таблицы.

Новая таблица не имеет записей, а содержит только наименование столбцов (полей). Для заполнения таблицы данными курсор устанавливает в требуемую ячейку указателем мыши. Переход к следующей ячейке можно выполнять с помощью клавиши <Tab>, а в предыдущее поле можно попасть с помощью комбинации клавиш <Shift+Tab>. После заполнения последней ячейки и нажатия клавиши <Tab> курсов переместится в первую ячейку следующей строки, и Access автоматически сохранит только что введенную запись. Таким образом, после заполнения таблицы данными их сохранять не надо; Access все введенные данные сохраняет автоматически. Однако если при работе с таблицами происходит изменение ее структуры (например, менялась ширина столбцов), то Access попросит подтвердить эти изменения. Для увеличения или уменьшения ширины столбцов в таблице (в режиме конструктора) нужно указатель мыши установить в строку заголовка столбцов, на границу между столбцами, и перетащите мышь вправо или влево.

Рисунок 19 - Заполненная таблица № 2

Рисунок 20 - Заполненная таблица № 1

3.2 Формирование списка развлекательных передач, оканчивающихся не позднее запрашиваемого момента времени с указанием времени окончания передачи

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

В окне базы данных выбрать объект «Запросы» и выполнить щелчок на кнопке «Создать». В окне «Новый запрос» выбрать режим «Конструктор» и выполнить щелчок на кнопке «ОК». После чего на экране появится окно «Запрос на выборку», а также диапазон окно «Добавление таблицы». Запрос создаём с помощью конструктора.

В окне «Добавление таблицы» выделить таблицу «Таблица2» и выполнить щелчок по кнопке «Добавить», аналогичное действие выполнить с таблицей «Таблица1», после чего щелчок по кнопке «Закрыть».

Окно «Запрос на выборку» состоит из двух частей. В верхней части отражается таблицы, а нижняя часть разбита на столбцы и каждому столбцу соответствует определенное поле будущей итоговой таблицы. Для создания такой таблицы необходимо перенести имена полей из таблиц, расположенных в верхней части окна, в строку «поле», тем самым, осуществив выбор полей, включаемых в итоговую таблицу. В условие отбора пишем данное условие например, <=17.11.2014 9:20:00.

Рисунок 21 - Создание простого запроса

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

Рисунок 22 - Вывод данных по простому запросу

3.3 Подсчет суммарной продолжительности передач выбранного пользователем жанра по каждому телеканалу

Так же создаём запрос, но в условиях отбора в строке «Жанр передачи» пишем «Развлекательная».

Рисунок 23 - Создание запроса конструктором

После во вкладке «Главная» выбираем «Итоги». В появившейся строке в столбце «Продолжительность передачи» выбираем «Сумма», а в столбце «Номер телеканала» с помощью фильтра выбираем нужный телеканал. В итоге таблица покажет нам суммарное время передач на выбранном телеканале нужного жанра.



Рисунок 24 - Суммарное время всех передач развлекательного жанра

Рисунок 25 - Суммарное время всех передач развлекательного жанра на телеканале «Первый»

3.4 Подсчет средней продолжительности рекламы, по каждому телеканалу

Сделать это еще проще, чем предыдущий пункт. Нам надо в нашей первой таблице во вкладке «Главная» выбрать «Итоги».

Рисунок 26 - Выбор пункта «Итоги»

После чего в итогах в столбце «Время, отведенное на рекламу (В минутах)» нам надо выбрать «Среднее».

Рисунок 27 - Выбор типа итогов

В итоге мы получаем среднюю продолжительность рекламы во всех передачах.

Рисунок 28 - Среднее время, отведенное на рекламу во всех передачах

Чтобы посмотреть, какая средняя продолжительность рекламы, к примеру, на канале ТНТ, мы в столбце «Номер телеканала» в фильтре оставляем галочку только напротив ТНТ.

Рисунок 29 - Задание фильтра для столбца «Номер телеканала»

В итоге получаем среднюю продолжительность рекламы на канале «Первый». Для просмотра средней продолжительности рекламы на других телеканалах в фильтре просто выбираем нужный нам телеканал.

Рисунок 30 - Средняя продолжительность рекламы на канале «Первый»

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

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

Рисунок 31 - Порядок создания отчёта



Рисунок 32 - Порядок создания отчёта

Рисунок 33 - Порядок создания отчёта

Рисунок 34 - Порядок создания отчёта

Рисунок 35 - Порядок создания отчёта

Теперь в макете надо выбрать в области данных «Название телевизионной передачи», потом «Итог» и там выбрать «Число записей».

Рисунок 36 - Макет отчета. Задание итога для столбца «Название телевизионной передачи»

Потом в области данных выбрать «Продолжительность передачи (В минутах)», там выбрать «Итог» и выбрать «Сумма».

Рисунок 37 - Макет отчета. Задание итога для столбца «Продолжительность передачи (В минутах)»

Отчет готов. Теперь для того, чтобы узнать количество передач определенного жанра и их общую продолжительность, надо в фильтре столбца «Жанр передачи» выбрать нужный нам жанр.



Рисунок 38 - Готовый отчет

Рисунок 39 - Готовый отчет

Рисунок 40 - Выбор фильтра для столбца «Жанр передачи»

Рисунок 41 - Готовый отчет с применением фильтра.

Заключение

база данных microsoft exсel

Программы Microsoft Excel и Microsoft Access позволяют решить одну и ту же задачу двумя немного схожими способами. Оба приложения позволяют выполнять разнообразные запросы для сортировки и фильтровки данных, производить сложные расчеты для получения необходимых данных, составлять отчеты по данным и просматривать их в разных вариантах, использовать формы для добавления, изменения, удаления и просмотра данных. В обеих программах данные организованы в виде столбцов, или полей, в которых хранится информация определенных видов или типов данных, то есть в виде таблиц, что упрощает работу с ними. Только в Excel достаточно создать одну таблицу, а в Access необходимо создавать несколько. Но при желании в Microsoft Excel тоже можно составлять несколько таблиц на разных листах. Первая ячейка вверху каждого столбца отводится для метки столбца. Имеется одно терминологическое различие: в Microsoft Excel строка, в Microsoft Access запись. Microsoft Exсel - это программа управления электронными таблицами, в которых элементы данных хранятся в ячейках строк и столбцов, образующих листы, а Microsoft Access представляет собой программу управления базами данных. Наиболее типичной задачей, выполняемой в Microsoft Exсel, является управление списками данных, например списками адресов или сведений о персонале или технике. В Microsoft Access же данные хранятся в таблицах, которые очень похожи на листы Microsoft Excel, но рассчитаны на выполнение сложных запросов данных, находящихся в других таблицах и хранилищах, и даже в полях других таблиц.

Microsoft Excel удобно использовать, если объем данных не слишком велик, данные представлены в простой форме, и к ним достаточно создать одну таблицы.

Microsoft Access часто используется, при работе с большим объемом информации, и если требуется выполнить сложные запросы и отчеты.

Работа с программами особых трудностей не вызвала. Учитывая небольшой объем обрабатываемой информации удобнее было работать в Excel, но Access тоже понравился. На мой взгляд, Access предоставляет больше возможностей при обработке информации: оригинальные формы и отчеты. В этой программе дольше происходит первичная обработка данных (создание БД, запросов, форм и отчетов), но после удобнее пользоваться ими за счет возможности запрашивать только то, что необходимо. Excel тоже позволяет фильтровать данные, но таблица сохраняет все столбцы, включая не нужные в данный момент.

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

...