37363

Разработка программного обеспечения решения задачи о назначении сотрудников на должности

Курсовая

Информатика, кибернетика и программирование

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

Русский

2013-09-24

199.52 KB

6 чел.

Министерство образования и науки Российской Федерации

ФГАОУ ВПО «Уральский федеральный университет
имени первого Президента России Б.Н. Ельцина»

Институт материаловедения и металлургии

Кафедра «Теплофизика и информатика в металлургии»

Оценка работы: _____________

Члены комиссии:

________________  _________

Подпись               расшифровка подписи

________________  _________

Подпись               расшифровка подписи

________________  _________

Подпись               расшифровка подписи

«___» ___________ 20___ г.

Разработка программного обеспечения решения задачи

о назначении сотрудников на должности

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

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

ПОЯСНИТЕЛЬНАЯ ЗАПИСКА

280075 000000 00104 ПЗ

 Руководитель

 Доцент, к.т.н.       В.В.Лавров

должность, звание     подпись        расшифровка подписи

 Нормоконтролер

 Доцент, к.т.н.       В.В.Лавров

должность, звание     подпись        расшифровка подписи

Студент

 МТ-………        И.О.Фамилия

номер группы     подпись        расшифровка подписи

Екатеринбург

2012

ФГАОУ ВПО «Уральский федеральный университет
имени первого Президента России Б.Н. Ельцина»

Институт материаловедения и металлургии

Кафедра «Теплофизика и информатика в металлургии»

УТВЕРЖДАЮ:

Зав. кафедрой______________ (Н.А. Спирин)

« ____ » _____________ 2012 г.

Задание № 2

на выполнение курсовой работы

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

Студент группы Мт-_________. Специальность: ____________________________________

Фамилия: __________________. Имя: ______________. Отчество  ______________.

Руководитель курсовой работы: к.т.н., доцент Лавров В.В. (lavll2007@rambler.ru)

Срок выполнения работы: с «     »                        2012 г. по «    »                         2012 г.

1. Тема курсовой работы:

Разработка программного обеспечения решения задачи о назначении сотрудников на должности

2. Содержание курсовой работы

2.1. Пояснительная записка:

  1.  титульный лист;
  2.  бланк задания преподавателя на выполнение курсовой работы;
  3.  оглавление;
  4.  постановка задачи;
  5.  математическая модель;
  6.  решение задания в электронных таблицах MS Excel;
  7.  выводы;
  8.  список литературы;
  9.  приложение.

2.2. Компьютерные версии

  1.  файл электронных таблиц с решением;
  2.  файл с электронной версией пояснительной записки.

3. Особые дополнительные требования:

  1.  осуществить расчет в пакете MS Office Excel с использованием элементов управления Windows с их программной обработкой на языке программирования VBA;
  2.  предусмотреть защиту от некорректно вводимых данных на листе MS Office Excel;
  3.  выполнить защиту листа рабочей книги;

4. К защите предоставляются следующие материалы

  1.  программная реализация (файл электронных таблиц с решением);
  2.  пояснительная записка (ПЗ), выполненная в соответствии с требованиями по оформлению курсовых и дипломных работ.

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

Задание к исполнению принял ___________________________________  (И.О.Фамилия)

«___»________2012 г.

Руководитель работы, доцент, к.т.н.  ______________________________ (В.В. Лавров)


Оглавление

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

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

3. Математическая модель 5

4. Решение задания в электронных таблицах MS Excel 7

4.1. Исходные данные для решения задачи 7

4.2. Параметры мастера поиска решения и базовые ограничения 7

4.3. Результат количественного решения задачи о назначении 8

5. Описание разработки макроса 9

Выводы 11

Список литературы 12

Приложение №1 Листинг макроса 13


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

Целью курсовой работы является разработка программного обеспечения в среде электронных таблиц MS Office Excel для решения задачи, постановка которой приведена ниже.

