Метод создания рабочего расписания
Содержание
Введение
.
Постановка задачи
.
Функциональные возможности приложения
.
Алгоритм программы
.
Алгоритм процедуры выполнения управляющих функций (Main)
.
Запуск приложения
.
Настройки листов Excel
.
VBA-код
.1
Код процедуры Workbook_Open
.
Пользовательские формы и обработка событий
.1
Код обработки событий формы OptionsForm
.2
Алгоритм процедуры входных данных модели (OptionsForm)
.3
Код обработки событий формы InputsForm
.4
Алгоритм процедуры обработки событий (InputsForm)
.
VBA - код модуля
.1
Объявление переменных с областью действия Public
.2
Код процедуры Main
.3
Код процедуры RunSolver
.4
Код процедуры CreateReport
.5
Алгоритм процедуры вывода отчета (CreateReport)
.6
Код процедуры Sensitivity
.7
Алгоритм процедуры анализа чувствительности (Sensitivity)
.8
Код процедуры GoToExplanation
Заключение
Литература
Введение
программа расписание
сотрудник
В современных рыночных условиях развития
экономики оперативное управление предприятием требует новых подходов: на первый
план выходят экономические, рыночные критерии эффективности, повышаются
требования к гибкости. Научно-технический прогресс и динамика внешней среды
заставляют современные предприятия превращаться во всё более сложные системы,
для которых необходимы новые методы обеспечения управляемости. Руководство
крупных компаний испытывает потребность в достоверной информации о различных
аспектах бизнеса компании в целях поддержки принятия решений. От этого зависит
качество управления компанией, возможность эффективного планирования ее
деятельности, выживание в условиях жесткой конкурентной борьбы. При этом
критически важными являются наглядность форм представления информации, быстрота
получения новых видов отчетности, возможность анализа текущих и исторических
данных. Системы, предоставляющие такие возможности, называются Системами
Поддержки Принятия Решений (СППР).
Они с успехом применяются в самых разных
отраслях: телекоммуникациях, финансовой сфере, торговле, промышленности,
медицине и многих других.
Системы СППР позволяют решать три основных задачи:
ведение отчётности, анализ информации в реальном времени (OLAP) и
интеллектуальный анализ данных.
В современном мире очень важным фактором
является время. Проблема нехватки времени или вопрос как все успеть, рано или
поздно приходит к любому человеку, на любом предприятии, в любой компании, в
последнее время она становиться все более актуальной.
В данном курсовом проекте была
разработана система поддержки принятия решений, а именно создание рабочего
расписания для сотрудников компании. Этот вид приложения поможет в составлении
и оценке плана работы. Когда имеется большой штат служащих, может возникнуть
путаница, которая часто приводит к потере времени и денег. С помощью данного
приложения для составления рабочего расписания, можно избежать таких проблем,
сохранив при этом время и ресурсы компании.
1.
Постановка задачи
Составить рабочее расписание
для сотрудников компании таким образом, чтобы заполнить семидневную рабочую
неделю. Каждый сотрудник должен работать не более 5 дней в неделю, однако некоторые
сотрудники могут иметь несмежные выходные дни. Например, сотрудник может
приходить на работу в понедельник, среду, четверг, пятницу и воскресенье. У
этого работника тоже два выходных дня (вторник и суббота), но они несмежные. В
модели задано ограничение на количество работников с несмежными выходными
днями. Целью оптимизации является минимизация выплат заработной платы с учетом
общего рабочего времени сотрудников компании и различной почасовой оплаты в
будние и выходные дни.
Входные данные модели показаны
на рисунке 1.1:
Рисунок 1.1 - Входные данные
модели
2.
Функциональные возможности приложения
Приложение обеспечивает
пользователей следующими функциональными возможностями.
. Позволяет
просматривать/изменять исходные параметры модели: ограничение на рабочее время,
размер почасовой оплаты труда в будние и выходные дни, а также количество
сотрудников с несмежными выходными днями. На основе полученных входных
параметров приложение определяет оптимальное решение и представляет его
пользователю в дружественной форме.
. На основе указанных
требований к рабочему времени и размеру почасовой оплаты труда приложение
выполняет анализ чувствительности для максимального количества несмежных
выходных дней. Результаты анализа отображаются в графической форме.
3. Алгоритм
программы
Рисунок 3.1 - Алгоритм
программы
4. Алгоритм
процедуры выполнения управляющих функций (Main)
Рисунок 4.1 - Алгоритм процедуры
выполнения управляющих функций
5.
Запуск приложения
Приложение находится в файле Scheduling.xls.
После его открытия на экране отображается лист Описание с кнопкой (рисунок
5.1). Щелкнув на кнопке, пользователь может выбрать один из двух переключателей,
показанных на рисунке 5.2.
Рисунок 5.1 - Лист Описания
Если выбрать первый переключатель, то будет
отображено диалоговое окно, показанное на рисунке 5.3. В этом диалоговом окне
содержатся значения, полученные при предыдущем запуске модели. Конечно, любые
из представленных значений можно изменить. После щелчка на кнопке ОК введенные
пользователем данные копируются в строку (скрытого) листа Модель (рисунок 5.7).
Рисунок 5.2 - Диалоговое окно с
переключателями
После ввода параметров и щелчка на кнопке ОК
вызывается надстройка Поиск решения, а результат оптимизации выводится на
рабочий лист Отчет, который показан на рисунке 5.4.
Рисунок 5.3 - Диалоговое окно с требованиями к
рабочему времени
Рисунок 5.4 - Отчёт с оптимальным решением
Если в диалоговом окне, показанном на рисунке
5.2, установить второй переключатель, то надстройка Поиск решения будет вызвана
несколько раз - для каждого максимального количества сотрудников с несмежными
выходными днями в диапазоне от 0% до 100% с шагом 10%. Оптимальное решение
будет представлено в графической форме (рисунок 5.5). В частности, для каждого
максимального процентного значения в отчете отображается общее количество
сотрудников и количество сотрудников, которые имеют несмежные выходные дни.
Также отображается напоминание, показанное на рисунке 5.6.
Рисунок 5.5 - Графическое представление
оптимального решения
Рисунок 5.6 - Напоминание о возможности
существования нескольких оптимальных решений
Все результаты основаны на данных предварительно
настроенной на листе Модель модели - рисунок 5.7. (Несмотря на то, что в этой
модели для вычислений используются простые формулы, с ними все же стоит
ознакомиться отдельно. Перед этим необходимо открыть лист Модель с помощью
команды Сервис Лист Отобразить.)
Рисунок 5.7 - Модель составления расписания
6.
Настройка листов Excel
Представленная модель
оптимизации всегда имеет постоянный размер, так как в неделе всегда 7 дней.
Таким образом, большую часть приложения можно настроить на этапе разработки
средствами пользовательского интерфейса Excel
(без использования кода VBA).
Приложение состоит из четырех листов.
. Лист Модель (рисунок 5.7)
полностью заполняется на этапе разработки произвольными значениями. Кроме того,
на нем вводятся параметры настройки Поиск решения, что возможно только
благодаря фиксированному размеру модели - в ней изменяются только входные
параметры. Со структурой этой модели можно ознакомиться в файле Sheduling.xls.
Большая часть модели предельно понятна. Но обратите особое внимание на ячейку Е32,
которая содержит формулу =СУММ (AvailThu).
В данном случае AvailThu
представляет имя диапазона, в который включаются несмежные ячейки для всех дней
недели, за исключением одного - в данном случае среды (выходной). Присвоение
имен диапазонам несмежных ячеек в Excel
не вызывает затруднений и выполняется достаточно часто.
3. Шаблон, который можно использовать для разработки
отчета, создается на листе Отчет, показанном на рисунке 5.4. Такой шаблон
представлен на рисунке 6.1. Разделы оплаты труда, доступности рабочей силы и
количества работников содержат формулы, связанные с данными листа Модель,
поэтому на листе Отчет всегда будут отображаться результаты последнего
выполнения приложения. А вот раздел оптимального решения задачи в шаблоне
остается пустым. В этом разделе указываются только положительные значения, а
они становятся известны только на этапе выполнения приложения. Таким образом,
код VBA необходим
для копирования положительных значений с листа Модель на лист Отчет.
Рисунок 6.1 - Шаблон отчета
Рисунок 6.2 - Данные для диаграммы
. Диаграмма, показанная на рисунке 5.5,
расположена на отдельном листе Диаграмма. Она связана с данными на листе Модель
(рисунок 6.2). В отображенной области листа Модель содержатся процентные
значения в столбце АА и количественные показатели модели оптимизации в столбцах
АВ и АС. Значения столбцов АВ и АС указывают количество работников с несмежными
выходными днями и общее количество работников в оптимальном решении. Для
создания диаграммы можно воспользоваться любыми приемлемыми значениями в
столбцах АВ и АС. Конечная диаграмма создается с помощью мастера диаграмм. В
процессе анализа чувствительности VBA-код
заменит эти значения на оптимальные.
7. VBA-код
Данное приложение содержит две пользовательские
формы, которые называются InputsForm
и OptionsForm, модуль и ссылку
на надстройку Solver.xla.
После добавления этих компонентов окно Project
будет выглядеть так, как показано на рисунке 7.1.
Рисунок 7.1 - Окно проекта
7.1 Код
процедуры Workbook_Open
Этот код обеспечивает
отображение листа Описание при открытии файла. Следующая процедура вводится в
окне кода объекта ThisWorkbook.
Процедура GoToExplanation
располагается в модуле (и показана ниже); ее вызов из процедуры Workbook_Open
объекта ThisWorkbook
вполне допустим.
Private Sub
Workbook_Open().Show Sub
8.
Пользовательские формы и обработка событий
Внешний вид пользовательской формы OptionsForm
показан на рисунке 8.1. На форме расположены стандартные кнопки ОК и Отмена,
подпись с описанием и рамка Options,
а также два переключателя, включенные в рамку, - Option1
и Option2.
Рисунок 8.1. - Внешний вид пользовательской формы
Options Form
Пользовательская форма InputsForm
содержит 10 текстовых полей ввода данных и соответствующие подписи (рисунок
8.2), стандартные кнопки ОК и Отмена, а также несколько подписей с описанием
назначения диалогового окна слева от кнопок. Поля ввода данных, каждое для
своего дня недели, называются Day1Box,
Day2Box
и т.д. до Day7Box
(с понедельника по воскресенье). Поля для указания ставки заработной платы
называются WeekdayBox
и WeekendBox,
а поле для ввода максимально допустимого количества сотрудников с несмежными
выходными (в процентном отношении) называется MaxPctBox.
Как только разработка
пользовательской формы будет завершена, можно приступать к написанию кода
обработки событий. Для диалогового окна OptionsForm
в процедуре UserForm_Initialize
первый переключатель устанавливается по умолчанию. Процедура OKButton_Click
заносит установленное значение в переменную Choice.
Процедура CancelButton_Click
выгружает пользовательскую форму и завершает выполнение программы.
8.1 Код
обработки
событий
формы
OptionsForm
Sub
UserForm_Initialize()
' Установка первого
переключателя по умолчанию
Optionl = True
SubSub OKButton_Click()
' Сохранение переключателя в
переменной Choice
If Option1 = True
Then
Choice = 1
Choice = 2If
' Выгрузка
диалогового
окна
Unload Me Sub
Private Sub
CancelButton_Click()
' Выгрузка диалогового окна и
выход
Unload Me
End
End Sub
Рисунок 8.2 - Внешний вид
пользовательской формы InputsForm
Код обработки событий
пользовательской формы InputsForm
немного сложнее. В этом случае процедура UserForm_Initialize
используется для копирования существующих входных параметров с листа Модель в
поля ввода данных на форме. Процедура OKButton_Click
применяется для копирования введенных пользователем данных обратно на лист
Модель. Сначала проверяется правильность значений, введенных в текстовые поля.
Если текстовое поле содержит некорректное значение, то пользовательская форма
отображается повторно и в ней активизируется поле с ошибочным значением. Если
значения введены правильно, то с помощью функции Val
они (строковые значения) будут преобразованы в числовой формат и скопированы в
соответствующие ячейки листа Модель. (Если бы текстовые поля возвращали
значения типа Single
или Integer,
то функция Val
оказалась бы не нужна. Но поскольку значение текстового поля копируется
непосредственно в ячейки рабочего листа, то функция Val
просто необходима, так как Excel
будет интерпретировать значения ячеек как надписи, а потому не позволит
выполнять над ними арифметические операции.)
8.2 Алгоритм
процедуры входных данных модели (OptionsForm)
Рисунок 8.3 - Алгоритм
процедуры входных данных модели
8.3 Код
обработки событий формы InputsForm
Private Sub
UserForm_Initialize()
Dim ctl As Control, DayIndex As
Integer
' Вводит в поля значения из диапазонов Required,
' BonusPct
и MaxPct, если таковые
существуют
For Each ctl In
Me.Controls
If TypeName(ctl)
= "TextBox" Then
' Поля
называются
Day1Box-Day7Box. Первые три символа в
' имени - "Day",
а четвертый символ изменяется от 1 до 7
If Left(ctl.Name,
3) = "Day" Then
DayIndex =
Mid(ctl.Name, 4, 1) .Text = Range("Required") _
.Cells(DayIndex)
ctl.Name = "WeekdayBox" Then .Text = Range("WeekdayRate")
ctl.Name = "WeekendBox" Then .Text = Range("WeekendRate")
.Text = Range("MaxPct") If If SubSub OKButton_Click()ctl As Control,
DayIndex As Integer
' Проверяет введенные значения.
Если найдены ошибочные
' данные, то окно не
выгружается, а процедура завершается
For Each ctl In
Me.ControlsTypeName(ctl) = "TextBox" Thenctl.Text = "" Or
Not IsNumeric(ctl.Text) Then "Введите числовое
значение", _
vbInformation,
"Некорректные данные"
ctl.SetFocus Sub
Left(ctl.Name, 3) = "Day" And _ .Text
< 0 Then
MsgBox
"В поля вводятся целые " _
& " положительные
числа", _
vbInformation,
"Некорректные данные"
ctl.SetFocus Sub
Left(ctl.Name, 4) = "Week" And _ .Text
<= 0 Then
MsgBox
"Ставка зарплаты представляется" _
& " положительным числом", _
vbInformation,
"Некорректные данные"
ctl.SetFocus Sub Left(ctl.Name, 3) =
"Max" And _
(ctl.Text < 0 Or ctl.Text > 1)
Then "Процент
сотрудников, имеющих " _
& " несмежные выходные, вводится в
", _
& " виде десятичной дроби", _
vblnformation,
"Некорректные данные"
End If
' Если данные корректны, то они
сохраняются в диапазоне
' Required
или MaxPct листа
Модель. Текстовое поле
' возвращает строку; функция Val
конвертирует ее в число
If Left(ctl.Name,
3) = "Day" Then = Mid(ctl.Name, 4,
1)("Required").Cells(DayIndex) = Val(ctl.Text)ctl.Name =
"WeekdayBox" Then("WeekdayRate") = Val(ctl.Text) ctl.Name =
"WeekendBox" Then("WeekendRate") = Val(ctl.Text)
("MaxPct") = Val(ctl.Text)If If
' Выгрузка
диалогового
окнаMe
SubSub CancelButton_Click()
' Выгрузка окна и выход
Unload
Me
End Sub
8.4 Алгоритм
процедуры обработки событий (InputsForm)
Рисунок 8.4 - Алгоритм процедуры
обработки событий
9.
VBA-код модуля
Большая часть VBA-кода
расположена в модуле. Сам код приводится ниже. Код имеет модульную структуру,
что всегда считалось хорошим тоном в программировании. После объявления
переменной Choice с областью
действия Public переменная Main
последовательно вызывает остальные процедуры.
9.1 Объявление
переменных с областью действия Public
Option
Explicit
' Переменная Choice
принимает значение 1 или 2 в
' зависимости от установленного
переключателя в первом
' диалоговом
окнеChoice
As Integer
9.2 Код
процедуры Main
Процедура Main
выполняет в приложении управляющие функции и назначена кнопке, расположенной на
листе Описание. Для выполнения расчетов процедура Main
вызывает другие процедуры. Структура процедуры описывается в комментариях.
Sub
Main ()
' Запускается при щелчке на
кнопке листа Описание
' Отображается диалоговое окно
Параметры приложения; дальнейшие
' действия зависят от значения
переменной Choice
OptionsForm.Show
If
Choice = 1 Then
' Отображается диалоговое окно
Входные данные модели, в котором
' вводятся параметры модели
InputsForm.Show
' Получение решения и вывод
отчета
Call RunSolver
CreateReport
' Анализ
чувствительности
Sensitivity
' Активизация листа Диаграмма,
его отображение и вывод
' информационного сообщения
Application.ScreenUpdating
= True
With Sheets("Диаграмма")
.Visible = True
.Activate With
MsgBox
"Необычное поведение диаграммы вызвано " _
& "существованием
нескольких оптимальных " _
& "решений для текущей
модели.", _
vbInformation,
"Оптимальное
решение"
If Sub
9.3 Код
процедуры RunSolver
Процедура RunSolver
отображает и активизирует лист Модель, а после этого запускает надстройку Поиск
решения. Обратите внимание, что надстройка Поиск решения уже настроена (на
этапе разработки приложения), поэтому для ее запуска достаточно выполнить
функцию SolverSolve.
Также удостоверьтесь, что проверка существования решения не выполняется, так
как всегда существует возможность пригласить достаточное количество работников,
чтобы обеспечить дневную потребность - просто это будет больше стоить для
работодателя.
Sub
RunSolver().ScreenUpdating = False Worksheets("Модель")
.Visible = True
.Activate
Withuserfinish:=True Sub
9.4 Код процедуры
CreateReport
Данная процедура отображает и
активизирует лист Отчет, очищает ячейки от значений, полученных при предыдущем
запуске приложения, и копирует положительные значения с листа Модель в
соответствующие ячейки (под ячейкой С10) листа Отчет.
Sub CreateReport()i
As Integer, Counter As Integer .ScreenUpdating
= False
' Отображение и активизация
листа Отчет
With
Worksheets("Отчет")
.Visible = True
.Activate With
' Очистка данных, полученных
при предыдущем запуске
With
Range("CIO")(.Offset(1, 0), .Offset(1, 1) _
.End(xlDown)).ClearContents
End
With
' Передача данных с листа
Модель на лист Отчет
Counter = 0
Range("C10")i = 1 То
21Range("Assignments").Cells(i) > 0 Then =
Counter + 1
' Сохранение дней недели и
количества сотрудников
.Offset(Counter, 0)
= _ ("Assignments").Cells(i).Offset(0, -2)
.Offset(Counter, 1)
= _ ("Assignments").Cells(i) If
Next
End
With("Al").Select Sub
9.5 Алгоритм
процедуры вывода отчета (CreateReport)
Рисунок 9.1 - Алгоритм процедуры
вывода отчета
9.6
Код процедуры Sensitivity
Public Sub
Sensitivity() i As Integer .ScreenUpdating
= False
' Отображает и активизирует
лист Модель
With
Worksheets("Модель")
.Visible = True
.Activate With
' Анализ 11 задач поиска
максимального количества
' сотрудников
For
i = 1 То 11
Range("MaxPct")
= (i - 1) * 0.1("Assignments") = 0 userfinish:=True
' Сохранение результата в
массиве, с которым связана уже
' построенная диаграмма
With
Range("AA1")
.Offset(i, 1) =
Range("Nonconsec")
.Offset(i, 2) =
Range("TotalWorkers") With Sub
9.7
Алгоритм процедуры анализа чувствительности (Sensitivity)
Рисунок 9.2 - Алгоритм процедуры
анализа чувствительности
9.8 Код
процедуры GoToExplanation
Процедура GoToExplanation
предназначена для упрощения навигации по листам. Она связана с соответствующими
кнопками на листах Модель, Отчет и Диаграмма.
Public
Sub GoToExplanation()
' Запускается при щелчке на кнопке на любом
листе,
' кроме листа Описание. Она не только возвращает
вас на первый
' лист, но и скрывает остальные листы
Worksheets("Описание").Activate("Е4").Select("Модель").Visible
= False("Отчет").Visible
= False("Диаграмма").Visible
= False Sub
Заключение
В данном курсовом проекте был описан
метод создания рабочего расписания, таким образом, чтобы заполнить семиндневную
рабочую неделю. Каждый сотрудник должен работать не более 5 дней в неделю, и
иметь два выходных дня (не обязательно суббота и воскресение). Основной задачей
программы является минимизация выплат заработной платы с учетом общего рабочего
времени сотрудников компании и различной почасовой оплаты в будние и выходные
дни.
Для этого был использован MS Excel
(в
частности надстройка Поиск решения) и VBA. В условии нашего задания начальными
данными являлись (рисунок 10):
Рисунок 10 - начальные данные задачи
Оптимальным решением задачи является (рисунок
11):
Рисунок 11 - оптимальное решение задачи
Приложение позволяет просматривать и изменять
исходные параметры модели, такие как ограничения на рабочее время, размер
почасовой оплаты труда в будни и выходные дни и т.д. На основании полученных
выходных параметров приложение определяет оптимальное решение и представляет
его пользователю в дружественной форме.
В работе был показан принцип
взаимодействия документов Excel
между собой, передача данных из одной книги в другую, что существенно может
облегчить и ускорить работу с приложением. Рассмотрен принцип работы модуля VBA
с данными из разных документов и программный способ управления надстройкой
Поиск Решения. Рассмотрена процедура обработки событий Click:OKButton_Click
выполняет основную работу по проверке правильности введенных данных перед
занесением их в несколько переменных с областью действия Public.
В настоящее время линейное программирование
является одним из наиболее употребительных аппаратов математической теории
оптимального принятия решения. Для решения задач линейного программирования
разработано сложное программное обеспечение, дающее возможность эффективно и
надежно решать практические задачи больших объемов. Эти программы и системы
снабжены развитыми системами подготовки исходных данных, средствами их анализа
и представления полученных результатов.
Литература
1. Кристин Олбрайт. Моделирование с помощью
Microsoft
Excel и VBA:
разработка систем принятия решений.
2. www.bestreferat.ru
<http://www.bestreferat.ru>
. revolution.allbest.ru
. diplom.by