Презентация "Решение задачи оптимального планирования в MS Excel"

Решение задачи оптимального планирования в MS Excel Постановка задачи планирования

• имеются некоторые плановые показатели: х, у и другие;
• имеются некоторые ресурсы: R1, R2 и другие, за счет которых эти плановые показатели могут быть достигнуты. Эти ресурсы практически всегда ограничены;
• имеется определенная стратегическая цель, зависящая от значений х, у и других плановых показателей, на которую следует ориентировать планирование.

Нужно определить значение плановых показателей с учетом ограниченности ресурсов при условии достижения стратегической цели. Это и будет оптимальным планом. Рассмотрим пример Пусть совхоз занимается возделыванием только двух культур — зерновых и картофеля — и располагает следующими ресурсами:

• пашня — 5000 га,
• труд — 300 тыс. чел.-ч,
• возможный объем тракторных работ — 28 000 условных га.
Цель производства—получение максимального объема валовой продукции (в стоимостном выражении). Найдите оптимальное сочетание посевных площадей культур.

Решение.

• Этап I.

культуры	Затраты на 1 га посева		Стоимость валовой продукции с 1 га, р.
	труда, чел.-ч	тракторных работ, усл. га
Зерновые	30	4	400
Картофель	150	12	1000

Для поиска оптимального решения задачи обозначим через Для поиска оптимального решения задачи обозначим через х1 -га площадь, отводимую под зерновые, х2 га — площадь, отводимую под картофель. Тогда стоимость зерновых составит 400 х1 р., а стоимость картофеля — 1000 х2 р. Отсюда стоимость всей валовой продукции составит ( 400 х1 + 1000 х2) р. Обозначим это выражение через у и назовем его целевой функцией: у = 400 х1 + 1000 х2 Нам надо найти максимум этой целевой функции при соблюдении следующих условий: Нам надо найти максимум этой целевой функции при соблюдении следующих условий: а) общая площадь зерновых и картофеля не должна превышать 5000 га, т. е. х1 + х2≤5000; б) общие затраты труда не должны превосходить 300 тыс. человеко-часов, т. е. 30 х1 + 150 х2≤ 300 000; в) общий объем механизированных работ не должен превосходить 28 000 усл. га, т. е. 4 х1 + 12 х2≤28 000; г) площади, отводимые под зерновые и картофель, могут принимать только неотрицательные значения: х1≥0 и х2 ≥0. Таким образом, условия задачи выражаются следующей системой неравенств Таким образом, условия задачи выражаются следующей системой неравенств Требуется найти такие значения х1 и х2, при которых целевая функция у = 400 х1 + 1000 х2 принимает наибольшее значение. Этап II. Решим задачу графически. Построим прямую х1 + х2=5000.

Построим прямую 30 х1 + 150 х2=300 000.

Построим прямую 4 х1 + 12 х2=28 000.

А

Е

М

К

О

Таким образом, наибольшее значение целевой функции достигается в вершине М, что соответствует варианту плана, по которому под зерновые отводится 4000 га, а под картофель — 1000 га.

Решение задачи оптимального планирования в MS Excel программа оптимизации «Поиск решения». Для этого надо выполнить команду => Сервис => Поиск решения. На экране откроется соответствующая форма программа оптимизации «Поиск решения». Для этого надо выполнить команду => Сервис => Поиск решения. На экране откроется соответствующая форма Далее надо выполнить следующий алгоритм:

• Далее надо выполнить следующий алгоритм:
• Ввести координату ячейки с целевой функцией. В нашем случае это В15. (Заметим, что если перед этим установить курсор на ячейку В15, то ввод произойдет автоматически).
• Поставить отметку «максимальному значению», то есть сообщить программе, что нас интересует нахождение максимума целевой функции.
• В поле «Изменяя ячейки» ввести В5:С5, то есть сообщить, какое место отведено под значения переменных -плановых показателей.
• В поле «Ограничения» надо ввести информацию о неравенствах-ограничениях, которые имеют вид B10<=D10; B1K=D11; B12>=D12; B13>=D13.

Закрыть диалоговое окно «Добавление ограничения». Снова появится форма «Поиск решения»

• Закрыть диалоговое окно «Добавление ограничения». Снова появится форма «Поиск решения»

6. Теперь надо дать последние указания: задача является линейной (это многократно облегчит программе ее решение). Для этого следует щелкнуть по кнопке «Параметры» — появится форма «Параметры поиска решения»

• 6. Теперь надо дать последние указания: задача является линейной (это многократно облегчит программе ее решение). Для этого следует щелкнуть по кнопке «Параметры» — появится форма «Параметры поиска решения»

Вся информация введена. Далее надо щелкнуть по кнопке «Выполнить» — мгновенно в ячейках В5 и С5 появится оптимальное решение

• Вся информация введена. Далее надо щелкнуть по кнопке «Выполнить» — мгновенно в ячейках В5 и С5 появится оптимальное решение

Практическая работа в парах (делятся на пары, каждая пара вытягивает задачу. Приступают к решению)

• Практическая работа в парах (делятся на пары, каждая пара вытягивает задачу. Приступают к решению)
• Итоги (что получилось в решении, мнение о работе)

Спасибо за урок!

• Спасибо за урок!