Математическая постановка задачи

Обозначим месячный выпуск продукции Стенок через шт., Шкафов — шт., Гарнитуров — шт.

Тогда расход ресурсов за месяц составит:

древесины — м³,

стекла — м²,

зеркал — м²,

Трудозатраты на производство продукции равны: чел.-дней

 

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

Добавим тривиальные условия (неотрицательность и целочисленность):

, ,

— целые

Целевая функция (прибыль от продажи продукции за месяц, которую надо максимизировать):

 

Решение задачи в Microsoft Excel

Покажем, как лучше подготовить данные для решения задачи средствами Microsoft Excel, в таблице 1.

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

В столбцах H,I,J,K,L, поставлены функции, готовящие вычисление суммарных затрат ресурсов и суммарной прибыли (обозначенных символом (автосуммы), который необходимо поставить в соответствующие ячейки). В последней строке этих столбцов стоят величины соответствующих запасов. В ячейке L6 находится подлежащая максимизации величина общей прибыли за месяц. Все величины таблицы или постоянны (удельные расходы и запасы), или зависят (то есть являются функциями) от искомых величин выпусков в ячейках ВЗ, В4, В5.

При заполнении таблицы необходимо использовать средства автоматизации ввода информации Excel (абсолютной адресацией).

Внимание: если все формулы введены правильно, то внешне правая часть таблицы (без формул) должна совпадать с левой (с формулами)

После того, как информация в таблицу занесена, можно приступать к решению задачи средствами надстройки «ПОИСК РЕШЕНИЯ».

В командном меню выбираем пункт СЕРВИС►ПОИСК РЕШЕНИЯ. После запуска этой надстройки на экране появляется диалоговое окно «Поиск решения» (если такой строчки в меню Сервис нет, то ее надо доустановить СЕРВИС►НАДСТРОЙКИ, поставить галочку у строчки Поиск решения и OK)

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

Установим целевую ячейку с суммарной прибылью L6

Переключатель Равной должен стоять в позиции «максимальному значению»

В поле Изменяя ячейки установим диапазон В3:В5

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

В поле Ссылка на ячейку укажем диапазон ячеек с суммами H6:K6, оставим знак неравенства <=, переведем курсор в поле Ограничение, укажем диапазон ячеек с запасами H7:K7, так как добавлены еще не все условия нажмем кнопку Добавить (таким образом мы ввели ограничение на ресурсы)

Введем ограничения на переменные. В поле Ссылка на ячейку укажем диапазон ячеек B3:B5. Поменяем знак неравенства <= на >=, открыв соответствующий список рядом с полем знака неравенства, и в поле Ограничение поставим с клавиатуры число 0 (требование неотрицательности введено), так как добавлены еще не все условия нажмем кнопку Добавить

Введем условие целочисленности. В поле Ссылка на ячейку укажем диапазон ячеек B3:B5 (для этого условия диапазон ячеек должен совладать диапазоном ячеек, указанном в поле Изменяя ячейки!). Поменяем знак неравенства <= на цел, открыв соответствующий список рядом с полем знака неравенства. Поле Ограничение заполнится автоматически словом «целое», так как добавлены все условия нажмем кнопку OK (завершим ввод ограничений)

Процесс решения задачи запускается кнопкой Выполнить. По завершении процесса появляется окно Результаты поиска решения, предлагающее сохранить найденное решение и выбрать различные типы отчетов по результатам решения задачи. Выберем Тип отчета Результаты. Нажмем OK — задача решена (произошли изменения и пересчет всех формул)