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

 

 

 

 

 

 

 

 

 

 

3. РЕШЕНИЕ ЗАДАЧИ И  АНАЛИЗ ОПТИМАЛЬНОГО ПЛАНА

3.1. Постановка задачи.

Рассчитать оптимальный  состав машинно-тракторного парка  для выполнения трех видов сельскохозяйственных работ в весенний период. Выполнение каждого вида работ возможно с  помощью двух видов агрегатов (табл.1)

1. Фрагмент технологической карты

Виды работ	Объем работ, га	Агрегат		Произво- дитель-ность, га	Прямые затраты, руб.
		Трактор	СХМ
Первая пятидневка
Боронование в 2 следа	3250	ДТ-75М	21 БЗСС-1	230	232
		МТЗ-80	18 БЗСС-1	187	158
Вторая пятидневка
Культивация с боронованием	350	ДТ-75М	2 КПС-4 и 8 БЗСС-1	130	222
		МТЗ-80	КПС-4 и 4 БЗСС-1	57	421
Боронование в 1 след	780	ДТ-75М	21 БЗСС-1	230	132
		МТЗ-80	18 БЗСС-1	187	180

Стоимость сельскохозяйственных машин и тракторов:

ДТ-75М – 3230 руб., МТЗ-80 – 3900 руб., БЗСС-1 – 7,5 руб., КПС-4 – 310 руб.

     Коэффициент  эффективности капиталовложений  составляет 0,2.

Критерий оптимальности  – минимум приведенных затрат.

 

3.2. Развернутая экономико-математическая  модель

Система переменных

Х₁ – количество агрегатов в составе ДТ и 21 борона в 1-ю пятидневку

Х₂ – количество агрегатов в составе МТЗ и 18 борон в 1-ю пятидневку

Х₃ – количество агрегатов в составе ДТ, 2КПС и 8 борон во 2-ю пятидн.

Х₄ – количество агрегатов в составе МТЗ, КПС и 4 бороны во 2-ю пятидн.

Х₅ – количество агрегатов в составе ДТ и 21 борона во 2-ю пятидн.

Х₆ – количество агрегатов в составе МТЗ и 18 борон во 2-ю пятидн.

Х₇ – количество ДТ в 1-ю пятидневку

Х₈ – количество ДТ во 2-ю пятидневку

Х₉ – количество МТЗ в 1-ю пятидневку

Х₁₀ – количество МТЗ во 2-ю пятидневку

Х₁₁ – количество борон в 1-ю пятидневку

Х₁₂ – количество борон во 2-ю пятидневку

Х₁₃ – количество культиваторов во 2-ю пятидневку

 

Система ограничений     

1. По выполнению заданных объемов работ:

а) боронование в 2 следа  в 1-й пятидневке, га: 230Х₁ + 187Х₂ = 3250

б) культивация с боронованием во 2-й пятидн., га: 130Х₃ + 57Х₄ =350

в) боронование во 2-й пятидневке, га: 230Х₅ + 187Х₆ = 780

2. По соотношению агрегатов  и отдельных видов тракторов  и сельхозмашин:

а) количество ДТ-75М в 1-ой пятидневке: Х₁ <= Х₇     Х₁ – Х₇ < 0

б) количество ДТ-75М во 2-ой пятидневке: Х₃ + Х₅ – Х₈ < 0

в) количество МТЗ-80 в 1-ю пятидневке: Х₂ – Х₉ < 0

г) количество МТЗ-80 во 2-ю  пятидневке: Х₄ + Х₆ – Х₁₀ < 0

д) количество БЗСС-1,0 в 1-ой пятидневке: 21Х₁ + 18Х₂ – Х₁₁ < 0

е) количество БЗСС-1,0 во 2-й  пятидневке: 8Х₃ +4Х₄ +21Х₅ + 18Х₆ < Х₁₂

ж) количество КПС-4 во 2-ой пятидневке: 2Х₃ + Х₄ – Х₁₃ < 0

