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

Размещено на http://www.allbest.ru/

Содержание

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

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

В работе был показан принцип взаимодействия документов Excel между собой, передача данных из одной книги в другую, что существенно может облегчить и ускорить работу с приложением. Рассмотрен принцип работы модуля VBA с данными из разных документов и программный способ управления надстройкой Поиск Решения. Рассмотрена процедура обработки событий Click:OKButton_Click выполняет основную работу по проверке правильности введенных данных перед занесением их в несколько переменных с областью действия Public.

В настоящее время линейное программирование является одним из наиболее употребительных аппаратов математической теории оптимального принятия решения. Для решения задач линейного программирования разработано сложное программное обеспечение, дающее возможность эффективно и надежно решать практические задачи больших объемов. Эти программы и системы снабжены развитыми системами подготовки исходных данных, средствами их анализа и представления полученных результатов.

Литература

1. Кристин Олбрайт. Моделирование с помощью Microsoft Excel и VBA: разработка систем принятия решений.

2. www.bestreferat.ru

3. revolution.allbest.ru

4. diplom.by

Размещено на www.allbest.ru