Имеется конечное число видов работ, которые могут быть выполнены потенциальными кандидатами на эти должности. При этом каждого кандидата можно назначить на выполнение только одной работы, а каждая работа, в свою очередь, должна выполняться только одним кандидатом. Известна эффективность выполнения каждой работы (или издержки при назначении) любым из потенциальных кандидатов. Требуется распределить всех кандидатов по работам так, чтобы общая эффективность выполнения всех работ была наибольшей [3–5].

Оценочной (целевой) функцией является общая эффективность выполнения всех работ, а ограничениями служат дополнительные условия на выполнения каждой работы только одним кандидатом и участие каждого кандидата в выполнении только одной работы.

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

Рассмотрим вариант задачи о назначении в форме минимизации общих затрат на выполнение работ. В качестве кандидатов рассмотрим пять сотрудников некоторой фирмы, а в качестве работ – пять вакантных должностей в этой фирме. Затраты на замещение должностей кандидатами, связанные с необходимостью их предварительного обучения и стажировки, представлены в форме следующей таблицы (табл. 2.1).

Таблица 2.1 – Затраты на замещение должностей кандидатов (в тыс. руб.)

Кандидаты/Должности

Иванов

Петров

Сидоров

Григорьев

Дмитриев

Менеджер

5

10

9

14

6

Программист

13

15

11

19

17

Бизнес-аналитик

7

14

12

8

10

Маркетолог

8

11

6

7

9

Руководитель проектов

15

12

17

13

16

3 Математическая модель

Пусть N – количество видов работ, на выполнение которых претендует каждый из N кандидатов. Заданы эффективности cij (i, j =1, 2, …, N) индивидуального выполнения каждым из потенциальных кандидатов всех рассматриваемых работ.

Введем в рассмотрение логические переменные: xij, которые будут соответствовать назначению кандидатов на выполнение работ. В этом случае будем считать, что xij=1, если i-й кандидат назначается на выполнение j-й работы и xij=0, если i-й кандидат не назначается на выполнение j-й работы. Тогда математическая постановка задачи о назначении может быть сформулирована следующим образом:

,         (3.1)

где множество допустимых альтернатив G формируется следующей системой ограничений типа неравенств:

       (3.2)

Математическая постановка рассматриваемой индивидуальной задачи о назначении может быть записана в следующем виде:

     (3.3)

где множество допустимых альтернатив G формируется следующей системой ограничений типа равенств:

         (3.4)

Формулы (3.1) – (3.4) представляют собой математическую модель данной задачи.

4 Решение задания в электронных таблицах MS Excel

Исходные данные для решения задачи в пакете MS Excel представлены на рис. 4.1.

Рис.4.1. Исходные данные для решения задачи

Для расчета целевой функции необходимо использовать встроенную в пакет MS Office Excel функцию:

=СУММПРОИЗВ(B2:F6;B9:F13)      (4.1)

(Перемножает соответствующие элементы заданных массивов и возвращает сумму произведений), которую мы разместили в ячейке G2.

Для расчета ограничений используется формула:

=СУММ(B9:B13)         (4.2)

(для каждого ограничения свой диапазон ячеек), эту формулу мы разместили в ячейках B14:F14 и G9:G13.

Для дальнейшего решения задачи следует вызвать мастер поиска решения, для чего необходимо выполнить операцию главного меню: Сервис/Поиск решения. Окно мастера нужно заполнить, как показано на рис. 4.2. В окне дополнительных параметров поиска решения (кнопка Параметры) выбрать отметку Линейная модель и Неотрицательные значения.

Рис. 4.2. Параметры мастера поиска решения и базовые ограничения

После задания ограничений и целевой функции можно приступить к поиску численного решения, для чего следует нажать кнопку «Выполнить». После непродолжительного выполнения расчетов программой MS Excel будет получено количественное решение, которое представлено на рис. 4.3 в ячейках B9:F13 и G2.

Рис. 4.3. Результат количественного решения задачи о назначении

Результатом решения рассматриваемой задачи о назначении являются найденные оптимальные значения переменных: x15 = 1, x23 = 1, x31 = 1, x44 = 1, x52 = 1. Остальные переменные равны 0. Найденному оптимальному решению соответствует минимальное значение целевой функции: fопт = 43.

