Метод создания рабочего расписания

Метод создания рабочего расписания

Содержание

Введение

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

. Функциональные возможности приложения

. Алгоритм программы

. Алгоритм процедуры выполнения управляющих функций (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