Создание информационной системы средствами МС Excel и VBA

                                                                                                                                                                                                     

ФЕДЕРАЛЬНОЕ АГЕНТСТВО  ПО ОБРАЗОВАНИЮ 

ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

 

Камская Государственная Инженерно-Экономическая Академия

 

 

Кафедра «ПИУ»

 

 

Курсовая  работа

 

по дисциплине «Информатика»

на тему «Создание  информационной системы средствами МС Excel и VBA

(База данных «Сведения о багаже»)

 

 

 

 

 

 

                       Выполнил: студент гр. 2119

                       Нигматуллина В. Д.

                       № зачётной кн. 2081917

 

                                                                  Проверил: ст. преподаватель   

                                                       Хасанова Ф. С.        

 

 

 

Набережные Челны

2009

 

Содержание

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Введение

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

 

 

 

 

 

 

 

 

 

 

 

 

Задание

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

  Выяснить, имеется ли пассажир, багаж которого состоит из  одной вещи весом менее 30 кг. Найти максимальный и минимальный вес багажа среди багажей всех пассажиров. Вычислить общий вес багажа пассажиров одного из рейсов. Найти пассажира, чей вес багажа равен 23 кг. На основании веса всех багажей, вычислить средний вес багажа. Вычислить среди всех рейсов количество билетов с номером 1 .Вычислить общий вес и общее количество вещей багажа каждого рейса. Найти пассажиров, чей вес багажа более 20кг. и рассчитать, внесённую ими доплату. Создать сводную таблицу. Упорядочить информацию. Построить диаграмму.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Титульный лист

При открытии документа «Курсовая  работа» перед нами выходит титульный  лист, который, представляет своеобразное меню данной курсовой работы (рис.1):

Рис.1

Для каждой из кнопок записан макрос для автоматического перехода на нужный лист.

Например для кнопки «Расширенный фильтр» :

Sub врасширфильтр()

'

' врасширфильтр Макрос

' Макрос записан 10.05.2009 (Венера)

'

 

'

    Sheets("Расширенный фильтр").Select

    ActiveWindow.SmallScroll Down:=-36

End Sub

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

Разработка форм

Форма — это способ представления  данных из таблицы, когда на экране представлено содержимое только одной записи.

С помощью формы можно:

заносить данные в таблицу;

просматривать или корректировать данные;

удалять данные;

отбирать записи по критерию.

Вставка записей  с помощью формы

1. Укажите ячейку списка, начиная с которой следует добавлять записи.
2. Выберите команду Форма в меню Данные.
3. Нажмите кнопку Добавить.
4. Введите поля новой записи, используя клавишу TAB для перемещения к следующему полю. Для перемещения к предыдущему полю используйте сочетание клавиш SHIFT+TAB.

Чтобы добавить запись в список, нажмите клавишу ENTER. По завершении набора последней  записи нажмите кнопку Закрыть, чтобы добавить набранную запись и выйти из формы.

Если поле списка содержит формулу, то в форме выводится  ее результат. Изменять это поле в  форме нельзя. При добавлении записи, содержащей формулу, результат формулы не будет вычислен до нажатия клавиши ENTER или кнопки Закрыть. Чтобы отменить добавление записи, нажмите кнопку Вернуть перед нажатием клавиши ENTER или кнопки Закрыть. Microsoft Excel автоматически добавляет запись при переходе к другой записи или закрытии формы.

Поиск записей в списке с помощью  формы

Для перемещения  на одну запись нажмите на стрелки  полосы прокрутки в диалоговом окне. Чтобы переместиться на 10 записей, нажмите полосу прокрутки между стрелками.

Нажмите кнопку Далее для перехода к следующей  записи списка и Назад — для  перехода к предыдущей.

Чтобы задать условия  поиска или условия сравнения, нажмите  кнопку Критерии. Введите критерии в форме. Чтобы найти совпадающие  с критериями записи, нажмите кнопки Далее или Назад. Чтобы вернуться  к правке формы, нажмите кнопку Правка.

