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.  

 

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

47911. Біохімічне обгрунтування методики занять фізичною культурою і спортом з особами різного віку та статі 369 KB
  Перше дитинство 47 років 5. Друге дитинство 812 років хлопчики 811 років дівчинки. Підлітковий пубертатний 1316 років хлопчики вік 1215 років дівчатка. Зрілий вік: I період 2235 років чоловіки 2135 років жінки.
47912. ПОНЯТТЯ ТА ПОТЕНЦІЙНІ ЗАГРОЗИ БЕЗПЕЦІ ПІДПРИЄМНИЦЬКОЇ ДІЯЛЬНОСТІ 1.41 MB
  Провідні українські експерти визнають той факт що багаторівнева економіка потребує також різноманітних видів забезпечення економічної фізичної та інших видів безпеки та організації охорони. Це дає можливість вибору для клієнтів аналізу та порівняння різних форм охоронної діяльності і в кінцевому результаті сприяє в цілому підвищенню рівня безпеки підприємницької діяльності в Україні. В умовах збільшення частки приватного капіталу в економіці України розвитку малого та середнього бізнесу а також роздержавлення стратегічних об'єктів...
47913. Призначення випробувань, впливаючі фактори при експлуатації РЕМА і ЕОЗ 1.59 MB
  Призначення випробувань впливаючі фактори при експлуатації РЕМА і ЕОЗ. Слід виділити три групи завдань вирішуваних проведенням випробувань: здобуття емпіричних даних необхідних для проектування апаратури; встановлення відповідності виробу проектним вимогам; визначення граничного стану ЕА. Цілі випробувань не постійні: вони міняються на різних етапах проектування і виготовлення апаратури. Перерахуємо основні цілі випробувань ЕА : Експериментальне підтвердження теоретичних розрахунків прийнятих допущень і гіпотез заданих показників якості...
47914. Сучасний стан охорони праці в Україні. Мета та завдання дисципліни Основи охорони праці 19.12 MB
  Виявити мету та завдання дисципліни Основи охорони праціâ€œ. Визначити правові та організаційні питання охорони праці. Сучасний стан охорони праці в Україні.
47916. Звітність підприємства. Методичні вказівки 334 KB
  Звіт про рух грошових коштів Тема 5 Звіт про власний капітал Тема 6. Фінансова бухгалтерська звітність документи які містять інформацію про фінансовий стан результати діяльності про рух грошових коштів підприємства за звітний період і тому подібне. № 2 Звіт про рух грошових коштів ф. № 4 представляється за станом на 31 грудня Звіт про рух грошових коштів ф.
47917. СУТЬ І ЗМІСТ МАРКЕТИНГУ 1.29 MB
  За ним маркетинг це: спрямування ділового мислення інтегрована орієнтована на споживача і кінцевий результат діяльність фірми довгостроковий максимальний прибуток від реалізації її продукції філософія ділової активності бізнесу в умовах ринкових відносин і конкуренції; оснований на точному знанні передбаченні та врахуванні вимог ринку і побажань споживачів комплекс науково обгрунтованих уявлень про управління виробничозбутовою діяльністю фірми в умовах ринкових відносин; Більш конкретним є функціональний підхід до розуміння...
47918. Мікроекономіка. Конспект лекцій 1.22 MB
  Особливості споживчого попиту. Попит закон попиту чинники що впливають на попит. Ринкова рівновага цін попиту і пропозиції. Поняття і зміна цінової еластичності попиту.
47919. Кінематика матеріальної точки. Основні поняття і способи задання руху 4.42 MB
  Основні поняття і способи задання руху Теоретичне ядро Вступ в курс класичної механіки. Природничі науки: хімія біологія астрономія географія вивчають різні але конкретні явища та процеси живої і неживої природи а також форми матерії та закони її руху. Таким чином предметом фізики є вивчення найбільш загальних і найпростіших властивостей і форм руху матерії. Проте цим не вичерпуються наші уявлення про типи та форми існування матерії як відомо матерія перебуває у вічному русі що означає: а світ що оточує нас за своєю природою...