Методические рекомендации. «Поиск оптимального плана производства»

Лабораторная работа № 2

«Поиск оптимального плана производства»

Проверила: Выполнила:
преподаватель студентка 4 курса 9 группы
Смирнова А. Ю. финансового факультета
Нгуен Хоай Тху

Харьков, 2012

Цель - закрепление теоретического и практического материала, приобретение навыков решения задач линейного программирования в среде Microsoft Ехсеl.

Задача - сформировать математическую постановку задачи линейного программирования, подготовить таблицу для предварительных расчетов, найти оптимальный план производства с помощью надстройки пакета Microsoft Ехсеl " Поиск решения", проанализировать отчет по результатам решения задачи.

Методические рекомендации

1. Составление постановки задачи. Для решения задач линейного программирования и последующего их анализа используется надстройка пакета Microsoft Ехсеl " Поиск решения", назначением которой является решение оптимизационных задач различного типа.

Рассмотрим следующую задачу:

Пiдприємство, має в своему розпорядженi ресурси сировини чотирьох видiв A, B, C, D, може виготовляти продукцiю 2х видiв P1, P2, У табл.1 вказанi витрати ресурсiв на виготовлення 1т продукцiю об’єм ресурсiв i прибуток що одержується вiд продажу 1т. вiдповiдноi продукцii.

Визначити асортимент продукцii, що випускається, при одержаний прибуток буде максимальними.

Таблица 1

Исходные данные

Пусть изделие А будет Х1, а изделие В будет Х2.

Тогда

Линейная функция, максимум которой нужно определить, вместе с системой неравенств и условием додатности переменных составляют математическую модель исходной задачи линейного программирования.

2. Формирование таблицы для расчетов. Вводим в ячейки рабочего листа исходную информацию (рис. 1).

Рис. 1. Таблица для расчетов

Сначала все компоненты плана принимается равными единице. Это достаточно удобно для последующей проверки используемых формул. Для расчета левых частей неравенств относительно ресурсов задачи, которыми в данном случае являются материалы и оборудование, и целевой функции используется функция СУММПРОИЗВ, которая умножает соответствующие элементы заданных массивов и возвращает сумму произведений. Массивы, которые являются аргументами функции СУММПРОИЗВ, должны иметь одинаковые размерности.

Как показано на рис. 1, при использовании функции СУММПРОИЗВ можно применять абсолютные ссылки на строку, содержащую план производства. Такое применение абсолютной ссылки особенно эффективно при решении задач со значительным количеством ресурсных ограничений.

При расчете строки «План» столбца «Расчет» используется функция СУММ, которая суммирует соответствующие элементы (рис.2).

Рис. 2. Функция СУММ

3. Использование надстройки " Поиск решения". Выделим ячейку, в которой вычисляется целевая функция, и выберем в меню «Сервис / Поиск решения». В диалоговом окне (рис. 3) в поле ввода " Установить целевую ячейку: " уже содержится адрес ячейки с целевой функцией $В$6. Установим переключатель " равной максимальному значению". Перейдем к полю ввода " Изменяя ячейки: " нужно занести адрес блока с неизвестными задачи – x₁, x₂. В нашем случае это адрес блока $В$7: $С$7.

Рис. 3. Диалоговое окно надстройки " Поиск решения"

Перейдем к введению ограничений. При нажатии кнопки " Добавить" появится диалоговое окно " Добавление ограничения" (рис.4).

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

В поле ввода " Ссылка на ячейку: " укажите $D$7. Справа расположен выпадающий список, с условными операторами (раскройте его и ознакомьтесь с операторами сравнения для ограничений). У нас есть одно ограничение, поэтому, исходя из этого диалогового окна, нажмите кнопку " Добавить" и введите ограничение $D$2: $D$4 < = $Е$2: $Е$4. Последнее ограничениям задачи является ограничение додатности неизвестных - $В$7: $С$7 > = 0. Введение ограничений закончено, поэтому нажмите " ОК". Вы снова опинетесь в диалоговом окне " Поиск решения" (рис. 5). Вы увидите все введенные ограничения. Справа есть кнопки " Изменить" и " Удалить". С их помощью можно изменить ограничение или стереть его.

Рис. 5. Окно " Поискрешения" с условиями задачи

Нажмите кнопку " Параметры". Вы окажетесь в диалоговом окне " Параметры поиска решения" (рис. 6). Чтобы узнать назначении полей ввода этого окна, нажмите кнопку " Справка". Менять ничего не будем, только установим флажок " Линейная модель" (потому что наши ограничения и целевая функция являются линейными по переменным x₁ и х₂). Если установить флажок " Неотрицательные значение" (для переменных х₁ и х₂), то можно было не вводить ограничения $В$7: $С$7 > = 0. Нажмем " ОК" и опинемося в исходном окне.

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

Задача оптимизации полностью подготовлена. Нажимаем кнопку " Вьиполнить". Появляется диалоговое окно " Результаты поиска решения". В нем мы читаем сообщение " Решение найдено. Все ограничения и условия оптимальности вьиполненьи." На выбор предлагаются варианты: " Сохранить найденное решение" или " Восстановить исходньие значения". Выбираем первое. Можно также вывести отчеты: по результатам, по устойчивости и по границам. Выберите отчет по результатам.

После нажатия " ОК" вид таблицы меняется: в ячейках строки плана производства появляются оптимальные значения: изделие типа А нужно выпускать в количестве 1, 75 штук, а типа В - 1шт. Согласно перечисляются все формулы. Целевая функция достигает значения 10.

4. Анализ отчета по результатам. Этот отчет (Рис. 7) содержит целевую ячейку, список изменяемых ячеек и ограничений. В отчете приведена информация для каждого ограничения. По каждому ограничению выдаются его статус и разница. Статус может быть " Связано", " Не связано" и " Невыполненный".

Рис. 7. Отчет по результатам

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

Связано ограничения - это ограничения, для которого значение разницы равна 0.

Несвязанное ограничения - это ограничения, было выполнено с ненулевым значением разницы.

Вывод: в результате выполнения лабораторной работы я закрепила знания теоретического и практического материала и приобрела навыки решения задач линейного программирования в среде MS Excel.