В ходе работы также создан макрос для автоматического расчета целевой функции. Данный макрос автоматически задает ограничения для переменных и рассчитывает целевую функцию по заданным параметрам эффективности работы каждого из кандидатов. Для написания макроса используется язык программирования Visual Basic for Applications (VBA) [1–5, 7].


5 Описание разработки макроса

1. Очищаем ячейки с переменными:

  

Range("B9:F13").Select

   With Selection

      .Clear

   End With

2. Записываем уравнение целевой функции:

Range("G2").Select

ActiveCell.Formula = "=B2*B9+C2*C9+D2*D9+E2*E9+F2*F9+B3*B10+C3*C10+D3*D10+E3*E10+F3*F10+B4*B11+C4*C11+D4*D11+E4*E11+F4*F11+B5*B12+C5*C12+D5*D12+E5*E12+F5*F12+B6*B13+C6*C13+D6*D13+E6*E13+F6*F13"

3. Записываем ограничения для нужных ячеек:

   Range("B14").Select

   ActiveCell.Formula = "=Sum(B9:B13)"

   Range("C14").Select

         ActiveCell.Formula = "=Sum(C9:C13)"

   Range("D14").Select

   ActiveCell.Formula = "=Sum(D9:D13)"

   Range("E14").Select

   ActiveCell.Formula = "=Sum(E9:E13)"

   Range("F14").Select

   ActiveCell.Formula = "=Sum(F9:F13)"

   Range("G9").Select

   ActiveCell.Formula = "=Sum(B9:F9)"

   Range("G10").Select

   ActiveCell.Formula = "=Sum(B10:F10)"

   Range("G11").Select

   ActiveCell.Formula = "=Sum(B11:F11)"

   Range("G12").Select

   ActiveCell.Formula = "=Sum(B12:F12)"

   Range("G13").Select

   ActiveCell.Formula = "=Sum(B13:F13)"

 

   Dim wbSolv As Workbook

   Dim pth As String

4. Подключаем механизм поиска решений:

On Error Resume Next

Set wbSolv = Workbooks("Solver.xla")

   

On Error GoTo EH

If wbSolv Is Nothing Then

Set wbSolv = Workbooks.Open(AddIns("Поиск решения").FullName)

   End If

  

5. Инициализируем надстройку и подготавливаем данные для расчета:

   

Application.Run "Solver.xla!Auto_Open"

Application.Run "Solver.xla!SolverReset"

pth = "'[" + ThisWorkbook.Name + "]" + ThisWorkbook.ActiveSheet.Name + "'!"

6. Указываем ячейку для целевой функции:

Application.Run "Solver.xla!SolverOk", pth + "$G$2", 2, 0, pth + "$B$9:$F$13"

7. Указываем ограничения для нужных ячеек:

Application.Run "Solver.xla!SolverAdd", pth + "$B$9:$F$13", 5, "двоичное"

Application.Run "Solver.xla!SolverAdd", pth + "$B$14:$F$14", 2, "=1"

Application.Run "Solver.xla!SolverAdd", pth + "$G$9:$G$13", 2, "=1"

8. Устанавливаем формат ячеек:

   Range("B9:F13").Select

   Selection.NumberFormat = "0"

   Range("G2").Select

   Selection.NumberFormat = "0"

9. Запускаем оптимизатор:

   

   Application.Run "Solver.xla!SolverSolve", True

  ' Range("$B$9:$F$13").Select

   ''ActiveCell.for.FormatConditions = "#"

10. Сохраняем модель:

   Range("A16").Select

   ActiveCell.Value = "Сохранение оптимизационной модели"

   Range("A17").Select

   ActiveCell.Value = "Целевая функция"

   Range("A18").Select

   ActiveCell.Value = "Переменные"

   Range("A19").Select

   ActiveCell.Value = "Диапазон переменных"

   Range("A20").Select

   ActiveCell.Value = "Значения"

   Range("A21").Select

   ActiveCell.Value = "Ограничения"

   Range("A22").Select

   ActiveCell.Value = "Количество итераций"

   Application.Run "Solver.xla!SolverSave", pth + "$B$17"

   MsgBox "Расчет завершен!", vbInformation, "Сообщение"

   

   Exit Sub

   

