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

     КАМСКАЯ ГОСУДАРСТВЕННАЯ  ИНЖЕНЕРНО-ЭКОНОМИЧЕСКАЯ  АКАДЕМИЯ

     Кафедра  ММИТЭ 
 
 
 

     КУРСОВАЯ  РАБОТА 

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

     На  тему «Информационно-аналитическая  система аптечного управления»

     Вариант №14 
 

Выполнил:

студент     гр. 5114Б

ФИО: Хабибуллина Алсу Абузаровна 

     Проверил:

     К.т.н., доцент

     Каляшина  А.В. 
 
 

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

     2009

Содержание:

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

2. Исходные  данные                                                                                                                   4

3. Используемые  методы обработки                                                                                  6

                        3.1 Используемые функции                                                                                                6

                        3.2 Макросы                                                                                                                               7

                        3.3 Управление данными  с помощью объекта UserForm                                     9

                                                      3.3.1 Добавление данных в таблицу                                                                          9

                                                      3.3.2 Удаление данных  из таблицы                                                                         14

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

                                            3.3.4 Фильтрация данных                                                                                             15

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

                                                      3.3.6 Сводные таблицы                                                                                                  17

4. Система меню                                                                                                                        25

5. Проверка данных                                                                                                                  26

Список  используемой литературы                                                                                    27

Приложение                                                                                                                                 28 
 
 
 
 

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

      Информационно-аналитическая  система аптечного управления.

Исходная  информация содержится в трех таблицах:

• «Лекарства» (Код лекарства, название лекарства, дата изготовления, срок годности, цена за единицу, номер аптеки, адрес).
• «Аптеки» (номер аптеки, адрес аптеки)
• «Справочник» (код лекарства, название лекарства)

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

 
 

     2. Исходные данные

      Таблица «Аптеки» состоит из следующих столбцов:

• Номер аптеки;
• Адрес аптеки;

        Столбцы «Номер аптеки», «Адрес аптеки» содержат исходную информацию и набираются вручную. Таблица имеет следующий вид (полный вид таблицы приводится в Приложении): 
 

Таблица «Лекарства» состоит из следующих столбцов:

• Код лекарства;
• Название лекарства;
• Дата изготовления;
• Срок годности;
• Цена за единицу;
• Номер аптеки;
• Адрес;

       Столбец «Название лекарства» таблицы «Лекарства» подставляется с помощью формулы ВПР и ЕСЛИ из таблицы «Справочник». Это реализовано с помощью следующей формулы: =ЕСЛИ(RC[-1]=0;" ";ВПР(RC[-1];'Справочник'!R1C1:R10C5;2)). Причем выбрать можно только те лекарства, которые существуют в таблице «Справочник». При удалении лекарства из таблицы «Справочник», удаляются все сведения для этого лекарства из таблицы «Лекарства».

       Столбец «Цена лекарства» высчитывается с помощью формулы ВПР, которая просматривает таблицу «Справочник» и по произведению цены игрушки на количество проданных единиц высчитывается нужное значение. Формула: =ЕСЛИ(RC[-3]=0;" ";ВПР(RC[-3]; 'Справочник'!R1C1:R10C5; 4)*RC[-1]). 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

       3. Используемые методы обработки

       3. 1 Используемые функции

             Функция ЕСЛИ

       Возвращает  одно значение, если заданное условие  при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

       Функция ЕСЛИ используется для условной проверки значений и формул.

       Синтаксис:

       =ЕСЛИ (RC[-3]=0; ВПР(RC[-3]; 'Справочник' !R1C1:R10C5; 4) * RC[-1]).

       Функция ВПР

       Ищет  значение в крайнем левом столбце  таблицы и возвращает значение в  той же строке из указанного столбца  таблицы. Функция ВПР используется вместо функции ГПР, когда сравниваемые значения расположены в столбце слева от искомых данных.

       Синтаксис:

       = ВПР(RC[-3]; 'Аптеки' !R1C1:R10C5; 4) 
 
 
 
 
 
 
 
 
 

       3. 2 Макросы

       Общие сведения

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

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

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

       Запись  макроса

             В меню Сервис выберите подменю Макрос и выберите команду  Запись.

       Введите имя для макроса в соответствующее поле.

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

           Чтобы выполнить  макрос с клавиатуры с помощью  сочетания клавиш, введите соответствующую  букву в поле Сочетание клавиш. Для строчных букв используется сочетание CTRL+ буква, а для заглавных — CTRL+SHIFT+ буква, где буква — любая клавиша  на клавиатуре. Буква, используемая в сочетании клавиш, не может быть цифрой или специальным символом. Заданное сочетание клавиш будет заменять любое установленное по умолчанию в Microsoft Excel, пока книга, содержащая данный макрос, открыта.

           В поле «Сохранить» в книге выберите книгу, в которой должен быть сохранен макрос.  Нажмите кнопку OK.

           Выполните макрокоманды, которые нужно записать.

     Пример макроса:

     ActiveSheet.ShowAllData

         Range("A1").Select

         Range("A1:D8"). Sort key1:=Range("A1"), order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= xlTopToBottom,   DataOption1:=xlSortNormal

           Нажмите кнопку «Остановить запись»   на соответствующей панели инструментов.

      Выполнение  макроса в Microsoft Excel

     1)  Откройте книгу , которая содержит макрос.

     2) В меню Сервис установите указатель на пункт Макрос и выберите команду Макросы.

     3)  В поле Имя макроса введите имя того макроса, который нужно выполнить.

          4) Нажмите кнопку Выполнить. Для того чтобы упростить эти действия можно назначить выполнение макроса кнопку или сочетание клавиш.

