Решение задачи линейной оптимизации с помощью Excel

Рассмотрим пример решения задачи линейной оптимизации в Excel

Дана оптимизационная задача в виде таблицы

Ресурсы	Нормы затрат на изготовление 1 ед. кровати	Нормы затрат на изготовление 1 ед. шкафа	Общее количество ресурсов
Сосна	0,8	1,4	200
Дуб	1,2	0,6	150
Трудоемкость (человеко-часов)	4	5	800
Прибыль от продажи одной единицы	9	11

По условию задачи составим целевая функция, которая будет иметь вид
Z=9x₁+11x₂
Ограничения
0,8x₁+1,4x₂≤200
1,2x₁+0,6x₂≤150
4x₁+5x₂≤800
x₁,x₂≥0

В Excel создаём таблицу с формулами, пример показан ниже

Формулы можно скопировать из этой таблицы

Переменные
x1	x2
0	0
Функция целевая	=9*A4+11*B4
=0.8*A4+1.4*B4	200
=1.2*A4+0.6*B4	150
=4*A4+5*B4	800

Затем переходим на вкладку Данные -> Поиск решения

Выбираем ячейку, в которой надо оптимизировать целевую функцию, в нашем случае B5. Ставим галочку на максимум, затем выбираем ячейки с изменяемыми переменными это x₁ и x₂ – A4 и B4 и прописываем ограничения, нажимаем на кнопку добавить.

Из условия задачи значения выражений левой части меньше или равно значений правой части. Указываем сразу диапазон значений. Жмём на кнопку добавить ограничения.

И выбираем из списка метод решения – решения линейной задачи симплекс методом.

Вылетает информационное окно — результаты поиска решения, жмём Ок.

Переменные
x1	x2
75	100
Функция целевая	1775
200	200
150	150
800	800

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