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

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

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

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

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

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

Таблица в 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

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

анализ данных и поиск решения Excel

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

Параметры поиска решения Добавление ограничения

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

Добавление ограничения

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

Параметры поиска решения симплекс метод решения линейной задачи в excel

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

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

решение задачи линейной оптимизации в excel

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

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

Leave a Reply

Ваш e-mail не будет опубликован.