37363

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

Курсовая

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

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

Русский

2013-09-24

199.52 KB

7 чел.

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

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

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

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

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

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

________________  _________

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

________________  _________

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

________________  _________

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

«___» ___________ 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


 

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

45245. Брифинг как информационная ПР - акция: цели, аудитория, методика подготовки 28 KB
  Брифинг как информационная ПР акция: цели аудитория методика подготовки Брифинг это плановое мероприятие которое проводится с регулярной периодичностью и посвящается распространению текущей информации о деятельности организации или компании. Например брифинги регулярно проводит министерство иностранных дел. На брифингах министерства внутренних дел проходящих раз в неделю можно услышать официальную статистику ДТП раскрытых преступлений узнать о планируемых профилактических мерах по борьбе с организованной преступностью. Еще одна...
45246. Методика подготовки и проведения пресс-конференций 34.5 KB
  Методика подготовки и проведения пресс-конференций Тема: социально-значимая и актуальная не коммерческая дискуссионная диалог спор. Пресс-конференция проводится по мере накопления информации или при наличии глобальных новостей. Мотивация: мотивация организации пресс-конференции; мотивация журналистов статусные персоны корпоративность присутствие всех СМИ наличие компромата самопрезентация. Прессконференция проводится при хорошем знании журналистов критическая масса 30 40.
45247. Корпоративные коммуникации и материалы корпоративной прессы: виды, функции, принципы организации 34 KB
  В результате оглашения конкретных цифр и фактов сотрудники получают полное представление о готовых итогах развития и перспективах роста компании. Такой отчет может содержать: письмо руководителя в котором выражается благодарность сотрудникам и описывается работа компании ее основные достижения в течение года; отчет об использовании фондов. Этот документ часто оформляется в виде графиков и диаграмм характеризующих использование организацией поступивших средств; анализ финансового положения предприятия; отчет об участии компании в...
45248. Деятельность корпоративной радиостанции и ТВ-центра: приоритеты, аудитория, содержание коммуникации 26.5 KB
  Обычно это организаци-ипартнеры со схожими корпоративными стандартами и принципами. Приоритеты: Главным приоритетом в деятельности корпоративной радиостанции и ТВ центра является формирование корпоративной культуры организации. Сообщения транслируемые по радио и ТВ должны отвечать на следующие вопросы: что происходит в организации почему это происходит в организации что должно произойти в организации Аудитория: Обычно эти самые радиостанции пли ТВ центры находятся на территории самой компании и вещают только на их территории то...
45249. Цели и содержание деятельности корпоративного сайта 28.5 KB
  Цели которые преследуют создатели сайтов: улучшение имиджа и поднятие престижа компании; продвижение торговой марки; доступность информации о продуктах и ценах для клиентов; поддержка дилерской сети доступность информации о продуктах и ценах для дилеров; прямая продажа продукции в Internet организация виртуального магазина; доступность внутренней информации для сотрудников работающих вне офиса; другое. служит дополнительным элементом фирменного стиля; укрепляет имидж и престиж поскольку в российских условиях не каждая...
45250. Информационное, организационное и финансовое обеспечение ПР- акции 28 KB
  Информационное организационное и финансовое обеспечение ПР акции 1. Формы опосредованной маркетинговой коммуникации нередко играют важнейшую роль в обеспечении акции. 3 этап формирование плана в основе которого лежат конкретные ПР-акции которые должны быть спланированы по времени и иметь свое отражение в СМИ репортажи интервью аналитический обзор и т. Организационное обеспечение также предполагает написание сценарного плана акции который включаете: а открытие акции включая представления гостей б содержание и последовательность...
45251. Представительские акции в ПР-технологиях: функции, формы, этапы подготовки 22.5 KB
  Представительский обед 7-8 часов вечера прибытие по приглашению не более чем за 10 минут до начала не опаздывать; 1 банкетный стол или несколько; приглашение с указанием места; правила рассадки дамы и кавалеры сидят последовательно; приветственные тосты и рассадка от самых серьезных по убыванию. Время проведения около часа с переменой блюд: горячий стол музыкальная пауза сладкий стол кофе уход. Фуршет перед началом возможны некоторые объявления пока не сели за стол.
45252. Презентация как форма PR-акции: функции, этапы разработки и реализации 33 KB
  Полная готовность к проведению презентации 11 часов на час раньше назначенного времени остается время на устранение мелких неполадок и недочетов. Приглашенных встречает первое лицо и ведущий презентации. Модератор ведущий выступает с предварительной речью приветствие и обращение к аудитории оглашение тем презентации представление участников президиума оглашение регламента проведения презентации представительский блок. Примечание: Все участники президиума имеют на руках специально подготовленные папки с материалами презентации...
45253. Ярмарка как форма ПР акции: функции, классификация, этапы разработки и реализации 29.5 KB
  Ярмарки - это регулярно организуемые оптово-розничные рыночные мероприятия с ограниченным временем проведения где значительное количество экспонентов реализуют характерные услуги и товары одной или нескольких отраслей. Ярмарки имеют множество сходных признаков и характеристик деятельности с выставками однако на ярмарках производится не только демонстрация выставочных образцов но и прямая продажа продукции посетителям как оптом так и в розницу. Ярмарки зародились как мероприятие рыночного характера основной целью которого являлся сбыт....