Решение задачи линейного программирования в Microsoft Excel

Для решения рассматриваемой задачи с помощью программы MS Excel необходимо выполнить следующие действия.

1. Осуществляем ввод данных в таблицу Excel согласно схеме

Для переменных задачи x1 иx2 отведены ячейки B3 и C3. Эти ячейки называются рабочими или изменяемыми ячейками. В изменяемые ячейки значения не заносятся, в результате решения задачи в этих ячейках будет отражено оптимальное значение переменной.

В ячейку D4 необходимо ввести формулу для вычисления целевой

функции задачи (дохода) F = 15x1 + 10x2. Формула в алфавите языка

Excel имеет вид: =B4*B3+C4*C3.

Израсходованное количество ресурса Электроэнергия составляет

3x1 + 7x2. В ячейку D7 (расход электроэнергии) вводится формула

=B7*$B$3+C7*$C$3.

Аналогично вводятся формулы расхода остальных ресурсов, а

именно Сырья иОборудования. В ячейку D8 (расход сырья) вводится

формула =B8*$B$3+C8*$C$3. В ячейку D9 (расход оборудования) вводится

формула =B9*$B$3+C9*$C$3.

В результате страница примет вид:

В меню Сервис выбираем процедуру Поиск решения. В появившемся

окне нужно установить адрес целевой ячейки D4, значение

целевой ячейки, равное максимальному значению, адреса изменяемых

ячеек B3:C3.

Рис. Окно средства Поиск решения

Чтобы ввести ограничения задачи, нажать кнопку Добавить. В

появившемся диалоговом окне слева ввести адрес D7 (израсходованное

количество Электроэнергии), затем выбрать знак ≤ и в правой части

количество этого ресурса, (или адрес ячейки E7). После ввода

нажать кнопку Добавить и аналогично ввести второе и третье

ограничения, а также условие положительности изменяемых ячеек.

После ввода ограничений получим следующий вид окна Поиск

Затем в окне Поиск решения необходимо нажать кнопку Параметры и

в появившемся окне установить флажок в пункте Линейная

модель. В этом случае при решении задачи будет использоваться

симплекс-метод. Остальные значения можно оставить без изменения.

После нажать кнопку ОК.

Для решения задачи в окне Поиск решения нажать кнопку Выполнить.

Если решение найдено, появляется окно

Для просмотра результатов выбираем тип отчета Результаты и

нажимаем кнопку ОК. Отчет по результатам решения задачи

Из этих таблиц видно, что в оптимальном решении:

 максимальный доход ($D$4) равен 117;

 объем выпуска продукта А ($B$3) равен 39;

 объем выпуска продукта В ($С$3) равен 0;

 расход ресурса Электроэнергия ($D$7) равен 39, статус ресурса

связанный, разница (остаток ресурса) 17;

 расход ресурса Сырье ($D$8) равен 78, статус ресурса связанный,

разница (остаток ресурса) 2;

 расход ресурса Оборудование ($D$9) равен 117, статус ресурса

не связанный, разница (остаток ресурса) 0.

Первоначальная таблица рабочей книги заполняется результатами,

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

По результатам

Microsoft Excel 11.0 Отчет по результатам
Рабочий лист: [Лист Microsoft Excel.xls]Лист3
Отчет создан: 02.10.2011 22:21:29
Целевая ячейка (Максимум)
	Ячейка	Имя	Исходное значение	Результат
	$D$4	Цена Целевая функция	117	117
Изменяемые ячейки
	Ячейка	Имя	Исходное значение	Результат
	$B$3	Продукт А	39	39
	$C$3	Продукт В	0	0
Ограничения
	Ячейка	Имя	Значение	Формула	Статус	Разница
	$D$7	Электроэнергия Расход	39	$D$7<=$E$7	не связан.	17
	$D$8	Сырье Расход	78	$D$8<=$E$8	не связан.	2
	$D$9	Оборудование Расход	117	$D$9<=$E$9	связанное	0

Устойчивость, нажимаем кнопку ОК. В рабочей книге появится лист Отчет

по устойчивости, который позволяет проанализировать изменение

параметров задачи Отчет по устойчивости решения задачи

Отчет состоит из двух таблиц: в таблице 1 приводятся сведения о

чувствительности решения к изменению коэффициентов целевой

функции (cj), в таблице 2 приводятся сведения о чувствительности

решения задачи к изменению запасов сырья (bi).