EH:

   MsgBox Err.Source & "~" & Err.Description

End Sub

Выводы

В ходе выполнения курсовой работы были выполнены следующие задачи:

  1.  Реализована математическая модель задачи в MS Office Excel (заданы формулы ограничений и дальнейший расчет с помощью мастера поиска решений).
  2.  Созданы средства автоматического расчета по математической модели с использованием языка программирования VBA.
  3.  С использованием созданной в MS Office Excel программы проведен расчет задачи по исходным данным для заданного варианта.

Анализ найденного решения показывает, что при замещении вакантных должностей в рассматриваемой фирме следует на должность менеджера назначить сотрудника Дмитриева, на должность программиста – Сидорова, на должность бизнес-аналитика – Иванова, на должность маркетолога – Григорьева и, наконец, на должность руководителя проектов – Петрова. При этом общие затраты на обучение и стажировку сотрудников составят 43 тыс. рублей.

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


Список литературы

  1.  Леоненков А.В. Решение задач оптимизации в среде MS Excel. – СПб.: БХВ-Петербург, 2005. – 704 с.
  2.  Сборник задач по математике для втузов. Ч.4. Методы оптимизации. Уравнения в частных производных. Интегральные уравнения. Учебное пособие / Э.А.Вуколов, А.В.Ефимов А.В., В.Н.Земсков и др. – М.: Наука. Главная редакция физико-математической литературы, 1990. – 304 с.
  3.  Оптимизация технологических процессов в металлургии методом линейного программирования: Методические указания к лабораторной и самостоятельной работе студентов по дисциплине "Моделирование и оптимизация в технических системах" / Н.А.Спирин, В.В.Лавров. – Екатеринбург: УГТУ. 1995. – 36 с.
  4.  Оптимизация, идентификация и оценивание теплотехнических процессов в металлургии: Учебное пособие / Н.А.Спирин, В.В.Лавров, В.С.Шаврин. – Екатеринбург:УГТУ,1996.–188с
  5.  Лавров В.В. Информатика: Методические указания к выполнению курсовой работы для студентов металлургических специальностей / В.В.Лавров. – Екатеринбург: УГТУ–УПИ, 2010. – 21 с.
  6.  Лошкарев Н.Б. Указания к оформлению дипломных и курсовых проектов и работ. Методические указания / Н.Б.Лошкарев, А.Н.Лошкарев, Л.А.Зайнуллин. – Екатеринбург: ГОУ ВПО УГТУ – УПИ, 2007. – 49 с.
  7.  Орвис В. Excel для ученых, инженеров и студентов: пер. с англ. / В.Орвис. – К.: Юниор, 1999. – 528 с.


Приложение №1 Листинг макроса

