Урок 1. Решение задачи линейного программирование в Excel с помощью надстройки «Поиск решения»

Статья с подробным разбором.

Условие задачи
Экономико-математическая модель
Решение задачи с помощью надстройки "Поиск решения"
Отчёт по устойчивости
Видео

Условие задачи
Предприятие планирует выпуск продукции А, Б, В, Г. Для изготовления продукции необходимо четыре вида ресурсов: сталь, цветные металлы, токарные станки, фрезерные станки.
Имеется запас ресурсов:
-сталь (250 кг)
-цветные металлы (40 кг)
- токарные станки (100 станко-час)
- фрезерные станки (80 станко-час)
Нормы расхода единицы сырья на изготовление одного вида продукции приведены в таблице по столбцам А, Б, В и Г соответственно.

В последней строке таблицы приведены величины прибыли от реализации одного вида продукции.

Таблица 1.

Вид ресурса	Объем ресурса	Нормы расхода на одно изделие
Вид ресурса	Объем ресурса	А	Б	В	Г
Сталь, кг	250	10	20	15	18
Цв. Металлы, кг	40	0	5	8	7
Токарные станки, станко-час	100	15	18	12	20
Фрезерные станки, станко-час	80	8	12	11	10
Прибыль, ден. ед.		4	2	4	3

Экономико-математическая модель
Перейдём к созданию математической модели задачи.
Обозначим через Х₁, Х₂, Х₃, Х₄- количество изделий каждого вида соответственно, планируемого к выпуску, а через f--величину прибыли от реализации этих изделий.

Целевая функция запишется в виде:
f = 4Х₁ + 2Х₂ +4Х₃ + 3Х₄ (мах)

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

10Х₁ + 20Х₂ +15Х₃+18Х₄≤250
0Х₁ + 5Х₂ + 8Х₃+ 7Х₄≤40
15Х₁ + 18Х₂ +12Х₃+ 20Х₄≤100
8Х₁ + 12Х₂ + 11Х₃+ 10Х₄≤80

Условия не отрицательности:
Х_j≥0 (j=1,4)

Решение задачи с помощью надстройки «Поиск решения»
Вводим исходные данные в Excel

Исходные данные в Excel

Далее в ячейку G5 с помощью функции СУММПРОИЗВ вводим формулу целевой функции:

Формула целевой функции

Значок «$» означает, что ссылки по столбцам в ячейках с В3 по Е3 не будут меняться, так как значок стоит после названия ячейки, если значок стоит перед обозначением ячейки, то ссылки по строкам не меняются.
Далее протягиваем формулу и СУММПРОИЗВ автоматически заполняется в ячейки ниже.

Формулы расхода ресурсов

Удаляем формулу из ячейки G6.
В ячейки H7:H10 вводим ограничения на ресурсы:

Форма задачи перед началом "Поиска решения"

Заполняем диалоговое окно «Поиск решения»

Диалоговое окно "Поиск решения"

Выбираем вид отчёта – устойчивость.

Выбор вида отчёта - отчёт на устойчивость

Получим решение:

Решение поставленной задачи

Согласно оптимальному плану необходимо выпускать 2,667 единицы продукции А и 5 единиц продукции В. Продукцию Б и Г выпускать не следует прибыль при этом будет максимальной и составит 30,667 ден. ед.

Отчёт по устойчивости
Рассмотрим отчёт по устойчивости.

Ограничения
			Окончательное	Тень	Ограничение	Допустимое	Допустимое
	Ячейка	Имя	Значение	Цена	Правая сторона	Увеличение	Уменьшение
	$G$7		101,6666667	0	250	1E+30	148,3333333
	$G$8		40	0,1	40	6,376811594	40
	$G$9		100	0,266666667	100	6,875	40
	$G$10		76,33333333	0	80	1E+30	3,666666667

Теневая цена для первого вида ресурса (сталь) равна 0, это значит, что данный вид ресурса не является дефицитным, действительно, мы расходуем 101,667 кг из имеющихся 250 кг. Имеется резерв в размере 148,33 кг, который можем пустить на другие цели. Теневая цена второго ресурса (цветные металлы) равна 0,1, это значит, что при увеличении данного ресурса на 1 кг мы сможем получить оптимальный план значение целевой функции которого возрастёт на 0,1 ден. ед.
Файл с решением здесь:
https://vk.com/wall-216984375_78
https://t.me/smys_l/124

Заявка на услуги

Укажите наиболее удобный для ВАС способ связи
и с Вами свяжутся в ближайшее время

Нажимая на кнопку, Вы соглашаетесь на обработку персональных данных в соответствии с Условиями.