50298

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

Курсовая

Информатика, кибернетика и программирование

Создание базы данных БД. Система управления базами данных CCESS EXCEL позволяет работать с базами данных.

Русский

2014-01-20

726 KB

16 чел.

Министерство Путей Сообщения Российской Федерации

Петербургский Государственный Университет Путей Сообщения

Кафедра ”Информатика и информационные технологии”

Пояснительная записка

к курсовой работе

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

 MS Excel и MS Access »

Вариант №9

    Выполнил: 

Санкт-Петербург

2005
Оглавление

[1] 2005
Оглавление

[1.1] 1.1. Основные положения.

[1.2] СУММ(B3:B12), что позволяет подсчитать общую протяженность судоходной части исходя из того, какая протяженность каждой из имеющихся рек в отдельности.

[1.3] 1.4. Создание базы данных (БД).

[2] 2.Система управления базами данных ACCESS

[3] 2.1. Общие положения.

[3.1] 2.2. Разработка структуры БД.

[3.2] 2.3. Создание таблиц в режиме конструктора.

[3.3] 2.4. Заполнение таблиц данными (кроме вычисляемого поля).

[3.4] 2.5. Запросы на обновление

[3.5] 2.6. Запросы на выборку. Параметрические запросы.

[3.6] 2.7. Создание и редактирование форм

[3.7] 2.8. Создание и редактирование отчетов.

[4] 3. Список использованной литературы

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

1.1. Основные положения.

EXCEL позволяет работать с базами данных.Основное назначение EXCEL-работа с таблицами, содержащими формулы. Для обозначения объекта, представляющего электронную таблицу, в EXCEL принят термин рабочий лист. Рабочий лист-это множество ячеек, каждая из которых принадлежит некоторому столбцу и некоторой строке. Строки и столбцы идентифицируются, т.е. столбцы именуются, а строки нумеруются. По умолчанию рабочий лист имеет 256 столбцов с именами от А до IV и 65536 строки. Рабочий лист представлен на рисунке 1.

рис. 1

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

Спроектировать базу данных в  EXCEL,содержащую следующие данные.

Входные данные:

- Название реки;

- Протяженность судоходной части;

- Глубина фарватера;

- Количество месяцев навигации  в году;

- Название водоема, куда впадает река;

           - Название области, где река имеет наибольшую протяженность;

Выходные данные:

- Общая протяженность рек;

- Формирование списка рек, протяжённостью которых не ниже запрашиваемой величины

– Формирование списка рек, впадающих в выбранный пользователем водоём

Должны выполняться следующие функции:

1. Заполнение и редактирование таблиц базы данных.

2. Формирование списка рек, протяжённостью которых не ниже запрашиваемой величины.

3. Формирование списка рек, впадающих в выбранный пользователем водоём.

4. Подсчет общей протяженности рек по областям.

5. Формирование отчета, включающего: название реки,  число месяцев навигации  и глубину фарватера с указанием реки, имеющей наименьшее количество месяцев навигации в году.

.

1.3. Математическое описание задачи.

СУММ(B3:B12), что позволяет подсчитать общую протяженность судоходной части исходя из того, какая протяженность каждой из имеющихся рек в отдельности.

1.4. Создание базы данных (БД).

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

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

Сначала задаются имена полей БД, затем вводим данные в остальные ячейки. Вычисляемые поля заполняются в последнюю очередь. Заголовок таблицы целесообразно формировать с помощью надписи.

Ввод формул.

Любая формула начинается со знака =. Формулы могут содержать:

- числа и относительные ссылки =2*А2

- абсолютные ссылки =A3*$B$6

- функции = СУММ(A1:B5)

- имена ячеек = Стоимость * Льготы

Копировать ячейки можно несколькими способами:

  1.  Методом заполнения.

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

  1.   С помощью буфера обмена

   - Выделить диапазон копируемых ячеек

   - Правка- копировать

- Правка- вставить(в ряде случаев также надо выделить тот диапазон ячеек, куда копируем).

Процесс заполнения БД с помощью формы.

  1.  создать заголовок таблицы Главная.
  2.  задать имена полей БД.
  3.  выделить ячейки A2:F2 (заголовки) и выполнить команду Данные - Форма. После этого появится форма (рис 2). Теперь надо ввести данные во все ячейки, кроме вычисляемого поля, переход осуществляется с помощью кнопки(tab) или мышью. Заполнив первую запись нажать на кнопку далее, также и с остальными записями.
  4.  закончив вводить данные, щелкнуть на кнопке закрыть.
  5.  ввести в строку «общая стоимость» формулу.

Форма приведена на рисунке 2

рис.2

                   

Заполненная таблица приведена на рис. 3

