Microsoft Excel

Рисунок 7 Диалоговое окно «Аргументы функций» 

      После ввода всех данных нажимаем Ctrl+Shift+Enter чтобы ввести формулу как табличную. В результате вычисления получаем значения независимых переменных x1, x2, x3.

      Решение системы линейных уравнений с  помощью MS Excel представлены в разделе 4 «Листы Microsoft Excel с исходными данными, формулами, диаграммами и полученными результатами решения».

3. Решение Задач оптимизации с помощь надстройки Поиск решения 

       Многие  проблемы производства, проектирования, прогнозирования сводятся к широкому классу задач оптимизации, для решения которых применяются математические методы. Типовыми задачами такого плана являются, например, следующие:

• Ассортимент продукции. Максимизация выпуска товаров при ограничениях на сырье для производства этих товаров.
• Штатное расписание. Составление штанного расписания для достижения наилучших результатов при наименьших расходах.
• Планирование перевозок. Минимизация затрат на транспортировку товаров.
• Составление смеси. Достижение заданного качества смеси при наименьших расходах.
• Размер емкости. Определение размеров некоторой емкости с учетом стоимости материала для достижения максимального объема.
• Случайные величины. Разнообразные задачи, в которые входят случайные величины.
• Разнообразные задачи оптимального распределения ресурсов и оптимального проектирования и т.д.

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

Рисунок 8 Диалоговое окно Окно Поиск решения

Рисунок 9 Диалоговое окно Добавления ограничения

Таблица 1 Опции окна Поиск решения

Продолжение таблицы 1

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

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

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

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

• в модели с несколькими периодами времени величина материального ресурса на начало следующего периода должна равняться величине этого ресурса на конец предыдущего периода;
• в модели постановок величина запаса на начало периода плюс количество полученного должна равняться величине запаса на конец период плюс количество отправленного;
• многие величины в модели по своему физическому смыслу не могут быть отрицательными, например, количество полученных единиц товара.

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

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

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

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

      В результате поиска решения могут  быть получены не только оптимальные  значения параметров (изменяемых ячеек) задачи, но и отчеты трех типов: Результаты, Устойчивость и Пределы. Для создания нужных отчетов выделите их названия в списке Тип отчета диалогового окна Результаты поиска решения, приведенного на рисунке 10, и щелкните кнопку OK. (При выделении нескольких отчетов следует удерживать клавишу Ctrl.) Каждый отчет выводится на от дельном листе текущей рабочей книги.

Рисунок 10 Диалоговое окно Результаты поиска решения 

      В отчете Результаты выводятся значения целевой ячейки, изменяемых ячеек и ограничений. Для каждого ограничения включается также информация о состоянии и невязке. Состояние может быть связанным, не связанным или не удовлетворенное. Невязка есть разница между значением решения в ячейках с ограничениями и правой частью формулы ограничения. Ограничение является связанным, если невязка равна 0. Ограничение является не связанным, если решение удовлетворяет ему при невязке, отличной от 0.

      В отчете типа Устойчивость выводится информация о чувствительности целевой ячейки к изменениям ограничений. Отчет содержит два раздела: Изменяемые ячейки и Ограничения. Данные по каждой изменяемой и ограничиваемой ячейке выводятся на отдельной строке. Правый столбец каждого раздела содержит информацию об устойчивости. В разделе Изменяемые ячейки – это Нормированный градиент, характеризующий изменение значения целевой ячейки при увеличении на единицу значения соответствующей изменяемой ячейки. Аналогично, в разделе Ограничения столбец Множитель Лагранжа характеризует изменение значения целевой ячейки при увеличении на единицу значения соответствующего ограничения.

      При установке флажка Линейная модель в отчет типа Устойчивость включаются дополнительные данные. В разделе Изменяемые ячейки столбец Редуцированная стоимость характеризует изменение целевой ячейки при увеличении на единицу значения изменяемой ячейки. Столбец Целевой коэффициент характеризует корреляцию значений изменяемой и целевой ячеек. Столбцы допустимое увеличение и Допустимое уменьшение характеризует пределы, в которых может изменяться Целевой коэффициент, не затрагивая изменяемые ячейки.

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

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

3.2 Использование команды Поиск решения в решении задания 3 

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

Таблица 2

      Итак, выберем команду Поиск решения из меню Сервис – открывается диалоговое окно, показанное на рисунке 11. В поле Установить целевую ячейку устанавливаем адрес ячейки которая используется в качестве критерия в нашем случае количество пряжи Е16.Но так как для выполнения плана нам достаточно 120 кг пряжи, а производство обеспечено 180 кг пряжи, то в графе равной установим переключатель значению и введем в поле справа 180.