Запишем макрос к кнопке «Вызов формы»:

Sub примененформы()

'

' примененформы  Макрос

' Макрос записан  21.05.2009 (Венера)

'

'

    ActiveWindow.ScrollWorkbookTabs Position:=xlLast

    Sheets("Применение формы").Select

    ActiveWindow.SmallScroll Down:=-27

End Sub

Теперь, нажимая на кнопку «Вызов формы» на лист выводится следующие окно (рис.2):

    Рис.2

Теперь, выбрав одно из действий мы сможем изменить нашу базу данных (добавить, или удалить  строку). На рисунке 3 изображена БД

Рис.3

 

 

 

Сортировка данных

Чтобы выполнить сортировку списка, нужно выделить соответствующий диапазон ячеек, выбрать команду Данные/Сортировка. В появившемся окне (рис.4) задаются ключи сортировки, порядок сортировки. Всего можно задать 3 ключа сортировки. Порядок сортировки может быть «По возрастанию» и «По убыванию».

 Рис.4

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

1. с помощью команды Сервис/Параметры вызвать окно Параметры
2. перейти на вкладку Списки
3. в поле Элементы списка ввести элементы, разделяя их нажатием клавиши Enter
4. после ввода всех элементов нажать кнопку Добавить и закрыть окно Параметры

 Рис.5

Теперь, мы имеем собственные параметры для сортировки в особом порядке по рейсам.(рис.6)

  Рис. 6

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

Sub сортировка()

'

' сортировка Макрос

' Макрос записан 08.05.2009 (Венера)

'

 

'

    ActiveWindow.SmallScroll Down:=-30

    Range("A1:H36").Select

    Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range("H2") _

        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=8, MatchCase:= _

        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _

        :=xlSortNormal

    ActiveWindow.SmallScroll Down:=-6

End Sub

А при нажатии на кнопку «База  данных» выведется первоначальная БД :

Sub базасортировка()

'

' базасортировка Макрос

' Макрос записан 08.05.2009 (Венера)

'

 

'

    ActiveWindow.SmallScroll Down:=-33

    Range("A1:H36").Select

    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _

        OrderCustom:=8, MatchCase:=False, Orientation:=xlTopToBottom, _

        DataOption1:=xlSortNormal

End Sub

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Автофильтр и функция ВПР

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

Чтобы включить автофильтр, нужно  воспользоваться командой Данные/Фильтр/Автофильтр. Excel выведет кнопки со стрелками (кнопки авто фильтра) рядом с каждым заголовком столбца.(рис. 7)

  Рис.7

При помощи автофильтра мне следует  найти пассажирей чей вес багажа более 20кг. Для этого произведем фильтрацию по столбцу «Вес багажа». Выберем пункт «Условие»,перед нами появится окно (рис.8) выберем соответствующие условие.

    Рис. 8    

При выполнении всех этих действии следует запустить автоматическую запись макроса для кнопки «Автофильтр»

Sub автофильтр()

'

' автофильтр Макрос

' Макрос записан 17.05.2009 (Венера)

'

 

'

    ActiveWindow.SmallScroll Down:=-36

    Range("A1:H36").Select

    Selection.AutoFilter

    Selection.AutoFilter Field:=7, Criteria1:=">20", Operator:=xlAnd

    Range("C40").Select

End Sub

 Рис.9

При помощи автофильтра я нашла  пассажиров, которым предстоит внести доплату, за превышение веса багажа(рис.9). Для багажа каждого пассажира будет оформлена следующая квитанция (рис.10):

  Рис. 10

Создам таблицу доплата(рис.11):

Пассажир	Превышение(кг.)	Доплата

Рис.11

