Тема: Анализ задач линейного программирования в Excel

Цель: Научиться проводить экономико-математический анализ задачи линейного программирования

 

Теоретические сведения

Анализ внутренней структуры

Анализ оптимального решения выполняется на основании тех положений симплекс - метода, которые были рассмотрены на лекции, и начинается после успешного завершения решения задачи, когда на экране появляется диалоговое окно Результат поиска решения. Решение найдено. С помощью этого диалогового окна можно вызвать отчеты трех типов по алгоритму 3.1:

¨ результаты;

¨ устойчивость;

¨ пределы

Алгоритм 3.1. Вызов отчетов анализа.

На экране: диалоговое окно Результат поиска решения. Решение найдено.

1. Курсор на тип вызываемого отчета. Начнем с Отчета по результатам.

2. ОК.

На экране: вызванный отчет на новом листе, на ярлычке которого указано название отчета.

3. Курсор на ярлычок с названием отчета.

4. М1.

На экране: вызванный отчет.

Отчет по результатам

Отчет по результатам состоит из трех таблиц:

¨ Таблица 1 приводит сведения о целевой функции.

В столбце Исходно приведены значения целевой функции до начала вычислений.

¨ Таблица 2 приводит значения искомых переменных, полученные в результата решения задачи.

¨ Таблица 3 показывает результаты оптимального решения для ограничений и граничных условий.

Для Ограничений в графе Формула приведены зависимости, которые были введены в диалоговое окно Поиск решения; в графе Значения приведены величины использованного ресурса; в графе Разница показано количество неиспользованного ресурса. Если ресурс используется полностью, то в графе Состояние указывается связанное; при неполном использовании ресурса в этой графе указывается не связан.

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

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

Отчет по устойчивости состоит из двух таблиц.

В таблице 1 приводятся следующие значения для переменных:

¨ Результат решения задачи;

¨ нормир. стоимость (нормированая стоимость), т. е. дополнительные двойственные переменные Vj, которые показывают, насколько изменится целевая функция при принудительном включении единицы этой продукции в оптимальное решение;

¨ коэффициенты целевой функции;

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

В таблице 2 приводятся аналогичные значения для ограничений:

¨ величина использованных ресурсов;

¨ теневая цена, т. е. двойственные оценки Z_i, которые показывают, как изменится целевая функция при изменении запаса ресурса на единицу;

¨ значения приращения ресурса Db_i, при которых сохраняется оптимальный набор переменных,входящих в оптимальное решение.

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

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

¨ приводятся значения Xj в оптимальном решении;

¨ приводятся нижние пределы изменения значения Xj.

Кроме этого, в отчете указаны значения целевой функции при выпуске данного типа продукции на нижнем пределе. Так, при значении 730 видно, что

F = C₁ X₁ + C₃ X₃ = 60 * 0 + 120 * 6 = 720

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

F = 60 * 10 + 120 * 6 = 1320

 

Вариантный анализ

Для задач линейного программирования наибольший интерес представляет решение двух задач вариантного анализа:

· параметрического анализа, в ходе которого решаются задачи при различных значениях одного из параметров;

· сравнительный анализ нескольких моделей одной и той же ситуации

Параметрический анализ

Под параметрическим анализом будем понимать решение задачи оптимизации при различных значениях того параметра, который ограничивает улучшение целевой функции. Параметрический анализ будем выполнять для задачи, которая разобрана в лабораторной работе №1 “Решение задач линейного программирования средствами ЭТ Excel”, решая ее при различных значениях имеющихся финансов.

Сравнительный вариантный анализ

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

Возможные варианты постановки задачи:

1. Критерий эффективности – максимум прибыли, ограничения – по запасам ресурсов, граничные условия – неотрицательность управляемых переменных.

2. Критерий эффективности – минимум затрат, ограничения и граничные условия те же.

3. Критерий эффективности – максимум продукции в стоимостном выражении, ограничения и граничные условия те же.

4. Критерий эффективности – максимум продукции в натуральном выражении, ограничения и граничные условия те же.

5. Критерий эффективности – максимальное использование имеющихся ресурсов, ограничения и граничные условия те же.

 

2 пример выполнения Лабораторной работы

2.1 пример проведения параметрического анализа

Алгоритм 3.2. Выполнение параметрических расчетов.

1. Подготовительные работы:

1.1. Составить на бумаге таблицу вариантов

Вариант	1	2	3	4	5
Финансы

 

1.2. Вызвать на экран таблицу с результатом решения задачи

1.3. Удалите результат решения, находящийся в B3:E3.

