Линейная оптимизационная задача

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

Таблица 1

Тип сырья	Нормы расхода сырья на одно изделие	Запасы сырья
А	Б	В	Г
I
II
III
Цена изделия

Целевая функция имеет вид: f(x)=20х₁+17х₂+8х₃+10х₄.

Ограничения по типу сырья:

2х₁ + 2х₂ + 3х₃ + х₄ ≤ 20

2х₁ + 3х₂ + х₃ + х₄ ≤ 10

х₁ + 3х₂ + 2х₃ + 2х₄ ≤ 50.

Таблица с исходными данными заполнена так, как показано на рис. 2.15.

Рис. 2.15. Исходные данные

В ячейку F4 введена формула для вычисления значения целевой функции. Заполним аргументы встроенной математической функции СУММПРОИЗВ, как показано на рис. 2.16.

Рис. 2.16. Встроенная математическая функция СУММПРОИЗВ

В ячейке F4 пока выведется 0, а в строке формул отобразится текст содержимого ячейки (рис. 2.17).

Рис. 2.17. Содержимое ячейки F4

Заполним диапазон ячеек F8:F10 соответствующими формулами, используя операцию копирования содержимого ячейки F4:

=СУММПРОИЗВ($B$3:$E$3;B8:E8);

=СУММПРОИЗВ($B$3:$E$3;B9:E9);

=СУММПРОИЗВ($B$3:$E$3;B10:E10).

В ячейках F8, F9 и F10 результат так же равен пока нулю (рис. 2.18).

Рис. 2.18. В ячейках F8, F9 и F10 результат равен нулю

На вкладке Данные, активизируем мастер Поиск решения (рис. 2.19).

Рис. 2.19. Вкладка Данные

Если мастер Поиск решения отсутствует, то необходимо из контекстного меню Ленты выбрать команду Настройка ленты (рис. 2.20), активизировать опцию Все команды, создать Новую группу и добавить в нее мастер Поиск решения.

Рис. 2.20. Контекстное меню Ленты.

Команда Настройка ленты

Мастер Поиск решения выведет на экран диалоговое окно Параметры поиска решения, которое необходимо заполнить следующим образом (рис. 2.21). В качестве целевой ячейки установим ячейку F4, диапазон изменяющихся ячеек зададим как В3:Е3, используя кнопку Добавить, зададим ограничения (рис. 2.22).

Рис. 2.21. Диалоговое окно Параметры поика решения

Рис. 2.23. Добавление ограничения

После нажатия кнопки Найти решение, начнется процесс поиска решения и на экран выведется запрос, в каком виде сохранять результаты поиска (рис. 2.24). Выберем тип отчета – Результаты.

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

Рис. 2.25. Результат решения

Полученное решение (рис. 2.25) означает, что максимальный доход в 100 единиц можно получить при выпуске 5 единиц изделия первого вида. При этом сырье II типа будет использовано полностью, а из 20 единиц сырья I типа будет использовано только 10 единиц, а из 50 единиц сырья III типа только 5.

Отчет по результатам Excel сформирует автоматически на новом листе (рис. 2.26).

Рис. 2.26. Лист с результатами расчета

Транспортная задача

Исходные данные транспортной задачи приведены схематически (рис. 2.27): внутри прямоугольника заданы удельные транспортные затраты на перевозку единицы груза, слева указаны мощности поставщиков, а сверху – мощности потребителей. Найти оптимальный план закрепления поставщиков за потребителями.

Рис. 2.27. Исходные данные транспортной задачи

Транспортная задача является одной из наиболее распространенных задач линейного программирования и находит широкое практическое применение. При решении этой задачи необходимо составить план перевозок, позволяющий вывести все грузы, полностью удовлетворить потребности потребителей и имеющий минимальную стоимость. Транспортная задача, в которой суммарные запасы и потребности совпадают, является закрытой. В нашем примере СУММ(В1:Е1) = СУММ(А2:А5) = 660 единиц груза (рис. 2.28).

Рис. 2.28. Исходные данные в таблице Excel

В диапазон ячеек В12:Е12 и F8:F11 введем формулы, как показано на рис 2.29.

Рис. 2.29. Диапазоны ячеек В12:Е12 и F8:F11

В ячейку С14 введем формулу для вычисления целевой функции =СУММПРОИЗВ(B2:E5;B8:E11). Так как значения еще не вычислены, в ячейках с формулами пока отображается ноль (рис. 2.30).

Рис. 2.30. Заданы все параметры для начала

поиска решения задачи

На вкладке Данные, активизируем мастер Поиск решения, заполняем диалоговое окно Параметры поиска решения как показано на рис. 2.31.

Рис. 2.31. Параметры решения транспортной задачи

Рис. 2.32. Результат решения транспортной задачи

В результате решения (рис. 2.32) получен оптимальный план перевозок:

Х₁₂ = 140 ед. груза следует перевезти от 1-го поставщика 2-му потребителю;

Х₁₄ = 30 ед. груза следует перевезти от 1-го поставщика 4-му потребителю;

Х₂₁ = 60 ед. груза следует перевезти от 2-го поставщика 1-му потребителю;

Х₂₄ = 120 ед. груза следует перевезти от 2-го поставщика 4-му потребителю;

Х₃₁ = 70 ед. груза следует перевезти от 3-го поставщика 1-му потребителю;

Х₃₃ = 120 ед. груза следует перевезти от 3-го поставщика 3-му потребителю;

Х₄₁ = 120 ед. груза следует перевезти от 4-го поставщика 1-му потребителю.

Общая стоимость перевозок = 3470.