Практика 11 класс ЗЛП Решение_

Решение задачи линейного программирования (ЗЛП) в Excel

Метод ЗЛП решает проблему распределения ограниченных ресурсов между конкурирующими видами деятельности с тем, чтобы максимизировать или минимизировать некоторые численные величины, такие как прибыль или расходы.

Для решении ЗЛП необходимо:

- составить математическую модель, то есть сформулировать условия на математическом языке;

- решить ее с использованием надстройки Excel «Поиск решения» или с помощью специализированных компьютерных программ.

Задача. Николай Кузнецов управляет небольшим механическим заводом. В будущем месяце он планирует изготавливать два продукта (А и В), по которым прибыль оценивается в 2500 и 3500 руб., соответственно. Изготовление обоих продуктов требует затрат на машинную обработку, сырье и труд. На изготовление каждой единицы продукта А отводится 3 часа машинной обработки, 16 единиц сырья и 6 единиц труда. Соответствующие требования к единице продукта В составляют 10, 4 и 6. Николай прогнозирует, что в следующем месяце он может предоставить 330 часов машинной обработки, 400 единиц сырья и 240 единиц труда. Технология производственного процесса такова, что не менее 12 единиц продукта В необходимо изготавливать в каждый конкретный месяц. Необходимо определить количество единиц продуктов А и В, которые Николай должен производить в следующем месяце для максимизации прибыли.

1.      Математическая модель задачи:

Максимизировать: Z = 2500 * х₁ + 3500 * х₂

При условии, что:  3 * х₁ + 10 * х₂ ≤ 330

                                 16 * х₁ + 4 * х₂ ≤ 400

                                 6 * х₁ + 6 * х₂ ≤ 240

                                 х₂ ≥ 12

                                 х₁ ≥ 0

2. Создадим экранную форму и введем в нее исходные данные (рис. 1).

Рисунок 1 - Экранная форма для ввода данных ЗЛП

Обратите внимание на формулу в ячейке С7. Это формула целевой функции. Аналогично, в ячейки С16:С18 введены формулы для расчета левой части ограничений.

3. Проверьте, если у вас установлена надстройка «Поиск решения» (рис. 2), пропустите этот пункт.

Рис. 2. Надстройка Поиск решения установлена; вкладка «Данные», группа «Анализ»

Если надстройки «Поиск решения» вы на ленте Excel не обнаружили, щелкните на кнопку Microsoft Office, а затем Параметры Excel (рис. 3).

Рисунок 3 -  Параметры Excel

Выберите строку Надстройки, а затем в самом низу окна «Управление надстройками Microsoft Excel» выберите «Перейти» (рис. 4).

Рисунок 4 - Надстройки Excel

В окне «Надстройки» установите флажок «Поиск решения» и нажмите Ok (рис. 5). (Если «Поиск решения» отсутствует в списке поля «Надстройки», чтобы найти надстройку, нажмите кнопку Обзор. В случае появления сообщения о том, что надстройка для поиска решения не установлена на компьютере, нажмите кнопку Да, чтобы установить ее.)

Рисунок 5 - Активация надстройки «Поиск решения»

После загрузки надстройки для поиска решения в группе Анализ на вкладке Данные становится доступна команда Поиск решения (рис. 2).

4. Следующим этапом заполняем окно Excel «Поиск решения» (рис. 6)

Рисунок 6 - Заполнение окна «Поиск решения»

В поле «Установить целевую ячейку» выбираем ячейку со значением целевой функции – $C$7. Выбираем, максимизировать или минимизировать целевую функцию. В поле «Изменяя ячейки» выбираем ячейки со значениями искомых переменных $C$4:$D$4 (пока в них нули или пусто). В области «Ограничения» с помощью кнопки «Добавить» размещаем все ограничения нашей модели. Нажимаем клавишу «Выполнить». В появившемся окне «Результат поиска решения» выбираем все три типа отчета (рис. 7) и нажимаем Ok. Эти отчеты нужны для анализа полученного решения.

Рисунок 7 - Выбор типов отчета

На основном листе появились значения максимизированной целевой функции – 130 000 руб. и изменяемых параметров х₁ = 10 и х₂ = 30. Таким образом, для максимизации дохода Николаю в следующем месяце следует произвести 10 единиц продукта А и 30 единиц продукта В.

Если вместо окна «Результат поиска решения» появилось что-то иное, Excel`ю найти решение не удалось. Проверьте правильность заполнения окна «Поиск решения». Можно уменьшить точность поиска решения. Для этого в окне «Поиск решения» щелкните на Параметры (рис. 8) и увеличьте погрешность вычисления, например, до 0,001. Иногда из-за высокой точности Excel не успевает за 100 итераций найти решение.

Рисунок 8-  Увеличение погрешности вычислений