2. Решение задачи для 1 - го варианта.

2.1. Ввести в ячейку H11 => 50

2.2. Сервис, Поиск решения...

2.3. Выполнить.

На экране: диалоговое окно Результаты поиска решения

2.4. Сохранить сценарий...

2.5. Ввести имя сценария финансы = 50 (рисунок 3.1)

Рисунок 3.1

2.6. ОК

На экране: диалоговое окно Результаты поиска решения

2.7. ОК

На экране: результат решения для данного варианта (рисунок 3. 2)

 

Рисунок 3.2

3. Решение задачи для последующих вариантов.

3.1. Ввести в H11 значение финансов для следующего варианта.

3.2. Выполнить п.2.2 - 2.7, при этом в п.2.5 вводить имя сценария, соответствующее значению финансов.

4. Представление результатов решения.

4.1. Сервис, Сценарии...

На экране: диалоговое окно Диспетчер сценариев

4.2. Отчет...

На экране: диалоговое окно Отчет по сценарию

4.3. Структура.

4.4. ОК

На экране: отчет Структура сценария

Внимание!!!

Сравните данные из структуры сценария со значениями, приведенными на рисунке 3.3. Если числа не совпадают, проверьте какой тип разделителя между целой и дробной частью использует операционная система WINDOWS:

- Пуск → Настройка → Панель управления → Язык и стандарты → Числа

- Установить в окне Разделитель целой и дробной части: "."

 

Для удобства дальнейшей работы выполним редактирование Итогового сценария.

Алгоритм 3.3. Редактирование Итогового сценария

1. Для размещения на экране всего отчета Итоговый сценарий в окне масштаба назначить 75 %.

2. Скрыть столбцы B и D.

3. Удалить строки 5 и 10.

4. Ввести:

¨ Прод1: Прод4 в ячейки С5:С8

¨ Прибыль в С9.

¨ Виды ресурсов: трудовые, сырье, финансы в ячейки С10:С12

5. Увеличить ширину столбца С.

6. Для наглядного представления данных на диаграммах:

¨ В дробных значениях Прод1: Прод4 назначить 2 знака после запятой.

¨ Дробные значения в строках Прибыль, трудовые, сырье и финансы округлить до целых чисел.

7. Убрать примечание.

На экране: Отредактированный итоговый сценарий (рисунок 3. 3)

 

Рисунок 3.3

Для наглядного представления результатов параметрического анализа на основании отредактированной таблицы построим графики.

Алгоритм 3.4. Построение гистограммы для искомых переменных

1. Выделить С3:I8

2. Построить гистограмму

3. Отформатировать гистограмму (рисунок 3.4)

Из диаграммы можно сделать выводы:

¨ При различном финансировании в план входит продукция различных видов, однако, ни в один вариант не входит выпуск Прод2. Это объясняется тем, что при высоком потреблении ресурсов прибыль от производства Прод2 ниже, чем от производства других видов продукции.

 

Рисунок 3.4

 

¨ Для значений финансов 50,150,200 величина выпускаемой продукции является дробной. Такое положение допустимо при планировании выпуска ткани, добычи нефти и т.д.

Алгоритм 3.5. Построение смешанной диаграммы для целевой функции и требуемого сырья

1. Выделить С3:I3, С9:I9, С11:I11.

2. Построить смешанную диаграмму

3. Выполнить форматирование диаграммы.

 

 

Рисунок 3.5

На экране: диаграмма (рисунок 3.5), на основании которой можно сделать выводы:

¨ Увеличение финансирования дает увеличение прибыли.

¨ При увеличении финансирования, начиная со 150, происходит уменьшение потребляемого сырья, т.к. выпуск Прод3, Прод4, обеспечивающих увеличение прибыли, требует меньшего потребления сырья.

 

2.1 пример выполнения сравнительного

вариантного анализа

Выполним вариантный анализ распределительной задачи.

1 постановка задачи

Составить план выпуска продукции, что бы прибыль была максимальна.

Целевая функция:

 

F = 60*X1+70*X2+120*X3+130*X4 -> max

 

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

x1 + x2 + x3 + x4 £ 16

6 * x1 + 5 * x2 + 4 * x3 + 3 * x4 £ 110

6 * x1 + 6 * x2 + 10 * x3 + 13 * x4 £ 100

 

Решение задачи:

1. Ввести условие задачи и целевую функцию в таблицу для ввода условий задачи, руководствуясь алгоритмом 1.1

2. Назначить целевую функцию, ввести ограничения и граничные условия по алгоритму 1.2

3. Решить задачу по алгоритму 1.3

