Решение задачи с помощью надстройки Excel Поиск решения

Поставить курсор мыши в ячейку E3 и нажать на левую кнопку мы­ши: туда после решения задачи будет занесено вычисленное значение целевой функции.

Войти в меню Сервис, выбрать в нем Поиск решения и щелкнуть на нем левой кнопкой мыши. На экране появится диалоговое окно Поиск решения (рис. 1.2). В поле Установить целевую ячейку занести ячейку с адресом $E$3. Для этого проще всего установить курсор мыши внутрь поля Установить целевую ячейку, щелкнуть в нем левой кнопкой мы­ши, а затем - мышью на ячейке E3. Именно в ячейке E3 будет вычислено значение целевой функции.

Поскольку ищется максимум целевой функции, то после слова Рав­ной выделим Максимальному значению, щелкнув в кружочке мышью.

В поле Изменяя ячейки занести диапазон ячеек $А$2:$D$2, так как именно эти ячейки отведены под значения вычисляемых переменных х₁, х₂, х₃ и х₄. Для этого поставить курсор в поле Изменяя ячейки и щелкнуть на нем левой кнопкой мыши. Затем поставить курсор на ячейку А2 и при нажатой левой кнопке мыши перевести («протащить») курсор на ячейку D2. В поле Изменяя ячейки появится необходимый диапазон ячеек.

Рис.1.2. Фрагмент Листа Excel с диалоговым окном Поиск решения

В поле Ограничения занести ограничения (2)-(4). Для этого необ­ходимо щелкнуть мышью на кнопке Добавить диалогового окна Поиск решения. Появится диалоговое окно Добавление ограничения (рис. 1.3).

Занесем ограничение (2).

В поле Ссылка на ячейку поставить курсор и щелкнуть на нем ле­вой кнопкой мыши, затем поставить курсор на ячейку E4, где задана формула ограничения (2), и щелкнуть на нем левой кнопкой мыши. В поле Ссылка на ячейку появится адрес ячейки E4.

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

В среднем поле щелкнуть на кнопке справа от этого поля (со стре­лочкой) и выбрать соответствующий знак неравенства. В среднем поле появится знак ≤.

В поле Ограничение занести правую часть ограничения, располо­женную в ячейке F4. Для этого поставить курсор в поле Ограничение и щелкнуть на нем левой кнопкой мыши. Затем поставить курсор на ячейку F4 и щелкнуть на ней левой кнопкой мыши. В поле Ограничение поя­вится адрес ячейки F4.

После проделанных действий щелкнуть на кнопке ОК. Попадаем снова в поле Поиск решения. Повторяя описанные выше действия, зано­сим остальные ограничения (рис. 1.2).

Снова в поле Поиск решения (рис. 1.2). Щелкнуть мышью на кноп­ке Параметры.

На экране появится диалоговое окно Параметры поиска решения. В этом окне (рис. 1.4) устанавливаются параметры поиска решения. Здесь отметить квадратики Линейная модель, Неотрицательные значения, Автоматическое масштабирование. Щелкнуть мышью на кнопке ОК.

Рис. 1.4. Диалоговое окно Параметры поиска решения

Снова попадаем в диалоговое окно Поиск решения. В этом окне (рис. 1.2) щелкнем левой кнопкой мыши на кнопку Выполнить. На экран выводится окно Результаты поиска решения (рис. 1.5). В диалоговом окне (если решение найдено) Результаты поиска решения появляется надпись (рис. 1.5) Решение найдено. Все ограничения и условия оптимальности выполнены. Щелкнуть левой кнопкой мыши на кнопке ОК.

Одновременно на Листе экрана также появляются результаты реше­ния задачи (рис. 1.6): в столбце Ограничения выводятся их рассчитан­ные значения. В строке переменные - значения рассчитанных опти­мальных переменных х₁, х₂ х₃ и х₄. В строке Целевая функция в ячейке E3 - рассчитанное значение целевой функции.

Итак, найдено оптимальное решение: х₁ = 0, х₂ = 30, х₃= 10, х₄ = 0, при этом максимальная стоимость выпущенной продукции равна F_max = 150.

Рис. 1.5. Диалоговое окно Результаты поиска решения

В окне Результаты поиска решения (рис. 1.5) содержится Тип от­чета: Результаты, Устойчивость, Пределы. Для получения всех видов отчетов надо щелкнуть левой кнопкой мыши на каждом из них - соответ­ствующие строчки будут закрашены, - а затем на кнопке ОК. Отчеты отображаются в нижней строке Листа на экране Excel. Для их вызова необходимо щелкнуть на соответствующем отчете.

Рис.1.6. Результаты решения, расположенные на Листе экрана

В отчете Результаты приводятся исходные и конечные значения целевой и изменяемых ячеек, а также данные о выполнении огра­ничений (рис. 1.7).

В отчете Устойчивость приводятся границы устойчивости неиз­вестных задачи - допустимое увеличение и уменьшение коэффициентов целевой функции, границы устойчивости двойственных оценок. В графе Нормированная стоимость элемент этой графы показывает, на сколько уменьшится значение функции, если в решении переменную увеличить на единицу.

В отчете Пределы показаны нижние и верхние пределы изменения неизвестных и значения целевой функции при этих изменениях.

Рис.1.7. Содержание отчета по результатам

В отчете по результатам содержатся оптимальные значения переменных х₁, х₂ х₃ и х₄, которые соответственно равны 0, 30, 10 и 0, значение целевой функции – 150, а также левые части ограничений.

Содержание остальных отчетов будет рассмотрено в других лабораторных работах.