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

Цель занятия: изучение функциональных возможностей табличного процессора Excel 2007 и приобретение навыков практической работы по использованию средств «Поиска решений» для исследования экономических моделей.
Задачи занятия:
Научиться использовать надстройку MS Excel «Поиск решения» для решения задач линейного и нелинейного программирования.

План лабораторного занятия:
Добавление надстройки «Поиск решения»
Постановка задачи линейного программирования
3. Решение задач
4. Задание для самостоятельной работы

1. Добавление надстройки «Поиск решения»
1.1
1.2

1.3

1.4
1.5

Линейное программирование (ЛП) – область математики, разрабатывающая теорию и численные методы решения задач нахождения экстремума (максимума или минимума) линейной функции многих переменных при наличии линейных ограничений, т. е. линейных равенств или неравенств, связывающих эти переменные. К задачам линейного программирования сводится широкий круг вопросов планирования экономических процессов, где ставится задача поиска наилучшего (оптимального) решения.
Общая задача линейного программирования (ЗЛП) состоит в нахождении экстремального значения (максимума или минимума) линейной функции.

Задача распределения неоднородных ресурсов. Составление оптимального плана выпуска продукции
Краткая теория
Пусть некоторое предприятие обладает ресурсами S1,S2,…,S n в
количествах соответственно b1,b2,…,b n единиц. Используя данные ресурсы предприятие может изготовить изделия И1,И2,…, Иm , при этом известны величины aij, – количество i-го ресурса, идущего на изготовление одного изделия j-го вида (i = 1,2,…,n, j = 1,2,…,m). Кроме того, известны величины cj –
прибыль, получаемая предприятием от реализации одного изделия j-го вида. Требуется составить план выпуска изделий, при котором достигается максимальная суммарная прибыль (прибыль от реализации всех изделий).

Для решения поставленной задачи сформулируем её математическую модель, первоначально сведя исходные данные в следующую таблицу:

Математическая модель задачи распределения неоднородных ресурсов. Для построения математической модели задачи:
1. Определим неизвестные и их количество.
Введем следующие обозначения: пусть х1,x2,…,x m – количество изделий
И1,И2,…, Иm, которые может производить предприятие.
Поэтому количество рассматриваемых переменных – m штук.
2. Запишем целевую функцию, зависящую от х1,x2,…,x m и что с ней необходимо сделать (максимизировать или минимизировать).

Сформулируем ограничения рассматриваемой задачи.
Ограничения по запасам сырья. Зная количество сырья каждого вида, идущее на изготовление одной единицы изделия, и запасы сырья можно составить следующую систему ограничений:
Ресурс S1 : a11 * x1 + a12 * x2 + ... + a1m * xm £ b1;
: a21 * x1 + a22 * x2 + ... + a2m * xm £ b2 ;
Ресурс S2
... ... ... ...
... ...
Ресурс Sn : an1 * x1 + an 2 * x2 + ... + anm * xm £ bn .

3.2.Условие неотрицательности переменных. Исходя из физического смысла, на переменные налагаются дополнительные условия,
требующие неотрицательности их значений:
(3)
При этом равенство нулю соответствующей переменной означает, что данное изделие не выпускается.

Условие целочисленности переменных. На переменные можно накладывать дополнительное условие целочисленности, которое “ запрещает” выпуск не целых изделий:
Таким образом, целевая функция (1) и ограничения (2-4) образуют математическую модель задачи распределения неоднородных ресурсов.
(4)

Пример
Постановка задачи. Пусть предприятие располагает запасами сырья трех видов – цемент, щебень и арматура в количествах b1=18, b2=120 и b3=42 условных единиц соответственно. Из этого сырья может быть изготовлено два вида изделий – плиты перекрытия и фундаментные блоки. Известны так же значения аij – количество единиц i-го вида сырья, идущего на изготовление единицы j-го изделия и сj – доход, получаемый от реализации одной единицы изделия каждого вида (i=1,2,3; j=1,2). Все указанные величины представлены в табл. 1.

Направление подготовки бакалавров
38.03.06 Торговое дело
профиль Коммерция
Б2.В.ОД.1 Компьютерное моделирование в профессиональной деятельности
Таблица 1. Данные к задаче об использовании сырья

Требуется составить такой план продукции, при котором прибыль была бы максимальной
Математическая модель задачи распределения неоднородных
ресурсов. Для построения математической модели задачи: 1. Определим неизвестные и их количество.
Введем следующие обозначения: х1 – количество плит перекрытия, х2 – количество фундаментных блоков, которые может выпускать предприятие. 2. Запишем целевую функцию.
Суммарная прибыль, получаемая предприятием от реализации х1 единиц плит перекрытия и х2 единиц фундаментных блоков, может быть записана в виде
F(х1,х2 ) = 3 * x1 + 2 * x2 -> max.

Ограничения по запасам сырья.
Зная количество сырья каждого вида, идущее на изготовление одной единицы изделия, и запасы сырья можно составить следующую систему ограничений
(2)

Условие неотрицательности переменных. Исходя из физического смысла, на переменные налагаются дополнительные условия,
требующие неотрицательности их значений:
(3)
Условие целочисленности переменных. На переменные х1 и х2 можно накладывать дополнительное условие целочисленности, которое “ запрещает” выпуск не целых изделий:

Решение задачи
1. Построим таблицу

2. Выберем на вкладке данные «Поиск решения»
Зададим целевую функцию.
Желаемое значение целевой функции
Изменяемые ячейки

6. Зададим ограничения по ресурсам

7. Введем ограничения на выпуск продукции

В результате получаем следующие дынные

Сохраним отчеты

Результат решения

Направление подготовки бакалавров
38.03.06 Торговое дело
профиль Коммерция
Б2.В.ОД.1 Компьютерное моделирование в профессиональной деятельности

Направление подготовки бакалавров
38.03.06 Торговое дело
профиль Коммерция
Б2.В.ОД.1 Компьютерное моделирование в профессиональной деятельности

Направление подготовки бакалавров
38.03.06 Торговое дело
профиль Коммерция
Б2.В.ОД.1 Компьютерное моделирование в профессиональной деятельности