Целевая функция: минимум приведенных затрат, руб.

F = 232Х₁ + 158Х₂ + 222Х₃ + 421Х₄ + 132Х₅ + 180Х₆ + 646Х₇ + 646Х₈ + + 780Х₉ + 780Х₁₀ +1,5Х₁₁ + 1,5Х₁₂ + 62Х₁₃        min

Приведенные затраты = стоимость * 0,2

 

3.3. Алгоритм решения задачи  с помощью сервисной функции  Excel

Ввод данных задачи в таблицу  Excel:

Рис.1.

1. Заполняется таблица  «Переменные»:

• в строке Имя вводятся имена переменных (х₁, х₂, х_3…….).
• в строке Значения (В3:N3), которая не заполняется, будут высвечиваться промежуточные и оптимальные значения переменных.
• в строке Нижняя граница (В4:N4), которая не заполняется по умолчанию, каждая ячейка этой строки содержит ноль, используется для задания условия не отрицательности переменных, при необходимости может быть введена нижняя граница для переменных.
• строка Верхняя граница (В5:N5) заполняется при необходимости задания верхней границы переменным, в нашем примере не заполняется.
• в строке Коэффициенты целевой функции (В6:N6) вводятся коэффициенты целевой функции из модели.
• в ячейку Целевой функции (O6) вводится функция Сумма произведения ячеек строки Значения на ячейки строки Коэффициенты целевой функции: =СУММПРОИЗВ($B$3:$N$3;B6:N6)

 

2. Заполняется таблица  «Ограничения», для каждого ограничения:

• из модели вводятся технико-экономические коэффициенты
• в столбец Знак (P9:P18) проставляется знак ограничений
• в столбец Правая часть (Q9:Q18) вводятся объемы ограничений
• в столбец Левая часть (O9:O18) копируется формула из целевой ячейки: =СУММПРОИЗВ($B$3:$N$3;B9:N9)

=СУММПРОИЗВ($B$3:$N$3;B10:N10)

=СУММПРОИЗВ($B$3:$N$3;B11:N11)

=СУММПРОИЗВ($B$3:$N$3;B12:N12)

=СУММПРОИЗВ($B$3:$N$3;B13:N13)

=СУММПРОИЗВ($B$3:$N$3;B14:N14)

=СУММПРОИЗВ($B$3:$N$3;B15:N15)

=СУММПРОИЗВ($B$3:$N$3;B16:N16)

=СУММПРОИЗВ($B$3:$N$3;B17:N17)

=СУММПРОИЗВ($B$3:$N$3;B18:N18)

 

     Заполнение окна «Поиск решений»

• Курсор установить в Целевую ячейку (О6)
• В меню выбрать Данные, Поиск решения
• Заполнить окно «Поиск решения» (рис.2):

• в поле «Установить целевую ячейку» указать адрес Целевой ячейки (О6)
• установить переключатель в положение Равно минимальному значению
• в поле «Изменяя ячейки» мышью ввести интервал ячеек строки значения из таблицы «Переменные» $B$3:$N$3.

 

 

Рис. 2

 

2. Заполнить поле «Ограничения»:

• Щелкнуть мышью в поле «Ограничения»

• Щелкнуть мышью в поле «Добавить»
• Заполнить окно «Добавление ограничения»:

• Первым всегда вводим Граничное условие – все переменные должны быть больше или равны нулю (рис.3,4)
• Щелкнуть мышью в поле «ссылка на ячейку»
• Выделить мышью интервал ячеек строки Значения ($B$3:$N$3)
• Изменить знак, выбрав из списка >=
• Щелкнуть мышью в поле «Ограничения»
• Выделить мышью строку Нижняя граница ($В$4:$N$4)
• Нажать кнопку «Добавить»

 

Рис.3.

 

• В новом окне «Добавление ограничения» ввести следующее ограничение, так как три ограничения в модели имеют одинаковый знак, то их можно ввести одним выражением (рис.4):

• Щелкнуть мышью в поле «Ссылка на ячейку»
• Выделить мышью интервал ячеек столбца Левая часть ($O$9:$O$11)
• Выбрать знак   =
• Щелкнуть мышью в поле «Ограничения»
• Выделить мышью столбец Правая часть ($Q$9:$Q$11)
• Нажать кнопку ОК

Рис.4.

 

• В новом окне «Добавление ограничения» ввести следующее ограничение, так как все остальные ограничения в модели имеют одинаковый знак, то их можно ввести одним выражением (рис.5):

• Щелкнуть мышью в поле «Ссылка на ячейку»
• Выделить мышью интервал ячеек столбца Левая часть ($O$12:$O$20)
• Выбрать знак <=
• Щелкнуть мышью в поле «Ограничения»
• Выделить мышью столбец Правая часть ($Q$12:$Q$20)
• Нажать кнопку ОК.

Рис.5.

• Заполнить окно параметры:
• В окне поиск решения (рис. 2) нажать кнопку Параметры
• В окне Параметры (рис.6) установить флажки в положения Линейная модель и Показывать результаты итераций.
• Нажать кнопку ОК.
• Будет осуществлен переход в окно Поиск решения (рис.2).

Рис.6. Параметры поиска решения

 

• Получить оптимальное решение:
• В окне Поиск решения нажать кнопку Выполнить.
• На экране появиться результат первой итерации
• В окне Текущее состояние поиска решения нажать кнопку Продолжить – на экране появиться результат следующей итерации.
• Когда решение будет оптимальным на экране появиться окно «Результаты поиска решения» (рис.8).

Рис.7.Оптимальное  решение

 

Рис.8.Результаты поиска решения

 

• В окне «Результаты поиска решения» мышью выбрать Тип отчета «Устойчивость» и нажать кнопку (рис.8)
• В результате на текущем листе останется модель с оптимальным решением (рис.7), а на предыдущем листе будет сформирован Отчет по устойчивости1 (рис.9).

 

Рис.9. Отчет по устойчивости.

 

4. Анализ оптимального решения

По оптимальному плану (табл.2) следует использовать:

В 1-ю пятидневку: 6 агрегатов в составе ДТ и 21 БЗСС (х₁);

Во 2-ю пятидневку: 2 агрегата – ДТ, 2 КПС и 8БЗСС (х₃);

3 агрегата  в составе ДТ и 21 БЗСС (х₅).

Нецелесообразно использовать агрегаты с трактором МТЗ-80 (х₂, х₄, х₆).

Всего потребуется:

В 1-ю пятидневку: ДТ-75М – 6 шт. (х₇); БЗСС – 1,0 – 126 шт. (х₁₁);

Во 2-ю пятидневку: ДТ-75М – 5 шт. (х₈); БЗСС – 1,0 – 79 шт. (х₁₂);

КПС-4 – 4 шт. (х₁₃).

Выполнение условий:

• 1 гр. ограничений – все работы будут выполнены полностью;
• 2 гр. Ограничений – все математические соотношения выполнены.

 

Критерий оптимальности:

Приведенные затраты будут  минимальными  и составят …..руб.

 

5. Анализ устойчивости оптимального решения

Таблица Изменяемые ячейки (табл.3)

Нормированная стоимость  показывает, что затраты увеличатся, если:

• Использовать во 2-ю пятидневку 1 агрегат в составе с МТЗ – на 280 руб.;
• Использовать в любую пятидневку 1 МТЗ - на 290 руб.

 

2.Оптимальный план использования машинно-тракторного парка

 

3.Отчет по устойчивости. Изменяемы ячейки.

 

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

• От 0 до 486 руб. – на боронование 1-м агрегатом;
• От 0 до 631 руб. – на боронование 2-м агрегатом;
• И так далее.

Теневая цена показывает, что  затраты увеличатся, если увеличить  объем работ на 1 га: - по боронованию  – на 3,5 руб.; - по культивации –  на 7,2 руб.

 

 

 

 

 

3. Отчет по устойчивости. Ограничения.