Sub Оптимизация()

  'Очистить ячейки с переменными

  

   Range("B9:F13").Select

   With Selection

      .Clear

   End With

       

   ' Записать уравнение целевой функции

   Range("G2").Select

   ActiveCell.Formula = "=B2*B9+C2*C9+D2*D9+E2*E9+F2*F9+B3*B10+C3*C10+D3*D10+E3*E10+F3*F10+B4*B11+C4*C11+D4*D11+E4*E11+F4*F11+B5*B12+C5*C12+D5*D12+E5*E12+F5*F12+B6*B13+C6*C13+D6*D13+E6*E13+F6*F13"

   

   

   ' Записать ограничение 1

   Range("B14").Select

   ActiveCell.Formula = "=Sum(B9:B13)"

    ' Записать ограничение 2

   Range("C14").Select

   ActiveCell.Formula = "=Sum(C9:C13)"

   ' Записать ограничение 3

   Range("D14").Select

   ActiveCell.Formula = "=Sum(D9:D13)"

    ' Записать ограничение 4

   Range("E14").Select

   ActiveCell.Formula = "=Sum(E9:E13)"

    ' Записать ограничение 5

   Range("F14").Select

   ActiveCell.Formula = "=Sum(F9:F13)"

   ' Записать ограничение 6

   Range("G9").Select

   ActiveCell.Formula = "=Sum(B9:F9)"

   ' Записать ограничение 7

   Range("G10").Select

   ActiveCell.Formula = "=Sum(B10:F10)"

   ' Записать ограничение 8

   Range("G11").Select

   ActiveCell.Formula = "=Sum(B11:F11)"

   ' Записать ограничение 9

   Range("G12").Select

   ActiveCell.Formula = "=Sum(B12:F12)"

   ' Записать ограничение 10

   Range("G13").Select

   ActiveCell.Formula = "=Sum(B13:F13)"

 

   Dim wbSolv As Workbook

   Dim pth As String

   ' подключить "Поиск решений"

   On Error Resume Next

   Set wbSolv = Workbooks("Solver.xla")

   

   On Error GoTo EH

   If wbSolv Is Nothing Then

       Set wbSolv = Workbooks.Open(AddIns("Поиск решения").FullName)

   End If

  

   ' инициализировать надстройку

   Application.Run "Solver.xla!Auto_Open"

   Application.Run "Solver.xla!SolverReset"

   ' подготовить данные для расчета

   pth = "'[" + ThisWorkbook.Name + "]" + ThisWorkbook.ActiveSheet.Name + "'!"

 ' Ввод целевой функции, экстремум и переменные

   Application.Run "Solver.xla!SolverOk", pth + "$G$2", 2, 0, pth + "$B$9:$F$13"

    ' Ввод ограничения на неотрицательность

   

   Application.Run "Solver.xla!SolverAdd", pth + "$B$9:$F$13", 5, "двоичное"

   ' Ввод ограничения 1

   Application.Run "Solver.xla!SolverAdd", pth + "$B$14:$F$14", 2, "=1"

   ' Ввод ограничения 2

   Application.Run "Solver.xla!SolverAdd", pth + "$G$9:$G$13", 2, "=1"

   ' Формат ячеек

   Range("B9:F13").Select

   Selection.NumberFormat = "0"

   Range("G2").Select

   Selection.NumberFormat = "0"

   

   ' Запустить оптимизатор

   Application.Run "Solver.xla!SolverSolve", True

  ' Range("$B$9:$F$13").Select

   ''ActiveCell.for.FormatConditions = "#"

   ' Сохранить модель

   Range("A16").Select

   ActiveCell.Value = "Сохранение оптимизационной модели"

   Range("A17").Select

   ActiveCell.Value = "Целевая функция"

   Range("A18").Select

   ActiveCell.Value = "Переменные"

   Range("A19").Select

   ActiveCell.Value = "Диапазон переменных"

   Range("A20").Select

   ActiveCell.Value = "Значения"

   Range("A21").Select

   ActiveCell.Value = "Ограничения"

   Range("A22").Select

   ActiveCell.Value = "Количество итераций"

   Application.Run "Solver.xla!SolverSave", pth + "$B$17"

   MsgBox "Расчет завершен!", vbInformation, "Сообщение"

   

   Exit Sub

   

EH:

   MsgBox Err.Source & "~" & Err.Description

End Sub


 

А также другие работы, которые могут Вас заинтересовать

53218. Линейная функция и её график 101 KB
  Дать учащимся представление о линейной функции вида у=kх в рассмотреть частный случай у= kх; формировать умение строить график линейной функции и выяснять отдельные характеристики линейной по её графику; развивать логическое мышление вычислительные навыки по табличным значениям находить соответствующие точки на координатной плоскости; развивать самообразовательную и информационную компетентность. Выполнение математического диктанта Вариант 1...
53219. Простейшие преобразования графиков функций 54.5 KB
  І красные Графики функций y=fxn n 0 Каждый учасник получает задание построить график функции: №1. х Каждый ученик построил график своей функции дома. Группа делает вывод преобразования графика своей функции. 15 минут работы – каждый ученик рассказывает построение графика своей функции.