рис. 3

Название реки

Протяжённость судоходной части

Глубина фарватера

Количество месяцев навигации в году

Название водоёма, куда река впадает

Название области, где река имеет наибольшую протяженность

Волга

3000

150

6

Каспийское

Европейская равнина

Амур

5000

200

10

Японское

Дальний Восток

Обь

4200

190

5

Море Лаптевых

Восточная Сибирь

Лена

4500

120

6

Берингово

Западная Сибирь

Енисей

3800

130

6

Карское

Западная Сибирь

Урал

1200

70

5

Малые озера

Урал

Уренгой

700

80

8

Японское

Дальний Восток

Грязная

500

30

3

Карское

Западная Сибирь

Вонючая

30

10

3

Берингово

Западная Сибирь

Нева

250

40

11

Финский залив

Северо-запад

Итого:

23180

Для отображения формул вместо результатов надо выполнить команду  Сервис - Параметры и на вкладке Вид поставить флажок около параметра Формула. Таблица с формулами приведена на рисунке 4.

рис. 4

Название реки

Протяжённость судоходной части

Глубина фарватера

Количество месяцев навигации в году

Название водоёма, куда река впадает

Название области, где река имеет наибольшую протяженность

Волга

3000

150

6

Каспийское

Европейская равнина

Амур

5000

200

10

Японское

Дальний Восток

Обь

4200

190

5

Море Лаптевых

Восточная Сибирь

Лена

4500

120

6

Берингово

Западная Сибирь

Енисей

3800

130

6

Карское

Западная Сибирь

Урал

1200

70

5

Малые озера

Урал

Уренгой

700

80

8

Японское

Дальний Восток

Грязная

500

30

3

Карское

Западная Сибирь

Вонючая

30

10

3

Берингово

Западная Сибирь

Нева

250

40

11

Финский залив

Северо-запад

Итого:

=СУММ(B2:B12)

1.5. Поиск данных.

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

Рассмотрим поиск данных на примере поиска рек, протяженность судоходной части которых, удовлетворяет запрашиваемой величине. Для этого надо раскрыть список поля “Протяженность судоходной части” и выбрать команду (Условие…).

Результат выполнения данного примера расположен на рисунке 5.

рис. 5

                                                                                                                                 рис.6

Волга

3000

150

6

Каспийское

Европейская равнина

Амур

5000

200

10

Японское

Дальний Восток

Обь

4200

190

5

Море Лаптевых

Восточная Сибирь

Лена

4500

120

6

Берингово

Западная Сибирь

Енисей

3800

130

6

Карское

Западная Сибирь

23180

1.6 Использование функций для получения итоговых значений.

Для подсчета общей протяженности судоходной части рек используется функция СУММ. Для этого табличный курсор устанавливаем в ячейку В14. Затем выполняю команду Вставка – Функция и выбираем функцию СЧЕТЕСЛИ, методом протаскивания выделяем требуемый диапазон ячеек ( E3:E16 ).

                                                                                                                                  рис.8

1.7. Создание макросов.

Макрос - это программа, записанная на встроенном в EXCEL языке VISUAL BASIC for application (VBA).  Макрос может быть написан программистом или создан автоматически макрорекордером. Макрос, созданный этим макрорекордером, запоминает с момента его записи все действия пользователя, в том числе и ошибочные.

Рассмотрим создание макроса на примере поиска рек, протяженность судоходной части которых удовлетворяют запрашиваемой величине. Для  этого надо:

- табличный курсор поместить внутрь таблицы

- выполнить команду Сервис- Макрос - Начать запись

- в окне диалога «Запись макроса» (рис.9) задать имя макроса, в данном случае «Река».

рис.9

- после нажатия Ok макрорекордер начинает запись макроса и записывает до тех пор, пока не будет выполнена команда  Сервис - макрос - Остановить запись.

- Выполнить действия, которые записывает макрос

- Остановить запись макроса.

Запуск макроса.

- Восстановить исходное состояние таблицы путем снятия автофильтра

- Убедиться, что табличный курсор находится внутри таблицы

- Выполнить команду Сервис- Макрос- Макросы, выделить требуемый макрос и щелкнуть на кнопке “ Выполнить ”

рис10

Типичный командный макрос следующие элементы:

- Операторы sub и end sub располагаются в конце и начале макроса

- Имя макроса следует после оператора sub

- Тело макроса, то есть часть макроса, заключенная между операторами sub и end sub ,оно состоит из последовательности операторов, каждый из которых соответствует выполненному во время записи макроса действию.

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

- Команда Сервис - Макрос - Начать запись

- Присвоить макросу имя

- Команда Данные - Фильтр – Отобразить все

