8 Реализация задач линейного программирования средствами ms Excel Реализация задачи распределения ресурсов посредством ms Excel.

1. Подготовка формы ввода данных

2.Ввести данные в форму.

3.Выбрать команду Сервис, Поиск решения

Если опция «поиск решения» не активирована, то необходимо выбрать команду Сервис, Надстройка, найти и Поиск решения и установить «галку».

3 .1. Ввести номер целевой ячейки;

3.2. Ввести диапазон изменяемых ячеек( искомых переменных);

3.3. Добавить ограничения по трудовым ресурсам, материалов Y, Z. Для этого выбираем команду Добавить. Появляется диалоговое окно для ввода ограничений.

П осле ввода всех ограничений выбираем команду ОК, и выходим в диалоговое окно Поиск решений.

4. В окне Поиск решений выбираем команду Параметры

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

5. В диалоговом окне Поиск решений выбираем команду Выполнить.

6. На экране окно Результаты поиска решения

7 . На рабочем листе отображены результаты решенной задачи.

В ыводы по задаче ( интерпритация полученных результатов):

1. Максимальная прибыль, которая может быть получена на данном предприятии равна 99. 2857 рублей.

2. Продукт типа А целесообразно выпускать по первому технологическому процессу в объеме 7. 1429, а продукт типа Б по третьей технологии в объеме 7.857.

Анализ оптимального решения

Анализ оптимального решения выполняется после успешного решения задачи, когда на экране появляется диалоговое окно Результат поиска решения Решение найдено.

С помощью данного диалогового окна можно вызвать отчеты трех типов:

результаты;

устойчивость;

пределы;

Алгоритм вызова отчета

1. Курсор на тип вызываемого отчета.

2. ОК

На экране вызванный отчет на новом листе, на ярлычке которого указано название отчета.

3. Курсор на ярлычок с названием отчета.

на экране вызванный отчет рис

Отчет по результатам

Отчет состоит из трех таблиц:

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

Таблица 2 приводит значения исходных переменных, полученных в результате решения задачи.

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

Для ограничений в графе Формула приведены зависимости которые были введены в диалоговое окно Поиск решения; в графе Значения приведены величины использованного ресурса; в графе Разница показано количество неиспользованного ресурса. Если ресурс используется полностью, тол в графе Состояние указывается связанное, при неполном использовании ресурса в этой графе указывается не связан.

Отчет по устойчивости ( рис )

В таблице 1 приводятся следующие значения для переменных:

результат решения задачи;

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

коэффициенты целевой функции;

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

В таблице 2 приводятся аналогичные значения для ограничений:

величина использованных ресурсов;

теневая цена, т.е. двойственные оценки, которые показывают, как изменится целевая функция при изменении ресурсов на единицу.

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

Отчет по пределам( рис )

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

- приводятся значения в оптимальном решении;

- приводятся нижние пределы изменения значений;

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

рис. Отчет по устойчивости

Изменяемые ячейки
			Результ.	Уменьш.	Целевой	Допустимое	Допустимое
	Ячейка	Имя	значение	стоимость	Коэф-фициент	Увеличение	Уменьшение
	$B$3	значение проц1	7,142857143	0	4	2,199999999	0,6
	$C$3	значение проц2	0	-0,428571429	5	0,428571429	1E+30
	$D$3	значение проц3	7,857142857	0	9	4,333333333	0,916666666
	$E$3	значение проц4	0	-1,571428571	11	1,571428571	1E+30
Ограничения
			Результ.	Тень	Ограничение	Допустимое	Допустимое
	Ячейка	Имя	значение	Цена	Правая часть	Увеличение	Уменьшение
	$F$9	ТРУД.Р лев.часть	15	1,857142857	15	5,327868852	5
	$F$10	МАТ.Y лев.часть	73,57142857	0	120	1E+30	46,42857143
	$F$11	МАТ Z лев.часть	100	0,714285714	100	50	55

рис Отчет по результатам

Целевая ячейка (Макс)
	Ячейка	Имя	Исходно	Результат
	$F$6	коэф.в цф	0	99,2857144
Изменяемые ячейки
	Ячейка	Имя	Исходно	Результат
	$B$3	значение проц1	0	7,142857119
	$C$3	значение проц2	0	0
	$D$3	значение проц3	0	7,857142881
	$E$3	значение проц4	0	0
Ограничения
	Ячейка	Имя	Значение	Формула	Состояние	Разница
	$F$9	ТРУД.Р лев.часть	15	$F$9<=$H$9	связанное	0
	$F$10	МАТ.Y лев.часть	73,57142848	$F$10<=$H$10	не связан.	46,42857152
	$F$11	МАТ Z лев.часть	100,0000002	$F$11<=$H$11	связанное	0
	$B$3	значение проц1	7,142857119	$B$3>=$B$4	не связан.	7,142857119
	$C$3	значение проц2	0	$C$3>=$C$4	связанное	0
	$D$3	значение проц3	7,857142881	$D$3>=$D$4	не связан.	7,857142881
	$E$3	значение проц4	0	$E$3>=$E$4	связанное	0

рис Отчет по пределам

	Целевое
Ячейка	имя	Значение
$F$6	коэф.в цф	99,2857144
	Изменяемое			Нижний	Целевой		Верхний	Целевой
Ячейка	имя	Значение		предел	результат		предел	результат
$B$3	значение проц1	7,142857119		0	70,71428593		7,142857064	99,28571418
$C$3	значение проц2	0		0	99,2857144		0	99,2857144
$D$3	значение проц3	7,857142881		0	28,57142848		7,857142864	99,28571426
$E$3	значение проц4	0		0	99,2857144		0	99,2857144