Технология работы с надстройкой «Поиск решения»

Задачи линейного, нелинейного и целочисленного программирования очень эффективно решать с помощью надстройки «Поиск решения» приложения Excel. Рассмотрим технологию работы при решении задачи примера 1.

1. Загрузить программу Excel.

2. Ввести исходную информацию (см. таблицу 3):

· обозначение неизвестных в диапазон ячеек А1:B1;

· коэффициенты при неизвестных (управляемых переменных) левой части ограничений в диапазон ячеек А4:B7;

· коэффициенты при неизвестных целевой функции в диапазон ячеек А9:B9;

· значения правых частей ограничений в диапазон ячеек D4:D7.

3. Выделить ячейки для результатов решения задачи:

· ячейки А2:B2 для значений управляемых переменных (в таблице 3 в эти ячейки записаны результаты решения, первоначально значения управляемых переменных равны нулю);

· ячейки E4:E7 для значений левой части ограничений;

· ячейку Е9 для значения целевой функции.

4. Ввести формулы, по которым считаются левые части ограничений и целевая функция, в соответствующие ячейки результатов решения. Формулы эти представляют собой суммы произведений, поэтому для их задания эффективно использовать функцию = СУММПРОИЗВ(массив 1; массив 2) мастера функций. Щелкнуть на ячейке, в которую будет вводиться формула, например Е4, затем на кнопке стандартной панели инструментов, в списке категории диалогового окна выбрать математические, в списке функции – СУММПРОИЗВ, щелкнуть на кнопке ОК. В диалоговом окне CУММПРОИЗВ в поле массив 1 ввести диапазон ячеек А4:В4, щелкнуть в поле массив 2 и ввести диапазон ячеек, выделенных под значения неизвестных, $А$2:$В$2 (абсолютные адреса ячеек не изменяются при копировании формул), затем ОК. В ячейку Е4 запишется число 0 (в ячейке E4 таблицы 3 записано значение ограничения, полученное в результате решения задачи). Эту формулу скопировать в диапазон ячеек Е5:Е7. Аналогично записать формулу в ячейку Е9.

5. Установить курсор на ячейку Е9, в которую будет записано после оптимизации значение целевой функции, войти в меню Сервис и выбрать надстройку Поиск решения. В диалоговом окне Поиск решения (рис. 3):

· в поле установить целевую ячейку должен быть адрес Е9;

· установить переключатель Равной в положение максимальному значению;

· в поле Изменяя ячейки ввести диапазон ячеек А2:В2.

6. Ввести ограничения:

· щелкнуть на кнопке Добавить диалогового окна Поиск решения;

· в диалоговом окне Добавление ограничения (рис.4) в поле Ссылка на ячейку ввести адреса Е4:Е7, переключатель <= остается в этом положении, щелкнуть в поле Ограничение и ввести адреса D4:D7;

 

Рис. 3. Диалоговое окно надстройки «Поиск решения»

 

· щелкнуть на кнопке Добавить диалогового окна Добавление ограничений и ввести ограничения, указывающие на целочисленность определяемых переменных, путем установки переключателя <= в положение цел: А2:В2 целое;

· после ввода всех ограничений щелкнуть на кнопке ОК.

7. Щелкнуть на кнопке Параметры диалогового окна Поиск решения. В диалоговом окне Параметры установить флажки в поля Линейная модель и Неотрицательные значения. Щелкнуть на кнопке ОК.

 

 

Рис.4. Диалоговое окно «Добавление ограничения»

 

8. Щелкнуть на кнопке Выполнить диалогового окна Поиск решения. После окончания расчета появится диалоговое окно Результаты поиска реше

9. Щелкнуть на кнопке ОК. Результаты решения появятся в ячейках А2:В2, Е4:Е7 и Е9.

 

Работа 5. ТРАНСПОРТНАЯ ЗАДАЧА

Цель работы. Освоить методику составления математической модели транспортной задачи и методы ее решения.

Содержание и последовательность выполнения работы

1. Изучить постановку транспортной задачи и ее решение методом потенциалов.

2. Исходные данные своего варианта представить таблицей.

3. Составить математическую модель задачи.

4. Подготовить размещение информации на рабочем листе электронной таблицы.

5. Ввести исходную информацию в память компьютера и получить оптимальный план перевозок, используя надстройку «Поиск решения».

6. Решить задачу методом потенциалов.

7. Выполнить анализ результатов оптимизации.

8. Оформить и защитить отчет.

 

Краткие сведения из теории

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

Сводка исходных данных приведена в таблице 1.

Таблица 1. Сводка исходных данных