Решение задачи оптимизации расходов предприятия

Лабораторная работа № 32

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

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

Понедельник	Вторник	Среда	Четверг	Пятница	Суббота	Воскресенье

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

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

1. Запустите программу Excel и откройте рабочую книгу. Присвойте имя новому рабочему листу Заработная плата.
2. В первую строку рабочего листа введите заголовки столбцов: в ячейку А1 – Выходные дни, в ячейку В1 – Работники, в ячейки D1-J1 – дни недели (Пн, Вт, Ср, Чт, Пт, Сб, Вс).
3. В столбец А введите заголовки строк. В ячейках А2-А8 введите разрешенные пары выходных (от Понедельник,вторник до Воскресенье, понедельник). В ячейки А9 и А10 введите одиночные выходные (Суббота и Воскресенье). В ячейке А12 укажите заголовок Всего. Ячейка А15 должна содержать фразу Недельная оплата работника, а ячейка А16 – текст Общий недельный расход.
4. Введите фиксированные данные. В ячейке В13 напишите Всего требуется и введите в ячейках D13-J13 требования к минимальному числу работников согласно заданной таблице. В ячейку В15 введите фиксированную недельную оплату – 500 рублей.
5. В ячейках диапазона D2:J10 укажите 1. если для данного расписания день является рабочим, и 0 – если выходным.
6. В ячейки В2-В10 введите нулевые (или иные произвольные) значения. Предполагается, что в дальнейшем эти значения будут вычислены автоматически.
7. В ячейку D12 введите следующую формулу:

=D8*$B8+D2*$B2+D3*$B3+D4*$B4+D5*$B5+D6*$B6+D7*$B7+D10*$B10+D9*$B9.

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

1. Методом заполнения скопируйте только что введенную формулу в ячейки диапазона Е12 – J12.
2. В ячейку В12 введите формулу =СУММ(В2:В8)+СУММ(В9:В10)*1,4. Для ввода имени функции используйте строку формул или Мастер формул. По этой формуле вычисляется «приведенное» число сотрудников с учетом увеличенной заработной платы при шестидневной рабочей недели.
3. В ячейку В16 введите формулу для исчисления итоговых расходов на заработную плату за неделю:=В15*В12. Именно это значение необходимо свести к минимуму.
4. Запустите надстройку Поиск решений (Сервис ®Поиск решения). Если пункт Поиск решения в меню отсутствует, значит, необходимо подключить эту надстройку, дав команду Сервис ®Надстройки и установив соответствующий флажок.
5. В поле Установить целевую ячейку выберите ячейку В16.
6. Для переключателя Равной выберите вариант минимальному значению.
7. Щелкните на поле Ограничения и затем- на кнопке Добавить.
8. Щелкните на поле Ссылка на ячейку и выберите диапазон В2:В10. В качестве ограничения выберите вариант цел. Щелкните на кнопке Добавить.
9. В поле Ссылка на ячейку выберите диапазон В2:В8. Выберите операцию >= и значение 1 в поле Ограничение. Аналогичным образом для диапазона В9:В10 выберите значение >=0.
10. В поле Ссылка на ячейку выберите диапазон D12:J12. Выберите операцию >=. В поле Ограничение выберите диапазон D13:J13. Этим задается ограничение на минимальное число работников. Щелкните на кнопке ОК.
11. Щелкните на кнопке Выполнить, чтобы провести поиск оптимального варианта. По окончании расчета щелкните на кнопке ОК, чтобы сохранить найденное решение.
12. Сохраните рабочую книгу.

Дата добавления: 2014-12-25