Решение задачи линейного программирования с помощью функции Excel Поиск решения

По умолчанию Поиск решений в Excel отключен.

Для включения: Файл – Параметры – Надстройки – щелкнуть в окне Поиск решения и нажать внизу Перейти. В открывшемся меню Надстроек Excel отметить (поставить галочку) у Поиск решений. Теперь Поиск решений появился в меню Данные справа. Настройки меню сохраняются для одного листа, поэтому разные задачи рекомендуется решать на разных листах.

Целевая ячейка – ячейка на рабочем листе с таблицей исходных данных, куда занесена формула целевой функции.

Изменяемые ячейки – ячейки, отражающие значения переменных, которые необходимо найти в результате оптимизации.

Ограничения – задаются посредством кнопки Добавить и отражают связь формул левых частей ограничений с их правыми частями.

Задание:

Задача составления рациона. Пищевой рацион должен содержать не менее b1, b2, b3 питательных веществ S1, S2, S3. Для составления пищевого рациона используются два вида продуктов питания P1, P2. Содержание питательных веществ в единице каждого продукта и их стоимость указаны в таблице 1.


Таблица 1.


Содержание питательных веществ в разных видах продуктов питания

Питательные вещества	Содержание в P1	Содержание в P2	Норма (b), единиц
S1	3	1	9
S2	1	2	8
S3	1	6	12
Цена, ден. единиц	4	6

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

1. На рабочем листе Excel занести исходную информацию.
Пример занесения исходной информации представлен на рисунке 1.

Рисунок 1. - Исходные данные для задачи линейного программирования.


В столбце с жёлтой заливкой необходимо прописать формулы для расчета ограничений вида 3х1+х2. В виде Excel это может выглядеть как

=B8*B4+C8*C4

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

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

Рисунок 2. - Параметры функции Поиск решения.

При нажатии кнопки Найти решение во всплывающем диалоговом окне отражается результат поиска решения.

Рисунок 3. – Выбор параметров результата поиска решения.


Для получения отчета о результатах в данном диалоговом окне необходимо выбрать Отчеты-Результаты. В результате данной операции формируется новая вкладка, которая содержит информацию о значении целевой функции до оптимизации и после, исходные и оптимальные значения переменных. Отчет по ограничениям позволяет выделить дефицитные (состояние – Привязка) и недефицитные (состояние – Без привязки) виды ресурсов (Рисунок 4).




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


Ответ: для достижения минимальной стоимости продуктов питания в сумме 26 денежных единиц необходимо взять 2 единицы продуктов питания P1 и 3 единицы продуктов питания P2.