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

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

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

Этап I.

Для поиска оптимального решения задачи обозначим через
х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 принимает наибольшее значение.

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

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

А

Е

М

К

О

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

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

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

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

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

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

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

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