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

Рис. 14. Отчет по устойчивости для решения при крайних значениях целевых коэффициентов

Если значения удельных прибылей равны, то получим случай множественных альтернативных оптимальных решений задачи линейной оптимизации: любая пара неотрицательных чисел х1 и х2 таких, что их сумма равна 500 и х2 £ 150(ограничения должны выполняться), будет решением данной задачи, при этом значения целевой функции для любых таких решений будут одинаковыми. Чтобы убедиться в этом, введите в ячейки В8 и С8 одинаковые значения, например 2300. Затем в ячейки В4 и С4 введите числа, удовлетворяющие перечисленным выше условиям, и запустите Поиск решения. Наверняка вы получите решение с введенными вами значениями переменных. Если же в ячейки В4 и С4 вы введете произвольные числа в качестве начальных значений для переменных х1 и х2 то получите либо решение х1 = 500 и х2 = 0 (рис. 15), либо решение х1 = 350 и х2 = 150 (рис. 16). Это так называемые «крайние» решения. Других решений, хотя их существует бесконечно много, вы не получите.

Рис. 15. Решение, предлагающее отказаться от краски Б

На практике при решении задач линейной оптимизации множественные оптимальные решения встречаются относительно редко. Скорее, эта ситуация может проявиться при проведении анализа чувствительности, как в нашем примере. Признак того, что при данном решении существуют другие альтернативные решения, опять дает отчет по устойчивости. Если в таблице Изменяемые ячейки в столбцах Допустимое увеличение и Допустимое уменьшение для некоторых переменных присутствуют нули, то это и является признаком того, что существуют альтернативные решения. Например, на рис. 17 показан отчет по устойчивости для нашей задачи, когда целевые коэффициенты равны 2300, а х1 = 350 и х2 = 150. Как видите, в столбцах Допустимое увеличение и Допустимое уменьшение таблицы Изменяемые ячейки действительно присутствуют нулевые значения. В других отчетах, показанных выше, нулей в этих столбцах вы не найдете, поскольку там множественных альтернативных решений не было.

Рис. 16. Альтернативное решение, сохраняющее производство краски Б

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

Рис. 17. Отчет по устойчивости в случае множественных решений

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

Что дает наличие альтернативных решений? Хорошо это или плохо? Плохо, поскольку решений бесконечно много, и надо сделать выбор из бесконечного множества решений. Хорошо — поскольку с «точки зрения» целевой функции все эти решения равнозначны, можно привлечь дополнительный критерий отбора решений, который изначально не учитывался в модели. Тем самым можно улучшить решение, сделать его «более оптимальным», но в соответствии с новым критерием. Например, в нашем примере среди альтернативных решений можно найти такое решение, которое обеспечивает минимальные суммарные запасы сырья при той же величине прибыли. Легко убедиться, что при решении х1 = 500 и х2 = 0 потребуется 80 тонн всех видов сырья, а при решении х1 = 350 И x2 = 150 — 93,5 тонн.

Прежде чем подвести итоги анализа чувствительности, надо как-то записать и структурировать ту информацию, которую мы получили в результате этого анализа. Для этого можно нарисовать таблицу, где для тех значений параметров модели, которые изменялись при проведении анализа чувствительности, были бы приведены значения переменных решения и соответствующие значения целевой функции. В Excel есть средство для создания подобных таблиц. Это средство называется сценарии.

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

Покажем на нашем примере, как создавать и сохранять сценарии и как на их основе затем построить отчет. Конечно, сценарии надо сохранять по мере их «созревания», т. е. после каждого изменения, внесенного в табличную модель. Но, допустим, что мы забыли об этом правиле или вообще ничего не знали о сценариях. И сейчас хотим наверстать упущенное, создав кучу сценариев на все случаи жизни.

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

Итак, вспомним нашу первую модель и ее решение, где целевые коэффициенты с1 и с2 равнялись соответственно 2000 и 2500, а правая часть пятого ограничения равнялась 30. Восстановите на рабочем листе эти значения и запустите средство Поиск решения для получения решения. Надеюсь, вы получили прежнее решение: x1 = 257,14, x2 = 150 и z =  889285,71 (см. рис. 7).

Чтобы создать новый сценарий для текущего рабочего листа, выполните следующие действия.

1. Выберите команду Сервис->Сценарии (В Excel 2007 — Данные->Анализ “что если”).

2. В открывшемся диалоговом окне Диспетчер сценариев щелкните на кнопке Добавить (рис. 18).

3. В диалоговом окне Изменение сценария введите название сценария в поле ввода Название сценария (рис. 19). Желательно давать содержательные названия, показывающие отличия данного сценария от других. В нашем примере первый сценарий назовем Исходный.

4. В поле ввода Изменяемые ячейки введите адреса ячеек, содержащих константы, задающие параметры модели. Эти ячейки в сценариях называются изменяемые ячейки. В нашем примере надо ввести B4:C4;B8:C8;F11:F17. Проще всего вводить адреса ячеек путем выделения ячеек непосредственно на рабочем листе.

5. В поле ввода Примечание желательно ввести комментарии к создаваемому сценарию. Если вы не введете комментарии, то Excel автоматически создаст примечание, содержащее имя создателя сценария (по зарегистрированному имени пользователя) и дату его создания.

6. Щелкните в диалоговом окне Изменение сценария на кнопке ОК.

7. В открывшемся диалоговом окне Значения ячеек сценария проверьте и при необходимости измените значения для изменяемых ячеек (рис. 20).

8. Щелкните в диалоговом окне Значения ячеек сценария на кнопке ОК, что создает сценарий и возвращает в диалоговое окно Диспетчер сценариев.

