Решение задачи лп в среде электронных таблиц
Рассмотрим решение задачи в среде ЭТ с помощью надстройки «Поиск решения». Результаты решения и размещение информации представлено в таблице 3.
Таблица 3. Результаты решения и размещение информации
Анализ результатов оптимизации. Для получения максимальной прибыли в размере 8400 грн. необходимо изготовить и реализовать на рынке 12 деталей первого вида и 6 деталей второго вида. Аналогичные результаты получены графическим методом. Ресурсы долбежного станка использованы не полностью.
Технология работы с надстройкой «Поиск решения»
Задачи линейного, нелинейного и целочисленного программирования очень эффективно решать с помощью надстройки «Поиск решения» приложения Excel. Рассмотрим технологию работы при решении задачи примера 1.
Загрузить программу Excel .
Ввести исходную информацию (см. таблицу 3):
обозначение неизвестных в диапазон ячеек А1:B1;
коэффициенты при неизвестных (управляемых переменных) левой части ограничений в диапазон ячеек А4:B7;
коэффициенты при неизвестных целевой функции в диапазон ячеек А9:B9;
значения правых частей ограничений в диапазон ячеек D4:D7.
Выделить ячейки для результатов решения задачи:
ячейки А2:B2 для значений управляемых переменных (в таблице 3 в эти ячейки записаны результаты решения, первоначально значения управляемых переменных равны нулю);
ячейки E4:E7 для значений левой части ограничений;
ячейку Е9 для значения целевой функции.
Ввести формулы, по которым считаются левые части ограничений и целевая функция, в соответствующие ячейки результатов решения. Формулы эти представляют собой суммы произведений, поэтому для их задания эффективно использовать функцию =СУММПРОИЗВ(массив 1; массив 2) мастера функций. Щелкнуть на ячейке, в которую будет вводиться формула, например Е4, затем на кнопке
стандартной панели инструментов, в
списке категории диалогового окна
выбрать математические, в списке
функции – СУММПРОИЗВ, щелкнуть на
кнопке ОК. В диалоговом окне
CУММПРОИЗВ в поле
массив 1 ввести диапазон ячеек
А4:В4, щелкнуть в поле массив 2 и
ввести диапазон ячеек, выделенных под
значения неизвестных, $А$2:$В$2 (абсолютные
адреса ячеек не изменяются при копировании
формул), затем ОК. В ячейку Е4 запишется
число 0 (в ячейке E4 таблицы
3 записано значение ограничения,
полученное в результате решения задачи).
Эту формулу скопировать в диапазон
ячеек Е5:Е7. Аналогично записать формулу
в ячейку Е9.Установить курсор на ячейку Е9, в которую будет записано после оптимизации значение целевой функции, войти в меню Сервис и выбрать надстройку Поиск решения. В диалоговом окне Поиск решения (рис. 3):
в поле установить целевую ячейку должен быть адрес Е9;
установить переключатель Равной в положение максимальному значению;
в поле Изменяя ячейки ввести диапазон ячеек А2:В2.
Ввести ограничения:
щелкнуть на кнопке Добавить диалогового окна Поиск решения;
в диалоговом окне Добавление ограничения (рис.4) в поле Ссылка на ячейку ввести адреса Е4:Е7, переключатель остается в этом положении, щелкнуть в поле Ограничение и ввести адреса D4:D7;
Рис. 3. Диалоговое окно надстройки «Поиск решения»
щелкнуть на кнопке Добавить диалогового окна Добавление ограничений и ввести ограничения, указывающие на целочисленность определяемых переменных, путем установки переключателя в положение цел: А2:В2 целое;
после ввода всех ограничений щелкнуть на кнопке ОК.
Щелкнуть на кнопке Параметры диалогового окна Поиск решения. В диалоговом окне Параметры установить флажки в поля Линейная модель и Неотрицательные значения. Щелкнуть на кнопке ОК.
Рис.4. Диалоговое окно «Добавление ограничения»
Щелкнуть на кнопке Выполнить диалогового окна Поиск решения. После окончания расчета появится диалоговое окно Результаты поиска реше
Щелкнуть на кнопке ОК. Результаты решения появятся в ячейках А2:В2, Е4:Е7 и Е9.