На экране: рисунок 3.6

4. Сохранить сценарий…

5. Ввести имя МАКС ПРИБ

2 постановка задачи

Критерий эффективности – максимум продукции в натуральном выражении

 

Рисунок 3.6

Целевая функция:

 

F = X1 + X2+ X3+ X4 -> max

 

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

x1 + x2 + x3 + x4 £ 16

6 * x1 + 5 * x2 + 4 * x3 + 3 * x4 £ 110

6 * x1 + 6 * x2 + 10 * x3 + 13 * x4 £ 100

 

Решение задачи:

1. Ввести условие задачи и целевую функцию в таблицу для ввода условий задачи, руководствуясь алгоритмом 1.1

2. Назначить целевую функцию, ввести ограничения и граничные условия по алгоритму 1.2

3. Решить задачу по алгоритму 1.3

4. На экране: рисунок 3.7

5. Сохранить сценарий…

6. Ввести имя МАКС ПРОД

 

Рисунок 3.7

3 постановка задачи

Критерий эффективности – максимальное использование имеющихся ресурсов

Целевая функция:

 

F = x1 + x2 + x3 + x4 + 6 * x1 + 5 * x2 + 4 * x3 + 3 * x4 + 6 * x1 + 6 * x2 + 10 * x3 + 13 * x4 -> max

 

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

x1 + x2 + x3 + x4 £ 16

6 * x1 + 5 * x2 + 4 * x3 + 3 * x4 £ 110

6 * x1 + 6 * x2 + 10 * x3 + 13 * x4 £ 100

 

Решение задачи:

1. Ввести условие задачи в таблицу для ввода условий задачи, руководствуясь алгоритмом 1.1

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

Ø Курсор в F6

Ø М1

Ø Ввести =F9+F10+F11

2. Назначить целевую функцию, ввести ограничения и граничные условия по алгоритму 1.2

3. Решить задачу по алгоритму 1.3

4. На экране: рисунок 3.8

5. Сохранить сценарий…

6. Ввести имя МАКС РЕСУРС

7. Сервис, Сценарии…

8. Отчет…

Структура

10. ОК. На экране: Структура сценария(рисунок 3.9)

max

Рисунок 3.8

 

Рисунок 3.9

 

Рисунок 3.10

 

11. Выполните редактирование структуры сценария. На экране: Структура сценария после редактирования (рисунок 3.10).

 

Задание

1. Откройте файл, созданный при выполнении лабораторной работы №1 “Решение задач линейного программирования средствами ЭТ Excel”

2. Перейдите на лист с решением задачи 1.

Установите начальные значения в ячейки исходных данных.

3. Сервис, Поиск решения…,Выполнить.

4. Создайте отчет по результатам, отчет по устойчивости, отчет по пределам.

5. Дайте ответ на вопросы, пользуясь информацией из отчетов:

5.1. Какие ресурсы являются дефицитными, а какие не дефицитными?

5.2. Каковы остатки ресурсов (Y_i )?

5.3. Определите, как изменится целевая функция при принудительном включении единицы каждого вида продукции в оптимальный план?

Проверьте на практике правильность своих выводов.

5.4.В каких пределах можно изменять коэффициенты целевой функции, оставляя оптимальный план неизменным?

Проверьте на практике правильность своих выводов.

5.5. Как изменится целевая функция при изменении запаса дефицитного ресурса на единицу?

Проверьте на практике правильность своих выводов.

5.6. Определите, во сколько раз ресурс 1 дефицитнее ресурса 3?

5.7. Как перевести изделие из категории убыточных в категорию безразличных?

5.8. Проверьте обоснованность хранения запасов ресурса, имеющего остатки?

5.9. Определите значение целевой функция, при X1=0?

5.10. Определите значение целевой функция, при X3=0?

6. Выполните параметрический анализ по алгоритмам 3.2, 3.3, 3.4.

7. Выполните экономико-математический анализ решения оптимального задачи распределения ресурсов, составленной вами (анализ на чувствительность и вариантный анализ).

 

ТРЕБОВАНИЯ К ОТЧЕТУ ПО ЛАБОРАТОРНОЙ РАБОТЕ

 

Отчет должен содержать:

1. Выводы по результатам анализа на чувствительность задачи 1.

2. Выводы по параметрическому анализу задачи 1.

3. Выводы по результатам анализа на чувствительность задачи, составленной вами.

4. Выводы по параметрическому анализу задачи, составленной вами.

5. Выводы по сравнительному вариантному анализу задачи, составленной вами.

 

Лабораторная работа N4