Рис. 18. Диалоговое окно Диспетчер сценария — основное окно для работы со сценариями

Рис. 19. Диалоговое окно Изменение сценария — создание нового сценария

Рис. 20. Задание значений для нового сценария

Сценарий создан. Чтобы посмотреть, как сценарий вычисляет результаты (и для проверки сохраненных в сценарии значений), измените какие-либо значения на рабочем листе (например, измените значения переменных решения) и затем выполните следующие простые действия. Выберите команду Сервис->Сценарии, в открывшемся диалоговом окне Диспетчер сценариев в списке Сценарии выберите сценарий, который вы хотите отобразить, и щелкните на кнопке Вывести. Excel должен воспроизвести на рабочем листе решение нашей первой задачи, которое показано на рис. 7. Если есть какие-нибудь числовые расхождения между тем, что показано на рис. 7, и результатами восстановленного сценария, то проверьте в сценарии значения изменяемых ячеек.

Далее создаем сценарий для решения, где правая часть пятого ограничения заменена значением 36,5. Для этого введите в ячейку F14 данное значение и найдите решение с помощью средства Поиск решения (см. рис. 11). Затем повторите описанные выше действия по созданию сценария. Этот новый сценарий назовем, к примеру, Полная загрузка.

Подобным образом создаем сценарий, где удельные прибыли красок обоих типов равны, и поэтому Excel предлагает отказаться от производства краски Б (см. рис. 15). Этот сценарий назовем Без краски Б. Наконец, создадим еще один сценарий, где удельные прибыли красок также равны, но требуется произвести 150 тонн краски Б (см. рис. 16). Этому сценарию дадим название Даешь краску Б!.

Создать отчет по имеющимся сценариям можно следующим образом.

1. Выберите команду Сервис->Сценарии.

2. В открывшемся диалоговом окне Диспетчер сценариев щелкните на кнопке Отчет.

3. В диалоговом окне Отчет по сценарию укажите, какой тип отчета вы хотите создать — выберите переключатель структура для создания итогового отчета в виде структурированного рабочего листа либо переключатель сводная таблица — для создания итогового отчета в виде сводной таблицы (рис. 21). Для сценариев решения задач оптимизации наиболее подходит отчет в виде структурированного рабочего листа.

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

5. Щелкните на кнопке ОК.

Рис. 21. Диалоговое окно Отчет по сценарию

Отчет по сценариям будет легко читаться и будет понятен с первого взгляда, если изменяемым ячейкам сценариев и ячейкам результатов (задаваемых при создании отчета) присвоить уникальные имена, соответствующие их «сущности». Присвоить имена ячейкам можно, в частности, с помощью команды Вставка->Имя->Присвоить. В противном случае ячейки в столбце В отчета останутся пустыми и заполнять их придется вручную.

Готовый отчет по нашим сценариям показан на рис. 22. Этот отчет может послужить необходимому делу документирования и обоснования принятия решения. Он послужит основой для заключительных выводов выполненного анализа чувствительности.

 

Подведем итоги выполнения анализа чувствительности в нашем примере.

1. Первоначальное решение (сценарий Исходный в отчете на рис. 22) — производить 257,14 т краски А и 150 т краски Б, при этом будет получена прибыль в размере 889 285,17 руб. — не загружает полностью производственные мощности.

Рис. 22. Отчет по сценариям

2. Чтобы полностью загрузить производственные мощности, надо увеличить месячный запас сырья 2 с 30 до 36,5 тонн (сценарий Полная загрузка в отчете на рис. 22), при этом следует производить 350 т краски А и 150 т краски Б, тогда будет получена прибыль в размере 1 075 000 руб.

3. Первые два решения имеют силу, если удельная прибыль краски Б превышает удельную прибыль краски А. Если удельная прибыль краски Б меньше удельной прибыли краски А, то производить краску Б нерентабельно.

4. Если удельная прибыль краски Б примерно равна удельной прибыли краски А, то прибыль не зависит от количества произведенной краски Б (сценарии Без краски Б и Даешь краску Б! в отчете на рис. 22). При этом рационально отказаться от производства краски Б или уменьшить ее производство до минимума, поскольку это сокращает необходимый для производства суммарный запас всех видов сырья (сценарий Без краски Б).

 

Вот так можно кратко подвести итоги анализа нашей математической модели реальной ситуации. Как эти итоги и полученные «оптимальные» решения будут интерпретированы в реальную ситуацию, какое влияние они окажут (и окажут ли) на процесс принятия реального решения, как это решение будет воплощаться в жизнь — это вопросы реальной жизни, ответить на которые может только сама жизнь.

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

 

Задача 1

На швейной фабрике для изготовления четырёх видов изделий может быть использована ткань трёх артикулов. Нормы расхода тканей всех артикулов на пошив одного изделия приведены в таблице. В ней так же указаны имеющиеся в распоряжении фабрики общее количество тканей каждого артикула и цена изделия данного вида. Определить, сколько изделий каждого вида должна произвести фабрика, чтобы стоимость изготовленной продукции была максимальной. Сколько ткани каждого из артикулов может сэкономить фабрика не теряя прибыли? Насколько минимально нужно поднять цену на четвертое изделие, чтобы это увеличило прибыль? Что произойдет с прибылью, если фабрике будет необходимо выпускать изделие 3 в количестве не меньше 5 штук?

 

Артикул ткани	Норма расхода ткани (м) на одно изделие  вида				Общее количество ткани
	1	2	3	4
I II III	1 - 4	- 1 2	2 3 -	1 2 4	180 210 800
Цена одного изделия (руб.)	9±2	6	4±3	7