Федеральное агентство по образованию
Южно-Уральский Государственный университет
Кафедра Информатика
Пояснительная записка к курсовой работе по дисциплине
«Система автоматизации экономических расчётов»
на тему «Почта»
Выполнил:
студент II курса факультета
Экономики и Управления
группы ЭиУ-264
Кадученко Вадим Дмитриевич
Проверил:
Горных Е.Н.
Челябинск 2007
Аннотация
Разработана программа
автоматизации учёта работы почты. Автоматизирован учёт полученной, отправленной
корреспонденции, выдача корреспонденции, печать квитанций об оплате и извещений
для получателей. Программа предоставляет суммарные отчёты полученной и
отправленной корреспонденции, а также сопроводительные ведомости для полученной
и отправленной корреспонденции за выбранный день.
Содержание
Аннотация. 2
Содержание. 3
Введение. 4
Основная часть. 5
Постановка задачи. 5
Описание исходных данных. 6
Описание структуры приложения. 9
Описание форм. 10
Описание отчётов. 15
Заключение. 17
Список использованной литературы.. 18
Введение
Программа Microsoft Excel, входящая в пакет офисных программ Microsoft Office, представляет собой мощную систему разработки систем
автоматизации экономических расчётов. Microsoft Excel представляет
возможность как простой записи последовательности действий с помощью макросов,
так и создания сложных алгоритмов и полноценных приложений на его основе с
помощью встроенного языка программирования Visual Basic for Applications. Большое количество встроенных
функций и процедур значительно упрощает процесс создания подобных приложений. Эти
возможности Microsoft Excel были широко использованы в ходе данной работы.
Основная часть
Постановка задачи.
Используя средства Excel по созданию окон диалога и написание
макросов на VB, создать приложение,
автоматизирующее учет отправленной и полученной корреспонденции на почте. Приложение
должно выполнять следующие функции:
-
Регистрировать
отправленную корреспонденцию и печатать квитанцию об оплате.
-
Регистрировать
полученную корреспонденцию и ее выдачу.
-
Печатать
извещение для получателя корреспонденции
-
Рассчитывать
стоимость отправки корреспонденции в зависимости от пункта назначения.
-
Составлять отчет
по направлениям о стоимости и весе корреспонденции.
-
Формировать
сопроводительную ведомость.
Описание исходных данных
Исходными данными для
приложения являются 5 таблиц. 3 таблицы содержат стоимости отправки посылок,
бандеролей и заказных писем (за 1 кг) по направлениям и выглядят следующим
образом (см. рис. 1):
Рисунок 1 – Таблица
стоимостей отправки посылок
На рисунке представлена
таблица стоимостей отправки посылок, соответствующие таблицы для бандеролей и
заказных писем выглядят аналогично, поэтому приводить их в виде рисунков нет
необходимости. Данные, занесённые в таблицы стоимостей, являются неизменяемыми.
Данные из таблицы стоимостей используются при автоматическом расчёте стоимости
отправки корреспонденции.
Следующие 2 таблицы
являются изменямыми и заполняются последовательно при работе с приложением. Эти
таблицы содержат информацию об отправленной и полученной корреспонденции.
Рисунок 2 – Таблица
отправленной корреспонденции
Таблица отправленной
корреспонденции содержит следующую информацию: порядковый номер
корреспонденции, дату отправки, вид корреспонденции (посылка, бандероль или
заказное письмо), пункт назначения, ФИО и точный адрес отправителя, ФИО и
точный адрес получателя, вес, стоимость отправки. Таблица полученной
корреспонденции содержит полностью аналогичные столбцы плюс 1 дополнительный
столбец «Выдано», содержащий значение «ВЫДАНО», если корреспонденция выдана
получателю, и пустую ячейку в противном случае.
Рисунок 3 – Часть таблицы
«Полученная корреспонденция»
К исходным данным также
можно отнести два бланка квитанций, ячейки которых заполняются нужными
значениями. На рисунке 4 представлен образец извещения, доставляемого на дом
получателю, если на его имя получена корреспонденция.
Рисунок 4 – Образец
извещения, формируемого программой
На
рисунке 5 представлен образец квитанции об оплате отправки корреспонденции.
Данная квитанция выдаётся на руки отправителю.
Рисунок 5 – Образец
квитанции об оплате
Описание структуры приложения
Приложение содержит 2
формы: основную, «Почта», и вспомогательную «Справка». Схема подчинения форм
представлена в приложении А. Форма «Почта» содержит необходимые элементы для
ввода данных об отправленной и полученной корреспонденции, которые заносятся в
таблицы «Отправленная корреспонденция» и «Полученная корреспонденция»
соответственно. На этапе ввода данных предусмотрено формирование извещения,
квитанции об оплате отправки (при условии корректности введённых данных).
Помимо этого, на форме «Почта» содержится список полученной корреспонденции,
ещё не выданной на руки получателям. Вкладка «Отчёты» формы «Почта» содержит 4
кнопки, каждая из которых вызывает соответствующий отчёт: отчёт по направлениям
для отправленной либо полученной корреспонденции, а также сопроводительную
ведомость для отправленной либо полученной корреспонденции.
При запуске рабочей книги
приложение Excel становится скрытым, на экране
отображается основная форма «Почта». Таблицы исходных данных недоступны
пользователю для просмотра и редактирования. При формировании отчёта становится
видимым соответствующий лист рабочей книги Excel, на котором расположен этот отчёт. Для продолжения
ввода данных либо формирования другого отчёта пользователь должен вернуться на
форму, для чего предусмотрена кнопка «Вернуться на форму» на дополнительной
панели инструментов.
При завершении работы с
программой все данные, содержащиеся в рабочей книге (в том числе последние
отчёты) сохраняются.
При разработке приложения
использован официальный логотип фирмы «Почта России».
Описание форм
Форма «Почта» содержит 4
вкладки «Отправка», «Получение», «Выдача», «Таблицы и отчёты».
Рисунок 6 – Вкладка
«Отправка» формы «Почта»
Вкладка
«Отправка» (см. рис. 6) содержит следующие элементы: TextBox «Дата», 2 компонента ComboBox для выбора вида корреспонденции (посылка, бандероль или
заказное письмо) и пункта назначения, 4 компонента TextBox для ввода ФИО и адреса получателя и отправителя,
отдельный TextBox для ввода веса корреспонденции. При
изменении вида корреспонденции, пункта назначения либо веса (обрабатываются
события OnChange соответствующих компонент)
автоматически рассчитывается стоимость отправки, которая отображается на
компоненте Label10. Кнопка «Отправить» добавляет
введённые пользователем данные в таблицу «Отправленная корреспонденция», кнопка
«Распечатать квитанцию об оплате» подставляет введённые пользователем данные в
ячейки квитанции (см. рис. 5). Автоматически осуществляется проверка введённых
пользователем данных: если какое-либо из полей не заполнено, в поле «Вес»
содержится нечисловое значение, в поле «Дата» - значение, которое не является
датой, то запись данных произведена не будет и пользователь увидит сообщение об
ошибке.
Рисунок 7 – Вкладка
«Получение» формы «Почта»
Вкладка «Получение» формы
«Почта» (см. рис. 7) содержит полностью аналогичные элементы для ввода исходных
данных. Отсутствует лишь компонент Label, на который выводится стоимость отправки (за ненадобностью). Проверка
введённых данных осуществляется аналогично. Кнопка «Распечатать извещение»
подставляет введенные данные в ячейки бланка извещения (представлен на рисунке
4), кнопка «Получить» заносит данные в таблицу «Полученная корреспонденция»,
причём в ячейку «Выдано» значение не заносится.
Рисунок 8 – Вкладка
«Выдача» формы «Почта»
На вкладке «Выдача» формы
«Почта» (см. рис. 8) содержится динамически обновляемый компонент ListBox, в котором отображаются все строки
таблицы «Полученная корреспонденция», для которых в столбце «Выдано» находится
пустое значение. Список обновляется при каждом нажатии кнопки «Получить» на
вкладке «Получение» (увеличивается на одну строку). При каждом нажатии кнопки
«Выдать» выбранная строка удаляется из списка, а в соответствующую ячейку
таблицы «Полученная корреспонденция» заносится значение «ВЫДАНО».
Рисунок 9 - Вкладка
«Таблицы и отчёты» формы «Почта»
Вкладка «Таблицы и
отчёты» формы «Почта» (см. рис. 9) содержит 4 кнопки для 4 отчётов
соответственно. Подробно отчёты по направлениям и сопроводительные ведомости
описаны ниже, в разделе «Описание отчётов».
Кнопка «Выход» закрывает
форму и сохраняет текущую рабочую книгу, кнопка «Справка» отображает форму
«Справка», которая содержит краткие инструкции для пользователя по работе с
программой.
Рисунок 10 – Окно справки
Описание отчётов
С полным программным
кодом отчётов можно ознакомиться в приложении Б.
1) Отчёт по направлениям
(отправка), отчёт по направлениям (получение). Формирует таблицу, в которой
отображается суммарное количество отправленных (или, соответственно,
полученных) в каждый город посылок, бандеролей и заказных писем, а также их суммарный
вес. Пример этого отчёта находится в таблице 1:
Таблица 1
пример отчёта по направлениям.
Отчёты по направлениям
для отправленной и полученной корреспонденции одинаковы, различие лишь в
таблице исходных данных. Отчёты располагаются на лист
становится виден пользователю при формировании этого отчёта. Встроенные
средства и функции Excel не использовались, подсчёт веса и
количества производился напрямую, просмотром исходной таблицы с использованием
вспомогательных переменных.
2) Сопроводительная
ведомость (отправка), сопроводительная ведомость (получение). Формирует выборку
из таблицы исходных данных («Отправленная корреспонденция» или «Полученная
корреспонденция») за один день. Дата вводится пользователем с клавиатуры, при
некорректном вводе отображается окно ввода до тех пор, пока не будет введена
корректная дата. В сопроводительной ведомости не указывается стоимость отправки
корреспонденции. Пример этой сопроводительной ведомости представлен на рисунке
11.
Рисунок 11 – Пример сопроводительной ведомости
Сопроводительная
ведомость для отправленной и полученной корреспонденции одинаковы, различие
лишь в обрабатываемой таблице исходных данных. Отчёты располагаются на листе
«Сопроводительная ведомость». Использовались методы Copy и Paste, поскольку
копирование результатов расширенного фильтра напрямую на другой лист
невозможно. Кроме этого, не используются дополнительные ячейки рабочих листов.
3) Отчёт о невыданной
корреспонденции. Этот отчёт отображается не на ячейках книги Excel, а на компоненте ListBox формы (см. рис. 8). Список
обновляется при каждом нажатии кнопки «Получить» (то есть при получении
корреспонденции). При каждом нажатии кнопки «Выдать» выбранная строка удаляется
из отчёта, так как выбранная корреспонденция выдаётся получателю. При
разработке этого отчёта использовался механизм встроенного в Excel расширенного фильтра, а ячейки -
результат этого расширенного фильтра использовались как RowSource для компоненты ListBox. Пример отчёта о невыданной
корреспонденции находится на рис. 8.
Заключение
В ходе работы было
разработано полноценное приложение с использованием Microsoft Excel на языке VBA, позволяющее
автоматизировать деятельность почты по учёту полученных и отправленных посылок,
бандеролей и писем без приобретения дополнительных специализированных
программных продуктов. Разработанное приложение полностью удовлетворяет
требованиям, поставленным в задаче: производится учёт корреспонденции,
формируются бланки извещения и необходимые для работы почты отчёты. В ходе
работы были изучены средства языка VBA, позволяющие скрывать и отображать отдельные листы и всё приложение
целиком; методы и средства работы как с отдельными ячейками, так и с их
диапазонами.
Дальнейшее
развитие программного продукта возможно в следующих направлениях:
-
расширение
исходных данных и обрабатываемой корреспонденции (наложенный платёж, денежные
переводы и пр.)
-
добавление новых
отчётов на базе уже имеющихся исходных данных (например, формирование списка
корреспонденции, которая не была выдана получателю в течение месяца, а потому
подлежит возврату отправителю и т.д.)
Список
использованной литературы
1) Встроенная справочная система Microsoft Office Excel.
2) Тематический раздел форума city.is74.ru по
программированию на VBA.
3) Справочная система MSDN
Приложение А
Схема связи форм
приложения.
Вызов формы «Справка»
осуществляется по нажатию кнопки «Справка» на главной форме «Почта».
Вызов главной формы
«Почта производится автоматически при открытии рабочей книги.
Приложение
Б
Ниже представлены
основные смысловые фрагменты кода программы.
Дублирующиеся фрагменты
кода (например, сопроводительной ведомости полученной и отправленной
корреспонденции) приводятся 1 раз.
'Обработка
нажатия кнопки «Отправить». Добавление данных в исходную таблицу
Private Sub CommandButton11_Click()
Dim Flag As Boolean
Flag = True
If IsNumeric(TextBox6.Text) = False Or
IsDate(TextBox1.Text) = False Or ComboBox1.Value = "" Or _
ComboBox1.Value = "" Or TextBox1.Text =
"" Or TextBox2.Text = "" Or TextBox3.Text = "" Or
_
TextBox4.Text = "" Or TextBox5.Text =
"" Or TextBox6.Text = "" Then Flag = False
If
Flag = False Then
a = MsgBox("Данные введены неверно или не полностью", vbCritical,
"Ошибка")
Exit
Sub
End
If
Sheets("Отправленная
корреспонденция").Select
Range("A3").Select
Selection.CurrentRegion.Select
i = Selection.Rows.Count
j = i + 3
Range("A" & j).Value = i
Range("B" & j).Value = TextBox1.Text
Range("C" & j).Value = ComboBox1.Value
Range("D" & j).Value = ComboBox2.Value
Range("E" & j).Value = TextBox2.Text
Range("F" & j).Value = TextBox3.Text
Range("G" & j).Value = TextBox5.Text
Range("H" & j).Value = TextBox4.Text
Range("I" & j).Value = TextBox6.Text
Range("J" & j).Value = Label10.Caption
End
Sub
'Обработка
нажатия кнопки печати квитанции об оплате отправки корреспонденции
Private Sub CommandButton4_Click()
If IsNumeric(TextBox6.Text) = False Or
IsDate(TextBox1.Text) = False Or ComboBox1.Value = "" Or _
ComboBox1.Value = "" Or TextBox1.Text =
"" Or TextBox2.Text = "" Or TextBox3.Text = "" Or
_
TextBox4.Text = "" Or TextBox5.Text =
"" Or TextBox6.Text = "" Then
a = MsgBox("Данные введены неверно или
не полностью", vbCritical, "Ошибка")
Exit Sub
End
If
Sheets("Отправленная
корреспонденция").Select
Range("A3").Select
Selection.CurrentRegion.Select
i = Selection.Rows.Count - 1
Sheets("Бланки").Select
Range("Q18").Value = TextBox1.Text
Range("P19").Value = i
Range("M23").Value = TextBox2.Text
Range("M24").Value = TextBox3.Text
Range("M25").Value = TextBox5.Text
Range("M26").Value = TextBox4.Text
Range("N27").Value = ComboBox1.Value
Range("L28").Value = TextBox6.Text
Range("M29").Value = Label10.Caption
Application.Visible = True
For Each m In Sheets
If m.Name <> "Бланки"
Then m.Visible = False
Next m
Application.Visible = True
Почта.Hide
End
Sub
'функция,
рассчитывающая стоимость отправки
Private Function DispatchCost(City As String, Kind As
String, Optional weight As Double)
Sheets("Стоимость отправки").Select
Range("A2").Select
Selection.CurrentRegion.Select
n = Selection.Rows.Count
For i = 3 To n Step 1
If InStr(1, Sheets("Стоимость
отправки").Range("A" & i).Value, City, vbTextCompare) > 0
Then
If Kind = "посылка" Then DispatchCost =
weight * Range("B" & i).Value
If Kind = "бандероль" Then DispatchCost =
weight * Range("E" & i).Value
If Kind = "заказное письмо" Then
DispatchCost = weight * Range("H" & i).Value
End If
Next i
End Function
'Пересчёт
стоимости отправки корреспонденции при изменении города. Аналогичный код
обрабатывает изменение веса и типа корреспонденции
Private Sub
ComboBox2_Change()
If IsNumeric(TextBox6.Text) And ComboBox1.Value <>
"" And ComboBox2.Value <> "" And TextBox6.Text
<> "" _
Then Label10.Caption = DispatchCost(ComboBox2.Value,
ComboBox1.Value, CDbl(TextBox6.Text)) _
Else Label10.Caption =
""
End Sub
'Отчёт
по направлениям (отправка)
Private Sub CommandButton5_Click()
Worksheets("Отчёты").Activate
Cells(3, 1).Select
Selection.CurrentRegion.Select
n = Selection.Rows.Count + 2
Worksheets("Отправленная
корреспонденция").Select
Cells(3, 1).Select
Selection.CurrentRegion.Select
n2 = Selection.Rows.Count + 2
For i = 4 To n Step 1
Sheets("Отчёты").Select
CurrentCity = Range("A" & i).Value
'перебор городов
Count1 = 0
Count2 = 0
Count3 = 0
Sum1 = 0
Sum2 = 0
Sum3 = 0
Sheets("Отправленная
корреспонденция").Select
For j = 4
To n2 Step 1
'перебор отправленной корреспонденции
If Range("D" & j).Value = CurrentCity Then
If Range("C" & j).Value = "посылка"
Then
Count1 = Count1 + 1
Sum1 = Sum1 + Range("I" & j).Value
End If
If Range("C" & j).Value =
"бандероль" Then
Count2 = Count2 + 1
Sum2 = Sum2 + Range("I" & j).Value
End If
If Range("C" & j).Value = "заказное
письмо" Then
Count3 = Count3 + 1
Sum3 = Sum3 + Range("I" & j).Value
End If
End If
Next j
Sheets("Отчёты").Select
Range("B" & i).Value = Count1
Range("C" & i).Value = Sum1
Range("D" & i).Value = Count2
Range("E" & i).Value = Sum2
Range("F" & i).Value = Count3
Range("G" & i).Value = Sum3
Next i
For Each m In Sheets
If m.Name <> "Отчёты" Then m.Visible =
False
Next m
Application.Visible = True
Почта.Hide
End Sub
'формирование
сопроводительной ведомости (получение)
Private Sub
CommandButton8_Click()
data = InputBox("Укажите
дату", "Сопроводительная ведомость (получение)")
While IsDate(data) = False
data = InputBox("Укажите
дату", "Сопроводительная ведомость (получение)")
Wend
Sheets("Сопроводительная ведомость").Select
Range("K4").Select
Selection.CurrentRegion.Select
n = Selection.Rows.Count + 4
If n > 4 Then Range("K5:S" & n).Clear
Sheets("Полученная
корреспонденция").Select
Range("A3").Select
Selection.CurrentRegion.Select
n = Selection.Rows.Count + 2
c = 5
For i = 4 To n Step 1
Sheets("Полученная корреспонденция").Activate
If Range("B" & i).Value = data Then
Range("A" & i & ":I"
& i).Copy
Sheets("Сопроводительная
ведомость").Activate
Range("K"
& c).Select
ActiveSheet.Paste
c = c + 1
End If
Next i
For Each m In Sheets
If m.Name
<> "Сопроводительная ведомость" Then
m.Visible = False
Next m
Application.Visible = True
Почта.Hide
End Sub
'обработка
нажатия кнопки «Выдать»
Private Sub CommandButton13_Click()
i = ListBox1.ListIndex
j = ListBox1.List(i, 0)
+ 3
Sheets("Полученная
корреспонденция").Select
Range("J" & j).Value = "ВЫДАНО"
Range("A3").Select
Selection.CurrentRegion.Select
n = Selection.Rows.Count + 2
Range("N3").Select
Selection.CurrentRegion.Select
Selection.Clear
Range("A3:J" & n).AdvancedFilter
Action:=xlFilterCopy, CriteriaRange:=Range( _
"L3:L4"),
CopyToRange:=Range("N3"), Unique:=False
Range("N3").Select
Selection.CurrentRegion.Select
n = Selection.Rows.Count + 3
ListBox1.RowSource = "N4:V" & n
End
Sub