Презентация "Решение задачи оптимального планирования в 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 появится оптимальное решение
Практическая работа в парах (делятся на пары, каждая пара вытягивает задачу. Приступают к решению)
  • Практическая работа в парах (делятся на пары, каждая пара вытягивает задачу. Приступают к решению)
  • Итоги (что получилось в решении, мнение о работе)
Спасибо за урок!
  • Спасибо за урок!