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

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

Математическая модель этой задачи, записанная в обычной математической форме:

Целевая функция

S = 60*x1+70*x2+120*x3+130*x4         → max

Система ограничений

1*x1+1*x2+1*x3+1*x4 ≤ 16

6*x1+5*x2+4*x3+3*x4 ≤ 110

4*x1+6*x2+10*x3+13*x4 ≤ 150

x1, x2, x3, x4 ≥ 0 - целые

В Excel математическая модель может быть представлена, в лучшем случае, в виде таблицы чисел. Размещение данных в Excel оформляется в свободном порядке, формы ввода не предусмотрены.

Рис.1.1. Модуль «Поиск решения» программы MS Excel

Далее, переходим к решению. Выбираем в меню «Сервис | Поиск решения». Открывается диалоговое окно «Поиск решения». Здесь указывается ячейки целевой функции, переменных и устанавливаются ограничения исходя из системы ограничений. Можно начать решение, или установить «параметры» решения (рис.1.2).

Рис.1.2. Окно «Параметры поиска решения» модуля «Поиск решения»

Здесь устанавливаем «параметры» задачи - выбираем «линейная модель», «неотрицательные значения» и «показывать результаты итераций».

«Линейная модель» - служит для ускорения поиска решения линейной задачи оптимизации. «Показывать результаты итераций» - служит для приостановки поиска решения для просмотра результатов отдельных итераций.

По-умолчанию выбран метод поиска «Ньютона», есть возможность выбрать «сопряженных градиентов».

«Метод Ньютона» - реализация квазиньютоновского метода, в котором запрашивается больше памяти, но выполняется меньше итераций, чем в методе сопряженных градиентов.

«Метод сопряженных градиентов» - реализация метода сопряженных градиентов, в котором запрашивается меньше памяти, но выполняется больше итераций, чем в методе Ньютона. Данный метод следует использовать, если задача достаточно велика и необходимо экономить память, а также если итерации дают слишком малое отличие в последовательных приближениях.

Причем здесь нет возможности выбрать ни графический симплекс-метод, ни симплекс-таблиц. Применение метода позволяющего найти целочисленное решение определяется лишь добавление условия на каждую переменную – «целое».

Такой подход к решению задач хорошо подходит для проведения практических расчетов, когда важен лишь результат решения, а не сам процесс получения оптимального решения.

Если Вам важен сам процесс решения задачи с применением какого-либо метода решения, модуль «Поиск решения» может быть использован лишь для сравнения результатов решения задачи в качестве проверки правильности применения методов. Тут следует отметить, что некоторые задачи могут иметь несколько вариантов оптимального решения. Так, например, транспортная задача (частный случай задачи ЛП, и может быть представлена в виде целевой функции и системы ограничений) может иметь два (и более) оптимальных плана перевозок с одинаковой стоимостью.