- Остановить запись макроса

1.8. Назначение макросов объектам,

Для назначения макроса конкретному объекту объект помещается на лист.

Выделяется правой клавишей мыши и из контекстного меню выбирается команда Назначить макрос.

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

- Выполняется команда Вид – Панели инструментов – Формы, на экране появится панель инструментов.

- Щелкнуть на элементе кнопка и методом протаскивания сформировать кнопку на листе.

- На экране появится окно назначить макрос объекту, в котором надо выделить требуемый макрос и нажать ОК.

- Пока кнопка выделена на листе, можно изменить ее имя, а затем щелкнуть вне кнопки.

1.9. Построение диаграмм.

Для построения диаграммы надо проделать следующие действия:

-  Выделить диапазон ячеек с данными, необходимыми для построения диаграммы

– Щелкнуть на кнопке Мастер диаграмм или выполнить команду вставка – диаграмма.

Существует 4 шага мастера диаграмм:

1. выбирается тип диаграмм (круговая ) и ее вид.

2. выбирается диапазон данных.

3. выбираются различные параметры оформления диаграммы: заголовки, легенда (добавить легенду, справа); подписи данных (доля).

4.определяется место расположения диаграммы.

Построенная диаграмма приведена на рис.11

рис.11

2.Система управления базами данных ACCESS

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

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

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

Реляционная база данных представляет собой множество взаимосвязанных двухмерных таблиц- реляционных таблиц, называемых также отношениями, в каждой из которых содержатся сведения об одной сущности автоматизируемой предметной области.

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

База данных ACCESS включает в себя следующие объекты:

- Таблицы, запросы, схемы данных, имеющие непосредственное отношение к базе данных;

- Формы, отчеты, страницы доступа к данным, макросы и модули, называемые объектами приложения.

Базы данных ACCESS работают со следующими типами данных:

- Текстовый - тип данных, используемый для обычного неформатированного текста ограниченного размера(до 255 символов)

- Поле мемо - специальный тип данных для хранения больших объемов текста(до 65535) символов

- Дата/время-тип данных для хранения календарных дат и текущего времени

- Числовой - тип данных для хранения действительных чисел

- Денежный - для хранения денежных сумм

- Счетчик- специальный тип данных для уникальных натуральных чисел с автоматическим наращиванием

- Логический.

2.2. Разработка структуры БД.

Необходимо создать таблицу. В ней будут размещены следующие поля:

- Название реки;

- Протяженность судоходной части;

- Глубина фарватера;

- Количество месяцев навигации  в году;

- Название водоема, куда впадает река;

           - Название области, где река имеет наибольшую протяженность;

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

Для создания новой таблицы в окне базы данных надо выбрать объект Таблицы и нажать кнопку Создать. В открывшемся окне Новая таблица выбирается Конструктор.

После этого появляется окно Таблица 1:таблица, в которой определяется структура таблицы базы данных.

Для определения поля в окне Таблица задаются Имя поля, тип данных, Описание –краткий комментарий. А также свойства поля в разделе Свойства поля.

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

                                        

Рис. 12   

.                                                                        

2.4. Заполнение таблиц данными (кроме вычисляемого поля).

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

Если при заполнении таблицы  была изменена ее структура, то при закрытии ACCESS потребует подтверждения изменений.

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

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

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

В задании необходимо обновить записи поля “Протяженность судоходной части”.

Чтобы создать запрос на обновление, надо сначала создать запрос на выборку, который затем преобразуется в окне конструктора запросов в запрос на обновление при выборе команды Обновление контекстного меню, появляющегося при нажатии кнопки Тип запроса на панели инструментов конструктора запросов, или команды меню Запрос->Обновление.

В ячейку строки Обновление вводится формула: [Новая протяженность] и выполняется команда Запрос->Запуск. После этого  ACCESS предупредит, что будут обновлены значения какого-то количества записей. Результаты изменения  данных можно увидеть в таблице, выбранной в качестве источника записей. При добавлении данных  в таблицу надо каждый раз открывать запрос.

2.6. Запросы на выборку. Параметрические запросы.

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

Рассмотрим принцип формирования запроса на примере таблицы “Дата оплаты”.

Для этого:

- В окне базы данных активизировать объект Запросы и нажать Создать

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

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

- В строку условие отбора в ячейку поля Дата оплаты вводится значение >1200

- Закрыть запрос, сохранить его, задать имя «Запрос2».

- При открытии запроса в окне базы данных на экран выводится результирующая таблица запроса (рис.14)

рис.14

Параметрический запрос является частным случаем запроса на выборку.

Пользователь может вводить значение поля в диалоговом режиме в процессе выполнения запроса.

