Упражнение 13.11. Решение задачи оптимизации расходов предприятия 30 мин

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

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

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

1. Запустите программу Excel и откройте рабочую книгу book.xls.

2. Создайте новый рабочий лист (Вставка > Лист), дважды щелкните на его ярлычке и присвойте ему имя Заработная плата.

3. В первую строку рабочего листа введите заголовки столбцов: в ячейку А1 — Выходные дни, в ячейку В1 — Работники, в ячейки D1-J1 — дни недели (Пн, Вт, Ср,Чт, Пт, Сб, Вс).

4. В столбец А введите заголовки строк. В ячейках А2-А8 введите разрешенные пары выходных (от Понедельник, вторник до Воскресенье, понедельник). В ячейки А9 и А10 введите одиночные выходные (Суббота и Воскресенье). В ячейке А12 укажите заголовок Всего. Ячейка А15 должна содержать фразу Недельная оплата работника, а ячейка А16 — текст Общий недельный расход.

5. Введите фиксированные данные. В ячейке В 13 напишите Всего требуется и введите в ячейках D13-J13 требования к минимальному числу работников согласно заданной таблице. В ячейку В 15 введите фиксированную недельную оплату — 500 рублей.

6. В ячейках диапазона D2:J10 укажите 1, если для данного расписания день является рабочим, и 0 — если выходным.

7. В ячейки В2-В10 введите нулевые (или иные произвольные) значения. Предполагается, что в дальнейшем эти значения будут вычислены автоматически.

8. В ячейку D12 введите следующую формулу: =D8 *$B8+D2*$B2+D3*$B3+D4*$B4+D5*$B5+D6*$B6+D7*$B7+D10*$B10+D9*$B9. По ней рассчитывается число работников, занятых в понедельник. Элементы абсолютной адресации использованы для того, чтобы формулу можно было копировать.

9. Методом заполнения скопируйте только что введенную формулу в ячейки диапазона E12-J12.

10. В ячейку В12 введите формулу =СУММ(В2:В8)+СУММ(В9:В10)*1,4. Для ввода имени функции используйте строку формул или Мастер формул. По этой формуле вычисляется “приведенное” число сотрудников с учетом увеличенной заработной платы при шестидневной рабочей неделе.

11. В ячейку В16 введите формулу для исчисления итоговых расходов на заработную плату за неделю: =В 15*В 12. Именно это значение необходимо свести к минимуму.

12. Запустите надстройку Поиск решения (Сервис > Поиск решения). Если пункт Поиск решения в меню отсутствует, значит, необходимо подключить эту надстройку, дав команду Сервис > Надстройки и установив соответствующий флажок.

13. В поле Установить целевую ячейку выберите ячейку В16.

14. Для переключателя Равной выберите вариант минимальному значению.

15. Щелкните на поле Ограничения и затем — на кнопке Добавить.

16. Щелкните на поле Ссылка на ячейку и выберите диапазон В2:В10. В качестве ограничения выберите вариант цел. Щелкните на кнопке Добавить.

17. В поле Ссылка на ячейку выберите диапазон В2:В8. Выберите операцию >= и значение 1 в поле Ограничение. Аналогичным образом для диапазона В9:В10 выберите значение >=0.

18. В поле Ссылка на ячейку выберите диапазон D12J12. Выберите операцию >=. В поле Ограничение выберите диапазон D13:J13. Этим задается ограничение на минимальное число работников. Щелкните на кнопке ОК.

19. Щелкните на кнопке Выполнить, чтобы провести поиск оптимального варианта. По окончании расчета щелкните на кнопке ОК, чтобы сохранить найденное решение.

20. Сохраните рабочую книгу book.xls.

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