Куда введем фамилии пассажиров, чей багаж более 20 кг. Рассчитаем количество превышающих килограмм багажа каждого из пассажиров: =G5-C58   где G5-ячейка веса багажа(одного из пассажиров), а ячейка C58=20 (соответственно). Рассчитаем доплату из расчета, что 1кг. Превышения веса равен 100 руб. Воспользуемся функцией ВПР( ) (вертикальное поисковое решение)(рис.12). Применяя эту функцию мы должны ответить на след. вопросы:

• что ищем
• где осуществляется поиск
• что является результатом поиска
• как точно нужно искать

Рис.12

=ВПР(G50;$G$49:$H$52;2;ЛОЖЬ)*H46   домножив формулу функции ВПР() на ячейку,где указана сумма доплаты за 1 кг., получаем в руб. доплату которую необходимо будет ввести пассажирам. (рис. 13)

  Рис. 13

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

Sub доплата()

'

' доплата Макрос

' Макрос записан 17.05.2009 (Венера)

'

 

'

    Range("G49").Select

    ActiveCell.FormulaR1C1 = "Нуреева Р. Р."

    Range("G50").Select

    ActiveCell.FormulaR1C1 = "Голубина С. В."

    Range("G51").Select

    ActiveCell.FormulaR1C1 = "Курбанаев М. Х."

    Range("G52").Select

    ActiveCell.FormulaR1C1 = "Бочкарёв Д. Р."

    Range("H49").Select

    ActiveCell.FormulaR1C1 = "=R[-44]C[-1]-R[9]C[-5]"

    Range("H50").Select

    ActiveCell.FormulaR1C1 = "=R[-40]C[-1]-R[8]C[-5]"

    Range("H51").Select

    ActiveCell.FormulaR1C1 = "=R[-21]C[-1]-R[7]C[-5]"

    Range("H52").Select

    ActiveCell.FormulaR1C1 = "=R[-20]C[-1]-R[6]C[-5]"

    Range("I49").Select

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],R49C7:R52C8,2,FALSE)"

    Selection.FillDown

    Range("I49").Select

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],R49C7:R52C8,2,FALSE)*R[-3]C[-1]"

    Range("I50").Select

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],R49C7:R52C8,2,FALSE)*R[-4]C[-1]"

    Range("I51").Select

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],R49C7:R52C8,2,FALSE)*R[-5]C[-1]"

    Range("I52").Select

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],R49C7:R52C8,2,FALSE)*R[-6]C[-1]"

    Range("I53").Select

      End Sub

Для сброса всех действий на этом листе  создала кнопку «Сброс» :

Sub сбросдоплата()

'

' сбросдоплата Макрос

' Макрос записан 17.05.2009 (Венера)

'

 

'

    Range("G49:I52").Select

    Selection.ClearContents

    ActiveWindow.SmallScroll Down:=-12

    Selection.AutoFilter

    ActiveWindow.SmallScroll Down:=18

    Range("F41").Select

End Sub

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Расширенный фильтр

При помощи расширенного фильтра необходимо выяснить, имеется ли пассажир(ы), багаж  котор(ого)(ых) состоит из одной вещи весом менее 30кг.- представим это  условие, как диапазон условий расширенного фильтра (рис.14)

Рис. 14

Расширенный фильтр является более  гибким средством отбора записей  из БД, чем Автофильтр и позволяет  задавать:

• условия, соединенные логическим оператором ИЛИ, для нескольких столбцов
• три и более условий для конкретного столбца
• вычисляемые условия

чтобы воспользоваться расширенным  фильтром, нужно выбрать команду  меню Данные/Фильтр/Расширенный фильтр. На экране появится диалоговое окно (рис.15):

 Рис.15

После нажатия на кнопку ОК, на рабочем листе, увидим пассажиров, которые соответствуют выше указанным условиям (рис.16):

Рис.16

Макрос для кнопки «Условие»:

Sub условиерасшир()

'

' условиерасшир Макрос

' Макрос записан 10.05.2009 (Венера)