Имя параметра может задаваться непосредственно в поле Условие отбора в квадратных скобках. При выполнении запроса это имя появится в диалоговом окне Введите значение параметра (рис.16)

Рассмотрим создание параметрического запроса Необходимо выбрать телефоны, которые оплачиваются по льготному тарифу (рис.17). Назовем этот запрос «Льготы».

рис.15

рис.16

рис.17

2.7. Создание и редактирование форм

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

Для создания формы в окне базы данных в области Объекты выбирается закладка Формы, после этого надо нажать на кнопку Создать. В диалоговом окне Новая форма (рис. 18) выбирается режим Автоформа: в столбец.

Рис.18

На экране появится форма, на которой отображены сведения о названии рек.  (рис.19).

Рис.19

После этого форма сохраняется и ей присваивается имя.

Для создания ленточной формы в окне Новая форма выбирается режим Автоформа: ленточная. В качестве источника данных можно взять таблицу или запрос.

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

2.8. Создание и редактирование отчетов.

Отчеты служат для форматированного ввода данных на печатающие устройства.

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

Средства автоматического проектирования реализованы автоотчетами. Существуют «ленточные» автоотчеты и автоотчеты «в столбец». Для создания такого отчета в окне База данных надо выполнить команду “Создать” и в открывшемся окне выбрать из списка нужный тип отчета.

Редактирование структуры отчета выполняется в режиме Конструктора. Приемы редактирования такие же, что и для форм.

С помощью мастера отчетов я создал отчет на основании таблицы Льготы. Отчет приведен на рисунке 22

Рис.22

3. Список использованной литературы

  1.  Хомоненко А.Д. самоучитель MICROSOFT WORD 2000.- СПб,2002-.560 стр.
  2.  Долженков В., Ю.Колесников. Справочник MICROSOFT ACCESS- СПб,1999.-480 стр.
  3.  Долженков В., Ю.Колесников. Самоучитель  MICROSOFT Excel 2000- СПб,1999.-356 стр.

8

  1.  

 

А также другие работы, которые могут Вас заинтересовать

4707. Вивчення основ програмування на мові Python. Регулярні вирази для обробки текстів 256.5 KB
  Вивчення основ програмування на мові Python. Використання регулярних виразів для обробки текстів Короткі теоретичні відомості Синтаксис регулярних висловів залежить від інтерпретатора, що використовується для їх обробки. Пошук слів із за...
4708. Ознайомлення з основними типами даних в Python, вивчення основ програмування на мові Pytho 44.5 KB
  Мета роботи:ознайомлення з основними типами даних в Python, вивчення основ програмування на мові Python. Короткі теоретичні відомості Python - це проста і потужна об’єктно-орієнтована мова програмування високого рівня з чудовими можливост...
4709. Вивчення бібліотеки прикладних програм nltk, для опрацювання текстів природною мовою 21.61 KB
  Мета роботи: вивчення основ програмування на мові Python. Короткі теоретичні відомості Оператор for в Python трохи відрізняється від аналогічного оператора в C або Pascal. Замість незмінного проходження по арифметичній прогресії з чисел (як в Pascal...
4710. Вивчення методів доступу та роботи з лексичним ресурсами 261 KB
  Мета роботи: Вивчення основ програмування на мові Python. Вивчення методів доступу та роботи з лексичним ресурсами. Семантичний словник англійської мови WordNet. Короткі теоретичні відомості При програмуванні часто необхідно частин...
4711. Вивчення методів роботи з файлами на локальних дисках та з інтернету. Використання Юнікоду при обробці текстів 178.5 KB
  Мета роботи: Вивчення основ програмування на мові Python. Вивчення методів роботи з файлами на локальних дисках та з Інтернету. Використання Юнікоду при обробці текстів. Нормалізація текстів, стемінг, лематизація та сегментац...
4712. Вивчення основ структурного програмування мовою Python 72 KB
  Мета робота Вивчення основ програмування на мові Python. Вивчення основ структурного програмування мовою Python. Повторення та закріплення знань отриманих при виконанні попередніх лабораторних робіт. Покращення загальних нав...
4713. Разработка и сопровождение многофункционального персонального сайта преподавателя 109.5 KB
  Введение Работа в сети Internet дает неограниченные возможности в получении необходимой информации, например, для учебного процесса. Разработанный персональный сайт преподавателя дает возможность получить студенту такую информацию в виде...
4714. Основи структурного програмування мовою Python 127.5 KB
  Мета робота Вивчити основи структурного програмування мовою Python, повторити та закріпити матеріал, пройдений у попередніх лабораторних роботах, покращити загальні навички програмування. Короткі теоретичні відомості Присвоювання...