Процедура поиск решения табличного процессора excel

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

— минимизация затрат на транспортировку (классическая задача);

— составления оптимального по прибыли замысла выпуска продукции;

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

— составление смесей;

— оптимальный раскрой материалов;

— максимизация выпуска товаров при имеющихся ограничениях на сырье.

Вызов процедуры Поиск ответа осуществляется одноименной командой из подменю Сервис главного менюExcel. Наряду с этим появляется диалоговое окно Поиск ответа, продемонстрированное на рис. 3.

В поле Установить целевую записывается ссылка на ячейку (в нашем случае на ячейку D4), содержащую формулу с целевой функцией. В группе тумблеров Равной указывается направление оптимизации ЦФ (в нашем случае: равной большому значению).

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

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

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

Рис. 3 Диалоговое окно Поиск ответа

Наряду с этим вызывается диалоговое окно Добавление ограничения, продемонстрированное на рис. 4.

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

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

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

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

Работа в окне диалога Поменять ограничение подобна работе в окне Добавление ограничения.

Кнопка Параметры приводит к диалоговому окну Параметры поиска

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

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

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

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

Рис. 5. диалоговое окно Параметры поиска

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

Поле Сходимость помогает для останова ответа при, в то время, когда относительное изменение значения в целевой ячейке за последние 5 итераций делается меньше указанного значения. Используется лишь к нелинейным задачам. Поле должно содержать число из промежутка от 0 (нуля) до 1.

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

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

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

Поле Оценка помогает для указания способа экстраполяции (Линейная либо Квадратичная), применяемого для получения исходных оценок значений переменных в каждом одномерном поиске. При ответе нелинейных задач квадратичная экстраполяция дает отличных показателей.

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

Поле Способ помогает для выбора метода оптимизации (Способ Ньютона либо Сопряженных градиентов) для указания направления поиска.

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

Этот вариант предусмотрен для хранения на странице более одной модели оптимизации. Первая модель сохраняется машинально.

Задание2. Проверить установку параметров поиска ответа Вашей задачи и при необходимости поменять их.

Окно диалога Результаты поиска ответа

Поиск ответа осуществляется при нажатии кнопки Выполнитьокна Поиск ответа. На рис. 6 продемонстрированы результаты ответа задачи оптимизации замысла выпуска продукции для условий примера, приведенного на рис. 1.

A B C D E F G
Переменные
х1 х2 Целевая
их значение: функция:
Коэф. в ЦФ:
Ограничения: вычисляемые символ исходные
На одно
изделие
элементов:

Рис. 6. Результаты ответа задачи оптимизации.

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

При повторном обращении к диалоговому окну Поиск ответа и нажатии кнопки Вернуть будут восстановлены все выделения поиска ячеек и параметры решения.

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

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

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

Рис. 7. Окно итогового сообщения о итогах поиска ответа.

Кнопка Сохранить сценарийслужит для отображения окна диалога Сохранение сценария,в котором возможно сохранить сценарий ответа задачи, дабы применять его в будущем посредством диспетчера сценариев Ехсе1.

Поле Тип отчетаслужит для указания типа отчета, размещаемого на отдельном странице книги Ехсеl. В этом поле возможно пометить каждые (либо все) из предлагаемых типов отчетов, каковые будут созданы по выходе из окна Результаты поиска ответа:

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

Тип Устойчивость употребляется для отчета, содержащего сведения о чувствительности ответа к малым трансформациям в формуле модели либо в формулах ограничений.

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

Задание 3. Отыскать оптимальное ответ сформулированной задачи, просмотреть его, и вернуть исходные значения задачи. Снова взять оптимальное ответ. Сохранить его с одновременным выводом на отдельных страницах Ехсеl всех типов отчетов о поиске ответа. Сохранить под выбранным именем книгу Ехсеl с результатами работы.

Урок 1.Поиск ответа, оптимизация, оптимальный замысел производства


Также читать:

Понравилась статья? Поделиться с друзьями: