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

МИНИСТЕРСТВО СЕЛЬСКОГО ХОЗЯЙСТВА РФ

ФГБОУ ВО «Бурятская государственная сельскохозяйственная

академия им. В. Р. Филиппова»

Кафедра «Информатика и информационные технологии в экономике»

Самостоятельная работа № 1

По дисциплине «Экономико-математические методы и моделирование»

На тему:

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

Вариант 4

Выполнила: Проверила

Улан-Удэ

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

Для решения задачи необходимо:

1. Создать форму для ввода условий задачи.

2. Указать адреса ячеек, в которые будет помещён результат решения

(изменяемые ячейки).

3. Ввести исходные данные.

4. Ввести зависимость для целевой функции.

5. Ввести зависимости для ограничений.

6. Указать назначение целевой функции (установить целевую ячейку).

7. Ввести ограничения.

8. Ввести параметры для решения ЗЛП.

[pic 1]

Вводим переменные:

Х1-количество изделия А, шт.

Х2- количество изделия Б, шт.

Х3- количество изделия В, шт.

Х4- количество изделия Г, шт.

Составляем ограничения:

Z=12x1+7x2+18x3+10x4→max

        [pic 2]

1. Вводим исходные данные в Exсel:

[pic 3]

        Рис.1 Исходные данные

2.Вводим зависимость для целевой функции и левых частей ограничений с помощью Мастер функций в строке формул:

1. Выбираем категорию Математические.
2. Выбираем функцию СУММПРОИЗВ.
3. В массив 1 ввести B$3:E$3.
4. В массив 2 ввести B4:E4.

Далее ячейку F4 копируем в выделенный блок F7:F9.

[pic 4]

Рис.2. Зависимость для целевой функции и левых частей ограничений

3.Запускаем команду Поиск Решения

4.Вводим назначение целевой функции (установить целевую ячейку).

1. Курсор в поле «Установить целевую ячейку».
2. Вводим адрес $F$4.
3. Вводим направление целевой функции: Максимальному значению.
4. Вводим адреса искомых переменных:
5. Курсор в поле «Изменяя ячейки».
6. Вводим адреса B$3:E$3.

5.Вводим ограничения.

[pic 5]

Рис.3. Введены все условия для решения задачи

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

[pic 6]

Рис.4. Решение найдено

Создание отчёта по результатам поиска решения

Отчет о результатах

Результаты (Answer). В отчёт включаются исходные и конечные значения целевой и влияющих ячеек, дополнительные сведения об ограничениях.

[pic 7]

Рис.5. Отчет о результатах

Отчет об устойчивости

Устойчивость (Sensitivity). Отчёт, содержащий сведения о чувствительности решения к малым изменениям в изменяемых ячейках  или в формулах ограничений.

[pic 8]

Рис.6. Отчет об устойчивости

Отчет о пределах

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

[pic 9]

Рис.7. Отчет о пределах

Ответы на вопросы

1. Получено оптимальное решение X1=18, X2=0, X3=0, X4=11 т.е. необходимо произвести 18 изделий 1 вида и 11 изделий 4 вида. Изделие второго и третьего вида не производится, так как X2=0, Х3=0. Общая стоимость продукции равна 326 усл.ед.
2. Сырье 1 и 3 вида расходуются полностью, и остается неиспользованным 1 ед. сырья 2 вида. Следовательно, сырье 1 и 3 вида являются дефицитными.
3. Максимальный интервал изменения запасов каждого из ресурсов, в пределах которого структура оптимального решения, т.е. номенклатура выпускаемой продукции, остается без изменений:

Сырье 1-го вида может увеличиваться на 2 ед. и уменьшаться на 18 ед.

Сырье 2-го вида может увеличиваться бесконечно и уменьшаться на 1ед.

Сырье 3-го вида может увеличиваться на 2 ед. и уменьшаться на 22 ед.

1. Цена 1-го сырья равна 12 ед., цена 2-го сырья равна 7 ед., цена 3-го сырья равна 18 ед., цена 4-го сырья равна – 10 ед. Производство 2-го и 3-го вида сырья– нерентабельно.
2. При принудительном выпуске нерентабельной продукции 2-го и 3-го вида стоимость выпускаемой продукции уменьшиться на 22 ед. и 4ед., следовательно, прибыль составит 300 ед.
3. Снизить можно только недефицитное значение, то есть не связанное (рис.5). Недефицитным ресурсом в задаче сырье 2. Исходя из данных таблицы, запасы сырья 2 можно снизить на 1 ед. и это не приведет к уменьшению прибыли. Снижение запасов остальных ресурсов неизбежно приведет к потерям.
4. Цена 1-го сырья может бесконечно увеличиваться и уменьшаться на 4 ед. Цена 2-го сырья может увеличиваться на 22 ед. и бесконечно уменьшаться. Цена 3-го сырья может увеличиваться на 4 ед. и бесконечно уменьшаться. Цена 4-го сырья может увеличиваться на 14 ед. и уменьшаться на 4 ед.

1. Общая стоимость продукции и план её выпуска при увеличении запасов сырья I и II вида на 4 и 3 ед. соответственно и уменьшении на 3 ед. сырья III вида увеличиться на 13 ед. Общая стоимость составит 339ед.(Рис.8)[pic 10]

Рис.8. Общая стоимость при увеличении запасов сырья

10 .  Включение в план изделие Д ценой 10 ед., на изготовление которого расходуется по 2 ед. каждого вида сырья является нецелесообразным т.к максимальная прибыль не изменится.

[pic 11]

Рис.9. Включение в план изделие Д