3. 3 Управление данными с помощью объекта UserForm

      Общие сведения

     Объект  UserForm представляет собой окно или диалоговое окно, являющееся составной частью интерфейса пользователя в приложении.

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

     Формы пользователя также могут реагировать  на события, инициированные пользователем или запущенные системой. Например, пользователь может написать подпрограмму обработки события Initialize (Инициализация) для UserForm, чтобы инициализировать переменные уровня модуля до того, как UserForm будет выведена на экран.

     Помимо  свойств и событий для программной обработки форм пользователя могут использоваться методы. Например, можно использовать метод Move для изменения положения и размера формы UserForm.

     При конструировании форм пользователя следует задать свойство BorderStyle для определения границ и свойство Caption для занесения текста в область заголовка. В программе можно использовать методы Show и Hide, чтобы делать форму соответственно видимой и невидимой во время выполнения.

           3. 3. 1 Добавление данных в таблицы

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

     Данные  процедуры работают на основе цикла

     Do While Not IsEmpty(текущая)<>''''

         Set следующая = текущая.Offset(1, 0)

     …………………………

        Set текущая = следующая 

     Loop,

     который работает пока не дойдет до первой пустой ячейки. При этом используются переменные объектного типа, которые используются как следующая и текущая ячейки. Метод OFFSET(i,j) используется для указания на ячейку, отдаленной на i –столбцов и на j - строк относительно данной ячейки.

     В теле цикла стоит оператор развилки IF…Then… Else… EndIF, который проверяет, нет ли такого же ключа в данной таблице (ключ – уникальное значение для строки, которое однозначно определяет строку).

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

     Для примера рассмотрим форму для  добавления в таблицу «Регистрация поступлений».

     Пример:

      Перейдите в редактор Visual Basic (Alt+F11) и постройте форму следующего вида (при создании используется элемент управления Page – что позволяет создать две вкладки «Добавить» и «Удалить».

     В этой форме использованы такие элемнеты как  Label, TextBox, CommandButton, ComboBox.

     На  рисунке показано расположение и количество всех необходимых  элементов управления.

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

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

     Private Sub CommandButton1_Click()

     Dim Y As Byte

      If TextBox1 = " " Or TextBox2 = " " Or TextBox3 = " " Or TextBox4 = " " Or TextBox5 = " " Then

         MsgBox ("Введите все данные!"), vbExclamation, "Ошибка"

        Exit Sub

      End If

      If Not IsNumeric(TextBox1.Text) Then

          Msg = MsgBox("Вводить надо числовые данные", vbCritical)

          Exit Sub

        End If

      If Not IsNumeric(TextBox3.Text) Then

          Msg = MsgBox("Вводить надо числовые данные ", vbCritical)

          Exit Sub

        End If

       If Not IsNumeric(TextBox4.Text) Then

          Msg = MsgBox("Вводить надо числовые данные ", vbCritical)

          Exit Sub

         End If

       If Not IsNumeric(TextBox5.Text) Then

          Msg = MsgBox("Вводить надо числовые данные ", vbCritical)

          Exit Sub

        End If

     Dim name As String, simvol As String

     Dim pr As Object, X As Object

     Dim Наименование As String

     Dim Цена As Integer, Возраст As Integer, Символ As Integer, Количесвто As Integer

     ActiveWorkbook.Sheets(«Регистрация поступлений").Activate

     simvol = TextBox1

     Set pr = ActiveSheet.Range("a1")

     Do While Not IsEmpty(pr)

     Set X = pr.Offset(1, 0)

     If pr = simvol Then

     Y = MsgBox("Добавление невозможно т.к. введенный код уже существует", vbOKCancel, "Код")

     If Y = vbCancel Then GoTo e Else GoTo 12

     End If

     Set pr = X

     Loop

     ActiveWorkbook.Sheets("Регистрация поступлений ").Activate

     name = TextBox2

     Set pr = ActiveSheet.Range("b2")

     Do While Not IsEmpty(pr)

     Set X = pr.Offset(1, 0)

     If pr = name Then

     Y = MsgBox("такой товар уже есть в списке внести под другим кодом?", vbYesNo + vbQuestion, "Товар")

     If Y = vbYes Or Y = vbNo Then GoTo 5 Else GoTo 12

     End If

     Set pr = X

     Loop

     5 Символ= TextBox1

       Название = TextBox2

       Возраст = TextBox3

     Цена = TextBox4

       Количесвто= TextBox5

        ActiveWorkbook.Sheets("Регистрация поступлений ").Activate

      Range("a3").Select

       Selection.EntireRow.Insert

      Set pr = ActiveSheet.Range("a2")

      Do While Not IsEmpty(pr)

        Set X = pr.Offset(1, 0)

       Set pr = X

      Loop

       pr = Символ

      pr.Offset(0, 1) = название

      pr.Offset(0, 2) = возраст

       pr.Offset(0, 3) = цена

       pr.Offset(0, 4)= количество

       Set pr = Nothing

      Set X = Nothing

      Selection.Sort key1:= Range("a1"), order1:= xlAscending, Header:= xlGuess, OrderCustom:= 1, MatchCase:= False, Orientation:= xlTopToBottom

     TextBox1.Text = ""

     TextBox2.Text = ""

     TextBox3.Text = ""

     TextBox4.Text = ""

     TextBox5.Text = ""

     12  UserForm1.Hide

     e: End Sub

      Далее вводим тексты программ для кнопки «Отмена», «Удалить» и т.п. (полный текст программ приводится в Приложении).

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

     UserForm1.Show

     При нажатии выводится  форма для редактирования данных (показана на рисунке).

     3. 3. 2  Удаление данных из таблиц

     Удаление  данных из таблиц производится с помощью  основных форм для работы с таблицами.  Форма содержит список со строками из таблиц. Необходимо выбрать из списка необходимую строку. Потом нужно нажать кнопку «Удалить».

     Формирование  списков производится в процедуре, происходящей при активизации формы  с помощью того же цикла. В теле цикла стоит оператор ADDITEM(текущая ячейка) или ADDITEM(текущая ячейка.OFFSET(0,1)).

     Удаление  производится с помощью поиска  нужного элемента в таблице и  оператора: текущая.ENTIREROW.DELETE.

     Все исходные тексты программ можно просмотреть  в приложении. На рисунке показана форма для удаления данных.

       
 
 

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

      Рассмотрим на примере формы для  работы с таблицей «Ведомость покупок». Кнопки для сортировки расположены следующим образом.

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

     Процедура выглядит следующим образом:

Range("A1").Select

   Selection.Sort key1:=Range("a2"), order1:=xlAscending, Header:=xlGuess,    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

      3.  3. 4 Фильтрация данных

     Кнопка  Фильтр запускает процедуру, которая  переносит данные из полей формы  в скрытые ячейки,(которые являются условиями отбора для расширенного фильтра таблицы Регистрация) и  выполняет команду Расширенный  фильтр.

     Процедура выглядит следующим образом:

     Range("A1:E1").Select

       Selection.AutoFilter

      Range("A2").Select

      Me.Hide 

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

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

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

      В Excel создание диаграмм производиться либо через мастер диаграмм, либо  с помощью макроса.

      Текст программы:

      Sub диаграмма()

       Charts.Add

          ActiveChart.ChartType = xlPie

        ActiveChart.SetSourceData Source:=Sheets("Итог").Range("B1:C8"), PlotBy:=  xlColumns

          ActiveChart.Location Where:= xlLocationAsObject, name:= "Диаграмма "

          With ActiveChart

              .HasTitle = True

              .ChartTitle.Characters.Text = Доля выручки по товарам"

          End With

      End Sub 

        При выполнении макроса создается  диаграмма. 
 
 

       3. 3. 6 Сводные таблицы

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

       Sub Сводная таблица()

       Sheets("Сводная таблица").Select

       Application.DisplayAlerts = False

           ActiveWindow.SelectedSheets.Delete

           Application.DisplayAlerts = True

           Sheets("Сводная таблица").Select

         ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= "Лекарства!R1C1:R41C5"). CreatePivotTable Table Destination:= "", TableName:= "Сводная таблица3", DefaultVersion:= xlPivotTableVersion10

       Полный  текст макроса приводится в приложении. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

       5. Система меню

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

• Лекарства;
• Справочник;
• Сводная таблица;
• Диаграмма;
• Аптеки;
• Сведения об авторе;
• Кнопка выхода из приложения.